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
visualisation 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 favourites 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.
