SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
New Year, New Server: Ten Steps to Get Your SQL Servers Fighting Fit for 2008
Web Monkey About the Author

With security clearance and well over a decade of experience with SQL Server, Web Monkey is the resident DBA at Norb Technologies and a real database guru.

A regular contributor to SQL Server Club, his background includes software development, hardware, a smattering of essential networking knowledge and an unhealthy interest in web security. He has an obsession with automating repetitive tasks, but has a tendency to forget where he left his last open bag of fruit and nut...

Typepad Profile RSS Feed Web Monkey on Twitter
Send to a Friend Printer Friendly Version
Whether you work for a small or large company, the Christmas and New Year holiday period is a great time to sort out all the unwanted stuff on your servers. It's also a good way to impress your boss by freeing up server capacity and keeping those SQL Servers up and running for another year.

So when you're in that "quiet" period between Christmas and New Year, not wanting to move because you're still full of food, but wanting to do something useful but not too taxing done, why not run through a few of our top tips to make your life easier in 2008...

  1. Upgrade RAM, Hard Disks, CPU and Network Hardware

    The Christmas holiday is an ideal time to perform hardware upgrades. Unless you like being in at the weekends, hardware changes can easily be done over Christmas and you have plenty of pressure-free time to sort out any problems you might hit before you bring your business to a halt.

  2. Clear out Old Backups

    Why not clear out all of the ad-hoc backups that have accumulated over the year, which you no longer need? Take a look in your usual backups folders, the root of each drive on the server, and do a quick search for *.bak. Tools such as Treesize from Jam Software are useful for spotting where those missing gigabytes disappeared to.

  3. Resize your Databases

    Any databases that grow on a regular basis are using up valuable CPU and user time. So long as you have sufficient disk space, why not re-size them to something bigger and keep your server performance at top speed?

    Don't forget to free up 15-20% of the space on your disk drive to make the defrag more efficient - too little space and it may not even run.

  4. Defragment Your Disks

    All of those databases that were wrongly sized last year can quickly get fragmented across your disk. Use the festive period to run that outstanding disk defrag job, as it consumes lots of I/Os and can take hours, or even days to run if you've got a large, heavily fragmented disk.

    Make sure you defrag your disks before your indexes, otherwise you could degrade performance. If you defrag your disks first and your indexes second, you'll have a super fit SQL Server as a result!

  5. Defragment Your Database Indexes

    Once you've defragmented your disk drives, the next step is to defrag your database indexes. This too can take an age to run, but it's pretty quick to set up and kick off (particularly if you've already have a copy of our very own Visual Defrag 2008 which will automate the whole lot for you).

    Defragging both disk and database indexes can do wonders for your SQL Server's performance and save you a stack of cash on server upgrades and Christmas is a great time to do it.

    Just think how delighted your hung-over colleagues will be in the New Year when their applications respond as quickly as they did when they were new.

  6. Apply Service Packs and Image Servers

    Applying a SQL Server service pack can be a fraught business, which is why we recommend you don't do it as soon as they come out, unless you have a very pressing need to do so.

    Many companies will image their critical live database servers before applying a patch, just in case. This is a time consuming process which often involves booting the server up in DOS mode, so the festive holiday is a great time-slot to get this sort of patch out of the way.

  7. Consolidate Servers and Databases

    This needs careful planning and unless you have loads of time on your hands, may be too late to plan for this year.

    However, backing up (or detaching, copying and re-attaching) database files can be a very time intensive process, especially when network traffic is high and it's nearly impossible to do when users need your system, so schedule this sort of move for the holiday period.

  8. Security Housekeeping - Purge those Old Users

    Get a script together or use your favorite audit tool to check for users who are no longer at your company. If they've still got accounts on your servers, now's a better time than ever to remove them from your system.

    Better still, figure out a process with your HR department so they let you know when people leave and you can remove them immediately.

  9. Removal of Test Objects

    Test instances, databases, tables, stored procs, unused scheduled jobs and other unused objects all create bulk.

    create database test
    create table dummy (int x, dummy varchar(10))
    create proc sp_test1 as ....

    We've all done it, we all still do it. This is especially common in development databases. Use your time to scour for these and remove them from your system. Always back up anything you're going to remove first (just in case…) and look at putting unused databases into offline mode in SQL Server 2005.

  10. Replace Faulty or Intermittently-Operating Equipment

    Faulty router? Got a dodgy connection on that network port that's always generating packet errors and timeouts? Talk to your network guys and get it swapped out as soon as possible. Network equipment often impacts more people than you can imagine, so replacing it over an extended holiday period is often the best time to do it.

  11. Ok, since Christmas is coming and I’m feeling generous, so here are a couple extra freebie tips for you…

  12. Aircon Cleaning, Power Breaking and UPS Testing

    Getting your server room air conditioning checked over, cleaned and maintained is essential. Don't do it and it will come back to bite you. Likewise your power breakers and UPSes need a check-up from time to time too.

    You'll probably have to involve the building maintenance guys for some of these tasks, but the holiday period is a good time to get this sort of thing out of the way ready for the New Year.

  13. Replace Old Servers

    Need to replace that ageing old Pentium 4 which won't take more than 2GB of RAM? Then festive system replacement is the answer. Plus you get a chance to spend lots of valuable internet time drooling over, sorry, sensibly considering which hardware upgrade will be best suited for your site.

Remember, a little simple housekeeping can save you the time and expense of upgrading your servers when you could be doing something much more interesting - like writing an article for SQL Server Club!
Send to a Friend Printer Friendly Version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form