Vikram Kamath

October 27, 2005

Happy Birthday Blog

Filed under: General — Vikram @ 22:34

As the title suggests, its been one year since I started blogging. Thank you all readers.

After a late start to the day, due to train delays at Reading Station, a tiring football game after office and some more office work from home, I am exhausted now and need to fall on that bed in front of me.


October 7, 2005

New Entry

Filed under: General — Vikram @ 19:49

There is a new entry to the list of people I know. Its Tom Medhurst!!! Welcome Tom to my list. 😛 Enjoy!

Microsoft© SQL Server 2000 Replication Bug

Filed under: SQL Server — Vikram @ 13:30

About three weeks back I came across a problem with Microsoft© SQL Server 2000 replication. This problem was specifically found in the Transactional replication. The details of SQL Server 2000 Replication is out of scope in this post and can be found on the Microsoft website.

Since this problem turned out to be a bug in SQL Server 2000 (SQL2K), I thought it would be worthwhile to blog about it so that any of you readers may benefit out of it. Before starting off with with the details I assume that you readers have knowledge of SQL2K Transactional replication.

About three weeks back I was given the task to set up replication for one of our clients, since the existing set up had broken down due to a server crash. It was the subscriber server that had crashed. We had tried to build up replication as normal, but it seemed to failing during the Snapshot initialisation of data at the subscriber. This was because, the system kept running out of disc space when the snapshot files for a particular Publication were being copied on to the disk as the publisher database was way too large for the snapshot (initialisation) process. As a result, we had to come up with another solution. Suggestions were made to have additional disks or to create separate Publications for the huge tables and so one. But one of my colleagues (who does not have a web presence yet) suggested the option using a back up of the database, restoring it on to the subscriber and setting up the replication by allowing the Subscription NOT to initialize the data on the subscriber. So, out of the three it was decided to use the third option, the backup. I was given the task to create scripts for the same.

So, I started on with a test dataset to make life easier. Before jumping into the task, I thought I would do some Google search on the same and to my luck I found this article from Microsoft© that said “HOW TO: Manually Synchronize Replication Subscriptions by Using Backup or Restore”. I went through this article and realized this was exactly what I wanted to do. To add to that I did not know about the scripts that needed to be generated manually on the Publisher database and applied on to the Subscriber database. So this article was very helpful in the sense that I may have run into other problems if I had missed this article. For example I did not know that we had to use the backup of a “Published” database and not just any backup, or the creation of scripts etc..

So, I started off by following exactly what the article said.

I Published my database first. Then I took a full back up of the published database and restored it on the subscriber server. Then I created my Subscriptions where I selected not to deliver the data and schema. I then changed the Distribution Agents schedule to run once. I used option of Queued Updating Subscriber.

Once this was done, I ran sp_scriptpublicationcustomprocs for each of my publications on the Publisher database and executed the generated scripts on to the Subscriber database. Since I had a Queued Updating subscriber, I also ran sp_script_synctran_commands for each of my publication on the Publisher database and executed the generated code on to the Subscriber database successfully. I did run the Distribution Agents once (before applying the scripts) to create the table MSsubscription_agents. As the last step I changed the Distribution Agent properties to run continuously by adding -Continuos to the command line.

It went very smoothly and the transactional replication was set on our test machines using the backup of a Published database. As a test, I changed the data in a particular column in a table on the publisher. The result, it got replicated as expected. Since we had used Queued Updating subscriber, I had to test a data change on the Subscriber database to see whether it got queued by the Queue Reader and updated on the Publisher database. So, I changed the data in particular table on the Subscriber database. This time the result was not as expected. My Queue Reader failed with an error as follows:

Server: PublisherServerName, Database MyDatabaseName: ODBC Error:Could not find stored procedure ”upd.sp_MSsync_upd_MyArticleName_1”.

As the error suggests the Queue Reader was trying to execute the stored procedure sp_MSsync_upd_MyArticleName_1 on the Publisher database (which did existed in the database) with a wrong user “upd”. I opened every possible stored procedure and trigger that to part in that update statement but failed to understand why the Queue Reader was trying to execute the stored procedure in the context of a non existed user “upd”.

I then set up replication as normal, i.e. not taking the back up of the published database. Whilst pushing the subscriptions, I selected the option to initialise the data and schema on the subscriber. After setting up the replication, I did the same data modification tests and everything went on fine, without any errors with the Queue reader.

This particular problem was happening only when subscriptions were created with the option not to initialize data at the subscriber.

After much trial and analysis, we decided to put this problem up to Microsoft©. And after two weeks of continuous interaction with Microsoft support team and their extensive tests and debugging, they realised that the Queue Reader version was being set according to what they said to a PRE SP3 version and was the reason why the stored procedure was being executed in the context of the user “upd”. This version information is calculated using the data in the queue_id in the table MSsubscription_agents. The Pre SP3 value in the table for the column was “mssqlqueue” and Microsoft had changed it to “mssqlqueueev2” in their Service Pack 4 for SQL Server 2000. So, they finally accepted it as their bug and gave a work around until they fix it in their subsequent Service Pack if any.

The work around is as follows:

Once you have set up the replication using the backup and restore method described in the article you would need to do the following:

1. Stop the Queue Reader and the Distribution Agents

2. Execute the following piece of SQL on SQL Query Analyzer© on the Subscriber database:

sp_configure ‘allow update’,1
reconfigure with override
update dbo.MSsubscription_agents set queue_id = ‘mssqlqueuev2’ where queue_id = ‘mssqlqueue’
sp_configure ‘allow update’,0
reconfigure with override

3. Start the Queue Reader and the Distribution Agents.

Simple!! isn’t it? But it took two weeks for Microsoft© support team to come out with it.

At the end it was indeed a great exercise for me to go little more in depth into SQL Server 2000 Transactional replication.

More later!!!!

Blog at