database replication (i)--Replication Introduction

Introduction:

In a running database-driven application, SQL replication can solve many problems. Because the Send/subscribe pattern is not very easy to understand, the complex scripting language and the Monitoring replication system also need some thought in it. I hope that in the next few chapters can be as far as possible the basic principles and operations elaborated in detail, easy to understand.

In SQL Server, replication is the creation or copying of data; For example, you need to create a copy of your data, or copy a change in that data, and SQL replication comes in handy.

Replicas can be replicated in the same database or on a remote, delimited server.

The copy is kept in real time with the source data, or synchronized within the specified time interval. One-step synchronization is as feasible as bidirectional synchronization, and replication can even be used to maintain synchronization between multiple datasets. Since there are so many advantages, then we can not wait to start learning to copy it, of course, first of all to describe some basic information, such as the basic replication components and how these components together to achieve replication. Come on!

the composition of the copy:

SQL Server replication consists primarily of three components: publishers, resellers, and subscribers, which act on articles within the distribution and subscribers.

By naming we can infer that replication is much like the publication of newspapers and magazines, and can simply understand its general process: publishing-"Distribution-" subscription.

articles (copied objects)

For each object that should be copied, a copy of the article needs to be defined. Each article corresponds to a visual SQL Server object or a subset of an object. This replicated object is usually a table, a view, or a stored procedure. Of course, you can also create multiple objects in a single story.

Publications (collections of objects)

A group of logically together articles (copied objects) are blended into one publication. This publication has public, defined options, and the main option is the type of replication.

Publisher (publisher)

An instance of SQL Server that provides a copy of the publication is called a publisher. Publishers monitor all changes to the article and notify the reseller of these changes.

Reseller (distributor)

Resellers are both tracking all subscribers and tracking changes for all publishers, while ensuring that any change is known to each Subscriber. The vast majority of changes are traced to the distribution server. Although a reseller can act as a standalone database instance, the distributor typically runs on the publisher's machine.

Subscribers (subscribers)

A subscriber can be seen as a DB instance that can receive all of the published information in a subscription manner.

Subscribe

The subscription is relative to the publication, and the subscription defines which subscriber will be going to receive updates from the publisher. Each subscription creates a link between the Publisher and the Subscriber. There are two types of subscriptions, push subscriptions (push) and pull subscriptions.

In the case of a push subscription, the Distributor updates the subscribed data directly at the Subscriber database;

In the mode of pull subscriptions, subscribers are required to periodically query the Distributor for available updates, and if there are any available updates, the subscriber finishes updating the data itself.

Types of replication

There are mainly three types of replication available in SQL Server: snapshot replication, merge replication, and thing replication.

snapshot replication

Snapshot replication creates a copy of the full copy object and object data for each run. It uses the bcp tool of the database to write the contents of each table into the snapshot folder. The snapshot folder is a shared folder address that must be built at the distributor when the replication is initiated. And each participant is a folder that has access to the snapshot replication and needs to be set when the replication is set.

The downside to this pattern is that every snapshot copy runs, everything starts all over again, so it consumes very high bandwidth and storage.

It is necessary to understand that all other types of replication use a simple copy snapshot when initializing settings to synchronize to all subscribers and resellers.

Transactional Replication

As the name implies, it is based on business. Changes to each committed transaction are scanned into the copied article. The transaction log Read agent scans for these changes and reads the transaction log of the publication database. If there are changes affecting the published object, then these changes will be logged in the distribution database, and then the distribution database can be sent to the subscribers in the appropriate manner.

Transactional replication can be used as near-real-time synchronization while leaving only a few traces at the publisher. Although there are some options to consider using bidirectional data movement, transactional replication is designed to be a one-way mode at the outset.

merge Replication

Merge replication allows the Publisher to update the database and also allows subscribers to update the data. These updates are periodically merged so that the published data remains consistent across all nodes. As a result, it is possible that the Publisher and Subscribers update the same data, and when conflicts arise, they are not handled exactly as the publisher takes precedence over the conflicts, but are processed according to the settings, which are described in subsequent articles.

set up transactional replication

This section is a step-by-step guide on how to configure transactional replication to include single-sided replication. In order to configure replication, the Distributor, Publisher, and Subscriber are required to be configured. Replication can be fully configured and controlled by using T-SQL scripts. But it will be used to sing too many tedious parameters, so we will configure it by using SSMS. Here we use the SQLServer2008 R2 version.

Configure Distribution

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

Go to SSMs and right-click on the "Replication" folder in the left menu bar, select Configuration Assignment, 1:

Figure 1

Configuring the Assigned Wizard

Figure 2: Configuration Wizard

As with other software, next. Next you want to choose whether to run the distribution service on this server or if you already have a configured distributor on the network. Here we choose the default to install the distribution service from the service, and then click "Next". 3

Figure 3

The next step is the dialog box, which asks you to select the path to the snapshot folder. 4

Figure 4

The snapshot folder can be placed on your machine or on the network. Of course, it is valuable to create a network share on the distribution server. The example here is the use of this \\WIN2008A\ReplicationSnapshotFolder address.

The settings for the snapshot folder also require the appropriate permissions to authorize. There is no need to go too far to study the details, we grant Write permission to "authenticated Users" (Figure 5) on this folder, the Read permission is granted to "Everyone" (Figure 6) share.

