SQL Server database replication

SQL Server database replication
Introduction:

SQL replication can solve many problems in running database-driven applications. Because the sending/subscription mode is not easy to understand, complicated scripting languages and monitoring and replication systems also need some ideas. I hope that in the following chapters, we will try to elaborate on the basic principles and operations to facilitate your understanding.

In SQL Server, replication generates or copies data. For example, if you need to create a copy of your data or copy a change of that data, SQL replication will be useful.

Copies can be copied on the same database or remotely separated servers.

The copy and source data are synchronized in real time or within the specified time interval. The single-step synchronization method is just like bidirectional synchronization. replication can even be used to synchronize multiple datasets. Since there are so many advantages, we can't wait to start learning replication. Of course, we must first describe some basic information at the beginning, for example, how to combine basic replication components and these components to achieve replication. Come on!

Composition of replication:

SQL Server replication consists of three parts: Publishers, dealers, and subscribers. These components act on articles within the publishing and subscription servers.

Through naming, we can imagine that copying is similar to the publishing of newspapers and magazines, and you can simply understand its general process: publishing, distribution, and subscription.

Article (replicated object)

For each object to be copied, a copy article needs to be defined. Each article corresponds to a known SQLServer object or a subset of an object. The Copied object is usually a table, view, or stored procedure. You can also create multiple objects in a single article.

Publications (collection of objects)

A group of logically composed articles (replicated objects) are combined into a publication. This publication has a public definable option. The main option is the replication type.

Publisher (Publishing Server)

An SQL Server instance that provides copies is called a publisher. Publishers monitor all changed articles and notify dealers of these changes.

Distributor (distributor)

A dealer must track all subscribers and the changes of all publishers, and ensure that any change is known to every subscriber. The vast majority of changes are tracked in the distribution server. Although the distributor can act as an independent database instance, the distributor usually runs on the publisher's machine.

Subscriber (subscription server)

A subscriber can be seen as a database instance that can receive all published information through subscription.

Subscription

Subscription is relative to publishing. A subscription defines which subscription server will receive updates published by the publishing server. Each subscription creates a link between the publisher and the subscriber. There are two subscription methods: Push and Pull ).

When pushing and subscribing, the distributor directly updates the subscribed data in the database of the subscription server;

In the request subscription mode, the subscription server needs to regularly query whether there are available updates on the distribution server. If there are any available updates, the subscription server completes the data update on its own.

 

Replication type

There are three available replication types in SQLServer: Snapshot copy, merge copy, and transaction copy.

Snapshot replication

Snapshot replication creates a copy of the complete copy object and object data each operation. It uses the BCP tool of the database to write the content of each table to the snapshot folder. The Snapshot folder is a shared folder address, which must be created on the distributor when replication is started. In addition, each participant has the permission to access the snapshot copy folder and needs to be set during replication settings.

The disadvantage of this mode is that every time a snapshot is copied and run, everything needs to be done from the beginning, so it will occupy a high bandwidth and storage.

You need to know that a simple copy snapshot is used for all other types of replication during initialization settings to synchronize a copy to all subscribers and dealers.

Transaction Replication

As the name suggests, it is based on transactions. Changes to each committed transaction must be scanned into the copied article. The transaction log reading proxy scans these changes and reads the transaction logs of the published database. If there are changes that affect the published objects, these changes will be recorded in the distribution database, and then the distribution database will choose the appropriate method for sending to the subscriber.

Transaction replication can be used for close-to-real-time synchronization, leaving only some traces on the producer side. Although two-way data movement can be considered for some options, transaction replication is designed as a one-way mode at the beginning.

Merge and copy

 

Merge replication allows the Publishing Server to update the database and the subscription server to update data. These updates are merged regularly to ensure that the released data is consistent across all nodes. Therefore, it is possible that the publishing server and the subscription server have updated the same data. When a conflict arises, the publisher does not take precedence over the publishing server, but processes the conflict according to the settings, these will be discussed in subsequent articles.

 

SET transaction Replication

This part provides step-by-step guidance on how to configure transaction replication, including unilateral replication. To configure replication, the publisher, publisher, and subscriber must be configured. Replication can complete configuration and control by using the T-SQL script. However, too many complicated parameters will be used, so we will use SSMS for configuration. Here we use SQLServer2008 R2.

Configure Distribution

Distribution is the core of transaction replication. It is a prerequisite for all other components, so it needs to be configured first.

Go to SSMS, right-click the "Replication" folder on the left-side menu bar, and select Configuration allocation. 1:

Figure 1

Configure the allocation wizard

Figure 2: Configuration Wizard

Just like other software, click NEXT. Next, you want to choose whether to run the distribution service on the server or whether you already have a configured distribution server on the network. Here we choose to install the distribution service on the service by default, and then click "Next ". 3

Figure 3

The following is the dialog box. You are required to select the path of the snapshot folder. 4

Figure 4

The Snapshot folder can be placed on your machine or on the network. Of course, it is very valuable to create a network share on the distributor. In this example, the \ WIN2008A \ ReplicationSnapshotFolder address is used.

The Snapshot folder must also be authorized with appropriate permissions. There is no need to study the details here. we grant the write permission to "Authenticated Users" (figure 5) in this folder, and the read permission is granted to "Everyone" (figure 6) for sharing.

Figure 5

Figure 6

After you share and enter the network address in the Wizard's input address, click "Next" to go to the database distribution window. 7:

Figure 7

You need to specify the name of the distribution data and the location where the data and logs are stored. Click "Next" to go to the publisher window (figure 8)

In the release process, potential publishers must be prepared to use the distributors. We will install and release it on the same instance. In addition to the default options, we will directly store the store for the next step (figure 9)

