?
Solved

Alternatives to Microsoft SQL replication

Posted on 2013-11-27
13
Medium Priority
?
1,040 Views
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!
0
Comment
Question by:Cashbuddies
13 Comments
 
LVL 21

Expert Comment

by:mastoo
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.
0
 

Author Comment

by:Cashbuddies
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!
0
 
LVL 21

Expert Comment

by:mastoo
ID: 39682312
Sorry, maybe somebody else will chime in with an idea.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 15

Assisted Solution

by:mcmonap
mcmonap earned 336 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:
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
 

Author Comment

by:Cashbuddies
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...
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 336 total points
ID: 39687829
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
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 332 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?
http://stackoverflow.com/questions/6484424/sql-server-stored-procedures-and-permissions
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 332 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:

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
 
LVL 81

Expert Comment

by:arnold
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.
0
 
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.
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 332 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:

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
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 332 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:  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
 

Author Closing Comment

by:Cashbuddies
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

839 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