主页 / dba / 问题 / 30692
Accepted
Rachel
Asked: 2012-12-19 10:58:47 +0800 CST

如何在不影响性能的情况下备份大型 SQL Server 数据库？

  • 772

我们收到了关于查询运行缓慢或一大早超时的报告，而我认为唯一可能影响此运行的工作是我们的数据库备份工作。

数据库本身大约 300GB，备份作业从凌晨 4:30 开始，直到早上 7:00 之后才完成。我们备份作业的当前语法是：

BACKUP DATABASE [DatabaseName]
TO DISK = N'E:\Database Backups\DatabaseName.Bak'
WITH INIT, NOUNLOAD, NAME = N'DatabaseName.Bak',
NOSKIP, STATS = 10, NOFORMAT

E:\是服务器上的一个分区，它同时保存数据库和数据库备份。

还应该注意，这是一个虚拟服务器，而不是专用的独立服务器。在我们切换到虚拟服务器后，我们开始收到有关备份过程中速度变慢的投诉，所以我认为这可能是相关的。

有没有办法运行这个备份作业，所以它在运行时不会影响查询性能？

我们正在使用 SQL Server 2005

sql-server sql-server-2005

4 个回答

  1. Best Answer
    1. 将备份 I/O 与系统上的其余 I/O 隔离开来。虽然它可能需要更长的时间并且更容易出现问题，但通过网络而不是本地磁盘备份可能有助于减轻对实例的直接影响。即使在虚拟机中，您也应该能够公开其他存储，以便 SQL Server 可以写入不同的 I/O 子系统。
    2. 它不太可能产生任何影响，但是，如果这是从工作中运行的，为什么需要使用该STATS选项？您确定需要其他选项 ( NOUNLOAD, NOSKIP, NOFORMAT) 吗？我没有对整个选项矩阵进行任何广泛的性能测试，但是恕我直言，您应该只使用您知道自己需要的选项。
    3. 在不干扰常规用户活动的不同时间运行完整备份。这并不总是可能的，但即使在 24/7 的运营中也存在高峰和平静时间。
    4. 如果您迁移到 2008+，您可以利用备份压缩（2008 年的企业版、2008 R2+ 版的标准版或企业版）。如果您无法迁移到 2008 或没有足够的版本，则有 3rd 方备份工具可以为您进行压缩，而且它们都非常擅长。自 2008 年以来，我已经能够使用本机压缩，并且我对那里的速度和压缩百分比都非常满意，无需进一步投资 3rd 方。但是在 2005 年，我记得Red-Gate SQL Backup取得了巨大的成功；Quest LiteSpeed也相当不错，但我不知道自从被戴尔收购后在这方面付出了什么样的努力。
    5. 如果您的数据库处于完全恢复状态，您可以在完全备份之间间隔更长的时间，以换取知识，即如果您必须恢复到某个时间点，您可能会有更多的日志需要恢复。您可以将此选项与 Ali 下面关于在一周内进行 diff 的建议相结合 - 如果您必须在星期四恢复，您将需要恢复 1 个完整的、1 个 diff 以及自 diff 以来的日志。
    6. 如果您有大量陈旧且不变的数据，您可以考虑将其托管在具有不同恢复计划的不同数据库中，或者至少托管在不同的文件组中，并以这种方式拆分备份操作。如果您有可以设为只读的参考或存档数据，并且如果您正在进行简单恢复，则可以将其移至其自己的只读文件组，备份一次，而不必将其包含在每晚的备份中以后再。请参阅这些 MSDN 主题：

    执行零碎恢复

    示例：仅部分文件组的分段还原（简单恢复模型）

    • 22

  2. 这是一个常见问题，有多种解决方案，实际上取决于您的环境。让我们来看看它们：

    1- 即时备份压缩

    2008 年 R1 Backup Compression 在 Enterprise 中可用，在 2008R2 中在 Standard 中可用。这是巨大的。它将为您节省大量时间。如果你能升级就去吧。如果不能，请查看RedGate 的 HyperBak实用程序或Quest LiteSpeed。两者都有免费试用。

    2-完整和差异备份

    我继承了一个 2TB 的产品数据库，导致我工作的一家 24/7 主要互联网公司出现很多超时。我们启用了完整备份和差异备份，这为我们节省了大量时间。我会在周日上午 12:00 活动较少的时候进行完整备份，并在一周内进行差异分析。这节省了很多空间。Diff 的工作与事务日志不同，因为它们处理更改了哪些数据库页面。备份任何更改的页面。因此，您进行完全还原，然后进行差异还原以添加修改后的页面。

    3-你的瓶颈是什么？

    瓶颈分析对诊断很重要。您是否备份到与数据文件相同的磁盘阵列？您的数据文件是否被挂钩？备份期间数据磁盘的 DISK SEC/READ 和 DISK SEC/WRITE 是多少？我修改了备份以创建 4 个文件。每个文件都有自己的线程编写器，并且在我们的 SAN 中效果很好。测试一下，我只创建了 4 个备份文件就缩短了 45 分钟。只需确保上面列出的磁盘指标较低。获取基线。

    4-复制到不同的服务器并备份

    这个有点高级。您必须确保您的复制数据库是最新的，并且您需要对此进行适当的监控。如果是这样，您可以只备份复制的数据库。

    • 8

  3. 您可以使用以下参数：

    块尺寸 - 选择尺寸512、1024、2048、4096、8192、16384、32768、65536。（以 KB 为单位）

    BUFFERCOUNT - 指定用于备份操作的 I/O 缓冲区的总数。您可以指定任何正整数；但是，由于 Sqlservr.exe 进程中的虚拟地址空间不足，大量缓冲区可能会导致“内存不足”错误。- 来自 MSDN

    MAXTRNASFERSIZE - 从 65536 字节 (64 KB) 到 4194304 字节 (4 MB)

    • -1

  4. 试试看。它解决了大型数据库时超时过期的问题。

    Private Sub Command1_Click()
On Error Resume Next
Dim con As New Connection
Dim tm As String
con.CommandTimeout = 500'''Command timeout should be 500


 With con
    .ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=dbiBMS;Data Source=192.168.103.4"
    .Open
 End With
 tm = CStr(Time)

con.Execute " backup database dbiBMS to disk='E:\Database_Backup\Test1.bak' with format "

con.Close
MsgBox tm
Exit Sub
x:
MsgBox Err.Description

End Sub
    • -3

相关问题