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.
Requires Free Membership to View
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.
This was first published in December 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation