SQL 2005 Replication Hell

Here in Nebras we are using SQL 2005 Replication to deploy solution for synchronizing our clients multi-branches sites to avoid centeral data store which doesnot work with the unreliable internet connections available here in Egypt.

Also this kind of replication is two way replication with instant synch, in other words what happen in branch 1 should appear in branch 2 instantly if connection is up.

So our choice was SQL 2005 Transactional Replication with Updatable Subscribers.

The main pain was in solving replication issues which concentrate in:
1- If connection is not reliable, the replication start conflicting without any way to find the reason of conflict. Is it data conflict due to concurrency or a conflict due to a failed stored procedure?
2- Triggers are a real hell in SQL 2005 Replication. You need to full understand the replication operations internals to be able to solve the triggers/replication issues
3- The replication logs didn't deliver that much information to be able to find the core  cause of replication conflicti ssue
4- The SQL Replication Monitor is a joke. you cannot rely on it to really monior or find problems in your replication deployment. And the Monitor itself causes issues to the replication monitor parameters if left open for long period of time.
5- The SQL BOL is really not helpful to understand the internals of SQL Replication which is necessary with business requirement challenges here in egypt

In the next post I will provide some usefull links that may help you in your transactional replication problems regarding main causes of data conflict which is not based on data concurrency.

Also in future posts I will introduce SQL 2008 Replication deployment experience and I will give a comparison with SQL 2005 Replication (the odds and evens)

SQL 2005 and SET OPTIONS Tip

In SQL 2005 if you have stored procedure or SQL statement that execute update operation on table with computed column that refer to function, or have update triggers, one of the following errors may occur:

• Msg 8624, Level 16, State 1, Procedure Patient_Update, Line 96
  Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
• UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

The cause of this problem can be that the SET OPTIONS for stored procedure is different in the computed column function or update triggers.

For example, if computed column function has SET ANSI_NULLS ON and stored procedure has SET ANSI_NULLS OFF, one of the above errors may occur

To resolve this, ensure that you are using the same SET OPTION in the stored procedures, computed columns functions and update triggers.