A vendor includes a data database (read only) that will get delivered to us via dvd each week. Their upgrade script detaches the present copy from the database, overwrites the MDF and LDF, drops all of the customers and recreates the things they think proper security ought to be. It is possible to way will be able to just synchornize the information if you don't take the database offline? This can be a 24/7 facility that triggers fifteen minutes of down time throughout the updates.
Auxilary Information: The database has ~50 tables having a total size 400 Megabytes. The particular quantity of transformed information is around 400kb. Server is running Server 2008 with SQL Server Enterprise Edition 2008.
Educate yourself on Red-colored Gate Data Compare
This can produce a script of variations for you personally that you could affect the present database.
This is able to instantly synchronize your computer data
You'll have to load the incoming database to some server with this operation.
Something you should do is to possess two databases DB_A and DB_B once they give back the brand new DB you put in it and replace DB_B. Meanwhile all of your customers are utilizing DB_A. Then relabel the DB_A to DB_C and relabel DB_B to DB_A. Which will reduce the down time to just about . Or just alter the link with point from DB_A to DB_B when the DB is prepared.
Can't believe nobody has pointed out this process.
Hug: You can load the restore (or attach) the DB to a different catalogue after which reconfigure your applications (for instance via a ODBC DSN) to utilize the brand new DB -- when you the rollover is performed you are able to remove that old one. This process is known as the "Moving upgrade" and it is used everywhere for twenty-fourOr7 sites.
You can write your personal program that connects to both vendor provided database as well as your own (possibly a DTS, or perhaps a C# application) and it has some intelligence about which data ought to be moved/migrated in the vendor DB to your own.
You can load the db like a separate database after which make use of a utility like SQL Data Compare to diff the information variations and produce a script.
This ought to be a backup/restore operation. Remove/reattach is fast and simple but clearly takes the database offline.
Alternatively, they are able to provide diff scripts to update data and schema changes they require. Run the scripts (essentially a lot of create, alter, adds and drops for schema changes, together with card inserts for brand new data) around the active database to create it current. It has the benefit the scripts can be simply version controlled.
You can synchronize the information utilizing a tool like Red-colored-Gate Data Compare.
You should use SSIS to produce a data import/update process. One question I'd have before doing this is what will be the problem if throughout a short time table has new data and table b is not up-to-date? Must all of the data remain in synch? Follow Jose Chama's advice should you cant have things from synch for one minute.