Free Tool: Replication Schema_Option Translator

How’s Your Hex?

I don’t spend nearly as much time with SQL Server Replication as I’d like. It’s a fun technology (even with its warts) that provides some amazing capabilities and options. Yet I usually only bump into a few situations a year where I’m actively helping clients with it.

Either way, nearly EVERY time I work with replication topologies, I end up needing to figure out just what the @schema_options argument for a given article actually mean – as I’m not that good at converting the ever-increasing list of options available for sp_addarticle/sp_addmergearticle into bitmaps in my head. Or, more importantly – teasing them apart into a set of options.

For example, given that the following is a bitmap for Merge Replication:

0x000001450D031FD1

How quickly can you tease that apart to tell if it’s allowing permissions or not? If you’ve been working with replication a long time, you can almost spot it instantly (reading hex and knowing the options isn’t exactly rocket science). But, if you’re like me and go through periods of messing around with replication and then longer periods where you do nothing with it, you’ll find that figuring that out can take a second or three.

SQL Server Replication Schema Options Reader to the Rescue

Consequently, being the programmer-type, I went ahead and whipped up a quick tool that takes in a given bitmap, translates it into a set of options, and then runs through those options (comparing them to the bitmap) to give you a breakdown of which options have been selected.

Then, once the mechanics of this solution were working – along with the ability to let me MAKE CHANGES to those options and ‘translate them back’ into a working bitmap, I went ahead and added in some additional logic and functionality that lets this tool work against different flavors of replication (Snapshot/Transactional and Merge) as well as against different versions of SQL Server (2000, 2005, 2008/2008 R2).

I’ve used this tool a bit on my own for some current projects I’m working on, and therefore figured I’d make it available to anyone else who finds themselves in a similar predicament.

Free Download

If you’d like a copy of the tool, it’s available for free on my SQL Server Consulting site. It’s nothing huge or earth-shattering. But hopefully it can save you some time or make your life a bit easier if you’re working with SQL Server Replication. And, of course, if you can think of ways to make it better or more productive, just let me know.