I have a SQL server database with data supplied from a 3rd party. They supply a tool to update the database data weekly. The tool is pretty old and uses ODBC. The update can take several hours. In order to have high availability, it was suggested to have 2 SQL databases, and store a "active database" setting in another database to determine which of the two databases applications should use (while the other could be being updated).
One issue we are running into is: How to do reference the active database in stored procedures in other databases?
Is this the right approach, or is there a better way?
Notes: Updates can either be incremental or can delete all database data then recreate the data. Databases are currently read-only besides the update tool.