Why Would I Upgrade to SQL Server 2012?

by Michelle Gutzait on Mar 2, 2012 12:00:00 AM

The million-dollar question we get asked by clients is, why should we upgrade to the next version of SQL Server? The process of upgrading SQL Server is usually time consuming, costs money and requires availability of human resources. If you don’t see the immediate need… Why bother?! Before deciding on upgrading, you should be aware of the new features and make sure you will really benefit by the upgrade. From our experience at Pythian with dozens of clients and hundreds of environments, what could be worth upgrading to SQL Server 2012? Here are some of the top features related to the database engine (Part I):

High Availability

SQL Server Multi-Subnet Clustering

A great MS solution to the geographically dispersed clustering. For Disaster Recovery (DR) solutions, where you previously had to choose between other options than cluster such as Database Mirroring, Log Shipping, Replication etc., you can now implement a cluster where nodes reside in different geographical locations.

AlwaysOn

More High Availability can be achieved with this one feature that combines all the advantages of clustering, Database Mirroring and Log Shipping:

application connects to one logical name
a group of databases can failover as a unit
multiple mirrors/secondaries
the mirror could be read-only and therefore available for reporting
some nodes connecting asynchronously and thus have a remote replica of your databases without needlessly slowing down the primary

Indirect checkpoints

Database-specific checkpoint configuration. A per-database algorithm that provides a more accurate guarantee of database recovery time in the event of a crash or a failover than is provided by automatic checkpoints configured on the instance level.

Server Core Installation

Install SQL Server on Windows Server 2008 R2 Server Core SP1 which provides a minimal environment for running specific server roles. This helps reduce maintenance and management requirements and the attack surface for those server roles

Backup and restores

Point-in-time restore

Improved by the addition of a visual timeline that allows identifying a feasible point in time as a target time for a database restore operation.

Page Restore dialog box

Enables to check database pages for corruption and to restore selected corrupt pages from a database backup and subsequent log backups.

Partially Contained databases

When moving databases from one instance to another, some dependencies do no longer make sense. Some examples are: user-login, end-points, Replication, change data capture, and change tracking. This new configuration feature allows moving databases without those unnecessary dependencies.

Performance and Tuning

Database tuning advisor

There is a new option of using the query plan cache as a Database Engine Tuning Advisor (DTA) workload and the –n option to determine the number of T-SQL in the workload. No more need for manual capture of load or working with scripts!

Columnstore indexes

A new index type, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases.

Online index Create, Rebuild and Drop

Indexes containing varchar(max), nvarchar(max), and varbinary(max) columns can now be created, rebuilt, and dropped as an online operation

Increased number of Partitions in a Partitioned Table

Number of partitions can now go up to 15,000 rather that the 1000 allowed in SQL 2008.

Programmability enhancements

File tables

Built on the FILESTREAM technology, allowing to store files and documents in special tables in SQL Server, but access them from Windows applications as if they were stored in the file system, without making any changes to the Windows applications. This feature lets an application integrate its storage and data management components and provides integrated SQL Server services (including full-text search and semantic search) over unstructured data and metadata, along with easy policy management and administration.

Semantic search

Builds upon the existing full-text search feature in SQL Server, but enables new scenarios that extend beyond keyword searches. While full-text search lets you query the words in a document, semantic search lets you query the meaning of the document. Solutions that are now possible include automatic tag extraction, related content discovery, and hierarchical navigation across similar content. For example: query the document similarity index to identify resumes that match a job description.

EXEC WITH RESULT SETS

Ability of changing names and datatypes of results returned by SPs. Example: [sql]EXEC uspGetEmployeeManagers 16 WITH RESULT SETS ( ([Reporting Level] int NOT NULL, [ID of Employee] int NOT NULL, [Employee First Name] nvarchar(50) NOT NULL, [Employee Last Name] nvarchar(50) NOT NULL, [Employee ID of Manager] nvarchar(max) NOT NULL, [Manager First Name] nvarchar(50) NOT NULL, [Manager Last Name] nvarchar(50) NOT NULL ) );[/sql]

Control which rows will be returned by Add-hoc query

Specify a range of rows returned by a SELECT statement based on row offset and row count values that you provide: number of rows to skip and how many rows to fetch. Requires the ORDER BY CLAUSE. Example: [sql]ORDER BY DepartmentID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; [/sql]

Sequence Objects

A user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. It operates similar to an identity column, but sequence numbers are not restricted to use in a single table

THROW statement

Used to raise an exception and transfer execution to a CATCH block of a TRY…CATCH construct

14 New Functions

Conversion functions: PARSE, TRY_CONVERT, TRY_PARSE
Date and time functions: DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, EOMONTH, SMALLDATETIMEFROMPARTS, TIMEFROMPARTS
Logical functions: ,IIF
String functions: CONCAT, FORMAT

Support of Windows functions in the OVER clause

Window functions perform a calculation across a set of rows that are in some relationship to the current row. For example, you can use the ROWS or RANGE clause over a set of rows to calculate a moving average or cumulative total.

Security

Some of the new features:

User-defined server roles are now available
Default schema for Windows groups
Contained databases access - access to contained databases is permitted through contained database users which do not require logins

No Comments Yet

Let us know what you think

See all

Share this