SQL 2008 Extended Events Manager Build 1.0.1.38

I uploaded a newer version of the app I have been working on tonight.  It now has the ability to create/edit/drop Event Sessions by script.

Extended Events Manager - Build 1.0.0.12 (Released)

For a few weeks I have been focused on Extended Events and how they work in SQL 2008.  One of things missing for the Extended Events is UI support.  In fact there isn't even a SMO namespace for Extended Events as of the RTM of SQL 2008.  This makes using Extended Events fairly complex, and requires that you have a firm understanding of the architecture and Metadata for the packages available.

To simplify this, I have written a C#.NET winforms application that can be used to help manage/explore extended events in SQL 2008.  I posted the source as well as compiled binaries for the first release on the MSDN code gallery as an opensource project.  I am sure that the code is not in keeping with design patterns or best practices, but I am not a C# developer, I am a DBA.  I welcome any comments or advice on this:

http://code.msdn.microsoft.com/ExtendedEventManager/

SQL 2008 SSMS - What happened to F8 = Open Object Explorer

A online friend and fellow moderator from the forums, Deepak Rangarajan, asked a good question this morning in a chat that is worthy of blogging about.  In SQL Management Studio 2005 you could hit the F8 key as a shortcut to bring up the Object Explorer.  I personally didn't know this until today, but out of the box SSMS 2008 doesn't do this.  Since I started in SQL 2000 using the old Query Analyzer, I know the old hot keys for doing things like Ctl+Shft+C will comment out the current highlighted code block, and Ctl+Shft+R will uncomment it.  This was not available with the default keyboard configuration in SSMS 2005, but you can change the Keyboard to the SQL 2000 settings in Tools->Options and it will work as it did in Query Analyzer.  Naturally this was one of the first changes I made in SSMS 2008 when I installed it, but this also brings the F8 shortcut functionality back. 

SQL 2008 Filestream and Attach Database

If when you upgrade to SQL Server 2008 you decide to use the Filestream feature, you can not detach a database, move the files and attach the database back to SQL Server with as much ease as you previously used to do.  In order to move the filestream binaries, and the database data and log files to a new location, you have to use the CREATE DATABASE command and specify the FOR ATTACH option:

CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = N'D:\Srvapps\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = N'D:\Srvapps\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = N'D:\Srvapps\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\archlog1.ldf')
GO

If you create the above database, specifying the appropriate paths for your environment, and then detach the database from the SQL Instance, you can move the files, so the mdf, ldf, and filestream1 folder will be moved to the D:\Data\ directory.  To attach the database back from this new path, you can not use SSMS.  It is not aware of the Filestream filegroup, so you have to issue a DDL CREATE DATABASE command:

USE [master]
GO
CREATE DATABASE [Archive] ON 
( FILENAME = N'D:\Data\archdat1.mdf' ),
( FILENAME = N'D:\Data\archlog1.ldf' ),
( FILENAME = N'D:\Data\filestream1' )
 FOR ATTACH
GO

This will attach the database and update the system catalogs to reflect the new file locations properly.

Suggestion for Extended Events

As you can probably tell, I have been completely focused on Extended Events and learning/documenting how to use them for the last few weeks.  One thing I noticed is that the new Audit functionality in SQL 2008 actually runs on the Extended Events Engine.  This morning a friend chatted me by IM and asked about sp_rename and how the Audit catches it.  It is actually picked up by the Audit as an ALTER of the object.  However, they also needed to know the HostName that originated the request, which is not available in the Audit.  This is because it is also not available as an Action in Extended Events.  This would be a valuable piece of information to have and I have filed this as a suggestion with the SQL Server connect site:

SQL 2008 Extended Events Addition

There is however, a workaround that I provided my friend.  You can create a DDL LOGON trigger that uses the session_id to get the HostName if it exists from the sys.dm_exec_sessions DMV and store it to an Audit table.  Then you can correlate the Audit Event Time, and session_id with the Audit tables Session_id and event time for the Logon to know what HostName was logged onto the Session_ID.  Kind of a hack work around but it will get the job done.

Extended Events and Errors in the BOL

I have been working since the RTM release of SQL Server 2008 with the Extended Events, which are a powerful tool to assist Administrators in troubleshooting problems.  That is, if you can actually get an understanding of how to use them from the little information that actually exists currently (not to worry, I am doing a lot of heavy documentation as I go, and will publish it in the coming weeks).  To compound the issue, the Books Online haven't been maintained from CTP to CTP or even through the RC0 to RTM, and contain numerous errors in them.  I filed a few connect items already:

Errors in the BOL for SQL 2008 DMV sys.dm_xe_map_values

Errors in the BOL for SQL 2008 DMV sys.dm_xe_objects

I goofed on the title for the second one, but I can't figure out how to update it at this point.  I will keep this post updated with other bugs I file as I find them.

How to tell if you have Instant File Initialization Enabled

I subscribe to a number of great blogs, and Paul Randal's is one of my favorites.  As a previous member of the SQL Development team at Microsoft, and the writer of such wonderful tools as CHECKDB, his knowledge of the storage engine is amazing at times.  I don't really like to cross post blogs like this usually, but Paul has a great post for validating that you have Instant File Initialization Enabled:

How to tell if you have instant initialization enabled?

SQL 2008 Intellisense, Only for SQL 2008

I am playing with my RTM install of SQL 2008, the first server at work to be on the new version, and I realize that Intellisense isn't working.  Actually it is, just not for any server connection except the SQL 2008 one.  It seems as though Microsoft doesn't care based on the number of comments on the Connect site for numerous postings of this by the community.  The claim is that they are working on a new feature set, but with the ability of other tools like SQLPrompt to work for all versions of SQL it shouldn't seem far fetched to expect the same from an IDE that does intellisense for every other language it works with.

I don't know that it will matter much since it is closed, but leave a feedback on this item if you care:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=341872

SQL 2008 RTM

Finally, it is here.  SQL 2008 was made RTM today:

http://www.microsoft.com/Presspass/press/2008/aug08/08-06SQLServer2008PR.mspx

I am downloading the MSDN subscriber ISO right now to see what changes, if any, were made in the final cut.