Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
T-Bone
Starting Member
8 Posts |
 Posted - 2004-12-16 : 15:33:00
|
| I have a large number of transactional replication publishers pushing data to a central subscriber. The SQL Server version is 2000 with SP3a and the publishers all run on Windows 2000 Professional. The subscriber had been Windows 2000, as well, but it was recently upgraded to run on Windows Server 2003, Enterprise Edition.After the upgrade replication was running fine. After a couple of weeks I had two of the publishers fail. When I dropped and recreated replication using the original scripts the primary key constraints and clustered indexes were removed from the subscriber tables (not the fields, just the constraints and indexes). The publishers and subscriber all have the same data schema. I tried recreating replication from scratch without the scripts but the same problem occurs.Has anyone else had a similar incident? If so, how did you get past it? Thanks for any thoughts or help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-16 : 16:53:37
|
| That's normal. Are you saying that the PKs were there before though? Because they shouldn't have been unless you specifically created them after replication was configured.Tara |
 |
|
|
T-Bone
Starting Member
8 Posts |
Posted - 2004-12-16 : 19:40:58
|
| The PKs were definitely there beforehand (they are even part of the script that creates the database). The biggest problem that is caused by this is that the tables are rather large and there are 27 different publishers trying to replicate sales transactions to the subscriber. When the constraints and indexes get wiped out it is causing the other publishers to time out. I do not remember this happening in the past and I've often had to recreate replication from different locations. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-16 : 19:44:34
|
| What do you mean they are even part of the script that creates the database? Replication creates the tables at the subscribing side, so what scripts are you referring to. The index thing is an option in the replication setup though. It's turned on by default.Tara |
|
|
|
T-Bone
Starting Member
8 Posts |
Posted - 2004-12-16 : 20:00:29
|
| The database already exists at the subscriber and it is set up with the same primary keys (and related clustered index) as the publishers. Replication is set up with the option that the schema already exists. I've got the option set so that it should not be deleting and recreating the tables (which it isn't). All that it is doing is removing the PKs and indexes but the PKs and indexes are identical at both the subscriber and publisher prior to replication starting. Why would the publisher delete them? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-16 : 20:07:12
|
| I'm guessing that the publisher didn't and either someone did this on their own or a snapshot was run and the option was changed to drop the tables and recreate them for a snapshot. It's a guess of course. But I'm pretty sure that there's no way for that to have occurred by the publisher.Tara |
|
|
|
T-Bone
Starting Member
8 Posts |
Posted - 2004-12-16 : 20:12:29
|
| Tara - I appreciate your input. I'm stumped by this and I can't figure it out. I'm going to open up a Microsoft service call on it tomorrow. I'll post the solution (hopefully there will be one) after the call. |
|
|
|
T-Bone
Starting Member
8 Posts |
Posted - 2004-12-22 : 14:51:15
|
| After six hours on the phone with Microsoft Support we were able to identify the problem and then create a work around for the problem (I know that a lot of people like to complain about MS but I have to say that the MS rep was very knowledgeable and helpful).Anyway, I sent MS the database and replication scripts so that they could recreate the situation in their lab. They did not experience the same problem so we knew that the problem did not have to do with the set up but rather the environment here at my office. We were able to verify that the PK constraints were dropped after the snapshot agent ran and then replication would time out before they could get rebuilt. We have 27 publishers to a central subscriber and it turns out that the number of connections and amount of data replicating was interfering with the replication scripts completing their process.The solution is to set up replication but make sure that the snapshot is not created automatically. After replication is created, open the Distribution Agent properties and disable the job. Make sure that the Distribution Agent has been stopped prior to manually running the Snapshot Agent. After the Snapshot Agent finishes running open the snapshot.pre file that it creates (it should be in one of the subdirecteries under C:\Program Files\Microsoft SQL Server\ but you may have to search for it). This file contains the script that deletes the PK constraints at the Subscriber (by running sp_MSdroparticleconstraints). Delete all of the code except for a GO command, save it and close it. Now you can manually start the Distribution Agent and when the snapshot.pre runs at the Subscriber the keys and index won't be deleted. Problem solved.This problem should not occur if you have a small number of publishers and/or a smaller amount of data replicating. If your setup is complicated (with a large number of publishers and/or high volume of data) you may need to use this work around. |
|
|
|
|
|
|
Primary Key Constraints deleted at Subscriber