Replication after db restore on subscriber 1

[jcaulder]

I was hoping someone could clear up a question I have about merge replication in Sql Server 2000.

It appears to me the process of replication basically creates records in a merge table(s) from triggers that fire when data is modified on the publisher and/or subscriber within an identified article(s).

Periodically, the merge replication agent runs which pushes/pulls the changes to/from the publisher to/from the subscriber.

Now, assuming the above is close to the actual process, my question involves exactly what happens when a subscriber database is restored. I would assume that once a record is replicated to the subscriber, the merge agent marks that record as having been replicated and no longer attempts to do anything with it. Perhaps even removes it from the merge table altogether.

So if the subscriber database contains a table, say tblSubscriber that has received replicated data every hour from 12:00AM until 10:00AM. The database fails at 10:00AM and must be restored from the full backup that was done at 12:00AM(no transaction log backups on MSDE). Replication restarts after the successful restore. All of the data that was replicated from 12:00AM until 10:00AM will be basically lost at this point on the subscriber won't it? Surely the merge agent will not examine the restored database and realize that it is missing the replicated data from 12:00 to 10:00 an re-push it out will it?

Similarly, if the merge tables within the subscriber had not pushed yet when the backup was done, after the restore, won't the agent try to re-push these records from subscriber to publisher since it does not know they have been pushed already?

Hopefully I haven't complicated this question with too much detail. Just trying to understand what happens in a replicated environment when a database restore must be done on the subscriber. It almost seems to me that a new snapshot should be taken and applied and then replication continued.

TIA,

J

 

[ptheriault]

The proper way to restore a database that is participating in replication is to disable the subscription. restore your database then re-snapshot the subscription. You will get lots of errors if you don't.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jcaulder]

I appreciate the reply!

Can you provide any more detail about this though?

Is the process I described correct or is there some other process involved that checks to see if publisher and subscriber are still synchronized?

We have several tasks we need to accomplish with minimal interruptions.

1)Rolling out a new version of the database with a new schema(modified tables, new tables, new stored procs, etc)
2)Archiving data on subscribers without deleting their counterparts on publisher.

We have around 23 subscribers so taking a snapshot and pushing it out at one time does not seem realistic to me for scenario #1. I thought perhaps we could break replication, update the schema on both sides(publisher/subscriber), re-create the articles and publications then restart replication and all would be well. Is this possible?

In scenario #2, all our subscribers are running MSDE which has a 2 GB size limit which we are quickly approaching. Once again, I thought we could periodically break replication, delete records from the subscriber that are old and then restart replication and all would be well. This was assuming that replication is driven solely off of triggers that create records in the various merge tables. Since replication would be disabled during the archival process, it would not write the deleted records to the merge tables and therefore the publisher's copy of these records would remain.

Unfortunately, I have heard that replication attempts to periodically examine publisher/subscriber for differences via an incremental snapshot and that the deletes from subscriber would eventually be propagated publisher even if replication was disabled when the deletes actually occur.

Hopefully someone can shed a little light on this for me. Nothing I've read mentions incremental snapshots. My next step is to set up a test environment and prove/disprove it for myself.

TIA!

J

 

[mrdenny]

What replication schema are you using Snapshot, Merge, or transactional?

We have around 23 subscribers so taking a snapshot and pushing it out at one time does not seem realistic to me for scenario #1.

When you take a snapshot you can reinitalize all the subscribers at the same time and push the snapshot to all of them at once.

Data changes from the Subscriber to the Publisher are only pushed to the Publisher if using Merge Replication. Transactional Replication is one way.

Removing replication should almost never be done.

If you need to make schema changes to published tables, use the Replication procedures to make the changes as this will replicate the changes.

Once I know what replication schema you are using I can advise on how the process it running.

If the two gig limit is that much of an issue on MSDE you might want to look to upgrading to Workgroup Edition. It's less than standard and doesn't have the file size limit.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jcaulder]

