Edition Differences in SQL 2008

Based on the current whitepaper from Microsoft it is extremely important to pay attention to which version of SQL 2008 you pick. A lot of features are once again Enterprise only, to include multiple instances, which previously was available in SQL 2005 Standard. I am not sure why this would be moved up to Enterprise Edition, but will be asking some experts and making a post on the 2008 Forums to find out more information.

CLR Approved Assemblies

A little known fact about using CLR integration in SQL Server is that not all of the .NET Assemblies are available for use.

Supported .NET Framework Libraries

This means that in order to use components of the .NET Framework that are not supported, you have to first create them as a, generally UNSAFE, Assembly in the database. This however can lead to support issues with Microsoft Support, according to their KB Article

Bob Beauchemin covered this in a recent blog after upgrading .NET on a SQL 2005 Server. If you choose to reference an unsupported Assembly in your CLR code, your Assembly will cease to function and execution will result in an exception with Message 622 and an error ending with:

Assembly in host store has a different signature than assembly in GAC.

To correct, you must drop your assembly as well as the unsupported assemblies, and add them back so the GAC is refreshed with the newer version.

SQL 2008 Changes to Minimal Logging

I like to keep up with certain blogs from time to time to stay in tune with new features and changes in SQL Server, and one of those is the Microsoft SQL Server Storage Engine Blog. Sunil Agarwal finished a series this month on changes coming post CTP6 in SQL 2008 regarding Minimally Logged Transactions. It is a three part series that will change how we do things in SQL Server if it makes it into the final release.

Part 1
Part 2
Part 3

A Day with the Experts

Today I spent the day at a seminar put on by Quest where we were able to spend time learning performance monitoring and tuning from SQL Server Experts and MVP's Kevin Kline and Hilary Cotter. Quest also provided a demonstration of SQL Spotlight, a tool for monitoring and managing SQL Server Performance. If you've never seen this product, you should contact Quest for a demonstration.

Kevin and Hilary also held a 2 hr questions and answers session on the coming release of SQL 2008. Of particular interest to me was the lack of knowledge surrounding Powershell integration in the room. Also lacking heavily was knowledge about the new policy Management in SQL 2008. Both of these are newer features, and only those testing the CTP's or following them closely would be aware.

I have been playing with the DMF/Policy management since installing my Feb CTP and plan to post more information on this in the days/weeks to come. From there I guess I am going to move on to Powershell and play around with it.

SQL 2008 Upgrade In Place

While working on testing the upgrade path from SQL 2005 to SQL 2008, I came across the following mesage when using the Upgrade Analyzer:


Message: SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

This is very odd to me because the databases being upgrade have none of the above items in it. I did some searching online, and others seem to say that these messages are slightly vague. I did find a post on the MSDN Forums:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=659712&SiteID=1

but in the end I figured out that by resetting the ARITHABORT to on for all my databases would allow the Upgrade Advisor to pass this test.

sp_msforeachdb 'ALTER DATABASE [?] SET ARITHABORT ON WITH NO_WAIT'

Another thing I found with the Upgrade Advisor is it doesn't catch if you are not on SP2 or not, which is required in SQL 2005 to upgrade in place to SQL 2008. Once I fixed these two little items, everything else in the upgrade went smooth.

CLR Safety Issues After Database Restore to New Server

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/03/06/clr-safety-issues-after-database-restore-to-new-server.aspx

SQL Database Management for Sharepoint

If you have a database server that hosts Sharepoint databases, and if you run Sharepoint or MOSS you do, I hope you have checked out the database maintenance whitepaper that Bill Baer released last week.

It provides a stored procedure that can be run to update your indexing and statistics while maintaining support from Microsoft, and not breaking your search services. For further information about previous problems see:

Information about the Maintenance Plan Wizard in SQL Server 2005 and about tasks that administrators can perform against SharePoint Databases

Understanding 'login failed' (Error 18456) errors in SQL Server 2005

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/03/04/understanding-login-failed-error-18456-errors-in-sql-server-2005.aspx