Re: replication / caching SQL Server data on Oracle
From: CarlosAL <miotromailcarlos_at_netscape.net>
Date: Wed, 7 Dec 2011 00:14:26 -0800 (PST)
Message-ID: <e41547a0-9140-492f-b33c-554095abb101_at_n10g2000vbg.googlegroups.com>
On Dec 7, 12:00 am, onedbguru <onedbg..._at_yahoo.com> wrote:
> On Dec 6, 2:34 pm, Walt <walt_ask..._at_yahoo.com> wrote:
>
>
>
>
>
>
>
>
>
> > On 12/5/2011 2:58 PM, mhoys wrote:
>
> > > On Dec 5, 5:51 pm, Walt <walt_ask..._at_yahoo.com> wrote:
> > >> Hi,
>
> > >> For performance reasons, we replicate a small subset of a remote
> > >> database on our Oracle instance (10.2 W2k3). Currently the remote
> > >> database is Oracle and the replication is happening smoothly (and has
> > >> for a decade or so).
>
> > >> We just found out that the remote data is being migrated to a SQL Server
> > >> database, so we'll need to find a different way to do the replication.
>
> > > SQL Server used to have DTS (Data Transformation Services) - I'm not
> > > sure it's still called this way. Using DTS it was fairly easy to
> > > configure and schedule data transfer between SQL server and Oracle
> > > tables using OLE DB. You only needed to install the Oracle Net client
> > > software on the SQL Server box and define a Oracle Net alias, and then
> > > use the DTS wizard to set up the data transfer.
>
> > I guess I should have added that we don't "own" the SQL Server database
> > - we've been given an account and granted select privileges on certain
> > tables, but no more. Getting the admins of the SQL Server database to
> > install/configure anything is probably a non-starter. So, whatever
> > solution we implement needs to be at the Oracle end only (where we can
> > install & configure pretty much anything we want)
>
> > //Walt
>
> As has been stated, I found that pushing to Oracle from SQLServer via
> DTS to be a much more viable option, but, given your description, you
> may need to use heterogeneous services. Here is a good place to
> start: http://docs.oracle.com/cd/B19306_01/server.102/b14232/gencon.htm
>
> A quick google search will turn up some examples of how to configure
> heterogeneous services to pull data from MSS.
Date: Wed, 7 Dec 2011 00:14:26 -0800 (PST)
Message-ID: <e41547a0-9140-492f-b33c-554095abb101_at_n10g2000vbg.googlegroups.com>
On Dec 7, 12:00 am, onedbguru <onedbg..._at_yahoo.com> wrote:
> On Dec 6, 2:34 pm, Walt <walt_ask..._at_yahoo.com> wrote:
>
>
>
>
>
>
>
>
>
> > On 12/5/2011 2:58 PM, mhoys wrote:
>
> > > On Dec 5, 5:51 pm, Walt <walt_ask..._at_yahoo.com> wrote:
> > >> Hi,
>
> > >> For performance reasons, we replicate a small subset of a remote
> > >> database on our Oracle instance (10.2 W2k3). Currently the remote
> > >> database is Oracle and the replication is happening smoothly (and has
> > >> for a decade or so).
>
> > >> We just found out that the remote data is being migrated to a SQL Server
> > >> database, so we'll need to find a different way to do the replication.
>
> > > SQL Server used to have DTS (Data Transformation Services) - I'm not
> > > sure it's still called this way. Using DTS it was fairly easy to
> > > configure and schedule data transfer between SQL server and Oracle
> > > tables using OLE DB. You only needed to install the Oracle Net client
> > > software on the SQL Server box and define a Oracle Net alias, and then
> > > use the DTS wizard to set up the data transfer.
>
> > I guess I should have added that we don't "own" the SQL Server database
> > - we've been given an account and granted select privileges on certain
> > tables, but no more. Getting the admins of the SQL Server database to
> > install/configure anything is probably a non-starter. So, whatever
> > solution we implement needs to be at the Oracle end only (where we can
> > install & configure pretty much anything we want)
>
> > //Walt
>
> As has been stated, I found that pushing to Oracle from SQLServer via
> DTS to be a much more viable option, but, given your description, you
> may need to use heterogeneous services. Here is a good place to
> start: http://docs.oracle.com/cd/B19306_01/server.102/b14232/gencon.htm
>
> A quick google search will turn up some examples of how to configure
> heterogeneous services to pull data from MSS.
If you take the Heterogeneous Services route you must keep in mind that you will not be able to do INSERT...SELECT with a dblink, you will have to do it row by row with a cursor.
HTH. Cheers.
Carlos. Received on Wed Dec 07 2011 - 02:14:26 CST