53、SQL Server 数据复制全流程指南

SQL Server 数据复制全流程指南

在数据库管理中，数据复制是一项关键技术，它允许我们在不同的数据库服务器之间同步数据。本文将详细介绍 SQL Server 中的数据复制，包括异构复制、设置复制环境、创建和订阅事务性发布，以及如何测试复制功能。

异构复制

并非所有的复制都发生在 SQL Server 之间。有时，我们需要将数据复制到 Sybase、Oracle、Access 或其他数据库服务器上。异构复制就是将数据从 SQL Server 复制到其他类型数据库系统的过程。在这种情况下，订阅者必须符合开放数据库连接性（ODBC）标准。如果目标符合 ODBC 标准，它就可以接收来自 SQL Server 的推送订阅。如果需要从 SQL Server 拉取订阅到第三方数据库系统，则需要编写一个自定义程序来访问 SQL - DMO（分布式管理对象）。

设置复制环境

设置和配置复制需要几个步骤，主要包括准备分发服务器、发布服务器和订阅服务器。
1. 分发服务器 ：分发服务器负责收集发布服务器的更改并将其复制到订阅服务器。它的工作负载可能很大，特别是当服务多个发布服务器和订阅服务器时，因此需要为其分配足够的内存（约 256MB）。发布服务器的更改存储位置取决于复制类型：事务性复制的更改存储在分发数据库中；其他类型的更改存储在分发工作目录（ \\distribution_server\Program Files\Microsoft SQL Server\MSSQL$(instance)\REPLDATA ）。同时，要确保有足够的磁盘空间来处理系统中的所有更改。
2. 配置分发服务器 ：
- 打开企业管理器（从“开始”菜单的“程序”中的 SQL Server 2000 程序组中选择）。
- 选择默认的 SQL Server 实例，然后在“工具”菜单中，指向“复制”并单击“配置发布、订阅服务器和分发”，启动“配置发布和分发向导”，点击“下一步”。
- 在第二个屏幕上，选择分发服务器，这里使用本地服务器，选中“使‘服务器’成为其自己的分发服务器”单选按钮，然后点击“下一步”。
- 询问是否自定义分发服务器属性，建议选择“是，让我设置分发数据库属性”，然后点击“下一步”。
- 提供分发数据库的相关信息，包括名称、数据文件位置和事务日志位置。为了便于恢复，最好将数据文件和事务日志放在不同的物理硬盘上，本次练习接受所有默认值，点击“下一步”继续。
- 启用发布服务器，选择主服务器和 SECOND 服务器（如果没有 SECOND 服务器，请参考附录 B）。
- 启用 SECOND 服务器时，会提示提供 SECOND 的安全信息，以便它可以登录到分发服务器。确认相关选项后，点击“确定”：
- 分发数据库已自动填充，这是发布服务器将使用的数据库。
- 快照文件夹是分发工作文件夹，快照在等待分发时存储在此处。
- 复制代理必须登录到 SQL Server 才能使用它作为分发服务器，默认情况下，复制代理使用 SQLServerAgent 服务的账户，也可以为复制代理创建一个 SQL Server 登录。本次接受默认的模拟方式。
- 为了更安全，可以要求外部服务器连接到分发服务器时使用密码，本次选择不使用密码。
- 点击“确定”继续。
- 选择要配置复制的数据库，并选择复制类型（事务性或合并复制，或两者），这里勾选 Northwind 旁边的“Trans”和“Merge”，然后点击“下一步”。
- 选择要配置为该发布服务器的订阅服务器，选择两个服务器，然后点击“下一步”。
- 查看所选内容，如果正确，点击“完成”配置复制。
- SQL Server 将显示正在执行的任务列表，完成后会通知成功，点击“确定”。
- 此时会出现一个对话框，告知有一个新工具“复制监视器”，稍后会讨论，现在点击“确定”。
3. 验证复制是否启用 ：
- 在企业管理器中，展开服务器，然后展开“数据库”，点击“分发”，检查内容窗格（右侧）中显示的数据库属性。
- 查看树视图窗格（左侧），应该会看到 Northwind 数据库下有一个小手图标，表示它已共享用于复制。

创建和订阅事务性发布

