Re: replication / caching SQL Server data on Oracle
From: CarlosAL <miotromailcarlos_at_netscape.net>
Date: Wed, 7 Dec 2011 00:43:00 -0800 (PST)
Message-ID: <9dde27cd-11fa-4703-8969-931321d914fa_at_gl2g2000vbb.googlegroups.com>
On Dec 7, 9:14 am, CarlosAL <miotromailcar..._at_netscape.net> wrote:
> 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.
Date: Wed, 7 Dec 2011 00:43:00 -0800 (PST)
Message-ID: <9dde27cd-11fa-4703-8969-931321d914fa_at_gl2g2000vbb.googlegroups.com>
On Dec 7, 9:14 am, CarlosAL <miotromailcar..._at_netscape.net> wrote:
> 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.
Oooops!
Sorry, I misread the question.
This was only if you want to insert into SQLServer from Oracle an not vice-versa.
Cheers.
Carlos. Received on Wed Dec 07 2011 - 02:43:00 CST