Return to Answer

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.

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.

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.

The query optimiser is inevitably tweaked and improved between both releases and service packs. In any large scale system there will inevitably 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 inevitably the queries that will be subject to an unexpected change of behaviour on upgrade. 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.

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.

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 guidelines in those guides, there are a couple of sections that are 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.

The query optimiser is inevitably tweaked and improved between both releases and service packs. In any large scale system there will inevitably 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 inevitably the queries that will be subject to an unexpected change of behaviour on upgrade. 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.

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.

The query optimiser is inevitably tweaked and improved between both releases and service packs. In any large scale system there will inevitably 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 inevitably the queries that will be subject to an unexpected change of behaviour on upgrade. 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.