如何将生产环境的字段类型从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是许多应用程序和整个公司使用的每个订单的唯一编号。

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

在测试期间,我使用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分钟,并且大量的密集工作都从生产实例中删除了。我没有看到使用对象级恢复对表的恢复有多大影响。

总结

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

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

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/119682.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • SecureCRT中文乱码解决方法

    SecureCRT中文乱码解决方法SecureCRT中文乱码解决方法1、先选中当前的Session地址2、然后点击SercureCRT上排主菜单的“Options”,也就是“选项”的意思3、点击Options之后,会出现一个下拉列表,我们选择第一个“SessionOptions…”4、接着会出现一个弹出框,选择目录中的“Appearance”,该功能可以对SercureCRT的外观进行设置5、此时可以看到SercureCRT的编码格式是“Default”,也就是默认的编码格式,我们改为“UTF-8”6、改完编码格式之后,我们回

  • CentOS7 network.service loaded failed 处理技巧

    CentOS7 network.service loaded failed 处理技巧

    2021年10月18日
  • 超级账本(hyperledger)介绍_以太坊上的账户类型有哪些

    超级账本(hyperledger)介绍_以太坊上的账户类型有哪些区块链组织-超级账本(Hyperledger)的简介

  • 依赖关系(“使用”关系)

    依赖关系(“使用”关系)依赖关系,可以理解成“USE-A”关系即使用关系。依赖关系是一种使用关系,如果A类中的某个方法使用了B类对象,那么就可以说A类依赖B类。A类方法的形式参数是B类类型。也就是说A类对象如果要使用方法

  • win10多合一原版系统_win10多合一原版系统[通俗易懂]

    win10多合一原版系统_win10多合一原版系统[通俗易懂]win10多合一原版系统装机系统拥有着大多数同类型定位的装机系统所没有的强大稳定性可以确保系统在运行的过程中绝对不会因为一些小毛病而出现崩溃的现象,对win10多合一原版系统装机系统感兴趣的朋友们快下载吧。win10多合一原版系统介绍:1、在不影响大多数软件和硬件操作的情况下,尽可能关闭不必要的服务。2、电脑兼容通用驱动助手,可以智能判断硬件类型并安装最兼容的驱动。3、综合2000-2020年流行…

  • redis如何设置密码及验证密码_redis如何设置密码及验证密码

    redis如何设置密码及验证密码_redis如何设置密码及验证密码在百度云安装redis服务之后,一直给我发送系统安全警告,推荐我redis设置访问密码,于是出于安全考虑我就设置一下redis的密码1.修改redis.conf配置文件:找到requirepass这一行,解注这一行代码,requirepass后面就是跟的自己的密码。2.关闭redis服务,发现报错:可以使用下面两个方法关闭服务:方式一:通过psaux|grepred…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号