Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Build a SQL Server 2005 Updatable Subscription Replication Topology


By:   |   Last Updated: 2008-03-10   |   Comments (2)   |   Related Tips: More > 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 this option at (http://technet.microsoft.com/en-us/library/ms187691.aspx).

Login For Updatable Subscriptions screen - A Linked Server or Remote Server will be needed to connect to the publisher for Updatable subscription. For more information, visit - http://msdn2.microsoft.com/en-us/library/ms152769.aspx.

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. If that is not the case, please see http://msdn2.microsoft.com/en-us/library/ms152769.aspx for how to setup the link server.

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:

Msg 548, Level 16, State 2, Procedure usp_AddMyTable, Line 16 The insert failed. It conflicted with an identity range check constraint in database 'ReplPlayground', replicated table 'dbo.MyTable', column 'MyID'.
If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute
sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.
The statement has been terminated.

** An error was encountered during execution of batch. Continuing.
Batch execution completed 1000 times

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
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



Last Updated: 2008-03-10


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, April 06, 2016 - 10:29:34 AM - DBAPrincess Back To Top

In the Checking the Subscriber section, why was dbo.conflict_DCSQLDEV140_KunPlayground_MyTable table was created? what is this conflict table used for? Can it be deleted? I've cleaned up all my conflict and know these conflict tables are everywhere and they hold no data. Please help.

 


Tuesday, March 25, 2014 - 5:35:21 PM - Srikanth Back To Top

Hi Kun,

I really liked your post on Transactional Replication with updatable Subscription . I am trying to set up Transactional Replication with updatable Subscription in SQL 2005 but I dont see that option instead I see peer to peer replication. But As per I know from some posts it seems Transactional Replication with updatable Subscription deprecated in SQL 2012. Can you tell me wny I am not seeing Transactional Replication with updatable Subscription in SQL 2005.

 

Thanks


Learn more about SQL Server tools