Figure 5

Figure 6

After you share and enter the network address into the wizard's input address, click Next to go to the window where you distributed the database. 7:

Figure 7

You need to make the name and data of the distribution data and where the logs are placed. Other default options are good, click "Next" to go further to the Publisher window (Figure 8)

You need to prepare a potential publisher to use the Distributor at the release stage. Let's install the release on the same instance, here in addition to the default options direct store next to the last link (Figure 9)

Figure 8: Preparing the Publisher

Figure 9

The final question is whether you want to go to the immediate execution of your options or do you want to go to create a script after some time in execution? Once again, the default configuration, last click Next. Now you can see a list of tasks in Figure 10. Click "Finish" to open this process

Figure 10:

Finally, this screen will show 11. Gives information about the progress and the success of the replication configuration.

Figure 11

First time release

To create a publication, we need to first have a data containing the table to publish. Execute 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 a NULL DROP TABLE dbo. Test; Gocreate TABLE dbo. Test (  Id int IDENTITY (PRIMARY) KEY,  Data INT CONSTRAINT test_data_dflt DEFAULT CHECKSUM (NEWID ())); Goinsert into dbo. Test DEFAULT VALUES; GO 1000USE MASTER; Gorevert; GO

Script 1: Creating a test database and tables

Now you're ready to set up the release.

Open the Replication folder in the SSMs Object Browser right-click on "Local Publication". Select "New Publication ..." from the drop-down menu.

Figure 12:new Publication

In Figure 13, the wizard interface appears

Figure 13: Configure Publishing Wizard

Click "Next".

In the Publish Database selection box, select the database you just created, I'm Repla here, click Next, select the type of quota replication you want to use. Select Transactional replication, click Next in Figure 15

Figure 14:

Figure 15: Selecting a replication type

Now you will choose which object group to use as the published article. Figure 16. Select Table Dbo.test Click Next and continue to filter the marked lines in Figure 17. The advanced content of the filter section is described in the next section. So far just click next to no longer make other selections.

Figure 16: Copying objects

Figure 17: Row filtering

The next three steps are to process the Snapshot Agent Figure 18, select "Create a snapshot immediately" and click Next. Click the "Security Settings" button in the "Agent Security" window (Figure 19) 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 is the Task Wizard figure 21, select "Create the publication". Click Next to go to the last window in Figure 22

Figure 21: Wizard

Figure 22

In this you need to select a name for the publication. Enter "Myfirstpublication" to enter the text click "Finish" to open this process. Here is the progress information and status diagram 22

Figure 23

First subscription

In the vast majority of cases, subscribers are on different machines, but in order to keep the instance simple 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: Creating the target Database

Now we go to SSMs Object Browser right-click on "Local Subscriptions" and select "New subscriptions ..." 24

Figure 24

In the Subscriber Wizard welcome interface, and then the next step

Figure 25

In the "Publication" window, figure 26, select the publication I just created, and click Next. In the Distribution Agent Location window, select the subscription for push or pull mode. Other default go to subscription window Figure 28.

Figure 26

Figure 27:push or pull

Figure 28: Target Database

Here you need to select your service and select the REPLB database, and the next window will set up "Distribution Agent Security" (Figure 29).

Figure 29:distribution Agent Security

Click the Small ellipsis button on the right and select "Run under the SQL Server Agent service account" in the newly opened window in Figure 30

Figure 30:

Click "OK" and then click Next, go to Synchronization Schedule "window (Figure 31), select a" Run continuously "and proceed to the" Initialize Subscriptions "window (Figure 32). The default is OK. Then initialize immediately and click Next to the "Wizard Actions" window (Figure 33)

Figure 31: Synchronization steps

Figure 32: Initialization

Figure 33: Execution Wizard

As before, the rest is the default, click Next. To the final form (Figure 34), the Task list, click Finish to start the process, and then wait for the green success flag to appear on the last page (Figure 35).

Figure 34: Wizard

Figure 35:status

Success

Script 1 creates the dbo. The Test table is repla in the database, and 1000 contains data is inserted. When the initialization completion snapshot is transferred to the Subscriber, it can also be found here that the table is also in REPLB, and there are 1000 rows of data. After you have completed the configuration you can run script to verify that the replication pushes all the data to subscribers. A script that connects two tables 4来 shows which one was copied. Ability to run tests further, insert and update ReplA.dbo.Test and see if these changes magically appear in ReplB.dbo.Test, haha.

Script 3

SELECT TOP (a.id) as [repla.id],a.data as [repla.data],b.id as [replb.id],b.data as [Replb.data] from ReplA.dbo.Test AFU LL OUTER JOIN ReplB.dbo.Test BON a.id = B.idorder by a.ID DESC

Script 3: Comparing subscribers and Publishers

Summarize

The object in the database is called the publisher, which is called the article by the publisher who is marked as a copy. The article is a combination of replication. Subscribers are the way to get updates that occur in the article through subscriptions. The data flow is distributed through the distribution database that exists in the Distributor. Publishers, distributors and subscribers can be the same instance, or they can be standalone instances on the same or different machines. The source and destination databases can be the same, but the distributed database must be independent.

This brief explains the replication-related concepts and the configuration and testing of simple transactional replication. Next we'll look at more complex replication scenarios.

database replication (i)--Replication Introduction