CODEWITHSUNDEEP
sqlt-sqlsql-server-2008relational-database
mg1075
mg1075

Reputation: 18155

Keep table downtime to a minimum by renaming old table, then filling a new version?

I have a handful or so of permanent tables that need to be re-built on a nightly basis.

In order to keep these tables "live" for as long as possible, and also to offer the possibility of having a backup of just the previous day's data, another developer vaguely suggested taking a route similar to this when the nightly build happens:

  1. create a permanent table (a build version; e.g., tbl_build_Client)

  2. re-name the live table (tbl_Client gets re-named to tbl_Client_old)

  3. rename the build version to become the live version (tbl_build_Client gets re-named to tbl_Client)

To rename the tables, sp_rename would be in use.
http://msdn.microsoft.com/en-us/library/ms188351.aspx

Do you see any more efficient ways to go about this, or any serious pitfalls in the approach? Thanks in advance.

Update

Trying to flush out gbn's answer and recommendation to use synonyms, would this be a rational approach, or am I getting some part horribly wrong?

Three real tables for "Client":
1. dbo.build_Client
2. dbo.hold_Client
3. dbo.prev_Client

Because "Client" is how other procs reference the "Client" data, the default synonym is 

CREATE SYNONYM         Client  
FOR           dbo.hold_Client

Then take these steps to refresh data yet keep un-interrupted access.
(1.a.) TRUNCATE dbo.prev_Client (it had yesterday's data)
(1.b.) INSERT INTO dbo.prev_Client the records from dbo.build_Client, as dbo.build_Client still had yesterday's data

(2.a.) TRUNCATE dbo.build_Client
(2.b.) INSERT INTO dbo.build_Client the new data build from the new data build process
(2.c.) change the synonym

DROP SYNONYM           Client
CREATE SYNONYM         Client  
FOR          dbo.build_Client

(3.a.) TRUNCATE dbo.hold_Client
(3.b.) INSERT INTO dbo.hold_Client the records from dbo.build_Client
(3.c.) change the synonym

DROP SYNONYM          Client
CREATE SYNONYM        Client  
FOR          dbo.hold_Client

Upvotes: 5

Views: 4863

Answers (4)

gbn
gbn

Reputation: 432471

Use indirection to avoid manuipulating tables directly:

  • Have 3 tables: Client1, Client2, Client3 with all indexes, constraints and triggers etc
  • Use synonyms to hide the real table eg Client, ClientOld, ClientToLoad
  • To generate the new table, you truncate/write to "ClientToLoad"
  • Then you DROP and CREATE the synonyms in a transaction so that
    • Client -> what was ClientToLoad
    • ClientOld -> what was Client
    • ClientToLoad -> what was ClientOld

You can use SELECT base_object_name FROM sys.synonyms WHERE name = 'Client' to work out what the current indirection is

This works on all editions of SQL Server: the other way is "partition switching" which requires enterprise edition

Upvotes: 6

pabdulin
pabdulin

Reputation: 35245

Except of missing step 0. Drop tbl_Client_old if exists solutions seems fine especially if you run it in explicit transaction. There is no backup of any previous data however.

The other solution, without renames and drops, and which I personally would prefer is to:

  1. Copy all rows from tbl_Client to tbl_Client_old;
  2. Truncate tbl_Client.
  3. (Optional) Remove obsolete records from tbl_Client_old.

It's better in a way that you can control how much of the old data you can store in tbl_Client_old. Which solution will be faster depends on how much data is stored in tables and what indices in tables are.

Upvotes: 1

Alex_L
Alex_L

Reputation: 2654

if you use SQL Server 2008, why can't you try to use horisontal partitioning? All data contains in one table, but new and old data contains in separate partitions.

Upvotes: 0

Derek
Derek

Reputation: 23288

Some things to keep in mind:

  1. Replication - if you use replication, I don't believe you'll be able to easily implement this strategy
  2. Indexes - make sure that any indexes you have on the tables are carried over to your new/old tables as needed
  3. Logging - i don't remember whether or not sp_rename is fully logged, so you may want to test that in case you need to be able to rollback, etc.

Those are the possible drawbacks I can think of off the top of my head. It otherwise seems to be an effective way to handle the situation.

Upvotes: 1

Related Questions