July 8, 2008 at 3:21 am
Hi all,
I'm experiencing incredibly slow response times and regular timeouts on my subscribers.
This problem started after the Publisher/Distributor DB was upgraded to SQL 2005...
The process I followed was as follows:
1.Merge agents for subscribers deleted and sp_mergesubscription cleanup ran on all subscribers.
2.SQL 2000 DB(publisher) was taken off replication and publication deleted.
3.Backup made of DB
4.SQL 2000 DB restored to 2005 (standard ed) server instance.
5.In Manangement Studion set compatibility to 90(2005).
6.Merge replication setup to subscribers using a snapshot.
I tried to keep all the settings as close as possible to my replication as it was on the 2000 instance.
The only change in the setup is that the Publisher/Distributor DBs are now running on 2005, nothing changed on the subscriber side.
No performance issues have been experienced while running queries on the Publisher DB but running same queries against subscribers is very slow and prone to repeated timeouts...
If anyone has a similar setup or just wants to give their 0,02c please feel free to make any suggestions ...
July 14, 2008 at 3:43 am
Came accross something rather strange.
I examined the setup of each subscriber and found that the subscribers that were not experiencing timeouts were on SQL 2000 sp3...
The performance is still slower than on the Publisher but not as bad as to cause timeouts.
I remember while setting up the Publisher there was an option to set compatibility for subscribers, I selected the SQL 2000 (using Management Studio).
Is it possible to set compatability to different service packs?
While reading books online I came accross the following:
-- Create a new merge publication.
USE [AdventureWorks]
EXEC sp_addmergepublication
@publication = @publication,
-- Set the compatibility level to SQL Server 2000 SP3.
@publication_compatibility_level = '80RTM';
In any case I've requested that all subscribers be upgraded to SP4 (unfortunately I'm not the one in charge of these servers.)
July 14, 2008 at 4:16 am
I ran EXEC sp_helpmergepublication to see what the details of the publication were and it returned a backward_comp_level of 40.
Now I'm really confused because that level is not listed by MS at all.
http://msdn.microsoft.com/en-us/library/ms176103.aspx
Anyone have experience with compatibility levels in Replication?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply