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 ofNvarchar
,nchar
,ntext
when we have no need of ascii characters, andtext
wherevarchar
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 tovarchar
/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?
Best Answer
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 havetempdb
logs on a log drive and ourtempdb
files (1/4-1/2 the number of cores) on their own drive. While you're at it, make sure thattempdb
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 1118Schema 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
, andNTEXT
? While you're at it,NTEXT
is deprecated so moving toTEXT
would not be good. Check outVARBINARY
orVARCHAR(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 withsp_WhoIsActive
by Adam Machanic,sp_Blitz
by Brent Ozar. I'd also look for Kendra Little's work with scheduling and storing the results fromsp_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