Author: LizetP

We finally launched our external sql server 2005 with merge replication with pull subscriptions. After extensive QA testing, surprising data loss due to filtering, more testing with new parameterized row filter, we finally made it.

Right after we launched the servers (our publisher and distributor) and the subscribers began to synchronize we had a few conflicts we hadn’t seen in any of the test environments.

The conflict cause was CONNECT REPLICATION permission missing on the users; the server data overwrote the client data on each case.

The Books Online documentation is pretty scarce on this new permission. It only says that if you have CONTROL permission at Database level, the CONNECT REPLICATION permission is implied on the database. Also if you have CONTROL SERVER permission at server level, the CONNECT REPLICATION permission is implied as well. Other than that, there isn’t much.

The official article documenting the breaking changes can be found here:

http://technet.microsoft.com/en-us/library/ms143470.aspx

and it does not mention the difference in permissions.

The breaking change here is that this permission that you can set through the Securables item on the user properties is brand new to SQL Server 2005.

On a SQL Server 2000 engine, the Management Studio looks like:

While on SQL Server 2005 the set of permissions for a given database looks like:

After our server upgrade to SQL Server 2005, some of the stored procedures stopped working. The reason, the change in behavior of the @@identity function in 2005:

The value of @@identity is not restricted by current scope and can be affected, for example, by triggers. I.e. if original ‘insert’ deals with table T1, and the table T1 has a trigger that inserts rows into table T2, then after the original insert is done the @@identity will show new value for the table T2 (not for the T1 as might be expected!!!)

The suggested solution is using SCOPE_IDENTITY()function instead of @@identity. This function IS restricted by the scope and for the example above it will return new identity value for the original table T1 (not for T2 or any other table affected by triggers).

It seems to me that SELECT MAX(T1) FROM T1 is not the best solution, because T1 (and any other) table can be updated simultaneously from multiple sessions and the maximal value is not always guaranteed.

If you have merge replication going on, be aware that now replication uses triggers that were not present in 2000:

For instance – insert trigger MSmerge_ins_5D17CE9EBD2142E3ADC630C47F017946 which does insert rows into other system table with identity column!!! We didn’t see these triggers in SQL 2000 database which may explain why the @@identity worked fine in the same code under SQL 2000.

Hope this helps and happy upgrading!

PG

We developers are all a little bit lazy, time conscious, we don’t like to perform the same installation/upgrade over and over on 30+ remote machines.
It is Friday, and even though I added this link to my bookmarks already, I know it will take me a few minutes to find on Monday… yes, my bookmarks are that messy.
I need to create a cmd install batch that will

1. Upgrade the local MSDE instance to SQL Express 2005
2. Use Advance features during the installation as I’m not creating a new instance but upgrading the default one.
3. Add the replication components that will be needed for the merge replication
4. Add the client components in case I need to connect remotely to that rebel local instance :-p
5. Add the Connectivity components that will install the SMO and RMO (Server Management Objects and Replication Management Objects). I’m using both namespaces to trigger/manage replication from this .NET smartie client.

My cheat sheet is here:

Command-line installation

It doesn’t seem too complicated, let’s wait for Monday to see how it goes. Now I’m still working on my RMO code. We had Replication pretty much working with the a Replication ActiveX component for MSDE but this component is deprecated for 2005.

Have a nice weekend!

We’ve all been pretty busy lately with a juicy project. We have a client application that runs on TabletPCs. Among other interesting parts of this project we have SQL Server Merge Replication going on. We used to have a topology of SQL Server Enterprise Edition for the Publisher and Distributor and the reliable MSDE for the subscribers.
A few months ago our excitement increased when the company decided to migrate some of the servers to SQL Server 2005 Ent.
The excitement continue but we’re having quite a few headaches with the replication. Our main problem is the hardware. Our client boxes have merely 500MB of RAM and the recommended requirements for SQL Server 2005 Express edition is 512, oh joy.
We tried to keep MSDE alive and use it still as subscriber but we use some of the features that invalidate the compatibility, in particular the Parameterized Row Filtering. More details on the incompatibility on this MSDN Reference. Please see the topic Compatibility Level for Merge Publications where it specifies that parameterized row filtering requires Compatibility Level for Merge Publications 90RTM or higher.

The solution, it seems we upgrade our subscribers to SQL Server Express 2005 and hope they still work at a decent level with the scarce hardware, or upgrade and hope there is budget for a hardware upgrade too :-p, at the end it’s all about budget, right?

Cheers!