Scenario: We want to use SQL Server 2005 Standard's version of DB mirroring along with a witness server in an Active Directory domain environment. The database is fed from a 3rd party app server that cannot be modified apart from the DB connection string.
Two questions related to this:
- How can we make the failure somewhat invisible (or at least, require less manual work) to our 3rd party app server spending as little money as possible? My thinking is set up DNS "sqlserver.ourdomain" that points to the primary and use that in the connection string. Then should it fail, we change DNS to point to the mirror and re-start the app server. Not invisible or automatic but is cheap and seems like it ought to work.
- How do we push transactions from the secondary back to the primary after failure and re-promote the primary? This process must take into account "undoing" the failover mode in #1. Obviously if we go with the DNS option above we'd re-point DNS to the primary but how do I get the system back as before -- with the primary up to date from the mirror and the mirror demoted back to mirror?