As we’ve come to expect from new SQL Server releases, SQL Server 2012 has so many new features that it’s impossible even to mention them all. Nearly everyone is well served, from the BI-hungry users of Reporting Services to the IT folks who oversee query performance and uptime. SQL Server 2012 brings improvements across the board, with only a few disappointing exceptions.
Reporting Services adds a Web front end for data exploration and visualization called Power View. Analysis Services introduces a new semantic model, giving BI specialists more flexibility in building solutions. New column store indexing shifts query performance into high gear, while new Data Quality Services and improvements to Master Data Services round out the tools for taking care of company data.
Of course, SQL Server 2012 also has a slew of new features that hit DBAs right where they live. I’ll focus on these in this review, starting with some new capabilities that take high availability to a new level.
Higher high availability
Let’s start with one of the largest efforts for this release, AlwaysOn. AlwaysOn is the new HA technology that will put database mirroring on the deprecation list. Being limited to a single target server for a mirror has been a nuisance since mirroring was implemented. Also, mirroring targets are completely useless until your primary database goes down, because they can’t even be read from. AlwaysOn fills in both of these holes. You can easily configure many read-only copies of your primary database and use them for reporting while you’re waiting for a disaster. And of course just like mirroring, you can set an AlwaysOn target to be completely synchronous with the primary or allow it to lag a little behind.
Readable mirrors alone solves a pretty big issue with mirroring, but the biggest issue solved by AlwaysOn is the failover of multiple dependent databases. Oftentimes two or more databases depend on one another for their operations, and if one of them has a failure, it’s not enough to swap in just its mirroring partner because the databases it depends on are still on the other server. AlwaysOn addresses this problem with Availability Groups, which allow you to define a group of databases that must fail over together, so even if there isn’t anything wrong with the other databases in the group, they’ll fail over with the failed database to keep things running.
Database dependencies are another area DBAs have trouble managing. When you restore databases to different environments, there are a lot of considerations like linked servers, user accounts, and cross-database procedures and views that all have to be synched up to work properly. SQL Server 2012 introduces a feature called ContainedDB that allows you to identify a database to be self-contained so it’s not allowed to have external dependencies. You can’t write objects that have any dependencies in other databases, or even external dependencies in the server instance itself. In fact, the user accounts in a ContainedDB don’t even have a server-level login associated with them, so you don’t have to worry about synching those accounts when you move the database to a new box. You should be aware that there are some limitations in this first version, but we’re off to a good start.
The next big improvement is with event logging and tracing. SQL Profiler is now officially on the deprecation list courtesy of the new XEvents (Extended Events) GUI. XEvents have been greatly expanded in this version and the new trace mechanism will use them exclusively. It’s going to take some getting used to, but I promise it’s a good thing. XEvents are far more flexible and much more lightweight than SQL Trace, which means tracing activity will have much less impact on your box. Along with the new trace mechanism, there’s also a new replay mechanism called Distributed Replay, and fortunately it does just what the name implies, allowing you to replay a trace workload from multiple boxes so you can better simulate your production activity. This is really handy when you’re testing upgrades or even just data explosion scenarios and the like.
Indexing improvements
There have been two major indexing improvements — online re-indexing and column store indexes. It’s often the least touted features that make the biggest difference to DBAs, and the enhancement made to online re-indexing is one of those. We were all thrilled to get online re-indexing in SQL Server 2005, at least until we discovered that it didn’t work for all data types. We quickly discovered that any index that has varchar(max), nvarchar(max), varbinary(max), or XML columns couldn’t be re-indexed online. So we’ve had to build logic into our re-indexing routines that understands there are two types of indexes. Now that these data types can be re-indexed online, we can have true online index maintenance for our 24/7 applications. If only we could re-index individual table partitions online, we’d really be in business.
SQL Server 2012 also brings a new type of index called a column store index. Traditional indexes store data for each row and then join all those rows to complete the index. A column store index stores data for the columns and then joins those columns together to complete the index. Microsoft says this delivers about 10x the performance of a traditional index in the same scenario. However, in the data sets I’ve used and in the demos I’ve seen, the performance gain is many times more than that. Column store indexes were created for use in warehouses with huge data sets. The reason you don’t want to use this for OLTP is because column stores are read-only.
As long as we’re talking about performance, note that SQL Server 2012 can now be installed on Windows Server Core. This can increase not only the speed of the server in general, but also the security. On Server Core, there are fewer services running which means fewer security holes to plug, and fewer software bugs dragging down performance.
T-SQL enhancements
T-SQL doesn’t have many new features, but the ones it has are cool enough. My favorites are the new LAG and EOMonth windowing functions. LAG gives each row in your result set access to the column data in the previous row. So say you’ve got a price column and you want to be able to easily display the current price and the previous price in the same row. LAG will let you do this. EOMonth is a function that lets you have instant access to the last day of the month for the value passed into it. There are other new functions, but I’ll have to leave them to you to discover on your own.
Among all the T-SQL enhancements, FileTable is probably the coolest feature there is. Basically, it’s file stream data that can be accessed directly from the file system. Let me explain. File stream allows you to store documents on the file system, but they’re backed up with the database so you can make sure they’re protected. FileTable takes this one step further. FileTable makes the line between database and file system transparent.
You start by defining a table as a FileTable and assigning it a directory on the file system. Now all you have to do to put files in the table is just drop them into the folder from Windows Explorer. There’s no T-SQL to write, nothing else special to do. You just manage the files at the file system level like you always have and they get stored in the database. You can also make changes to these files directly from T-SQL or at the Windows level.
SQL Server 2012 isn’t all good news. There are some disappointments. For me, the biggest disappointment is the lack of love given to PowerShell in this release. Other than a few cmdlets for AlwaysOn and backup/restore, you’ll find no real PowerShell improvements in SQL Server 2012. As heavily as Microsoft is relying on PowerShell these days, I fully expected more. Another disappointment is the lack of enhancements to SSMS (SQL Server Management Studio). Microsoft has ported SSMS to Visual Studio 2010, but other than what we get from that move (like better snippet management and integration with Team Foundation Server), there’s really nothing to help DBAs manage their servers better. I would have liked to see better multi-server management and reporting features, and tighter integration of PowerShell into SSMS, for example.
I’ve long had a five-point rule for database upgrades. It means you should have at least five features you’re interested in before upgrading your database. And while I was only able to talk about a small handful here, there are a plethora of features I wasn’t able to talk about. SQL Server Integration Services has had a major overhaul, and there are some really nice enhancements to SQL Server Analysis Services and SQL Server Reporting Services as well. You will have no trouble finding your five points for the upgrade to SQL Server 2012.