Note the following four key points for upgrading SQL Server 2014: sql2014

Note the following four key points for upgrading SQL Server 2014: sql2014

Upgrading is not easy, but some features are worth learning. The following sections describe some tips to avoid upgrading.

Upgrading a key business SQL Server instance is not easy; it requires a comprehensive plan. The non-plenary part of the plan increases the possibility of upgrading problems, thus affecting or delaying the upgrade of SQL Server 2014. When planning an SQL Server 2014 upgrade, you must pay attention to some precautions to avoid upgrade problems.

Note: four key points for upgrading SQL Server 2014!

Select upgrade policy

Consider using internal or parallel migration policies for SQL Server upgrades. I prefer a parallel migration policy because it is less risky, even if it is more difficult. Internal migration is not easy to roll back after the upgrade. The main advantage of internal migration is that you do not need to worry about SQL Server Security and other configurations because they are synchronized before and after the upgrade. However, internal migration requires that all systems and user databases be backed up during upgrade. If SQL Server runs in a virtualized environment, you can directly execute a VM snapshot and use it as the rollback method after the upgrade fails.

In parallel upgrade, the new version of SQL Server exists at the same time as the old version, and is located on different instances of the same or different servers. This is similar to migrating a database from an existing SQL Server instance after a new database is installed. For example, if you use this method to upgrade an existing SQL Server 2008 instance to SQL Server 2014, you must install SQL Server, SQL Server 2008 instances are located on different instances or different servers. The advantage of this method is that you can perform similar tests on database functions. In addition, the rollback process is simpler than the internal upgrade because the original server remains unchanged. The disadvantage of this method is that you must execute the same database migration process after the new instance is generated, and manually modify the configuration and security of the SQL Server instance. However, to upgrade to new hardware or merge Multiple SQL Server instances, it is more suitable to adopt the parallel upgrade policy.

Understand compatibility and hardware requirements

Before upgrading SQL Server 2014, you must first understand the compatibility and hardware platform requirements. SQL Server 2014 has some requirements for operating system and hardware platform installation. Different Versions of SQL Server 2014 have different requirements. For example, each SQLServer 2014 version has different operating system requirements. You must install the operating system and the specified version of the service package (SP) to meet the SQL Server installation requirements. The specified SP requirement refers to the minimum requirement. The SQL Server 2014 installer checks whether the computer meets the operating system and hardware requirements before the installation or upgrade is allowed. Check the hardware and software requirements of a specific version. For example, if you want to upgrade SQL Server 2005 to SQL Server 2014, make sure that the hardware supports running SQL Server.

Next, analyze the SQL Server environment to be upgraded to ensure that the environment supports upgrading the current version to the expected version of SQL Server 2014. For information on the supported paths for upgrading from an earlier version of SQL Server to SQL Server 2014, and the upgraded versions supported by SQL Server 2014, see the SQLServer Books Online article: "supported upgrade versions ."

Use Upgrade Advisor

No matter which SQL Server upgrade method you choose, ensure that the application works properly after the upgrade. The SQL Server Upgrade Advisor tool can help prepare for SQL Server Upgrade. UpgradeAdvisor helps identify key issues that may affect the upgrade, such as obsolete or stopped features, destructive modifications, and Behavior modifications. The Upgrade Advisor Installation File (sqlua. msi) is located in the redist folder of SQL Server 2014 Installation Introduction. It can also be found in the SQL 2014 Feature Pack download package.

Install Upgrade Advisor

Run the SQLUA. msi file in the redist folder of the SQL Server 2014 product medium to install Upgrade Advisor. If you have downloaded the SQLServer 2014 feature package, you can double-click the self-extracting executable file, and then it will prompt whether to accept the authorization protocol. After installation, it will prompt you to enter the registration information.

When running Upgrade Advisor, a welcome page is displayed first. On the welcome page, you can view the Upgrade Advisor document and start the Upgrade Advisor wizard and the UpgradeAdvisor report browser.

Perform the following steps to start and run UpgradeAdvisor:

1. Find "All Programs"-"Microsoft SQL Server 2014" in the "Start" menu, and click SQL Server 2014 Upgrade Advisor.

2. On the SQL Server 2014 welcome page, click "Launch Upgrade Advisor Analysis Wizard" and then click Next ).

3. Check the upgrade service you want to analyze and click Next ). You should select only the services already installed on the computer. If you select an uninstalled service, Upgrade Advisor detects that the service is not installed and reports it as an error.

4. In the report, the status of the When to fix column with the word "Before" (Before Upgrade) must be corrected Before the SQL Server can be upgraded.

5. Enter the Instance name. Enter an Instance name or select an instance from the drop-down list to select an existing instance.

6. Select the authentication type: Windows authentication or SQL Server Authentication. If you select Windows authentication, the current logon user is used as the authentication account.

7. Handle prompts related to the selected service. By default, SQL Server Upgrade Advisor checks all databases. In addition, you can analyze trace files and SQL batch files to help detect application compatibility issues. However, you must ensure that the trail file and batch file contain all the commands used by the application. If you select SSIS package detection during upgrade, select the Analysis Server or the package stored in the package file. To analyze the two services, you must run the Upgrade Advisor twice.

8. check and confirm the settings.

9. Click Run to start the analysis process.

10. At this time, UpgradeAdvisor will run and a progress dialog window will display the analysis progress. The final analysis result is displayed in the dialog box, which lists all the alarms or error statuses that may affect the upgrade.

For more information about Upgrade Advisor, refer to official Microsoft documents.

The SQL Server upgrade process is similar to the SQL Server 2014 installation process. You can install and upgrade services contained in the same installation file. For example, you can upgrade an existing instance in the database engine or install Analysis Services, IntegrationServices, and Reporting Services. For more information, see the Microsoft SQL Server UpgradeHow-to Topics webpage.

After the upgrade, you must monitor the application performance and check the Windows and SQL Server error logs to confirm that there are no problems. In addition, you should re-create all user database indexes and run the sp_updatestats script to execute update statistics for all internal and external tables.

In addition to these tasks, you also need to execute dbcc updateusage for all user databases; it will correct the page calculation and row count in the directory view.

The above are the four key points for upgrading SQL Server 2014. Be sure to pay attention to them.