Upgrading to SQL Server 2005

Microsoft Belgium launched an Upgrade to SQL Server 2005 campaign. One of the cornerstones of the campaign is the brand new website www.sqlserverupgrade.be. The website is loaded with links to documents, webcast, hand-on labs and much more.

On December, 6th I will be presenting a TechNet Evening on Upgrading to SQL Server 2005. I will demonstrate different upgrade scenarios and show you the pros and cons of each upgrade path. I will pinpoint potential pitfalls and show you the tools which can help you during the upgrade process.

Full details and free registration can be found here.

As a Sinterklaas present all attendees will receive a copy of the SQL Server Upgrade Resource kit for free.

See you next week.

30 minutes of fun with Bart

Make sure to catch Bart's funcast on Wednesday.

During this 30min demo session we will show and explain you how to create your own blog and wiki site with Sharepoint 2007.

http://www.sharepointblogs.com/bart/archive/2006/11/21/16292.aspx

SQL Server Logon triggers part 2

In April I wrote an article entitled "SQL Server 2005 logon triggers". The article will be published in the forthcomming book "The best of SQL Server Central - Volume 4".

In the article I described how to mimic Oracle logon triggers using event notifications in SQL Server 2005.

That was part one. Now comes part 2. Last week Microsoft released the CTP of SQL Server 2005 SP2. Here's a list of all new features in SP2. And one of them is logon triggers. Great, finally we have them.

So can we do everything with logon triggers? No, there are some differences with the method I descibed in April using event notifications.

• Logon triggers are part of the "logon transaction", this means you can rollback or cancel a logon. Event notifications go on the queue, after the logon. So, no easy way to cancel a logon.
• triggers are raised synchronously with events, whereas event notifications are asynchronous.
• Logon triggers only fire if authentication succeeds, event notifications can also capture failed logons and logouts.
  

Anyway it is a nice new feature. I rewrote my logon trigger and it's now a lot easier to understand (keeping in mind the differences mentioned above). You can find the script at the end of this post.

Ever wanted to prevent those Excel or Access users from reading your SQL tables directly? Well these 7 lines of codes will do the trick:

CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
 ROLLBACK
END

Here's a complete sample script:

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
-- Frederik Vandeputte - www.vandeputte.org


--TESTED ON SQL 2005 SP2 CTP

USE MASTER
GO
--Clean up old logging db
IF DB_ID ('Logging_demoSP2') IS NOT NULL
BEGIN
ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Logging_demoSP2
END
GO
--Create Logging DB and table
CREATE DATABASE Logging_demoSP2
GO

USE Logging_demoSP2

CREATE TABLE Logging (

LoggingID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
FullLog XML
)
GO


--The LOGON Trigger
CREATE TRIGGER logon_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN

DECLARE @message_body XML

SET @message_body = eventdata()

INSERT INTO Logging_demoSP2.dbo.Logging (
EventTime,
EventType,
LoginName,
HostName,
AppName,
FullLog )
VALUES
(
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/ClientHost/text()') AS VARCHAR(100)),
APP_NAME(),
@message_body)


END


GO

--Open demo connections

select * from logging


--Prevent acces from Excel
CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
 ROLLBACK
END


--Try to connect from Excel

--Clean DB
USE MASTER
GO
ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Logging_demoSP2
DROP TRIGGER logon_trigger ON ALL SERVER
DROP TRIGGER logon_trigger_not_from_excel ON ALL SERVER

biTunes - The Source code

As promised last week on Teched... the source code for biTunes.

This zip files contains all the scripts, database backup, and BIDS solution for running your own biTunes BI solution.

Scripts were created with SQL Server 2005 SP1.

I included a small sample iTunes music library file. Copy your own iTunes Music Library.xml file to c:\biTunes\iTunes Music Library.xml to analyze your own MP3 collection.

Have fun!!

(Update, June 29h 2007 - new download location)