SQL Server replication Article 3: Practice of transaction replication-publisher

SQL ServerReplicatioN Article 3: ThingsCopyPractice-create publisher

For many SQL Server DBAs, replication is not a new thing. It is also a common saying that replication is used by many friends in projects. Like other technologies: Some people can use it well, and some people complain that the technology is not good.

WeAgilesharpThe Team has also gone through a lotHigh AvailabilityIn this case, replication is still very valuable. Therefore, we sorted out a lot of resources and decided to release a series of ReplicationArticleFirst, to help everyone understand replication, and to pave the way for high availability in the future.

In the previous article, we have made a general introduction to replication and described how to configure distributor. In this article, we will describe how to configure publisher.

Publisher can be seen as the source of data production and the core of replication, because only publisher has changed, and the running of other components makes sense. As you can imagine, a publisher can have a lot of publication, that is,Publishing ServerThere can be multiple releases. In fact, this is a good understanding.

This is a good explanation of the book subscription of life: we cannot be interested in all the books of the Publishing House. We just subscribe to the books we like, the publishing house also provides a subscription server for many readers, that is, publishing many different books.

Similarly, because a publisher Publishing Server isDatabaseInstance. Many objects in this instance change, such as tables. However, many subscription servers are not interested in all these changes, but are only interested in some of them. Therefore, we can create multiple publication releases on this Publishing Server, so as to meet different subscription servers.

Each publication Release contains many aritcle (projects). We know that a project is used to identify the database objects contained in the release. A single release can contain different types of projects, including tables, views, stored procedures, and other objects. When a table is published as a project, you can use a filter to restrict the columns and rows of data sent to the subscription server.

Next, we will lead you to create a publication.

 

 

CreatePublication

To create a publication, you must first associate a publisher with a distributor. The previous article describes how to create and configure a distributor.

Who can createPublication released

It should be clear that no one can create a publication. Only users with the db_owner role can create one.

To make the database usable in replication, we must make certain configurations. We perform the following operations in the publisher (publisher) Object Manager,

20121017144149. PNG (47.93 K) 10/17/2012 2:48:18 pm

 

Right-click the replication node and start to set relevant content, such:

2. PNG(49.20 K) 10/17/2012 2:48:18 pm

 

The pop-up window contains two options. On the "General" tab, we can set the information of the user connected to the distributor. Because publisher is connected to the distributor, it must be connected by a user, here we can set the user's password and other information.

On the "Publication databases" tab, you can select which database will be used as the publishing source to publish data externally. You can also select the replication mode, such as transaction replication and merge replication.

 

 

 

Create a newPublication

After publisher is configured, you can create a publication. In fact, this step is a brief demonstration in the first article,

 

4. PNG(37.73 K) 10/17/2012 3:08:37 pm

 

 

Then, follow the step-by-step creation in the Wizard. Here we will not repeat it here. The following figure shows it directly:

 

5. PNG(31.15 K) 10/17/2012 3:08:37 pm

 

 

 

6. PNG(56.71 K) 10/17/2012 3:08:37 pm

 

 

Here is an example of transaction replication.

In addition, there is another transaction replication (transaction publication with updatable subscriptions, updatable subscription of transaction replication ).

Transaction replication supports updating through updatable subscriptions and peer-to-peer replication on the subscription server. The following describes two types of updatable subscriptions:

· Update immediately. You must connect to the Publishing Server and subscription server to update data on the subscription server.

· Queue update. You can update data on the subscription server without having to connect to the publishing server and the subscription server. It can be updated when the subscription server or the Publishing Server is offline.

When updating data on the subscription server, the data is first transmitted to the publisher and then to other subscriber servers. If immediate update is used, two-phase protocol submission will be used to disseminate changes immediately. If queuing update is used, the changes are stored in the queue. When the network connection is available, queuing transactions are asynchronously applied on the Publishing Server. Because updates are asynchronously transmitted to the Publishing Server, the Publishing Server or another subscription server may update the same data, and conflicts may occur when the application is updated. The policy detects and resolves conflicts based on the conflicts set during release creation.

If you create a transaction publication with updatable subscription in the new Publishing Wizard, immediate update and queued update are enabled at the same time. If you use a stored procedure to create a release, you can enable one or two options. When creating a published subscription, you can specify the update mode to be used. If necessary, you can switch between the two update modes in the future.

 

Articles)

After you select the publication type in the wizard above, you need to publish the objects as articles. Generally, an article refers to an object in the database. In, all objects that can be published are listed in the articles Wizard: tables, stored procedures, views, and functions.

 

7. PNG(54.86 K) 10/17/2012 3:23:51 pm

 

 

Most of the time, when we use articles to select a table, we need to release the changed data in the table. For tables, we also have more options, such as those fields, because some data in the table may remain unchanged. In this case, we only need to select fields that are often changed by data, reduce Unnecessary data transmission.

 

8. PNG(12.12 K) 10/17/2012 3:23:51 pm

 

 

In addition, you can click "aritlces properties" to set more articles options,

 

9. PNG(68.15 K) 10/17/2012 3:23:51 pm

 

 

Note that you can set the options for many articles at the same time or for a certain article.

If the selected articles contain tables, we can add some filter conditions to the table in the next step of the Wizard ,:

 

 

Snapshot

After selecting and configuring articles above, we need to consider the question of "chicken eggs, eggs, and chickens": since the data in publisher is synchronized to subscriber, in subscriber, you must first have a database that is the same as publisher before you can synchronize the changes in publisher.

The Snapshot step in the Wizard is to create a snapshot of an existing database in subscriber. the snapshot creation process can be performed immediately or at a certain time after the Wizard is complete,

11. PNG(55.62 K) 10/17/2012 4:03:06 pm

 

There are some points to note here. When a snapshot is created, the tables selected by the publication will be locked. If the tables are large, the locking process will be long, resulting in a delay in reading and writing tables, in addition, a large number of log reading and writing operations will be performed to consume a large amount of CPU, disk I/O and other resources. In addition, if the table is too large, network resources are greatly consumed during transmission, which may cause external access to the database to be blocked.

 

Security Settings

After a snapshot is set, the next step is to set the identity of the proxy process running publication.

12. PNG(46.73 K) 10/17/2012 4:28:33 pm

 

The two proxies involved in transaction replication publication are snapshot proxy and log read proxy.

Snapshot proxyThe task is to move the data in the publication to the snapshot folder and the distribution database set in the distributor. To implement these operations, the user set here must be in the db_owner role in both the database involved in the publication and the distribution database. This user must have the write permission on the snapshot folder.

Log read AgentIt is mainly used to copy data in publication to distribution, but this proxy does not need to access the snapshot folder. Therefore, the log reading proxy user only needs to be in the db_owner role in the distribution database.

We can click "security setting" for more settings,

13. PNG(53.74 K) 10/17/2012 4:28:33 pm

 

After setting, click Next until the setting is complete.

20121017162747. PNG (62.31 K) 10/17/2012 4:28:33 pm

 

Final result:

20121017162755. PNG (27.77 K) 10/17/2012 4:28:33 pm

 

Potential Problem Analysis

As you can see from the above operations, it takes many steps to create a replication application. The more steps there are, the more problems you may encounter. The most likely problem is the security settings. Therefore, users and their roles must be allocated before configuration. We recommend that you use SQL Server verification to connect.