Thursday, January 8, 2009

So you can't configure clustering in a Windows Server 2003?

You might encounter this error when adding a node in a Windows Server 2003 Cluster

Status: 0x800713de The quorum disk could not be located by the cluster service.

This occurs even if you have created shared disks on a node you wanted to add in a cluster but created it on the same SCSI bus as the disk that holds the operating system. Windows does not allow you to do that since if you are going to failover to the other node, the shared disk should be flexible enough to move. This is a common mistake when trying to create a cluster in a virtualized environment, assigning shared disks on the same SCSI bus as that of the disk that holds the operating system. Check out this blog entry on creating clustered VMs in VMWare Workstation 6. It doesn't matter whether you are using VMWare, Microsoft Virtual Server or Hyper-V; if you don't take note of this, you'll end up spending a lot of time troubleshooting a very simple configuration issue

Wednesday, January 7, 2009

Fixing PRIMARY KEY constraint issues in SQL Server 2005 Replication

I must admit that I had to stay away from replication for quite a while since most of the work I've done in my previous job has a lot to do with disaster recovery. Nonetheless, I'm presented with new challenges every single day which I need to face. I got notified for an error regarding a transactional replication issue between two SQL Server 2005 instances. The error went something like this

Replication-Replication Distribution Subsystem: agent INSTANCE-db-Publication-SUBSCRIBER_INSTANCE-21 failed. Violation of PRIMARY KEY constraint 'PK_Whatever'. Cannot insert duplicate key in object 'dbo.table1'

The error message simply says that replication between the publisher and the subscriber could not push thru due to an existing record in the subscription that is causing a violation of the PRIMARY KEY constraint. Either a record is manually inserted in the subscriber or its just an annoying application which is not properly configured. Here's how you can fix this

  1. Open Replication Monitor and check for the publication that is causing the error. You should see some error messages in the details that display something about the transaction sequence number, publisher id and command id values
  2. Next, you retrieve the command associated with the transaction sequence number using the system stored procedure sp_browsereplcmds. This will return a result set of the replicated commands stored in the Distribution database at the Distributor. The query should look something like this

  3. EXEC Distribution..sp_browsereplcmds @xact_seqno_start = '0x000B5103000043B7000700000000' ,@xact_seqno_end = '0x000B5103000043B7000700000000',@command_id =2 , @publisher_database_id = 1
    This will give you an idea on the possible INSERT/UPDATE/DELETE statement that is inside this transaction. Based on the error message, we are looking at either an INSERT or an UPDATE statement.
  4. Next, run a SELECT query on the subscriber instance against the table specified in the subscription passing the values you retrieved from the sp_browsereplcmds system stored procedure.
  5. If the record already exists, simply delete the record since it will be written over by the replication (of course, this is not always the case and would depend on your business requirements)

This is just an overview of what you can do when you see such errors in replication. There are a few more articles and tips to help you get by from this website by Paul Ibison