Microsoft SQL
Server 2012:
What to Expect
Prepared for ASPE by Global Knowledge's
Brian D. Egler MCITP-DBA, MCT,
Real Skills. Real Results. Real IT.
ASPE
RESOURCE
SERIES
Microsoft SQL Server 2012:
What to Expect
Brian D. Egler MCITP-DBA, MCT, Global Knowledge Course Director
Introduction
Microsoft SQL Server 2012 is coming
Three underlying themes categorize the SQL Server 2012 release: • Mission Critical Confidence
• Breakthrough Insight • Cloud On Your Terms
In this white paper, we will explore just some of the new features under each theme to give a feel for the capa-bilities of the upcoming release. Other features, while too numerous to describe here, are documented on the Microsoft web site at http://www.microsoft.com/sql . You can also download a pre-release version of the soft-ware or Community Technology Preview (CTP) from the same location.
Mission Critical Confidence
• AlwaysOn for High Availability and Disaster Recovery
• Project “Apollo” provides column compression and performance gains • Organizational compliance with enterprise-wide security controls
AlwaysOn for High Availability and Disaster Recovery
Figure 1: AlwaysOn Availability Group
Project Apollo provides column compression and performance gains
SQL Server 2008 introduced Data Compression at the Row and Page Levels to provide not only storage sav-ings but also, surprisingly, performance gains based on less I/O. In SQL Server 2008 R2, the VertiPaq technology allowed massive Column-level compression in conjunction with the PowerPivot add-in. In SQL Server 2012, the new column-store index, as part of Project Apollo, will provide the same massive column-level compression within the Database Engine itself. Data Warehouse queries against Star or Snowflake schemas will be optimized to give potential performance gains and storage savings in the order of 100 times improvement.
Figure 2: ColumnStore Index
Organizational compliance with enterprise-wide security controls
the normally tricky situation of orphaned users. However, the Contained Databases option has to be specifi-cally enabled at the instance level, an existing database needs to be converted to be Contained, and the users need to be migrated to become Contained Database Users. There exist some specific security threats that relate uniquely to Contained Databases (documented in SQL Server 2012 Books Online), but if you need a database to be easily portable, the benefits may outweigh the drawbacks.
Figure 3: Contained Database server option
The SQL Audit feature, which was introduced in SQL Server 2008 Enterprise Edition, allows Server- and Data-base-level auditing without the need for DDL or DML Trigger, giving a more efficient method of auditing that is built in to the Database Engine. SQL Server 2012 allows new features such as filtering audit output and now supports Server level audits for all SQL Server editions.
Breakthrough Insight
• SSAS 2012: VertiPaq, BI Semantic Model, and PowerPivot • Consistent data via Data Quality Services • Parallel Data Warehouse and Reference ArchitecturesSSAS 2012: VertiPaq, BI Semantic Model and PowerPivot
Figure 4: Tabular Project template Support for rowsets in excess of 2 billion rows is now available using this technology with rapid ad-hoc analy-sis, search, and sort. Business Intelligence Development Studio is now integrated with Visual Studio 2010 and includes the Tabular Model Designer, which is the developer equivalent of PowerPivot, with support for Data Analysis Expression (DAX) queries. Classic Multi-Dimensional solutions using MDX queries continue to be sup-ported with increased support for tables in excess of 2 billion rows.
Figure 5: Tabular Project - VertiPaq query mode
Consistent data via Data Quality Services
Server Integration Services (SSIS) and Master Data Services (MDS) to give a full data quality platform. The new SSIS Data Correction transform is based on DQS as is the MDS data quality functionality.
Figure 6: DQS Client - Data Quality Project
Parallel Data Warehouse and Reference Architectures
When Microsoft acquired DATAllegro, its stated direction was to support Very Large Databases (VLDB) through a scalable appliance architecture using the combination of hardware and software to support databases in excess of 100TB. Now with SQL Server 2012, that vision is closer to reality with many hardware vendors supporting the Fast Track configurations and reference architectures. The Parallel Data Warehouse edition supports features such as Remote BLOB Storage and large-scale data partitioning while using up to 256 logical CPUs for high performance in the VLDB arena.
Cloud on Your Terms
Figure 7: SQL Azure DataSync
Conclusion
Microsoft SQL Server 2012 has many great new features that will allow you to develop higher performing, more scalable next-generation applications using more than just relational data. Although this release will be a little early for many customers, the fact that the features are largely incremental in nature should reassure users that Microsoft is building on the established foundation of SQL Server 2008. Using the same architecture and man-agement tools, customers will be able to smoothly upgrade their systems and skills based on the need for the new features and according to their own schedule.Learn More
Learn more about how you can improve productivity, enhance efficiency, and sharpen your competitive edge through training. Beta: Administering Microsoft SQL Server 2012 Databases (M10775) Beta: Querying Microsoft SQL Server 2012 (M10774) Beta: Developing Microsoft SQL Server 2012 Databases (M10776) Beta: Implementing a Data Warehouse with Microsoft SQL Server 2012 (M10777) Implementing Data Models and Reports with Microsoft SQL Server 2012 (M10778)Visit www.globalknowledge.com or call 1-800-COURSES (1-800-268-7737) to speak with a Global Knowledge training advisor.
