REPLTalk covers Using and Tuning SQL Replication

Cannot Replicate “Stored Procedure Execution” when CDC is Enabled

ReplTalk — Sun, 28 Apr 2013 22:22:07 GMT

Cannot Replicate “Stored Procedure Execution” when CDC is Enabled

Taiyebali Zakir | Support Escalation Engineer | MS SQL Server CTS

Replicate Execution of Stored Procedure and CDC:
=====================================

With Transactional Replication you can replicatethe execution of the Stored Procedure like this:

This has several advantages as discussed in this article: http://msdn.microsoft.com/en-us/library/ms152754.aspx .

When you enable Change Data Capture (CDC) at the Database level, replication of the stored procedure execution will NOT work. This is By Design. Change Data Capture (CDC) does not support tracking at stored procedure execution level, which means individual rows logged by stored procedure execution need to be flagged with REPLICATE bit, which makes it impossible for Transactional Replication to replicate only the stored procedure execution. As a result, ‘stored proc execution’ will work only when Transactional Replication is enabled and CDC is not enabled.

Exploration into “Row was not found”

ReplTalk — Wed, 16 Jan 2013 19:16:55 GMT

Exploration into “Row was not found”

Chris Skorlinski
Microsoft SQL Server Escalation Services.

“The row was not found at the Subscriber when applying the replicated command.” errors reported by the SQL Replication Distribution Agent can often be some of the hardest issues to troubleshoot. With great satisfaction I was able to uncover this mysterious error for one of my customers.

In the customers SQL Replication topology they’re consolidating 2 databases from a single Publisher into 1 database on the Subscriber. The published tables going into different “schema” or groups on the Subscriber.

Database A, Publication A into Subscriber schema A.{table}
Database B, Publication B into Subscriber schema B.{table}

They subscriber tables are unique only by the “schema” or group name. For this topology, (2 into 1) is possible, however, to make this work “special” handling is required or Distribution Agents will fail with “missing data” and/or “invalid data” in the Subscriber.

Here is why:

By default SQL Server Replication creates stored procedures on the subscriber to update the subscriber tables. In my example I’m replicating the CUSTOMER table. The default stored procedure created by Replication to update the subscriber is called [dbo].[sp_MSupd_dboCustomer]. As you can see there is no A or B schema naming. Therefore each publication will use the same stored procedure name on the subscriber, however, the whichever stored procedure is created LAST will get ALL of the data changes.

If I first create A publication, the stored procedure [dbo].[sp_MSupd_dboCustomer] updates [A].[Customer] table. Next if I create the B publication and push to the same subscriber database, the stored procedure created by B publication is created with the same name as A publication, over writing the A stored procedure. As result ALL, yes ALL changes from both A and B are routed to the [B].[Customer] table. Not only is A missing inserts, those inserts are being apply to B.Customer table.

create procedure [dbo].[sp_MSupd_dboCustomer]
update [A].[Customer] set
where [CustomerID] = @pkc1

create procedure [dbo].[sp_MSupd_dboCustomer]
update [B].[Customer] set
where [CustomerID] = @pkc1

Solution

To correct this problem ALL publications need to be DROPPED and re-created with their own UNIQUE stored procedures on the subscriber. You can accomplish this via the Replication Wizard by editing the “INS/UPD/DEL stored procedure” names under the “Statement Delivery”.

Or by modifying the CREATE PUBLICATION script, then sp_addarticle command to specify a unique name for each of the INS, DEL, and UPD commands.

-- Adding the transactional articles (Publication A)

exec sp_addarticle @publication = N'A', @article = N'Customer', … , @ins_cmd = N'CALL [sp_MSins_A_Customer]', @del_cmd = N'CALL [sp_MSdel_A_Customer]', @upd_cmd = N'SCALL [sp_MSupd_A_Customer]'

-- Adding the transactional articles (Publication B)

exec sp_addarticle @publication = N'B', @article = N'Customer', … , @ins_cmd = N'CALL [sp_MSins_B_Customer]', @del_cmd = N'CALL [sp_MSdel_B_Customer]', @upd_cmd = N'SCALL [sp_MSupd_B_Customer]'

After these changes 2 stored procedures for updating the subscriber tables will be created, one from each publication updating only that publications data.

Notes on Log Reader Parameters MaxCmdsInTran and ReadBatchSize

