Build a SQL Server 2005 Updatable Subscription Replication Topology

By: Kun Lee   |   Updated: 2008-03-10   |   Comments (3)   |   Related: > Replication

Problem

SQL Server 2005 has introduced a new replication method called "Updateable Subscription Replication" which allows data modification at both the publisher and the subscriber(s). An option is also available to queue the data from the subscriber so the subscriber does not have to be on the network all of the time. The overall setup is not difficult, but there are a few tricky areas you should be aware of during the setup and configuration. Let's outline setup process in a step by step manner to better understand what is needed in order to make an informed decision about Updatable Subscription Replication in SQL Server 2005.

Solution

Let's start off with the Replication Topology to set the ground work for the remainder of the tip:

• Publisher/Distributor: DCSQLDEV140
• Subscriber: DCSQLDEV300
• Database name: ReplPlayground
• Database option: SIMPLE for every database.
• Security: Using SQL Agent service account and it is being shared

Configure Publisher Database and Table (Please see the Final Implementation Script at the end of this tip)

Create Database call ReplPlayground (if you want to use existing database, please comment out drop database from the provided script) and create table call MyTable with below requirement

• MyID int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL (Primary Key)
• MyDesc varchar(100) NOT NULL
• DateAdded datetime NOT NULL CONSTRAINT [DF_MyTable_DateAdded] DEFAULT (getdate())
• Make MyID as PK
• Create a trigger
• Create an non-clustered index on MyDesc
• Add extended property to see if it presumes.
• Create a store procedure that insert a row

Pre-populate data with 100 rows as below and here is the initial look after run the script.

SELECT COUNT(*) AS CNT, @@SERVERNAME AS ServerName FROM MyTable
GO
SELECT TOP 10 * FROM MyTable ORDER BY MyID DESC
GO

Configure Distribution on DCSQLDEV140

Welcome Screen - Right click the "Replication" folder and choose "Configure Distribution..." then click "Next" to proceed.

Distributor Screen - Indicate that 'DCSQLDEV140' will act as its own Distributor.

Snapshot Folder screen - When using PUSH replication, accept the default configuration. If you are using PULL replication, please provide a UNC path like \\DCSQLDEV140\ReplData$\.

Distribution Database screen - Typically the distribution database name is 'distribution' but location of the distribution database and transaction log should be corrected to the correct paths for the SQL Server.

Publishers Screen - If more than one publisher is needed, use the interface below. However, additional publishers can be configured after the initial configuration.

Script File Properties - Script out the configurations for disaster recovery and change management purposes.

Complete the Wizard screen - Review the configurations and press the 'Finish' button to complete the process.

Configure Distributor Properties on DCSQLDEV140

• To Configure Distribution properties, right click on the Replication folder and choose the Distribution Properties option
• This interface can be used to:
  • Change Administrative link password
    • Recommend making this change immediately.
  • Add publishers
  • Change the replication profile
  • Change the Retention policy

Create New Publication Wizard

To access the New Publication Wizard right click on Replication | New | Publication.

Welcome screen - Select the Next button to start the process.

Publication Database screen - Choose the 'ReplPlayground' database as the publication database.

Publication Type screen - Choose the 'Transactional publication with updatable subscriptions' option.

Articles screen - If you want to replicate Tables and Store procedures, choose them on this interface.

Properties for All Table Articles screen - Highlight table (MyTable), then click the on the Article Properties and choose 'Set Properties for all table articles'.

Article Properties screen - MyTable - Now, here is one important setup. Press the 'Cancel' button then click 'MyTable' and choose 'Set Properties of Highlighted table Article' and now you will see a new set of options. Scroll down to the 'Identity Range Management' section. You can choose the 'Automatic' which works well for typical OLTP environments. However, if for any reason you are going to pre-populate the data with a large number of insert statements, then the Publisher range size has to be adjusted to meet the right size. Depending on the application and overall growth, the values could be 100,000 or 1,000,000.

Article Issues - If a unique identifier column is not already defined, the wizard will create one to permit the data tracking.

Filter Table Rows screen - For this example we will not filter table rows for testing purposes.

Snapshot Agent - Let's create a snapshot immediately by selecting the top check box.

Agent Security screen - Choose the 'Security Settings...' button to assign which login is going to be used during the snapshot process.

Snapshot Agent Security screen - In this example we are not going to create a specific login and just impersonate the login used for the SQL Server Agent service.

Queue Reader Agent Security screen - Just as on the previous screen run as the SQL Server Agent service account.

Agent Security screen - Once the setup is complete, this is final set of configurations.

Script File Properties screen - Specify the file properties.

Complete the Wizard screen - Review the final settings and press the 'Finish' button to complete the process.

Create Subscription

To create the subscription navigate to the following location Replication | Local Publication | ReplPlayground: DCSQLDEV140_ReplPlayground. To create the subscription right click on the last option and select 'Create Subscription'.

Welcome screen - Click the 'New' button to begin the process.

Publication screen - Choose ReplPlayground as publication.

Distribution Agent Location screen - In this example, push subscriptions use will be used, but if you need to change to pull subscription for any other reasons like a multi trust domain policy, this interface is where the subscription will be configured.

Subscribers screen - Click the "Add SQL Server Subscriber.." button to see the 'Connection to Server' interface shown below.

Connect to Server screen - Here is the screen mentioned above. As far as authentication is concerned, please keep in mind that the Publisher does not have to be able to authenticate to the subscriber, but the distributor needs to be able to authenticate to the subscriber as with any other type of replication.

Subscribers screen - Now is the time to choose the correct database. If you already created a database call "ReplPlaygound", this database will automatically be chosen. If you have not created the database or misspelled it like below "RepPlayground" (without "L"), then choose the correct database or manually create it during setup.

Distribution Agent Security screen - Again, it is not best practice but for this testing, use the SQL Server Agent service account.

Distribution Agent Security - Final settings.

Synchronization Schedule screen - Depending on your requirement select the appropriate Agent Schedule. For this test, the 'Run continuously' option was selected in order to see the results as quickly as possible.

Updatable Subscriptions screen - On this interface choose whether or not to 'Queue changes and commit when possible' option over simultaneously commit changes. You can read more about updatable subscriptions.

Login For Updatable Subscriptions screen - A Linked Server or Remote Server will be needed to connect to the publisher for Updatable subscription.

Initialize Subscriptions screen - In this example choose to initialize the subscription immediately.

Wizard Actions - Ssave the script for documentation, disaster recovery and change management purposes. As a point of reference, the script will not include the actual password, so if you plan on running the script replace the password portion with the actual password.

Script File Properties screen - Review the file properties.

Complete the Wizard - Review the settings and press the Finish button to complete the process.

Creating Subscriptions screen - If you are using the same service account within same domain, you can ignore below warning.

New Subscription Wizard screen - For those who are curious about the warning, here is the detailed warning. Again, if you are using the same service account, it will still work. In addition, this is another reason Microsoft does not recommend using the SQL Server Agent service account for security purposes.

Post installation checks

Congrats, replication is setup! Let take a look around to see what has happened.

Checking the Publisher/Distributor on DCSQLDEV140

When you review the table (dbo.MyTable) you will see there is one more column added to the table (msrel_tran_version). This column is used to help manage replication.

If you query the table after replication has been setup, you see the data from the new column.

When reviewing the Link Servers on the Publisher, you will see the new entry for repl_distributor.

A trigger has been added on the dbo.MyTable to update the msrepl_tran_version column. Also note that this trigger is set to execute first.

create trigger [dbo].[sp_MSsync_upd_trig_MyTable_1] on [dbo].[MyTable] for update not for replication as 
declare @rc int
select @rc = @@ROWCOUNT 
if @rc = 0 return 
if update (msrepl_tran_version) return 
update [dbo].[MyTable] set msrepl_tran_version = newid() from [dbo].[MyTable], inserted 
where [dbo].[MyTable].[MyID] = inserted.[MyID] 
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[sp_MSsync_upd_trig_MyTable_1]', @order=N'First', @stmttype=N'UPDATE'

The following constraints have also been created on the

ALTER TABLE [dbo].[MyTable] WITH NOCHECK ADD CONSTRAINT [repl_identity_range_tran_2137058649] CHECK NOT FOR REPLICATION (([MyID]>(1) AND [MyID]<(200000)))
GO
ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [repl_identity_range_tran_2137058649]
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [MSrepl_tran_version_default_ED8C8652_79CE_4DD9_838E_E5DA66FC39D5_2137058649] DEFAULT (newid()) FOR [msrepl_tran_version]

Checking the Subscriber (DCSQLDEV300)

Let's check the subscriber to determine what has been added or changed in the environment.

First, the dbo.MyTable table has been created with extra column (msrepl_tran_version) just like on the publisher.

Second, the dbo.conflict_DCSQLDEV140_KunPlayground_MyTable table was created.

CREATE TABLE [dbo].[conflict_DCSQLDEV140_KunPlayground_MyTable](
[MyID] [int] NOT NULL,
[MyDesc] [varchar](100) NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
[origin_datasource] [nvarchar](255) NULL,
[conflict_type] [int] NULL,
[reason_code] [int] NULL,
[reason_text] [nvarchar](720) NULL,
[pubid] [int] NULL,
[tranid] [nvarchar](40) NULL,
[insertdate] [datetime] NOT NULL,
[qcfttabrowid] [uniqueidentifier] NOT NULL DEFAULT (newid())
)

Third, three triggers has been created (trg_MSsync_del_MyTable, trg_MSsync_ins_MyTable, trg_MSsync_upd_MyTable) but the existing trigger has not been replicated.

Fourth, the user defined store procedures have been replicated.

Fifth, index and statistics has been created.

Finally, note the extended properties were not created since we did not choose to replicate the properties by default. If needed this configuration can be updated to include these items.

Testing Scenarios

With the replication environment setup, let's work through some testing cases to include:

• Stored procedure change
• Single INSERT
• Identity Range Management

Test Case 1 - Stored Procedure Change

Change a store procedure on the publisher by using the ALTER PROCEDURE command. As you can see below, any code changes on the Publisher will be replicated to the Subscriber. This change can be verified by reviewing the Replication Monitor indicating the change has been pushed.

Test Case 2 - Insert one row on the Publisher by using below query

-- Execute on the Publisher
EXEC dbo.usp_AddMyTable 
@Mydesc = 'After Replication Setup - inserted from Publisher', 
@ShowResult = 1,
@RowReturn = 10 -- returns last 10 rows

In our example, you will see the record with MyId = 798 has been inserted.

Run the code below on the subscriber to validate the record exists.

SELECT COUNT(*) AS CNT, @@SERVERNAME AS ServerName FROM MyTable
SELECT TOP 10 * FROM MyTable ORDER BY MyID DESC

Test Case 3 - Insert a large number of rows

Let's insert a record on the Subscriber for a total of 101 records? As shown below, the new record as a MyID of 2001.

Now, do you remember the 'Identity Range Management' configurations during the setup process? In this scenario those configurations become important. If data is inserted on the Subscriber, the ID starts a 20,001 which is double the value of the 'Publisher range size' value.

Since that process works, let's try inserting 999 rows on the Subscriber as shown below. After you run the code, you are able to verify and it works fine.

-- Don't know what 'GO 999' does? Check out http://www.mssqltips.com/sqlservertip/1216/executing-a-tsql-batch-multiple-times-using-go/
EXEC dbo.usp_AddMyTable 
go 999 

Now try inserting 1000 rows on the Subscriber as shown below.

EXEC dbo.usp_AddMyTable 
go 1000

As you can see, this code generates the following error:

So, now you need to run the sp_adjustpublisheridentityrange system stored procedure on the Publisher as shown below to adjust the identity range on the publication. This reallocates new ranges based on the threshold value for the publication. This stored procedure is executed on the Publisher in the publication database.

Use ReplPlayground
exec sp_adjustpublisheridentityrange 
@table_name = 'MyTable',
@table_owner= 'dbo' 

When you re-run the code it works properly. However, you will encounter the problem once again when you reach the threshold. This test explains why it is important to correctly configure the Identity Range value with a correct estimated threshold.

