Extract numbers from a string

You see SQL string extraction techniques all over the web.  Here are a couple flavors I like.  Of course before you can use these techniques, you must first have a flawed database design.  That makes their use tantamount to an admission of failure (I know... we all inherited the poorly designed systems).

-- extract numbers from a string

-- method one returns ALL of the digits

-- method two returns the first sequence of digits

 

-- i didn't have a numbers table handy when I wrote method one, so I derived one

-- by cross joining sysobjects to itself and using row_number() over(order by so.name)

-- of course this means it won't work on sql server 2000 or earlier

 

DECLARE @inputstring VARCHAR(200), @outputstring VARCHAR(200)

SET @inputstring = 'seven million 312 thousand 987'

 

 

--- get all digits from the input string ('555-1212' returns '5551212')

SELECT

   @outputstring = 

      COALESCE(@outputstring + SUBSTRING(@inputstring, src.position, 1),

               SUBSTRING(@inputstring, src.position, 1) )

FROM

   (SELECT ROW_NUMBER() OVER(ORDER BY so.NAME) AS position

    FROM sysobjects AS so CROSS JOIN sysobjects AS so2) AS src

WHERE SUBSTRING(@inputstring, src.position, 1) LIKE '[0-9]'

 

SELECT @outputstring

 

 

-- get the first group of digits from the input string ('555-1212' returns '555')

SELECT

-- let's get our string's first number group

-- and ignore all the alphabet soup

-- then if we find some more digits

-- we'll drop them like widgets

-- cause we STUFF-ed them all out sans a loop

   NULLIF(

   SUBSTRING(@inputstring,                            

   PATINDEX('%[0-9]%', @inputstring),                  

   PATINDEX('%[^0-9]%',                               

   STUFF(@inputstring, 1,                             

   PATINDEX('%[0-9]%', @inputstring), '') + '-')), '')

---- the limerick is mine but the code belongs to Plamen Ratchev:

---- http://www.eggheadcafe.com/conversation.aspx?messageid=33699815&threadid=33699786

 

Data Explosion

I grabbed total database size numbers for the last year (in GB) from one test server.  Just multiply this by Production, Reporting, Training, Development, and Disaster Recovery: our 240GB just turned into 1.4TB (plus backups!).  Compare that to the 10GB/60GB from the previous year.

We seem to be ahead of the curve as far as the global data explosion goes.

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...