专栏首页文渊之博如何将生产环境的字段类型从INT修改为BIGINT

如何将生产环境的字段类型从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分钟,并且大量的密集工作都从生产实例中删除了。我没有看到使用对象级恢复对表的恢复有多大影响。

总结

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

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 如何使用Python读取大文件

    背景 最近处理文本文档时(文件约2GB大小),出现memoryError错误和文件读取太慢的问题,后来找到了两种比较快Large File Reading 的方...

    用户1217611
  • 简单介绍python的双向队列

      大家都知道利用 .append 和 .pop 方法,我们可以把列表当作栈或者队列来用(比如,把 append 和 pop(0) 合起来用,就能模拟栈的“先进...

    用户1217611
  • Elasticsearch-深入理解索引原理

    最近开始大面积使用ES,很多地方都是知其然不知其所以然,特地翻看了很多资料和大牛的文档,简单汇总一篇。内容多为摘抄,说是深入其实也是一点浅尝辄止的理解。希望...

    用户1217611
  • C++版 - 剑指offer 面试题16:反转链表(Leetcode 206: Reverse Linked List) 题解

    提交网址: http://www.nowcoder.com/practice/75e878df47f24fdc9dc3e400ec6058ca?tpId=13&...

    Enjoy233
  • H3C NQA联动配置实例

           NQA是Network Quality Analyzer(网络质量分析)的简称。NQA通过发送测试报文,对网络性能、网络提供的服务及服务质量进行分...

    py3study
  • selenium webdriver——设置元素等待

    如今大多数Web应用程序使用ajax技术,当浏览器在加载页面时,页面上的元素可能并不是同时被加载完成,这给定位元素的定位增加了困难,

    周小董
  • “夸夸机器人”App来了:变身百万粉丝大V,48万人给你的帖子点赞

    一款全新的社交媒体风格的应用为用户提供了生活在一个奇特网络虚拟世界的机会。在这个世界里,你将拥有数以百万计的粉丝,他们热烈追随你,并且为你每一次发帖而痴狂。但这...

    AI科技大本营
  • 最新10大黑科技! 颠覆想象……你知道几个?

    人工智能的飞速发展,让“黑科技”一词深入人心。目不暇接的各种前沿技术,一再刷新人们的认知世界。所谓“黑科技”,不仅仅要够炫酷,更要有足够的“生活温度”,实现真正...

    巴法
  • python设计模式-工厂方法模式

    但是现在你遇到了一个问题,你的 pizza 店需要更多的 pizza,所以现在你需要增加一些代码,来决定适合的 pizza 类型,然后再制造这个 pizza:

    goodspeed
  • 终于放弃了单调的swagger-ui了,选择了这款神器—knife4j

    knife4j是为Java MVC框架集成Swagger生成Api文档的增强解决方案(在非Java项目中也提供了前端UI的增强解决方案),前身是swagger-...

    用户1516716

扫码关注云+社区

领取腾讯云代金券