SQL Server 2008 R2's Extended Management Features

Much improved is the lock hashing algorithm and installation this time out.

• By Joshua Jones
• 02/08/2010

Microsoft's "interim" R2 release of its database platform, is due out by May this year. While a number of new features have been introduced, such as the SQL Server Utility, there have been some "under the hood" changes as well that can help administrators and developers alike.

Lock Hashing Algorithm: In previous versions of SQL Server, you managed locks on pages, rows, and tables by calculating a hash for every object being manipulated. The lock hashes were then kept in memory, thereby preventing the need for SQL Server to store the lock information on the actual pages, and enabling SQL Server to manage the locks entirely in memory. The problem is that existing hashing algorithm occasionally resulted in duplicate hashes. This meant that SQL Server would run into situations where a row was being inserted/updated, but the transaction would be blocked by the existence of a lock on another, irrelevant row (een so, this problem is rare). This can cause issues, particularly during extremely large data loads.

In SQL Server 2008 R2, the SQL team completely rewrote the hashing algorithm. While the fundamental principle is the same, the algorithm used to calculate hashes generates a more evenly distributed set of values, greatly reducing the frequency of these collisions. If you've had problems in the past with large data loads (think Terabytes of data) running into strange blocking/locking issues, SQL Server 2008 R2 may in fact solve your problem. Obviously, the only way to be sure is to test, test, and then test again.

SQL Server Installation Changes: The SQL Server team has also made some changes to SQL Server installation, particularly in the area of unattended installations. Starting with this version, administrators can use SQL Server SysPrep to install SQL Server, specifying which step they wish to complete at a given time; Prepare or Complete.

With a Prepare Image step, the installation of SQL Server is stopped after the product binaries are installed, leaving administrators the duty of manually configuring computer, network, and account specific information later. The prepared image can be finished with the Complete Image.

The Complete Image step can finish an installation started earlier with the Prepare Image process. This means that administrators can, when using Windows SysPrep, build SQL Servers for deployment with basic components (such as the Database Engine, Reporting Services, etc.) installed but not configured. Then they can build different Complete Image steps based on roles or departments, thereby allowing for a single base installation image that can be "completed" in a customized fashion. This can reduce the number of images that need to be stored and managed, and greatly decrease the amount of time spent building new SQL Servers.

Unfortunately, SysPrep cannot install clusters, can only install the database engine and Reporting Services, and it cannot install SQL Server on IA64-based computers.

When combined with the new features in SQL Server 2008 R2, these features can mean better performance and manageability for SQL Server installations. Until next time, have fun!