Get Ready for SQL Server 2012!

  • Article

This will be an exciting year for us. We can expect to see the launch of the next version of Microsoft SQL Server, codenamed “Denali”, sometime this year in the very near future. SQL Server 2012 is expected to deliver some groundbreaking enhancements to what is already a very powerful and stable database platform. Here are just highlights of 5 new features I’d like to share with you:

1. SQL Server AlwaysOn

SQL Server AlwaysOn is an integrated high availability and disaster recovery solution that provides redundancy within a datacenter and across datacenters. AlwaysOn supports configuring availability for specific databases (AlwaysOn Availability Group) and also the entire database instance (AlwaysOn Failover Cluster Instance). AlwaysOn Availability Group will eventually replace Database Mirroring in SQL Server and has the added ability of failing over a group of databases simultaneously, instead of only one at a time using Database Mirroring. On top of that, a new feature called Active Secondary enables improved utilization of secondary hardware by offloading read workloads to one or more secondary instances. AlwaysOn Failover Cluster Instance also supports the ability for multi-site clustering across subnets, which will offer improved site protection.

2. Power View

Power View (codenamed “Project Crescent”) is the name of the new interactive reporting and visualization tool in SQL Server 2012. It is a browser-based Silverlight tool that provides intuitive ad-hoc reporting for business users who will be able to create and interact with data views built from tabular models derived from PowerPivot workbooks or deployed to SSAS. Using Power View, the actual data is seen at all times and there is no distinction between ‘design time’ and ‘run time’. A report can also contain multiple views with its own visualizations and filters on each view. Although it is a feature of SQL Server 2012, Power View is only available through SharePoint, so keep in mind that SharePoint Server 2010 will likely be a requirement.

3. SQL Server Data Tools

The new SQL Server Data Tools (codenamed “Project Juneau”) is an integrated environment for database developers to carry out all their design and development work for any SQL Server platform (both on and off premise) within Visual Studio. It provides a unified toolset that combines the previous versions of Database Project in Visual Studio with the projects found in SQL Server Business Intelligence Design Studio (BIDS). With features to compile and publish databases to specific versions of SQL Server and SQL Azure, it also eliminates the requirement to maintain separate builds, which was oftentimes a consideration when deploying to SQL Azure.

4. Sequences

The community has been asking for sequences for a long time and the introduction of the new Sequence Object in SQL Server 2012 will be widely welcomed. A sequence is a substitute for the Identity property for a column and allows the generation of auto-incrementing numbers which are often used as a primary key for tables. Unlike identities, sequences are stored in memory and are not linked to a specific table, and they can also be shared across multiple tables or columns within a table. Apart from the performance benefits, the support for sequences also makes it easier to migrate database applications from Oracle to SQL Server.

5. ColumnStore index

ColumnStore indexes differ from traditional indexes by grouping and storing data by columns rather than by rows. They can be used to significantly speed up the processing time of common data warehousing queries, which typically involve summarizing huge amounts of data. With ColumnStore indexes, only the columns needed for a query must be read, and since most queries do not require all columns of a table, most columns will never be brought into memory. This, combined with heavy compression, improves performance tremendously (Microsoft reports improvements of up to 100x for star joins and similar queries).

These are just some of many exciting new features in SQL Server 2012. You can download and evaluate the latest release of SQL Server 2012 RC0 from https://technet.microsoft.com/en-us/evalcenter/hh505660.aspx?ocid=otc-f-corp-jtc-DPR&wt.mc_id=TEC_103_1_33

Our community group SPAN will continue to bring you the latest updates and developments from the world of SQL Server in general and SQL Server 2012 in particular. SPAN is also a great place for fellow DBAs and developers to network and stay in touch with the community. If you are not yet a member, please feel free to visit our Facebook page at https://www.facebook.com/groups/sqlspan/ and register as a member.

Note: This article is contributed by our MVP – Phua Chiu Kiang from SPAN community.