Problem solve Get help with specific problems with your technologies, process and projects.

Setting up automatic switchover between SQL Server instances

In the event that your primary SQL Server 2000 Standard Edition box has a meltdown, is it possible to kill replication on an identical, secondary box to make that the new primary? Contributors Adam Machanic and Hilary Cotter offer their advice.

We have two identical boxes, both running SQL Server 2000 Standard Edition with Service Pack 4 on Windows 2003. We are using transactional replication to keep these our application database on the primary and secondary instances 'in synch.'

If we have a primary meltdown, we'd like to kill replication on the secondary and make the secondary the new primary. We have about 200-300 Visual Fox Pro 9.0 users who should now be directed to the new primary instance of SQL Server. What I'm wondering about is the least painful way to make this happen.

Our instances of SQL Server are named instances ... in other words, we did not go with the MachineName = SQL Server InstanceName default. Let's assume our Instance names are Machine1Primary and Machine2Secondary and our Primary has a meltdown. What I would like to do is have our 200-300 users 'magically' point to the Machine2Secondary instance of SQL Server without having to change the client connect strings for several hundred users. I was hoping the 'magic' involves some SQL Server stuff that I'm just not aware of.

As a follow up, is there a way to have the secondary instance not be visible? Since it's participating in replication as the subscriber, the database cannot be read-only because the replication function needs to write to it. I'd like to ensure that nobody accidentally or purposefully got connected to it.

As I am not an expert when it comes to SQL Server high availability, I consulted my friend Hilary Cotter (http://www.nwsu.com), master of all things replication-related.

Hilary noted that replication cannot help with the automatic failover. It can only provide mirroring. In his opinion, you'll "have to either come up with a code solution to redirect to the secondary or somehow update the DNS cache so that all clients are redirected to the secondary. Basically, you need to have some sort of geospatial load balancing which virtualizes the IP. The code solution would involve a checking to make sure the database is online each time any data access occurs and if it is not, it will try the secondary."

I should also note that what you're describing is exactly what SQL Server 2005's database mirroring feature provides. If you can upgrade, you might want to start testing that as a possible solution. It will be available early next year, so you won't have to wait too long to roll it into production.

Dig Deeper on Microsoft SQL Server 2000

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.