sql server 快照_SQL Server复制：配置快照和事务复制

sql server 快照

In this article of the series on SQL Server replication, we will explore ways to configure SQL Server Snapshot replication and Transactional replication step by step.

在有关SQL Server复制的系列文章中，我们将逐步探索配置SQL Server Snapshot复制和事务复制的方法。

SQL Server Replication is one of available disaster recovery solutions which is useful to maintain or prepare a second copy or backup copy of the primary server database objects. The advantage of replication is that we do not need to replicate the entire database to another server; we can replicate specific database objects like Tables, views, and Stored procedure on another server.

SQL Server复制是可用的灾难恢复解决方案之一，可用于维护或准备主服务器数据库对象的第二个副本或备份副本。 复制的优点是我们不需要将整个数据库复制到另一台服务器。 我们可以在另一台服务器上复制特定的数据库对象，例如表，视图和存储过程。

SQL Server复制术语 (SQL Server Replication Terminology )

Publisher – It is a source database server which transfers or replicate data to another server.

发布者–它是源数据库服务器，可将数据传输或复制到另一台服务器。

Subscriber – It is server data gets replicated from the Publisher server.

订阅服务器–从发布服务器复制服务器数据。

Article – It is a database object like Tables, Views, and Stored procedures which replicates from the publisher to subscriber.

文章–它是一个数据库对象，例如表，视图和存储过程，可从发布者复制到订阅者。

快照复制 (Snapshot replication)

何时使用SQL Server快照复制 (When to use SQL server Snapshot Replication)

SQL Server Snapshot replication is useful for the databases which are not critical and/or data records of the database do not change frequently. SQL Server Snapshot replication takes a snapshot until a specific time on the Publisher server and applies to the Subscriber server.

SQL Server Snapshot复制对于不重要的数据库和/或数据库的数据记录不经常更改的数据库很有用。 SQL Server快照复制会在发布服务器上的特定时间之前拍摄快照，并应用于订阅服务器。

• Database records do not change frequently
  数据库记录不经常更改
• The database is not critical
  数据库不是关键
• The database is for reporting purposes
  该数据库用于报告目的

To configure step by step Snapshot replication, I have set-up two server node SQL1 act as a Publisher server and SQL2 act as Subscriber server.

为了逐步配置快照复制，我已经设置了两个服务器节点SQL1充当发布服务器，而SQL2充当订阅服务器。

配置发行版 (Configure the Distribution)

1. Login to the Publisher server SQL1

   登录到发布服务器SQL1

   

2. Right-click the Replication Folder and click Configure Distribution.

   右键单击“ 复制文件夹”，然后单击“ 配置分发”。

   

3. Once clicked on Configure Distribution, we got the following warning message. It states that we need to use the actual server name. The default server name is INST-INSTALL-SQ so that we will change it. To remove the actual server name warning message, we need to implement the following step.

   单击“ 配置分发”后 ，我们收到以下警告消息。 它指出我们需要使用实际的服务器名称。 默认服务器名称为INST-INSTALL-SQ，以便我们对其进行更改。 要删除实际的服务器名称警告消息，我们需要执行以下步骤。

   

4. Execute SELECT @@SERVERNAME query to get the current server name of the Publisher.

   执行SELECT @@ SERVERNAME查询以获取发布服务器的当前服务器名称。

   

5. Execute the below query to drop the current server INST-INSTALL-SQ and add the new server name as an SQL1. This step is not mandatory; we can also use current server name while connecting the SQL server instance, however for our convenience, we have changed to SQL1.

   执行以下查询以删除当前服务器INST-INSTALL-SQ并将新服务器名称添加为SQL1。 此步骤不是必须的； 我们还可以在连接SQL Server实例时使用当前服务器名称，但是为了方便起见，我们已更改为SQL1。

   

6. Once the actual server name changed to SQL1, we will able to connect the Publisher SQL server instance SQL1 and perform Step-2 again and we will able to configure Distribution wizard as the following.

   实际的服务器名称更改为SQL1后，我们将能够连接Publisher SQL服务器实例SQL1并再次执行步骤2 ，并且将能够按照以下方式配置分发向导。

   

7. Select SQL1 to configure its Distributor. It will create the Distribution database in system databases.

   选择SQL1配置其分发服务器。 它将在系统数据库中创建分发数据库。

   

8. Select the following option. Yes, configure the SQL Server Agent service to start automatically.

   选择以下选项。 是的，将SQL Server代理服务配置为自动启动。

   

9. Snapshot SQL Server replication takes a snapshot of the database and puts it into the Snapshot folder. The following step indicates the path of the Snapshot folder. The Snapshot folder path will be accessed by the Distribution Agent to apply the snapshot of the publisher database to the Subscriber. We need to use a network path for the snapshot folder path.

   快照SQL Server复制获取数据库的快照并将其放入Snapshot文件夹。 下列步骤指示Snapshot文件夹的路径。 分发文件夹将访问Snapshot文件夹路径，以将发布者数据库的快照应用于订阅服务器。 我们需要使用网络路径作为快照文件夹路径。