如何將生產環境的欄位類型從INT修改為BIGINT

介紹改變數據類型是一個看起來很簡單的事情，但是如果表非常大或者有最小停機時間的要求，又該如何處理那？這裡我提供一個思路來解決這個問題。背景在一個常規SQL Server heath檢查中，使用sp_blitz，我們最大的生產表之一引發了令人擔憂的警報。保存客戶訂單信息的表的ID列是一個INT ...

介紹

改變數據類型是一個看起來很簡單的事情，但是如果表非常大或者有最小停機時間的要求，又該如何處理那？這裡我提供一個思路來解決這個問題。

背景

在一個常規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中重用相同的名稱。

建議：開發環境中可以把表進行壓縮這樣會小很多。

萬事俱備，旦所有對象都被重命名，您可以刪除觸發器以重新打開表。

部署到生產環境

在我看來，方法奏效了。我們在驗收環境中運行了一個試點，模擬了我們的生產設置，並且運行良好。

在驗收和生產過程中，流程按照以下步驟進行:

將生產資料庫的完整資料庫備份恢復到開發/測試環境。
在還原的資料庫中，用BIGINT代替INT創建副本表。
創建SSIS包，並啟IDENTITY INSERT ，傳輸數據。
在複製表上創建所有索引和約束。
壓縮表
將對象還原到生產資料庫中，保持表名為PersonNew。
使用SSIS包定期更新PersonNew表，以將數據從可用性組中的報告實例轉移
在計劃的維護視窗中，多做一個SSIS傳輸，然後創建觸發器以使表為只讀。還關閉了訪問此表的應用程式。
差異備份
表切換
檢查數據一致性
刪除觸發器並將api返回到線上。

這種方法將停機時間從可能的9小時縮短到15分鐘，並且大量的密集工作都從生產實例中刪除了。我沒有看到使用對象級恢復對錶的恢復有多大影響。

總結

有許多方法可以將數據類型更改用於生產資料庫。您選擇的選項通常取決於可用的停機時間視窗。總得來說，標準方法和後面的方法都是比較好的方式，同時確保數據的完整性是第一位的。

我介紹的方法最小化了停機時間和影響生產伺服器性能的潛力，同時它允許我在單獨的開發實例上完成大部分工作。