Search results matching tags 'sql server' and 'Database Testing'

Quick Database Connectivity Testing

andyleonard — Fri, 26 Aug 2011 16:00:00 GMT

Introduction

Ever find yourself needing to test connectivity, but you’re on a machine with no client tools? It happens to me occasionally. Here’s one trick to help:

UDL files

Right-click on the Windows Desktop, hover over New, and click Text Document:

Rename the file to test.udl:

Because you are changing the extension, you will be prompted:

Click Yes. Double-click the test.udl file to open the Data Link Properties window. Enter (or select) the server name, select integrated security or SQL Login and provide credentials, then select or enter a database name:

Click the Test Connection button to test connectivity:

Conclusion

There you have it – a way to test connectivity without database client tools installed!

:{>

Stress testing UPSERTs

Alexander Kuznetsov — Mon, 15 Dec 2008 01:30:00 GMT

Suppose that you need to implement the following logic:

IF(row exists)

Update the row

ELSE

Insert a new row.

If you already are on 2008, you should use MERGE command, and you don’t need to read this post. Prior to 2008 this logic has to be implemented using UPDATE and INSERT commands. I will stress test several UPSERT implementations in a high concurrency environment. I will demonstrate that under most circumstances the best approach is to serialize your modifications.

Prerequisites

The following tables are used in this post:

CREATE TABLE [dbo].[TwoINTs](

[ID] [int] NOT NULL,

[i1] [int] NOT NULL,

[i2] [int] NOT NULL,

[i3] [int] NOT NULL

);

CREATE TABLE [dbo].[TwoINTsErrorLog](

[ID] [int] NOT NULL,

[i1] [int] NOT NULL,

[i2] [int] NOT NULL,

[error] [int] NOT NULL

);

Implementing optimistic approach

Optimistic approach means that the procedure does not prevent any race conditions up front – it handles errors caused by concurrency as they occur. The following procedure implements optimistic approach and logs all the errors:

CREATE PROCEDURE dbo.SaveTwoINTs(@ID INT, @i1 INT, @i2 INT)

BEGIN

SET NOCOUNT ON;

SET XACT_ABORT OFF;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @ret INT;

SET @ret=0;

BEGIN TRAN;

INSERT INTO dbo.TwoINTs(ID, i1, i2, i3)VALUES(@ID, @i1, @i2, @i1);

SET @ret=@@ERROR;

IF @ret=2601 BEGIN

INSERT INTO [dbo].[TwoINTsErrorLog](ID, i1, i2, error)VALUES(@ID, @i1, @i2, @ret);

UPDATE dbo.TwoINTs SET i1=i1+@i1, i2=i2+@i2 WHERE ID=@ID;

SET @ret = @@ERROR;

IF @ret<>0 BEGIN

INSERT INTO [dbo].[TwoINTsErrorLog](ID, i1, i2, error)VALUES(@ID, @i1, @i2, @ret);

END

END ELSE BEGIN

IF @ret<>0 BEGIN

INSERT INTO [dbo].[TwoINTsErrorLog](ID, i1, i2, error)VALUES(@ID, @i1, @i2, @ret);

END

END;

COMMIT;

RETURN @ret;

END

Stress testing optimistic approach

The following two procedures upsert 50K values each, the first one into column i1, and the second one into column i2:

CREATE PROCEDURE Testers.UpsertLoop1

BEGIN

DECLARE @ID INT, @i1 INT, @i2 INT, @count INT, @ret INT;

SET @count = 0;

WHILE @count<50000 BEGIN

SELECT @ID = COALESCE(MAX(ID),0) + 1 FROM dbo.TwoInts;

EXEC @ret=dbo.SaveTwoINTs @ID, 1, 0;

SET @count = @count + 1;

END;

CREATE PROCEDURE Testers.UpsertLoop2

BEGIN

DECLARE @ID INT, @i1 INT, @i2 INT, @count INT, @ret INT;

SET @count = 0;

WHILE @count<50000 BEGIN

SELECT @ID = COALESCE(MAX(ID),0) + 1 FROM dbo.TwoInts;

EXEC @ret=dbo.SaveTwoINTs @ID, 0, 1;

SET @count = @count + 1;

END;

When I simultaneously ran these two test procedures from two connections, all values were saved, and in more than 90% cases a row was actually updated from both connections:

SELECT SUM(i1), SUM(i2) FROM [dbo].[TwoINTs];

SELECT SUM(i1), SUM(i2) FROM [dbo].[TwoINTs] WHERE i1+i2=2;

----------- -----------

50000 50000

----------- -----------

46030 46030

As you have seen, all values were saved. Was it because the procedure actually works in high concurrency environments, or was it just because the stress test harness was too weak to expose problems?

Does this test harness actually work?

Whenever you do stress testing, you need to demonstrate that your harness actually works. You need to come up with a procedure which work without concurrency, and have your harness expose problems in it. Here is a faulty implementation:

ALTER PROCEDURE dbo.SaveTwoINTs(@ID INT, @i1 INT, @i2 INT)

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @ret INT;

SET @ret=0;

BEGIN TRAN;

UPDATE dbo.TwoINTs SET i1=i1+@i1, i2=i2+@i2 WHERE ID=@ID;

IF @@ROWCOUNT=0 BEGIN

INSERT INTO dbo.TwoINTs(ID, i1, i2, i3)VALUES(@ID, @i1, @i2, @i1);

SET @ret = @@ERROR;

IF @ret<>0 BEGIN

INSERT INTO [dbo].[TwoINTsErrorLog](ID, i1, i2, error)VALUES(@ID, @i1, @i2, @ret)

END

END;

COMMIT;

RETURN @ret;

END

Set up a fresh start before you rerun the loop tests:

TRUNCATE TABLE [dbo].[TwoINTsErrorLog];

TRUNCATE TABLE [dbo].[TwoINTs];

Rerun the loop tests. Clearly the faulty procedure does not work under high concurrency:

SELECT SUM(i1), SUM(i2) FROM [dbo].[TwoINTs];

----------- -----------

46433 39017

If it worked, I would get 50000 in both cases.

Implementing and stress testing pessimistic approach.

Pessimistic approach means that you acquire some kind of exclusive lock before you start modifications and hold it until you commit – once the lock is acquired, this eliminates all concurrency. Let us reuse the technique published by SQL Server MVP Tony Rogerson in his blog:

here

ALTER PROCEDURE dbo.SaveTwoINTs(@ID INT, @i1 INT, @i2 INT)

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @ret INT;

SET @ret=0;

BEGIN TRAN;

DECLARE @result INT, @iteration INT, @resourceName VARCHAR(50);

SELECT @result = -1, @iteration = 0, @resourceName = 'dbo.TwoINTs '+CAST(@ID AS VARCHAR(10));

WHILE @result NOT IN ( 0, 1 ) BEGIN -- Only successful return codes

EXEC @result = sp_getapplock @Resource = @resourceName, @LockMode = 'Exclusive';

SET @iteration = @iteration +1;

IF @iteration>1000 BEGIN

RAISERROR ( 'Lock failed to acquire.', 16, 1 );

ROLLBACK;

RETURN -1;

END;

UPDATE dbo.TwoINTs SET i1=i1+@i1, i2=i2+@i2 WHERE ID=@ID;

IF @@ROWCOUNT=0 BEGIN

INSERT INTO dbo.TwoINTs(ID, i1, i2, i3)VALUES(@ID, @i1, @i2, @i1);

SET @ret = @@ERROR;

IF @ret<>0 BEGIN

INSERT INTO [dbo].[TwoINTsErrorLog](ID, i1, i2, error)VALUES(@ID, @i1, @i2, @ret)

END

END;

COMMIT;

RETURN @ret;

END

If you stress test this procedure in the same way as before, you will see that no saves are lost and no errors are raised.

Performance considerations.

Under high concurrency, the pessimistic approach repeatedly runs noticeably faster. Typical costs on my laptop are as follows:

Pessimistic: CPU – 12600, duration - 35500

Optimistic: CPU – 16400, duration – 50800

Without any concurrency, the optimistic approach repeatedly runs slightly faster. Typical costs on my laptop are as follows:

Pessimistic: CPU – 12200, duration - 29500

Optimistic: CPU – 10900, duration – 26800

Note that for this benchmarking I commented out logging errors into a table, which made very little difference.

Isolation level considerations

As usual in such cases, if you up isolation level to REPEATABLE READ or to SERILIZABLE, you will get a lot of deadlocks.

This post continues my series on defensive database programming.The next post is:

Defensive database programming: SET vs. SELECT.

Here are the previous posts from the series:

Defensive database programming: fun with UPDATE. Defensive database programming: eliminating IF statements. Defensive database programming: fun with changing column widths. Avoid mixing old and new styles of error handling. Defensive database programming: adding ESCAPE clauses. Defensive database programming: qualifying column names. Defensive database programming: rewriting queries with NOT IN().

Fun with DBCC CHECKIDENT

Alexander Kuznetsov — Thu, 26 Jun 2008 21:51:00 GMT

Suppose you have an empty table:

SELECT COUNT(*) FROM SampleTable

---

What would be the result of the following query:

DBCC CHECKIDENT('SampleTable', RESEED, 1)

INSERT SampleTable(j) SELECT 1

SELECT SCOPE_IDENTITY()

Surprisingly enough, the answer is “it depends”. If the table is brand new, you’ll get one, if there have been inserts into the table, the answer is 2. See for yourself:

CREATE TABLE SampleTable(i INT IDENTITY, j INT)

SELECT COUNT(*) FROM SampleTable

---

DBCC CHECKIDENT('SampleTable', RESEED, 1)

INSERT SampleTable(j) SELECT 1

SELECT SCOPE_IDENTITY()

---

DELETE FROM SampleTable;

DBCC CHECKIDENT('SampleTable', RESEED, 1);

INSERT SampleTable(j) SELECT 1;

SELECT SCOPE_IDENTITY();

---

Even more surprising is the fact that this inconsistent behavior is by design – it has been documented in MSDN:

http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx

quoting from this MSDN article: “If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1.”

So far I cannot come up with a reason why such inconsistent behavior could be required. Any feedback is welcome.

Anyway, if you are unit testing a stored procedure inserting into a table with identity, this feature of DBCC CHECKIDENT may break your unit tests. The workaround that I am using in my database unit testing is simple – when I set up my test fixture, I run the following script against all the tables with identities:

BEGIN TRY

INSERT INTO MySchema.MyTable DEFAULT VALUES

END TRY

BEGIN CATCH

PRINT '1'

END CATCH

Database Unit Testing: Impersonation

Alexander Kuznetsov — Wed, 18 Jun 2008 16:19:00 GMT

When you run NUnit/C# unit tests against your local instance, you are a dbo. As such, you are not getting any errors caused by missing permissions. However, you can impersonate another user, and run your unit tests in the context of that other user. The following test harness demonstrates the technique:

using System.Data;

using System.Data.SqlClient;

using NUnit.Framework;

namespace MyDB.Tests

{

[TestFixture]

public class ImpersonatorTests

{

private SqlConnection connection = null;

private Impersonator realUser;

#region constants

private const string userName = "me";

private const string domainName = "myDomain";

#region hide me

private const string pwd = "myPassword";

#endregion

[TestFixtureSetUp]

public void TestFixtureSetUp()

{

realUser = new Impersonator(userName, domainName, pwd);

connection = new SqlConnection("server=(local);trusted_connection=true;database=FinancialDW;");

connection.Open();

}

[TestFixtureTearDown]

public void TestFixtureTearDown()

{

if (realUser != null) realUser.Dispose();

}

[Test]

public void ImpersonationSucceededTest()

{

SqlCommand reader = connection.CreateCommand();

reader.CommandText = "SELECT SUSER_NAME()";

reader.CommandType = CommandType.Text;

string suserName = reader.ExecuteScalar().ToString();

Assert.AreEqual(@"myDomain\me", suserName);

//Console.WriteLine(suserName);

}

[Test]

public void CanSelectFromSalesTest()

{

SqlCommand reader = connection.CreateCommand();

reader.CommandText = "SELECT COUNT(*) FROM Data.Sales";

reader.CommandType = CommandType.Text;

int numRows = int.Parse(reader.ExecuteScalar().ToString());

Assert.IsTrue(numRows >= 0);

}

So, if the myDomain\me user does not have the SELECT permission on Data.Sales, I am getting an exception:

System.Data.SqlClient.SqlException: SELECT permission denied on object 'Sales', database 'MyDb', schema 'data'

The code that I used to implement the Impersonator class is mostly copied and pasted from http://support.microsoft.com/?scid=306158

, and here it is:

using System;

using System.Runtime.InteropServices;

using System.Security.Principal;

namespace MyDB.Tests

{

public class Impersonator : IDisposable

{

/* code taken from

http://support.microsoft.com/?scid=306158

public const int LOGON32_LOGON_INTERACTIVE = 2;

public const int LOGON32_PROVIDER_DEFAULT = 0;

WindowsImpersonationContext impersonationContext;

[DllImport("advapi32.dll")]

public static extern int LogonUserA(String lpszUserName,

String lpszDomain,

String lpszPassword,

int dwLogonType,

int dwLogonProvider,

ref IntPtr phToken);

[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]

public static extern int DuplicateToken(IntPtr hToken,

int impersonationLevel,

ref IntPtr hNewToken);

[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]

public static extern bool RevertToSelf();

[DllImport("kernel32.dll", CharSet = CharSet.Auto)]

public static extern bool CloseHandle(IntPtr handle);

public Impersonator(string user, string domain, string password)

{

if(!ImpersonateValidUser(user, domain, password))

{

throw new Exception(string.Format(@"Logon failed for {0}\{1}", domain, user));

}

private bool ImpersonateValidUser(String userName, String domain, String password)

{

WindowsIdentity tempWindowsIdentity;

IntPtr token = IntPtr.Zero;

IntPtr tokenDuplicate = IntPtr.Zero;

if (RevertToSelf())

{

if (LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE,

LOGON32_PROVIDER_DEFAULT, ref token) != 0)

{

if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)

{

tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);

impersonationContext = tempWindowsIdentity.Impersonate();

if (impersonationContext != null)

{

CloseHandle(token);

CloseHandle(tokenDuplicate);

return true;

}

if (token != IntPtr.Zero)

CloseHandle(token);

if (tokenDuplicate != IntPtr.Zero)

CloseHandle(tokenDuplicate);

return false;

}

#region IDisposable Members

public void Dispose()

{

if (impersonationContext != null)

impersonationContext.Undo();

}

#endregion

}

Database Edition Now Does DB2!

andyleonard — Mon, 16 Jun 2008 16:20:00 GMT

Tech Ed 2008 included some major announcements for database development folks. One of the biggees was this - and announcement that Database Edition is going to support DB2 development!

Gert and the team have been working hard to expand the underlying architecture and functionality of Database Edition (making it extrememly difficult for this one author I know in Farmville to keep up... I'm not complaining!). Rearchitecting Data Dude to utilize a provider-based model was no small task, but the team at Microsoft has been able to pull it off - way to go team!

These changes affect a lot of how Database Edition currently works. We were given a preview at the MVP Summit in April. The work is impressive, but the ability and agility provided by the new architecture is even more so.

You will see a ton of changes with the release of Visual Studio Team System 2008 Database Edition GDR. You can currently download the June CTP here.

:{> Andy

Unit Testing Tips: Reproduce a Lock Timeout

Alexander Kuznetsov — Fri, 30 May 2008 22:24:00 GMT

Well, I have just finished a series of articles about unit testing on simple-talk.com, and some techniques were not included, just to keep the articles short. Still problems such as reproducing a lock timeout and unit testing the retry after it (lock timeout) are interesting, at least to me, so I am continuing.

You will need the database StressTests, which can be downloaded at the following URL:

http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes---reproduce-database-errors/

You will also need the following stored procedure which logs the timeout and retries at most five times:

CREATE PROCEDURE [Writers].[UpdateUserWithRetry]

@UserID INT,

@FirstName VARCHAR(8),

@LastName VARCHAR(8),

@Position VARCHAR(8)

SET NOCOUNT ON

DECLARE @ret INT, @succeeded INT, @attemptNumber INT;

SELECT @ret = 0, @succeeded = 0, @attemptNumber = 1;

WHILE(@succeeded = 0 AND @attemptNumber < 5) BEGIN

SET @attemptNumber = @attemptNumber + 1;

BEGIN TRY

UPDATE Data.Users

SET FirstName = @FirstName,

LastName = @LastName,

Position = @Position

WHERE UserID = @UserID;

SET @succeeded = 1;

END TRY

BEGIN CATCH

IF (XACT_STATE()) = -1

BEGIN

ROLLBACK TRANSACTION;

END;

INSERT INTO Data.ErrorLog(ERROR_PROCEDURE, ERROR_LINE, ERROR_NUMBER, ERROR_MESSAGE, ERROR_TIME)

SELECT ERROR_PROCEDURE(),

ERROR_LINE(),

ERROR_NUMBER(),

ERROR_MESSAGE(),

CURRENT_TIMESTAMP;

SET @ret = 1;

END CATCH

END

RETURN @ret;

Also you will need the LockTimouter class which reproduces a lock timeout inside your stored procedure:

· - it opens another connection,

· - begins a transaction,

· - modifies the data from that connection,

· - then it attempts to modify the same data from your stored procedure

· - releases the lock as soon as your error is logged, so that your retry can succeed

public class LockTimeouter : IDisposable

{

private readonly SqlConnection _anotherConnection;

private readonly SqlCommand _command;

private readonly SqlCommand _anotherCommand;

private readonly IAsyncResult _anotherCommandStarted;

public LockTimeouter(SqlConnection connection, string hookCommand)

{

_command = connection.CreateCommand();

_command.CommandText = "SET LOCK_TIMEOUT 1;DELETE FROM Data.ErrorLog";

_command.CommandType = CommandType.Text;

_command.ExecuteNonQuery();

_anotherConnection = new SqlConnection(connection.ConnectionString + ";Asynchronous Processing=true");

_anotherConnection.Open();

_anotherCommand = _anotherConnection.CreateCommand();

_anotherCommand.CommandText = string.Format("BEGIN TRANSACTION; {0};", hookCommand);

_anotherCommand.CommandType = CommandType.Text;

_anotherCommand.ExecuteNonQuery();

_anotherCommand.CommandText = "DECLARE @c INT;SET @c=0;WHILE @c=0 BEGIN SELECT @c=COUNT(*) FROM Data.ErrorLog;END;COMMIT";

_anotherCommand.CommandType = CommandType.Text;

_anotherCommandStarted = _anotherCommand.BeginExecuteNonQuery();

}

public void Dispose()

{

_command.CommandText = "IF @@TRANCOUNT>0 BEGIN ROLLBACK; END";

_command.ExecuteNonQuery();

_anotherCommand.EndExecuteNonQuery(_anotherCommandStarted);

_anotherCommand.CommandText = _command.CommandText;

_anotherCommand.ExecuteNonQuery();

_anotherConnection.Close();

}

The unit test itself is quite simple, because most of the work is wrapped in the LockTimouter class

[Test]

public void LockTimeoutTest()

{

SqlCommand command = _connection.CreateCommand();

command.CommandText = "DELETE FROM Data.Users WHERE UserId = 12345";

command.CommandType = CommandType.Text;

command.ExecuteNonQuery();

command.CommandText = "Writers.UpdateUserWithRetry";

command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@UserId", 12345);

command.Parameters.AddWithValue("@FirstName", "Josh");

command.Parameters.AddWithValue("@LastName", "Olson");

command.Parameters.AddWithValue("@Position", "QA");

using (new LockTimeouter(_connection, "INSERT INTO Data.Users(UserID, FirstName, LastName, Position, LotsOfComments)VALUES(12345,'Jill','Hansen','QA','')"))

{

try

{

command.ExecuteNonQuery();

Console.WriteLine("No exception raised");

}

catch (Exception e)

{

if (!e.ToString().Contains("Transaction count after EXECUTE indicates "))

Console.WriteLine(e);

}

//check if there are updates in Data.Users, and if the error has been recorded

}

When the test is finished, you can verify that one error was indeed recorded and that the modification finally completed:

SELECT * FROM Data.ErrorLog ORDER BY ERROR_TIME

SELECT * FROM Data.Users

Installing Virtual Server On Vista Ultimate 64-bit

andyleonard — Sat, 08 Dec 2007 16:07:00 GMT

Mea...

This may be a little off-topic.

I use Virtual Server and Virtual PC a lot for things like testing beta and CTP releases of SQL Server. I don't have time or the inclination to reformat physical hard drives, load an OS, or even fiddle with restoring from an enterprise backup solution like Ghost.

I blogged about configuring IIS7 for Reporting Services 2005, so this may have more relevance than I imagine.

Introduction

If you're not using virtualized machines or servers, you should check them out. They're great for testing new stuff - relatively risk-free. I am aware of two flavors of virtual engines: VMWare and Microsoft products: Virtual PC and Virtual Server.

I have no experience with VMWare.

The Microsoft products are 100% free (as in beer).

I do not know which is better. I imagine that, like all things in life, one has advantages over the other and vice versa. The Microsoft stuff does what I need, so I've not ventured to search greener pastures (ever wonder why those pastures are greener? Think about it).

Get To The Configuration Already!

When I installed Virtual Server 2005 R2 SP1 on my newish Vista Ultimate 64-bit machine, I saw quirky stuff. For example, I could not change the port for admin - it defaulted to 80 and gave me an error message stating my server "didn't allow" installations on other ports.

Reeeeaaallly? Huh.

I proceeded with the installation anyway and nothing worked. Well, nothing in Virtual Server. I could not get to the administrative page and, in case you've never worked with Virtual Server, you can do nothing unless you can reach the administration page. (Note, you can do stuff if you like to type. I don't like to type.)

A search ensued. I found a good article on why this was happening and how to fix it. It happens because IIS7 isn't configured (out of the box) to allow Virtual Server 2005 R2 to do its thing. To fix it, open Control Panel, Programs and Features (the snappy new Add/Remove Programs), then click "Turn Windows features on or off".

You want to navigate to Internet Information Services folder and make your Windows Features look like this:

(A Thousand Words)

The image above may be the first on the web (it was the only one I could find) of how to configure IIS7 on Vista Ultimate 64-bit for both SSRS 2005 and Virtual Server 2005 R2.

Note you still get the Configure Components screen displayed above - the one with port textbox disabled and set to 80 - at least in Vista Ultimate. But you also get these screens:

:{> Andy

Skipping SQL Server 2005

andyleonard — Wed, 21 Nov 2007 17:53:00 GMT

According to numerous sources - most recently a SQL Server Magazine article entitled Too Soon for SQL Server 2008? Readers Say "Yes!" - a lot of SQL Server customers are holding off upgrading to SQL Server 2005, preferring to wait for the upcoming release of SQL Server 2008. While I understand the economics, I worry about the DBAs and database developers in the trenches.

Personally, I prefer to learn new technology while it's still, well, new. That makes me an early adopter. In my opinion, it also flattens the learning curve somewhat.

One the other hand, as new technology is put into play by early adopters bugs and enhancements are found and suggested - maturing the technology and making it easier to learn. Encountering fewer upgrade and translation issues flattens the learning curve too. So perhaps skipping a version isn't such a big deal after all.

One thing to consider: Microsoft is unlikely to return to 5-year release cycles for server products. So how do we make upgrading less painful / expensive / painful? (...not a typo...)

One suggestion is to add regression tests to your Disaster Recovery plan. You have regression testing in place already if you have a DR plan. It may consist of throw the thrid switch and monitor the error email account. This is an EMP (Expensive Management Practice) and there are much better ways to manage regression tests as we draw near the close of 2007.

I predict Test Automation will be a hot topic next year as more folks dig into the features of Team Foundation Server 2008. The powerful idea behind automated regression tests is very simple: Create a suite of tests and you can run them forever (for free, even!). Regression tests are money in the bank - you write them once and run them every time you release a (hopefully) backwards-compatible version. The test suite grows over time as more bugs are discovered, but the SOBER (Same Old Bugs Every Release) spectre passes into memory of days gone by. And you get the added benefits of improved quality and reduced time to market - what's not to love about regression tests?

If you don't have regression tests in place, start saving your troubleshooting queries and utility applications in a central location - build a repository starting today. When the next new version of SQL Server is released (circa 2011), your applications will be much more agile and able to make the leap. Then you won't be painted into the corner of "not having enough time to test" the impact of the new functionality and changes to existing functionality - you will be able to know where you stand.

How much is the option of upgrading worth to your company?

:{> Andy

Technorati Tags: SQL Server 2005 SQL Server 2008 Regression Testing EMPs Expensive Management Practices