如何将生产环境的字段类型从INT修改为BIGINT

介绍

改变数据类型是一个看起来很简单的事情，但是如果表非常大或者有最小停机时间的要求，又该如何处理那？这里我提供一个思路来解决这个问题。

背景

在一个常规SQL Server heath检查中，使用sp_blitz，我们最大的生产表之一引发了令人担忧的警报。保存客户订单信息的表的ID列是一个INT datatype，很快就将达到最大值。

这个表大约有500GB，有超过9亿行。根据在该表上每天的平均插入数，我估计未来八个月后，在这张表上的插入将会溢出。这是一个订单输入表，由于客户的活动，需要24小时的插入。一旦强行修改字段必然导致停机。

本文描述了我如何计划和执行从INT到BIGINT数据类型的更改。该技术在单独的SQL服务器实例上创建表的新副本，并使用BIGINT数据类型，然后使用对象级恢复将其移到生产数据库中。

评估可选方案

最为直接的方式就是修改表字段类型。但是相应的停机时间就会很长，ID列是聚集索引，因此修改前还必须删除索引键。问题一下子就浮出水面了。

如果用这种方式修改，推测会引起至少好几个小时的停机。另外由此产生的日志可能还要占据大量的磁盘。因此处于对停机时间的要求，这个选择pass了。

当然如果是AZURE SQL Database或者2016以及2017 都可以提供在线重建的功能，除此之外在线重建也有几个限制，比如在MSDN中的警告:

Online alter column does not reduce the restrictions on when a column can be altered. References by index/stats, etc. might cause the alter to fail. 意思就是也不是很好。

另一个方案就是引入触发器。这需要将所有数据复制到一个新表中，创建所有索引和约束，然后创建一个触发器，以确保插入两个表。我个人怀疑这个方案是否满足条件，包括维护和性能。

另一个方案就是建议使用INT的负值。这意味着要重新设定INT从-1 到-2.147 billion 行，这也只是短时间的解决问题。不能一劳永逸或者长期作为处理方式。

后来找到一个比较标准的方法我比较推荐的。就是去创建一个副本表，唯一不同就是使用BIGINT代替INT，然后小批量的赋值数据，保证两个表示同步，通过使用cdc或者触发器来捕捉原表的修改完成对目标表的插入。最后只需要一段很短时间的宕机时间就可以完成新旧表的切换。这是我的后来选择的方案，但是最近有找到一个比较好的方案，我创建了一个副本表在独立的开发环境的实例上。使用SSIS来保证数据同步。然后使用对象级别的还原，将新表切换到生产环境。事实证明这样做的的确也觉少了宕机时间。

具体实践

在我们的测试和开发环境中，我做了大量工作，确保这种方法能够像预期的那样工作。以下部分总结了测试工作。这个演示模仿接近的步骤，使用了AdventureWorks的样本数据库。假定已经将数据库恢复到一个开发环境，并从创建副本表开始

创建副本数据表

在一个新还原的AdventureWorks数据库中，创建一个PersonNEW表，使用BIGINT数据类型作为聚集索引列，如下所示。注意:为了模仿生产环境，在另一个实例的数据库中创建新表。

CREATE TABLE Person.PersonNEW