ReplTalk — Mon, 08 Oct 2012 18:37:15 GMT

Notes on Log Reader Parameters MaxCmdsInTran and ReadBatchSize

Jose Moreira B. Neto
Microsoft SQL Server Escalation Services

From SQL Server BOL we have:

-ReadBatchSize

Is the maximum number of transactions read out of the transaction log of the publishing database per processing cycle, with a default of 500.

-MaxCmdsInTran

Specifies the maximum number of statements grouped into a transaction as the Log Reader writes commands to the distribution database. Using this parameter allows the Log Reader Agent and Distribution Agent to divide large transactions (consisting of many commands) at the Publisher into several smaller transactions when applying commands at the Subscriber. Specifying this parameter can reduce contention at the Distributor and reduce latency between the Publisher and Subscriber. Because the original transaction is applied in smaller units, the Subscriber can access rows of a large logical Publisher transaction prior to the end of the original transaction, breaking strict transactional atomicity. The default is 0, which preserves the transaction boundaries of the Publisher. This parameter does not apply to Oracle Publishers.

What you should take into consideration:

MaxCmdsInTran was offered to customers as a way to decrease latency from the publisher to subscriber for very large transactions. It does this by breaking transactional consistency, and breaking up a single large transaction into smaller transactions. You may see poor logreader performance with low settings of MaxCmdsInTran because it causes the logreader to deliver less data into the distribution database per log harvesting “batch”. In other words, there is some fixed overhead cost of delivering a batch, so with respect to throughput, you probably want your batch size to be as large as possible. Lower MaxCmdsInTran values will increase latency.

A larger batch though can cause blocking, timeouts and increased transaction log size on the subscriber. Many customers work around blocking and deadlocks on the subscriber due to the locks held by the extended transaction when the change is replicated to the subscriber by implementing MaxCmdsInTran.

MaxCmdsInTran was never designed to be always turned on. It exists in the product to work around cases where someone accidentally performed a large number of DML operations in a single transaction (causing delay in distribution of commands until the entire transaction is in distribution database, locks being held, etc.). If the customer is routinely falling into this situation, they should serious look at their applications and ways to reduce transaction size.

Irrespective of ReadBatchSize, if you set a low value for MaxCmdsInTran the log reader needs to write small chunks to the distribution db and that will cause latency compared to writing one big chunk at once

Readbatchsize determines how many transactions to harvest from the transaction log per processing cycle. MaxCmdsInTran determines the maximum number of commands on a single transaction, possibly breaking transaction ACID. Suppose readbatch size is 10, and each transaction has 1,000 commands, in this example the log reader will harvest 10,000 commands contained in 10 transactions. These 10,000 commands will be applied to the subscriber as 10 individual transactions. If MaxCmdsInTran is also added to the agent profile and set to 500, there will now be 20 transactions to be applied to the subscriber, each with 500 commands, hence breaking the original transaction scope.

Summary

I would recommend not using maxcmdsintran at all since it was designed to be used temporarily for a short time in rare cases. If you see logreader going slow, try to modify your app to reduce transaction size, if transaction size is small you can then use ReadBatchSize to speed up the log reader.

How to show pending Merge Replication changes

ReplTalk — Wed, 22 Aug 2012 01:10:01 GMT

How to show pending Merge Replication changes

Jonathan Clark
Microsoft SQL Server Escalation Services

SQL Server sp_showpendingchanges is used to determine how many changes need to be moved to a subscriber or uploaded to a publisher. You can use these steps to “roll your own” and pull similar data.

To tackle this problem from the meta data can be a bit daunting, but there are some ways to get an idea of how many changes are left for a specific table. You can combine what you find for all the tables in a publication and get an estimate for how many changes remain to be synchronized.

If you have multiple publications that you are watching then your first stop will be in the sysMergePublications table. Query this table to find the pubid of the publication that you want to monitor. An example would be simply: select pubid,* from sysmergepublications

With an output like this

Next get your list of article nicknames for the publication from the sysMergeArticles table: select nickname,* from sysmergearticles where pubid = '22C4781D-E6BC-400B-BC27-A4DB70A0D182'

So now you are ready to count the changes for these articles that still need to be synchronized. To do this we need to check the progress “watermark” of generations (groups of changes) sent down to the individual subscribers. We store this watermark in the sysMergeSubscriptions table as the sentgen column. You could get this using a query like this: select sentgen, * from sysmergesubscriptions where pubid = '22C4781D-E6BC-400B-BC27-A4DB70A0D182' AND pubid <> subid

So now you know that for this particular subscriber, the last generation sent was generation #4. You need to discover how many generations exist in the metadata greater than #4 and how many changes those generations contain. To do this, you could use a query like this:

select mc.tablenick, COUNT (*) as changes from MSmerge_contents mc

where mc.generation > 4

group by mc.tablenick

A few additions to have the current sentgen passed in as a variable and return your actual table names and you end up with something like this:

declare @sentgen int =

(select sentgen from sysmergesubscriptions

where subid = '7E4942FB-A5D4-45CF-AB87-9D5566C1609A')

select ma.name, COUNT (*) as changes from MSmerge_contents mc

join sysmergearticles ma on (ma.nickname = mc.tablenick)

where mc.generation > @sentgen

group by ma.name

This tells you how many changes are remaining to be sent for each individual table to a specific subscriber. The subscriber was specified by its subid which I got from sysmergesubscriptions. In this case I have more than 2 million changes for the table named Artists and only 1 change for Table_1.

Really all you need is the last query, but I thought I’d take you through the data so that it makes sense. This way you are armed with some information and could potentially modify it or write your own unique query.

Initialization of Merge Replication subscriber fails with primary key violation

ReplTalk — Mon, 30 Jul 2012 23:04:26 GMT

Initialization of Merge Replication subscriber fails with primary key violation

Troy Moen
Microsoft SQL Server Escalation Services

We encountered an unusual timing problem while pushing a Snapshot down to a merge subscriber. We don’t expect many to encounter this problem, after all we’ve only seen it once in 15 years. We wanted to share this as a learning opportunity as it illustrates a classic application\thread synchronization problem.

Scenario

Customer has Merge Replication running and need to reinitialize 1 subscriber. Updates occur on both the Publisher and Subscriber. At the time, we believed all subscriber updates were being redirected to the publisher, but as you’ll see, we were wrong.

During the re-initialization the Merge Agent failed with the following message.

Error messages:

· The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001

· The process could not bulk copy into table '"dbo"."Table_1"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037

· Batch send failed (Source: MSSQLServer, Error number: 0)
Get help: http://help/0

