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),
textdatatypes all over the place (lots of
ntextwhen we have no need of ascii characters, and
varcharwould 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)
textdata types to
- 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?