Alternatives to Microsoft SQL replication

Posted on 2013-11-27
Last Modified: 2016-02-11
Hi All
One of our companies products currently has aprox 12 Dbs in total with 10-100Gbs in each SQL DBs
The customer Support team need to be able to query these databases real time and extract information from them...

We do not trust the customer support team with access to the Production cluster so we currently have transactional replication setup which replicates the DBs to a another standby server that the Support team can query.

Why don't we trust them? well it seems even with Read only access they can still manage to craft complex or inefficient queries that can bring the DB cluster to a halt!

Issue for us is that every time a new release of the product is made (every month) there are usually some sort of schema changes.... As a result we frequently have to completely remove the replication publishers and subscribers and basically start it again...

Of course during the time that the DBs are re-replicating the support team can not query the databases... and so are effectively "blind"

So we are looking for viable alternatives to SQL transactional replication?

-Log shipping is no good as latency is too bad (e.g 15 mins) and the DBs have to frequently be in single user mode if the shipping time is brought lower
-SQL mirroing is no good as the mirroring task needs single user access on the mirrored DBs
-DoubleTake is no good as the replicated MDF/LDFs need to be kept offline whilst DoubleTake is replicating the data

Can anybody suggest any viable alternatives?

Of course having a reporting interface on the product with canned queries would be the best solution... but until that is developed by development -  the SysAdmins get stuck with this issue!
Question by:Cashbuddies
LVL 21

Expert Comment

ID: 39682216
You don't mention Sql version but I'll mention AlwaysOn.  We're using Sql 2012 Enterprise with AlwaysOn and it gives us a nice low-latency async read-only replica.

Author Comment

ID: 39682234
Sorry Its SQL2008R2 (both Standard and enterprise - 1 DB cluster of each)

Seems AlwaysOn is a SQL 2012 component only

Unfortunately we have no budget for investing in a SQL upgrade (especially at teh price MS are now charging for SQL Ent Cores!
LVL 21

Expert Comment

ID: 39682312
Sorry, maybe somebody else will chime in with an idea.
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

LVL 15

Assisted Solution

mcmonap earned 84 total points
ID: 39682315
An option might be to use mirroring and snapshots combined; this will mean a less than realtime dataset but it should be possible to create a new snapshots regularly - whether it is viable will depend on Customer Support Team.

Some Information:

Author Comment

ID: 39682338
An option might be to use mirroring and snapshots combined
Unfortunately the customer "support" team are more client services than support (i.e.1st level - very junior)

But they dedinately need a real time copy of the data... basically they need to be able to check on the real time progress of a time critical file as it is being processed by the product, they also need to measure/check the time that the processing took etc

So the data can't be more than a 20-30 seconds old in order to be relevat to a time critical process...
LVL 22

Accepted Solution

Nico Bontenbal earned 84 total points
ID: 39687829
Maybe you can use the Resource Governor (
This way you can assign limited resources to support and make sure they don't bring your server down.
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 83 total points
ID: 39688286
Are you storing files in these databases?

Anyway, a simple reporting interface with canned queries sounds like something a LOT easier than replicating 12 huge databases in under 20seconds of lag.

However I understand you can only use the tools you have available and if you have no .net guys around SQL is the tool you have.

So how about setting up a few stored procedures and only granting access to those?
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 83 total points
ID: 39689994
When schema changes are being made on the publishing databases (which sounds like it happens regularly), why can't you propagate DDL changes back to your subscriber? It's not necessary for changes to your data structure to break replication and require a complete re-sync:

If that's not an option for some reason, I'd definitely encourage checking out the Resource Governer (as Nicobo mentioned), as it lets you limit the resources those engineers can consume on your live database, and then restoring their access to the production cluster.
LVL 77

Expert Comment

ID: 39758247
enterprise version
db mirroring, create snapshot from the mirrored db on the mirrored server.

not sure you would want to add overhead on the existing db by creating a read-only snapshot.

Everything requires additional resources.
LVL 38

Expert Comment

by:Jim P.
ID: 39758250
What if you gave the service team a monitoring tool?

I was using Confio's Ignite and it allows drill downs to see what queries/processes are running even in the free version.

That way you get the service team using the GUI.
LVL 26

Assisted Solution

Zberteoc earned 83 total points
ID: 39759325
For schema changes you don't have to break replication starting with SQL 2005 version. Another option would be the the Microsoft Sync Platform:

in more detail explained in:

In 2008 this feature was greatly improved. Basically with an alter statement you can track data changes in any table and then there is a built in mechanism to retrieve that data and propagate it to an external target. The drawback is that you will have to build an application to actually do the sync.

What I did I actually implemented myself a mechanism that doesn't use either of above but it uses triggers on every source table i want to track and stores the PKs for the rows that changed into a sync log table. I then use that info to retrieve the data from those rows and built nsert/update/delete statements that I apply on the target server. In my case I use Python for that but any language would be good.
LVL 10

Assisted Solution

PadawanDBA earned 83 total points
ID: 39760265
Is a schema on the production cluster with views(indexed or not, although indexed would alleviate a lot of the live table contention pressure and give them only the information they need)/sp's providing the canned information they require out of the question?  And then bind their logins to their schema so they just...can't...get...out!!!  Feel free to peruse:

Full disclosure: I like indexed views.
Downside: you'd have to account for schema changes in the views/sp's


And just to point out, I like what Zberteoc said, but you can do a lot of that with Change Tracking/Change Data capture and use that as a data source in a SSIS package that runs at scheduled intervals (Change Tracking is lighter weight).  Kinda like Snapshot replication, but only grabbing the changed data.  If you are interested in looking at it:

Change Data Capture:

Change Tracking:

Author Closing Comment

ID: 39770515
Thanks all for your input. I will be trying some of the suggestions, and I'll report back. For now I am closing this, as it is showing up as abandoned.

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question