4.5. Best practice considerations: installing and upgrading SQL Server

Despite the ease with which SQL Server can be installed and upgraded, adequate preparation is essential in ensuring a stable and secure platform for later use:

• Review the best practices from the previous chapters to ensure hardware components are designed and configured appropriately.

• Prior to installation, create nonadministrator domain accounts for each instance/service combination, and ensure the accounts don't have any password expiration policies in place.

• Grant the SQL Server service account the Perform Volume Maintenance Tasks right; and for 32-bit AWE and 64-bit systems, also grant the Lock Pages in Memory right.

• Prior to installation of each SQL Server instance, prepare directories on separate physical disk volumes for the following database components:

  • Data files

  • Log files

  • Backup files

  • tempdb data and log

• Prior to installation, use the resources available in the Planning tab of the installation wizard. Included here (among others) are hardware and software requirements, security documentation, and online release notes.

• Only install the minimum set of SQL Server features required. This will increase security by reducing the attack surface area, and ensure unnecessary services aren't consuming system resources.

• Don't install SQL Server on a primary or secondary domain controller.

• Ensure consistency in the selection of collations across SQL Server instances, and unless compatibility is required with earlier SQL Server versions, select a Windows collation.

• Unless it's being used for applications that can't work with Windows authentication, don't choose the Mixed Mode option. If you do, make sure you choose a strong SA password and enforce strong password policies for all SQL Server logins.

• If you need to change any of the SQL Server service accounts after the installation, make changes using the SQL Server Configuration Manager tool. Changing in this manner ensures the appropriate permissions are granted to the new account.

• When installing SQL Server using the GUI wizard, take care to click and review each of the tabs before clicking Next. For example, the Database Engine Configuration screen lets you select the Authentication mode. Once you do this, clicking Next will skip the Data Directories and FILESTREAM tabs, which will enforce the default values. One of the ramifications of this is that data and log files will be created in the same directory, unless you manually change them after the installation.

• For a smoother installation process, particularly when a non-DBA is responsible for installation, use a tailored checklist. Alternatively (or as well as), copy and modify a ConfigurationFile.ini created from a successful installation and use the command-line method with the /Configurationfile option.

• Before upgrading, download and read the SQL Server 2008 Upgrade Technical Reference Guide. It contains important information on best practices and specific advice for various upgrade scenarios.

• Always run through a trial upgrade in a test environment using a recent copy of the production database (if possible). Doing so offers many benefits as well as circumvents potential problems. A trial upgrade will enable you to gather accurate timings for the actual production upgrade, determine the effect of the new compatibility level on application behavior, allow performance testing against a known baseline to determine the performance impact of the upgrade, and finally, develop a checklist to ensure the real upgrade runs as smoothly as possible.

• Use the Upgrade Advisor to analyze upgrade targets for issues that will prevent an upgrade or to gather a list of issues that will need to be addressed after the upgrade. If possible, feed SQL Server Profiler trace files into the Upgrade Advisor to examine any application code that may need to change before the upgrade.

• After the upgrade, attend to any issues identified by the Upgrade Advisor, including the following: setting the database compatibility level, updating statistics, checking configuration for features that need to be enabled, and if upgrading from SQL 2000, setting the Max Worker Threads option to 0 and running DBCC UPDATEUSAGE.

• The in-place upgrade method may be simple, but it exposes the possibility of having no rollback position if required. The various side-by-side upgrade methods offer more choices for rollbacks while also minimizing downtime.

• If using the in-place upgrade method, perform a full backup and DBCC check of each database prior to the upgrade.

• Using the Microsoft Assessment and Planning Toolkit Solution Accelerator tool is an effective means of discovering SQL Server instances and can be used as the starting point for consolidation and/or upgrade projects.

• Prior to any installation, upgrade, or service pack/hotfix install, always read the release notes for any late-breaking news and details on how certain components and features may be affected.

• Prepare for service packs. They're released for a good reason. Have both the time and environments ready for regression testing before applying in production, and consider any software vendor warranties before application.

• Only apply hotfixes and cumulative updates if there's a specific reason for doing so; otherwise, wait for the next service pack release.

• Always read the release notes that accompany service packs, hotfixes, and cumulative updates. Such notes often contain crucial information that may impact certain configurations.

Additional information on the best practices covered in this chapter can be found online at http://www.sqlCrunch.com/install.

In the next chapter, we'll discuss installing SQL Server on a failover cluster.