Sql-server – Concerns regarding changing virtual NIC on SQL Server 2008 R2 / Windows Server 2008 R2

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.

Pass-through is a feature of Windows authentication. It's not related to SQL Server at all, and it's "on" by default (I'm not sure if there's a way to turn it off, now that I think about it).

Which accounts you use depends on the environment, and how the permissions are configured on the backup share. I'm assuming in this scenario that the copy job will be a "pull" from production to QA (in other words, the backup share exists within the production domain).

If domain trusts are configured such that a principal on the QA domain can authenticate against a production resource, the backup share (folder) permissions can be directly modified to give read access to whichever QA domain account/group is being used (they don't have to match at all). Some environments allow this kind of configuration, while others don't. If you don't know the trust configuration between the two domains, ask your Windows system administrator.

If there are no trusts configured between the two domains, then you'll need to use pass-through authentication. This is set up by creating identical AD accounts (matching user name + password) on both domains. Assuming appropriate permissions are configured on the backup share (folder) for the production account, the Windows authentication system will automatically handle the rest of the process.

See these two TechNet articles for more information about domain trusts and pass-through authentication.