Sql-server – SQL Server – redirect synonym without downtime

sql serversynonyms

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:

  1. Restore the updated shared database as shared_temp
  2. Redirect all synonyms in application databases to shared_temp
  3. Restore the updated shared database onto the "main" shared database
  4. Redirect synonyms back to shared
  5. Drop shared_temp

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.

Best Answer

Answer left in comments by Dan Guzman

DROP/CREATE all the synonyms within a transaction in steps 2 and 4. There may be some (hopefully) short-term blocking, but no downtime.