Some consider SQL Server 2008 R2 a “business intelligence release” because the largest, most publicized changes cater to the BI functionality of the product. There’s a lot more to R2 than that, though. Here we’ll look at some of the less popular — but no less important — changes to the latest version of SQL Server.
1. Increased SQL Server Express database size.
In the past, one of the main things that kept people from using the free version of SQL Server Express was the small database size. While SQL Server Express still has a maximum database size limit, it has been increased in SQL Server 2008 R2 by 150% (from 4 GB to 10 GB). This increase in size makes it possible for a much larger set of applications to use Express. It also allows applications that start on SQL Server Express to use the Express instance for a much longer period of time before being upgraded to one of the paid-for editions.
2. Extended Protection
SQL Server 2008 R2’s Extended Protection feature uses service and channel binding to help prevent authentication relay attacks. Service binding is used to protect against luring attacks while channel binding helps with both luring and spoofing attacks. Since service binding requires more server resources, the protection mode you use should depend in part on the CPU requirements of each.
Extended Protection is available on Windows Server 2008 and Windows 7 natively, though patches are available for download for other operating systems. The feature enhances the protection that already exists when using Windows Authentication.
3. Support for wildcard certificates with SSL encryption
In addition to supporting SSL encryption between the client and server where the SSL certificate is the same fully qualified domain name (FQDN) that the user connects to, support for wildcard certificates are now supported as well. This can help reduce SSL certificate costs and management overhead by allowing a single certificate to be used for the company website, webmail site, SQL Server and more. For instance, if a company needs to encrypt the connections to all SQL Servers, this can be done with a single SSL certificate instead of requiring one for each SQL Server.
4. Merge replication over IIS 7
When using merge replication between locations, you now have the option of using Internet Information Services (IIS) 7 as the listener for SQL Server. IIS 7 is configured on the publisher, and the subscribers connect to the IIS 7 installation and data replication. This is all protected by SSL encryption.
This great new feature allows for merge replication between two SQL Servers without exposing either to the public Internet. There is also no need for a VPN connection between the two sites. This makes it much easier for data to be replicated between two different companies without exposing either company’s network infrastructure to the other or the public Internet.
5. Collaboration and reuse within SQL Server Reporting Services (SSRS)
SQL Server Reporting Services for R2 supports several new features to enable and enhance the existing collaboration and component reuse within the SSRS environment. This includes new report parts and shared datasets. The ability to reuse these components will greatly decrease the amount of time needed to create new reports. It should also increase the uniformity of reports throughout the enterprise as they can now use the exact same module.
6. New data source types
The SQL Server 2008 R2 release of SSRS supports three new data source types — SQL Azure,Parallel Data Warehouse and a Microsoft SharePoint list. These new data sources let companies host their applications and databases in the Azure cloud while using their local SSRS infrastructure to run reports, implement report scheduling and automate file saving to network shares.
Similarly, support for the new SQL Server 2008 R2 Parallel Data Warehouse edition should allow Reporting Services to display reports from large enterprise data warehouses.
7. BIDS is backward compatible with SSRS 2008
The Business Intelligence Development Studio (BIDS) for SSRS 2008 R2 can be made backward compatible with SSRS 2008 by setting the version within the Report Server project properties. This lets you use a single development environment instead of having to maintain a separate installation, as is the case with SQL Server 2005 and 2008. This backward compatibility allows companies to more easily deploy SSRS 2008 R2 as a separate environment without having to upgrade all the existing reports at the time of deployment.
As you can see, there’s a lot more to R2 than PowerPivot and the other major features currently getting most of the marketing love. Hopefully this has opened your eyes to theother great enhancements that have been made for SQL Server 2008 R2.
Windows Management and Scripting 