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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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.
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.