SQL Server 2012, due out sometime in the first half of 2012, promises to be an exciting release on several fronts. I'll provide an overview of SQL Server 2012's new features and packaging options. I'll also briefly tell you about the system requirements, upgrade process, and some changes in the setup program.
Relational Database Engine Features
SQL Server 2012's relational database engine includes numerous availability, performance, security, manageability, and T-SQL improvements and features. I'll review the highlights in each area.
Availability. SQL Server 2012 includes the new AlwaysOn capabilities for high availability and disaster recovery. AlwaysOn lets you include up to four mirrors of your data, grouped into what are called AlwaysOn Availability Groups. You can choose the speed of mirroring, much like you can do in SQL Server 2008 R2. What's new is that the solution is built on top of Windows failover clustering, so the server uses virtual network names and virtual IP addresses. This allows clients to connect to the virtual names and IP addresses rather than depending upon coding the available partner servers in the connection string. This approach dramatically improves failover times to the secondary servers.
You can use the secondary servers for backup, reporting, or other read-only workloads, lightening your primary server's workload. This means your secondary servers can add business value and improve availability.
SQL Server 2012 also supports multi-subnet clustering, making geographically dispersed failover clusters across data centers (a much-anticipated feature among enterprise customers) a reality. This improvement, combined with the capability to use local disks for tempdb, offers some nice benefits for those organizations that use failover clustering.
The initial configurations for AlwaysOn must be made in the SQL Server Configuration Manager utility. If you try to use SQL Server Management Studio (SSMS) first, you'll receive an informative error message telling you exactly how to enable AlwaysOn. After you've enabled (and restarted) the SQL Server service, you can use the SSMS tools, such as the New Availability Group Wizard. As you step through the wizard, it's smart enough to tell you not only when a database can't work in an availability group but also why (e.g., you don't yet have a full database backup, you're not in the correct recovery mode). In a single interface, you configure all the available secondary replicas, as Figure 1 shows. Once configured, the AlwaysOn High Availability tree in Object Explorer is populated with the respective replicas, databases, and listeners, as Figure 2 shows.
Figure 1: Specifying the secondary replicas in the New Availability Group Wizard
Figure 2: Using Object Explorer to see the AlwaysOn replicas, databases, and listeners
Windows Server Core installations will be officially supported, which means you can run your SQL Server instances on servers without the Windows graphical interface. This capability should reduce the number of required Windows updates and reboots and give you a little more of your CPU and memory back to focus on being a server. However, note that only the 64-bit version of Windows Server 2008 R2 SP1 is supported in a Server Core installation. In addition, some SQL Server 2012 features -- such as Master Data Services, Data Quality Services, and the graphical interface -- aren't supported in a Server Core installation.
Performance. A new ColumnStore index, improved partitioning, and faster full-text searches are the most important performance enhancements in SQL Server 2012. If you have read-only data, you can create ColumnStore indexes on your frequently queried columns to dramatically improve query processing time. (The ColumnStore index capabilities work only on read-only data.) Partitioning has been extended to support 15,000 partitions in a single table. With the amount of data that organizations store growing, this expansion should help with the granularity of storage and usage of historical data. Although the Full-Text Search capabilities in SQL Server 2012 are very similar to those in SQL Server 2008 R2, there are significant improvements in performance and scalability.
Security. User-defined server roles have finally made it into SQL Server 2012. You can now define roles for a server and assign server-level permissions in unique configurations. These roles let you give DBAs and developers the permissions they need, without having to make them sysadmin server role members.
Auditing has been enhanced. There's a more robust auditing mechanism, and you have more control over how much disk space is used. Newer, stronger encryption algorithms are supported. Using the new concept of contained databases, support has also been added for bypassing logins altogether.
Manageability. SQL Server 2012's new Distributed Replay feature will be incredibly useful for testing, upgrades, and tuning. Like SQL Server Profiler, Distributed Replay lets you "replay" captured workloads. Unlike Profiler, Distributed Replay lets you replay captured workloads across multiple systems, simulating a more realistic set of workloads against SQL Server.
SSMS has a number of improvements, including a redesigned Restore Database dialog box. The SQL Server 2012 team enhanced not only its core capabilities but also its graphical interface. Improvements to the core capabilities include a new page-restore feature and improved logic. For example, SSMS does a much better job figuring out the correct order in which to do a restore from your full, differential, and log backups. The Restore Database dialog box now includes friendly condition notifications when you launch the dialog box and a really useful visual timeline that you use when restoring a database from multiple backups.
Support for Windows PowerShell has been extended in SQL Server 2012. The sqlps utility now snaps into the PowerShell 2.0 environment rather than being its own PowerShell 1.0 mini-shell. This change will be a big win for integrating Windows and SQL Server management actions.
T-SQL. The new THROW statement in SQL Server 2012 will be much more helpful than the RAISERROR statement. With the THROW statement, you can build dynamic, meaningful error messages for your code and pass them as proper errors, improving error-handling in T-SQL code.
The new PARSE, TRY_CONVERT, and TRY_PARSE functions for numeric, date, and time conversions look promising, particularly for international or multi-language programmers. I'm glad to see them because working with dates and times can be complicated.
The OVER clause has been enhanced to support Windows functions. If you're working with a set of rows and want to apply functions to those sets, this capability is for you. Itzik Ben-Gan has been lobbying for this capability for a number of years. For example, see his blog "Window Functions (OVER Clause) -- Help Make a Difference."
Finally, a number of analytic functions have been added to T-SQL. These functions enable more data-warehousing functionality to run natively within SQL Server.
SSAS Features
The unified Business Intelligence Semantic Model (BISM) affects a number of components in the business intelligence (BI) space, including SQL Server Analysis Services (SSAS). There are changes across many SSAS features to support BISM. For example, you'll find changes in SSAS's Analysis Management Objects library and XML for Analysis protocol.
You'll find a number of new features in SSAS, including:
- A new Tabular mode for installing the SSAS server; you use the VertiPaq engine, which was previously used in PowerPivot only
- New statistical, table, and search functions
- New row-level security functions and capabilities
SSAS's manageability has been improved across the board as well. For example, SSAS and PowerPivot now offer PowerShell cmdlets. And SQL Server Data Tools has an updated look and feel (which I'll discuss shortly).
SSRS Features
SQL Server Reporting Services (SSRS) now includes a new data-driven alerting capability known as Data Alerts. With Data Alerts, you can have emails sent to specific people based on changes in reports or based on a specific set of alert conditions.
Rendering SSRS reports has been improved, with a number of earlier limitations removed. You can now render a report as a Microsoft Excel document (Excel 2007 and later) or a Microsoft Word document (Word 2007 and later).
SSRS's integration into SharePoint has also been improved. Administrators will appreciate that they can now completely configure SSRS's integration into SharePoint with SharePoint utilities or with PowerShell. SharePoint users will appreciate Power View, the new presentation and visualization interface for SSRS. Power View, which is an addition to rather than a replacement for Report Builder, uses a new report file format (.rdlx).
SSIS Features
The updated dependency viewer can enhance your ability to perform SQL Server Integration Services (SSIS) package analysis and debugging. The dependency viewer helps you understand objects' dependencies, right from within SSMS. The viewer offers a number of troubleshooting views, stored procedures, and functions to help you troubleshoot package problems. The UI has been upgraded with several usability improvements.
When you open an existing SSIS package, the SSIS Package Upgrade Wizard launches to help convert your package to SQL Server 2012. The wizard points you to the Upgrade Advisor for a look at potential upgrade issues. (I'll talk more about the Upgrade Advisor later.)
After your package is converted, you'll see the new design environment with the updated look and feel, which Figure 3 shows. I also found this environment more intuitive than Business Intelligence Development Studio (BIDS) 2008 R2.
Figure 3: Presenting the new design environment for SSIS packages
Master Data Services Features
Master Data Services has a number of improvements as well, including improvements in the Excel add-on for Master Data Services and the Multiple Data Management web application interface. Security has been simplified, and the web UI works better with SharePoint.
Development Tools
With its incorporation of the new SQL Server Data Tools (formerly code-named Juneau) to create relational database projects in SQL Server 2012, SQL Server BIDS has been renamed SQL Server Data Tools. When you open Visual Studio to create a new project, you'll see the same Business Intelligence project templates, which you use to create SSAS, SSIS, and SSRS projects. If you navigate to Database and expand it, you'll see SQL Server as a category, as Figure 4 shows. Highlight SQL Server, and you can see options to create and work on SQL Server 2005 and SQL Server 2008 projects, SQL Server data-tier applications, and other types of database projects.
Figure 4: Using the SQL Server 2012 Business Intelligence project templates
Developing database tools will be much easier with these database project options. And if you're using the Visual Studio Team Data toolset, you'll be pleased to see even better functionality and capabilities now included with SQL Server.
Data Quality Services
Data Quality Services is a new component available in the Enterprise Edition and the new Business Intelligence Edition of SQL Server 2012. Data Quality Services integrates with SSIS and Master Data Services to improve data correctness, addressing such issues as making corrections to data based on recognized patterns, removing duplicate data, and performing other data-cleansing functions. To learn more about Data Quality Services, which consists of both server and client components, see SQL Server 2012 Books Online (BOL).
Packaging Options
There are quite a few changes to the product editions. The Datacenter Edition, which was introduced in SQL Server 2008 R2, has been rolled back into the Enterprise Edition. The Workgroup Edition and the Small Business Edition (available only to OEMs) are no longer available. The Itanium processor is no longer supported, so the IA64 port is no longer provided. And the licensing model has significant changes.
As I mentioned previously, the Business Intelligence Edition is new to SQL Server 2012. This edition would be a good choice if you want additional functionality in SSAS and SSRS and additional capabilities such as Data Quality Services, Master Data Services, Tabular mode, and PowerPivot for SharePoint, but you don't need more processor core support.
SQL Server Express LocalDB, which replaces SQL Server Express (now deprecated), will make many application developers happy. It's a lightweight installation of just the database engine. It doesn't require you to manage services and service accounts, which makes a lot of sense. SQL Server Express LocalDB is fully compatible with SQL Server yet is easy for application developers to install and use.
The SQL Server Enterprise, Standard, Evaluation, Developer, and Compact Editions are still available, as before. The Web Edition will be offered only to web hosters with a special license agreement.
System Requirements and the Upgrade Process
If you're interested in SQL Server 2012, you're probably wondering what's involved in the upgrade process. First, you need to make sure you're in compliance with the OSs and SQL Server versions supported for a direct upgrade:
- You must be running Windows Server 2008 R2 SP1, Windows Server 2008 SP2, Windows 7 SP1, or Windows Vista SP2.
- You must be running SQL Server 2008 R2 SP1 (or a later service pack, as applicable, for each edition), SQL Server 2008 SP2, or SQL Server 2005 SP4.
If you're running an older version of SQL Server or the Windows OS, you must first upgrade to a supported version before you can upgrade to SQL Server 2012.
SQL Server 2012 also requires that PowerShell 2.0 and both Microsoft .NET Framework 3.5 SP1 and Microsoft .NET Framework 4.0 are installed. Unlike SQL Server 2008 R2, SQL Server 2012 won't necessarily install the required software for you.
Hyper-V is fully supported in Windows Server 2008 SP2 and later. Microsoft Internet Explorer (IE) 7.0 or later is required for the SQL Server Data Tools, SSMS, and the report designer component of SSRS. For the graphical tools, your display must support 1024 ´ 768 screen resolution. Although there isn't an Itanium version of SQL Server 2012, 32-bit and 64-bit versions of SQL Server 2012 are available.
To make sure your environment is ready for an upgrade, you can run the SQL Server Upgrade Advisor. You can install the Upgrade Advisor from the home page of the SQL Server setup program. Once installed, you can run it by selecting Launch the Upgrade Advisor Analysis Wizard on the Start menu.
As Figure 5 shows, Upgrade Advisor consists of a wizard and a report viewer. The wizard is reasonably logical to follow. Make sure that all your SQL Server services are started before you run the wizard. After the analysis is complete, you'll get a report with any problems found, along with suggestions for how to address those problems. Note that the Upgrade Advisor also can analyze captured profiler traces to do an even better job assessing your existing environment.
Figure 5: Running Upgrade Advisor
For the server components, the upgrade will be relatively straightforward. However, I recommend that you first read about the upgrade process on SQL Server 2012 BOL and look at the readme notes for any special instructions. For some of the client-side components, such as PowerPivot for Excel, you'll need to uninstall the SQL Server 2008 (or other) version before you install the SQL Server 2012 version. The usual caution applies if you're using replication: You need to plan carefully and observe the restrictions, as documented in SQL Server 2012 BOL.
Setup Program Changes
The SQL Server 2012 setup experience involves some changes because of new capabilities that have been added, particularly to the BI components. However, if you're using SQL Server 2008 R2 or SQL Server 2008, you'll be right at home because their setup experiences are similar. I did find it a bit disconcerting, though, that the setup program repeatedly closed windows and then opened new windows. So be patient, and don't assume that the setup program isn't still running because the setup window has temporarily disappeared.
The setup program automatically attempts to check for updates before it launches, and I'm happy to say it's smart enough not to fail if there's no Internet connection. When you get to the Feature Selection page shown in Figure 6, you can select the capabilities and features you want to install. A nice enhancement is that the setup program automatically selects default service accounts for all the services. So, for many installations, the setup program auto-configures the correct defaults.
Figure 6: Selecting and installing the SQL Server 2012 features
One thing to watch out for is the changes to service accounts. If you're using Windows Server 2008 R2 or Windows 7, there are two new options: managed service account and virtual accounts. If you intend to use failover clustering or AlwaysOn Availability Groups, you shouldn't use the virtual accounts. You can read more about these new service account options in the Microsoft article "Configure Windows Service Accounts and Permissions."
Take the Plunge
There's a lot to evaluate in SQL Server 2012, and I hope you take the plunge. Download the product and give it a try!
