SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What, if any, is the best type of replication for implementing on hundreds of databases on one...


What, if any, is the best type of replication for implementing on hundreds of databases on one server?

Author
Message
WWDMark
WWDMark
Right there with Babe
Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)

Group: General Forum Members
Points: 745 Visits: 530
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
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7911 Visits: 7155
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
tomes12
tomes12
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 268
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,
Oliiii
Oliiii
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 777
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).
Oliiii
Oliiii
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 777
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) :-)
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5812 Visits: 3866
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12487 Visits: 8554
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 on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search