New Features in SQL Server 2012 for Database Administrators


Feb 12, 2012

Introduction

At SQLPASS 2011, Microsoft has announced the launch of SQL Server 2012 which was also known to the SQL Server Community by its code name SQL Server Denali. It is expected that Microsoft will release SQL Server 2012 in the first half of year 2012. Currently SQL Server Enthusiast can download the Microsoft SQL Server 2012 Release Candidate 0 (RC0) version of SQL Server 2012 to explore new feature in the product.

Quick Overview of New Features in Microsoft SQL Server 2012 for Database Administrators

Microsoft SQL Server 2012 introduces lot many new features for Business Intelligence Developers, TSQL Developers and Database Administrators. This article gives you an overview of some of the new features in SQL Server 2012 for Database Administrators.

Contained Database in SQL Server 2012

Contained Databases is a new feature which is available in SQL Server 2012. A contained database is a database that will store all its metadata within the database thereby not storing any configuration information within the master database of the SQL Server Instance where the Contained Database is created. A contained database is isolated from other databases which are available on the instance of SQL Server. For more information read Contained Databases SQL Server 2012.

SQL Server AlwaysOn High Availability Feature

SQL Server 2012 introduces a new High Availability option namely SQL Server AlwaysOn. This feature is an enhancement to the existing Database Mirroring Feature which was introduced way back in SQL Server 2005 SP1. SQL Server AlwaysOn feature currently supports up to four replicas of database hence the data within the replicas can be queries, can be backed as well there by allowing maximum return on hardware investments. However, in order to configure SQL Server AlwaysOn feature you need to enable Windows Failover Clustering feature on all the nodes participating in the High Availability Environment hence you need to install Windows Server 2008 Enterprise Edition or later. Using SQL Server AlwaysOn Feature you can achieve Multi-subnet failover clusters i.e., you can configure Failover Cluster node to connect to a different set of subnet which can be either in the same location or in a geographically dispersed location there by improving your High Availability Environment.

Indirect Checkpoints Feature in SQL Server 2012

Indirect Checkpoints feature is an interesting feature which is available in SQL Server 2012. Using this feature a Database Administrator can change the target recovery time in seconds’ parameter for a particular user database from its default value ZERO. By default, recovery interval (min) value is set to ZERO at the SQL Server Instance level. When target recovery time in seconds’ value is ZERO automatic checkpoints occur for the database approximately once a minute for all the active databases and the recovery time for the database will be typical less than a minute. However, if you change the target recovery time in seconds’ parameter for a particular user database from its default value of ZERO. Then the recovery of the database in the event of a system crash will be more predictable than automatic checkpoints and as per Microsoft indirect checkpoint provide potentially faster recovery. For more information read Indirect Checkpoints Feature in SQL Server 2012.



Partially Contained Database

A Partially Contained Database is an interesting concept which is introduced in Microsoft SQL Server 2012. A Contained Database is a database which incorporates all the database setting and metadata within a database without any configuration dependencies on the instance of SQL Server where the database was initially created. As a result a user can get connected to the contained database without actually authenticating the user at the database instance level thereby isolating the database from the database engine completely. This will help a database administrator to easily move the contained database from one instance of SQL Server to another without actually bothering about orphan user issues. For more information read How to Configure and Use Contained Databases in SQL Server 2012.

User Defined Server Level Role

At last Microsoft has accepted the long pending demand from SQL Server Customers by introducing the feature to allow database administrators to create User Defined Server Level Role in SQL Server 2012. Beginning SQL Server 2012 a database administrator can create a User Defined Server Role and even add a SQL Server Level Permissions to the User Defined Server Role. This feature will definitely help many organizations to delegate non critical work to junior database administrators.

Support for 15,000 Partitions in SQL Server 2012

Microsoft SQL Server 2012 by default supports up to 15,000 partitions for a Table. In the earlier versions of SQL Server, this number was limited to 1,000 partitions by default. However, the good news is SQL Server 2008 SP2 and SQL Server 2008 R2 SP1 Support 15,000 Partitions. For more information read Partitioned Tables and Indexes.

Columnstore Index Feature SQL Server 2012

It is becoming very crucial for many organizations now-a-days to improve data warehouse query performance. Considering this demand Microsoft SQL Server 2012 introduces a new in-memory, Columnstore index build directly in SQL Server Database Engine. This feature can be used to improve the performance of query when run on large data sets. Using this feature one can get 10 to 100 times performance improvements while running queries on large data sets. One can use this feature to queries which run against Star Schemas which retrieves data from very large fact tables. Once you enable this feature on a table do remember that the table will become Read Only. i.e., Insert, Update, Delete and Merge operations are prohibited.

Online Index Create, Rebuild, and Drop Option in SQL Server 2012 for VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) columns

Microsoft SQL Server has come up a long way since its first release; it has been widely used by many organizations across the world to run mission critical workloads. With SQL Server 2012 one can Create Indexes, Rebuild Indexes and Drops Indexes which contain VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) columns as an online operation thereby ensuring business doesn’t experience any downtime during the routine maintenance window. This feature will help business to have their SQL Server’s up and running for user activity while maintenance is going on.

Achieve Maximum Stability, High Availability and Scalability with Windows Server 2008 R2

With SQL Server 2012 one can achieve maximum stability when it is run on Windows Server 2008 R2 as Windows Server 2008 R2 supports up to 256 logical processors and supports the use of up to 2 terabytes of memory on a single instance of Windows Server 2008 R2 instance. It is recommended to use SQL Server 2012 on Windows Server 2008 R2 as its support huge workload, dynamic scalability, high availability and stability.

IntelliSence Feature Enhancements in SQL Server 2012 Management Studio

In SQL Server 2012 IntelliSence feature suggest stings that are matched based on the partial words. However in the previous versions of SQL Server suggestions where made typical made on the first few characters of the word typed by the user.

Conclusion

In this article you have seen some of the new feature which are introduced in Microsoft SQL Server 2012 for Database Administrators.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article


History of Microsoft SQL Server


Geeks who read this article also read…




Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."