October 6, 2010

SQL Server 2000 Replication – Good one Microsoft

Filed under: Database — joel.cass @ 3:56 pm

One thing that I hate having to deal with, is replication in SQL Server 2000. It just seems like it’s half finished, and no-one bothered to think about what they’re doing when they wrote it. I’m not going to profess that I’m an expert in the area, I just think that it should have been done differently.

Recently I’ve had to set up replication going both ways between two servers. I had been recommended to stick to transactional replication, as it is being used on other databases set up similarly. One problem I have been having with transactional replication however, is that if a table is replicated both ways, the transactions related to the replication of data will be replicated, resulting in a horrible circular reference and before too long, full logs and no more disk space.

Furthermore, you will get random errors that occur anytime new data is inserted into a table on either database: “Cannot insert duplicate key row in object [blah]” or “Violation of [blah] constraint ‘[blah]‘. Cannot insert duplicate key in object ‘[blah]‘.”. Microsoft erroneously suggest that you add the term “-SkipErrors 2601;2627″ to the startup of the Distribution agent. Wrong. It should be “-SkipErrors 2601:2627″ – and, no error occurs on startup if the parameter is incorrect.

So, solving the full logs issue? You will need to stop both agents from running continuously, and schedule one or both of the transfers to happen every [x] minutes, otherwise the transactions will be replicated non-stop until the logs are full.

But the best solution for two-way replication would not be to bother at all with SQL Server transactional replication, at least for 2000. You could try merge replication, or set up a web service to allow data only to be written to a master db and replicated back to the child databases. Two-way replication is a bad idea.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment