Question Locked

SQL Server 2000 Replication

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I'm looking at building a reporting server to help take some load off one our production SQL servers. Does the replication setup make any changes to the tables or databases that are going to be replicated? Are columns added to the tables replicated? How does SQL server know which tables, rows, columns, etc to replicate? Thanks for any answers.

2 replies

by tony hopkinson

Replication works by selecting tables for publication on the sourse server. Changes are distributed via the transaction log (basically)
Some changes are possible there's a NOT FOR REPLICATION directive but that's more to break referential integerity if you only publish one side of a relationship.

If you are doing reporting though, how current does the data have to be? If you can get away with an up to yesterday type 'snapshot' solution DTS would be a better solution, you can do all sorts of tricks with it, including building a database optimised for reporting. You need a suitable slot to run the update though. Note DTS is on it's way out replaced by 2005's SSIS, so a complex solution will give you problems as the conversion wizard is not that good if you plan to upgrade at some point.

If your design is applicable (some sort of changed since X type manouvre you could update more regulary with smaller batches.

Another posiblity is to add triggers to populate your own tranaction log table

Created X at time
Udated Y at time
etc
then write someting else to suck up the relevant data and punch it across to another database.

I used to use the above method to distribute from SQL server to a HP3000 Image database and a VMS memory mapped file, worked very well, though there was a fair bit of application code to write at either end.

HTHs

This discussion is closed to new replies.