Sunday, June 04, 2006

Some SQL2005 Upgrade Gotchas
Model Databases

  • The model database will be set to a database compatibility mode of 90. This may affect the behavior of scripts in or against this database.
  • The PAGE_VERIFY database option of the model database will be set to CHECKSUM.

Target Servers

  • If upgrading target servers from SQL Server 7.0, you must manually reenlist them with the upgraded master server.
  • You must upgrade all target servers (TSX) before you upgrade master servers (MSX).

Scripts and Stored Procedures

  • Database administrators must register each extended stored procedure using the full path for the DLL name. Extended stored procedures registered without the full path will not function after upgrading to SQL Server 2005.
  • You should review the use of trace flags in administration scripts to determine if the trace flag still exists or if the functionality of the trace flag has not changed in SQL Server 2005.

User Accounts

  • After upgrading from SQL Server 2000 to SQL Server 2005, all user proxy accounts that existed before upgrading are changed to the temporary global proxy account UpgradedProxyAccount. The UpgradedProxyAccount is only granted access to those subsystems that were explicitly used, and does not have access to all subsystems after upgrading.
  • Administrators must log into SQL Server using Windows Authentication in order to view maintenance plan tasks in SQL Server 2005.

Configuration and Memory

  • The max server memory option is a hard limit for the buffer pool size in SQL Server 2005. SQL Server 2005 will no longer allow the buffer pool to exceed this setting even if additional memory is available. Queries will fail with an "insufficient system memory" error if the max server memory value is reached.
  • Changes in the query cost modeling may affect the successful execution of queries in SQL Server 2005 if the sp_configure query governor cost limit option has been set in the upgraded installation. Review the value of this option and reset to a higher value or set to 0 to specify no time limit.
  • Direct system catalog updates are not supported in SQL Server 2005. Review the allow updates option of sp_configure to determine if direct updates are allowed before upgrading to SQL Server 2005. Scripts updating system tables must be modified to use documented commands instead of direct updates.
  • The open objects option of sp_configure has been deactivated in SQL Server 2005. This option is present but it will not function. Review modify scripts utilizing this option before upgrading to SQL Server 2005.
  • The set working set size option of sp_configure has been deactivated in SQL Server 2005. This option is present but it will not function. Modify scripts containing this option before upgrading to SQL Server 2005.

SQL Server Agent

  • The syntax for calling tokens in the SQL Server Agent job steps has been changed and must be modified after upgrading to SQL Server 2005.
  • SQL Server Agent 2005 uses a new format for error messages written to the job step log files; you must modify any custom or third-party applications that parse the new format.
  • Scripts using the xp_sqlagent_proxy_account extended stored procedure must be modified to remove references to this extended stored procedure after upgrading to SQL Server 2005.
  • The SQL Server Agent is now only available for members of the sysadmin, SQLAgentUserRole, or MaintenanceUserRole roles.
  • The SQL Server Agent service account no longer allows SQL Server Authentication.

No comments: