CODEWITHSUNDEEP
sql-server
Kevin Pluck
Kevin Pluck

Reputation: 681

Refresh a readonly database with minimal downtime

We have a legacy readonly sql-server price database supplied by a third party that gets refreshed once a month.

It's basically three very large look-up tables.

There are numerous applications that access this db.

Due to the nature of the prices they all change so diffing isn't useful.

What we would like to do is automate this currently manual task.

How can we replace the data while minimising downtime?

(Googling this sort of question came back with a lot of noise - apologies if it's been duplicated many times.)

Upvotes: 2

Views: 542

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280439

A couple of other options, that will have virtually zero intrusion (truncate / re-populate will still interfere with uptime considerably, depending on the time it takes to re-populate):

  1. Have two copies of the database, on the same server (say, db1 and db2). Then a central database that your application connects to, which just hosts views and/or synonyms (perhaps in stored procedures). Let's say db1 is currently active, at month end, you back up the refreshed production database, restore it with replace, recovery as db2, then change the views and synonyms to point to db2.

  2. Similarly, have two copies of the database, but with the same name and on different servers. Then follow the same process at refresh time, and when the new copy is ready, change DNS or the connection strings to point at the other server.

  3. Similar process but with two copies of each table in separate schemas (or partitioning and switching). I won't belabor this option too much, as I've written about it and posted a follow-up, but the basic premise is the same - you do all the work in a background place that doesn't affect the user, then you switch in the new data, which is a metadata operation and should be near instantaneous when it gets its turn in the sequence of transactions. At my previous job, we did this every x minutes (depending on the data) and it was never a problem.

Upvotes: 2

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

If most of the prices are changed and all prices are presented in a new file, may be the best way is to delete all tables with TRUNCATE. The to insert all data again from the new file. You can make that with script. Remark all needed data must be presented in the new import file.

Upvotes: 1

Related Questions