接下来，我们将配置一个事务性发布，让 SECOND 服务器订阅该发布，并测试数据复制。
1. 创建事务性发布 ：
- 如果不在企业管理器中，从“开始”菜单的“程序”中的 SQL Server 2000 程序组中选择打开。
- 在内容窗格中点击默认服务器（不是 SECOND），从“工具”菜单中选择“复制”，然后点击“创建和管理发布”。
- 从“创建和管理发布”对话框中，选择 Northwind 并点击“创建发布”，启动“创建发布向导”。
- 在向导的第一个屏幕上，勾选“显示高级选项”旁边的框，然后点击“下一步”。
- 选择 Northwind 作为要发布的数据库，然后点击“下一步”。
- 选择复制类型为“事务性”，然后点击“下一步”。
- 询问是立即更新事务性复制还是允许排队更新，确保两个框都未选中，然后点击“下一步”：
- 立即更新订阅允许用户对其本地数据副本进行更改，并使用 Microsoft 分布式事务协调器将更改发送回发布服务器。这需要可靠的网络连接，因为如果无法联系到发布服务器接受更改，订阅者的更改将被回滚。
- 排队更新允许用户对复制的数据副本进行更改，但这些更改可以存储在中间主机（数据库或 Microsoft 消息队列 [MSMQ]）中，直到可以传输。这在客户端网络连接不可靠时非常有用。
- 如果都不选择，订阅将对复制数据的用户视为只读。
- 决定是否允许某些订阅者使用数据转换服务技术转换数据，这里选择“否”，然后点击“下一步”。
- 询问订阅者将运行的数据库系统，由于仅在 SQL Server 2000 之间复制，保留默认选择，然后点击“下一步”。
- 选择要发布的文章，在“对象类型”下保留默认的“表”已选中，然后在对话框右侧勾选“Products”旁边的框以启用发布。
- 点击“Products”表旁边的省略号按钮，打开文章的属性。
- 在“表文章属性”对话框的“常规”选项卡上，将“目标表名”更改为“Repl_Products”。
- 选择“快照”选项卡，这里设置初始快照的属性，每个发布都有一个初始快照来初始化订阅者，保留默认值，然后点击“确定”。
- 点击“下一步”继续，会看到一条警告消息，告知 IDENTITY 属性将不会被复制，这是为了确保订阅者的数据是发布者数据的副本，点击“下一步”继续。
- 选择发布名称和描述，在“发布名称”框中输入“Northwind Products”，保留自动填写的描述，不勾选在活动目录中列出发布，然后点击“下一步”。
- 询问是否进一步自定义发布，允许匿名订阅者或添加分区，选择“是”，然后点击“下一步”。
- 询问是否对某些文章中的数据进行水平或垂直过滤，勾选“垂直”旁边的框，然后点击“下一步”。
- 询问是否从文章中删除任何列（垂直分区），取消勾选“Discontinued”旁边的框，然后点击“下一步”（必须复制主键列，因此不能取消选择它）。
- 询问是否允许匿名订阅者访问发布，如果选择“否”，所有订阅者必须在企业管理器中注册；如果选择“是”，任何服务器都可以订阅数据。如果打算使用拉取订阅，选择“是”，这里选择“是”，然后点击“下一步”。
- 可以更改创建快照以刷新订阅者的计划，使用默认计划，然后点击“下一步”。
- 在最后一个屏幕上，点击“完成”创建发布。
- SQL Server 将显示创建发布必须完成的任务列表，完成后会通知成功，点击“确定”。
2. 推送订阅 ：
- 如果打开了属性对话框，请关闭它，确保在“创建和管理发布”对话框中。
- 选择 Northwind 下的“Northwind Products”图标，点击“推送新订阅”按钮。
- 在“推送订阅向导”的欢迎屏幕上，勾选“显示高级选项”旁边的框，然后点击“下一步”。
- 选择订阅服务器，选择 SECOND，然后点击“下一步”。
- 选择目标数据库，由于更改了目标表名，可以使用 Northwind 作为目标数据库，然后点击“下一步”。
- 决定代理运行的位置，如果有很多订阅者，选择“订阅者”；如果只有少数订阅者，选择“分发者”（更容易管理），这里选择在分发者上运行代理，然后点击“下一步”。
- 决定何时将更改复制到订阅者，选择默认的“连续”，即每次发生更改时都复制，然后点击“下一步”。
- 询问是否在订阅者处初始化架构，如果是复制到新数据库或尚未在订阅者上创建表，应该初始化架构，这里选择“是”（因为在订阅者处创建新表），然后点击“下一步”。
- 确保发布者上的 SQLServerAgent 服务正在运行，点击“下一步”继续。
- 在最后一个屏幕上，点击“完成”推送订阅。
- SQL Server 将显示推送订阅必须执行的任务列表，完成后会通知成功，点击“确定”。
3. 测试复制 ：
- 从“开始”菜单的“程序”中的 SQL Server 2000 程序组中选择打开第一个查询分析器，登录到主服务器（以下称为“第一个”）。
- 打开另一个查询分析器，在“服务器名称”框中输入“server_name\SECOND”，登录到 SECOND 服务器（以下称为“第二个”）。
- 在第二个查询分析器中，输入并执行以下代码以验证复制是否成功：

