February 14, 2008 at 9:53 am
Ok i need some help here, i am just simply not sure how to go about this. The scenario is as follows:
We have SQL Server 2005 running on our server and we host at current, approximately 100 databases on there. The number of databases we host is going to be increasin dramatically over time (possibly 400 - 500 within the year). A backup plan has been setup for Full, Differential and TLog backups as expected but i have been approached about a new backup plan.
Basically my boss has bought a new server and wants to be able to "mirror" our setup onto this second server, with regualar updates on the second server so that, should the server OR particular database/number of databases fail, we can simply switch over to the second server. The software we use enables us to point to a different server no problem, but with the number of databases we hold, is this possible? Database Mirroring in SQL Server 2005 wont support this number of databases i know.
And i am unsure how efficient setting up replication for EVERY database would be, i would REALLY appreciate some advice on this - i was happy with the backup plan in place but they feel the time required to restore this many databases would be too great so want to be able to switch between effectively "synchronized" servers for efficiency. Please tell me if this is possible.
Thanks in advance 🙂
February 14, 2008 at 3:56 pm
Before you say it's not feasible to use mirroring, you may want to test it. I think Microsoft recommend 10 as a max, but if the load is light on each database you may be fine with more (and I would assume that it can't be that great and still support the number you mentioned). Really it's a question of resources - bandwidth/latency will be a big factor, CPU less so, and also the backup specs - if you use synchronous mirroring and the backup cannot log the changes quickly enough, it will impact the primary.
Also consider that SQL2008 will have log compression which should ease the network load (albeit at the cost of some cpu).
February 14, 2008 at 4:02 pm
One thing that i can think quickly for such a huge number of databases is using Doubletake for replication of the databases at the file level and then using a custom script to attach the database files to the other server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 14, 2008 at 4:59 pm
Also - just so there's a distinction: synchronous mirroring and replication (synchronous) are not backup solutions. They're High Availability solutions.
Meaning - your backup scenario doesn't get replaced by mirroring or replication, backups and mirroring/replication/clustering deal with two non-overlapping types of issues.
For example - mirroring (certainly synchronous) is worthless to help you with data corruption, lost data, a bad day in query analyzer, etc... Since each change would happen essentially instantaneously on both servers, there's no "backup".
This, on the other hand provides for some amount of hardware redundancy (depending on what option was picked).
Snapshot replication is arguably a backup solution, but again - it's got very little "depth", so you'd need to know that there's an issue within the frequency of your replication schedule (6 hours? 12 hours? 15 minutes?).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 15, 2008 at 1:43 am
Yea this is basically what i thought. I tried to put them off the idea but it seems fixed, which is a pain. And yea, i agree - it would not be a replacement for the backup plan already in place, and i would obviously still maintain that. I have never worked with this many databases before, and i can understand their anxiety over it all of course, afterall, if they lose these databases they lose their business. The reason i have disregarded Database Mirroring is because i have seen people with similar setups who have managed to mirror a number of databases (maybe 60 or so) but then get stuck - and with my setup already at 100 databases and the potential to climb to any number of hundreds, i would rather find a solution that will DEFINATELY support this future expansion.
Ill take a deeper look into replication but it seems to be the general consensus that it would be inefficient to do so. Thanks for all the info everyone....any more advice would be appreciated as always 🙂
February 15, 2008 at 1:59 am
Log shipping has been suggested to me, and i had a quick look at Redgate SQL Backup...seems like might be a good solution. Ill need to look further into it of course. Thanks again for all the advice.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply