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

Canberra SQL Group Next Week

Just had our Canberra mate Jeff Wharton confirm that he's organised a SQL user group meeting while I'm there next week. I'll be doing the session "Answering the queries your users really want to ask" which covers full-text indexes in SQL Server 2008. I find three categories of people in these sessions:

* those that have never used full-text indexing in SQL Server

* those that have old pain associated with trying to use it

* those that are using it and are interested in what's new in SQL Server 2008

Whichever category you fall into, we'll cover off why it should be part of your arsenal from SQL Server 2008 onwards. If you'll be in Canberra, we'd love to see you there. Meeting is Tuesday 10th February at Excom in the city, 6pm start (food from 5:30) and we'll be done about 7pm.

SQL Server 2008 System Views Poster now available for download

I'm really proud of the work my colleagues did on the SQL Server 2008 System Views poster in conjunction with our partners. Ron Talmage led our team. But the best news is that it's now available for download for free:

http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en

 

SQL Server Certification Statistics

Ken Tanner recently reminded me of the link showing current certification status:

http://www.microsoft.com/learning/mcp/certified.mspx

 

The SQL Server related entries from this list are:

 

MCDBA SQL 2000 152086
MCTS SQL 2005 41665
SQL 2005 BI 2600
SQL 2008 Dev 336
SQL 2008 BI 134
MCITP SQL 2005 DBA 6695
SQL 2005 Dev 2925
SQL 2005 BI 1088
SQL 2008 DBA 92
SQL 2008 BI 50
MCM SQL 2005 18
SQL 2008 2

The 18 SQL 2005 Certified Masters folk have been brought across from the earlier SQL Ranger program. The list doesn't include the SQL 2008 DBA TS certification for some reason. I'd be interested in hearing your thoughts on why there was such a big drop in numbers from the 2000 versions to the 2005 versions. I suspect that part of it has to do with the 2000 exams being much easier and that they were a relatively easy option for those pursuing a variety of certifications, including the developer certifications.

New Sydney CBD Lunch-time SQL Server User Group

Victor Isakov has decided to spin up a new SQL Server user group that will be meeting on the 1st Wednesday of each month, at Westpac Place in the city. He describes the group as focussing on best practices, optimisation, design and implementation and is aiming directly at the local corporate market.

Access to Westpac Place will be quickest if you have pre-registered so Victor is asking you to let him know you're coming by sending him an email to victor@sqlserversolutions.com.au.

The first three meetings for 2009 are as follows:
 
Topic: DBA: Best Practices for All DBAs to Follow
Date: 4th February
Description: As the popularity of SQL Server continues to grow, so is the demand for new SQL Server DBAs. Unfortunately, you can't “go to school” to learn how to become a DBA. Most DBAs learn from books, seminars, short classes, and trial and error. This session is designed to encourage a cross-pollination of ideas and best practices between DBAs from different organizations. It includes best practices from the trenches which can only be obtained through experience.
 
Topic: DBD: A Comprehensive Guide to Indexing in SQL Server
Date: 4th March
Description: With the release of SQL Server 2008 the database developer has more choices in their arsenal for optimally indexing database solutions. In this session we will explore the different indexing technologies that are available in SQL Server 2005 and SQL Server 2008, such as included, composite and filtered indexes. We will discuss the primary design considerations and tradeoffs made when implementing an indexing strategy and present more "exotic" scenarios such as how to implement custom hash indexes, how to combine filtered indexes with sparse columns, partitioned indexes and how to implement partitioned views with different underlying table indexes.
 
Topic: ARCH: Reducing Your Storage Cost in SQL Server 2008
Date: 1st April
Description: Are increasing storage costs hurting your bottom line as data sizes keep growing throughout your organization? In this session, we explore how SQL Server can help you lower your storage costs. We start with Microsoft SQL Server 2005 partitioning to transparently migrate older data to lower-cost storage tiers over time. We then discuss new storage cost-saving functionality in Microsoft SQL Server 2008 such as data compression, backup compression, and sparse columns, filtered indexes, file stream which can get you to 50% or more reduction in storage requirements for typical applications. Throughout the session, we share experiences, insights, and best practices from SQL Server customers and Microsoft-internal deployments. Learn how to make good use of these technologies in combination.