<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
What, if any, is the best type of replication...
17 posts, Page 2 of 2
««
1
2
What, if any, is the best type of replication for implementing on hundreds of databases on one server?
Rate Topic
Display Mode
Topic Options
Author
Message
WWDMark
WWDMark
Posted Friday, February 22, 2013 8:56 AM
SSC-Addicted
Group: General Forum Members
Last Login: Today @ 3:54 AM
Points: 449,
Visits: 353
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
Website:
www.weekendwebdesign.co.uk
Post #1423109
ScottPletcher
ScottPletcher
Posted Friday, February 22, 2013 11:28 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 1,323,
Visits: 1,777
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)
One man with courage makes a majority. Andrew Jackson
Post #1423183
tomes12
tomes12
Posted Friday, February 22, 2013 11:35 AM
Grasshopper
Group: General Forum Members
Last Login: 2 days ago @ 3:53 PM
Points: 16,
Visits: 175
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
Oliiii
Oliiii
Posted Monday, February 25, 2013 12:48 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 11:38 PM
Points: 194,
Visits: 685
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
Oliiii
Oliiii
Posted Monday, February 25, 2013 1:00 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 11:38 PM
Points: 194,
Visits: 685
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
EdVassie
EdVassie
Posted Tuesday, February 26, 2013 4:48 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 2,621,
Visits: 2,759
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.
Author:
SQL Server FineBuild
1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005.
25 March 2013
: now over 23,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
TheSQLGuru
TheSQLGuru
Posted Wednesday, February 27, 2013 6:30 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 3,578,
Visits: 5,119
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 »
17 posts, Page 2 of 2
««
1
2
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