One more tip to create a trigger

When you create a trigger on the table that needs to be replicated, please make sure not to set the ORDER of the trigger to be first as below. If you do that, you will see the following error while creating the publication:

EXEC sp_settriggerorder @triggername=N'[dbo].[trgu_MyTable]', @order=N'First', @stmttype=N'UPDATE'

Final Implementation Script

Based on the original set of processes, below outlines the final implementation script:

-- Create a Replication Testing database call "ReplPlayground"
USE master
GO

IF EXISTS(SELECT * FROM sys.databases WHERE name = 'ReplPlayground')
DROP DATABASE ReplPlayground
GO

CREATE DATABASE ReplPlayground
GO

USE ReplPlayground
GO 

-- Create a new table.
IF object_id(N'dbo.MyTable') IS NOT NULL
DROP TABLE dbo.MyTable
GO

CREATE TABLE MyTable(
MyID int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
MyDesc varchar(100) NOT NULL,
DateAdded datetime NOT NULL CONSTRAINT [DF_MyTable_DateAdded] DEFAULT
(getdate()),
CONSTRAINT PK_MyTable_MyID PRIMARY KEY CLUSTERED (MyID ASC)
)
GO

CREATE NONCLUSTERED INDEX IX_MyTable_MyDesc ON dbo.MyTable (MyDesc ASC)
GO

EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'This table is for testing replication' , 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'MyTable'
GO

EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'MyID is identity and PK' , 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'MyTable', 
@level2type=N'COLUMN',
@level2name=N'MyID'
GO

EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'description of the inserted row' , 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'MyTable', 
@level2type=N'COLUMN',
@level2name=N'MyDesc'
GO

EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'DateAdd' , 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'MyTable', 
@level2type=N'COLUMN',
@level2name=N'DateAdded'
GO

create trigger [dbo].[trgu_MyTable] on [dbo].[MyTable] for update not for replication as 
declare @rc int
select @rc = @@ROWCOUNT 
if @rc = 0 return 
if update (DateAdded) return 
update [dbo].[MyTable] set DateAdded = getdate() from [dbo].[MyTable], inserted 
where [dbo].[MyTable].[MyID] = inserted.[MyID] 
GO

-- Set the trigger order to be last
EXEC sp_settriggerorder @triggername=N'[dbo].[trgu_MyTable]', @order=N'Last', @stmttype=N'UPDATE'
go

if object_id('dbo.usp_AddMyTable') is not null
DROP PROC usp_AddMyTable
GO

/*
EXEC usp_AddMyTable 'MyDescription'
*/
CREATE PROC [dbo].[usp_AddMyTable]
@Mydesc varchar(100) = NULL,
@ShowResult BIT = 0,
@RowReturn INT = 10 -- Show only last @RowReturn rows
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @Mydesc IS NULL
SET @Mydesc = REPLICATE(LEFT(NEWID(),2),3) + '_' + CONVERT(VARCHAR(23)GETDATE(),126)
SET @Mydesc = @@SERVERNAME + '_' + @Mydesc
INSERT INTO MyTable (MyDesc) VALUES(@Mydesc)
IF @ShowResult = 1
BEGIN
SELECT COUNT(*) AS CNT, @@SERVERNAME AS ServerName FROM MyTable
SELECT TOP (@RowReturn) * FROM MyTable ORDER BY MyID DESC
END
END
GO

--Execute 100 times
EXEC dbo.usp_AddMyTable
GO 100 
-- Don't know what 'GO 100' does? Check out http://www.mssqltips.com/sqlservertip/1216/executing-a-tsql-batch-multiple-times-using-go/

Next Steps

• This was a long tip with all of the creation and testing steps, so be sure to re-read and understand this tip before you implement replication in your environment.
• Be sure to setup a few different test environment and test cases to be sure all of the replication needs are thoroughly tested and clearly understood. As is the case with any technology, limitations do exist and by not following the 'rules' you can break the implementation.
• Stay tuned for more tips related to the Replication Monitor and Updatable Subscription Replication.
• For more information about replication visit:
  • MSSQLTips Category: Replication

About the author

Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips