Four points to upgrade SQL Server 2014 Note _mssql

Source: Internet
Author: User
Tags microsoft sql server pack rollback advantage

Upgrades are not easy, but there are some features worth taking time to understand. The following article describes some tips for avoiding escalation issues.

Upgrading a critical business instance of SQL Server is not easy; it requires thoughtful planning. Unplanned meetings increase the likelihood of experiencing escalation problems, which can affect or delay the upgrade of SQL Server 2014. When planning your SQL Server 2014 upgrade, there are a few caveats to help you avoid escalation problems.

Note: Four points for upgrading SQL Server 2014!

Choose an upgrade policy

Consider an internal or parallel migration strategy in SQL Server upgrades. I prefer a parallel migration strategy because it is less risky, even more difficult. Internal migration is not easy to roll back after an upgrade. The main advantage of internal migration is that there is no need to worry about SQL Server security and other configurations because they are synchronized before and after the upgrade. However, internal migration requires a backup of all system and user databases in the upgrade. If SQL Server is running in a virtualized environment, you can perform a virtual machine snapshot directly and then use it as the Rollback method after the upgrade failed.

In a parallel upgrade, the new version of SQL Server is installed with 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 instance of SQL Server after a new database is installed. For example, if you use this method to upgrade an existing instance of SQL Server 2008 to SQL Server2014, you must install SQL Server 2014, and SQL Server 2008 instances are on different instances or different servers. The advantage of this approach is that you can perform similar tests on database functions. Also, the rollback process is simpler than an internal upgrade because the original server remains unchanged. The disadvantage of this approach is that you must perform the same database migration process once the new instance is generated, and manually modify the configuration and security of the SQL Server instance. However, if you are upgrading to new hardware or merging multiple instances of SQL Server, it is more appropriate to adopt a parallel upgrade strategy.

Understanding compatibility and hardware requirements

Before you upgrade SQL Server 2014, be sure to understand compatibility and hardware platform requirements first. SQL Server 2014 has some installation requirements for operating systems and hardware platforms. Different versions of SQL Server version 2014 have different requirements. For example, each SQL Server 2014 version has different operating system requirements. You must install the operating system and the specified version of the service Pack (SP) to meet SQL Server installation requirements. The specified SP requirements refer to the minimum requirements. SQL Server 2014 Setup checks to see if your computer meets the operating system and hardware requirements before allowing installation or upgrades. Be sure to check for specific versions of hardware and software requirements. For example, if you are preparing to upgrade from SQL Server 2005 to SQL Server2014, you must ensure that your hardware supports running SQL Server 2014.

You should then analyze the SQL Server environment that you plan to upgrade to ensure that your environment supports upgrading the current version to the expected version of SQL Server 2014. For upgrade support path information for upgrading to SQL Server 2014 from an older server version, as well as an upgraded version supported by SQL Server 2014, see the Server Books Online article: "Upgraded versions supported." ”

Using Upgrade Advisor

Regardless of which SQL Server upgrade you choose, you should ensure that your application works correctly after the upgrade. The SQL Server Upgrade Advisor tool can help you complete SQL Server upgrade preparation. UpgradeAdvisor can help identify key issues that may affect upgrades, such as deprecated or discontinued features, disruptive modifications, and behavioral modifications. The Upgrade Advisor installation file (Sqlua.msi) is located in the Redist folder of the SQL Server 2014 installation, or it can be found in the SQL 2014Feature pack download package.

Install upgrade Advisor

You can install upgrade Advisor by running the Sqlua.msi file for the SQL Server 2014 Product Media redist folder. If you have already downloaded the SQL Server 2014 Feature Pack, you can double-click the self-extracting executable file, and then it prompts you to accept the licensing agreement, prompting you to enter the registration information after installation.

When running Upgrade Advisor, a welcome interface is first displayed. On the Welcome screen, you can view the Upgrade Advisor documentation, launch the Upgrade Advisor Wizard, and the UpgradeAdvisor report browser.

Perform the following steps to start and run UpgradeAdvisor:

1. On the Start menu, locate All Programs-Microsoft SQL Server 2014, and click SQL Server 2014 Upgrade Advisor.

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

3. Review the upgrade services that you want to analyze, and then click Next. You should only select the services that are already installed on your computer. If you select a service that is not installed, Upgrade Advisor detects that the service is not installed and then reports as an error status.

4. In the report, the State in which the word "before" (pre-upgrade) is in the in-fix (when fixed) column must be corrected before you can continue to upgrade SQL Server.

5. Enter the instance name. If you enter an instance name or select an instance from the Drop-down list, you can select an existing instance.

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

7. Handle the relevant tips for the selected specific services. 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 trace file and the batch file contain all the commands used by the application. If you choose to detect SSIS packages when you upgrade, select the Analysis server or the package that is stored in the package file. If you want to analyze both services, you must run the upgrade Advisor two times.

8. Check and confirm settings.

9. Click Run to start the profiling process.

10. At this time UpgradeAdvisor will run, and there is a progress dialog window showing the progress of the analysis. The final dialog window displays the final analysis, which lists all the warnings or error states that may affect the upgrade.

For more information on Upgrade Advisor, please refer to Microsoft's official information.

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

After the upgrade, to monitor application performance and check windows and SQL Server error logs, verify that the problem does not occur. Also, you should rebuild all user database indexes, run sp_updatestats scripts, and perform update statistics on all internal and external tables.

In addition to these tasks, DBCC UPDATEUSAGE are executed against all user databases, and it fixes page calculations and row counts in catalog views.

That's the four key points to upgrading SQL Server 2014.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.