P2P Transactional "Query Timeout Expired" for Distributor to Subscriber

I thought I'd add a post to detail a problem I just had with replication where transactions were just not arriving at the subscriber database, resulting in the Replication Monitor listing the following errors (repeatedly every 30 mins or so...).

Query timeout expired
The process is running and is waiting for a response from the server
Initializing...

and then terminating with this error...
Agent 'xxxx' is retrying after an error, nn retries attempted

(apologies for scrubbing out sensitive names)

The statement that was run on the publisher was an update that affected 25k rows. It was only
to update the value of a 'bit' from 1 to 0, but each row was large, some 800k as it contained varchar(max) data.

Now it appears that even though it was just a small amount of data that had changed, the whole row is transmitted over, 800k and all. This meant that when the the system issued the sys.sp_MSget_repl_commands procedure things took too long....longer than 1800 seconds (1800 you say, why did you not just say 30 minutes like normal people do, you freak). Well...

Theres a parameter that the distributor and log agents run with
-QueryTimeout 1800 and the default setting (in seconds) appears to be...yes 1800.

So, i upped this to some 50000 seconds...and wahay things started to 'unbung' themselves, rather than timing out.

How do you change this setting? Well in SSMS I....
1) right click Replication
2) click distributor properties, choose general
3) click the 'Profile Defaults...' near bottom right
4) choose 'Distribution Agents' on left
5) click 'New..' to create a new default agent profile
6) choose 'Default Agent Profile' from the list displayed, (to copy this)
7) Choose a nice name (meaningful now, come on!), and amend the QueryTimeout in right column
8) Save, and then 'check' this profile as being 'Default for New'
9) Click 'Change Existing Agents', Confirm 'Yes'

Repeat from 4 to 9 for the 'Log Reader Agents'

Then I restarted 'SQL Server Agent' on the publisher machine.

Things are now chugging fine.

An update-update conflict between two peers on same node

I recently encountered an error with a peer to peer replication topology between two nodes that drove me near insane trying to solve.

The topology was typical of a peer-peer design in that updates are done on one node only, while reads are done on both, to gain scale out/load balancing benefits. (I had been testing this setup before implementing the final topology which was to horizantally partition the data updates, so that eventually updates were done on both nodes, but records updated on one node were for the first half of the data, and the other node updates for the second half. Reads being done on both still.)

Anyway, while testing the 'updates on 1 node' situation I got this conflict error....

"An update-update conflict between peer 1 (incoming) and peer 1 (on disk) was detected and resolved. The incoming update was skipped by peer 2."

This made no sense as it looks like the conflict was with itself! (ie Peer 1 incoming, and Peer 1 disk)

I tore down the replication many times, checked my code, and did this debugging for a full day without any luck. In the end I went to bed square eyed and disheartened, and woke tired and dissappointed at having wasted the whole previous day and got nowhere to go with the solution. My wife also concerned about my stress bless her.

I popped a message on the priority managed groups (luckily we are a gold partner) and lo and behold MVP Hilary Cotter pointed me to this....

http://support.microsoft.com/kb/973223


This talks about 'dummy updates' causing problems. Dummy updates?
 
Well in my case an update statement was along the lines of this
 
UPDATE FRED SET COL = 'ABC' WHERE ID=123.
 
The problem was that COL was already 'ABC' (and so really the update wasn't necessary), but I had not coded for this. Although the update was a valid statement, it sent Replication into a spin.
 
I hope this saves someone else some pain and anger.

Testing out replication: Two nodes on one box

One thing that is important with replication I feel, is the ability to set it up and have a play. This is the BEST way to learn about all the nuances and magic within this architecture. Things rarely go straight forward with a setup, and I think the pain of going through it only gives you a great wealth of experience when you come out of it the other side

When I first started to have a play, I wanted to make sure that any problems I encountered were elimitated down to SQL server only and not network, permissions or other such red herrings. So I decided to set up 2 instances on one server. Ie an developer edition installation as normal, and then another 'named install' called NODE2 for instance. Then I can replicate back and forth on the same box. This I found very useful to learn. You can set up a named instance by running the normal sqlserver install again, but choose to create 'named instance' along the way during the install wizard.

However, later on I had trouble connecting to NODE2 from client tools on another box, while I could connect to the default instance fine. After much digging I came accross this artice, I recommend it as it helped solve the problem.

http://martinsantics.blogspot.com/2009/09/running-multiple-instances-of-sql.html

Replicating from one node to another is now my standard way to test replication, I recommend it before you start getting multiple servers involved and to get your confidence up.

What is a 'DBA Developer' in the Microsoft world anyway....?

People like to have a title for what they do and DBA is something that has broad scope. I don't think I'm a DBA at all, but I it seems from what I read on the web, lots of the time I'm doing what a DBA might do..whatever a DBA is!

I think i'm probably a 'DBA Developer'.... but what IS that?

In my opinion a 'DBA Developer' is a .NET developer that along the way has fallen into the role of having to roll up sleeves and step from the world of ADO.NET into the one of T-SQL, and has had to learn the hard way, gradually becoming more and more experienced as the years past and have developed a passion for the way SQL Server works. I mean someone who knows both what a DataView is and also knows what a T-SQL nested subquery is.

So in other words, not just backing up databases analysing database growth patterns, but more a .NET developer that has added a good understanding of T-SQL and SQL server experience along the way, knows what an Index is and knows how to profile a query...anyone any thoughts on this? Are you a DBA Developer?

Peer to Peer replication is missing!

When I first started messing around with replication, I looked at a number of the different types, and at the time SQL 2005 had just turned up. It offered a number of replcation types...

Transactional
Transactional with updateable subscriptions
Snapshot
Merge

A great menu for those hungry to replicate, but something else came along aswell, "Peer to Peer replication". Great! at the time this seemed like the correct fit for the project I had in mind. Peer to Peer replication is not obvious to set up and all the tutorials mention that you have to set up 'Transactional' replication and then modify the publication properties (subscription options) to enable 'Allow Peer to Peer subscriptions'. Ok, not a problem, ready for some right click action...lets have a play..I said.

...but for some reason this option to 'Allow Peer to Peer subscriptions' it doesn't appear on my properties dialog...

Then....after much digging I realised that 'Peer to Peer replication' is only available for the Enterprise versions of SQL Server...and my server had 'Standard' installed. Something worth knowing if you are pricing up a setup as the price difference between these two versions is massive. Could leave you with egg on your face when you go back cap in hand to your purchasing department.