Now that Microsoft SQL Server 2012 SP1 has been available for a while, you may be considering an upgrade to SQL Server 2012. This may be a good move for a lot of organizations, since there are many new security and availability features in SQL 2012. However, it is important to do a thorough check of your existing SQL Server installation, to be certain that the upgrade will not cause any problems in your databases or third-party applications. This is not a complete list of all potential issues one may encounter while performing an upgrade, as every installation is unique, rather it is the first pieces of information a DBA will need during the project planning phase so he/she can decide if an upgrade is feasible.

Here are some items to check:

  1. Consider the licensing costs for SQL Server 2012, as Microsoft’s licensing price has shifted from the number of processers to the number of cores.
  2. Verify that third party applications are supported on SQL 2012.
  3. Check all current code to ensure that it is forward-compatible. The table below shows the deprecated features in SQL Server 2012. If any existing stored procedures use these features, these procedures/processes will have to be re-engineered to maintain the current functionality.
Category Deprecated feature Replacement Feature name Feature ID
Backup and restore BACKUP { DATABASE | LOG } WITH PASSWORD None BACKUP DATABASE or LOG WITH PASSWORD 104
Backup and restore BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD None BACKUP DATABASE or LOG WITH MEDIAPASSWORD

103

Backup and Restore RESTORE { DATABASE | LOG } … WITH DBO_ONLY RESTORE { DATABASE | LOG } … … WITH RESTRICTED_USER RESTORE DATABASE or LOG WITH DBO_ONLY

101

Backup and restore RESTORE { DATABASE | LOG } WITH PASSWORD None RESTORE DATABASE or LOG WITH PASSWORD

106

Backup and restore RESTORE { DATABASE | LOG } WITH MEDIAPASSWORD None RESTORE DATABASE or LOG WITH MEDIAPASSWORD

105

Compatibility levels 80 compatibility level and upgrade from version 80. Compatibility levels are only available for the last two versions Database compatibility level 80

107

Metadata DATABASEPROPERTY DATABASEPROPERTYEX DATABASEPROPERTY

38

Memory Management Address Windowing Extensions (AWE) support that allows 32-bit operating systems to use over 4 GB of physical memory. Use a 64-bit operating system to use over 4 GB of physical memory. None None
Database objects WITH APPEND clause on triggers Re-create the whole trigger. CREATE TRIGGER WITH APPEND

171

Instance options Default setting of disallow results from triggers option = 0 Default setting of disallow results from triggers option = 1 sp_configure ‘disallow results from triggers’

172

Database options sp_dboption ALTER DATABASE sp_dboption

77

Query hints FASTFIRSTROW hint OPTION (FAST n). FASTFIRSTROW

177

Remote servers sp_addremotelogin Replace remote servers by using linked servers. sp_addremotelogin

70

sp_addserver sp_addserver

69

sp_dropremotelogin sp_dropremotelogin

71

sp_helpremotelogin sp_helpremotelogin

72

sp_remoteoption sp_remoteoption

73

@@remserver Replace remote servers by using linked servers. None None
SET REMOTE_PROC_TRANSACTIONS Replace remote servers by using linked servers. SET REMOTE_PROC_TRANSACTIONS

110

Security sp_dropalias Replace aliases with a combination of user accounts and database roles. Use sp_dropalias to remove aliases in upgraded databases. sp_dropalias

68

SET options SET DISABLE_DEF_CNST_CHK None. Option has no effect. SET DISABLE_DEF_CNST_CHK

188

SET options SET ROWCOUNT for INSERT, UPDATE, and DELETE statements TOP keyword SET ROWCOUNT

109

Transact-SQL syntax Use of *= and =* Use ANSI join syntax. For more information, see FROM (Transact-SQL). Non-ANSI *= or =* outer join operators

178

Transact-SQL syntax COMPUTE / COMPUTE BY Use ROLLUP COMPUTE [BY]

180

System tables sys.database_principal_aliases Use roles instead of aliases. database_principal_aliases

150

Tools sqlmaint Utility Use the SQL Server maintenance plan feature None None
Transact-SQL The old style syntax for RAISERROR (Format: RAISERROR integer string) syntax is deprecated. Rewrite the statement using the current RAISERROR syntax. Old style RAISEERROR

164

Programmability SQL Server Database Management Objects (SQL-DMO) SQL Server Management Objects (SMO) None
Like this post? Share it!