Replication – Accommodating Application Inserts

Recently I have been involved in lots of projects involving replication.  I have done some things to make replication work in some very strange environments.  This posting discusses one of my adventures.  If you have any other suggestions on how this should or could have been done shoot me an email at pleblanc@tsqlscripts.  Please do post your comments here on the blog also.  I am always interested in learning and getting advice from all of you out there in SQL Server Land.

I received a call from a client regarding some replication issues they were having a couple of days ago.  He tried to describe it, but I just couldn’t understand.  Therefore, we scheduled a face-to-face and I was on my way.  This is what I found out:

They have a vendor database that is replicated to one of their local SQL Server 2008 databases in their environment, which I will call VENDOR.  This data must remain in an unchanged state.  No one touches that data.  That data is then replicated to a database, which I will call MERGE, that has several other SUBSCRIPTIONS from various sources, but all the sources are SQL Servers.  The challenge is that users are allowed to enter data into the MERGE database.  Even further, this data may be the same data that is replicated from the VENDOR database or vice versa.  Because of this, replication is breaking due to PRIMARY KEY VIOLATIONS. 

With that said, we had to come up with a solution.  A solution that would accommodate data from the application and data from replication that could potentially violate primary key constraints.  I asked the Application Developer how did he handle the violations, even though I had a pretty good idea.  He stated that he did a “check for existence” based on the primary key.  If the row existed he would update the row, and if it did not he inserted a new row.  Very simple solution.  I took his solution and applied it to the Transactional Replication that was already in place.

When configuring Transactional Replication you are given three methods of Statement Delivery.

1. Using an Insert, Update or Delete statement
2. Using an Insert, Update or Delete statement without a column list
3. Call a stored procedure

I chose #3.  This allowed me to apply the same “check for existence” methodology that the developer used.  If you accept the default when configuring replication, SQL Server will automatically generate each stored procedure for you.  The following script is an example of a stored procedure that was generated.

CREATE procedure [dbo].[sp_MSins_dboTableOne]
    @c1 int,
    @c2 int,
    @c3 varchar(25)
as
begin
     insert into [dbo].[TableOne]
     (
        column1, column2, column3
    )
    values (@c1,@c2,@c3)
end

The script was then modified to accommodate the Application Inserts, which can be seen in the following example:

CREATE procedure [dbo].[sp_MSins_dboTableOne]
    @c1 int,
    @c2 int,
    @c3 varchar(25)
as
begin
    /*1*/if not exists(select * from dbo.TableOne where column1 = @c1) /*1*/
    begin
        /*2*/ insert into [dbo].[TableOne]
         (
            column1, column2, column3
        )
        values (@c1,@c2,@c3) /*2*/
    end
    else
    begin
        /*3*/update dbo.TableOne
        set
            column2 = @c2,
            column3 = @c3
        where
            column1 = @c1 /*3*/
    end
end

The main modification is an addition of an IF NOT EXISTS and an UPDATE statement, which is labeled as Items 1 and 3, respectively.  Utilizing the primary key, I was able to check for the existence of the record.  If it did not exist a new row is added to the table, which is labeled as Item 2.  If it does exists, the row is updated based on the primary key, which is labeled as Item 3.  Since the values are passed as parameters to the Insert stored procedure, I was able to identify the primary key and all the other columns.  As a result, I was able to use them in my modifications.

I would use a lot of caution when modifying these procedures.  This database only receives approximately 2000 inserts and updates within a month.  You must ensure that the changes you are making will not increase the latency of your replication.  In others words, test, test, test, test.

 

Talk to you soon,

Patrick LeBlanc, Founder TSQLScripts.com (www.tsqlscripts.com)

SQL Down South