SQL Server 2012 features: Weighing the benefits, limitations
A comprehensive collection of articles, videos and more, hand-picked by our editors
More on SQL Server 2012
Learn more about AlwaysOn high availability
Column-store indexes are one of the bigger features
Find out how much SQL Server 2012 costs
Microsoft SQL Server 2012 came out in a “virtual launch” this month, which allows IT shops to download an evaluation version ahead of the product’s April 1 general availability. IT shops will begin to evaluate if they want to upgrade, and if so, when. In this edition of “SQL in Five,” Microsoft data platform specialist Mark Kromer lists his favorite features of SQL Server 2012, talks about high availability and column-based querying and argues in favor of SQL Server 2012’s new core-based licensing model.
If you had to pick one or two features that are most important in SQL Server 2012, what are they and why?
Mark Kromer: If I had to pick just two, then I would go with my two personal favorites: AlwaysOn and column-store indexes. AlwaysOn availability groups (AGs) are important because they solve several specific issues customers raised when implementing database mirroring. AGs now include multiple secondary servers, so there is no need for a witness server, readable secondaries or multiple databases within a mirrored group. Column-store indexes can dramatically improve query performance for data warehouse workloads and make a big difference in data warehousing and business intelligence.
SQL Server 2012 has core-based licensing, as compared with processor-based licensing in SQL Server 2008 R2. Why is this change good for IT shops migrating to SQL Server 2012?
Kromer: The way Microsoft has implemented core-based licensing is very beneficial to IT shops because the price of an Enterprise Edition license does not increase unless your server has processors with more than four cores. Because the price of a SQL Server core license is essentially today's processor license divided by four, this means that there is no price increase for core-based licenses on a server with four-core or fewer processors.
Can you explain how AlwaysOn and its availability groups will improve high availability in SQL Server 2012?
Kromer: AlwaysOn is a marketing term that encompasses all of the SQL Server high-availability features but mostly focuses on advances made to clustering and the new availability groups feature. AGs provide database mirroring-like functionality with synchronous and asynchronous options but allow multiple secondaries that can be read-only as well as allowing multiple databases included in that availability group. This allows you to offload reporting and database backups to a secondary server. Automatic failover is provided to a synchronous secondary server and uses a virtual host name capability via "listeners" that leverage the Windows Cluster Server API [application programming interface] instead of the existing SQL Server database mirroring secondary server ODBC [open database connectivity] clause. This means that AGs provide high availability, disaster recovery -- you can use asynchronous transactions to a remote site secondary server -- and performance gains by offloading backups and reporting to a secondary. Although AGs use Cluster Server, you do not need to configure complex cluster configurations. AGs only require that you install SQL Server as a typical SQL Server instance, as opposed to a failover cluster instance, and make sure that each node joins the cluster. SQL Server will do the remaining work, such as creating the necessary services and virtual names. This also eliminates the need for a witness server.
How can column-store indexes improve querying time by such a large factor? Also, how do column-store indexes compare with other column-based querying technologies out there, such as hybrid columnar compression in Oracle's Exadata?
Kromer: In SQL Server, as opposed to Oracle's hybrid approach, creating columnar indexes tells SQL Server to store data by column on separate data pages, making for highly compressed data. This improves performance and allows the SQL Server query engine to enable new batch-mode execution. This is a pure column-store based approach which greatly improves I/O scan performance and buffer hit rates. It’s very beneficial in data warehouse and reporting schemas.
How might the release of SQL Server 2012 affect the overall database market?
Kromer: I've only recently began talking to customers about how SQL Server 2012 will impact their business, but I can name five areas that I have seen time and time again during these early days of SQL Server 2012 that are going to make a difference for Microsoft:
Security: SQL Server has added new, included security features over the years, such as transparent data encryption, custom server-level security roles for separation of duties and a row-level label security framework on CodePlex.
Appliances: As opposed to a single, over-engineered physical architecture, Microsoft engineering works intimately with hardware vendors to ensure that customers have an option to purchase a database appliance that is engineered for a specific workload, such as data warehouses, private cloud and in-memory analytics.
Cloud: SQL Server 2012 and SQL Azure are providing more integration, from DACPAC [data-tier application package] to easy migration into public-cloud based computing on Windows Azure and SQL Azure.
Big data: Hadoop integration and a complete big data stack from HDFS [Hadoop Distributed File System] on Windows Server or Windows Azure to SQL Server Analytics is enabled via a partnership with the Apache open source project and Hortonworks.
Pricing: With the new licensing switch from per-processor to core-based metrics, Microsoft was able to still maintain the prices at a level that keeps SQL Server as the TCO leader in the market.
Mark Kromer has more than 16 years of experience in IT and software engineering and is well-known in the business intelligence (BI), data warehouse and database communities. He is the Microsoft data platform technology specialist for the mid-Atlantic region. Check out his blog at MSSQLDUDE.