主页 / dba / 问题 / 231268
Accepted
Enrique Zavaleta
Asked: 2019-03-05 11:29:41 +0800 CST

在不阻塞源的情况下将数据复制到其他位置的最佳方法

  • 772

我有一个生产数据库，其中包含一些我需要复制到其他位置的数据，可以是同一服务器上的另一个数据库，也可以是另一台服务器。

数据库很大，我不需要数据库中的所有数据，但例如在其中一个表中有约 3 亿条记录，我只需要约 6000 万条记录。

问题是由于空间限制，可能无法在同一台/另一台服务器中恢复整个数据库，因此我们可能被迫只复制需要的内容。

另一个限制是这些表由生产机器使用，因此它们需要 100% 可用，所以我不能阻止对它们的任何写入和读取访问。

那么，将部分数据从一个数据库复制到另一个数据库的最佳方法是什么？

  • 要运行插入 N 条记录直到完成的查询？INSERT INTO ... SELECT TOP N ... FROM ... WITH(NOLOCK) WHERE ...
  • 使用进出口数据？这个工具在运行时会阻塞源表吗？
sql-server copy

1 个回答

  1. Best Answer

    这在很大程度上取决于您对目的地数据的目标/要求。具体你需要权衡阻塞和数据正确性之间的利弊。

    避免阻塞——READ COMMITTED with SELECT...INTO

    例如，默认READ COMMITTED隔离级别下最天真的解决方案将解决您的问题，而源服务器上的阻塞很少：

    • [可选，如果数据库在另一台服务器上] 在连接到源 (SQL Server 2016) 实例的目标 (SQL Server 2008) 实例上创建一个链接服务器
    • 运行SELECT...INTO语句以复制所需的 60 毫米行
    SELECT /*column list*/ 
INTO [dbo].[BigTable]
FROM [SourceDatabase].[dbo].[BigTable]
WHERE /*predicates to get the 60mm rows you need*/

    注意：FROM如果使用链接服务器方法，则需要在子句中指定链接服务器名称

    当在聚集索引（或堆，视情况而定）中扫描页面时，共享锁将被获取和释放，因此会有短暂的阻塞特定页面的时刻，但对于您的生产系统来说，这应该不会非常明显。当然，可能还有其他资源争用点（例如存储吞吐量）。

    这种方法的主要问题是它基本上不能保证到达目的地的数据的正确性。Paul White 有一篇关于已提交读取隔离级别的精彩文章，您可以查看详细信息，但主要问题是这不能保证数据的时间点视图。

    换句话说，DML 语句（INSERT、UPDATE 和 DELETE）可以在扫描期间成功执行，这可能会导致行被多次读取、行被完全遗漏等。

    目标数据库的一个潜在问题是日志文件增长。如果要移动的数据的大小（不仅仅是行数）非常大，您可能需要考虑分批插入而不是一次完成所有插入。

    支持正确性——更严格的隔离级别

    如果希望到达目的地的数据提供与特定时间点一致的数据，可以在整个插入过程中使用SNAPSHOT或SERIALIZABLE隔离级别（实际插入过程可能与此相同或相似）如上所述）。

    SNAPSHOT隔离级别是一个可行的选择。它会导致 tempdb 使用量增加，因为要维护行版本以提供一致的数据库视图，因此您需要确保您的系统可以处理该工作负载。它还会导致某些类型的阻塞（多于READ COMMITTED，但少于SERIALIZABLEapproach）。这将对您的生产工作量产生多大影响取决于您的测试。

    隔离级别对SERIALIZABLE您的生产工作负载最具破坏性，因为它将阻止对表的所有访问，直到事务完成。这不符合您的要求，因此它不是一个真正可行的选择。

    正确性和无阻塞 - 复制/备份

    如果您能够在某处恢复完整的数据库备份，那么这是理想的解决方案。您有数据库的时间点视图，没有争用，然后您可以从中将需要的行复制到目标。由于您提到数据库大小/空间是一个问题，我想这不是一个可行的选择。

    由于您的情况有特殊要求，您可能要考虑购买支持“对象级恢复”的第三方工具。这将允许您直接从备份中将表所需的内容恢复到目标位置。我认为 Quest Litespeed 支持这一点，尽管很难在他们网站的付费专区后面分辨出来。

    如果您需要数据不断更新，事务复制也可能是一个可行的选择，尽管版本限制可能会阻止您利用此选项。此外，维护和设置开销可能不切实际。我没有在实践中使用过事务复制，所以我只是提到它作为一种可能性。

    关于诺洛克

    不要用那个。

    • 4

相关问题