Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite

I'm more of a network/windows admin and I've been tasked with overseeing a SQL server upgrade project. I need to meet with the DBAs and discuss their needs / wants regarding the upgrade. I don't want to go in totally blind, so I thought I would ask you guys first. We are moving from SQL Server 2008 to SQL Server 2008 R2 and likely moving to Windows Server 2008 R2 where possible. As a DBA, what would be your concerns with such an upgrade? Anything you'd like to see happen at the same time?

share|improve this question
2  
Where does the server/application sit on a criticality scale of 1-5? OLTP or OLAP? Busy/stressed server? Rationale for upgrade? – Mark Storey-Smith Oct 19 '11 at 15:49
There are around 150 DBs on the server in question. They really run the gamut from 1-5 with regards to criticality. Similarly, the server holds both transactional and analytical DBs. I checked out the resource usage today and it appears to be minimal. The upgrade rational is that the hardware (5 y/o, out of warranty) and SQL platform (2005, 2008) are nearing EOL. The servers currently comprise 2 clusters with one cluster serving legacy 32 bit apps. We will also contemplate a move from Fibrechannel to iSCSI storage. I'm hoping to virtualize the 32 bit cluster, as new apps are on the horizon. – drinkofwater Oct 20 '11 at 3:07

2 Answers

up vote 5 down vote accepted

Without any information on the nature of the system (see my comment on the question) or why you're upgrading, it's difficult to offer any specific and/or concise advice.

As a starting point, there are plenty of excellent checklists for building a new server, Brent Ozar and Jonathan Kehayias are two good examples. From the many recommendations in those guides, there are a couple of items worth highlighting. These are those which I encounter mis-configured most often.

  • Storage - Check partition alignment, although this is less of an issue for W2K8+ as manual partition alignment is not usually required (non-standard/exotic SAN aside). Format with 64k block size, not 4kb default, for data drives. Run a basic set of SQLIO tests so you a) have a yardstick to compare server X with server Y and b) you have a level of confidence in this servers capability.

  • Antivirus - Ensure MDF, NDF and LDF files are excluded from your antivirus scanner. These can cause chaos on a busy system, fix it before it does.

  • Model database - Any changes made to the Model database are reflected in every user database you create. Set the model size and growth rates to sensible values for your environment/system. In lieu of any other guide, SIMPLE recovery (in case someone forgets to configure log backups), 2048MB datafile with 1024MB growth, 1024MB logfile with 512MB growth (as per JK's checklist).

For a server/system/application which is considered business critical and is subject to a tight SLA, plan for the worse. With these you need to be as close to 100% sure as possible that the upgrade doesn't turn out to be a downgrade as far as the business or users are concerned. For that level of confidence you're going to have to test, test and test some more.

In any large scale system there will be a query or two which required a hack/workaround/hint to optimise. Some are deliberate and based on the best advice at the time, others will be emergency fixes that got forgotten about. These are the queries that will unexpectedly change behaviour on upgrade as a result of tweaks and improvements to the query optimiser. Only one way to spot them, run them on your new kit.

My preferred approach is workload capture and replay with the RML tools. There is an excellent guide from SQL CAT to using RML for exactly this purpose, Precision Performance for Microsoft SQL Server using RML Utilities 9.0.

share|improve this answer
Thank you, much appreciated. I will look into these tips tommorow. Those blogs that you've referenced look to be great resources. – drinkofwater Oct 20 '11 at 3:11
up vote 5 down vote

SQL 2008 to SQL 2008 R2 is not a big switch, but if you are going from Windows 2003 to Windows 2008 you will need to address the host based firewall. Other thigns to be concerned about are Agent jobs, permissions in master/msdb/model, migrating logins from one server to another, whether to do a backup restore to new server or in place upgrade, compatibility mode, etc.

share|improve this answer
Good point about the firewall. We will be upgrading from S2K3 to S2K8 R2. – drinkofwater Oct 20 '11 at 3:12
+1 but I would suggest an in-place upgrade would be out of the question? – Jack Douglas♦ Oct 20 '11 at 5:37
Good point on the firewall. – StanleyJohns Oct 20 '11 at 13:55
@Jack Yes, if changing the OS is going to be done then I wouldn't recommend trying an in place upgrade. Clean start for SQL and Windows would be better. – Jason Cumberland Oct 24 '11 at 17:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.