Practical Tips for Initializing Replication Snapshots

I’ve recently spent a bit of time helping a client initialize snapshots for a number of production databases. Only, unlike previous engagements where this was pretty easy due to the fact that the databases being replicated were either fairly new or continuously managed, these databases are years old and have seen numerous iterations of developers without a full-time DBA on hand to help keep things nice and tidy.

Consequently, when it comes to setting these databases up for Merge Replication, I’ve been bumping into gobs of the dreaded ‘script such and such’ could not be propagated to the subscriber errors – as shown in Figure 1.

Figure 1: The last thing you want to see after SQL Server has begun applying a 30GB snapshot at the subscriber.

The schema script ‘SprocOrViewNameHere_157.sch’ could not be propagated to the subscriber

Commonly when you run into these kinds of errors, they’re typically due to some sort of permissions or configuration problem – especially if you start seeing them immediately on the subscriber once the snapshot begins to be propagated.

On the other hand, if ‘parts’ of the snapshot end up getting deployed BEFORE you run into this error, it could be that it’s still crashing or erroring-out because the sprocs or views you’re scripting to the subscriber include GRANT statements that can’t be replicated because the users (or, more importantly: logins BACKING your defined users)  you’re trying to grant permissions to haven’t been added to the target system.

But, if you’ve ruled out those kinds of problems, and you’re dealing with lots of older views, sprocs, and udfs that may no longer be in use or which might be suffering from other scripting errors or problems, then trying to track down root causes can be painful.

And the reason that this can be painful is that you’ll only find these problems after you’ve waited for the time it takes to generate a snapshot, push it over the wire, and start up the propagation process from scratch, over and over as you’ll need to restart each time you find a sproc of view that has some sort of syntax error or problem that prevents it from being replicated to your target. Meaning that once you find it, address whatever problems its causing, and figure out what kinds of work-arounds you’ll need, you’ll have to START the whole process over again.

Which, in turn, means that if you’re not careful, you can quickly waste a lot of time.

Some Simple, Practical Techniques to Cut Down on Wait Times

One obvious technique you can use in cases where you’re trying to replicate gobs and gobs of older code is to simply script all of the older code from SSMS, copy/paste it (either as a file or via your clipboard) from the target and destination and attempt to execute scripts on the target server. Of course, the problem with this approach is that you’ll also need to replicate the schema that this code depends upon, and getting all of your ducks in a row to perfectly ‘replicate’ schema and code at the target can frequently be a bit of a pain. Even worse, however, is that while SQL Server replication does, in theory, simply mimic the process of scripting objects at the subscriber, the way in which it does so is typically a bit more complex and different than merely scripting out schema and objects and running scripts that the subscriber – meaning that even IF you take this approach it’s NOT going to be foolproof and you can easily find yourself pushing say, a 30GB snapshot over the wire only to find that after ‘churning along’ for a few minutes it will still crap-out on a particularly nasty sproc or view that managed to sneak its way onboard your snapshot – even AFTER you did due diligence to try and weed it out via ‘pre-emptive scripting’. (For example, maybe you’ve got an ‘auditing’ sproc that used to do something like INSERT INTO SomethingAudits SELECT *,GETDATE(), @@USERNAME FROM SomethingTable WHERE blah = ‘yada’. Only, ONCE a database has been modified for Merge Replication, a new RowGuid will have been ‘plunked’ to the end of tables making the worst-practice of SELECT * problematic for certain types of INSERT statements and other operations).

Baby-Stepping Your Snapshots

Consequently, if you’re bumping into numerous problems, one quick and simple way to make your life a bit easier is something that’s quite simple – but which will save you a bunch of time. The trick, simple: stop including tables with your snapshot.

Once you’ve identified that you’re bumping into the kind of scenario where you’ll try and fail, try and fail, and try and fail due to various scripting problems in your sprocs or views, switch to a ‘baby-step’ approach that’ll make things easier for you to tackle. Start by just replicating your tables – and nothing else. On larger databases this will generate a large snapshot, but one that should go through without any problems. And once it does, you’ll have all the schema (and data) in your target database needed to let SQL Server start making sense of your sprocs, views, and udfs.

Then, once your ‘data snapshot’ has been delivered, simply tear down your existing publication, and recreate it with ONLY sprocs, views, and UDFs (or you can further ‘baby-step’ your way along with just one of those options). Doing so will mean that instead of taking long amounts of time to script, package, and transmit data, you’ll only be spending a tiny amount of time as SQL Server bundles up some code, attempts to deliver it, and then crashes into another scripting problem.

Yes, you’ll STILL have to deal with hideous ‘schema script could not be propagated’ errors and their root causes – but at least you now won’t be waiting forever for them to crop up.

Likewise, if you’re at a loss to figure out why a script is failing (i.e., you want to get at the root cause), don’t forget that you can go grab the script from the snapshot (even if it’s compressed into a cab – though you might need a third-party tool on some versions of windows) and execute it on the target server to get a feel for exactly what kind of error SQL Server is running into when it raises those dreaded ‘could not be propagated’ errors.