迁移40TB SQL Server数据库

起初，我不确定是否要写这个迁移项目，但当我随机询问人们是否会感兴趣时，回答是压倒性的。这是一个漫长，有点无聊，非常重复，有时令人难以置信的令人沮丧的项目，但我学到了很多，也许其他人也会从这个项目中学到东西。也许有更好的方法来移动这样大量的数据。在我走过的小路上，有大量的杂耍必须发生(我稍后会解释这一点)。谢天谢地，我是个不错的杂耍高手。

虽然大多数人可能对我们的主要SQL服务器(运行Stack Overflow和Stack Exchange站点网络的服务器)所做的事情感兴趣，但本文不是关于这些服务器的。这篇文章是关于我在上一篇文章中所说的杂项服务器-更具体地说，它是关于我们的交通日志数据的迁移。

我们的HAProxy 1日志(又称流量日志)目前存储在两台SQL服务器上，一台在纽约，另一台在科罗拉多州。虽然我们存储的是流量数据的最小摘要，但我们拥有大量的流量数据。在2019年初，我们有大约4.5年的数据，总计约38TB。数据库最初设计为每天只有一个表。这意味着，在2019年初，我们的单个数据库中约有1600个表，其中包含多个数据库文件(由于数据文件的大小限制为16 TB)。每个表都有一个聚集的列存储索引，其中包含1亿-4亿行不等的行。

我不得不将数据从他们现有的每日表格移动到一个新的结构中-每个月一个表格。这需要在NY和CO服务器上完成，并且数据存储在SPINNY驱动器(EEEK)上。无论如何，这将是痛苦和缓慢的。

哦，让事情变得更复杂的是，这必须用现有服务器上最小的空闲磁盘空间来完成。服务器有一个44TB的旋转驱动器分区，我们使用了36-38TB，具体取决于服务器。这意味着我将在整个过程中遵循以下步骤：

我完全预计这一过程需要几个月的时间，它果然做到了-在获得新服务器方面遇到延误后，整个项目花了大约11个月的时间。

1注：如果您感兴趣，我的同事Nick Craver(b|t)在他的博客上详细介绍了我们的监控，并概述了我们的HAProxy使用情况。

在我开始之前，有几件事我需要提一下，它们影响了该项目：

我100%的时间都在远程工作，因此我不能从本地计算机运行任何进程。所有操作都需要在一台持续连接到网络的机器上执行，因此不会出现任何VPN故障。

我需要在两台不同的机器上运行这个程序。我是在两个不同数据中心的SQL服务器上执行此操作的，我需要同一位置的一台机器，这样我就不必处理网络上的缓慢问题。

我们在Stack Overflow有用于各种用途的跳箱，在纽约和CO有一个跳箱，非常适合运行迁移。

旧数据库仍在实时生产环境中使用，这意味着当我移动数据时，我们每天都在不断添加新表。换句话说，我有一个移动的目标。

数据库处于简单恢复中，因此我们不必处理事务日志。我们的备份是原始源日志文件，而这对SQL Server(NY和CO)是彼此的副本。

上面的子弹是整个项目的痛点。与VPN断开连接意味着我必须重新连接才能监控迁移进度。从跳箱启动，意味着任何正在运行的进程都需要在一些清理之后重新启动。因为我们还在插入新数据，所以我一直在追赶目标，所以花的时间越长，我最终移动的数据就越多。

需要这么做的原因有很多，其中之一就是科技债务。我们意识到原来的日表结构并不理想。如果我们需要查询几天或几个月的内容，那就太可怕了--一次遍历几天甚至几个月的大量Union all或循环是很慢的。

正如我提到的，我们在两台服务器上处理的空闲磁盘空间非常少。此数据主要由开发人员用于调查问题，并由数据团队用于分析。我们已经清除了一些数据，但是对于数据团队来说，数据越多越好。

我们的计划不是清除，而是最终获得新的硬件来取代它们，但我们不能确切地确定这将在什么时候发生。我们的目标是将数据迁移到新的格式，然后当我们获得新的服务器时，只需移动驱动器就可以将新的数据库安装到位(或者我们是这么认为的)。

适用于Windows的230 GB C：驱动器-显然我们不会将数据文件放在这里。

3.64TB NVMe D：包含驱动器的临时数据库、一个数据文件和现有HAProxyLogs数据库的日志文件-大约85%已满。

一个装满旋转磁盘的44TB E：驱动器-其余3个数据文件用于HAProxyLogs数据库-已满85%-90%。

没有太多空间可以将TB级的数据移动到同一服务器上的新数据库。

由于新服务器即将推出，我询问是否有可能获得更多磁盘空间。有更多的空间会给我一些喘息的空间，让我在这个过程中不会被压得太紧。经过一些研究后，我们决定可以在两台服务器中安装两个额外的NVMe固态硬盘。我们在服务器中只有PCIe插槽可用，所以我们最终在PCIe适配器上使用U.2 NVMe驱动器来腾出一些空间。固态硬盘为我们提供了全新的F：驱动器，可用空间为14TB。虽然我不能把所有东西都搬到新的硬盘上，但它给了我足够的空间来工作，至少有一小段时间是这样。

现在我们有了可用的空间，是时候设置新数据库并开始迁移了。我编写了创建新数据库的脚本：

