Regional Director Program – From Strength to Strength

The RDs (Microsoft Regional Directors) are one of the most amazing groups of people I deal with and I'm honoured and humbled to be a member of the group. There is rare talent amongst the group. Every day, I find myself amazed by both the breadth and depth of knowledge. I also love the off-the-wall humour.

Our lead Kevin Schuler does a great job of looking after our interests and of herding the cats. I was really pleased to receive a global impact award from Kevin today, covering 2008. It looks excellent and I'm pleased to see that it features "The Region". If you haven't looked into "The Region", I'd suggest doing so. I don't have time to read all the blogs by all the Regional Directors. I wish I could read them all but "The Region" is the next best thing. It's a moderated feed of the highest quality posts made by Regional Directors. You'll find the main feed here: http://www.theregion.com/ShowRSS.aspx.

Database Snapshot Performance Whitepaper

Our colleague Ron Talmage is a writing machine! There's another new whitepaper with his name on it along with Sanjay Mishra from Microsoft.

It covers the performance of database snapshots under I/O intensive workloads. You'll find it here: http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/DBSnapshotPerf.docx

Great work Ron and Mishra!

Microsoft retail stores soon?

This press pass article http://www.microsoft.com/presspass/press/2009/feb09/02-12CVPRetailStoresPR.mspx

makes it look pretty clear that Microsoft Retail Stores are on the way. David Porter has been appointed to head up this initiative. He's been at DreamWorks for the last year or two but was with Wal-Mart for 25 years prior. That should bring some interesting retail experience into the company.

PerformancePoint -> where to now? -> Find answers in Wellington

There has been quite a bit of confusion now over the future directions for the PerformancePoint product. I'm glad to see a few of the PASS chapters tackling the topic head-on.

Adrian from the Wellington chapter has organised Rob Hawthorne to present a session on this. I wish I was going to be in Wellington that night and could be there. The details are:

Thursday, 19th March 2009 – 6pm (pizza and drinks from 5.30pm)

Intergen

L7 Plunket House, 126 Lambton Quay

(Lifts lock at 17:30. If you're late and there's nobody around to swipe you up, contact Bridget on 021 529 258 or Adrian on 027 435 4050.)

Further information is available at: http://dot.net.nz/Default.aspx?tabid=101

If you have any questions, you can ping Adrian at: wgtnsql@gmail.com.

OT: Way too hot in Melbourne and Clint Eastwood's excellent

Today officially ended up getting to 46.4 in Melbourne. That's 115.5 for those still on Fahrenheit and way, way too hot no matter what you measure it in.

So we headed off to see a movie. Took a while to park. No-one seemed to want to park anywhere that wasn't under cover. No big surprise there.

We ended up seeing "Gran Tourino", the Clint Eastwood movie. He was actor, producer and director. What an excellent piece of work from him. Clint is a grizzly old Korean War veteran who used to work in the Ford plant in Detroit. His world isn't what he'd imagined it would be. His relationship with the Asian neighbours made for a great story. I won't spoil the plot by telling you anything too much about it but we all thoroughly enjoyed it.

OT: Levelator -> Awesome

A while back, I took a look at Levelator (from the Conversations Network) and it was a bit rough around the edges. It's a tool that take an audio conversation like a podcast and evens out the audio levels of the different speakers. This is no easy trick. I normally spend a while on this with every podcast we produce. I've still had comments that sometimes my voice is almost deafening but the guest is a bit quiet. I've found the main problem is that people you interview vary their amplitude enormously at different parts of the interview.

Today, I pulled down the latest version and tried it and have to say I love it. You'll see the effect of it in the latest SQL Down Under show (ie: show 43). The interface for selecting files, etc. is still a bit funky, there don't seem to be good options for naming input and output files and I'm surprised it doesn't deal directly with mp3 files (I converted to WAV via Audacity before using it) but the effect that it produces and the cute screen animations are both excellent.

We're now going to go back and "levelate" all the previous shows. This should improve them quite a bit.

Highly recommended! And even better, free!

Disallow results from triggers?

At a meeting I was at a few months back, it was noted that in a future version of SQL Server, the intention was to disallow results being returned from triggers. That surprised me somewhat.