Violation of PRIMARY KEY constraint 'PK_Table_1'. Cannot insert duplicate key in object 'dbo.Table_1'. (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627

If we started with an empty table and loaded rows from the Publisher how could we possibly get a “duplicate key”? Did the duplicate data already exists in the saved BCP data from the Publisher? We checked and it did NOT!!

We looked at the subscriber table and notice it contained 1 rows with Primary Key value 1, but this was a different row then 1 from the BCP data we’re trying to load. From where did this data originate? Looking closer at the data we determined it was inserted only moments ago directly on the Subscriber from Subscriber’s application, which yes, we thought was stopped, but was still trying to insert data. The application log shows it failing with “table not found” right up to the point where the table got created by the Merge Agent, then pop, it gets inserted.

Consider the following:

A publication exists with one table “Table_1” containing 100 rows.
A user marks the subscription for re-initialization.
A snapshot is generated of table, which contains max primary key value of 100.
The Merge Agent begins application of the snapshot.

Table is dropped from Subscriber
New empty table structure, with default starting identity value 1 is created

Split millisecond before Merge Agent can execute Bulk Insert from the Publisher rows, the subscriber client inserts row into the subscriber. This is assigned identity value 1.
Merge Agent start bulk inserts from previously saved Publisher data containing identity values 1 – 100, it fails on the 1st row as identity 1 already exists.

Under the right conditions, the above example can result in a primary key violation.

Solution:

Disable clients from entering data into the subscriber database during that time. In most cases the “window” in which duplicate data could be inserted is very small, which varies depending on subscriber performance at the time. In my testing, only a few seconds maximum had passed before the trigger was created, and thus stopped any chance of duplicate data being added.

Another creative solution was to create a TRIGGER as part of the table creation to abort any user activity until after the Merge Agent bulk loads existing data, then remove the trigger to if you will, open the spigot and allow data changes to flow. While creative, it takes a while to get all the detail in place for this to happen. The first option is much easier. Let’s take a look at this creative solution from a high level. The Merge Agent will follow these steps when applying the snapshot after a subscription has been marked for re-initialization:

1. Drop the replicated table on the subscriber, provided “Action if name is in use” is set to “Drop existing” in the article properties.

2. Create the replicated table on the subscriber.

3. Apply a trigger disabling DML operations on the table in question:

create trigger [dbo].[MSmerge_disabledml_9F571C3241264DA6972D846C0A738A71] on [dbo].[Table_1] for update, insert, delete

not for replication

as

set nocount on

if @@trancount > 0 rollback tran

raiserror (20092, 16, -1, '[dbo].[Table_1]')

return

GO

4. BCP the snapshot data into the table.

5. Remove the disable DML trigger.

Distribution Agent is blocking users on Subscriber while replicating INSERT, UPDATE, and DELETE statements.

ReplTalk — Tue, 24 Apr 2012 19:02:06 GMT

Distribution Agent is blocking users on Subscriber while replicating INSERT, UPDATE, and DELETE statements.

Chris Skorlinski
Microsoft SQL Server Escalation Services

In the middle of a huge phased migration from SQL 2000 to SQL 2008 we noticed the SQL 2008 publisher\distributor was causing blocking when replicating data changes to the SQL 2000 subscriber.

We discovered the SQL Server 2005\2008\2012 Distribution Agent was configured to replicate as native SQL commands instead of default parameterized stored procedures. This option is controlled by the @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL' settings for sp_addarticle.

delete from [dbo].[build_pricing] where [effort_code] = 'ALFKI'

When this is sent to the subscriber, the Distribution Agent converts this to sp_executesql such as following in attempt to optimize the execution.

exec sp_executesql N'delete from [dbo].[build_pricing] where [effort_code] = @P1 N'@P1 nvarchar(5)

By default, the Distribution Agent passed parameters are nvarchar() data types. If the subscriber data type is char() and not nvarchar(), the Query Optimizer will perform a SCAN and CONVERT rows on the table values to match the passed parameter value data type. Results is high IO hit, large number of locks, high memory usage, long blocking.

We also verified the high IO demands of a single row update or delete statement by examining the query plan.

1 1 |--Parallelism(Gather Streams)
1 8 |--Index Scan(OBJECT:([dbo].[build_pricing]….Convert([effort_code])….

This query plan shows 8 parallel streams (MaxDop = 8) was used to Scan (read) the Index while Converting char(5) [effort_code] data type to match the @P1 nvarchar(5)”. For a single row update or delete and NO conversion the expected query plan would be an INDEX SEEK with 5 not 50,000 reads.

“where [effort_code] = @P1 N'@P1 nvarchar(5)”

If you enable Verbose History You may also see Distribution WRITE taking much longer then READS.

Time to Apply Cmds (ms) : 1016331 Cmds/Sec : 2.54

Time to Retrieve Cmds (ms): 6269 Cmds/Sec : 411.39

Adding "-T 1" to the Distribution Agent Job Properties reverted the behavior to SQL 2000 which does not attempt to optimize the statements using sp_executesql, but instead submits them directly as SQL INSERT, UPDATE, and DELETE statements. In testing the IO overhead dropped from 50K READS to 5, no latency, little or no blocking.

Another option would be to replicated the data using the default Replication Stored Procedures instead of SQL commands.

Verified sp_addarticle the STATUS=0 and CMD='SQL'
http://blogs.msdn.com/b/chrissk/archive/2009/05/08/troubleshooting-slow-distribution-agent-performance-in-sql-2005-2008.aspx

How to set HistoryVerboseLevel Profile for all Agent

ReplTalk — Mon, 09 Apr 2012 23:35:08 GMT

How to set HistoryVerboseLevel Profile for all Agent

Chris Skorlinski
Microsoft SQL Server Escalation Services

This is a follow up post to my article Using “Verbose history agent profile.” while troubleshooting Replication.

Using SQL Server Management Studio you can set a default profile for all Replication Agents. In this walk through I’ll change ALL Merge Agents default to use the “Verbose History Agent Profile”.

In SSMS, right click the Replication folder and select Distributor Properties. Near the bottom of the dialog select “Profile Defaults”.

Select Merge Agent page, then select “Verbose history agent profile”.

This profile, similar to the default, has HistoryVerboseLevel = 3 to record detailed Merge history data in the MSmerge_history table in the Distribution database. To apply this new profile setting to ALL Merge Agent select “Change Existing Agents” then click OK.

While troubleshooting Replication you may want to increase the “History Retention”. Increasing DETAIL and keeping longer history will grow your distribution database.

Specify all articles when subscribing to a publication using concurrent snapshot processing.

ReplTalk — Wed, 04 Apr 2012 21:47:13 GMT

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Chris Skorlinski
Microsoft SQL Server Escalation Services

I recently encountered this error when setting up Transactional Replication via SQL scripts.

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533
Specify all articles when subscribing to a publication using concurrent snapshot processing.

Looking at my create Publication script I see the default @sync_method = N'concurrent' being set in sp_addpublication. Nothing I want to change here.

Further in my script I see the call to sp_addsubscription. From the BOL, this syntax look correct. I’m running sp_addsubscription once for each article for this subscriber.

While the script looks correct, I wondered what the script would look like using default setting created by the Replication Wizard. To test, I recreated the publication using the Wizard, then scripted out the publication. I noticed Replication Wizard used had only 1 call to sp_addsubscription with option @article = N'all'.

Now the error makes sense. When using Current Snapshot Processing I need to make 1 call to sp_addsubscription with option and specify ALL articles for this subscriber.

Replicating Non-Clustered Indexes Improves Subscriber Query Performance

ReplTalk — Tue, 03 Apr 2012 16:40:58 GMT

Replicating Non-Clustered Indexes Improves Subscriber Query Performance

Jared Poché, MCSE, MCDBA
SQL Server Sr. Support Escalation Engineer
Product Support Services (PSS) - Charlotte, NC

One of the advantages of replication is that subscribing servers can be used for reporting thereby offloading RO query activity from the Publisher. Recently I worked on a performance case on such a replication subscriber, which would have been prevented with a simple change to the article properties for this publication. We discovered the nonclustered indexes were missing from the subscriber.

Indexes are key to the performance of SQL Server, but only clustered key indexes are replicated by default. Unique constraints are replicated by default, so their indexes will also be created on subscribers, but nonclustered indexes are not replicated by default. While setting up transaction replication on a database, we can change the default settings for articles on the Articles window to include nonclustered indexes.

Once the necessary tables and other articles have been selected, choose Article Properties and Set Properties of All Table Articles.

This page shows us a number of settings for indexes, constraints, and so on. Find the Copy Nonclustered Indexes setting, set this option to true, and click OK. This will cause all nonclustered indexes on the publisher to be included in the snapshot for delivery on the Subscribers.

Changing the article options will effectively create indexes on all subscribers with only a few clicks. Also, if a new snapshot is taken for the publication and applied to the subscribers, any indexes created directly on those subscribers will be removed. If all indexes are not needed, alternatively, you can create selective indexes directly on a subscriber. However, it reduces administrative overhead to change the option directly in the replication settings for the article.

MSDN References:

http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/51512117-b53d-429b-8c3d-84d9a925126e

http://technet.microsoft.com/en-us/library/ms175980.aspx (sp_changearticle)

Marking Identity Columns as “Not For Replication” in existing Publication

ReplTalk — Tue, 06 Mar 2012 00:53:37 GMT

Marking Identity Columns as “Not For Replication” in existing Publication

Chris Skorlinski
Microsoft SQL Server Escalation Services

We encounter a problem today with a Distribution Agent failing with “Row Not Found” when pushing an UPDATE to a Subscriber. Reaching the problem we discovered the Subscriber was created using a Backup\Restore from the Publisher and had Identity Columns. The Identity Columns had “Not For Replication” set to NO, which flags the Distribution Agent to “renumber” (i.e. reseed) data as it is Replication to the Subscriber.

For example: Row 100 inserted at Publisher, but is renumbered by the Distribution Agent to row 20 at the Subscriber. Later same row 100 is edited on the Publisher. The Distribution Agent looks for but is unable to find row 100 and fails with “row not found”. Instead we want the Distribution Agent to use the same value from the Publisher. For this we need to enable the “Not For Replication” setting on the Identity Column on the Publisher table.

If you try and change the Publisher table design while data exists you get error which prevents saving changes. To work around this problem you can use the stored procedure below to change the “Not For Replication” setting for all tables in the database then reinitialize the subscriber.

Change identity setting for all tables in the database:

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'