My Server Admin and I share some DBA roles in our company, they focus on
Security and Backups, while I focus on BI, Database Development, etc.

We are on SQL Server 2008 (No Service Packs) and recently encountered a bug
that Microsoft fixes in one of their cumulative updates for SQL Server 2008.
We decided we wanted that cumulative update (which goes before SP1) but when
we realized there was a service pack after the cumulative update, we couldn't
decide on whether to apply the SP or the cumulative update.

We decided we would ask what the general amount of time DBA's like to let go
by after a release of a SP before they feel comfortable applying the service
pack. (We would still test it internally with our own "stuff" before going
to production.) We know Microsoft tests things, and has their own controls
before releasing SP's, but how long is it before DBA's start to trust that
the SP is ready for us? My Server Admin felt that 3 months was their comfort
level on Operating Systems. How about on the Database side?

Besides the fact that SQL Server 2008's SP1 seems to have come out in August
2009
(http://www.microsoft.com/downloads/d...9-ccc6a4f9dc19)
we are trying to build a mindset in terms of how long to wait. (An
assumption is that you don't "need" one of the fixes as that would obviously
change your perspective on how long you can wait.)

Your insights are appreciated; multiple answers are welcome as this is an
opinion question.

Thanks,
Keith

greenmtnsun (greenmtnsun (AT) discussions (DOT) microsoft.com) writes:
The main difference between a Service Pack and a Cumultative Update, is
that the SP undergoes more testing. An SP may include extra fixes that
do not qualify for a CU, and there may even be new features. Exactly how
much varies from SP to SP.

SQL 2005 SP1 - Made Database Mirroring fully supported. Added two new
trace flags, a minor behavioural change with TRY-CATCH,
a breaking change in SQL Server Agent Job Tokens (big
security hole)

SQL 2005 SP2 - Quite a few changes, not the least with the tools. On the
engine side, added vardecimal.

SQL 2005 SP3 - Bugfixes only. Well some changes around timers, but they
could be seen as bugfixes.

SQL 2008 SP1 - Bugfixes only. Or at least I cannot recall any behavioural
changes. (OK, so there is one around OPTION (RECOMPILE),
they reverted to the old SQL 2005 behaviour, since the
new behaviour could cause incorrect results. This has been
resolved in a later CU.)

As for the qualities of service packs, SQL 2005 SP2 was a disaster, and
they had to pull it twice because of serious regression bugs, both
involving maintenance plans. SQL 2008 SP1 has not receive any flak as
I recall.

Anyway, what you should absolutely not do is to run SQL 2008 RTM in
production. There is a serious bug involving outer joins which is of
the nastiest kind. That is, you can silently get incorrect results.
This bug was fixed SQL 2008 RTM CU2.

Iin your situation, I see little reason why you should not install
SQL 2008 SP1. The only reason is if you rely on OPTION (RECOMPILE)
working with the actual values as constants. In this case you should
install SQL 2008 SP1 CU5.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Keith,

It came out in April and includes the ability to slipstream and uninstall
service packs and CU's. See this
http://blogs.msdn.com/sqlreleaseserv...-released.aspx

Chris

"greenmtnsun" <greenmtnsun (AT) discussions (DOT) microsoft.com> wrote

I forgot about lock pages in memory on Standard Edition which looks like SP1
CU2 or 3
http://sqlblog.com/blogs/aaron_bertr...d-edition.aspx

Chris

"Chris Wood" <anonymous (AT) microsoft (DOT) com> wrote

In the short term we are of course asking if we should apply the SP, but we
also need that long term perspective...

In general, how long would you wait, knowing that at least one SP ended up
getting pulled in the past?

A week, month, 3 months, 6 months?

"Erland Sommarskog" wrote:

greenmtnsun (greenmtnsun (AT) discussions (DOT) microsoft.com) writes:
Let's see, these service packs all had serious regression problems:

SQL 6.5 SP5
SQL 2000 SP3
SQL 2000 SP4
SQL 2005 SP2

That is not a good record. But in all cases, the problems were discovered
fairly early on.

You must also weigh it against the risk running with the earlier versions.
Service packs may introduce regression bugs, but so may RTM versions. In
total it is very likely that the service pack fixes more bugs than it
introduces. Of course, that single bug it introduces may be fatal.

The question "how long should we wait until we install the service pack",
can also be phrased: "within which time frame from the service pack is
released should we install it"? In your case, you have been running
SQL 2008 RTM for more than six months after the release of SP1. I'm
tempted to call this neglect to maintain your system. Particularly since
this is an SP1. (Hopefully later service packs addresses fewer and
fewer and less and less critical bugs.)

To answer my own question: I think you should install the new service
pack within three months it has been released.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx