Why you can no longer rely on @@VERSION to determine your SQL Server version…
We’ve just upgraded to SQL Server 2005 Service Pack 2a.
You mean SQL Server 2005 Service Park 2, surely?
No, I mean Service Pack 2a…. Read on…
Problem:
In SQL Server 2005 Service Pack 2, maintenance plans were given a new ‘hours’ option, in addition to days, weeks and years.
Nice touch.
Good feature.
Unfortunately, someone didn’t regression test it too well when they coded it, as when you looked at an old maintenance plan, the run frequency was read incorrectly, changing each frequency to the next one ‘down’, ie: years became weeks, weeks became days.
It happens. We all code. Things like this can be missed in the heat of a release.
And Microsoft have fixed it in SP2a, and quickly, which is very admirable.
However, if you’ve downloaded and applied the service pack, then run a SELECT @@VERSION from Management Studio:
SP2 prior to 5th March 2007 SELECT @@VERSION = 9.00.3042.00
SP2(a) after 5th March 2007 SELECT @@VERSION = 9.00.3042.00
That’s right. They’re the same.
You have no way of easily telling if any given SQL Server is running with a scheduling problem or not.
Consequences:
You now have NO idea whether or not you have applied SP2 or SP2a, as both report the same version.
It means your legacy jobs will run either more often, or less often than you expect, without warning, and without being able to easily tell.
Which of course has no implications whatsoever (BTW, that was sarcasm if you didn’t pick up on it…)
Thanks Microsoft.
As a DBA, this is a major, MAJOR screw up.
Not because it affects the frequency of jobs which run cleanups or SSIS packages. I can handle that.
But because I now have no way of quickly and easily telling which ones are running on which version of SQL Server 2005.
If I had one server it wouldn’t be an issue, but most of use have at least several, sometimes dozens or even hundreds of SQL Servers.
Analysis:
We all screw up. When Microsoft do it, sure, it’s more noticeable.
But to re-release SP2 into the public domain, WITHOUT ALTERING THE REPORTED VERSION NUMBER is downright sneaky.
It smells of cover up.
Which is exactly the way the SQL Server 2005 Service Pack 2 release seems to be being handled right now.
Or, as I’m going to insist on calling it, SQL Server 2005 Service Pack 2a.
And what’s worse, SP2a DOES NOT upgrade the original SP2 with the maintenance plan issue. To fix that, you need to apply the SP2 hotfix. Or GDR (General Distribution Release) as it’s now known. Let’s stick with hotfix.
Infact the only minor glimmer of light is that if you do apply the hotfix for SP2 (instead of the SP2a release), you will indeed see @@VERSION = 9.00.3050.00, which correctly identifies the fact that the code has changed and that SQL Server will behave differently.
However, if you apply SP2a, it still reports as if it is SP2, which introduces an element of doubt into the mind of the DBA. Has it been patched? Or not?
Actions:
So how do you resolve this mess?
Simple.
IF you upgraded SQL Server 2005 with Service Pack 2 BEFORE 5th March 2007 THEN
Download and apply the hotfix from http://www.microsoft.com/downloads/details.aspx?FamilyId=e2c358a1-ecc4-4c49-8f65-daa6b7800eec&displaylang=en
ELSE
Download and apply SP2a (though Microsoft still insist on calling it SP2 folks) from http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx
ENDIF
NB : If you can’t find it at either of these locations, visit www.microsoft.com/sql and looks for the downloads link.
However, you still can’t accurately determine if you’re running SP2 or SP2a by running SELECT @@VERSION.
The only way to do it is by drilling down into the C:\Program Files\Microsoft SQL Server\90\DTS\Tasks folder on the database server, and checking the version number on one of the following files (right click, Properties, Version) :
Microsoft.SqlServer.MaintenancePlanTasks.dll
Microsoft.SqlServer.MaintenancePlanTasksUI.dll
SP2 reports the file version as 9.00.3042.00
SP2a reports the file version as 9.00.3043.00
However, some Windows sys admins don’t allow even DBAs to touch the server’s file system, so you’ll be relying on them to report the version information to you.
Fine if you keep a change log, but in practice many sites often don’t due to workload pressure.
And in the future you’ll no doubt inherit systems that don’t have a change log, even if your own change procedures are perfect.
Summary:
In short, you CAN NO LONGER rely on SELECT @@VERSION to tell you if SQL Server 2005 has been upgraded to SP2a.
It now returns the same value as SP2, which contains the maintenance plan problem.
Conclusion:
Microsoft makes some great technology. Personally I think SQL Server is THE best thing it does.
Developers code, fix and release. The release cycle automatically increments the build or version number of the software. Could it be an innocent procedural failure? Maybe. In which case something needs fixing in the build procedures.
But it looks very suspect that the SP2 hotfix (which was released BEFORE SP2a) got an incremented @@VERSION (9.00.3050), when a later *full* SP2 rebuild did not.
Maybe someone thinks that quietly sneaking a fix in without assigning it a new version means zero press coverage, so no one will notice. No bad PR.
Wrong.
We will, and it causes us way more headaches than if you do tell us.
My message to Microsoft:
Don’t treat your customers as fools, we are not. We do notice this stuff. It’s what we do every day. We’re not stupid.
Please don’t insult our intelligence.
If you fly fixes in under our radar without warning, you deserve to get shot down.
Service packs are part of our life. They’re a chore, but we accept them.
What we object to is not being able to tell whether or not we have applied them.
References:
Knowledge base article: http://support.microsoft.com/kb/933508
Service Pack 2a: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx
We’ve just upgraded to SQL Server 2005 Service Pack 2a.
You mean SQL Server 2005 Service Park 2, surely?
No, I mean Service Pack 2a…. Read on…
Problem:
In SQL Server 2005 Service Pack 2, maintenance plans were given a new ‘hours’ option, in addition to days, weeks and years.
Nice touch.
Good feature.
Unfortunately, someone didn’t regression test it too well when they coded it, as when you looked at an old maintenance plan, the run frequency was read incorrectly, changing each frequency to the next one ‘down’, ie: years became weeks, weeks became days.
It happens. We all code. Things like this can be missed in the heat of a release.
And Microsoft have fixed it in SP2a, and quickly, which is very admirable.
However, if you’ve downloaded and applied the service pack, then run a SELECT @@VERSION from Management Studio:
SP2 prior to 5th March 2007 SELECT @@VERSION = 9.00.3042.00
SP2(a) after 5th March 2007 SELECT @@VERSION = 9.00.3042.00
That’s right. They’re the same.
You have no way of easily telling if any given SQL Server is running with a scheduling problem or not.
Consequences:
You now have NO idea whether or not you have applied SP2 or SP2a, as both report the same version.
It means your legacy jobs will run either more often, or less often than you expect, without warning, and without being able to easily tell.
Which of course has no implications whatsoever (BTW, that was sarcasm if you didn’t pick up on it…)
Thanks Microsoft.
As a DBA, this is a major, MAJOR screw up.
Not because it affects the frequency of jobs which run cleanups or SSIS packages. I can handle that.
But because I now have no way of quickly and easily telling which ones are running on which version of SQL Server 2005.
If I had one server it wouldn’t be an issue, but most of use have at least several, sometimes dozens or even hundreds of SQL Servers.
Analysis:
We all screw up. When Microsoft do it, sure, it’s more noticeable.
But to re-release SP2 into the public domain, WITHOUT ALTERING THE REPORTED VERSION NUMBER is downright sneaky.
It smells of cover up.
Which is exactly the way the SQL Server 2005 Service Pack 2 release seems to be being handled right now.
Or, as I’m going to insist on calling it, SQL Server 2005 Service Pack 2a.
And what’s worse, SP2a DOES NOT upgrade the original SP2 with the maintenance plan issue. To fix that, you need to apply the SP2 hotfix. Or GDR (General Distribution Release) as it’s now known. Let’s stick with hotfix.
Infact the only minor glimmer of light is that if you do apply the hotfix for SP2 (instead of the SP2a release), you will indeed see @@VERSION = 9.00.3050.00, which correctly identifies the fact that the code has changed and that SQL Server will behave differently.
However, if you apply SP2a, it still reports as if it is SP2, which introduces an element of doubt into the mind of the DBA. Has it been patched? Or not?
Actions:
So how do you resolve this mess?
Simple.
IF you upgraded SQL Server 2005 with Service Pack 2 BEFORE 5th March 2007 THEN
Download and apply the hotfix from http://www.microsoft.com/downloads/details.aspx?FamilyId=e2c358a1-ecc4-4c49-8f65-daa6b7800eec&displaylang=en
ELSE
Download and apply SP2a (though Microsoft still insist on calling it SP2 folks) from http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx
ENDIF
NB : If you can’t find it at either of these locations, visit www.microsoft.com/sql and looks for the downloads link.
However, you still can’t accurately determine if you’re running SP2 or SP2a by running SELECT @@VERSION.
The only way to do it is by drilling down into the C:\Program Files\Microsoft SQL Server\90\DTS\Tasks folder on the database server, and checking the version number on one of the following files (right click, Properties, Version) :
Microsoft.SqlServer.MaintenancePlanTasks.dll
Microsoft.SqlServer.MaintenancePlanTasksUI.dll
SP2 reports the file version as 9.00.3042.00
SP2a reports the file version as 9.00.3043.00
However, some Windows sys admins don’t allow even DBAs to touch the server’s file system, so you’ll be relying on them to report the version information to you.
Fine if you keep a change log, but in practice many sites often don’t due to workload pressure.
And in the future you’ll no doubt inherit systems that don’t have a change log, even if your own change procedures are perfect.
Summary:
In short, you CAN NO LONGER rely on SELECT @@VERSION to tell you if SQL Server 2005 has been upgraded to SP2a.
It now returns the same value as SP2, which contains the maintenance plan problem.
Conclusion:
Microsoft makes some great technology. Personally I think SQL Server is THE best thing it does.
Developers code, fix and release. The release cycle automatically increments the build or version number of the software. Could it be an innocent procedural failure? Maybe. In which case something needs fixing in the build procedures.
But it looks very suspect that the SP2 hotfix (which was released BEFORE SP2a) got an incremented @@VERSION (9.00.3050), when a later *full* SP2 rebuild did not.
Maybe someone thinks that quietly sneaking a fix in without assigning it a new version means zero press coverage, so no one will notice. No bad PR.
Wrong.
We will, and it causes us way more headaches than if you do tell us.
My message to Microsoft:
Don’t treat your customers as fools, we are not. We do notice this stuff. It’s what we do every day. We’re not stupid.
Please don’t insult our intelligence.
If you fly fixes in under our radar without warning, you deserve to get shot down.
Service packs are part of our life. They’re a chore, but we accept them.
What we object to is not being able to tell whether or not we have applied them.
References:
Knowledge base article: http://support.microsoft.com/kb/933508
Service Pack 2a: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx
Comments