Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

What, if any, is the best type of replication for implementing on hundreds of databases on one server? Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 1:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 21, Visits: 230
Hi all,

We have a 2 node (active/passive) SQL 2008 SP1 Enterprise Cluster that hosts about 500 databases.
Now while the Cluster environment gives us the High Availability, we would also like to build some sort of Disaster Recovery solution as well.
We're considering replication, but I'm not sure how big of an additional load will replicating 500 databases to another SQL Server create on the Cluster.

Has anyone been in a similar situation?
Any thoughts/suggestions on how we handle our Disaster Recovery solution for this environment (the DR site can be a few hours "old")

Please do ask for additional info if you think something is unclear.

Thanks in advance.
Post #1422436
Posted Thursday, February 21, 2013 1:56 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
Although replication may seem like a good idea, I'm not sure it was intended by Microsoft to be used as a DR solution. Have you considered mirroring or log shipping?

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1422439
Posted Thursday, February 21, 2013 3:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 21, Visits: 230
Hi, thanks for the quick response.

Of course, I see what you're saying.
Yes, mirroring/log shipping is all an option and under consideration.

That still asks the question how would it work if I enable it for 500 databases?
How much additional load would it create? How much bandwidth would it take up?

I've done tons of DR solutions in the past, but never on this scale, and I can't just start ramping up log shipping for the databases one by one just to see "how will it go".
Is there maybe a way I could test/estimate/calculate this sort of thing before I actually go and implement it for 500 dbs.

Any ideas/thoughts. Thanks
Post #1422469
Posted Thursday, February 21, 2013 3:31 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
tomes12 (2/21/2013)
Hi, thanks for the quick response.

Of course, I see what you're saying.
Yes, mirroring/log shipping is all an option and under consideration.

That still asks the question how would it work if I enable it for 500 databases?
How much additional load would it create? How much bandwidth would it take up?

I've done tons of DR solutions in the past, but never on this scale, and I can't just start ramping up log shipping for the databases one by one just to see "how will it go".
Is there maybe a way I could test/estimate/calculate this sort of thing before I actually go and implement it for 500 dbs.

Any ideas/thoughts. Thanks


To be honest I've never been exposed to a SQL instance with so many databases so this is new to me too.

Let's see what the other experts have to say. I'm intrigued!


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1422478
Posted Thursday, February 21, 2013 8:50 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:20 PM
Points: 3,008, Visits: 2,608
I am not sure that mirroring will be a viable option if you are needing to mirror 500 databases. MS publish a document on the server resources required (http://support.microsoft.com/kb/2001270). Have a read before getting too serious about mirroring.

Replication really isn't a DR type of product - it doesn't do DR all that well. You would need to deal with failover yourself. As far as I am concerned, it would be fairly easy to handle the first failure with a set of manual steps needed but going back will most likely require either another publication to the original server and/or backup restores to that server with some possible outage needed to achieve it).

Your SAN vendor may be able to help out - some have technology to sync SANs between data centres. This is not my area of expertise or experience so I can't add anything else on this.



Post #1422856
Posted Thursday, February 21, 2013 9:30 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, September 29, 2014 6:50 AM
Points: 617, Visits: 1,278
Hi,

Replication is more like a reporting solution, it gives you choice of tables ore even columns that needs to be replicated.

Mirroring can be considered as DR (I had a set up we implemeted Mirroring across datacenters) but it is costly either in terms of budget or performance.
(One advantage for mirroring is it give you capability for failback (role reversal))

I think best option for you is log shipping. You can build it over your existing backup plan. Considering the number of databases only thing you might need have a good plan for monitoring, failover and faiback.
In log shipping failback is possible but you need to create your on custom steps.


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1422864
Posted Friday, February 22, 2013 5:09 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, September 1, 2014 7:39 AM
Points: 642, Visits: 520
Hi, I love DR so I'm also intrigued as to what people think....

Firstly, how big are the Db's on the server and secondly, do you have a budget to work with for your DR solution or do you have to 'price up' various scenarios first?

If budget isn't an issue and the DB's aren't too big then I'd definitely look at mirroring with an automatic fail-over and witness server. If you're worried about overhead etc then maybe have a look at switching it to asynchronous mirroring (especially as you've indicated that the 'DR site can be a few hours old'). Depending on what transactions/usage your primary server gets and how that affects performance, you could also log ship to a 'warm standby' just to be on the safe side (overkill though maybe if mirroring is in place - depends how paranoid you are!! ).

Do you have a testing environment that you can use, I'm not a huge fan of installing SQL Server on a virtual server but that might be one way of creating a viable test environment to check the impact of things like mirroring/log shipping on the sort of scale you're talking about.

Do you have a plan with regard to backups etc?



MCITP
MCTS - E-Business Card
Twitter: WWDMark

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: www.markallen-online.co.uk
Business Website: www.weekendwebdesign.co.uk
Post #1422988
Posted Friday, February 22, 2013 5:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 21, Visits: 230
From what I gather and researched, I would agree that log shipping would probably be the way to go.

Currently the backup strategy is just "Daily Full", but implementing custom log shipping in a way that it would be handled by just a few jobs (instead of jobs per database) wouldn't be that difficult.
I'm still not that clear on how it would impact the server having to tlog ship 500+ databases.

Do you have any ideas how to test/calculate/analyze this before-hand?

Thanks,
Post #1422994
Posted Friday, February 22, 2013 6:10 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, September 1, 2014 7:39 AM
Points: 642, Visits: 520
tomes12 (2/22/2013)
Do you have any ideas how to test/calculate/analyze this before-hand?,


I can't think of a way (other than creating a dedicated log shipping/mirroring test environment and using copies of your production DB's within that environment) that you'd be able to test or analyse the impact of the solution.

Does anyone have any other thoughts?



MCITP
MCTS - E-Business Card
Twitter: WWDMark

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: www.markallen-online.co.uk
Business Website: www.weekendwebdesign.co.uk
Post #1423013
Posted Friday, February 22, 2013 8:47 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 206, Visits: 765
We've tried mirroring many DB on the same instance and we started having problem at around 50 DBs, that might have changed on SQL 2012 though but mirroring is not designed for that kind of things.

Log shipping is the only real solution (beside geo-cluster + SRDF SAN).
The initial load is pretty easy to compute, just check the size of all your full backups, copy of few of them to your DR site and find out an AVG copy speed (in your case you might want to get another network card dedicated to copying files to the DR site).

For log shipping to work all your DB need to be in full recovery, if that's not already the case you should switch all the DBs to full recovery and see how much log backup you get per hour.

The limiting factor is the network speed, doing log backup is only an issue on heavily used system.

Handling the Copy/Restore/Cleanup is just an industrialized version of any log shipping scripts, powershell would probably be the quickest way to get you started. Log shipping being so old there are probably scripts floating around resilient enough to resist anything you can throw at it.

Depending on your RTO and RPO, and the size of some DBs you can do a mix of log shipping and regular backup/restore (Full & Diff can also be mixed).

In the end it all depend on how much data can be lost. If "none" is the answer, check the price of a geocluster + admins with enough experience + 2 san box linked with SRDF + san admins, show it to the service owner and ask the question again.
Post #1423106
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse