Geek Answers Handbook

Reduce table downtime to a minimum by renaming the old table and then filling out the new version?

I have several or more persistent tables that need to be rebuilt on a nightly basis.

To make these tables “live” as long as possible, as well as offer the ability to back up only data from the previous day, another developer vaguely suggested using a route similar to this when nightly builds:

  • create a persistent table (assembly version, e.g. tbl_build_Client)

  • rename the current table (tbl_Client is renamed to tbl_Client_old)

  • rename the build version to become a live version (tbl_build_Client is renamed to tbl_Client)

To rename tables, sp_rename will be used.
http://msdn.microsoft.com/en-us/library/ms188351.aspx

Do you see more effective ways to do this, or any serious pitfalls in the approach? Thanks in advance.

Refresh

Trying to discard the gbn response and recommendation to use synonyms, would this be a rational approach, or am I getting some part terribly wrong?

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

Since "Client" is how other procs refer to "Client" data, the default synonym is

CREATE SYNONYM Client FOR dbo.hold_Client

Then follow these steps to update the data, but without interrupting access. (1.a.) TRUNCATE dbo.prev_Client (he had data yesterday)
(1.b.) INSERT INTO dbo.prev_Client records from dbo.build_Client, since dbo.build_Client still had data yesterday

(2.a.) TRUNCATE dbo.build_Client
(2.b.) INSERT INTO dbo.build_Client new data built from a new data collection 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 entries from dbo.build_Client
(3.c.) change the synonym

 DROP SYNONYM Client CREATE SYNONYM Client FOR dbo.hold_Client
+4
source share
4 answers

Use indirection to avoid creating tables directly:

  • Have 3 tables: Client1, Client2, Client3 with all indexes, restrictions and triggers, etc.
  • Use synonyms to hide the real table, e.g. Client, ClientOld, ClientToLoad
  • To create a new table, you crop / write to "ClientToLoad"
  • Then you DROP and CREATE synonyms in the transaction to
    • Client → what is ClientToLoad
    • ClientOld → what is a client
    • ClientToLoad → what is ClientOld

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

This works in all editions of SQL Server: another way is "partition switching," which requires an enterprise version

+4
source

Except for the missing steps 0. Drop tbl_Client_old if exists solutions look great, especially if you run it in an explicit transaction. However, a backup of any previous data does not exist.

Another solution, without renaming and drops, and which I personally would prefer:

  • Copy all lines from tbl_Client to tbl_Client_old;
  • Trim tbl_Client.
  • (Optional) Remove obsolete entries from tbl_Client_old.

This is better, so you can control how much old data you can save in tbl_Client_old. Which solution will be faster depends on how much data is stored in the tables and what indexes in the tables.

+1
source

Some things to keep in mind:

  • Replication - if you use replication, I do not believe that you can easily implement this strategy.
  • Indexes - make sure that any indexes you specify in the tables are migrated to your new / old tables if necessary
  • Logging - I don’t remember if sp_rename is fully registered, so you can test this if you need rollback, etc.

These are possible flaws that I can think of from my head. Otherwise, it will be an effective way to deal with the situation.

+1
source

if you are using SQL Server 2008, why can't you use horizontal partitioning? All data is contained in one table, but new and old data are contained in separate sections.

0
source

More articles:


All Articles