March 15, 2008 at 6:22 am
We have a SQL 2000 SP4 Server.
I'm not a SQL expert (I'm a network admin) and leave the day to day admin to a couple of guys who have done SQL courses and know more about SQL than I do.
I need to know enough to be confident that things are being backed up as required hence this post.
As we don't have many databases and non are of any significance over any other, I stuck my nose in some time back and asked if we actually needed several maintenance plans or whether we could simply make do with two, one for "System Databases" and one for "All User Databases".
User Databases is set to:
Optimizations - Reorganize data and index pages, change free space per page to 10%, runs weekdays at 1am
Integrity - Check DB integrity, include indexes, weekdays at 00am
Complete backup - backup and verify, sub-dir for each db, remove files over 2 weeks, weekdays 1am
T-Logs - backup and verify, sub-dir for each db, remove files older than 1 week, daily every hour between 6am-8pm
Reporting - Write report, email operator, delete reports after 4 weeks.
System Databases is set to:
Optimizations - Reorganize data and index pages, change free space per page to 10%, runs weekdays at 1am
Integrity - Check DB integrity, include indexes, weekdays at 00am
Complete backup - backup and verify, sub-dir for each db, remove files over 4 weeks, every Monday at 2.30am
T-Logs - don't do anything
Reporting - Write report, email operator, delete reports after 4 weeks.
The first thing I want to change is the backup location so that the db's and t-logs are backed up directly to a UNC on a remote server (the SQL Server/Agent run under a domain account with rights to the UNC)
Secondly I'm thinking that for both plans firstly the database retention time should be the same, possibly 4 weeks for each.
If we do that it's clearly a lot of disk space for a nightly backup of every single database, could we change to a weekly full database backup with the transaction log backups (which we're doing each hour during core working hours) being sufficient so that we could get back to any given hour within the last week, and then increase the t-log retention from 1 week to 2 or perhaps even 4 weeks (I guess this comes down to the business recovery requirements)?
Essentially I'd like to simplify the backup process as much as possible as (to my non-SQL mind) it minimises the chances of something failing and it being missed amongst lots of other things.
I should add that the server to which I want to send the backups is in a remote location (fast LAN link) and is backed up to tape daily with standard GFS rotation.
Any input/feedback much appreciated.
March 15, 2008 at 7:20 am
Rather don't shrink your dataabses, especially straight after an index rebuild. Index rebuilds put the index pages in order, shrink will move them completely out of order. You may as well not reorg the indexes.
Some details (including very good links) here[/url]
From what I recall, SQL 2000 doesn't like backing up to network shares, and I don't think it's recommended. Perhaps backup local and copy the files afterwards. Backing up over the network will probably slow down the backup and, if the network glitches, you could have a corrupt backup file.
If you're going to do a full backup weekly, then I would suggest doing a diff backup each night. Otherwise, if the DB fails on the friday, to recover you'll have to restore the full and all of the tran log backups since then. With hourly tran log backups, that's around 120 backup files to restore. It could take a while.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2008 at 7:45 am
Thanks for the reply and information.
The article on shrinking makes a lot of sense. Presumably whenever SQL deletes anything the space is treated as white space and is re-used anyway.
I did find regarding backing up to UNC which suggests it can be done.
I take the point about local backup and then copy, it's just that if possible/practical I'd sooner not have gigs of the same data in three locations (we're running SQL in VMware so we'd have the backups on the virtual machine, which is imaged each night, as well as the copied logs and db's if you follow), I'd sooner just perform the backups directly to the remote location (it's on a 2gbps LAN link). Perhaps that one's just a case of try it and see?
I can't see any option to do differential backups in the Maintenance plans, only if I look at each individual database as if I were doing a manual backup?
March 17, 2008 at 7:40 am
Differentials are not an option in the maintenance plan but you can script it into a job. Daily index rebuilds - is that really necessary for your environment? Backups to a UNC path have always worked for me. You do want to verify the integrity of you backups so I would probably restore a backup on occasion to verify that everything is good. Retention of your backups is a tough call. I keep a week on disk but everything is also swept to tape and archived offsite. It all depends on your business needs. Seems to me that you're heading in the right direction. How do you like SQL on VMWare? Any issues that you'd care to share? We are planning to implement it here and I've read pros and cons about it so I'm just a little nervous about it.
-- You can't be late until you show up.
March 18, 2008 at 6:52 am
tosscrosby (3/17/2008)
Differentials are not an option in the maintenance plan but you can script it into a job. Daily index rebuilds - is that really necessary for your environment? Backups to a UNC path have always worked for me. You do want to verify the integrity of you backups so I would probably restore a backup on occasion to verify that everything is good. Retention of your backups is a tough call. I keep a week on disk but everything is also swept to tape and archived offsite. It all depends on your business needs. Seems to me that you're heading in the right direction. How do you like SQL on VMWare? Any issues that you'd care to share? We are planning to implement it here and I've read pros and cons about it so I'm just a little nervous about it.
To be entirely honest I think it's just a case that our environment is small (in SQL terms) so for how long/how much impact doing things daily takes it seems to do no harm not to do it daily.
Keeping in mind that our main tape backups run after 10.30pm, what we've done (and are monitoring) is the following:
Set a full backup of all user databases to occur Mon-Fri at 22.00 (this currently only takes a couple of minutes) directly to the UNC of the remote file server.
Set a backup of all user database transaction logs to occur hourly 24/7 (this literally takes a few seconds) directly to the UNC of the remote file server.
Set both to trim at 1 weeks worth of backups.
This means that, assuming everything runs, each night when the file server is backed up up to tape it will be backing up the databases from that evening, and the logs until the time the backup to tape starts.
On a Friday the entire weeks backups/logs will still be on the file server so will be backed up to tape, which will be kept on a GFS "weekly for a month, monthly for a year" schedule, it's a business call that we simply don't need to be able to pick a specific day within the last 12 months so don't see the need to keep 4 weeks worth of backups - though if the sizes are small enough I may tweak this simply to give us that ability.
As for VMware, as I said we're only small, and we're new to ESX, but so far so good, everything just seems much much easier than dealing with physical boxes, especially with regard to image level backups of servers and being able to tweak/tune resources rather than throwing a huge lump of hardware at an application that doesn't need it (but also being able to tune this when it does grow).
March 18, 2008 at 7:05 am
Well your "sanity-check" appears right on for you business needs. If your small and can do the maintenance jobs daily, cool. I personally think daily rebuilds are overkill in a small environment, especially if your transaction counts are low, but again, if you have the window to run the job, no harm done. Your backup startegy is very similar to one I (and probably many others) use and have had no issues, so far.
We're very new to VMWare and ESX ourselves. We definitely sized memory too low and are planning to increase it dramatically (current 8 Gb going to 64 Gb). Not sure what they were thinking on the original order but I'm sure the vendor priced it low in order to make the sale. I've only been on the job for six weeks so this occurred before my time. We're slowly moving development to it to try and benchmark things and will probably move some of our production to it later this year. If you run into any "gotchas", I'd appreciate a heads-up. I, in turn, will do the same. Lastly, pretty good setup for a "Non-DBA'. Sounds like you'll fit right in.
-- You can't be late until you show up.
March 18, 2008 at 7:20 am
To echo GilaMonster's point - it's usally not a great idea to back up to a mapped drive/network drive. Even on a fast network - it's going to be a rather dramatic drop in performance, especially since as part of a backup job, you're going to have network transfers equivalent to 4-6 passes through the data. Any net blips and you're also likely to corrupt the backup.
Much better would be to backup locally, and move the jobs to the share (through a "traditional" move/copy operation).
Otherwise - it looks like a decent place to start. Rebuilding/reorging your indexes nightly is a bit overkill, and will create a lot of transaction log activity - perhaps switch that to weekly?
----------------------------------------------------------------------------------
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?
March 18, 2008 at 7:25 am
Thanks both.
On the backup to UNC issue, I guess I'll sound like a smart-*** but we have verify on, and there's little goes on at that time of night (not that would affect a 2gig ethernet link) so I think we'll just try it for a bit and see how it goes.
I wasn't aware that the re-indexing would have an impact on the log size though I guess indexing is transactions so thinking about it, it makes sense, though I don't know enough about SQL to actually know exactly what a re-index is doing i.e. if the log generation is a price worth paying for the benefits?
Also should I be ticking the "repair minor errors" box?
Oh and to add we're also backing up the system DBs on a Friday just after the User DB full backup should have finished.
March 18, 2008 at 7:35 am
As long as the system is small enough, and you are willing to spend the time at night - going UNC is certainly your call. I would still do test restores every once in a while. Verifonly is good, but nothing beats actually double-checking (IMO).
The thing about reindexing: rebuilding all indexes entails rebuilding the clustered indexes which control the physical data order in a table. Essentially that forces a "rewrite" of the entire table, which then gets logged.
The overkill comment comes from the fact that indexes continue to function very well with some amount of fragmentation. Just like the OS file system, some amount of fragmentation is assumed and won't negatively impact performance for some time. That being said, again - if you have the extra time, and you don't mind having bigger trans. log backups to back up, that again is your call. If your maintenance window at night comes to shrink in the future, then perhaps you can consider whether doing all of those thing nightly is worth it or not.
----------------------------------------------------------------------------------
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?
March 18, 2008 at 7:46 am
Do not use the "Attempt to repair any minor problems" option. This will put your database in single user mode and can cause unforseen problems.
March 18, 2008 at 8:02 am
Any thoughts on the order of maintenance plan operations - optimization, integrity check and backups? I know that in creating the maintenance plan, they come in the order, as typed above, but, if you choose to schedule them, another order appears? Should I use the scheduling order or the order of appearance in creating the plan? Thanks.
Chris
March 18, 2008 at 8:10 am
I personally like to have all my maintenance done prior to my backups. If I come in the next day and we have any issue, if I restore to my last full backup, all maintenance has already processed. A lot of what you do depends on your maintenance window. One server I have, I can only do index rebuilds monthly and integrity checks weekly. My backups of course run daily. But my maintenance jobs all run prior to any full backup. Again, makes recovery easier and up time quicker, for me.
-- You can't be late until you show up.
March 18, 2008 at 9:36 am
Christopher G.S. Johnson (3/18/2008)
Any thoughts on the order of maintenance plan operations - optimization, integrity check and backups?
That should be fine. Just make sure shrink isn't included in the 'optimisations'
One thing to note about SQL 2000 and the verify option on the backups. It's not a guarentee. A backup can verify fine and still not restore. SQL 2005's a lot better in this regard, but still, the only way to ensure a backup is restorable is to restore it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2008 at 9:57 am
Great info. Thanks for the feedback, all.
Chris
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply