June 27, 2008 at 8:34 am
We receive our systems build from our corporate server support group.
Recently, we received two SQL 2005 servers, but upon setting up the databases, I see that although they have separate partitions for Data, Logs, and Backups, they are all still on the same physical drive.
I have a script that copies the backups to another server an hour after the nightly backups are complete. I am stuck with the configuration, but is there anything different that I should be doing as far as best practice goes?
Log shipping?
June 27, 2008 at 9:21 am
Maybe you could just write the backup directly to the other server?
Regards,
Phil
June 27, 2008 at 10:01 am
Chaotic,
Best practices suggest that at the very least the backups be on their own separate physical device. Partitioning is great, but it's all virtual.
If you lose the disk you lost the Data, Logs, and Backups in ONE :w00t: FELL :w00t: SWOOP!! :w00t:
On the upside, you have redundancy of the backups because you are moving/copying them via script once per day. However, this is not bullet proof if no one is checking to see if the backup job and moving job have completed successfully.
1) If this is a highly transactional database, run frequent Transaction Log Backups to a different physical location or shared directory on another Server every 15 to 30 minutes or so. If there is not much going on in this database, meaning not to many changes, run the Transaction Log Backup every hour. That way if you lose the disk and you have at least one nightly backup at the very least you can restore the transactions and recover to within 15 to 60 minutes depending on the option chosen.
2) If there is an opportunity, in the not to distant future, suggest that the architecture of the server be changed so that there is a physical separation of these devices.
3) For additional redundancy make sure that all the backups are being written to some removable media that is stored off-site. There is nothing worse than losing your whole server room in a natural disaster and not having anything to recover data. As a side not, suitable off-site storage locations do not include your basement, spare bedroom, or garage. (Yes, I have see this 😉 )
4) Last, but not least, make sure that all of your backup and restore procedures are well documented, in case you, as the DBA, are not around to implement them. All of the necessary information required to implement a Business Continuity Plan is included. Server Names, OS Versions, RDBMS versions and patch level, the off-site storage location, software vendors, hot site locations and purveyors (where applicable), contact information for the "recovery team", and contact information for management that can approve decisions.
These are some of the basic rules that I follow and suggest using. I wish you the best of luck!!
Regards, Irish
June 27, 2008 at 10:01 am
In an ideal world, you would have seperate physical drives, sure.
Is your database used 24/7? If not then backing up to the same physical drive won't cause any pain to users thankfully.
What is the need to copy the backups? Is it to provide a standby server? Even when I have used log shipping to create a standby box, I always take a full backup still.
If the backups are taking too long, it may also be worth doing differential backups.
June 27, 2008 at 1:24 pm
We had a consultant come in and help us establish best practices for managing our database environment. Don't laugh... here's the suggestion he gave us for disk architecture:
C:\ Drive - OS only (mirrored)
D:\ Drive - Applications, including SQL Server
E:\ Drive - Log files (RAID 5)
F:\ Drive - Data files (RAID 5)
G:\ Drive - tempdb database
H:\ Drive - Data files backup
I:\ Drive - Log files backup
Who doesn't have 12 drives to allocate to a single server? 🙂
Mike
June 27, 2008 at 1:44 pm
If there is not much going on in this database, meaning not to many changes, run the Transaction Log Backup every hour.
We have three databases that have enough traffic to merit going to Full recovery. I will set the logs up on an hourly schedule. We used to have an off-site storage company, but for now I will have to settle for another server. I will definitely create documentation for restores. I'm actually just a developer that "inherits" the SQL boxes from the shrugging admins. 😉
What is the need to copy the backups? Is it to provide a standby server?
I have been copying the backups to a Development server because I use those to refresh QA environment with Production data from time to time, and the whole "same physical disk" worries me.
June 27, 2008 at 1:47 pm
C:\ Drive - OS only (mirrored)
D:\ Drive - Applications, including SQL Server
E:\ Drive - Log files (RAID 5)
F:\ Drive - Data files (RAID 5)
G:\ Drive - tempdb database
H:\ Drive - Data files backup
I:\ Drive - Log files backup
Who doesn't have 12 drives to allocate to a single server? 🙂
Mike: That would be one serious batch of requirements to merit those specs.
June 27, 2008 at 1:54 pm
Mike Hunsaker (6/27/2008)
We had a consultant come in and help us establish best practices for managing our database environment. Don't laugh... here's the suggestion he gave us for disk architecture:C:\ Drive - OS only (mirrored)
D:\ Drive - Applications, including SQL Server
E:\ Drive - Log files (RAID 5)
F:\ Drive - Data files (RAID 5)
G:\ Drive - tempdb database
H:\ Drive - Data files backup
I:\ Drive - Log files backup
Who doesn't have 12 drives to allocate to a single server? 🙂
Mike
er........having H and I is redundant but otherwise thats a good layout if top notch i/o performance is a requirement. Could even go RAId 1 if its write intensive (except for H)
---------------------------------------------------------------------
June 27, 2008 at 1:56 pm
C:\ Drive - OS only (mirrored)
D:\ Drive - Applications, including SQL Server
E:\ Drive - Log files (RAID 5)
F:\ Drive - Data files (RAID 5)
G:\ Drive - tempdb database
H:\ Drive - Data files backup
I:\ Drive - Log files backup
And I bet administration wonders why you want to implement a SAN too, right?
If it were me and I did not have a SAN, I would use a RAID 10, but only for the data. Logs on a RAID 1, Temp DB can go where the applications are.
Yeah, that configuration is definitely overkill for a Dev system.
Regards, Irish
June 27, 2008 at 2:44 pm
of course in different driver.
you could image,your data file and backup file 's size increase everyday.
If they touch the limitation, what will happen?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply