QUESTION POSED ON: 29 November 2005
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.
|
