I have different heterogeneous databases (SQL Server as well as Oracle) which need to transfer their data to a central SQL Server database for reporting purposes. Central location needs to be kept up-to-date on a periodic basis, say every night we consoli
date data from different sources. There can be maximum 100 such source databases. Each source can have max 100KB of data.
Now I have to figure out which one is more suitable to my needs: replication or DTS? I am new to both of these approaches. I don't know whether DTS would even make a solution or not? So please advise what would be the better approach. Also I would appreci
ate further links/references that would help me move ahead with either of them.
Thanks
-megha
Megha,
if you select DTS , you'll have potentially a huge task ascertaining which
are the new records, which have been deleted and which have been updated.
So, using DTS practically limits you (in most circumstances) to taking the
complete table from each subscriber. Replication, on the other hand, has a
methodology which takes all this into account. I would recommend merge
replication with your reporting server as a central publisher. You need to
consider if the data is to be partitioned according to subscriber, or if
conflicts can arise and how to treat these conflicts, for which there are
several inbuilt resolution techniques. Have a look at BOL for further
information. I maintain a simple site (www.replicationanswers.com) and on
the links section there are some configuration walkthroughs. Unfortunately
there aren't yet any books to recommend, but keep an eye out for the
imminent release of Hilary Cotter's book.
HTH,
Paul Ibison
|||Unfortunately with SQL 2000 you can't replicate from Oracle to SQL Server
AFAIK. I have gone through Oracle's documentation and Oracle 8 and 9 and
don't see this option.
DTS sounds like the best option, although tracking changes (as Paul
mentions) will be difficult.
I did set something up in the past that did bulk inserts from Oracle to SQL
Server using vbscript which would so an insert select of the form
insert into SQLServer.Database.dbo.tableName
select * from OracleServer.Database.ServerName.SYSDBA.TableName where
PKOracle not in (select PKSQLServer SQLServer.dbo.tablename)
It might be simpler to use a linked server for this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"megha" <anonymous@.discussions.microsoft.com> wrote in message
news:A4C68146-4B9D-4136-8464-9E5271402E64@.microsoft.com...
> I have different heterogeneous databases (SQL Server as well as Oracle)
which need to transfer their data to a central SQL Server database for
reporting purposes. Central location needs to be kept up-to-date on a
periodic basis, say every night we consolidate data from different sources.
There can be maximum 100 such source databases. Each source can have max
100KB of data.
> Now I have to figure out which one is more suitable to my needs:
replication or DTS? I am new to both of these approaches. I don't know
whether DTS would even make a solution or not? So please advise what would
be the better approach. Also I would appreciate further links/references
that would help me move ahead with either of them.
> Thanks
> -megha
|||Thanks Paul for the feedback! I checked out your website and found that you work a lot with ASP.NET as well. I would like an advise here. How would web services fit in to my requirements here? Is there a way I can capture transactions on heterogenous data
base servers and then use web services to send them to a central server? Or is it better to go ahead with replication?
One more question: you suggested merge replication with central publisher. Why central publisher - I thought it would be central subscriber as I have many publishers and all data would need to come to one central server. Also, once arrived at central serv
er data might change but I don't want this data to propogate back to the publishers. Any changes coming from a publisher would overwrite the data at central location. Do I need to use merge replication or transactional replication is the solution? Please
explain.
|||Thank you Hilary for sharing your knowledge with me! I checked out the sample chapter of your upcoming book. There I have got a question for you. Are you saying that for SQL Server (any version) replication we can not have heterogeneous subscribers? I gue
ss thats correct and thats the reason you have mentioned DTS and Linked Servers.
But I did not quite follow the direction you pointed to. How can I use a linked server to an Oracle database that would simulate Oracle - SQL Server replication? Or how would DTS do that? OK I can use either of the two to access Oracle database but how do
I create/access the transaction log which I essentially need at central SQL Server - can you please explain more?
|||Megha,
you'll need to use replication for the SQL subscribers and DTS/linked
servers for Oracle. The data on the central reporting server can be
amalgamated into one table or more easily unioned using views. If you use
views and want to edit the data, you can partition them to enable edits or
use InsteadOf triggers. Webservices would only complicate things here, and
it'll be quite complex as it is :-)
The publisher/subscriber metaphor doesn't strictly apply to merge, and
centralized publishing is easier to manage/maintain.
You can implement unidirectional merge replication using the -EXCHANGETYPE
parameter. Transactional is possible, but merge is much simpler for your
needs.
HTH,
Paul Ibison
|||I had a mistake in my first question. I intend to ask - Are you saying that for SQL Server (any version) replication we can not have heterogeneous publishers?
|||Paul, I am still trying to understand: why central publishing would match my needs better. How I think about it is I do not need any data to flow from central location to different sources and so I would not need to publish anything on central side. Do I
understand it correct that whoever is changing the data can become a publisher and as central location's change of data do not need to go across it should not be a publisher? Am I missing on something here?
How unidirectional merge is simpler than transactional replication? Can you provide some facts? What I knew is that merge brings in additional complications to the process. I would like to know your reasons as I will be implementing it for the first time
and I do not have any convenience preferences so far.
You must have some insights into this, so please explain further.
|||Megha,
this is just my preference purely from the point of view of maintenance.
Remember, the publisher/subscriber metaphor doesn't strictly apply to merge,
meaning that either a publisher or a subscriber can change the data -
there's really no great distinction in this respect. There are some
differences eg the publisher wins conflicts by default, but in your case
this is probably not relevant.
If each remote computer is set up as a transactional publisher, this can be
a lot of maintenance. Each publisher typically is a publisher/distributer.
Each one has it's own set of alerts and jobs. All profile parameters
(timeouts, snapshot size etc), publication settings (duration of
transactions etc) are set on each remote machine separately. Also, backup
and restore for disaster recovery is also much more straightforward for
merge replication.
Regards,
Paul Ibison
|||OK now I can select central publishing topology for replication with unidirectional replication flowing from subscribers/publishers to central publisher. BUT even then I need to keep track of changes at subscribers/publishers so that it can bemerged at ce
ntral location. Won't I need then to define a distributor for each of these subscribers/publishers then? How can I get away with defining no publication settings (transaction duration as you quoted) on each one of them?
Can you explain it more or tell me if there is some book I can refer to in order to understand the merge replication more clearly? BOL does not detail on central publishing with unidirectional replication.
Thanks for your help!
