Solved

Alternatives to Microsoft SQL replication

Posted on 2013-11-27
13
580 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
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry, maybe somebody else will chime in with an idea.
0
 
LVL 15

Assisted Solution

by:mcmonap
mcmonap earned 84 total points
Comment Utility
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
Comment Utility
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 84 total points
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 38

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 83 total points
Comment Utility
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 83 total points
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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.
Comment Utility
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 26

Assisted Solution

by:Zberteoc
Zberteoc earned 83 total points
Comment Utility
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 83 total points
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Server  2012 Release with lots of Enhancements in Database Engine functions, SSIS, SSRS and some of new services like Data Quality Server and Master Data Service. Of particular interest, and the focus of this Article is SSIS. So, time to elab…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now