Alternatives to Microsoft SQL replication

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!
CashbuddiesAsked:
Who is Participating?
 
Nico BontenbalConnect With a Mentor Commented:
Maybe you can use the Resource Governor (http://technet.microsoft.com/en-us/library/bb933866.aspx).
This way you can assign limited resources to support and make sure they don't bring your server down.
0
 
mastooCommented:
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.
0
 
CashbuddiesAuthor Commented:
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!
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
mastooCommented:
Sorry, maybe somebody else will chime in with an idea.
0
 
mcmonapConnect With a Mentor Commented:
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:
http://technet.microsoft.com/en-us/library/ms175158.aspx
http://blogs.msdn.com/b/sqlcat/archive/2008/08/05/microsoft-sql-server-database-snapshots-and-synonyms.aspx
0
 
CashbuddiesAuthor Commented:
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...
0
 
Aaron TomoskyConnect With a Mentor SD-WAN SimplifiedCommented:
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?
http://stackoverflow.com/questions/6484424/sql-server-stored-procedures-and-permissions
0
 
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
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:

http://technet.microsoft.com/en-us/library/ms147331(v=sql.105).aspx

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.
0
 
arnoldCommented:
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.
0
 
Jim P.Commented:
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.
0
 
ZberteocConnect With a Mentor Commented:
For schema changes you don't have to break replication starting with SQL 2005 version. Another option would be the the Microsoft Sync Platform:

http://msdn.microsoft.com/en-us/sync/bb821992.aspx

in more detail explained in:

http://technet.microsoft.com/en-us/magazine/2008.11.sql.aspx

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.
0
 
PadawanDBAConnect With a Mentor Operational DBACommented:
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:  http://technet.microsoft.com/en-us/library/dd171921(v=SQL.100).aspx.

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

Edit:

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: http://technet.microsoft.com/en-us/library/bb522489(v=SQL.105).aspx

Change Tracking: http://msdn.microsoft.com/en-us/library/cc280462(v=sql.105).aspx
0
 
CashbuddiesAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.