迁移 40TB SQL 服务器数据库

背景

我们的 HAProxy1 日志又叫流量日志，当前存储在两台 SQL Server 上，一台在纽约，一台在科罗拉多州。在 2019 年初，我们有约 4.5 年的数据，总计约 38TB。最初，数据库设计为每天只存一个表。也就是说在 2019 年初，单个数据库中大约有 1600 个表，其中包含多个数据库文件（因为数据文件的大小限制为 16TB）。每个表都有一个集群化的列存储索引，其中包含 100-400 百万行。

我必须将数据从现有的每日表中移到一个新结构中——每个月一个表。这需要同时在 NY（纽约）和 CO（科罗拉多州）服务器上完成，并且数据是存储在机械硬盘上的，所以迁移会非常缓慢而痛苦。

此外，迁移工作只能用现有服务器上仅剩的一点磁盘空间来完成。这些服务器有一个 44TB 的机械硬盘分区，而我们已经用掉了 36-38TB 不等。所以，我得这么做：

• 将数据迁移到新格式
• 删除旧数据
• 压缩旧的数据库文件
• 不断重复

我以为只要几个月就能搞定，没想到，最后整个项目花了 11 个月的时间。

一些要点

先提一些项目要点：

• 我是完全远程工作的，所以无法从本地计算机运行任何进程。一切任务都需要在一台永久在线的计算机上执行，因此出现任何VPN故障都不行。
• 我需要在两台单独的机器上完成工作。我当时是分别在两个数据中心的SQL Server上进行迁移的，所以需要在两个地点各准备一台机器，以避免网络速度问题。
• 我们在Stack Overflow中有用于各种用途的跳转盒（jump box），在NY和CO各有一个，非常适合迁移工作。
• 旧数据库仍在实时生产环境中运行，这意味着在我移动数据时，每天都会添加一个新表。换句话说，我的迁移对象是动态的。
• 数据库处于simple recovery状态，因此我们不必处理事务日志。我们的备份是原始的源日志文件，而两地的SQL Server（NY和CO）是彼此的副本。

这些就是整个项目的痛点。与 VPN 断开连接意味着我必须重新连接才能监视迁移进度。从跳转盒启动意味着经过一些清理后，所有进程都需要重新启动。由于我们仍在插入新数据，因此我一直在和目标赛跑，花费的时间越长，我需要移动的数据就越多。

为什么要这样做？

这样做有很多原因，其中之一就是技术债务。我们意识到原始的每日表结构并不理想。如果我们需要查询跨越几天或几个月的数据，它会很慢。

为什么不删除或清除某些数据？

如前所述，我们在两台服务器上只剩下很少的可用空间。我们清除了一些数据，但对数据团队来说，数据越多越好。

我们迟早要购买新硬件，目标是将数据迁移到新格式，然后当我们获得新服务器时把硬盘挪过去就行。

开始工作

每台服务器的硬盘状态如下：

• 一个230GB的C盘，只安装Windows；
• 一个3.64TB的NVMe D盘，包含tempdb、一个数据文件和现有HAProxyLogs数据库的日志文件，大约85％已用；
• 一个44TB的E盘，HAProxyLogs数据库的其余3个数据文件，85-90％已用。

因此，可用的中转空间很小。

经过研究，我们决定在两个服务器中同时安装几个额外的 NVMe SSD。服务器中只有 PCIe 插槽可用，所以最后把 U.2 接口的 NVMe SSD 通过转换卡装到了这些插槽上。最后，我们得到了一个 14TB 的空白 F 盘，这至少给了我一点空间。

现在，我们有了一些自由空间，是时候设置新数据库并开始迁移。我编写了脚本来创建新数据库：

CREATE DATABASE [TrafficLogs] CONTAINMENT = NONE
ON PRIMARY
(   NAME = N'TrafficLogs_Current',
   FILENAME = N'F:\Data\TrafficLogs_Current.mdf',
   SIZE = 102400000KB,
   FILEGROWTH = 5120000KB
),
FILEGROUP [TrafficLogs_Archive]
(
   NAME = N'TrafficLogs_Archive1',
   FILENAME = N'E:\Data\TrafficLogs_Archive1.ndf' ,
   SIZE = 102400000KB,
   FILEGROWTH = 5120000KB
),
(
   NAME = N'TrafficLogs_Archive2',
   FILENAME = N'E:\Data\TrafficLogs_Archive2.ndf',
   SIZE = 102400000KB , FILEGROWTH = 5120000KB
),
(
   NAME = N'TrafficLogs_Archive3',
   FILENAME = N'E:\Data\TrafficLogs_Archive3.ndf',
   SIZE = 102400000KB,
   FILEGROWTH = 5120000KB
)
LOG ON
(
   NAME = N'TrafficLogs_log',
   FILENAME = N'F:\Data\TrafficLogs_log.ldf',
   SIZE = 5120000KB,
   MAXSIZE = 2048GB,
   FILEGROWTH = 102400000KB
);

