Replace SQL Server Database

Question

A vendor has a data database (read only) that gets sent to us via dvd every week. Their upgrade script detaches the existing copy of the database, overwrites the MDF and LDF, drops all the users and recreates what they think proper security should be. Is there a way that I can just synchornize the data without taking the database offline? This is a 24/7 facility that causes 15 minutes of downtime during the updates.

Auxilary Information: The database has ~50 tables with a total size of 400 MB. The actual amount of changed data is somewhere around 400kb. Server is running Server 2008 with SQL Server Enterprise Edition 2008.

Answer 1

You could write your own program that connects to both the vendor supplied database and your own (possibly a DTS, or a C# app) and has some intelligence about which data should be moved/migrated from the vendor DB into your own.

Answer 2

Read up on Red Gate Data Compare

http://www.red-gate.com/products/SQL_Data_Compare/index.htm

This will generate a script of differences for you that you can apply to the existing database.

This also has the ability to automatically synchronize your data

You will have to load the incoming database to a server for this operation.

Answer 3

You could load the db as a separate database and then use a utility like SQL Data Compare to diff the data differences and generate a script.

Answer 4

This should be a backup/restore operation. Detach/reattach is quick and easy but obviously takes the database offline.

Alternatively, they can provide diff scripts to update any data and schema changes that they require. Run the scripts (basically a bunch of create, alter, adds and drops for schema changes, along with inserts for new data) on the active database to bring it up to date. This has the advantage that the scripts can be easily version controlled.

Answer 5

You could synchronize the data using a tool like Red-Gate Data Compare.

Answer 6

Something you can do is to have two databases DB_A and DB_B when they send you the new DB you install it and replace DB_B. In the meantime all your users are using DB_A. Then rename the DB_A to DB_C and rename DB_B to DB_A. That will decrease the downtime to almost 0. Or you can just change the connection to point from DB_A to DB_B once the DB is ready.

Answer 7

Can't believe no one has mentioned this method.

KISS: You could load the restore (or attach) the DB to a new catalogue and then reconfigure your apps (for example through a ODBC DSN) to work with the new DB -- once you the rollover is done you can detach the old one. This method is called the "Rolling upgrade" and is used all over the place for 24/7 sites.

Answer 8

You can use SSIS to create a data import/update process. One question I would have before doing this would be what would be the issue if during a short period of time table has new data and table b has not been updated? Must all the data stay in synch? Follow Jose Chama's advice if you can;t have things out of synch even for a minute.