主页 / dba / 问题 / 208530
Accepted
Elaskanator
Asked: 2018-06-02 12:07:33 +0800 CST

SQL Server - 将不可为空的列添加到现有表 - SSDT Publishing

  • 772

由于业务逻辑，我们需要在表中添加一个新列，以确保始终填充该列。因此，应将其添加到表中NOT NULL。与之前解释如何手动执行此操作的问题不同，这需要由 SSDT 发布管理。

由于一些认识，我一直在为这个听起来很简单的任务撞墙一段时间：

  1. 默认值不合适，它不能是计算列。也许它是一个外键列，但对于其他列，我们不能使用像 0 或 -1 这样的假值，因为这些值可能有意义（例如数字数据）。
  2. 在预部署脚本中添加列将在第二次自动尝试创建同一列时使发布失败（即使预部署脚本被编写为幂等的）（这真的很严重，否则我可以想一个简单的解决方案）
  3. 在部署后脚本中将列更改为 NOT NULL 将在每次 SSDT 架构刷新发生时恢复（因此至少我们的代码库将在源代码控制和服务器上的实际内容之间不匹配）
  4. 现在将列添加为可为空以在将来更改为 NOT NULL 不适用于源代码控制中的多个分支/分支，因为目标系统在下次升级时不一定都具有相同状态的表（无论如何，这并不是一个好方法IMO）

听别人说的做法是直接更新表定义（这样架构刷新是一致的），写一个预部署脚本，将表的全部内容移动到一个临时表中，包含新的列填充逻辑，然后移动后部署脚本中的行。不过，这似乎很冒险，并且当它检测到一个 NOT NULL 列被添加到具有现有数据的表中时（因为验证在预部署脚本之前运行），它仍然会惹恼发布预览。

我应该如何添加一个新的、不可为空的列而不冒孤立数据的风险，或者在每次发布时使用本来就有风险的冗长迁移脚本来回移动数据？

谢谢。

sql-server scripting

1 个回答

  1. Best Answer

    我将分享我过去是如何做到这一点的。它旨在解决您在第二点中调用的预部署脚本的特定限制：

    在预部署脚本中添加列将在第二次自动尝试创建同一列时使发布失败（即使预部署脚本被编写为幂等的）

    为什么预部署脚本对此不起作用

    当您部署 SSDT 项目时，它将事物拼接在一起的方式是这样的（有点简化，但一般来说）：

    1. 在源（dacpac 文件）和目标（数据库）之间进行“模式比较”
    2. 根据比较结果生成部署脚本
    3. 处理 dacpac 中的任何预部署脚本（进行令牌替换等）并将内容插入到部署脚本的开头
    4. 对部署后脚本执行相同操作，附加到部署脚本的末尾

    当 dacpac 中存在新列而不是目标数据库中时，步骤 #2 将生成添加该列的代码。因此，如果预部署脚本添加此列，则脚本的主要部分将失败（因为它假定该列不存在，基于步骤#1 中的模式比较结果）

    解决方案：预 SSDT 脚本

    Martin Smith 在评论中提到了这个选项，这是迄今为止最适合我的解决方案：

    我们在部署管道中使用预模型脚本。这不是 SSDT 的一部分，而是在 dacfx 发布之前运行的一个步骤。因此，在这种情况下，预模型脚本可以添加具有所需值的列并使其不为空，并且在发布发生时它已经处于 SSDT 预期的状态，因此它没有任何事情要做。我还没有找到预部署脚本的用途。——马丁·史密斯 6 月 1 日 21:45

    实施此解决方案的步骤通常是：

    1. 在 SSDT 项目中创建一个脚本来保存您的“pre-SSDT”T-SQL 代码
      • 根据您的部署过程的工作方式，这些文件中的代码可能应该是幂等的
    2. 确保将此脚本设置为“Build Action=None”和“Copy to Output Directory=Copy always”
      • “总是复制”选项特别重要，因为部署过程需要能够在您的部署工件中找到此脚本
    3. 在您的部署过程中，在 SSDT 模式比较发生之前找到并运行此脚本（或多个脚本）
    4. 成功执行该脚本后，您可以像往常一样使用 DacServices / DacFx / 其他工具来完成部署

    最后，这允许您使用任何您喜欢的自定义代码添加列，使用复杂的业务逻辑填充，在 pre-SSDT 脚本中。

    您还在 SSDT 项目中添加列定义（因此源代码控制仍然与数据库的真实生活状态相匹配）。但是当模式比较运行时，它看不到与该列相关的任何更改（因为您已经部署了它）。

    pre-SSDT 的其他用途

    在测试部署时，我经常发现 SSDT 在完全没有必要的情况下执行“表重建”操作*。这是使用更新的模式创建新表的地方，所有数据都被复制到该表，旧表被删除，新表被重命名以替换旧表。

    如果表很大，这可能会导致大量事务日志文件增长和其他问题。如果我注意到架构更改导致了这种情况，我将自己在 SSDT 之前进行更改（这通常是一个简单的 ALTER TABLE语句）并避免重建表。

    这是一个好主意吗？

    我认同。如果您阅读了 Alex Yates 的《批判两种不同的数据库交付方法：迁移与状态》，这实质上是将这两种方法结合起来。SSDT 是基于状态的，但我们合并了一个迁移步骤（在 SSDT 之前）来处理一些 SSDT 无法以一般方式处理的更复杂的场景。

    在编写此答案时进行一些搜索，一旦您知道要搜索什么，这实际上是 SSDT 用户社区中讨论的一种非常常见的方法。我见过它叫：

    • 预比较
    • 预模型
    • 前 DAC
    • SSDT前

    等等。这是一篇很棒的文章，涵盖了我上面提到的很多要点：

    SSDT 的预比较和预部署脚本

    还有一个来自 Red Gate（在#4 – 从系统类型更改为用户定义类型部分）也将其称为预比较：

    如何修复十个 SSDT 部署障碍，无论是否使用 ReadyRoll

    那么预部署脚本有什么意义呢？

    Martin 指出他没有发现“预部署脚本有多大用处”。我也有同样的感觉。但是在某些情况下它们可能很有用。

    一位同事向我指出的一个例子是将一些数据存储在一个临时表中，以便在部署后脚本中使用（例如，您要将一列从一个表移动到另一个表）。

    *表重建看起来像这样，这很可怕，对吧？ 

    GO
PRINT N'Starting rebuilding table [dbo].[MyTable]...';


GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_MyTable] (
    [Id] BIGINT IDENTITY (1, 1) NOT NULL,
    -- etc, other columns
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[MyTable])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_MyTable] ON;
        INSERT INTO [dbo].[tmp_ms_xx_MyTable] ([Id], ...)
        SELECT   [Id],
                 -- etc, other columns
        FROM     [dbo].[MyTable]
        ORDER BY [Id] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_MyTable] OFF;
    END

DROP TABLE [dbo].[MyTable];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_MyTable]', N'MyTable';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    • 18

相关问题