SQL Server Customer Advisory Team - SQL Server Best Practices

With all the new features in SQL 2008 R2, here are the major ones getting all the press:

There is so much written on the ones above, I wanted to concentrate on talking about other new features in SQL 2008 R2. So, in no particular order:

1. SMB support – SMB stands for Server Message Block and this protocol is now officially supported by SQL Server 2008 R2 and beyond. This improvement has formalized the support status of placing SQL database files on SMB network file shares. From Kevin Farlee, the owner of this feature in SQL Server: “This presents a better-together story with the work that Windows has done in Windows7/Server 2008 R2 to make the Windows SMB stack far more performant and resilient than in the past. It is also a recognition that with the increasing acceptance of iSCSI, customers are viewing Ethernet as a viable way to connect to their storage. Finally, it gives customers in consolidation environments a very simple to manage method for moving databases between servers without investing in a large SAN infrastructure.”

2. Increased Performance – there are very nice performance improvements, especially with the combination of Windows 2008 R2 and SQL 2008 R2. The actual TPC-E measurements on have been audited and published.

3. SYSPREP – Finally! We can now create Sysprep versions of SQL Server environments, starting with SQL Server 2008 R2, but only for the relational engine. My favorite thing about this piece is that it even works with HyperV images containing SQL Server 2008 R2.

4. Report Builder 3.0 and Reporting Services – Too many great new features to talk about in a blog and the development team already has a great blog. But my favorite is the Report Part feature where you can take an existing report and designate report items and data regions to save and reuse in other reports. This can amount to a huge time savings for developing new reports. Other customers tell me they like the improved Sharepoint integration and the performance improvements in Sharepoint. But that is not all, there is Bing map support, spark lines, and shared data sets.

5. Master Data Services – for data consistency across heterogeneous applications. BOL link.

6. SSIS - Bulk Inserts with ADO.NET provider are now possible, which is extremely nice because it used to do it a row at a time. Now, if you check the box to “Use Bulk Insert when possible” then you can see vastly improved performance when it kicks in.

7. Setup – integrated Sharepoint mode setup is vastly improved for both Reporting Services and Analysis Services. See the link for Powerpivot for Sharepoint to get the instructions.

8. Excel 2010 – new additions for databases: slicers, data cleansing, AJAX data feeds, Odata feeds and named set improvements. To create a Named Set in Excel, once you’ve created a PivotTable against an OLAP source go to the Options tab under PivotTable Tools, and select “Fields, Items, & Sets” à “Manage Sets” à “New…” à “Create Set Using MDX…”. Another of my new favorites within PowerPivot in Excel is the new Data Cleansing ribbon. This allows the users to do their own clean up, which will be essential when they combine data from disparate sources. And you can also get an OData feed from a Reporting Services report.

9. Database Compression - now supports Unicode. If you have Unicode date types, like nchar and nvarchar, but the data contained within is normally single byte character sets, you will see significant space savings.

10. PHP 5 Driver – Version 1.1 of the PHP 5 driver has a list of new capabilities, allowing access to SQL Server 2005 and SQL Server 2008.

New and changed Editions (more details and pricing)

· Data Center Edition – needed for machine with more than 8 physical CPU sockets plus other improvements needed for the top SQL Server projects.

· Parallel Data Warehouse Edition – Massively Parallel Processing (MPP) Edition of SQL Server targeted at data warehouses in the 10’s to 100’s of terabytes. It is an appliance where you order the hardware and software together and it comes preinstalled and preconfigured. The minimum installation is one rack so no, you cannot install it on your laptop to play with it.

· Standard Edition – now has the capability to do backup compression.

Want to test SQL 2008 R2? Get the free download.

Want to look at some samples for the new features? Go here.

Just need to read Books On Line for SQL 2008R2?

Since I didn’t cover everything, if you want to find something specific, go to the main SQL Server 2008 R2 page.

Author: Kevin Cox

Reviewers: Sanjay Mishra, Carl Rabeler, Murshed Zahman, Peter Scharlock, Mike Weiner, Alexei Khalyako

Technical Notes

Top 10 Hidden Gems in SQL 2008 R2