What You Can Do With SQL Server Replication

Hey again everybody. This is me trying to stick to my self-imposed rule of writing something (re: anything) once a week that deals with SQL Server, so without further ado, here we go.

A very, very basic rundown of Replication goes a little something like this:

• Replication allows you to copy specific data from a database (known as a 'Publisher') to another database (known as a 'Subscriber'). 
• A 'Distributor' determines how the data is received by the Subscriber.
• The replicated data consists of items specified by the admin. For example: specific tables and specific columns within those tables.
• Data is synched between the Publisher and the Subscriber at an admin specified time interval.
• You can have one or more Subscribers for one Publisher.

Here’s a link to give you a much more in depth idea of what Replication is all about, types of Replication and how to set it up for the uninitiated.

Now that we are all on the same page, I will list a couple useful things that you CAN do with Replication:

1. ·         You can use Replication as a warm standby for fail-over.
2. ·         You can use a replicated database as a base for small-medium scale reporting.
3. ·         You can allow offsite, offline users to make changes, and then sync those changes with the database afterwards.

1) You can use Replication as a warm standby for fail-over.

First up, as stated you can use your replicated DB as a fail-over point in the event that your main production DB gets compromised for whatever reason. The idea is, you would replicate your data in its entirety to another DB that’s just waiting to be used. It’s alright in this regard, but I wouldn’t call it an ideal solution.

My first issue is the whole “warm” part of “warm standby”. This means that applications won’t fail-over automatically and near instantaneously; you will need to do some of the leg work with regard to stopping the replication jobs, reconfiguring connection strings to point to the replicated DB, etc.

Secondly, depending on how often you scheduled replication, you may lose some data. For example, if you replicate everything once a day at 1 AM and your DB fails at 7 AM, your replicated DB will not have data after 1 AM. Mirroring is more of what you might be looking into if you really need an instant, accurate fail-over, and I really hope to get a chance to write on Mirroring in more detail in the future.

2) You can use a replicated database as a base for small-medium scale reporting.

Next up, also as stated, you can use Replication as a part of your reporting solution. I prefer using replication for this purpose.

What is nice with replication is that you can specify specific tables and then, to fine tune even more, specific columns in those tables to replicate. Do you know how awesome that is? You can find out what columns are needed for your reports and ONLY replicate the data that is NEEDED by those reports. For a bit of context, we had a DB that was a little over 100 gigs and when we only took out what we needed for reports, the replicated DB turned out to be approx. 40 gigs. The small size, coupled with good indexing of the replicated DB lead to 10 minute reports taking a couple seconds.

Very nice.

Also an additional plus: you are no longer running queries directly on the production DB(s), which could affect overall performance.

3) You can allow offsite, offline users to make changes, and then sync those changes with the database afterwards.

Lastly via replication, offline users (say a user on a laptop without net access) can use data from the last time they were online/synced and make changes to that data and go about their job. When they are done and have returned to civilization (or at least somewhere with a decent net connection), these changes they have made will then be merged with the Publisher’s data and also sent to any other existing Subscribers.

Truth be told, I am only familiar with this type of replication theoretically (which is why I was hesitant to actually put it here in the first place). Once I get a chance to play around with it I’ll update this section.

There are a quite a few other uses for replication but I decided I’d stick with the couple I know. As time goes on I hope to add to this page until I’ve covered most of them. So is the goal anyway.

As usual, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)