USE Northwind
SELECT * FROM Repl_Products

- 切换到第一个查询分析器，输入并执行以下代码向原始表添加一条记录：

USE Northwind
INSERT Products
VALUES ('Wool Blankets',1,1,1,$10.00,10,10,1,0)

总结

通过以上步骤，我们完成了 SQL Server 数据复制的整个流程，包括异构复制的概念、复制环境的设置、事务性发布的创建和订阅，以及复制功能的测试。希望这些内容能帮助你更好地管理和维护数据库中的数据复制。

以下是创建和订阅事务性发布的流程图：

graph LR
    A[打开企业管理器] --> B[选择默认服务器，点击创建和管理发布]
    B --> C[选择 Northwind 并创建发布]
    C --> D[勾选显示高级选项]
    D --> E[选择 Northwind 为发布数据库]
    E --> F[选择事务性复制]
    F --> G[选择更新方式]
    G --> H[决定是否允许数据转换]
    H --> I[选择订阅者数据库系统]
    I --> J[选择发布文章（Products）]
    J --> K[设置文章属性]
    K --> L[设置发布名称和描述]
    L --> M[自定义发布（匿名订阅、分区等）]
    M --> N[设置过滤条件]
    N --> O[完成创建发布]
    O --> P[推送订阅]
    P --> Q[选择订阅服务器（SECOND）]
    Q --> R[选择目标数据库（Northwind）]
    R --> S[决定代理运行位置]
    S --> T[决定复制时间]
    T --> U[初始化架构]
    U --> V[确保服务运行]
    V --> W[完成推送订阅]
    W --> X[测试复制]

以下是设置复制环境的主要步骤表格：
|步骤|操作|
| ---- | ---- |
|1|打开企业管理器|
|2|选择默认 SQL Server 实例，启动配置发布和分发向导|
|3|选择分发服务器（本地服务器）|
|4|自定义分发服务器属性|
|5|提供分发数据库信息|
|6|启用发布服务器|
|7|提供 SECOND 服务器安全信息|
|8|选择要复制的数据库和复制类型|
|9|选择订阅服务器|
|10|完成配置复制|
|11|验证复制是否启用|

SQL Server 数据复制全流程指南

数据复制类型及特点分析

在进行 SQL Server 数据复制时，了解不同复制类型的特点对于选择合适的复制方案至关重要。以下是几种常见复制类型的详细分析：
|复制类型|特点|适用场景|
| ---- | ---- | ---- |
|事务性复制|将事务按顺序从发布者复制到订阅者，保证数据的一致性和顺序性。对于需要实时更新数据的场景非常有用，如金融交易系统。|数据更新频繁，需要实时同步的场景，如银行交易记录、股票行情数据等。|
|合并复制|允许订阅者在离线状态下进行数据更改，然后在连接到网络时将这些更改合并到发布者和其他订阅者。适用于移动设备和远程办公场景。|移动设备或远程办公场景，如销售人员使用移动设备记录销售数据，在有网络时同步到总部数据库。|
|快照复制|定期创建数据的快照，并将其分发到订阅者。适用于数据更新不频繁的场景。|数据更新不频繁，如产品目录、静态配置数据等。|

复制过程中的常见问题及解决方法

