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):
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.
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
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
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!
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.
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.
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.
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 )
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.
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
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
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.
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