Orphaned Distributed Transactions

----- We occasionally get orphaned transactions when unit tests fail.
----- This will kill the orphaned transactions for a specific database,
-----      or all on the server without having to restart the Distributed
-----      Transaction Coordinator (which could cause successfully executing
-----      unit tests within a different database to fail).

DECLARE @DBName sysname
--SET @DBName = 'Bob'
 
DECLARE @KILL varchar(1024)
DECLARE @currentUOW uniqueidentifier
SET @currentUOW = (SELECT TOP 1 req_transactionUOW
                   FROM sys.syslockinfo
                   WHERE ( rsc_dbid = db_id(@DBName) OR @DBName IS NULL)
                          AND req_spid = -2
                          AND rsc_objid <> 0 
                   ORDER BY req_transactionUOW)
 
WHILE @currentUOW IS NOT NULL BEGIN
   SET @KILL = 'KILL ''' + cast(@currentUOW AS varchar(40)) + ''';'
   PRINT @KILL
   EXEC ( @KILL )
   SET @currentUOW = (SELECT TOP 1 req_transactionUOW
                      FROM sys.syslockinfo
                      WHERE (    rsc_dbid = db_id(@DBName) OR @DBName IS NULL)
                             AND req_spid = -2
                             AND rsc_objid <> 0
                             AND req_transactionUOW > @currentUOW
                      ORDER BY req_transactionUOW)
END
 

SQL Server Per-Processor Licensing

Setting processor affinity does not allow you to reduce the number of processors for which you are licensed. Any processor to which the OS has access must be licensed [1].

Creating a virtual machine with fewer virtual processors than the physical host contains does allow you to reduce the number of processors for which you are licensed. You only have to have licenses for the number of virtual processors [2].

[1] If you run the software in a physical OS environment, you need a license for each physical processor used by the physical OS environment.
[2] If you run the software in virtual OS environments, you need a license for each virtual processor used by those virtual OS environments on a particular server—whether the total number of virtual processors is lesser or greater than the number of physical number of processors in that server.

These two references are both from page 6 from the following document:
http://download.microsoft.com/download/6/8/9/68964284-864d-4a6d-aed9-f2c1f8f23e14/virtualization_whitepaper.doc

MS Forum: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884303&SiteID=1
MS Licensing FAQ: http://www.microsoft.com/sql/howtobuy/faq.mspx#EZB

DBA Role

I recently read an article by Scott Ambler called “Where did all the positions go?” It was about how traditional software roles fit into an agile development paradigm. I thought I'd make some observations as to how I think this relates to our organization's current state of database development.

One thing I've noticed since we have adopted the agile development model is that I've been doing a lot less DBA work. Sure, I still do some modeling, write some SQL, and put the occasional database together but that is what I would consider database development; definitely not administration. I have often felt that, as a DBA, I'm not able to make a full contribution to my agile team.

Another thing I've heard from some of my database peers is that the iterations tend to be front loaded with database work because much of the development is dependent upon the completion of the database portion. Once the database work is done, development of the application can continue and the database developer tends to settle down to an application support role with minor fixes. Unless your project is a database only project, there is not typically enough database work to fill multiple complete iterations.

Scott's original positions question, as it pertains to our group, might be asked as “How does a traditional DBA fit on an agile development team?” The short answer is “s/he doesn't.” Does this sound harsh? Let's take a look at the longer answer.

Although the traditional DBA doesn't have a fully contributing role on an agile team (neither does the traditional programmer, business analyst, or project leader for that matter), Scott refers to something he calls a “generalizing specialist.” As traditional database developers, we obviously cover the “specialist” portion of this role but, with just the specialist portion covered, we are still only partial contributors to the project team.

To become fully contributing members of our agile teams, we are learning to expand our contributions to some non-database areas as project needs dictate. We are developing the “generalizing” to go with our “specialist.” I've seen some of our formerly traditional DBAs doing things such as writing VB.NET code, running regression tests, working with VB and C# CodeSmith templates, and creating and executing QTP scripts. These are definitely skills you don't expect to develop in a traditional DBA role!

So... about my previous lack of contribution as a DBA; job title aside, I'm now fully contributing to my team as a general agile developer with a database specialty.

Securing the SA Account

We've all heard of the 'sa' account, the built-in SQL authentication (user name / password combo) administrator account: that terrible thing that is the root of all vulnerabilities. Okay, maybe not all vulnerabilities but, enough SQL Server vulnerabilities that you occasionally hear about it.

Microsoft recommends using Windows Authentication, not mixed mode. SQL Server Authentication mode has been included in the last two versions of SQL Server allegedly for “backward compatibility” [1]. To my knowledge, it has not been deprecated so, it probably won't be going away any time soon.

With this in mind, what can we do to mitigate the risk posed by this account?

Even if you do use windows authentication, be sure to set a strong SA password (install as mixed mode, so you can set the SA password, then come back and change to windows mode). The authentication mode can be changed to mixed mode by changing a registry key and restarting the service [2]. If this happens, and you have not set the SA password, you now have an available SA login with no password (a great reason to lock down the registry, too!)

Microsoft has two definitions of a “strong” password. One states that a strong password should be at least seven characters in length [3]. The other states that a strong password should be at least six characters in length [4].

I've played around a little bit with NGSSQLCrack [5], and its authors are relatively certain that they can brute force any SQL Server password under 8 or 9 characters in 15 hours or less. Since we're not going to be using this account for general login purposes (right?), we shouldn't be stingy with the characters. There are 128 available, let's use a goodly portion of them. What's wrong with a 50 or 100 character password with mixed case, numerals, and special characters? If we weren't meant to use all those keys, they never would have been put on the keyboard. Remember, it's only for emergencies anyway; convenience is not an issue here. Maybe we could use something like the following:

ALTER LOGIN sa WITH PASSWORD = 'Ple@se_Don''t_C0mprom1se_My_SQL_S3rv3r_Bec@us3_It_Is_V3ry_Ne@r_And_D3ar_To_My_H3@rt'

What if you must use mixed mode authentication, due to organizational inertia, legacy / 3^rd party applications, or some other reason?

SQL Server 2005 provides a few useful features to help mitigate the risk associated with the SA account. It allows us to enforce password policy (length/complexity), to disable an account (including the SA account), and it also allows us to rename the SA account.

Password policy is a great tool to make sure people aren't creating SQL logins with weak or missing passwords. It also allows for locking accounts after a set number of failed logins. This helps prevent the password cracking tools from doing their job. I don't believe, however, that you can lock the SA account out in this manner.

Disabling the SA account is a good way to prevent its use, as well. If it's not enabled, we can't try to use or abuse it. Of course, this prevents us from using the SA account in an emergency; a capability your organization may require. Just for fun, let's disable the SA account:

ALTER LOGIN sa DISABLE

We can also now rename the SA account. I've seen organizations rename domain and local windows administrator accounts to help obfuscate their purpose. We can now replicate this behavior in SQL Server. Unfortunately, these domain and windows accounts all have an RID of 500 so, the bad guy windows hacker knows right where to go to break them. SQL Server has a similar situation. The product allows you to rename the account but, the SID is always 0x01. Does this give the bad guy SQL hacker a starting place for breaking the SA account? I suppose it might. After reading Jesper Johansson's article on securing the local and domain administrator accounts [6], I begin to wonder about the effectiveness of renaming the SA account. At any rate, until it's uselessness is confirmed, I think it remains a reasonable step to take. Let's give it a try:

ALTER LOGIN sa WITH NAME = alterEgo

If we were able to avoid mixed mode authentication, put a strong password on the SA account, and to secure our registry, we've gone a long ways towards keeping the SA account locked up tight.

[1] http://msdn2.microsoft.com/en-us/library/aa176599(SQL.80).aspx
[2] http://support.microsoft.com/kb/285097
[3] http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_6hgz.asp
[4] http://msdn2.microsoft.com/en-us/library/ms143705.aspx
[5] http://www.ngssoftware.com/products/database-security/ngs-sqlcrack.php
[6] http://www.microsoft.com/technet/technetmag/issues/2006/01/SecurityWatch/?topics=y

Legacy OUTER JOINs (*=, =*)

Maybe you've seen the *= and =* LEFT and RIGHT OUTER JOIN operators floating around third party data access code or even (gasp!) Microsoft system generated code. I've never used them but, I have seen them lurking around, on occasion. Although these operators have been valid and supported in SQL Server, they are going away for SQL 2005.

For backwards compatibility, they will still work in databases with a compatibility level = 80 but, for databases with compatibility level = 90, they no longer work. Not that this is very exciting but, to play around with these JOIN operators, we'll need a couple tables:

CREATE TABLE tbl_tst1 (
tst1_pk int NOT NULL identity(1, 1),
tst1_data varchar(50));

CREATE TABLE tbl_tst2 (
tst2_pk int not null identity(1, 1),
tst1_pk int,
tst2_data varchar(50));

We'll also need to load them up with a bit of data:

INSERT tbl_tst1 VALUES ('tst1_data01');
INSERT tbl_tst1 VALUES ('tst1_data02');
INSERT tbl_tst1 VALUES ('tst1_data03');
INSERT tbl_tst1 VALUES ('tst1_data04');
INSERT tbl_tst1 VALUES ('tst1_data05');

INSERT tbl_tst2 VALUES (1, 'tst2_data01');
INSERT tbl_tst2 VALUES (1, 'tst2_data05');
INSERT tbl_tst2 VALUES (2, 'tst2_data01');
INSERT tbl_tst2 VALUES (2, 'tst2_data05');

Now, that we have a bit of useless data, we'll want to select all rows from tbl_tst1 and, any rows from tbl_tst2 that have tst2_data = 'tst2_data05' AND that match our rows from tbl_tst1:

SELECT * FROM tbl_tst1 AS one, tbl_tst2 AS two
WHERE one.tst1_pk *= two.tst1_pk
AND two.tst2_data = 'tst2_data05';

Assuming your compatibility level = 90, the first thing you'll probably notice when you run this query is that you get an error:

Msg 4147, Level 15, State 1, Line 28

The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

Since that is highly inconvenient for our sample code (and for that 3^rd party monitoring application you just installed), we'll need to put our database into compatibility level 80:

EXEC dbo.sp_dbcmptlevel @dbname=N'db_name', @new_cmptlevel=80;

Now, we get the five rows from tst_tbl1 we were expecting. I was thinking of beating this dead horse with a =* RIGHT OUTER JOIN but, we all get the point. Let's get rid of the carcass:

DROP TABLE tbl_tst1;
DROP TABLE tbl_tst2;

And, since we don't wan't our 3^rd party monitoring tool cluttering up our shiny new SQL Server 2005 database with legacy JOINs, let's shut the door on them:

EXEC dbo.sp_dbcmptlevel @dbname=N'db_name', @new_cmptlevel=90;

Since these OUTER JOIN operators have been deprecated, you can count on them to not work in some future version of SQL Server, regardless of whether you have set your compatibility level below 90. Now, if we could only get rid of the legacy INNER JOIN syntax...

Database Mail

I have a confession to make. Beyond sending a couple of test emails, I've never used xp_sendmail to send email from my database servers. Because I was never comfortable with the idea of a MAPI client on the server, and hated the bother of setting it up, I've always used sp_OA* to send email via CDO (hold the derision, please).

The new mail procedure, sp_send_dbmail, however, works a bit differently. Although we no longer need a MAPI profile on the server, we still have a bit of setup to do. Database mail is disabled by default. The first thing we'll need to do is to enable it. Since it's an advanced option, we'll need to turn on advanced options, first:

EXEC sp_configure 'show advanced options', '1';
RECONFIGURE WITH OVERRIDE;

Even though sp_send_dbmail isn't an extended procedure like its predecessor, it still uses extended procedures to handle the mail. This is a bit of a case of “do as we say, not as we do,” since extended procedures are deprecated and Microsoft says we should no longer use them for new development (email might make a good CLR procedure candidate, assuming you don't have to run UNSAFE to access the email server). At any rate, let's enable mail extended procedures:

EXEC sp_configure 'Database Mail XPs', '1';
RECONFIGURE WITH OVERRIDE;

Despite being liberated from the tyranny of the MAPI profile, we still need a mail profile in the database. Even though we have to create a profile, it doesn't require any extra software; it's all in the database and is a convenient way to specify our email account settings as the sender of a message. Books-Online was kind enough to provide a code sample:

EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Database Mail Profile',
@description = 'Profile with which to send database mail';

Of course, with any legitimate email message, you need an mail account from which to send it. We get to specify several properties for our account. The account name, email address, and email server are some of the more pertinent properties. Don't forget to set your mail server to allow your database server to relay mail:

EXEC msdb.dbo.sysmail_add_account_sp
@account_name = 'Database Mail Account',
@description = 'EMail account for sending database mail',
@email_address = 'mymail@domain',
@replyto_address = 'myemail@domain',
@display_name = 'Inbox Assassin',
@mailserver_name = 'smtp.mymailserver.domain';

Now it's getting a bit tedious. Just one more step and we can actually send an email message. I mentioned before that the profile was a convenient way to specify our email setting for a message. This next procedure associates an email account with a profile:

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Database Mail Profile',
@account_name = 'Database Mail Account',
@sequence_number = 1;

Assuming we specified a valid email account and smtp server, we should be all set to go. All we have to do to send an email now is to provide our profile, a recipient, a subject, and a message:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Database Mail Profile'
, @recipients = 'myemail@domain'
, @subject = 'My first database mail'
, @body = 'Look, ma, No hands!';

Okay... that was fun. Now we have database mail set up on our server, what's next? Well, we could send out thousands of email messages to our close personal friends whose email addresses we store on the corporate database server, but, let's cleanup, instead! To remove our database mail settings, we pretty much just do our setup steps in revers. First we need to disassociate our email account from our profile:

EXEC msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = 'Database Mail Profile',
@account_name = 'Database Mail Account';

Now, we'll get rid of the email account:

EXEC msdb.dbo.sysmail_delete_account_sp
@account_name = 'Database Mail Account';

and get rid of our profile:

EXEC msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'Database Mail Profile';

Finally, since we want to leave our server just the way we found it, we'll disable the mail extended procedures and turn off advanced options:

EXEC sp_configure 'Database Mail XPs', '0';
RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'show advanced options', '0';
RECONFIGURE WITH OVERRIDE;

With a relatively straightforward setup, and no messing with MAPI or sp_OA*, I like database mail enough that I'll be using it for all of my database email messaging needs (all two of them); despite its reliance on extended procedures.

COLLATE

Well, not strictly SQL 2005 related but, as some of our developers have recently noticed, our database servers are set to be case insensitive. SQL Server, by default, is case insensitive. For example, SELECT * FROM myTable WHERE column = 'six' gives the same results as SELECT * FROM myTable WHERE column = 'SiX'.

So, how do we do a case sensitive SELECT statement for something like a password comparison, when you don't want 'six' = 'SiX'?

SQL Server provides a COLLATE clause to do this. You may have seen this clause when generating creation scripts for tables. Here is a quick example:

CREATE TABLE [dbo].[cd_LandCategory] (
[LandCategory_SV] [int] NOT NULL ,
[Desc_TX] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
etc...

The second to last character group in the collation specifies the case sensitivity (CI = Case Insensitive).

It turns out you can also include the COLLATE clause in a query to specify the collation you want your query to use at runtime, completely independent of the DDL used to create the table and store the data (this is similar in function to the CAST function). Using the above table snippet, you may want to write a case sensitive query on the [Desc_TX] column:

SELECT [Desc_TX]
FROM [dbo].[cd_LandCategory]
WHERE [Desc_TX] = 'Old' COLLATE SQL_Latin1_General_CP1_CS_AS

I have changed the CI character group in the collation to CS, providing a case sensitive comparison. One thing to note is that, if I am comparing two columns, I will need to use COLLATE on both sides of the comparison or, I could get a collation error or other unexpected results:

SELECT [Desc_TX]
FROM [dbo].[cd_LandCategory]
WHERE [Desc_TX] COLLATE SQL_Latin1_General_CP1_CS_AS = [SomeOtherColumn_TX] COLLATE SQL_Latin1_General_CP1_CS_AS

The COLLATE clause seems to work the same on both SQL 2000 and SQL 2005.

A readily apparent use for this is comparing passwords in a case-sensitive manner (assuming you didn't create your password column with a case sensitive collation to start with; a common oversight).

Below is a quick code sample you can run on your favorite development server to see how collation, and overriding it with the COLLATE clause, works. It covers case sensitive and insensitive column collations with no COLLATE clause in the query. It also demonstrates overriding the column's collation using the COLLATE clause.

/******** COLLATION EXAMPLE 1.
Case-insensitive column and no COLLATE in where clause ************/

CREATE TABLE #tmp (pwd varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS not null)
INSERT #tmp values ('@ssWord')
INSERT #tmp values ('@ssword')
INSERT #tmp values ('bit3mE')
INSERT #tmp values ('bit3me')

SELECT * FROM #tmp
WHERE pwd = 'bit3me'

DROP TABLE #tmp
GO

/******** COLLATION EXAMPLE 2.
Case-sensitive column and no COLLATE in where clause ************/

CREATE TABLE #tmp (pwd varchar(100) COLLATE SQL_Latin1_General_CP1_CS_AS not null)
INSERT #tmp values ('@ssWord')
INSERT #tmp values ('@ssword')
INSERT #tmp values ('bit3mE')
INSERT #tmp values ('bit3me')

SELECT * FROM #tmp
WHERE pwd = 'bit3me'

DROP TABLE #tmp
GO

/******** COLLATION EXAMPLE 3.
Case-insensitive column and case-sensitive COLLATE in where clause ************/

CREATE TABLE #tmp (pwd varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS not null)
INSERT #tmp values ('@ssWord')
INSERT #tmp values ('@ssword')
INSERT #tmp values ('bit3mE')
INSERT #tmp values ('bit3me')

SELECT * FROM #tmp
WHERE pwd = 'bit3me' COLLATE SQL_Latin1_General_CP1_CS_AS

DROP TABLE #tmp
GO

/******** COLLATION EXAMPLE 4.
Case-sensitive column and case-INsensitive COLLATE in where clause ************/

CREATE TABLE #tmp (pwd varchar(100) COLLATE SQL_Latin1_General_CP1_CS_AS not null)
INSERT #tmp values ('@ssWord')
INSERT #tmp values ('@ssword')
INSERT #tmp values ('bit3mE')
INSERT #tmp values ('bit3me')

SELECT * FROM #tmp
WHERE pwd = 'bit3me' COLLATE SQL_Latin1_General_CP1_CI_AS

DROP TABLE #tmp
GO