Some features not supported now in SQL 2005

Category

Discontinued feature

Replacement

Command prompt utilities

Isql

Sqlcmd

Configuration options

‘allow updates’

Option is present but direct updates to system tables are not supported.

Configuration options

‘open objects’

Option has been left in sp_configure to ensure backward compatibility with existing scripts

Configuration options

‘set working set size’

Option is present but it’s functionality has been deactivated.

Database creation

DISK INIT
DISK RESIZE

Leagacy behaviour from SQL Server 6.x

Database creation

FOR LOAD option of CREATE DATABASE

RESTORE operations can create a database

DBCC

DBCC DBREPAIR

Use DROP DATABASE to remove a damaged database

DBCC

DBCC NEWALLOC

DBCC CHECKALLOC

DBCC

DBCC PRINTABLE, DBCC UNPRINTABLE

None.

DBCC

DBCC ROWLOCK

Row-level locking is automatic

DBCC

DBCC TEXTALL
DBCC TEXTALLOC

DBCC CHECDB
DBCC CHECKTABLE

Network protocols

NWLink
IPX/SPX
AppleTalk
BanyanVines
Multiprotocol

TCP/IP sockets
Named pipes
VIA
Shared memory
only supported protocols now

Rebuild Master

Rebuildm.exe

Setup.exe

Sample Databases

Northwind
pubs

AdventureWorks
(Northwind, pubs available by download if necessary)

T-SQL

*=, =* outer join operators

Use JOIN syntax of the FROM clause

Virtual Tables

Syslocks

Sys.dm_tran_locks

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.