We are using merge replication.

I just can't see how to go about pushing snapshots to all subscribers at once. Snapshots are about 1GB each trying to be pushed to 23 subscribers. We are trying to upgrade in a reasonably short timeframe to minimize service interruptions as described in scenario #1. Pushing 23 GB does not seem reasonable. All the data is already in all locations. We just need to update the schema, add some tables and stored procs and populate the new tables with some initial data.

It just seems to me the quickest way is to:

1)Ensure all sites have replicated, then stop replication and take all DB's offline
2)Run a DTS script on each subscriber that creates identical schemas and populates the new tables with data such that subscriber and publisher are synchronized
3)Create a new publication based on the new and existing articles
4)Bring db's back online and restart replication


********
I just returned from the bookstore where I was encouraged to read that snapshotting and subsequent synchronization is an optional task. It is perfectly fine to create a synchronized subscriber database by simply restoring it from a master copy stored on CD for example. What I read seems to give merit to what I am proposing. The books said that synchronization can be skipped and SQL Server will assume that you have already handled synchronization through different means. Obviously if you haven't ensured that the instances are truly synched, there would be problems.
*********

You mentioned allowing the publication to push schema changes out but it cannot push out new tables/objects can it? These are not articles in the publication since they are new. So these must be handled in some special way, either through a new publication that adds these new items and a re-synch which puts me back in the dilemma of having significant downtime or else the process I described above.

Perhaps you can advise on my reading or perhaps I've misunderstood what creating and applying a snapshot entails. Just seems unreasonable to have to push 23 1GB snapshots when 98% of the data is already on the subscribers.

Thanks again for the replies!

J

 

[mrdenny]

First, I think you need to change your replication schema. Based on what you've described so far Merge Replication isn't the correct replication schema for you. Transactional sounds more like it is. Merge replication lets you make changes at the subscriber which are then pushed back to the publisher. It sounds like you don't want this to happen.

Over night I was thinking about your setup.

Something that I was thinking about was doing some row level filtering on the tables that you are planning on purging. If I was dealing with this system I'd add a column to the tables which I need to purge the data from at the subscribers called Purged (or something like that). Have the column have a data type of BIT with a default of 0. When the tables need to be removed from the subscribers change the value from 0 to 1. I'd then resetup the replicaiton using the Purged column as a vertical filter so that only rows with a 0 get replicated.

Then setup your snapshot and have it push out the records that are left to the subscribers.

Replication should be smart enough so that when the value of those records is changed from a 0 to a 1 the records disapear from the subscribers. If it doesn't a cleanup job would be easy enough. The nice thing about this technique is that when ever the snapshot is repushed to the subscribers only the records which haven't been purged are transmitted.

Correct, when new tables are added to the database and they need to be replicated you would need to add them as a new article and push out a snapshot. But with the row level filtering in place this shouldn't be that big of an issue. After you make the change to the publisher if you can't update the subscribers right away, simply schedule the snapshot to run at night as when it's done have a T/SQL script which goes and makes the subscriptions as needing to be reinitalized.

A 1 Gig snapshot isn't all that big. We have a table which is replicated which is about 10 Gigs of data in it, and has about 80 subscribers which receive it.

While we don't push the snapshot to them often, if replication ever gets that far behind we will.

You may want to look to having a couple of publications (which isn't technically recommended, but sometimes the not recommended can be the best method).

One publication for the large table, and a second for the small lookup tables which are fairly static, but get a new table every once and a while. This way when you add a new article you only have to push out a few megs of lookup data, instead of the large table(s).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jcaulder]

Amazingly enough, last night and into this morning, I devised a very similar plan of attacking the archiving data problem. I created a test table with an 'archived' bit column. I set this to 0 by default when new records are inserted. My plan was to delete records through a job on the subscriber then update the publisher 'archived' field to 1 for all records deleted. I created a publication that filters on this field so if the publisher updated the field, it wouldn't try to send this update to subscriber. I didn't realize until after I had set it all up that simply creating the filter and setting it to 1 would cause deletes at the subscriber. But lo and behold, just as you've suggested, marking my records in the publisher as archived = 1 makes them disappear in all subscribers. This seems to be a very good plan from my initial testing!

Concerning the method of replication, I think merge is the only option. Transactional by default is designed for read-only copies at the subscriber. There is a new method that allows for "updating subscribers" using transactional but this method does not allow for subscribers that may be disconnected for periods of time. Our subscribers must have high autonomy. A large percentage of new data is being created at the subscriber. There may be hours of time when our subscribers go offline for various reasons(internet down, etc.) but must still be operational within the site. Everything I read last night said that updating subscribers using transactional was for systems like ATM machines that had virtual guaranteed 100% connectivity back to the publisher. That is not the case here. Furthermore, we may have multiple sites that are attempting to change the same records at the same time. Merge replication largely handles conflicts related to this for us.

If you still think transactional is the correct approach, please expound further as I would love to switch to the simpler model, merge being the most complex it seems. I think I just didn't explain the model well enough and you would agree based on the above that merge is the correct model.

I am still trying to fully comprehend part of your suggestion. You mention 'whenever a snapshot needs to be repushed to the subscribers'. From my reading, the only time a snapshot ever needs to occur normally is when the subscription is first created. From then on, replication keeps the tables in synch correct? However, if the subscriber goes offline for 2 days for example and there are 100,000 updates that now need to be pushed, replication may not be able to catch up thereby requiring manual intervention via a new snapshot and reinitialize. While this seems to make sense when dealing with one rogue subscriber that went offline for a period of time, I still cannot understand why I would need to push out 23 GB of data(during an upgrade to subscriber) when all I really want is to send two tables with 14 new rows and perhaps 25 new columns to existing tables. We are looking at growing to a point where we may have 500+ subscriber sites all while the amount of data is growing also. So this could easily grow to 500 * 1 GB trying to be sent, all at the same time, all while requiring very little downtime to sites.

It just seems I could run a DTS script that is 50k with the same result. I think I am still missing a critical piece of information that would make the lights come fully on for me.

Guess I'll experiment some more and continue my reading. I still can't see why my steps 1-4 above are invalid(they probably are, I am still ignorant as to why)!!

THANKS again for the post!!

J

 

[mrdenny]

If you need to send data back from the subscriber to the publisher then yes, you'll need to use merge replication.

When ever a new article is added to a publication the snapshot is pushed out to all the subscribers.

That's why I was thinking about setting up two subscriptions.

The first would contain all the look up tables. The second would contain all the big tables. This way when you add new look up tables to the look up tables publication you only have to re push a very small snapshot.

When you add columns to a table that is published the best way to do it is via the GUI or the replication procedures. This way all the changes are replicated out to the subscribers along with the regular data change transactions.

Does that make sense?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jcaulder]

I do think it makes sense. As you've suggested, with the archiving solution in place, the snapshots potentially become very manageable since perhaps only 14 days worth of data is enough for the subscriber to maintain. They currently have years of data.

Combine that with two publications and I think it is the solution we need.

I think our plan would be to do this entire operation at deployment of our next release. Create the new publications, push the greatly reduced snapshots to subscribers and have a job that runs nightly to mark records as archived on our publisher that are more than 14 days old.

My only question still is about some of the finer details of merge replication and any automated synching it does but I am heading back tonight to the bookstore for more in-depth reading.

I really, really appreciate the advice!! Replication is new to me and we have some very real problems to address quickly. I feel armed(and maybe dangerous) at this point.

THANKS!

J

 

[mrdenny]

No problem.

The best advise I can give when it comes to replication is test first in a non-production enviroment and document every little thing that you do so that you can do the exact same thing in production.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com