Issue: 3

Issue: 3, Other Database Engine Upgrade Issues

The following upgrade issues cannot be detected by the current release of Upgrade Advisor. Review the issues listed below to evaluate their potential impact to your systems.

Multiple Database Engine Deprecated Features

In SQL Server 2008, the following Transact-SQL statements or options will be deprecated:

  • NO_LOG and TRUNCATE_ONLY options of BACKUP LOG
  • BACKUP TRANSACTION
  • RESTORE TRANSACTION
  • DUMP
  • LOAD
  • DBCC CONCURRENCYVIOLATION
  • sp_addalias
  • sp_addgroup
  • sp_changegroup
  • sp_dropgroup
  • sp_helpgroup
  • syssegments

60, 65 and 70 Compatibility Levels

After upgrading to SQL Server 2008, compatibility levels of 60, 65 and 70 will not be supported. Check if any of your scripts have sp_dbcmptlevel <db_name>, <60|70|80>, or any of your databases has a compatibility level of 60, 65, or 70. Compatibility level for these databases will be automatically changed to 80 during the upgrade process.

New Data Types Introduced in SQL Server 2008

In SQL Server 2008, the following will be reserved system types. Rename the existing conflicting user defined types either before or after upgrade to SQL Server 2008.

  • Geography
  • Geometry
  • Datetime2
  • HierarchyID

Target Table of the OUTPUT INTO Clause Cannot Have Any Defined Triggers

Starting with SQL Server 2008, OUPUT INTO a target table when the table has any enabled triggers will not be supported.

Compile Time Error for UDFs When the Target of an OUTPUT INTO Clause is a Table

User-defined functions (UDF) cannot be used to perform actions that modify the database state. For example, a UDF cannot perform any DDL (CREATE/ALTER/DROP) or DML (INSERT/UPDATE/DELETE) actions on any objects, except for table variables.

MERGE is a Reserved Keyword in SQL Server 2008

In SQL Server 2008, we introduced a new Transact-SQL statement called MERGE. As a result, MERGE is now a fully-reserved keyword. Your SQL Server 2008 applications can no longer have objects (table, column, etc.) called MERGE

Rename CDC Schema

SQL Server 2008 introduces a new schema name: CDC. This schema name cannot be in used if Change Data Capture is enabled for the database.

You must drop the CDC schema before you enable Change Data Capture for the database. This step can be done before or after the upgrade. To drop the schema, use the following steps:

Transfer the objects from CDC schema to a new schema name using ALTER SCHEMA.
Verify permissions for the objects in the new schema.
Make necessary modifications to the application.
Drop the CDC schema using DROP SCHEMA.
XML Showplan has New Attributes

The XML Showplan has added new attributes like the IndexKind attribute that describes the type of index that the plan contains, and non-clustered, spatial, and XML index.

The newly updated XML Schema for the XML Showplan (http://schemas.microsoft.com/sqlserver/2004/07/showplan) in SQL Server 2008 allows these optional attributes and still can validate SQL Server 2005 XML Showplans. However, it will not detect that these attributes are not allowed on SQL Server 2005 XML Showplans. If that is needed, use a SQL Server 2005 version of the XML Schema.

No corrective action is needed for this issue.

Changing Validation Semantics of xs:anyType from Strict to Lax

SQL Server 2008 adds support for lax validation to the XML Schema support in XML Schema collections and validates the wildcard type xs:anyType lax as required by the W3C XML Schema recommendation. SQL Server 2005 validated xs:anyType with strict validation semantics.

As a result, documents that may have been rejected as invalid in SQL Server 2005 may now be accepted by the same XML Schema in SQL Server 2008.

Since the SQL Server 2008 behavior is the recommendation-compliant behavior, no corrective action should be needed. If strict validation semantics are required, change the XML Schema to use a wildcard content model (xs:any) with process content setting strict.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s