In our previous article, SQL Server 2008 Overview, we looked over the different features that the Microsoft SQL Server provides. In this article we will focus on the new and advanced features of the latest SQL Server release – SQL Server 2012. They include, but are not limited to, the new High Availability solution AlwaysOn, improved security, and the capability of syncing with the cloud.

AlwaysOn consists of two main components – AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances. Let’s first focus on the Availability Groups. An availability group is a combination of databases that are hosted on a primary server and up to four secondary servers. Each availability group member is capable of replying to clients’ requests in the event that the primary group member is unavailable. All the databases in an availability group fail over to another group member together. This functionality is built on top of the Windows Server Failover Clustering feature and requires its installation.

The high availability of the SQL server can be further enhanced by using Failover Cluster Instances (FCI). However, this approach requires shared storage. Shared storage is not required for the AlwaysOn Availability Groups. The servers in an availability group are required to have shared storage only if they are also acting as FCI. Be aware that AlwaysOn Availability groups automatically failover to a different replica when a problem with primary one is detected. This automatic failover is not supported in FCI, so you will have to set the availability group members acting as FCI to manual failover.

Another service Microsoft offers for high availability of your SQL databases is SQL Azure Data Sync. It allows you to easily sync your on-premise SQL Server 2012 with your SQL Azure’s data. That’s why SQL Server 2012 is considered a cloud-ready application. Furthermore, it gives you the ability to specify exactly which columns you want to be synchronized. It is a full implementation of a service and there is no need for you to write extra code for this synchronization to occur. You can use it within your Disaster Recovery plan so that you will have a complete copy of your on-premise database on a different physical location.

Let’s now focus on the new features that SQL Server 2012 brings to the main SQL features:

1) Analysis Services
SQL Server 2012 offers a unified model for all the needs your organization may have. This is the BI Semantic Model. It has the benefits of the tabular and the multidimensional models and it includes: PowerPivot for Excel to meet end users’ needs; PowerPivot for SharePoint to meet teams’ needs; and Analysis Services to meet corporate’s needs. It offers you the choice between cached and passthrough storage mode and security on both row and cell level.

2) Reporting Services
In SQL Server 2012 the Reporting Services are now a SharePoint’s shared service taking advantage of all the native SharePoint service’s capabilities. Another big improvement is the enhanced end user alerting functionality. In SQL Server 2012 users can be notified when data in a report of their interest change.  This is done through a new user-friendly interface. It’s worth mentioning that the Reporting Services in SQL Server 2012 now support the 2007 and 2010 versions of Word and Excel.

3) Integration Services
One of the new characteristics of Integration Services is the new Project model for deployment. The traditional Package model is still supported. However, with the Project model you can take advantage of some advanced features, such as:  eased centralized management of parameters and connection managers; and enhanced security, since the projects can be automatically encrypted and access to them easily controlled. Amongst the enhancements in SQL Server 2012 Integration Services is also the support of Visual Studio for Applications 3.0 and .NET 4.0.

You can use all of these new features with SQL Server 2012. The editions basically remain the same as with SQL Server 2008 with the addition of SQL Server 2012 Business Intelligence. This is a customized version of SQL Server that provides a platform for building and deploying business intelligence solutions in the most secure and scalable way. All the supported features that an SQL Server 2012 edition offers can be found and compared here: http://technet.microsoft.com/library/cc645993(SQL.110).aspx