Here are Database Engine features that are no longer available in SQL Server 2012.

Category

Discontinued feature

Replacement

Backup and Restore

BACKUP { DATABASE | LOG } WITH PASSWORD and BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD are discontinued. RESTORE { DATABASE | LOG } WITH [MEDIA]PASSWORD continues to be deprecated.

None

Backup and Restore

RESTORE { DATABASE | LOG } … WITH DBO_ONLY

RESTORE { DATABASE | LOG } … … WITH RESTRICTED_USER

Compatibility level

80 compatibility levels

Databases must be set to at least compatibility level 90.

Configuration Options

sp_configure ‘user instance timeout’ and ‘user instances enabled’

Use the Local Database feature. For more information, see SqlLocalDB Utility

Connection protocols

Support for the VIA protocol is discontinued.

Use TCP instead.

Database objects

WITH APPEND clause on triggers

Re-create the whole trigger.

Database options

sp_dboption

ALTER DATABASE

Mail

SQL Mail

Use Database Mail. For more information, see Database Mail and Use Database Mail Instead of SQL Mail.

Memory Management

32-bit Address Windowing Extensions (AWE) and 32-bit Hot Add memory support.

Use a 64-bit operating system.

Metadata

DATABASEPROPERTY

DATABASEPROPERTYEX

Programmability

SQL Server Distributed Management Objects (SQL-DMO)

SQL Server Management Objects (SMO)

Query hints

FASTFIRSTROW hint

OPTION (FAST n).

Remote servers

The ability for users to create new remote servers by using sp_addserver is discontinued. sp_addserver with the ‘local’ option remains available. Remote servers preserved during upgrade or created by replication can be used.

Replace remote servers by using linked servers.

Security

sp_dropalias

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

Security

The version parameter of PWDCOMPARE representing a value from a login earlier than SQL Server 2000 is discontinued.

None

Service Broker programmability in SMO

The Microsoft.SqlServer.Management.Smo.Broker.BrokerPriority class no longer implements theMicrosoft.SqlServer.Management.Smo.IObjectPermission interface.

SET options

SET DISABLE_DEF_CNST_CHK

None.

System tables

sys.database_principal_aliases

Use roles instead of aliases.

Transact-SQL

RAISERROR in the format RAISERROR integer ‘string’ is discontinued.

Rewrite the statement using the current RAISERROR(…) syntax.

Transact-SQL syntax

COMPUTE / COMPUTE BY

Use ROLLUP

Transact-SQL syntax

Use of *= and =*

Use ANSI join syntax. For more information, see FROM (Transact-SQL).

XEvents

databases_data_file_size_changed, databases_log_file_size_changed

eventdatabases_log_file_used_size_changed

locks_lock_timeouts_greater_than_0

locks_lock_timeouts

Replaced by database_file_size_change event, database_file_size_change

database_file_size_change event

lock_timeout_greater_than_0

lock_timeout

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s