在数据复制过程中，可能会遇到各种问题，以下是一些常见问题及解决方法：
1. 复制失败 ：
- 原因 ：网络连接不稳定、分发服务器配置错误、安全信息不正确等。
- 解决方法 ：检查网络连接，确保分发服务器和发布服务器、订阅服务器之间可以正常通信；检查分发服务器的配置，确保其有足够的资源和权限；检查安全信息，确保发布服务器和订阅服务器可以正确登录到分发服务器。
2. 数据不一致 ：
- 原因 ：复制过程中出现错误、订阅者和发布者的数据更新冲突等。
- 解决方法 ：检查复制日志，查找错误信息并进行修复；使用合并复制时，可以设置冲突解决策略，如以发布者为准或以订阅者为准。
3. 性能问题 ：
- 原因 ：分发服务器负载过高、订阅者处理能力不足等。
- 解决方法 ：优化分发服务器的配置，如增加内存、磁盘空间等；合理分配订阅者的任务，避免订阅者处理过多的数据。

复制的安全设置

为了确保数据复制的安全性，需要进行一系列的安全设置：
1. 身份验证 ：复制代理必须登录到 SQL Server 才能使用它作为分发服务器，默认情况下，复制代理使用 SQLServerAgent 服务的账户，也可以为复制代理创建一个 SQL Server 登录。建议使用强密码，并定期更换密码。
2. 加密 ：在传输数据时，可以使用 SSL 加密来保护数据的安全性。在 SQL Server 中，可以通过配置 SSL 证书来实现数据加密传输。
3. 访问控制 ：只允许授权的服务器和用户进行数据复制操作。可以通过设置防火墙规则、用户权限等方式来实现访问控制。

复制的监控和维护

为了确保数据复制的正常运行，需要对复制过程进行监控和维护：
1. 使用复制监视器 ：复制监视器是一个强大的工具，可以实时监控复制的状态、进度和性能。通过复制监视器，可以及时发现和解决复制过程中出现的问题。
2. 定期备份 ：定期备份分发数据库和订阅数据库，以防止数据丢失。可以使用 SQL Server 的备份和恢复功能来实现数据备份。
3. 性能优化 ：定期检查分发服务器和订阅服务器的性能，如 CPU 使用率、内存使用率、磁盘 I/O 等。根据检查结果，对服务器进行优化，以提高复制性能。

未来发展趋势

随着数据库技术的不断发展，SQL Server 数据复制也将迎来新的发展趋势：
1. 云计算集成 ：越来越多的企业将数据库迁移到云端，SQL Server 数据复制也将与云计算平台进行更紧密的集成，如与 Azure 云服务的集成。
2. 自动化管理 ：未来，数据复制的管理将更加自动化，如自动配置、自动监控和自动修复等功能。
3. 大数据支持 ：随着大数据技术的发展，SQL Server 数据复制将支持更大规模的数据复制，如处理 PB 级别的数据。

以下是复制过程中常见问题及解决方法的流程图：

graph LR
    A[复制失败] --> B[检查网络连接]
    B --> C{连接正常?}
    C -- 是 --> D[检查分发服务器配置]
    C -- 否 --> E[修复网络问题]
    D --> F{配置正确?}
    F -- 是 --> G[检查安全信息]
    F -- 否 --> H[修复配置问题]
    G --> I{信息正确?}
    I -- 是 --> J[查找其他原因]
    I -- 否 --> K[更新安全信息]
    A[数据不一致] --> L[检查复制日志]
    L --> M{有错误信息?}
    M -- 是 --> N[修复错误]
    M -- 否 --> O[设置冲突解决策略]
    A[性能问题] --> P[检查服务器性能]
    P --> Q{性能正常?}
    Q -- 是 --> R[查找其他原因]
    Q -- 否 --> S[优化服务器配置]

以下是复制的安全设置和监控维护的操作步骤列表：
1. 安全设置 ：
- 为复制代理创建 SQL Server 登录，并使用强密码。
- 配置 SSL 证书，实现数据加密传输。
- 设置防火墙规则，只允许授权的服务器和用户进行数据复制操作。
2. 监控维护 ：
- 定期使用复制监视器监控复制的状态、进度和性能。
- 定期备份分发数据库和订阅数据库。
- 定期检查服务器的性能，根据检查结果进行优化。

通过对 SQL Server 数据复制的深入了解，我们可以更好地选择合适的复制方案，解决复制过程中出现的问题，确保数据的安全性和一致性。同时，关注数据复制的未来发展趋势，将有助于我们更好地应对不断变化的数据库管理需求。