I've got a database from a third party. They provide something to update the database data weekly. The tool is fairly old and uses ODBC. Updates may either be incremental or can remove all database data then recreate the information. The update may take several hrs. To be able to have high availability, it had been recommended to possess 2 SQL databases, and store a "active database" establishing another database to find out which of these two databases programs should use (as the other might be being up-to-date).
One problem we're encountering is: How you can do reference the active database in saved methods in other databases?
Is the best approach? It is possible to simple, possibly-infrastructure-based approach? (If this is published on ServerFault?)
Note: Databases are read-only aside from the update tool.
When the databases take presctiption different servers, you may create an alias for that server that will redirect towards the other server in SQL Server Configuration Manager. Under SQLNative Client 10. Configuration (or 9. if you are in SQL Server 2005) you can include a brand new alias.
Otherwise, you could relabel the databases using sp_dbrename so thata the consumer programs will always be using database1 when you are upgrading database2.
If you wish to use different databases in the saved procedure you can either have to:
Duplicate all of the calls. Ugly. You'd finish with many different:
if @firstDatabase=1 select * from database1..ExampleTable where ... else select * from database2..ExampleTable where ...
Use dynamic queries. Less ugly:
set @sqlQuery='select * from '+@currentDatabase+'..ExampleTable where...' exec sp_executesql @sqlQuery
The truth is that that neither option would be perfect...
I'd go ahead and take approach of getting the saved methods both in databases with a few kind of automatic trigger to update the saved methods within the other database if your saved procedure is transformed.