How does everyone backup?
-
To answer Will's question about the root drive, you can backup the entire server using some very reliable backup programs, we use CA Brightstor for our development environment and Galaxy Commvault for our production. You can do folder level backups or the entire server backups depending on what your needs are.
Jules
Jules Bui
IT Operations DBA
Backup and Restore Administrator -
Grasshopper - when you say that the daily backups are sent to the file server and then restored to the secondary. Is the restore automated?
Do you get any performance increase by putting your sql server on a seperate machine from your web server? We have a query intensive web application and I figure that tranferring the data from server to server before sending it out would kill any performance increase. Just curious.
I have setup a Windows backup of the boot drive that goes to another drive. That file is zipped nightly and then tranferred to my machine. The boot drive is only an operating system drive, so the file isn't that big.
Live to Throw
Throw to Live
Will Summers -
Yes, the restore is automated. However we're using Veritas for this function so I can't say for sure that you could do it with native SQL Server tools.
And yes, there's a performance benefit in seperating web services from sql services, at least in our environment where we are running Sharepoint Portal. But you also gain security benefits seperating the two. I'm sure you can find some white papers on the microsoft site about that.
When you say "query intensive", whether you mean lots of data being transferred, or lots of SQL processing and aggregation, you're best keeping things seperate. With lots of data transfer, you're bottleneck is still going to be the HTTP pipe back the end user. Or with lots of SQL processing, better to have one CPU dedicated to it instead of hampering it with web processing.
My $0.02.
- Rick
-
Jules (or anyone else),
Being fairly new to DBA stuff, I have a question (or two) about your backup process/strategy. We also use the CA Brightstor backup for backing up all or systems. But, my question is; do you use the SQL Server agent and backup the actual data files or backup to disk with SQL and then backup those files to tape? The issue I have is we will be installing a new Health system which in 5 years they figure will be in the 10 terabyte range. Being a small hospital with minimal funds (and already over budget on the hardware) I am trying to figure the best backup strategy to follow. Any help or ideas would be greatly appreciated.
Kindest Regards,Donald Mayer
Oswego Health -
Donald,
If you have databases which are in the terabyte sizes, I would strongly suggest you buying SQL Litespeed. It is one of the best and fastest database backup softwares in the market with minimal hit on your servers. It compresses the backup file to 1/3 of the normal size. I would back it directly to disk and have Brightstor transfer it to tape if you have the disk space to do this. If not, I think SQL Litespeed can also write to tape directly, but it won't be as fast.
Jules
Jules Bui
IT Operations DBA
Backup and Restore Administrator -
10 terabyte database maintenance for a begining DBA? Ouch. I got three things to for him to do on this project - Research, Research, Research. I hope you have high end hardware to support this database.
Live to Throw
Throw to Live
Will Summers -
Once you start to approach or exceed the terabyte range you need to start thinking in terms of differential and file group backups. SQL Litespeed is a great product, but I wouldn't expect it to fully backup a 10TB database in an acceptable timeframe.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I hope to get some help with idea's from the Vendor we are purchasing the product from. But, since they are new (only a couple of years into use) to SQL server and we are getting to be the first Beta [oh Joy
] of their new product, I want to make sure we have the best plan in place. If Litespeed isn't going to be fast enough
, what other suggestions do you have to make the size and time of backups reasonable?
Kindest Regards,Donald Mayer
Oswego Health -
Differential and file-group backups will probably be an essential part of your backup strategy. Read about them and experiment with them in a test environment. You will still need to do periodic full backups, but by using either or both of these approaches, you can make the interval between full backups longer and thus hopefully schedule them for time periods of low activity etc...
By working with the vendor to determine the frequency of of changes within the various tables of the database and from there devise the specific physical layout of your database.
Absent a comprehensive understanding of the system, specific recommendations are not worth much. Like Will says, be prepared to spend a lot of time with research and I would include testing in a test environment.
If the hospital is really expecting a 10TB database, they are going to have to open up the wallet pretty wide if they want their data to be protected. When you get that large, the stakes get very high unless you can afford to be down for hours (or days) for a restore, and/or suffer the losss of data...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Will -
You have my sympathy, the restoration of a server from scratch to pre-crash status is often a complex, time consuming and frustrating experience. Ideally you should be able to restore the complete operating system, applications, settings & data directly from your backup solution but...
My personal thoughts:
As DBA's many of us depend on network or server administrators to provide our working environment which can make the restoration process even more difficult if the DBA(s) and sysadmin types aren't in synch or are unaware of changes made by the other group. One of the simplest things I can think of to ease the pain is a Server Log that lists each and every change to the server. The Server Log should be paper based (3 ring binder?) and include changes to both the operating system, any application that runs on the server and also any "special" settings that the server may require. I also find it helpful to include CD sleeves for application installation disks, printouts of emailed license keys, etc. If there are 100 ways to physically back up the server (or to reinstall it from scratch) there have got to be a million little things that can make the process more difficult/time consuming than it needs to be. Forgotten settings (/3GB anyone?), lost license keys, missing media, etc. only make the whole process even more painful. Your Sysadmin is likely to get a little testy when you call him back to the office after he/she spent all night reinstalling everything on "your" SQL Server only to be told that you need SP3a, NOT SP4 of SQL Server (uninstall, reinstall & service pack again I believe) and oh yeah, SQL Mail won't work with that version of Outlook and we need to change the password for Service/User X on all the other servers because nobody can remember what it was...
Another key to any backup strategy is to periodically verify your ability to restore from your backups. I recently shared a beer with a DBA who ran into this: the DBA's dumped the data to a specific location on the LAN which was then swept to tape, and sent off site (dumps were then deleted to save space). Worked great until they had a crash and needed to restore from the previous weekends full backup - turns out that the tapes were encrypted and the individual who had set up the backup routine had left the company more than 6 months before under less than ideal circumstances... not sure how that one ended up but I'm sure it wasn't pretty.
While thinking about this I a ran across: http://www.taobackup.com/. Both funny and informative, well worth the read.
Joe
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply