Replication often plays an important part in your database management strategy. An organization may use database replication for load balancing, offline processing, redundancy, and other typical administrative tasks. This blog post shows you how to configure replication between two Microsoft SQL Server 2005 systems using the SQL Server Management Studio.

First, you will need to connect the database engine that contains the data you wish to replicate in SQL Server Management Studio. From there, you will need to right-click on the Replication folder (Figure A) and select Configure Distribution.

Figure A

Replication folder

Next, you will specify how you want to select your distribution server. (Figure B) In most small replication environments, using the server that hosts the data as your distributor is fine. However, if you are replicating large amounts of data on very busy servers, you may wish to set up a dedicated box to be your distribution system. This individual distribution system can be the distributor for multiple SQL Servers if you have that need.

Figure B

Distribution server

You will need to have your SQL Server Agent service configured to start automatically. (Figure C) This is due to the SQL Agent being responsible for managing subscriptions. To configure this through this wizard, the account running the SQL Server Service must have administrator privileges on the computer. If it does not, anyone with administrator privileges can set the SQL Server Agent account to run automatically.

Figure C

SQL Server Agent

Next, you will need to set your snapshot folder. (Figure D) This folder will contain a snapshot (a collection of files, which contain all of the data you are choosing to replicate initially). This folder will need to have sufficient space to contain all the data you will be replicating. If you are only going to be pushing your subscriptions to subscribers, a local path is fine. If your subscribers will be pulling data, you will need to place your snapshot on a network path that is accessible by both systems.

Figure D

Snapshot folder

The settings for distribution database (Figure E) must be determined by the amount of data you will be replicating and the frequency of the replication. The database (by default, named distribution) can be large (if you replicate sporadically) and highly active (if you have a lot of activity in replicated data). Be careful with your placement of this database to make sure you have sufficient storage and that it will not negatively impact disk subsystem performance on your server.

Figure E

Distribution

Now that the distribution aspect is set, you can select the data you would like to publish. To do this, right-click on Local Publications and select New Publication. (Figure F)

Figure F

Local Publication

You will then need to select the database you wish to replicate data from. (Figure G) You will see all of your user databases in this window and simply need to select the database that contains your data and click next.

Figure G

Publication database

Now, you need to select the tables that contain the data you wish to replicate. (Figure H) You can replicate data only from tables that have a primary key. If you data does not have a primary key, you will need to create one before this step. You can choose to replicate a whole table (or tables) worth of data.

Figure H

Select tables

You can also choose to replicate only certain columns from a table. (Figure I) This is useful if you need just a subset of your data to populate a Web-based or other application. Doing this will keep the size of your snapshot down and minimize the space requirements on the subscriber.

Figure I

Replicate columns

You can also filter your data if you need to. (Figure J) However, this is a more advanced option and is not recommended unless you are comfortable with manipulating the data you will be publishing.

Figure J

Filter data

You can choose to take your snapshot then or schedule it. (Figure K) The snapshot will lock tables while it copies the data out of them (to preserve replication integrity). The agent does run speedily, but if you have a large amount of data it can still take some time, so be sure to schedule accordingly.

Figure K

Now or schedule

You can then provide credentials for the snapshot agent. (Figure L) If your SQL Server Agent service does not have permissions to the directory you choose to place your snapshots in, you will need to provide a different authentication account to provide this access.

Figure L

Credentials

After selecting from a few more menus (where nothing but the defaults are needed), you can name your publication. (Figure M) You can send this same publication to many subscribers, so you will want to name the publication by the data it contains and not where it is going.

Figure M

Name your publication

You are now ready to set up subscribers to your publication. (Figure N) Subscribers are the servers that will be receiving your replicated data. To do this, right-click on the publication you have just created and select New Subscriptions.

Figure N

Set up subscribers

You will first need to select the publication you wish to replicate. (Figure O) If you are using a separate distributor system, you will need to connect to the SQL Server that houses the publication you will replicate.

Figure O

Select replication publication

Now you can select your subscribers. (Figure P) This is done by selecting Add Subscriber, which will prompt you with the standard SQL Server Management Studio prompt for a server connection. Simply provide the server name and your preferred type of authentication. You can also add multiple servers as subscribers at this point by repeating this step.

Figure P

Select subscribers

You can configure your authentication type for the distribution agent next. (Figure Q) If you wish to use a specific account to push to the data to the subscription server, this is where you do it. You can choose to use the SQL Server Agent account, a Windows account, or an SQL account, depending upon your own security infrastructure.

Figure Q

Authentication type

Next, you will set the schedule. (Figure R) You can set the agent to run continuously, which will provide a near real-time replication of your data. You can also replicate at any schedule you wish. This is useful if you have a slow WAN and need to maximize bandwidth during certain hours.

Figure R

Set the schedule

You can now see your subscriber under the publication. (Figure S) From here, you can modify the properties of your subscription, reinitialize the subscription, view your synchronization status, and bring up the replication monitor.

Figure S

Subscriber properties

By viewing the replication monitor, you can verify that your transactions have been delivered successfully and see the amount of time it took for each step to complete. (Figure T) If you have any errors, you can also view details on them here to help your troubleshooting.

Figure T

View replication monitor

Transactional Replication Monitoring

Transactional Replication requires the following to be monitored:

1. Replication agent success / failure
2. Replication agents are alive
3. Size of the DISTRIBUTION DATABASE
4. Size of the SUBSCRIBER DATABASE
5. Available space on the SNAPSHOT working directory
6. Latency of Replication
7. Synchronization of PUBLICATION ARTICLES

The following system stored procedures may be used to assist in monitoring Transactional

Replication:

• sp_helpreplication
• sp_helparticle
• sp_helpdistributor
• sp_helpsubscriberinfo
• sp_helpsubscription
• sp_replcounters
• sp_publication_validation

Potential Transactional Replication failure scenarios:

1. Data row count inconsistency
2. SUBSCRIBER / PUBLISHER schema change failure
3. Connection failure
4. Agent failure
5. SQL language failure blocks the REPLICATION QUEUE

For most failure scenarios involving the validation of SUBSCRIPTION processing it is best to RESNYCHRONIZE the SUBSCRIPTION by dropping it and RESUBSCRIBING or by REINITIALIZING the SUBSCRIPTION. Agent failure is typically due to some gremlin in SQL SERVER AGENT. Generally, stopping and restarting SQL SERVER AGENT will solve this problem.

When a REPLICATION object on the SUBSCRIBER becomes incongruent (messed up) the solution is usually to recreate the object and reload its data with BCP or SSIS followed by a RESYNCHRONIZATION. The worst possible case of TRANSACTIONAL REPLICATION failure is when a SQL command has failed and is blocking the REPLCIATION QUEUE because this scenario STOPS ALL REPLICATION. When all replication stops the DISTRIBUTION DATABASE begins to grow very rapidly and may fall-over.

The solution:

1. Examine the error detail in the DISTRIBUTION AGENT HISTORY in order to identify the offending SQL statement.
2. Use sp_browsereplcmds to find the SQL statement in the DISTRIBUTION DATABASE.
3. sp_browsereplcmds returns all transactions, SQL statements, and their xact_seqno; therefore, dump the return value into a file for easy searching.
4. Find the offending SQL statement and its associated xact_seqno
5. Delete the SQL statement from the MSrepl_commands table using the xact_seqno
6. PerfMon REPLICATION monitoring objects:

• SQLServer::Replication Agents provides the status of all agents.
• SQLServer:: Replication Dist provides status of DISTRIBUTION agents.
• SQLServer::LogReader provides status of the Log Reader agent.
• SQLServer::Replication Merge provides status of MERGE agents.
• SQLServer::Replication Snapshot provides count of number f transactions per second.