Re: replication / caching SQL Server data on Oracle
From: onedbguru <onedbguru_at_yahoo.com>
Date: Tue, 6 Dec 2011 15:00:21 -0800 (PST)
Message-ID: <96ee49af-b64c-4d1d-9673-edff93279917_at_i6g2000vbe.googlegroups.com>
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
Date: Tue, 6 Dec 2011 15:00:21 -0800 (PST)
Message-ID: <96ee49af-b64c-4d1d-9673-edff93279917_at_i6g2000vbe.googlegroups.com>
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. Received on Tue Dec 06 2011 - 17:00:21 CST