New Features in SQL Server 2012:-

New Features in SQL Server 2012:-

AlwaysOn Availability Groups –  AlwaysON Availability Groups is a revolutionary feature which replaces many other features such Database Mirroring, Logshipping and up to an extent Replication. AlwaysON also known as HADRON, which provides High Availability and Disaster Recovery solution for critical databases. AlwaysOn Availability Groups is a container which consists of one or more databases which can together failover as an unit. a set of replicas(servers) host the primary and secondary AlwaysON Availability Group databases. AlwaysON configuration Availability Group has One Primary Replica and up to 4 Secondary replicas including one Synchronous and 2 asynchronous replicas. We can perform Read/Write operations on Primary replica and can use Secondary replicas for Read_Only work loads, thus offloading primary server. We can have multiple Available Groups created between same server and failover happens at Availability Group level. Failing over an availability Group brings all databases part of that group ONLINE on the secondary replica. We can also offload backups to be performed on the secondary replicas. CheckDB can also be offloaded to the secondary replicas. It is also simple to implement AlwaysON Availability Groups.

AlwaysON SQL Server Failover Clusters – SQL Server AlwaysON Failover Clusters allows multi-subnet failover clustering where cluster nodes can be spread at geographically at different locations and data is moved to other node through SAN level replication. This provides both High Availability and Disaster Recovery. Another enhancement include flexible failover policy, where in we can choose the condition on which a failover should occur.

Contained Databases – SQL Server 2012 provides partial containment. In previous versions of SQL Server, moving a database from one server to another server also involved moving associated logins and other objects. Starting with SQL Server 2012 Contained Databases, users are associated with the database itself, thus no more dependent on the SQL Server instance. Moving contained database to new server does not require any additional actions of creating logins, or fixing orphan users. Also, previously there were issues related to collation where SQL Server instance is of different collation and databases are of different collation. SQL Server 2012 contained databases solves this problem where tempdb now creates objects related to contained database under same collation of the database.

ColumnStore Indexes – Microsoft SQL Server team brought the concept of column based indexes from VertiPaq. Columnstore indexes in the SQL Server 2012 Database Engine is used to significantly speed-up the processing time of some common data warehousing queries. In traditional clustered and non-clustered indexes which data is stored row wise in pages, where as in SQL Server 2012 ColumnStore Indexes, data is grouped and stored column-wise, so each column can be accessed independent of other columns.

Enhancements to ONLINE rebuild index operations – Starting SQL Server 2012, we can rebuild indexes ONLINE for BLOB indexes, which have data types like nvarchar(max), varchar(max), varbinary(max). This was not possible in previous SQL Server versions.

Database Recovery Adviser – In previous versions of SQL Server, DBA’s had to perform manual restores in order to recover a database from a failure for which Database administrators had to plan and restore a set of backup files in a logically correct order. New Database Recovery Advisor facilitates preparing the restore plans which implement optimal and correct restore sequences, thus reducing the overhead and any manual mistakes. Also with AlwaysON Availability Groups, backups could be performed on secondary replicas as well, in such cases Database Recovery Advisor will be helpful in preparing the restore sequence.

Reduced downtime for Application Upgrades – Adding new columns with default constraint is now meta data only operation, thus significantly reducing amount of time for changes.

New FILETABLE feature has been introduced in SQL Server 2012, which is built on top of FILESTREAM technology. With FILETABLES, we can now store the 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.

Product Update is another new feature introduced in SQL Server 2012 Setup where, setup integrates the latest available product updates with the main product during the installation time, so that the main version and all applicable updates are installed at the same time, thus reducing additional overhead and downtime later.

New Startup Parameters has been added to the SQL Server 2012 Configuration manager, so it makes it simple and manageable for changing paths for master database files or to add any startup trace flags.

Starting with SQL Server 2012 support for partitions is increased to 15,000 by default, which in previous versions was limited to 1,000 by default.

Starting with SQL Server 2012, FileStream FileGroups can contain multiple files and can be on different drives, which improves I/O performance by spreading the I/O load.

There are some changes in Licensing of SQL Server 2012 Enterprise Edition, where two types of Enterprise Edition Licenses are sold based on Server/Client Access License (CAL) or Per Core licensing. Also Data Center Edition has been removed in SQL Server 2012 and Business Intelligence Edition has been introduced.

Starting with SQL Server 2012, we can install it on Windows Server 2012 Core, which basically does not have any GUI.

– SQL Server Business Intelligence Development Studio has been redesigned and modified into SQL Server Data Tools (SSDT).

There are many other features and enhancements made in SQL Server 2012 as well, refer MSDN for more information