This is a good question. There’s a few ways to address it:
- We could configure two different connection strings and on failover, the alternative is used (by any arbitrary application mechanism)
- For mirroring, we can use the Failover Partner= element in the connection string – but this is transactional replication being used for DR, and failover should be manual
- We could set the instance names to be the same – however in this case it was too late since changing the instance name cleanly in SQL Server is practically impossible and breaks all sorts of things
None of these solutions was suitable. However, on each server, there was only one named instance and no default instance. What can we do?
Read on for a fourth solution.