Top five common mistakes in SQL Server database upgrades

SQL Server database upgrades require a significant amount of planning and testing by DBAs. Most of the time, there may not be any problems with the upgrade process, but some potential threats must not be overlooked. In this article, SQL Server expert Roman Rehak will introduce you to the issues that DBAs should be aware of during database upgrades or migrations.

Insufficient upgrade test. The most common cause of problems with SQL Server database upgrades is inadequate testing. DBAs need to test not only the application of each connected database, but also any program that executes database code, such as SSIS packages, database Tools, secondary stored procedures, and replication. If you use the SQL Server 2000 version, then the test becomes more important.

When you upgraded to SQL Server 2005, many of the syntax changed, including T-SQL-related features. For example, when using SQL Server NOLOCK statement, you need to add with. There are many ways to test for error syntax, and one of the most straightforward ways to do this is to recompile the T-SQL code with the ALTER command for all stored procedures. But this method cannot find syntax errors in dynamic SQL, so there is no shortcut, you need to execute all the database code.

No serious reading of the upgrade white paper. In addition to the basic SQL Server online manual, the DBA can also refer to the upgrade White paper provided by Microsoft, which is for all versions of SQL Server, as a SQL Server database upgrade. Although it looks boring, and most of the content you are not necessarily able to use, but read from cover to cover, the focus of the content, I think it is a good benefit to the DBA and no harm.

Before you upgrade, you need to figure out which features are discarded in the new version, and if you find such a feature, the DBA should revise and adjust the code in time. If you don't have time to read the white paper before you upgrade, be sure to remember that some features will not work in the next release.

The subjective view is that existing hardware can run a new version of the database. Typically, Microsoft will provide some technical documentation prior to releasing each new version of the SQL Server database to introduce new features to the new version of the database. Many DBAs make the low-level mistake of thinking that existing hardware can run a new database. In most cases, such problems may not occur, but not all servers can meet the hardware requirements of the new version.

For example, SQL Server 2005 is far more system-configurable than SQL Server 2000, and, in practical terms, a DBA indicates that the server is running significantly slower after upgrading SQL Server. Microsoft officials have acknowledged that, in rare cases, database performance has not risen since the installation of SQL Server 2005. The only way to find the truth is to perform benchmarking, and you can use the same server to install different versions of SQL Server, and then compare the performance to the application.

In addition, we can use virtualization technology to compare, in some cases, the database software upgrade also means that the hardware server also needs to upgrade, especially when the server is used for a long time, the DBA should fully consider the hardware problem.

The compatibility version is not changed. When making a database upgrade, the DBA needs to set the compatibility level, which allows you to run the new version of the database without changing the syntax. When you revert to a new version from an earlier version of the database, SQL Server retains the compatibility version, which means that it allows you to run the invalid syntax. This feature is designed to enable DBAs to have more time to modify the syntax.

If you set up the database in a previous version of compatibility mode, you may mistakenly assume that the upgrade is complete because there are no problems during the test. But if you later change to a new version of compatibility mode, T-SQL will have problems. Keep in mind, therefore, that you need to modify the compatibility mode of all data (including system databases), and you can change it back when you need to run a previous version of SQL Server.

No rollback plan was developed. Regardless of the size of your database upgrade, be sure to be able to roll back operations on SQL Server when a failure is encountered. "Database backup prior to upgrade" is known to all DBAs, but it's easy to say, especially if you use replication, not every DBA recovers all the databases and redo all SQL Servers.

For a 24/7-run enterprise, it is unacceptable to have a long downtime that causes unnecessary user churn. In these environments, database upgrades require minimal downtime, and DBAs cannot accept all sorts of errors in the upgrade. To minimize the risk, we can now run the new SQL Server database on the new machine and the previous machine will run. If the new test shows significant problems, you can also use the previous settings to continue working until all the problems are resolved.