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 Friday, February 22, 2013 8:56 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:38 AM
Points: 642, Visits: 519
Oliiii (2/22/2013)
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.


@ Oliiiii - What problems did you come up against when you hit 50 DB's, I have heard that MS recommend no more than 10 databases on a 32bit system as well - is that you're experience?



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 #1423109
Posted Friday, February 22, 2013 11:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 2,044, Visits: 3,059
You need to do mirroring at the disk level, not the database level. The number of databases is then irrelevent, it's only the total volume of changes that is an issue. A good mirroring system will support a lag time when things are very busy and/or the channel to feed data to the mirror is slow/busy.

Trying to recover 500 separate databases using log shipping would be quite a task, even if automated. Make sure you can accept the length of time it would take you get 500 databases back up and running before choosing that solution.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1423183
Posted Friday, February 22, 2013 11:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:35 AM
Points: 19, Visits: 222
Nice to see a discussion getting started, and I really appreciate all your ideas.
Just talked to management, and before we start looking at geo-clusters, mirroring at disk level (really like that idea), as always, I need to try and figure out what is possible without any of that.
We all agree though, seeing that network bandwidth would be our bottleneck, that we should definitely invest some in that area.
Btw, db sizes range from a few MBs to a several dozen GB, with most of them being around 5GB.
I would also add that there's no considerable growth observed during the last year, so no problems there.

To add one more level of complexity, we have 2 production clusters with 500 DBs EACH, and we would like to DR that to another location but to ONE Server if possible (mind you this would really just serve the disaster scenario, so the server probably won't be as powerful as those on Production) ?

Do you think this changes anything somehow?
Thanks,
Post #1423189
Posted Monday, February 25, 2013 12:48 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:21 AM
Points: 206, Visits: 764
WWDMark (2/22/2013)
Oliiii (2/22/2013)
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.


@ Oliiiii - What problems did you come up against when you hit 50 DB's, I have heard that MS recommend no more than 10 databases on a 32bit system as well - is that you're experience?


We started to get have more and more delay in the synchro and a once any of the DB required more resources, the effect would impact all the other much quicker. In the end the DBs would fail several time a day, even with high timeout.

We don't have 32 bits server, but for 64bits our local msft contact said we should not expect it to work beyond 50 DBs mirrored (no idea if that's an official recommendation or just a friendly advice).
Post #1423491
Posted Monday, February 25, 2013 1:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:21 AM
Points: 206, Visits: 764
tomes12 (2/22/2013)
To add one more level of complexity, we have 2 production clusters with 500 DBs EACH, and we would like to DR that to another location but to ONE Server if possible (mind you this would really just serve the disaster scenario, so the server probably won't be as powerful as those on Production) ?

Do you think this changes anything somehow?
Thanks,


It all come down to how much data goes into the log files, so you need to find that out first.
Unless your DR server is really underpowered, you'll have network speed issue before you have restore speed issue.

If you do log shipping with 2 source servers, you'll have to make sure you have no DB name conflict or login name conflict.

On a side note, avoid copying files around, backup directly to the DR server (trough a share) or to a network share and then restore directly from there. Copying files around in windows might bring it's own memory issues (especially if you have big files).

And don't forget to synchronize your logins and specific jobs (if any)
Post #1423493
Posted Tuesday, February 26, 2013 4:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 2,868, Visits: 3,214
As always, you need to look at your RTO (Recovery Time Objective), your RPO (Recovery Point Objective) and the value to the business of lost time or data. It is also good to know what is the business driver that is making your management look at this issue.

If you know all this than you can architect a solution that meets business needs and which will have budget made available. If you do not know these then you are working in a vacuum, and you could find your plans fly apart when they get a business or budget review.

With large numbers of databases, then miroring at the disk level definitely minimises the setup and support work, but without knowing the RTO and RPO it may not be the best solution for you.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1424005
Posted Wednesday, February 27, 2013 6:30 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 4,350, Visits: 6,162
1) Your server would likely die a horrible death if you set up replication on 500 databases, unless they were mostly read-only.

2) Likewise for database mirroring (even asynchronous).

3) I built a custom log-shipping process for a client that managed over 7300 databases on a single server, it was fairly complicated but bullet-proof once I got all the kinks worked out. I even had the backup server go into standby-mode for the restored databases so they could be hit for read-only reporting during the day. This isn't something we can do via forums but I would be happy to engage with you to see if we can modify the system I built to work in your environment to fit your needs.



Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1424496
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse