SQL Server 2005 Database move and rationalisation - Recommended maintenance processes

We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct.

I inherited this database and it came to me in a bit of a mess truth be told - on top of that I've basically learnt to be a SQL Server developer on this server (no educational background in computing) so I've made plenty of my own mistakes on it. We're underfunded as a department (public sector), and so lacking expertise, pulling ourselves up by our bootstrings. The database started out as a back room thing but has become very important to our organisation and given this the performance is frankly embarrassing.

So we have issues such as

• history of Autoshrink usage

• History of undersized storage - never really enough space on the server for the db to grow so I'd guess the files are probably quite internally fragmented.

• There are multiple collation types (for no good reason),

• Inappropriate text datatypes all over the place (lots of Nvarchar, nchar, ntext when we have no need of ascii characters, and text where varchar would be more appropriate)

The new server is a 64bit virtual server, storage on RAID.

My plan after installing and upgrading is to:

• Remove a vast amount of tables we've identified (with confidence) as redundant (about 1/3 of db size!)

• Normalise collations to 1 type (after some analysis and testing we're certain this won't cause us problems - we have no requirement for case sensitivity)

• Normalise text data types to varchar/char

• Set database file sizes to the expected usage for the coming 2 years plus 50%

Is there anything else I should be considering after/before these processes - and do any of decisions here look off mark? For example the file size issue - is this something I need to worry about on RAID?

Shrinking and Storage

Enable "Instant File Initialization"

Disable autoshrink

Size your storage properly (15% free per drive)

RAID what? RAID level matters and SQL Server accesses the files differently. Log file access is generally sequential and Data is generally random.

tempdb

Separate data and log files (see RAID, below) - get a good tempdb strategy. We have tempdb logs on a log drive and our tempdb files (1/4-1/2 the number of cores) on their own drive. While you're at it, make sure that tempdb has an appropriate number of data files, that their initial sizing is exactly the same, and that they have exactly the same growth factor. While you're at it, read up on Trace Flag 1118

Schema Changes

If you're going to be making these changes, make sure you have a good, durable backup. What happens in 9 months when someone actually needed those tables?

Can your application actually withstand the changes from NVARCHAR, NCHAR, and NTEXT? While you're at it, NTEXT is deprecated so moving to TEXT would not be good. Check out VARBINARY or VARCHAR(MAX) instead.

You can change the collation all you want, but it won't affect any of the existing data.

Compatibility

Change the db compatibility levels to 100 if you can.

Tuning Option

Check out some of these trace flags none of them may be appropriate. Some of them might be, "it depends." Also, read about trace flag 4199.

Tooling

I'd also recommend coming in to this install with a good tool set. Create a DBA database for this toolkit. I'd start with sp_WhoIsActive by Adam Machanic, sp_Blitz by Brent Ozar. I'd also look for Kendra Little's work with scheduling and storing the results from sp_WhoIsActive. Finally, I'd start up a way of capturing Waits and Queues so that you have a good history of your server's profile.

Look into getting a monitoring solution.

Finally (and maybe firstly), check out Glenn Berry's excellent series on provisioning a new SQL Server Instance.

Part 1, Part 2, and Part 3

StackExchange Database Administrators Q#36594, answer score: 5