SQL Server Upgrade issues - How to evaluate potential issues?
During the end of March 2009 I have presented a session "Upgrading To SQL Server 2005 & 2008: Notes & Best Practices" in SQLBits IV conference at Manchester. The session covered a brief tour on SQL Server Upgrade options, methodology, tools and planning, technical and non-technical considerations along with common issues, myths and mistakes. Also the coverage towards the exhaustive list of all upgrade issues with an end-to-end coverage or in-depth drilldown of all SQL Server 2005 & 2008 features. Equally the feedback was very high & good in terms of number of people commented on the specific aspects of this presentation, a good topic and coverage of tools & issues resolution. On specific to the feedback again last month I have had further questions about how to evaluate potential issues during the upgrade and here is blog post that I want to give a brief tour on the subject.
As I have referred on the presentation slides the SQL Server 2008 Upgrade technical reference guide from Microsoft is a great help to get to know the subject very well. As per the reference say whether you choose an in-place upgrade or a side-by-side upgrade of a relational database, there are a range potential issues that you might face during the upgrade. The first and foremost option you can choose is to run the SQL Server 2008 Upgrade Advisor no matter the size of database is involved here and that will get you a detailed report that identifies many of these potential issues. The advantage that you can get running this tool is nothing but a preview of problems that might occur during the upgrade and few of them might crop up at the later stages, whereby it is impossible to ROLLBACK or get back to previous state of the system.
So when you have got the report in front of you most of the warnings & errors are reported due to the deprecated and discontinued functionality, changes that might prevent an upgrade, and changes in feature behavior that might require modifications as per the new features within SQL Server 2008. By default you can obtain information on Backward Compatibility in SQL Server 2008 Books Online (updated). A doubt that was raised by one user during the conference that what is backward compatibility & discontinued features, several features from earlier versions of the SQL Server Database Engine are not supported in SQL Server 2008, so you must use replacement features for these. Backward compatibility with earlier versions of SQL Server was a high priority in SQL Server 2008, so in most cases, applications will behave as in the past. A sample list of such information here:
|
Discontinued Feature/Functionality |
Replacement Feature/Corrective Action |
|
sp_configure with the ‘allow updates’ option. Used to directly update system tables, but direct updates to system tables are no longer supported. This option, although present, has no effect. |
Modify scripts that update system tables directly to use documented commands instead of direct updates. |
|
Network Protocols. The NWLink IPX/SPX, AppleTalk, Banyan Vines, and Multiprotocol network protocols are no longer supported. |
Use TCP/IP sockets, named pipes, VIA, or shared memory. |
|
Rebuildm.exe. Used for rebuilding system databases, this executable is obsolete. |
This executable is replaced by the REBUILDDATABASE option in setup.exe. Modify any scripts that use the rebuildm.exe executable. |
|
Sample Databases. The Northwind and Pubs sample databases were discontinued. |
These sample databases are replaced by the AdventureWorks and the AdventureWorksDW sample databases. However, you can download or move Northwind and Pubs from a SQL Server 2000 or SQL Server 2005 instance. |
|
Remote setup. Used to install SQL Server on a remote computer, this option is no longer available in the Setup program. |
Use a remote connection to run the SQL Server Setup program on the remote computer. |
For more ease and in-depth of information about discontinued functionality in SQL Server 2005 and SQL Server 2008, see the following topics in SQL Server 2008 Books Online: Discontinued Database Engine Functionality in SQL Server 2005 & Discontinued Database Engine Functionality in SQL Server 2008
Also I have seen that few settings within the SQL Server 2008 setup program will prevent from starting the upgrade process for the Database Engine. If one of the following issues is encountered, the upgrade process will stop, and the legacy system will remain in place:
|
Issue |
Corrective Action |
|
Username of sys in a database. SQL Server 2008 does not permit a username of sys in a database. |
Create a new user who has a different name, transfer ownership of all database objects to that new user, and drop user sys from the database. |
|
Duplicate login SIDs. SQL Server 2008 does not permit duplicate login SIDs for SQL Server authentication. |
Drop and re-create the duplicate SQL Server logins on the legacy system. |
|
Login names matching fixed server role names. SQL Server 2008 does not allow login names to match fixed server role names. |
Rename the logins on the legacy system. |
|
Database ID 32767. SQL Server 2008 does not permit a database ID of 32767. |
Detach and reattach the database and make sure that it gets a new database ID. |
|
Duplicate index names. SQL Server 2008 does not permit duplicate index names on a table. |
Rename the indexes so that all indexes are unique within each table. |
Also further list of table within the Upgrade technical reference guide is:
|
Behavior Change |
Corrective Action |
|
Log file disk space. Additional space is required by transaction log files. |
Make sure that the log files for each user database are set to autogrow and have sufficient additional disk space or increase the size of each log file manually. Monitor the effect of workloads on transaction log space after upgrade. |
|
tempdb disk space. Additional space is required by tempdb data and log files because of its use by new features and enhancements to existing features. |
Make sure that the data and log files for tempdb are set to autogrow and have sufficient additional disk space or increase the size of each file manually. Monitor the effect of workloads on data and log space after upgrade. |
|
Extended stored procedures. Extended stored procedures that were previously registered without a full path for the DLL name might fail because the old BINN directory is not added to the new path during upgrade. |
Drop the extended stored procedure by using sp_dropextendedproc, and then register the extended stored procedure with the full path by using sp_addextendedproc. |
|
Dbo-owned objects. System objects are now owned by sys instead of dbo. |
Modify scripts that contain statements that query system tables or have search criteria specifying dbo. |
So after having such a list of issues that might prevent a smooth upgrade you must get to know about SQL Server 2008 features that offers many improvements over SQL Server 2000 and SQL Server 2005, in addition to many great new features. The first step in taking advantage of these improvements is upgrading your existing databases to SQL Server 2008. As I explained the 2 main choices for upgrading SQL Server 2000 and SQL Server 2005 databases to SQL Server 2008: in-place or side-by-side. And if you select the side-by-side method, you have additional choices to make, including whether to use backup/restore, detach/attach, or the Copy Database Wizard—or another alternative. Each of these options has its pros and cons, so you have to make sure that you understand your organization’s current configuration and needs. Then you have to prepare thoroughly and test extensively to make sure that an upgrade is successful and ready for production.
Due to the nature of the topic on upgrade issues is very large, it is not possible to cover in few steps in 1 blog post, so revisit this section again for specific points that I will blog again.