Microsoft’s SQL Server 2012 is released next month and available to download now (I am not sure what the distinction is). I have a high regard for Microsoft’s database server; it seems to me that the team mostly gets it right. The product has become somewhat diffuse though, especially as the Business Intelligence aspect has grown, and this may account for what to me is a rather unfocused launch for SQL Server 2012, even though its name suggests that it is the most significant release since SQL Server 2008.

The following slide summarises the new features, presumably with the type size suggesting the importance of each one.

But is the ODBC Driver for Linux really more important than the SQL Server Data Tools, for example? Not in my view; but that reflects how SQL Server represents different things to different people.

So what are the key new feature? Here’s my quick take.

Always On

A new feature called Availability Groups that is an improved version of database mirroring

Improved failover clustering which supports multi-site clustering across subnets – above to failover across datacentres.

ColumnStore Index

A new type of index for data warehouses. This is actually pretty simple: the name says it all. Here is Microsoft’s illustration:

and explanation:

A columnstore index stores each column in a separate set of disk pages, rather than storing multiple rows per page as data traditionally has been stored.

Why do this? Because it is more efficient when the query only requests a a few columns from the table. Microsoft claims performance improvements from 6X to 100X in cases where the the data can be cached in RAM, and thousand-fold improvements where the working set does not fit in RAM.

SQL Server Data Tools

This is my favourite feature, probably because it is developer-focused. These are the tools that were code-named “Juneau” and which install into Visual Studio 2010. There are some visual tools, but this is essentially a code-centric approach to database design, where you design your database with all its tables, queries, triggers, stored procedures and so on. You can then build it and test it against a private “localdb” instance of SQL Server. What I like is that the database project includes the entire design of your database in a form that can be checked into source control and compared against other schema versions. Here is the Add New Item dialog for a database project:

Data Quality Services

Data Quality Services (DQS) lets you check your data against a Data Quality Knowledge Base (DQKB), the contents of which are specific to the type of data in the database and may be created and maintained by your business or obtained from a third-party. If your data includes addresses, for example, the DQKB might have all valid city names to prevent errors. Features of DQS include data cleansing, de-duplication through data matching, profiling a database for quality, and monitoring data quality.

Illustration and more details are here.

Updated SQL Server Management Studio

SQL Server Management Studio now runs in the Visual Studio 2010 shell.

LocalDB

LocalDB is a local instance of SQL Server aimed at developers and for use as an embedded database in single-user applications. It is a variant of SQL Server Express, but different in that it does not run as a service. Rather, the LocalDB process is started on demand by the SQL native client and closed down when there are no more connections. You can attach database files at runtime by using AttachDBFileName in the connection string. LocalDB is intended to replace user instances which are now deprecated.

FileTables

This is the most intriguing feature in SQL Server 2012. It is described here:

The FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server … In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.

and the purpose:

Enterprises can move this data from file servers into FileTables to take advantage of integrated administration and services provided by SQL Server. At the same time, they can maintain Windows application compatibility for their existing Windows applications that see this data as files in the file system.

Integration of the file system and the database is not a new idea, and Microsoft has tried variants before, such as the “M” drive that was once part of Exchange, the aborted WinFS feature planned for Windows Longhorn (Vista), and SharePoint, which can store documents in SQL Server while presenting them as Windows file shares through WebDAV.

That said, FileTables in SQL Server 2012 are not an attempt to reinvent the file system, but presented more as a way of supporting legacy applications while managing data in SQL Server. It is an interesting feature though, and it would not surprise me if users find some unexpected ways to exploit it.

Power View

Codenamed “Project Crescent”, this is a web-based reporting client for businesses that have embraced Microsoft’s platform, because it has several key dependencies:

• SharePoint Server Enterprise Edition
• SQL Server Reporting Services
• Silverlight on the client

In fact, Power View is described as:

a feature of SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Server 2010 Enterprise Edition

Power View reports that I have seen do look good, and have an Office ribbon style designer for designing customising the report. That said, I would guess that Microsoft now wishes it had used HTML 5 rather than Silverlight for this – there are those Apple iPad and Windows 8 Metro users to think of, after all.

Microsoft emphasises that Power View is not a replacement for Report Designer or Report Builder, but an ad-hoc reporting tool.

Closing thoughts

There is more in SQL Server 2012, as a glance back at the initial slide will tell you, but the above is a starting point if you are wondering what it is all about. It is also worth noting that Microsoft still gives away SQL Server Express which supports up to 10GB per database and includes many of same features as the paid-for versions; it is the same product at heart.

Someone who finds that SQL Server Express actually meets all their needs asked me why Microsoft gives it away. My guess is that this is a consequence of all the other free database engines available such as MySQL, PostgreSQL, interesting  newer NoSQL options like mongoDB, and of course equivalent free versions of Oracle and IBM DB2. A proportion of customers who start with SQL Server Express will grow into the paid-for editions.

This does make SQL Server Express an excellent choice for smaller scale applications and small businesses, particularly since it integrates smoothly into Microsoft’s developer stack. Having said which, I am becoming something of an Entity Framework sceptic, but that is a story for another day – and fortunately you do not have to use EF if you do not want to.