Figure 8: Prepare a publisher

Figure 9

The final question is: Do you want to execute your option immediately or do you want to create a script and execute it after a while? Once again, the default configuration is used. The last time you click Next. Now you can see a task list in figure 10. Click "Finish" to start the process.

Figure 10:

In the end, the screen displays 11. Information about the progress and successful replication configuration.

Figure 11

First release

To create a release, we need to first have data that contains the table for release. Run the Code in the following script to create a test database.

USE MASTER;GOEXECUTE AS LOGIN = 'SA';GOCREATE DATABASE ReplA;GOUSE ReplA;GOIF OBJECT_ID('dbo.Test') IS NOT NULL DROP TABLE dbo.Test;GOCREATE TABLE dbo.Test(  Id INT IDENTITY(1,1) PRIMARY KEY,  Data INT CONSTRAINT Test_Data_Dflt DEFAULT CHECKSUM(NEWID()));GOINSERT INTO dbo.Test DEFAULT VALUES;GO 1000USE MASTER;GOREVERT;GO

Script 1: Create a test database and table

Now you are ready to set release.

In the SSMS Object Browser, right-click the copy folder and choose "Local Publication ". Select "New Publication..." from the drop-down menu ..."

Figure 12: New Publication

In Figure 13, the Wizard Page appears.

Figure 13: configuration Publishing Wizard

Click "Next ".

Select the database you just created in the select box for Database Release. Here is ReplA. Click Next and select the type to use. Select transaction replication and click Next in Figure 15

Figure 14:

Figure 15: select a replication type

Which object group will be selected as the published article. Figure 16. Select the table dbo. Test and click Next. Continue to filter the target row in figure 17. The Advanced content of the filter section is described in the following sections. So far, you only need to click Next to stop making other choices.

Figure 16: copying an object

Figure 17: Row Filtering

The next three steps are to process the snapshot proxy Figure 18. Select "Create a snapshot immediately" and click Next. In the "Agent Security" window (Figure 19), click "Security Settings" and select "Run under the SQL Server Agent service account" in Figure 20.

Figure 18

Figure 19

Figure 20: select an account

Click "OK" in the table and select "Next" in the Proxy Security window. Next, select "Create the publication" in Figure 21 of the task wizard ". Click Next to enter the final window. Figure 22

Figure 21: Wizard

Figure 22

Here you need to select a release name. Enter "MyFirstPublication" to enter the text and click "Finish" to start the process. The following figure shows the progress information and status 22.

Figure 23

First subscription

In most cases, the subscriber is on different machines, but to ensure the simplicity of the instance, we will choose to configure the subscription on the same instance. Use Script 2: create data ReplB

 

Use master;
GO
Execute as login = 'sa ';
GO
Create database ReplB;
GO
REVERT;
GO

Script 2: Create a target database

Now, go to the SSMS Object Browser, right-click "Local Subscriptions", and select "New Subscriptions..." 24.

Figure 24

On the welcome page of the subscriber wizard, next step

Figure 25

In Figure 26 of the "Publication" window, select the release I just created and click Next. In the "Distribution Agent Location" window, select push or pull mode subscription. For more information, see Figure 28 of the subscription window.

Figure 26

Figure 27: Push or Pull

Figure 28: target database

Select your service and ReplB database, and set "Distribution Agent Security" in the next window (figure 29 ).

Figure 29: Distribution Agent Security

Click the omitted symbol button on the right and select "Run under the SQL Server Agent service account". Figure 30

Figure 30:

Click "OK" and then click "Next" To Go To The Synchronization Schedule "window (figure 31). Select a" Run continuously "and continue to the" Initialize Subscriptions "window (Figure 32 ). by default. Then initialize immediately and click Next to go to the "Wizard Actions" window (figure 33)

Figure 31: synchronization steps

Figure 32: initialization

Figure 33: run the wizard

As before, all others are by default. click Next. To the final form (figure 34), go to the task list, click Finish to start the process, and then wait for the green sign to appear on the final page (Figure 35 ).

Figure 34: Wizard

Figure 35: Status

Successful

The dbo. Test Table created in script 1 is in database ReplA, and 1000 containing data is inserted. After the initialization is completed, the snapshot is transferred to the subscriber. You can also find that the table is also in ReplB, and there are also 1000 rows of data. After you complete the configuration, you can run Script 3 to verify that the replication pushes all data to the subscriber. Connect script 4 of two tables to show which one has been copied. You can further run the Test, insert and update ReplA. dbo. Test, and check if these changes are magic in ReplB. dbo. Test. Haha

Script 3

SELECT TOP(20) A.Id AS [ReplA.Id],A.Data AS [ReplA.Data],B.Id AS [ReplB.Id],B.Data AS [ReplB.Data] FROM ReplA.dbo.Test AFULL OUTER JOIN ReplB.dbo.Test BON A.Id = B.IdORDER BY A.Id DESC

Script 3: Compare subscriber and Publisher

Summary

An object in a database is called a publisher, which is called by a publisher marked as a copy. The article is a combination of copies. A subscriber is used to obtain updates that occur in an article and subscribe to them. Data streams are distributed by distribution databases that exist in the senders. Publisher, the publisher and subscriber can be the same instances or independent instances on the same or different machines. The source and target databases can be the same, but the distributed databases must be independent.

This article briefly introduces the concepts related to replication and the configuration and testing of simple transaction replication. Next we will learn more about the more complex replication and so on.

For more details, please continue to read the highlights on the next page:

1 2 Next Page

[Content navigation]
Page 1: Copy	Page 1: Transaction proxy job