SQL Server Replication (Replication)--Transactional replication setup

This article demonstrates how to build the most basic transactional replication.

Environment Preparation: 2 virtual machines:

The server names are Repa and Repb,repa are publishers, and REPB are subscribers. Are installed WindowsServer 2008r2 English version (the reason for working in a foreign company). and ensure that the two servers can exchange visits.

SQL Server:

In the above virtual machine, install SQL Server R2 x64 English Enterprise Edition separately.

Demo Database:

Microsoft Sample Database AdventureWorks2008 R2

Configuring Transactional Replication

The copied configuration can be implemented using T-SQL, but the required T-SQL and stored procedures may be hundreds, so here is the SSMs operation to demonstrate.

The configuration of transactional replication requires that both the Publisher, Distributor, and Subscriber be configured. This example uses a scenario where both the publishing and distribution servers are on one instance:

Here is the topology diagram:

Step one: Check whether the database has been involved in the replication process, this step in order to ensure that the replication of a database to avoid duplication configuration:

[SQL]View Plaincopyprint?

2. Use master
4. Go
6. SELECT Name,
8. Caseis_published
10. WHEN0 then N' not released '
12. ELSE N' published '
14. END N' publish ',
16. Caseis_subscribed
18. WHEN0 then N' not subscribed '
20. ELSE N' subscribed '
22. END N' whether to subscribe '
24. From sys.databases
26. WHERE name = ' adventureworks2008r2 '

Step Two: Configure the distribution server:

The distribution server is the core of transactional replication. The operation of other components must be based on the availability of the distribution server. Therefore, it is the primary configuration item. First login to SSMs, this is Repa. Then right-click:

This is an instance-level configuration that can be configured separately on separate instances, but for cost reasons, many real-world environments don't, so I also deploy distributions and releases on the same instance. The configuration steps are simple:

Step 2.1: Configure the Distribution Wizard:

This step directly skip is also no problem, simply say, if you choose to distribute the same instance, select the above option, if it is a separate distribution instance, select the following option. Because this example uses the same instance of distribution and publishing, click Next here.

Step 2.2: Configure the Distribution Agent:

From the description, to configure whether the SQLServerAgent is automatically run after the machine starts, as a formal application, there is little reason to choose to start manually, so go directly to the next step. Are you curious as to why there is a clickable "Finish" here? Because most of the Microsoft products are already more automated, so there are actually some configuration defaults, readers can directly point to "Finish" to see the results, if there is no further configuration, SQL Server will default to use the following path to hold the distribution data:

Let's go back to the previous step.

Step 2.3. Snapshot folder:

As mentioned in the previous article, all types of replication are snapshot-based, and snapshots in transactional replication are used for initialization, so all replication types are configured with snapshot folders, and each time it is initialized, replication takes a snapshot of the data that is configured as a publishing item in the Publisher and then stores it in this folder. The processing of this folder's data is done based on pull or push mode.

Need to explain is this is "shared folder", because the subscriber needs to read from this folder, so this folder at least to allow replication involved in the account has read permissions, for the publisher, but also need to write permissions.

This snapshot folder can be any path to this machine or a network path, and typically this path should be created on a network share disk at the Distributor, in this case at the publisher. For example, use the path below, and then give the appropriate permissions. In the example, I used to create a folder called Replicationsnaeshotfolder under the C drive, and then empower, create a user group called "Authorized User" (AuthenticatedUsers), and then give the Write permission to add " Everyone ", granting Read permissions.

We then use the path here as the shared folder path for the snapshot:

Step 2.4. Configure the distribution database:

This step is mainly to configure the distribution database in which place, due to the limitations of the demo environment, are placed on the C drive, if possible, suggest separate, at least do not put on the C disk, system disk, you understand.

Step 2.5: Configure the publisher (publisher):

Here you can configure the account password used by the Distributor, and other mixed items, the impersonatethe Agent process account refers to the account used by the SQLAgent as the connection issuer.

Alternatively, you can specify whether to publish SQL Server or Oracle.

Step 2.6: Configuration complete

Here you can choose to directly configure or just generate scripts, it is recommended to choose both, so that if there is a problem, the script can be rebuilt very quickly. The last point is "done" and let SQL Server configure it itself.

Finally, you can see that a new database appears under the System database folder:

Step Three: Configure Publishing

3.1. Release (Publication):

This is a noun, as mentioned earlier, is a set of logical collections of items (articles), each of which can contain one or more items. A configuration item in a publication affects all of the items it contains, which in this way can reduce the impact of managing objects. The most important configuration item is the type of replication. Since this article uses the Microsoft sample database, there is no need to create a test database here.

3.2. New Release:

Open the section in the diagram to configure the publishing item:

3.3. Publish the database:

Here is the choice of the database you want to publish.

3.4. Type of replication:

Here we choose a purely transactional publication, and for the third option, it is actually "peer-to release", which is demonstrated in subsequent articles.

