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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 78

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

749 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