迁移40TB SQL Server数据库

2020-07-30 23:29:19

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

虽然大多数人可能对我们的主要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编写,[email protected] DATETIME=';2015-08-01';[email protected]月DATETIME=';2021-01-01';而@MONTER<;@ENDMONTER BEGIN设置NoCount ON;[email protected] MONTH DATETIME=DateAdd(月,-1,@月);[email protected] DateTime=DateAdd(月,1,@月);[email protected] TABLE sysname=&#。+CAST(DatePart(Year,@Month)as varchar)+';_';+right(';0';+CAST(DatePart(Month,@Month)as varchar),2);如果Object_ID(@MonthTable,';U';)不为Null BEGIN [email protected] nvarchar(400)=';MONTH';+CONVERT(varchar(10),@Month,120)+';已移动到';[email protected]+';,正在中止。';抛出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});';;-存在约束。[email protected]=Replace(@table,';{name}';,@monthTable);[email protected]=Replace(@table,';{Filegroup}';,';Logs_Archive';);[email protected]=Replace(@table,';{LowerDate}';,Convert(varchar(20),@Month,120));[email protected]=Replace(@table,';{LowerDate}';,Convert(varchar(20),@Month,120))。{UpperDate}';,CONVERT(varchar(20),@nextMonth,120));[email protected]=Replace(@table,';{Compression}';,';ColumnStore_Archive';);[email protected];Exec [email protected][email protected] nvarchar(4000)=';使用(Drop_Existing=ON,Data_Compression=Columnstore_Archive)on Logs_Archive;[email protected]=Replace(@moveSql,';{name}';,@monthTable);在{name}上创建聚集列存储索引CCI_{name};[email protected];Exec [email protected];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 [email protected]=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';[email protected] 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。

.