(

BusinessEntityID BIGINT NOT NULL,

PersonType NCHAR(2) NOT NULL,

NameStyle dbo.NameStyle NOT NULL,

Title NVARCHAR(8) NULL,

FirstName dbo.Name NOT NULL,

MiddleName dbo.Name NULL,

LastName dbo.Name NOT NULL,

Suffix NVARCHAR(10) NULL,

EmailPromotion INT NOT NULL,

AdditionalContactInfo XML(CONTENT Person.AdditionalContactInfoSchemaCollection) NULL,

Demographics XML(CONTENT Person.IndividualSurveySchemaCollection) NULL,

rowguid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,

ModifiedDate DATETIME NOT NULL,

CONSTRAINT PK_Person_BusinessEntityIDNEW

PRIMARY KEY CLUSTERED (BusinessEntityID ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON

) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

GO

 

传输数据，创建索引约束

 

我使用SSIS将所有数据传输到PersonNEW表，然后创建所有必要的索引和约束。当创建SSIS包时，请确保单击Enable Identity Insert(参见下面)。您将在选择源表和视图的Edit Mappings选项卡下找到这个选项。在我的场景中有一个身份列，所以这是需要的。我也不希望有任何差异，因为ID是许多应用程序和整个公司使用的每个订单的唯一编号。

在测试期间，我使用SSIS包定期更新BIGINT表中的数据。例如，如果最后一个导入在ID 6000处停止，那么我将使用> 6000创建下一个SSIS包。增量插入。我每天都这样做，以保持数据传输时间的减少。下面提供了用于Person表的SSIS包中使用的查询。

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT [BusinessEntityID]

,[PersonType]

,[NameStyle]

,[Title]

,[FirstName]

,[MiddleName]

,[LastName]

,[Suffix]

,[EmailPromotion]

,[AdditionalContactInfo]

,[Demographics]

,[rowguid]

,[ModifiedDate]

FROM [AdventureWorks2014].[Person].[Person]

WHERE BusinessEntityID > 6000

在测试期间，我还使用了Redgate的SQL数据比较数据传输后的数据，以验证数据是否完全按照预期复制。

对象级还原

下一步是在一个单独的登台服务器上测试这个过程。我想看看是否可以将表的对象级别恢复到具有不同名称的数据库中。为此，我必须使用第三方SQL Server备份工具，因为对象级别的恢复不受本机支持。我将AdventureWorks的新副本恢复到登台服务器，并将其命名为AdventureWorksBIGINT。这在我的测试中代表了生产数据库。然后，我将新的表(PersonNEW)从备份恢复到新的staging数据库。

这是一种烟雾测试，以确保相同的对象级别恢复，从开发到生产将完全按照预期工作。在还原生产时，我使用SQL Server备份工具中的对象级别恢复功能恢复了表。

创建一个触发器来停止对原始表的条目

在切换表的期间，一定要暂停表数据的该表，可以使用触发器，停止所有对于标的增删改。

CREATE TRIGGER trReadOnly_Person ON [Person].[Person]

INSTEAD OF INSERT,

UPDATE,

DELETE

AS

BEGIN

RAISERROR( 'Person table is read only.', 16, 1 )

ROLLBACK TRANSACTION

END

GO

--DROP TRIGGER trReadOnly_Person

 

 

切换新表

现在，原始的和副本的表都在同一个数据库中，最后一步是交换表，交换索引、约束、表名、外键、触发器和几个数据库权限，以拒绝访问某些列。您可以在本文的底部下载AdventureWorks的测试对象翻转脚本，但我不会在这里展示它。回过头来看，我确实把索引名flip复杂化了，因为在我的环境中只需要主键。请记住，并不是所有的索引都需要更改，因为您可以在两个不同的tabl中重用相同的名称。

建议：开发环境中可以把表进行压缩这样会小很多。

万事俱备，旦所有对象都被重命名，您可以删除触发器以重新打开表。

部署到生产环境

在我看来，方法奏效了。我们在验收环境中运行了一个试点，模拟了我们的生产设置，并且运行良好。

在验收和生产过程中，流程按照以下步骤进行:

1. 将生产数据库的完整数据库备份恢复到开发/测试环境。
2. 在还原的数据库中，用BIGINT代替INT创建副本表。
3. 创建SSIS包，并启IDENTITY INSERT ，传输数据。
4. 在复制表上创建所有索引和约束。
5. 压缩表
6. 将对象还原到生产数据库中，保持表名为PersonNew。
7. 使用SSIS包定期更新PersonNew表，以将数据从可用性组中的报告实例转移
8. 在计划的维护窗口中，多做一个SSIS传输，然后创建触发器以使表为只读。还关闭了访问此表的应用程序。
9. 差异备份
10. 表切换
11. 检查数据一致性
12. 删除触发器并将api返回到在线。

这种方法将停机时间从可能的9小时缩短到15分钟，并且大量的密集工作都从生产实例中删除了。我没有看到使用对象级恢复对表的恢复有多大影响。

总结

有许多方法可以将数据类型更改用于生产数据库。您选择的选项通常取决于可用的停机时间窗口。总得来说，标准方法和后面的方法都是比较好的方式，同时确保数据的完整性是第一位的。

我介绍的方法最小化了停机时间和影响生产服务器性能的潜力，同时它允许我在单独的开发实例上完成大部分工作。