SQL Server 2000 Replication

By smittyrt ·
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

You are asking for a book here. a summary

by Tony Hopkinson In reply to SQL Server 2000 Replicati ...

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
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.


Related Discussions

Related Forums