Product Reviews

SQL Server 2012: Raising the Bar on Database Availability

Among many new features, Microsoft's new database management system is well-suited for applications that require uptime and portability.

• By Brien Posey
• 06/05/2012

With the recent release of SQL Server 2012, Microsoft is looking to up the ante of its flagship database platform by positioning it for mission-critical environments with improvements in scalability, availability and recoverability.

The new database introduces a number of features that make SQL Server more suitable than ever for large-scale computing requirements where availability and portability is critical. Likewise, it lends itself well to applications with larger data sets and business intelligence (BI) needs, and those that require support for virtual environments, including synchronization with public and private clouds.

System Requirements
Before I elaborate on the new features in SQL Server 2012, you'll want to make sure you have the proper hardware and software configuration.

Prior to installing SQL Server 2012, you must have version 3.5 SP1 of the Microsoft .NET Framework installed. On servers running Windows Server 2008 R2, the .NET Framework can simply be enabled through the OS. On Windows Server 2008 systems (or computers running Windows Vista) the .NET Framework and the required service pack must be downloaded and installed prior to deploying the new database. SQL Server 2012 also requires version 4 of the .NET Framework, but that's installed automatically as part of the installation process.

Like virtually every other Microsoft server product that's being released these days, Windows PowerShell 2.0 is a requirement for deploying SQL Server 2012. Windows PowerShell 2.0 can simply be enabled on Windows Server 2008 R2 systems, but it must be downloaded and installed for servers running older OSes.

SQL Server 2012 also requires Internet Explorer 7 or later if you plan on using the Microsoft Management Console, SQL Server Data Tools, Report Designer, Reporting Services or HTML-based help.

The hardware requirements for SQL Server 2012 vary depending on which SQL Server 2012 edition and components are being installed. For a standard 64-bit installation, Microsoft requires a 1.4GHz processor (with a 2.0GHz or faster processor recommended), 1GB of RAM (with 4GB or more recommended) and 6GB of hard disk space (plus space for databases and any additional components you may wish to install).

The Installation Process
As is the case with most Microsoft server products, inserting the installation DVD displays the product's splash screen (see Figure 1). One thing I liked about the splash screen is it doesn't just jump right into the installation the way so many other products do. Instead, the splash screen opens to a Planning tab that contains links to all the documentation that might be helpful to the person installing the product. Microsoft goes beyond the basic documentation here and also provides documentation for failover clustering deployments and for upgrades from previous versions of SQL Server.

[Click on image for larger view.]

Figure 1. The SQL Server 2012 splash screen provides a wealth of product documentation.

