November 10, 2010 at 2:56 am
Hi,
I have had experiences with mirroring, log shipping and other failover/availability/disaster recovery solutions, but never in this kind of environment.
Basically we have 3 SQL 2008 Servers with about 500 Databases each (ranging in size from around 50MB to around 80GB). There is only one other SQL 2008 Server that acts as the failover system.
Currently there's a small vb.net app that creates backups of the 3x500 DBs, copies all the 1500 .bak files to the one failover server and restores them all there (every night), so that one server has about 1500 DBs on it.
I am new in the company and don't know more details about the work of the vb.net app, other than it is custom 'home-built' and it does what I described above (nothing more special).
I was thinking mirroring could do it, but that doesn't quite go well with 3x500 DBs, so I was wondering what other approaches are possible, 'cause I have a feeling something much better might be done about this.
Providing some details with your ideas would be very helpful.
Thanks,
Tomas
November 10, 2010 at 5:15 am
With no mirroring, clustering then I would suggest all you have here is a form of backup.
If one of your 3 servers did go down. And you bring this backup server online, you would still have to point each and every application and any other connection strings to the new server.
unless you changes the DNS and IP settings to match that of the failed server. but then what if 2 servers or all 3 failed?
How would you change the connection strings of the apps then?
You wouldnt and could not mirror this many databases to 1 server, the network would fall over
My first question would be are any of these systems critical?
If you only have a fraction of them that are critical then consider mirroring them only, I am sure not every one of them databases require HA or DR
If you have the funds available then this would be a classic case where you would want to cluster these servers.
If this is something you would want to think about then consider what hardware you currently have and if it would eb compatiable.
November 10, 2010 at 6:16 am
Thank you very much for your reply.
The databases are all critical as they are all separate clients.
And I guess the 'redirecting' of apps and such is somehow been taken care of, as they certainly wouldn't leave it like that (as I said I'm new in the company, still familiarizing with stuff).
Anyway, thanks again, I'll have a look at the clustering option.
November 10, 2010 at 6:26 am
Do you also take t-log backups? and does the vb application copies the t-log backups too on the target server? Or is it acceptable for you company to lose a day's data in case of disaster?
Clustering would be a good option but that will not help if your SAN fails. You have to plan DR plus HA. Clustering would be first choice for DR.
If investment in hardware is not possible, consider log shipping.
November 10, 2010 at 6:33 am
No t-log backups. The company is probably OK with losing one day's data or didn't know better. They will certainly accept a better solution.
As hardware investments are probably out of the question for now, I did think of log shipping, but just couldn't imagine how it would work with 3x500 DBs (server performance-wise), or what would be the best way to set it up.
Thanks.
November 10, 2010 at 6:40 am
tomes12 (11/10/2010)
No t-log backups.
then log shipping is out of question.
As hardware investments are probably out of the question for now, I did think of log shipping, but just couldn't imagine how it would work with 3x500 DBs (server performance-wise), or what would be the best way to set it up.
Even if number of databases were less and you would have considered mirroring, databases need to be in full recovery mode and you need to take t-log backups.
Looking at all budget and other constraints, it looks like current setup is the best:cool:
November 10, 2010 at 7:01 am
hmm 🙂
guess I would just have to add some 'control' tables and procedures to have some sort of tracking the whole process.
November 10, 2010 at 7:29 am
Whate are your SLAs (service level agreements) - RTOs (recovery time objectives) and RPOs (recovery point objectives) for the databases?
I.E.
What kind of outage can they handle?
If you had a disaster in the middle of the day or if one happened at night?
What is the acceptable level of data loss?
Since the backups are being copied to another server and being restored what is the plan in case of one failure?
Would your applications connections be pointed to the "other" server? Would this require a change in the applications or would you use some sort of DNS aliasing to repoint the applications to the new location?
Mirroring would be out the window even if you were in Full Recovery mode as a server level strategy, which as ps pointed out is required. Mirroring opens up a thread per database being mirrored and at 500 databases you can see why this wouldn't work as a whole server strategy.
However Mirroring is a Database level technology and it may be applicable to some of your databases but not all. Mirroring would also require that the application be mirroring aware, and could require code changes since it is not currently in place.
I'm guessing because you have different applications they may have different recovery levels.
I would take a look at what the current plan is, and what the current expectation of the customer is.
But when you look at High Availability you have a lot of different choices. Remember there is a difference between:
Hot Standby - A server that will execute a recovery process without human intervention required
&
Warm Standby - A server that will have copies of the data, may have significant data loss in case of disaster depending upon the frequency of data refresh, and requires human interaction to come online.
Hot Standby's would be HA High Saftey Mirroring, Clustering, and Peer to Peer Replication (the last is not a trivial thing to set up)
Warm Standby's - All other Mirroring, Log Shipping, and most other replicated environments.
there are caveats to replication but it can be used.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
November 10, 2010 at 7:59 am
Thanks a lot for the thorough answer.
I have thought of all those approaches but I always kind of stumble when faced with the number of DBs and hardware and investment limits.
So I guess as ps. already mentioned I'm left with the current solution as the best one I got for now, and I'll just try to make it more reliable, controllable and 'trackable' (as it isn't now).
Thanks for all your time and answers, I think I got the info I wanted.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply