New Features in SQL Server 2012 for Database Administrators
Feb 12, 2012
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.
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...
History of Microsoft SQL Server
Geeks who read this article also read…
- New Features in Microsoft SQL Server 2012 for Database Developers
- Using WITH RESULT SETS Feature of SQL Server 2012
- SQL Server Paging Using OFFSET and FETCH Feature in SQL Server 2012
- Indirect Checkpoint Feature in SQL Server 2012
- How to Downgrade SQL Server Database from a higher version to a lower version
- Why an SQL Server Database from a higher version cannot be restored onto a lower version?
- When SQL Server was last restarted?
- Change SA Password in SQL Server
- Troubleshooting SYSPOLICY_PURGE_HISTORY Job Failure in Stand Alone Instance SQL Server 2008
- Troubleshooting OLE DB Provider Microsoft.ACE.OLEDB.12.0 is not registered Error
- Troubleshooting SQL Server blocked access to procedure sp_send_dbmail
- Performance Dashboard Reports in SQL Server 2012
- Tips to Avoid Account Lockout Issues
- Encrypt Database Backups in SQL Server Using MEDIAPASSWORD Option
- Using SP_SERVER_DIAGNOSTICS Stored Procedure Quickly Gather Diagnostic Data and Health Information in SQL Server 2012
- How to identify if the database was upgraded from a previous version of SQL Server
- Microsoft SQL Server 2008 R2 Build Versions
- Installing SQL Server 2008 R2 on Windows Server 2008 R2
- New Features in Microsoft SQL Server 2008 R2
- How to repair a Suspect Database in SQL Server
- Steps to Rebuild System Databases in SQL Server
- How to Remotely Shutdown, Restart or Log Off Windows Server across the network
- How to Backup an Analysis Services Database Using SQL Server Management Studio
- Using Transparent Data Encryption Feature of SQL Server 2008
- Database Backup Compression Feature In SQL Server 2008
- Auditing SQL Server Logins
- Read More SQL Server Articles…