通过重命名旧表，然后填充新版本，将表停机时间降至最低? [英] 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. 创建一个永久表(构建版本；例如，tbl_build_Client)

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

重命名实时表(tbl_Client 被重命名为 tbl_Client_old)

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

将构建版本重命名为实时版本(tbl_build_Client 被重命名为 tbl_Client)

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

要重命名表，将使用 sp_rename.
http://msdn.microsoft.com/en-us/library/ms188351.aspx

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.

更新

试图清除 gbn 的回答和使用同义词的建议，这是一种合理的方法，还是我的某些部分严重错误?

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?

客户"的三个真实表:
1. dbo.build_Client
2. dbo.hold_Client
3. dbo.prev_Client

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

因为Client"是其他进程引用Client"数据的方式，所以默认同义词是

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

CREATE SYNONYM         Client  
FOR           dbo.hold_Client

然后采取这些步骤来刷新数据，同时保持不间断访问.
(1.a.) TRUNCATE dbo.prev_Client(它有昨天的数据)
(1.b.) INSERT INTO dbo.prev_Client 来自 dbo.build_Client 的记录，因为 dbo.build_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 新数据构建过程中的新数据构建
(2.c.) 更改同义词

(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 来自 dbo.build_Client
的记录(3.c.) 更改同义词

(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

推荐答案

使用间接方式避免直接操作表:

Use indirection to avoid manuipulating tables directly:

• 有 3 个表:Client1、Client2、Client3，包含所有索引、约束和触发器等
• 使用同义词隐藏真实表，例如Client、ClientOld、客户端加载
• 要生成新表，请截断/写入ClientToLoad"
• 然后在事务中删除并创建同义词，以便
  • Client -> ClientToLoad 是什么
  • ClientOld -> 什么是客户端
  • ClientToLoad -> ClientOld 是什么
  • 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

    您可以使用 SELECT base_object_name FROM sys.synonyms WHERE name = 'Client' 来计算当前的间接地址

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

    这适用于所有版本的 SQL Server:另一种方式是分区切换"，需要企业版

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

    这篇关于通过重命名旧表，然后填充新版本，将表停机时间降至最低?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！