在主服务器(名称=N'；TrafficLogs_Current'；，文件名=N'；F：\Data\TrafficLogs_Current.mdf'；，大小=102400000 KB，文件=5120000 KB)、文件组[TrafficLogs_Archive](名称=N'；TrafficLogs_Archive1&#)上创建数据库[TrafficLogs]Containment=NONE(名称=N'；TRAFICLOS_CURRENT'；F：\DATA\TRAFICLOS_CURRENT'；，大小=102400000 KB，文件=5120000 KB)。，Size=102400000 KB，FILEGROWTH=5120000 KB)，(名称=N'；TrafficLogs_Archive2'；，FileName=N'；E：\Data\TrafficLogs_Archive2.ndf'；，大小=102400000 KB，文件=5120000 KB)，(Name=N'；TrafficLogs_Archive3'；，Filename=N'；E。，Size=102400000 KB，FILEGROWTH=5120000 KB)登录(NAME=N'；TRAFFICLOGS_LOG'；，文件名=N'；F：\DATA\TRAFICLOGS_LOG.ldf'；，SIZE=5120000 KB，MAXSIZE=5120000 GB，FILEGROWTH=102400000 KB)；

数据库将在TrafficLogs_Archive文件组中存储所有旧的历史表(即40TB)，我们将使用PRIMARY和TRAFICLOGS_CURRENT来添加较新的数据。

您会注意到，TrafficLogs_Archive文件组位于已经非常满的E：驱动器上，而不是新的F：驱动器上-稍后将详细介绍该错误。

我们有一个数据库，所以是时候开始迁移了。需要明确的是，我实际上接管了一个几年前开始、停止、然后被踢出去的过程。早在我成为Stack Overflow的DBA之前，这个项目就已经积压了很久。当时，每个人都意识到这将是一个多么耗时的项目，由于我们没有资源投入到这项工作中，罐子不断地被踢下了路。结果，添加了越来越多的表，使得整个任务变得更大。

因为这是一个之前被放弃的项目，所以已经有一些脚本写好了，这意味着我并不是完全从头开始。我收到了几个脚本给我：

--由Nick Craver编写，DECLARE@MOUNT DATETIME='；2015-08-01'；DECLARE@ENDMO月DATETIME='；2021-01-01'；而@MONTER<；@ENDMONTER BEGIN设置NoCount ON；DECLARE@PRVERMONT MONTH DATETIME=DateAdd(月，-1，@月)；DECLARE@nextMonth DateTime=DateAdd(月，1，@月)；DECLARE@MONTTH TABLE sysname=&#。+CAST(DatePart(Year，@Month)as varchar)+'；_'；+right('；0'；+CAST(DatePart(Month，@Month)as varchar)，2)；如果Object_ID(@MonthTable，'；U'；)不为Null BEGIN DECLARE@ERROR nvarchar(400)='；MONTH'；+CONVERT(varchar(10)，@Month，120)+'；已移动到'；+@MonthTable+'；，正在中止。'；抛出501337，@Error，1；Return；End--表创建声明@tableTemplate nvarchar(4,000)='；创建表{Name}([CreationDate]DateTime Not Null，<；插入所有列&>，Constraint CK_{Name}_Low Check(CreationDate&Gt；='；'；{LowerDate}'；'；)，{Filegroup}上的约束CK_{Name}_High Check(CreationDate<；'；'；{UpperDate}'；'；)；在{Name}上创建群集列存储索引CCI_{Name}，{Filegroup}上的(Data_Compression={Compression})；'；；-存在约束。Set@table=Replace(@table，'；{name}'；，@monthTable)；set@table=Replace(@table，'；{Filegroup}'；，'；Logs_Archive'；)；set@table=Replace(@table，'；{LowerDate}'；，Convert(varchar(20)，@Month，120))；set@table=Replace(@table，'；{LowerDate}'；，Convert(varchar(20)，@Month，120))。{UpperDate}'；，CONVERT(varchar(20)，@nextMonth，120))；set@table=Replace(@table，'；{Compression}'；，'；ColumnStore_Archive'；)；print@table；Exec sp_ecutesql@table；ECLARE@moveSql nvarchar(4000)='；使用(Drop_Existing=ON，Data_Compression=Columnstore_Archive)on Logs_Archive；set@moveSql=Replace(@moveSql，'；{name}'；，@monthTable)；在{name}上创建聚集列存储索引CCI_{name}；print@moveSql；Exec sp_ecutesql@moveSql；End TRY BEGIN CATCH Select Error_Number()ErrorNumber，Error_Severity()ErrorSeverity，Error_State()ErrorState，Error_Procedure()ErrorProcedure，Error_Line()ErrorLine，Error_Message()ErrorMessage；Throw；End Catch Set@Month=dateAdd(Month，1，@Month)End Go。

一个LINQPad脚本，用于循环每天(从最早的开始)，并将数据插入到新表中。

1 2 3 4 5 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 87 88 90 91 92 95 96 97 98 99 100 101 102 103 104 105 106 107 109 110 112 113 114 115。

--由Nick Craver<；query Kind=#34；Program>；<；NuGetReference>；Dapper<；/NuGetReference>；<；Namespace>；/Namespace>；<；/Query>；void main(){MoveDate(new datetime(2015，08，1))；datetime date(){MoveDate(new datetime(2015，08，1))；datetime date(){MoveDate(new datetime(2015，08，1))；date time date(){MoveDate(new datetime(2015，08，1))；datetime date。UtcNow){MoveDate(Date)；Date=Date.。AddDays(1)；}}静态只读列表<；string>；cols=new list<；string>；{"；<；collist>；"；}；public void MoveDate(Datetime Date){var tableName=GetTableName(Date)；var destTable=GetDestTableName(Date)；$"；正在尝试迁移{date：yyyy-MM-dt

理想情况下，这很简单，只需启动LINQPad脚本，然后让它在后台运行，循环遍历所有的表。我以为我不用担心，但事情从来没有那么简单。

我于2019年1月14日在科罗拉多州开始迁移(是的，我知道确切的日期)。从松散的驱动器中提取数百万行数据，然后将其插入到这些相同的松散驱动器上的新表中，速度很慢。到了第二天，我知道这会很痛苦。我最初的计划是先在科罗拉多州进行移民，然后在纽约。但是，一旦我看到每个表的运行速度有多慢，我就决定同时运行它们。大约一周后，我开始了纽约的数据迁移。

早些时候，我遇到了各种我需要解决的问题。其中一些是简单的修复，另一些就不是那么简单了。

第一个问题与C#脚本有关。脚本在查询数据库时定期超时。调整完设置后，我祈祷它能继续工作，大部分情况下它确实能工作。

接下来，我遇到了一个问题，如果出现错误，脚本将继续到下一张表，也就是一天。这太可怕了，因为我们希望数据按日期顺序插入聚集列存储索引。如果脚本出错并移至第二天，则需要对未完成的那一天进行清理。在这一点上，我将不得不在那一天重新开始这一过程。

让我快速后退一步，解释一下旧的流程。原始的表结构，也就是日常表，是带有通用标识(ID)列的行存储，以使每一行都是唯一的。14天后，我们将从表中删除主键，并添加一个聚集列存储索引。即使转换为群集列存储，ID列仍将保留。

在新表中，我们删除了ID列。删除此列意味着，尝试找出故障前插入的行的唯一性将非常困难。在发生故障的情况下，只从当天的表中删除所有内容，以及当天之后的所有内容(如果移到另一个表中)，然后重新启动该过程要容易得多。

我意识到我需要验证插入到新表中的总行数是否与旧的Daily表中的总行数匹配。听起来很简单，对吧？嗯，不一定。

当然，原始表是按天计算的，但在某些情况下，它可能不包含日期为00：00-23：59的数据，而是可能包含日期+1的一些行。这是一个已知问题(由于来自HAProxy的日志延迟)，我被告知总数不必完全匹配-如果丢失了一些行，也没问题。问题是，当您负责将数据从一个系统移动到另一个系统时，您确实希望与…匹配。完美无缺。

由于来自不同日期的掉队，我不能只查询旧表中的总行，并验证新表是否包含相同的计数。我不得不想出一种方法来查询白天的计数。为此，我向新表添加了一个名为OriginalLogTable的列，然后用旧表的名称填充它，例如Log_2019_07_01。有了这个列，我就可以通过按OriginalLogTable分组，将旧表中的行数与新表中的行数进行比较。这解决了我的问题，因为当我完成一个月时，我可以使用以下脚本轻松地验证新旧表之间的每一天是否匹配：

1 2 3 4 5 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 75 76 77 78 79 80 81 82 83 84 85 87 88 90 91 92 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110。

如果object_id('；tempdb.#NewTableDetails'；)不为空丢弃表#NewTableDetails创建表#NewTableDetails(TrafficLogOrigTable varchar(50)，TrafficLogTotalRows bigint，OriginalLogDate date)如果object_id('；tempdb.#LogDetails'；)不为空丢弃表#LogDetails创建表#LogDetails(LogDetails。2019-10-01'；DECLARE@ENDDATE DATETIME='；2019-11-01'；如果object_id('；tempdb..#date'；)不为空DROP TABLE#DATES；CREATE TABLE#DATES([DATE]DATE主键，FirstOfMonth AS CONVERT(DATE，DATEADD(MOND，DATEDIFF(月，0，[DATEDIFF(月，0，[日期])，0))，OldTableName AS CONCAT。，Year([date])，'；_'；，right('；0'；+rtrim(月([date]))，2)，'；_'；，right('；0'；+rtrim(day([date])，2))，NewTableName as Concat('；HAProxyLogs_'；，Year([date])，'；_。，right('；0'；+rtrim(Month([Date])，2)；INSERT#DATES([DATE])SELECT d FROM(SELECT d=DATEADD(DAY，Rn-1，@StartDate)FROM(SELECT TOP(DATEDIFF(DAY，@StartDate，@EndDate)Rn=ROW_NUMBER()OVER(ORDER BY S1.[object_id])FROM sys。将ALL_OBJECTS作为S1交叉联接sys。All_Objects as s2按s1排序。[object_id])as x)as y；--对于每个新表名，获取行数，获取OldTables列表--导入，然后获取计数f。

.