Review: SQL Server 2012 stands tall
Huge HA and indexing improvements top the list of great new features in Microsoft's latest database release
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.
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.