它将所有旧的历史表（40TB 的那个）存储在TrafficLogs_Archive文件组中，我们将使用PRIMARY和TrafficLogs_Current来添加新数据。

你会注意到，TrafficLogs_Archive文件组位于快塞满的 E 盘上，而不是新的 F 盘上——稍后将详细介绍该错误。

移动所有数据

我们有了一个数据库，所以是时候开始迁移了。需要明确的是，我实际上是接管了一个从几年前就开始、停止，然后不断重复的过程。在我成为 Stack Overflow 的 DBA 之前，这个项目已经积压了很多工作。那时，大家都意识到这将是一个非常耗时的项目，并且由于我们没有足够的资源，结果不断碰壁。每天都有新表加入，任务也越来越庞大。

由于这是一个曾被废弃的项目，因此我并不是完全从头开始的。我拿到了一些脚本来：

1.创建所有新的月度表

-- written by Nick Craver
Declare @month datetime = '2015-08-01';
Declare @endmonth datetime = '2021-01-01'
WHILE @month < @endmonth
BEGIN
Set NoCount On;
Declare @prevMonth datetime = DateAdd(Month, -1, @month);
Declare @nextMonth datetime = DateAdd(Month, 1, @month);
Declare @monthTable sysname 
    = 'Logs_' + Cast(DatePart(Year, @month) as varchar) 
      + '_' + Right('0' + Cast(DatePart(Month, @month) as varchar), 2);
Begin Try
  If Object_Id(@monthTable, 'U') Is Not Null
  Begin
    Declare @error nvarchar(400) 
      = 'Month ' + Convert(varchar(10), @month, 120) 
        + ' has already been moved to ' + @monthTable + ', aborting.';
    Throw 501337, @error, 1;
    Return;
  End
  -- Table Creation
  Declare @tableTemplate nvarchar(4000) = '
    Create Table {Name} (
      [CreationDate] datetime Not Null,
      <insert all the columns>,
      Constraint CK_{Name}_Low Check (CreationDate >= ''{LowerDate}''),
      Constraint CK_{Name}_High Check (CreationDate < ''{UpperDate}'')
  ) On {Filegroup};

    Create Clustered Columnstore Index CCI_{Name} 
      On {Name} With (Data_Compression = {Compression}) On {Filegroup};';
  -- Constraints exist for metadata swap
  Declare @table nvarchar(4000) = @tableTemplate;
  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, '{UpperDate}', Convert(varchar(20), @nextMonth, 120));
  Set @table = Replace(@table, '{Compression}', 'ColumnStore_Archive');
  Print @table;
  Exec sp_executesql @table;

  Declare @moveSql nvarchar(4000) 
    = 'Create Clustered Columnstore Index CCI_{Name} 
      On {Name} With (Drop_Existing = On, Data_Compression = Columnstore_Archive) On Logs_Archive;';
  Set @moveSql = Replace(@moveSql, '{Name}', @monthTable);
  Print @moveSql;
  Exec sp_executesql @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

2.一个 LINQPad 脚本，从最早的一天开始遍历，并将数据插入到新表中

-- written by Nick Craver
<Query Kind="Program">
<NuGetReference>Dapper</NuGetReference>
<Namespace>Dapper</Namespace>
</Query>
void Main()
{
  MoveDate(new DateTime(2015, 08, 1));
  DateTime date = new DateTime(2015, 08, 1);
  while (date < DateTime.UtcNow)
  {
    MoveDate(date);
    date = date.AddDays(1);
  }
}
static readonly List<string> cols = new List<string> { "<col list>" };
public void MoveDate(DateTime date)
{
  var tableName = GetTableName(date);
  var destTable = GetDestTableName(date);
  $"Attempting to migrate {date:yyyy-MM-dd} f
    from {tableName} to {destTable}".Dump($"{date:yyyy-MM-dd}");
  using (var conn = GetConn())
  {
    int rowCount;
    try
    {
      rowCount = conn.QuerySingle<int>($"Select Count(*) 
                        From HAProxyLogs.dbo.{tableName};");
    }
    catch (SqlException e)
    {
      ("  Error migrating: " + e.Message).Dump();
      return;
    }
    $"  Summary for {date:yyyy-MM-dd}".Dump();
    $"    {rowCount:n0} row(s) in {tableName}".Dump();
    var pb = new Util.ProgressBar($"{tableName} (0/{rowCount})");
    pb.Dump(tableName + " copy");
    Func<int> GetDestRowCount = () 
      => conn.QuerySingle<int>($"Select Count(*)