Why Would I Upgrade to SQL Server 2012?
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 ClusteringA 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.
AlwaysOnMore 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 checkpointsDatabase-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 InstallationInstall 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 restoreImproved 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 boxEnables to check database pages for corruption and to restore selected corrupt pages from a database backup and subsequent log backups.
Partially Contained databasesWhen 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 advisorThere 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 indexesA 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 DropIndexes 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 TableNumber of partitions can now go up to 15,000 rather that the 1000 allowed in SQL 2008.
File tablesBuilt 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 searchBuilds 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 SETSAbility 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 querySpecify 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 ObjectsA 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 statementUsed 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 clauseWindow 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.
SecuritySome 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