Sneak Preview: New Features in Microsoft SQL Server 2012

(Don Conrad, a.k.a. “Don SQL”, is’s resident database wizard and expert in all things Microsoft SQL related. In this post he examines the next version of SQL Server. Missed an installment of The Next Byte? Check out Don’s complete Microsoft SharePoint and SQL Server series.) 

The Next Byte – Special Edition

Microsoft is busy promoting its next version of SQL Server, the database that powers the SharePoint platform. The RTM (“Release to Manufacturing”) edition of SQL Server 2012 is already available for download, and Microsoft will publicly unveil this new database technology before the end of March. As a result, SQL Server database administrators – and DBAs-in-training – are occupied studying up on the differences between SQL Server 2008 and this new version. For this “Special Edition” post, I’ve taken a break in my on-going talks about SQL Server to cover the highlights of new features in SQL Server 2012.

Perhaps the most pressing questions for SQL Server DBAs are:

1) What are the major enhancements?
2) What editions are available?
3) Did the pricing change?

Many of the feature updates in this version are truly significant and really enhance the usefulness of SQL Server. They are less about storing more, faster. Instead, they are more about how can I use my data quicker and better. They also directly address the massive increase in information that has been ongoing for 10+ years and is now threatening to be unmanageably large. How can we store, and access in a meaningful manner, the incredible quantities of data we already have stored and continue storing the increasing magnitude of data that we will have accumulated in the next few years?

When I first became a DBA, we thought a 100 megabyte database was huge. I mean, it wouldn’t even fit on a single PC hard drive. (Anyone old enough to remember the 33 megabyte limit on data partitions? I know because my grandmother used to complain about it …)

Nowadays, a medium database is probably about 20-50 gigabytes, and a large one is a terabyte. A terabyte is 10,000 times larger than 100 megabytes. My suspicions are that in five or six years, a terabyte will be considered an average database while a Internet-retail company such as Amazon or Facebook will have 100 Terabyte databases. Amazon and Facebook will lease central Utah for their storage. Antarctica may very well be an option for the year 2025.

Oh well, back to business. I’ll identify the features I believe are most important, especially in a SharePoint environment. As always, if you disagree or have any questions, please leave a comment and I’ll do my best to explain.

SQL Server 2012 Features:

1) Advanced Version of Server Mirroring. It enables users to read/only the data in the standby copy of a mirrored server. Previously mirrored data could not be accessed until the mirror was broken. In addition, other high-availability features (e.g. clustering, log-shipping and replication) can now be integrated into a cohesive group. This enables users to enter data into the main database, have it protected by either clustering or mirroring, use the secondary server for reporting and have it log-shipped off-premises. Apparently all of this is under a single configuration umbrella. Currently each of these has to be done separately, and with each system unaware of the others. Under 2012, HA has been geographically diversified. Applications requiring more than one database on more than one server can be included in one HA umbrella.

2) Enhanced PowerPivot. Power Pivot now features an empowered diagram-view. This resembles an Entity Relationship diagrammer. It shows the ways tables are related, but enables the users to change the relationships via drop-drag or to add additional tables. It is zoom-able and has a mini-view to help scan large models. It has two versions: one for end-users based on Excel and one for IT types based on SharePoint. Another new feature is a Measures grid. When this option is selected, a work-area is opened at the bottom of the spreadsheet. This area shows the results of simple math functions performed on a subset of the spreadsheet. Suppose you need to know the sum of three numbers in a column, independent of the others. Select the values, select Sum from the ribbon option, and Voila! You have the sum defined and shown in the Measures grid.

3) Power View. It is a fast, very usable presentation front-end designed for end-users. It’s built on top of SharePoint and MS Reporting Services. It works directly with stored data. It enables customers to create their own dashboards. Power View is a highly visual design experience. It is entirely web-based and resembles Office tools you’re already familiar with.

Within SharePoint, connected to SQL 2012, you’re offered the dropdown option of starting Power View when you right-click a data file. Power View immediately presents selected fields of data in a row-format. The initial fields selected are chosen by a “best-guess” process within Power View. You can manually add or remove as many fields as you want. The presentation format can be changed to any of various formats by a click. Once the underlying tables have been related to each other, any and all related tables can be present. Drill-downs can occur by simply clicking on the individual groups. For instance, if you have tables containing movies, genres, and revenues, you can easily show all movies, only R-rated movies, or other choices. The revenues related to each are quickly presented by PV and as quickly changed. The presentation then can be saved for later viewing.

4) Increased Data Retrieval Speed via:

  • The filetable data type.  This creates type enables storing as a data element in SQL Server, what amounts to an O/S level directory. More than one such directory can be inserted into a single filetable variable. Then regular documents can be dragged and dropped into these filetable directories. Photos, audio and movies can also be inserted within SQL Server and then displayed/played back as such. T-SQL can manipulate the data contents. This could dramatically simplify and expedite creating presentations. By placing various documents and objects within a single SQL Server database and table, the user could call the database from other machines, use T-SQL to access the objects and display them as they are retrieved. Neat.
  • Semantic Search. This works hand-in-glove with the filetable data type. In fact it won’t work without it. Once this has been enabled, key phrases are found by searching a document. Those are then stored and other documents are searched to find similar phrases. Keyphrases are chosen by the frequency in which they occur within the various documents
  • ColumnStore indexes. Currently all indexes are row-oriented. ColumnStores are, curiously enough, column-oriented. This mostly benefits BI. Not the columns, rather the benefits of ColumnStore.  I don’t fully understand why it is faster. I’ll have to do more research. MS thinks highly of it. A caveat: once a columnStore index is created, the underlying table cannot be updated. I suspect then, that the index will have to be dropped first if the table needs updating. Again, I need to do more research to fully understand.
  • Full Text Search improvements:
    i. Scale up to 350meg size or 500 million documents.
    ii. Searches 7-10 times faster. Goal is searches under three seconds.
    iii. Semantic Search which expands searches to identify similar documents.
    Kind of like a “Select … Where Like ‘%..%’ The full text search can be applied to the filetable data type to simplify accessing document data.

5) Enhanced Multi-Server Management. Server settings (CPU, resource loads, etc.) has been taken to a lower detail level. For instance, all SQL processes, Integration services, reporting and analysis can be placed under the same CPU cap on a server. Currently only the SQL Server engine is included. Also SQL Server no longer claims all allotted memory. It releases memory when it’s not needed.

6) Many other new features and important enhancements including:

  • Tools such as StreamInsight, Data Quality Services and Master Data Services have been added which enable validating and cleansing data.
  • SQL Server Data Tools for analyzing on-line databases for obvious weaknesses, debugging stored procedures and prototype changes in off-line databases, and then synching changes to production databases.
  •  Big Data Analytics with Apache Hadoop.
  • Features to take better advantage of the Cloud.
  • Many enhancements to SQL Azure, including Azure Data Sync and Scale-on-Demand.

Microsoft is positioning SQL Server 2012 to better target non-traditional data storage, (e.g. content and collaboration applications). This unstructured data support addresses the needs for eDiscovery, Healthcare documentation and general document management. Sounds like areas which SharePoint is designed for, doesn’t it?

One recent bit of info is that SQL Server has been identified by the ITIC (Information Technology Industry Council) as far and away the most secure major DBMS. In 2012 security has again been enhanced.

The intro of a new version of SQL Server probably necessitates new versions and new, mostly higher costs. We now just have to choose between three main editions: Standard, BI and Enterprise. DataCenter, Workgroup and Small Business versions are gone.

Pricing is now core, or should I say core-centric. Enterprise only has core-based pricing, Server + CAL for BI, and the option of Core or Server+CAL for Standard versions. For complete details, please see Microsoft’s SQL Server 2012 Licensing Overview.

Also, I need to add a disclaimer. The above gems were gleaned by careful reviews of reviews and presentations. Minor mistakes and misinterpretation features (Did you notice the smooth way I redefined any errors on “features”? Where have I heard that before?) will be blamed on the gleaner. Unfortunately I’ve lost his email address. All I remember is that he shares several assets with me: Good looking, great sense of humor, the first name of Don.

Until the Next Byte,

2012-03-19T08:51:06+00:00 March 19th, 2012|

One Comment

  1. mcitp training May 26, 2013 at 4:36 pm - Reply

    I know this site provides quality depending articles or reviews and additional information, is there any other website which presents these kinds
    of data in quality?

Leave A Comment