Data Tank

The second version of Microsoft's SQL Server 2008 database server [1] is similar to Windows Server 2008 R2, in that it is a minor release: It doesn't offer an upgrade of the database kernel. However, if you compare the two versions, you'll see that the developers have improved the database server in many places, adapting it to reflect more closely the needs of the users.

As with previous versions, various editions of SQL Server 2008 R2 are available, and some features are available with the free Express Edition. Enterprise-critical features that improve availability and add scalability, however, are only available with the commercial editions. Table 1 gives an overview of the functions that the various editions of SQL Server 2008 R2 include and lists the maximum database size.

Old Friends and New

SQL Server 2005 introduced SQL Server Management Studio as a central management console for administrators wanting to create and configure databases, set up automated backups, and write queries (Figure 1). Here, you also manage database-specific objects such as triggers, views, or stored procedures. The console is clear cut and supports role-based access depending on the privileges owned by the user account.

Among the new features available, Power Pivot is a self-service analysis solution that lets users analyze data in Microsoft Office Excel 2010 and store and manage the data on Office SharePoint Server 2010.

Another new feature is the Master Data Services, which helps manage enterprise-critical data centrally across various systems, thus improving the data integrity. The new Application and Multi-Server Management tool is used for proactive database environment management and provides a centralized view of resource usage and consolidation.

Report Builder 3.0 is a report generation and manipulation component that also supports the visualization of geodetic data. The new version of Report Builder comes with improved wizards and visualization as well as more intuitive support for creating and editing reports (Figure 2). Report Builder is an SQL Reporting Services component.

StreamInsight is an event processing platform that helps administrators monitor and analyze data and show business decisions more or less in real time.

Installations and Provisioning Options

Whereas administrators of previous versions needed to install Reporting Services manually on an Internet Information Server (IIS), the installation routine in SQL Server 2008 R2 now handles this and enables the required Windows features and server roles autonomously. This functionality is made possible by PowerShell cmdlets and dependencies between roles and features, which were introduced with Windows Server 2008. If a server role requires a specific feature, the feature is automatically installed or enabled.

SQL Server is also easier to provision thanks to the SysPrep feature, which you may be familiar with from Windows operating systems. SysPrep gives the administrator the option of creating images of systems with the SQL server preinstalled for easier provisioning, which vastly reduces the database server provisioning overhead, especially in large enterprises (Figure 3).

The SQL Server SysPrep function works autonomously in the background and is divided into the preparation and configuration phases. The Prepare Image and Complete Image methods are new, although only available for the Database Engine and Reporting services. Prepare Image takes about 30 minutes to complete, compared with just a couple of minutes for Complete Image. According to Microsoft, administrators can save an average of 30 minutes per installation by using this approach, and this estimate doesn't even take into account the time you save installing Windows.

Problems and Solutions

During the installation of SQL Server 2008 R2 on a virtual machine under Virtual PC 2007 SP1 (VPC), an I/O error is thrown if the installation medium is an ISO file. This problem is caused by an error in the client virtualization ISO mounter. As a workaround, burn the file to a DVD and use the optical medium for installation on the guest. If the SQL service then doesn't launch, compression may be enabled. If it's enabled for the entire Windows system partition, you'll get an error when the database service launches. Databases on the SQL server must be in an uncompressed directory; SQL Server handles compression and will not allow the operating system to preempt it. Therefore, you need to disable compression on the system partition. If necessary, you can still configure specific directories to support compression.

MySQL Wizard-Based Migration

The SQL Server Migration Assistant (SSMA) for MySQL lets you easily migrate existing MySQL databases to SQL Server. The tool supports MySQL versions 4.1 through 6.0, SQL Server 2005/2008, and SQL Azure.

For the migration, you will need to have MySQL ODBC Connector Version 5.1 installed on both systems. SSMA for MySQL comprises two components: The client component (Figure 4) resides on the MySQL Server, and the server component runs on the SQL Server to migrate your tables, views, stored procedures, and transactions reliably.

Conclusions

SQL Server 2008 R2 offers considerable improvements to some of the database server's key features. Additionally, it offers easier provisioning (for standalone systems or for imaging) and high availability.

Failure protection, thanks to database mirroring, is available as of the standard edition. The free Express Edition of SQL Server 2008 R2 is useful for smaller projects or experimenting.