My main concern with this was about my ability to debug triggers. I often seem to end up working on systems where people have layers of nested triggers. (Perhaps I'm just lucky that way). Usually I have to end up using a combination of PRINT statements and writing values into tables to work out what's going on in the triggers. And given that the action of the triggers may well be rolled-back, writing to a table is often not a simple option if you want the values to still be there later.

So I'm often back to using PRINT statements. I was concerned that these might not work either. As an example, if we set up a table to use for testing:

USE tempdb;

GO

 

CREATE TABLE t

( c1 int IDENTITY(1,1) PRIMARY KEY,

  c2 varchar(20) NULL

);

GO

 

CREATE TRIGGER t_Insert ON t FOR INSERT

AS

  SELECT * FROM inserted;

GO

 

If we now attempt an insert on the table, this is the outcome:

INSERT INTO t (c2) VALUES('Hello'),('There');

GO

 

c1          c2

———– ——-

2           There

1           Hello

If we now enable the option, this is the outcome:

sp_configure 'disallow results from triggers',1;

GO

RECONFIGURE;

GO

INSERT INTO t (c2) VALUES('Hello'),('There');

GO

Msg 524, Level 16, State 1, Procedure t_Insert, Line 4

A trigger returned a resultset and the server option 'disallow results from triggers' is true.

If we try using PRINT, instead of SELECT in the trigger, we can see the result:

ALTER TRIGGER t_Insert ON t FOR INSERT

AS

  PRINT 'Hello';

GO

INSERT INTO t (c2) VALUES('Hello'),('There');

GO

 

Hello

 

(2 row(s) affected)

Note that it still works. What is not allowed is returning rowsets from the trigger. That's probably fair enough as resultsets coming back from triggers can cause unexpected results in client applications.

In Books Online, it is recommended that you set this value to 1 as the ability to return results from triggers will likely be removed from the product soon. If you strongly oppose that though, now would be a really good time to make it known to the product group via http://connect.microsoft.com.

Much ado about logins and SIDs

Some time back I posted about Logins and SIDs and I’ve had a number of people ask me for examples. So I’ve updated the post here.

A very common issue that's raised in the newsgroups relates to SQL Server logins that need to be moved between servers. When you recreate a SQL Server login (ie: not a Windows one), by default you get a new security ID (SID), even though you have the same user name and password.

The problem then comes when you restore a database from another server. You can't access it. If you try to create the user entry in the database, it says it already exists and fails. But if you try to list the users in the database, it also doesn't show it.

Let’s try an example: First I’ll create a database and a login and add the user to the database:

CREATE DATABASE LoginTest;

GO

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd';

GO

USE LoginTest;

GO

CREATE USER GregTest FROM LOGIN GregTest;

GO

USE master;

GO

 

Next we’ll detach the database and drop and recreate the login:

EXEC sp_detach_db 'LoginTest';

GO

DROP LOGIN GregTest;

GO

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd';

GO

 

If we reattach the database, we now have the situation where the new login has a different SID to the one that the user has in the database, even though the user has the same name. This is very much like what happens when you restore a database on another server and recreate the login there:

CREATE DATABASE LoginTest

ON (FILENAME = 'C:\SQLData\Data\LoginTest.mdf')

FOR ATTACH;

GO

 

If we try to use the login to access the database, it won’t work. If we try to create the login, it won’t work either:

USE LoginTest;

GO

CREATE USER GregTest FOR LOGIN GregTest;

GO

 

Msg 15023, Level 16, State 1, Line 1

User, group, or role 'GregTest' already exists in the current database.

 

The standard answer for this has been to use sp_change_users_login. It has an option to list any mismatched logins and database users ie: those with the same names but different SIDs.

 

EXEC sp_change_users_login 'Report';

GO

 

UserName    UserSID

———————————————-

GregTest    0x5D5F9089AFE1D4428106DE1B52BE0DFC

It then has an option to "fix" it. The way it fixes it is to update the SID in the database user to match the login:

EXEC sp_change_users_login 'Update_One', 'GregTest', 'GregTest';

GO

 

In Service Pack 2 of SQL Server 2005, new syntax was introduced to deal with this:

ALTER USER GregTest WITH LOGIN = GregTest;

GO

 

The problem I see with this all is that it still just temporarily fixes the problem or at worst, propagates it to other servers. It's not the database SID that needs fixing, it's the Login's SID. If the Login's SID was correct, there wouldn't be a problem with copying the databases around. The most common scenario I see is the following:

1. A database is restored from another server (or a reinstalled server).

2. The logins that use the database need to be recreated.

I've gotten around this problem in the past by specifying the SID value when creating the login in TSQL. It is an optional parameter. If you provide the same value as on the other server, you don't have the problem. For example, instead of executing sp_change_users_login or ALTER USER above, we could have done the following:

USE LoginTest;

GO

SELECT sid FROM sysusers WHERE name = 'GregTest';

GO

 

sid

———————————–

0x5D5F9089AFE1D4428106DE1B52BE0DFC

 

(1 row(s) affected)

What we could then have done was:

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd',

  SID = 0x5D5F9089AFE1D4428106DE1B52BE0DFC;

GO

 

The upside of this is that it’s a permanent fix. Next time you restore the database, you won’t have to fix it again.

Because this is such a common issue, I’d really like to see it directly supported in T-SQL, such as:

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd',

  SID FROM DATABASE LoginTest;

GO

 

This would avoid the problem in the first place and then give you a database you could copy around as needed. If you think that sounds interesting, vote for it here:

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