I have multiple application databases that use synonyms point to a shared database with immutable data that needs to be updated periodically by restoring an external backup. We now have a new requirement where we can no longer take the application down for the duration of the update, so the plan to update the database is to:
- Restore the updated shared database as
- Redirect all synonyms in application databases to
- Restore the updated shared database onto the "main"
- Redirect synonyms back to
Since there doesn't seem to be an
alter synonym statement, how would I perform these synonym redirects without a race condition where queries performed between
drop synonym and
create synonym fail to reference the shared database?
Is there any way to lock the synonyms and allow them to be recreated during the lock?
Alternatively is there a way to lock the whole database (or schema) in a way that incoming queries will be queued rather than rejected like in single user mode?
If all else fails, is there a reliable way to update the contents of all tables in a database from another without slowing down queries? Note that there are internal references in the data. Existing references are guaranteed to remain valid with the new data. As long as we don't downgrade to a previous version it should be safe.
Modifying statements that reference these synonyms is not an option.
Minimum SQL Server version is 2008 R2, although if newer versions make this possible then upgrading is an option.
We don't really care how long the process takes, just that there is no downtime. Dropping and renaming is not atomic and likely not lockable, otherwise that would be an option.