olly - Fotolia
Upgrading a business-critical SQL Server instance is not an easy task; it requires rigorous planning. Inadequate planning increases the chance of encountering difficulties that can derail or delay your SQL Server 2014 upgrade. As you plan an upgrade to SQL Server 2014, here are points to consider that can help you avoid potential upgrade problems.
Decide on your upgrade strategy
Consider an in-place or side-by-side migration for your SQL Server upgrade path. I prefer side-by-side migration because it is less risky, even if it is more difficult. An in-place migration offers no simple rollback once you upgrade. The key benefit of an in-place migration is that you don't need to worry about SQL Server security and other configurations because they will remain in sync before and after the upgrade. However, in-place migration requires a good backup of all system and user databases before upgrading. If SQL Server is running in a virtualized environment, you can simply perform a VM snapshot, which you can use as a rollback option for a failed upgrade.
In a side-by-side upgrade, the new version of SQL Server installs along with the old version as a separate instance on the same or a different server. This is the same as a new installation followed by a database migration from your existing SQL Server instance. For example, if you use this approach to upgrade an existing SQL Server 2008 instance to SQL Server 2014, you must install SQL Server 2014, either along with the SQL Server 2008 instance as a separate instance or on a different server. The advantage of this approach is that you can perform like-to-like testing of your database functionality. Furthermore, your rollback procedure is simpler than during an in-place upgrade because the original server is still intact. The drawback of this approach is that you have to follow the database migration exercise after the new instance is commissioned, and manually change the SQL Server instance's configuration and security. But a side-by-side upgrade is ideal when you are upgrading to new hardware, or when you are consolidating SQL Server instances.
Understand compatibility and hardware requirements
Before committing to a SQL Server 2014 upgrade, you should understand compatibility and hardware platform requirements. SQL Server 2014 installation has prerequisites for the operating system and the hardware platform. These requirements vary slightly between SQL Server 2014 editions. For example, SQL Server 2014 operating system requirements are specific to each edition. You must install both the operating system and specified service pack (SP) version to meet the SQL Server installation prerequisites. Stated SP requirements are minimum requirements. The SQL Server 2014 Setup program checks if your computer meets operating system and hardware requirements before it allows you to install or upgrade. Be sure to review the hardware and software requirements for the desired version. For example, if you are upgrading SQL Server 2005 to SQL Server 2014, make sure your hardware can run SQL Server 2014.
Next, you should analyze the SQL Server environment you plan to upgrade to ensure that you can upgrade this version to your desired version and edition of SQL Server 2014. For more information about supported upgrade paths from earlier versions of SQL Server to SQL Server 2014, and the supported edition upgrades for SQL Server 2014, refer to the SQL Server Books Online article "Supported Version and Edition Upgrades."
The Upgrade Advisor can help
Regardless of the path you choose for upgrading SQL Server, you should ensure that your database applications work after upgrading. The SQL Server Upgrade Advisor is a tool that can help you prepare for SQL Server upgrades. The Upgrade Advisor can help identify key issues that might affect your upgrade, such as deprecated or discontinued features, breaking changes and behavior changes. You can find the Upgrade Advisor installation file (sqlua.msi) in the redist folder of the SQL Server 2014 installation media, or as part of the SQL 2014 Feature Pack download.
Installing the Upgrade Advisor
To install the Upgrade Advisor from the redist folder of the SQL Server 2014 product media, run the SQLUA.msi file. If you have downloaded from the SQL Server 2014 feature pack, double-click the self-extracting executable file and you will be prompted to accept its license agreement and to supply registration information before installation.
When you run Upgrade Advisor, the welcome screen appears. From the welcome screen, you can view Upgrade Advisor documentation, launch the Upgrade Advisor wizard and Upgrade Advisor report viewer.
To run the Upgrade Advisor:
- Click Start, point to All Programs, point to Microsoft SQL Server 2014, and then click SQL Server 2014 Upgrade Advisor.
- From the SQL Server 2014 welcome screen, click "Launch Upgrade Advisor Analysis Wizard" and click Next.
- Check the services you want to analyze for upgrade and click Next. You should choose only those services installed on your computer. If you choose a service that is not installed, the Upgrade Advisor detects that the service is not installed and reports an error condition.
- Any conditions with a value of "Before" in the When to fix column should be corrected before you attempt to upgrade SQL Server.
- Enter the instance name. To check a named instance, type the instance name or select it from the dropdown list.
- Select your authentication type: Windows authentication or SQL Server authentication. If you choose Windows authentication, the currently logged in user is used for authentication.
- Respond to the additional prompts presented based on the services you selected. By default, the SQL Server Upgrade Advisor checks all databases. You can also choose to analyze trace and SQL batch files to help you detect application compatibility problems. However, you need to ensure that the trace or batch file you provide contains all the commands issued by your applications. When you choose to check SSIS packages for upgrade, you must choose to analyze packages on the server or stored in package files. If you want to analyze both, you have to run Upgrade Advisor twice.
- Review and confirm your settings.
- Click Run to start the analysis.
- A progress dialog box informs you of the progress while Upgrade Advisor is running. The same dialog box displays the final results, listing any warnings or error conditions that might interfere with the upgrade.
- Click Launch Report to view detailed information about the results.
For more information about Upgrade Advisor, refer to Microsoft's official resources.
The procedure for upgrading SQL Server is similar to a SQL Server 2014 installation. You can install and upgrade services as part of the same installation. For example, you can upgrade an existing instance of the database engine, and also install Analysis Services, Integration Services and Reporting Services. For more information, see the Microsoft SQL Server Upgrade How-to Topics page.
After your upgrade, monitor application performance and review Windows and SQL Server error logs to validate that no issues occur. Moreover, you should rebuild all user databases indexes and run sp_updatestats to run update statistics against all internal and external tables.
In addition to these tasks, execute DBCC UPDATEUSAGE against all user databases; this will correct page and row counts in the catalog view.
Learn more about the most talked-about features of SQL Server 2014
Check out these tips for managing SQL Server memory usage troubleshooting
See how SQL Server Availability Groups get an upgrade with SQL Server 2014
Learn how to get more out of OLTP with SQL Server 2014 in-memory