When I read the installation documentation for SQL Server 2012, I wasn't surprised to see Microsoft supports running SQL Server 2012 within a Hyper-V virtual machine (VM). What did surprise me, however, was that the documentation contains a warning indicating that when SQL Server 2012 is run within a VM, Microsoft recommends shutting down SQL Server prior to shutting down the VM. I've never seen this mentioned for previous versions of SQL Server (although it's possible I might have missed such a warning in the documentation). Unfortunately, Microsoft does not provide any clues as to the consequences of shutting down a VM without first shutting down SQL Server.

Like every previous SQL Server release, SQL Server 2012 is jam-packed with new features. Of these new features, two stand out as providing real value and a compelling reason to upgrade to the latest SQL Server release. These features are AlwaysOn and contained databases.

AlwaysOn
AlwaysOn is a new way of providing high availability (HA) for databases. In previous versions of SQL Server, there were two main methods for achieving HA. You could perform a failover for an individual database through log shipping, or you could use failover clustering to perform failover at the server level. Although these solutions worked, neither was ideal. Failing over an individual database was often too limited of a solution to be practical, and failover clustering depends on having a passive server that does little more than wait for a failure to happen.

AlwaysOn is a new way of performing SQL Server failovers. It borrows the concept of Database Availability Groups from Exchange Server 2010 (but with some architectural differences).

Many database-driven applications actually depend on multiple databases. In order for such an application to function, all of the databases that are used by the application must remain online. SQL Server 2012 addresses this problem by allowing you to create a structure known as an availability group. An availability group is a collection of databases that are able to fail over as a single unit. That way, if a failure occurs, all of the databases used by a particular application will fail over even if the failure only impacts a single database. This should go a long way toward making sure applications continue to function properly in a failure situation.

There are advantages to this approach that go beyond simply keeping groups of databases together. If properly implemented, this new feature can actually eliminate the need for a standby SQL Server.

In SQL Server 2012 a single instance can host multiple availability groups, so it's possible to avoid having to provide a standby database server. To understand why this is important, imagine you have a SQL Server containing three different availability groups. Now, let's pretend the server fails.

If the server were operating solely as a node in a failover cluster (which is still an option with SQL Server 2012) then all of the databases would fail over to a standby server. When availability groups are used, however, it's possible for each availability group to fail over to a different SQL Server. As such, the three availability groups on the server I used in my example could potentially fail over to three separate servers.

Why is this important? This approach means you can divide up a failed server's workload among multiple targets in a failure situation. That way, you don't have to have a standby server that's capable of running a failed server's entire workload. Instead, you can get by using the free resources that are available on other production SQL 2012 servers, so long as you plan carefully to ensure the servers won't suffer from resource exhaustion during a failover.

It's important to understand that Database Availability Groups are not just a new form of failover clustering. With failover clustering, all of the cluster nodes are connected to a central storage device on which the actual SQL databases reside. The problem with using this approach is, if not properly implemented, the storage mechanism can become a single point of failure.

SQL Server 2012 availability groups allow databases to be replicated to other SQL Server database systems. You can perform replication locally within the datacenter and you can also perform remote replication to off-site SQL Servers as a way of protecting your data against datacenter destruction. This new feature lets you perform failovers across subnets, meaning you can actually set up resiliency between multiple datacenters. It's even possible in SQL Server 2012 to create a policy that defines under precisely what conditions a failover occurs.

On the subject of database replication, there's another new, noteworthy replication-related feature. Performing backups has always been a big challenge for SQL Server administrators. It's not that backing up SQL Server is especially difficult, but rather the backup process can consume CPU and disk I/O resources on the SQL Server, thereby impacting performance.

Over the years, backup product vendors have created a number of elaborate solutions that are designed to minimize the impact the backup process has on a database server's performance. For the first time, however, SQL Server 2012 natively provides us with a way to perform database backups without impacting performance.

The idea is to use something called a read-only secondary. A read-only secondary is a read-only replica of a database, where backups can be run against a read-only secondary rather than against the primary SQL Server. As a result, there's no concern about the backup process impacting database performance.

Contained Databases
Database portability has always been a big issue with SQL Server. Administrators or DBAs often find themselves needing to move a database from one SQL Server instance to another. This might be done in an effort to utilize more powerful hardware, or because an administrator may need to move a database as part of transitioning from the development to the production phase of the database's lifecycle. Regardless of the reason, moving databases from one SQL Server instance to another has always presented a bit of a challenge.

This is where contained databases come into play. In a contained database, users are created within the database itself. The user's credentials are also stored in the database, hence there's no dependency on server logins and there's no need to map security identifiers. Because user's credentials are stored within the database itself, databases can be moved without impacting user accounts.

Contained databases also solve a problem related to database collation. In case you're unfamiliar with the term "collation," it refers to a set of rules governing the use of characters for a specific language. For example, English, Greek and Russian all use different character sets, and therefore have different alphabets and different sort orders. SQL Server collation specifies the sort order to be used for each character set as well as the code page that is to be used to store non-Unicode character data.

The reason why this matters is because sometimes, when a database is moved from one server to another, the database was designed to use a different collation than what the new server is using. When this happens, TEMPDB will retrieve the collation from the server, thereby throwing the database out of whack. Although workarounds exist to prevent these sorts of conflicts from occurring, the workarounds are no longer required in SQL Server 2012. Contained databases maintain their own collation.

This isn't to say SQL Server 2012 allows you to seamlessly move databases from one SQL Server instance to another. There's still some work that has to be done. Even so, using contained databases should make the process easier.

For the first time in a long time, Microsoft really listened to its customers with regard to what features users want to see in SQL Server. SQL Server 2012 represents a giant step forward, especially in terms of HA and database portability.