Sql-server – Hot Swapping Sql Server Databases

sql server

I manage a database in SQL Server for a business's customer transaction. The data is updated four times daily from a very complex Oracle database. The SQL Server database is then connected to by a .Net application that provides visualization of the data. This application only reads the SQL Server data, there is no writing back to the database.

At this point the data updates are taking 15 to 20 minutes. This locks the application out of the database. I am looking for a way to update the data without overly inconveniencing the users.

I was wondering about doing the updates into a second database, then swapping which database the application talks to. This of course has to be done entirely on the server side as I don't want to have to make the users log back in.

If I do the updates into a second database, if the DB engine is hitting hard at the updated database, does this greatly affect the performance of users looking at the live database.

I would appreciate suggestions as to how best do this (or some other) approach to minimize downtime for the users.

Best Answer

Introduce a third database and use synonyms. For each table (I'll call it dbo.MyTable for this example), it looks something like this:

  1. Point the synonym in the "shell" database to the version in the "live" database. So create synonym shell.dbo.MyTable for live.dbo.MyTable.
  2. Do your most nasty data load in the "load" database
  3. When you're ready to cut over, drop and recreate the synonym from step 1: drop synonym shell.dbo.MyTable; create synonym shell.dbo.MyTable for load.dbo.MyTable

You've effectively swapped your live and load databases and the application is none the wiser.

Related Question