3.5. Select a Publishing object (article)

We're looking for a few, but here's to note that transactional replication requires a primary key on the table, and I randomly create a simple table with no primary key, which you can see when you choose:

To add, when choosing a table here, you can specify some columns, or you can publish them all in the table. But even if you don't check the primary key column, the primary key column is automatically selected as long as you select some of the columns in the table.

3.6. Filter table Data

The previous step is to filter the columns, this step is to filter the rows, you can filter the data in the columns you have selected, and then click Next:

3.7. Configuring the Snapshot Engine

Here, you can choose to create a snapshot immediately after the configuration is complete and keep the snapshot before the subscription is initialized, or you can choose to schedule a specific time to run, such as when the system is idle. can also not be checked. In many cases, the first item will be selected, but I suggest that it is all unchecked, especially for large databases. This section is introduced again in the following article, where only the demo is done.

3.8. Agent Security

This article chooses to borrow the SQLAgent to run, for security, usually creates the dedicated domain account or the local account (non-domain environment) and the secure password, so does not need to change frequently.

3.9. Complete the release:

Here, as with the configuration distribution, you can save the script.

3.10. Define the name of the publishing item:

This name is the name that identifies each item, and here I use firstpublication as the name of the published item

Step 4: Configure the Subscription

Subscribers can actually be on the same machine, but in practice, it's usually a separate server, so there's a single server here, and after the release, the next step is to configure the subscription to receive information from the release:

First of all to ensure that the two machines can interconnect, in addition, the replication technology requires the use of the server name, if the use of IP address or alias, will be error messages, errors are as follows, so you need to use the server name to log in, in this case using REPB login:

4.1. Configure Subscription items

Now connecting to Subscribers, this example uses REPB, where the steps are basically the same as configuring the Publish item

4.2.

Here is a need to remind, if two servers are not in the domain, due to DNS resolution problems, you may need to modify the host file, the path in:

C:\Windows\System32\drivers\etc\host the IP of the other server can be added:

In addition, firewalls and other problems are to be pre-processed.

After you connect, you can see the publishing items, such as:

4.3. Select Distribution Agent Location

In fact, the choice of subscription type, in the first article has mentioned their differences, here Select Pull mode:

4.4. Select Subscribers

In fact, the subscription database, where the drop-down box can choose a new library, or an existing library, if you choose a new library, and the cubby is large, the initialization time will be long, so for the first deployment, and is a large library, it is recommended to first restore the release library to the local, so reduce the amount of data to be synchronized. Because it is a demo library, I create a new library with a different name, and the library names can be the same.

Here to create a new library called Adventureworks_rep, note the red box in Figure 32, and sometimes the error in the copy is not filled out here, if the permission requirements are not very strict, you can fill in SA.

4.5. Subscription Agent Security:

Here you can choose a dedicated account, you can also use SQL Server proxy account, for the sake of simplicity, here Select the proxy account, but for the formal environment, or recommend the use of dedicated accounts:

4.6. Synchronization schedule

Here you can choose whether to run continuously or on demand, because it is transactional replication, we actually want to be able to synchronize as much as possible, if not, in fact, merge replication is OK, so we choose to run continuously here:

To initialize a subscription:

You can choose to initialize immediately or initialize at the first synchronization, where you choose to initialize now, then click Next

Because there is no initialization at the time of configuration publishing, you can now see that there are no tables in the subscription library

We only need to initialize the publish item.

Errormessages:

· Theprocess could not execute ' sp_replcmds ' on ' Repa '. (Source:mssql_repl, errornumber:mssql_repl20011)
Get help:http://help/mssql_repl20011

· Cannotexecute as the database principal because the principal "dbo" doesnot exist, this type of principal cannot is impers Onated, or do not havepermission. (Source:mssqlserver, Error number:15517)
Get help:http://help/15517

The process could not execute ' sp_replcmds ' on ' Repa '. (Source:mssql_repl, Error number:mssql_repl22037)
Get help:http://help/mssql_repl22037

When you are finished, you can see that there are already subscriptions in your local subscription:

In the process of configuration, we may encounter various problems, but most of the problems can be solved, and many problems are caused by a root cause, such as the following two error:

Error One:

English Description:

Cannot execute as the database principal because Theprincipal "dbo" does not exist, this type of principal cannot beimpers Onated, or do not have permission.

English Description:

cannot be executed as a database principal because the principal "dbo" does not exist, cannot impersonate this type of principal, or you do not have the required permissions.

The database context has been changed to ' AdventureWorks2008 '. (Microsoft SQL Server, error : 15517)

Question 2:

SQL Server replication failing with:process couldnot execute ' sp_replcmds '

These two issues may be caused by other reasons, but usually because the database owner of the publication is not filled in, you can check with the owner of the file, database properties, if you have no words, fill in the SA and try again.

SQL Server Replication (Replication)-Transactional replication setup