In an environment where database replication has been setup and replication has been configured to transition automatically. What would be the recommended way of switching applications from the primary database to the secondary one after the transition?
If you are trying to automate the application failover you can use an ON STOP block in your -p procedure. When the original source db disconnects it should raise the STOP condition and re-run the initial procedure. If you have some logic in that procedure to detect that and then re-connect to the new source you could have a relatively automated fail-over. I've only ever fiddled around with this in "proof of concept" code - but it seemed to work. I can imagine that it might not work for *every* connection but it ought to get a bunch of them.
There are some features being kicked around for future releases under the "five nines" moniker to make this even more seamless but, SFAIK, they are not yet available.
Thank you Tom!