zagandesign - Fotolia

Get started Bring yourself up to speed with our introductory content.

Seven reasons to upgrade to SQL Server 2012

Support for early versions of SQL Server has ended. But rather than upgrade to SQL Server 2014, companies are turning to SQL Server 2012. Here is why.

Mainstream and extended support for SQL Server 2000 is over. Mainstream support for SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2 has ended and extended support for these versions will expire soon. Microsoft recommends that instead of buying the extended support or continuing unsupported, you upgrade to a current version of SQL Server in order to continue to have support from Microsoft under its Support Lifecycle policy. The current upgrade options are SQL Server 2012 and SQL Server 2014.

Although SQL Server 2014, the latest version of SQL Server, introduces several new features, many organizations prefer to upgrade to SQL Server 2012 first. SQL Server 2012 is widely used and is one of the most stable versions of SQL Server since the release of SQL Server 2005. Here are the seven SQL Server 2012 features that have convinced some companies to choose it for their upgrade.

Contained databases

Before the introduction of SQL Server 2012, when you moved or restored databases from one instance of a SQL Server database engine to another, any server logins associated with database users were not copied to the target instance automatically. This left you with orphaned users and a database that would not work immediately after migration. In order to get the database to function, you had to create and map server logins associated with the database users in the target instance. Obviously, this made the database less portable because database security depends on the instance of the SQL Server database engine where the database is installed.

SQL Server 2012 addressed this problem with the introduction of contained database authentication, which allows users to authenticate their database without having a login on the instance of the SQL Server database engine where the database is installed. A contained database can be moved easily between instances of SQL Server. It stores all the settings and metadata required to define the database and has no configuration dependencies on the instance of SQL Server where the database is installed. A contained database is also isolated from other databases hosted on the same SQL Server instance. It supports two types of users: SQL users with passwords and Windows Principal without Login. Both types of users do not need to login in the master database. These features make a SQL Server 2012 database much more portable than previous versions.

SQL Server auditing enhancements

Server and database audit specification objects found in SQL Server 2008 and SQL Server 2008 R2 are the most useful features of SQL Server. They help an organization meet various regulatory compliance requirements. The problem with these auditing features is that they were only in the enterprise edition.

Fortunately, server-level auditing is supported by all versions of SQL Server 2012. An important enhancement to auditing is that it's now resilient to failure when there are problems such as a failed network connection. SQL Server will keep trying to write audit records until any problem is resolved by buffering the records. SQL Server 2012 auditing also supports user-defined groups, meaning you can write audited events to the audit log by using the sp_audit_write (Transact-SQL) procedure. SQL Server 2012 also supports the ability to filter audit events and include new audited groups to monitor contained database users.

Default schema for Windows Groups

Before SQL Server 2012 was introduced, you could not specify the default schema for Windows Groups. As a result, when a user with access through Windows group membership created database objects such as a table or view inside a database, SQL Server automatically created a separate user (mapped to the Active Directory account), and a schema with the same name in the database. Because of this security manageability issue, databases included hundreds of users and schemas, which is a managerial nightmare for database administrators. Hence, the SQL Server community requested a fix for this security manageability issue via the Microsoft Connect site.

SQL Server 2012 addresses this security issue by allowing you to assign default schema for Windows Groups, which helps organizations simplify database schema administration.

User-defined server roles

SQL Server 2012 introduces user-defined server roles, which are similar to fixed server roles. The only difference is that they are created by a SQL Server administrator. SQL administrators can create, delete or manage these user-defined server roles using T-SQL or SQL Server Management Studio. The main advantage of user-defined roles is that they authorize user access based on job responsibilities.


SQL Server 2012 introduces FileTables, which let you store files and directories in a special table that builds on top of SQL Server FILESTREAM technology. A FileTable has a fixed schema, with each row representing a file or a directory. The main advantage of FileTable is that it supports Win32 APIs for file or directory management. This means you can access file and directory hierarchy through a Windows Share and database storage is transparent to a Win32 application. Files can be bulk loaded, updated and managed in T-SQL like any other column. SQL Server 2012 also supports backup and restore jobs for this feature. To learn more about SQL Server 2012 FileTable, see FileTables (SQL Server).

Nonclustered columnstore indexes

With the release of SQL Server 2012, Microsoft introduced a new type of nonclustered index called columnstore. Unlike traditional B-Tree indexes, where data is stored and grouped in a row-based fashion, columnstore indexes group and store data for each column in a separate set of disk pages. A columnstore index does not physically store columns in a sorted order. Instead, columnstore indexing is based on VertiPaq compression technology that allows large amounts of data to be compressed in-memory. This highly compressed in-memory store significantly improves query execution time by improving the buffer pool usage. It also reduces the total disk I/O and CPU usage because only the column-based data pages needed to solve the query are fetched from disk and moved in-memory.

Columnstore indexes are one of the most significant performance and scalability enhancements in SQL Server 2012. They speed up processing time for common data warehousing queries -- particularly in situations where very large quantities of data have to be aggregated and accessed quickly. According to Microsoft, under certain conditions the xVelocity columnstore index provides queries with speed improvements of four, 10 or even 100 times. For more information about columnstore indexes, refer to SQL Server Books Online.

AlwaysOn Availability Groups

SQL Server 2012 introduced AlwaysOn Availability Groups, which enhances and combines database mirroring and log shipping capabilities. It is a high availability and disaster recovery solution for SQL Server databases that provides an enterprise-level alternative to database mirroring. An availability group supports a primary database and from one to four sets of corresponding secondary databases. These secondary databases can be made available for read-only access or some backup operations. For more information about this feature, refer to Overview of AlwaysOn Availability Groups (SQL Server).

Next Steps

SQL Server 2014: Just a case of keeping up with the Joneses?

An overview of SQL Server 2014

Troubleshooting memory usage issues in SQL Server

Dig Deeper on SQL Server Migration Strategies and Planning