The Case to Upgrade to SQL Server 2012

In an ideal world, everyone would always upgrade to the latest and greatest version of SQL Server. However, not all businesses jump at the chance to move to the newest version. Sometimes cost is a hurdle. Sometimes lack of vendor support is the issue. Oftentimes there just needs to be a real cost benefit justification to entice businesses to go through the pain of upgrading their existing SQL Server instances to a newer version. Here are some of the most compelling features that might make a business want to upgrade to SQL Server 2012 (formerly code-name Denali).

High Availability and Disaster Recovery with AlwaysOn

Without a doubt, the most compelling reason to move to Denali is the new AlwaysOn feature. AlwaysOn is the next evolution of database mirroring. Database mirroring is relatively easy to set up, and it provides an almost instant failover when it’s used in High Safety mode. However, database mirroring has several important limitations. First, database mirroring is limited to a single failover partner. In addition, database mirroring is capable of failing over only a single database at a time. Database mirroring must be implemented either synchronously or asynchronously—you have to choose one or the other. Finally, the databases on the mirroring server are in a state of recovery, so they can’t be directly accessed. To use the data, you must take point-in-time snapshots of the databases. AlwaysOn addresses all of these issues.

With AlwaysOn you can have up to four replicas (the new name for mirrored servers). These replicas can be connected both synchronously and asynchronously. A new feature called Availability Groups lets you fail over multiple databases simultaneously. The big selling point for AlwaysOn is increased availability and uptime—capabilities that are important to all production SQL Server implementations. For more information about these technologies, check out “AlwaysOn vs. Database Mirroring.”

Relational Database Development with SSDT

Another important addition is the SQL Server Developer Tools (SSDT), formerly code-named Juneau. The SSDT IDE is based on Visual Studio 2010 and provides the ability to develop all types of SQL Server projects using a single IDE—essentially eliminating the need to use Business Intelligence Development Studio (BIDS) separately. SSDT lets you perform relational database development and work with SQL Server Analysis Services projects, SQL Server Reporting Services projects, and SQL Server Integration Services projects. I have used it and it seems promising, but quite frankly I have my doubts about this feature. I do want to have this ability, but I have unsuccessfully tried to use Visual Studio for T-SQL development many times and typically wind up using SQL Server Management Studio (SSMS) instead. Many times I wind up jumping between SSMS, Visual Studio, and BIDS, so a tool to combine all three under a single roof would be very welcome.

Data Compression with Columnar Indexes

Another important new feature included in Denali is support for columnar indexes (code-named Apollo). The columnar index uses the same data compression technology that Microsoft uses in PowerPivot and incorporates it into the database engine. Indexed data is stored column-wise rather than row-wise. The big selling point of the new columnar index feature is improved performance. Microsoft’s claims of 100x performance gains certainly grab your attention, but it’s also important to realize that the gains you get (or may not get) depend on the type of data that you have. The columnar index feature is best suited for data warehouses. For more information about this feature, check out the white paper “Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0.”

Other Important Features

Although there are many other important features included in Denali, such as windowing enhancements in T-SQL, the new Data Quality Services, and a new data visualization capability (code-named Crescent), the feature I like best is the ability for Denali to run on Windows Server Core. Running SQL Server on Windows Server Core reduces the OS overhead, but even more importantly it reduces the server attack surface as well as the need to perform server patching, giving you improved security and availability.