Upgrade SQL 2005 to 2008 checklist

Pre-upgrade checklist

• Make sure you don't have an sa accounts with blank passwords. This is a really good idea regardless.
  
• On the sql server, get your .net installation up to .NET Framework 3.5 SP1 or later. Best way to do this is to install Visual Studio 2008 SP1.
  
• For Server 2003, install Windows Installer v4.5 from the SQL 2008 media. Not needed for Server 2008.
• Make sure that all DBAs, sysadmins, and dev team leads know that they will need to install the SSMS 2008. (Still a good idea if you have SQL 2005 servers in your environment to keep SSMS 2005 and BIDS 2005 installed at your workstation, especially for replication and SSIS packages.)
  

General tips

• Easiest way I can think to do this is to install a SQL 2008 instance, detach/attach the 2005 databases to the new edition. Obviously, this creates a server with a different instance name. This may or may not be a big deal, if you can change your connections strings easily enough.
  
  In all likelihood, you'll instance want to upgrade in-place your SQL 2005 instance. This can be done through the wizard, using the option "Upgrade from SQL Server 2000 or SQL Server 2005."
  
  
• Remember that upgraded, attached or copied databases from SQL 2005 may still be in SQL 2005 compatibility mode. Be sure to do your post-implementation testing against databases in SQL 2008 compatibility mode!
  

If you have anything to add, please do so in the comments below so that others who find this blog post can benefit.

Links for more reading:
http://msdn.microsoft.com/en-us/library/ms144267.aspx
http://msdn.microsoft.com/en-us/library/bb677619.aspx
Clustered environment: http://msdn.microsoft.com/en-us/library/ms191295.aspx
http://www.sqlservercentral.com/articles/SQL+Server+2008/67066/

How many work days?

Here's a fun script I got asked to write to calculate the number of work days between a given start and end date, including those dates.

The query does not exclude holidays, only Saturdays and Sundays are removed from the count. It would be easy enough to exclude holidays by hooking a CASE up to a table that contains date records for the holidays your business has declared non-work days. SQL does NOT know that Mardi Gras is a holiday for your south Louisiana company, folks. :)

It also determines the value of your DATEFIRST setting and sets it if it is not default. This is only a session-wide declaration.

I welcome any feedback.

Again, I know, blogspot doesn't have a way to format this better.

IF @@DATEFIRST <> 7
SET DATEFIRST 7

declare @startdate smalldatetime
, @enddate smalldatetime
, @workdays int


select @startdate = '1/1/2010'
, @enddate = '1/17/2010'

select @workdays =

--Raw number of days including both the start and end dates.
datediff(d, @startdate, @enddate) + 1

--Is the start date a weekend?
- CASE WHEN
datepart(dw, @startdate) in (1, 7)
THEN
1
ELSE 0
END

--Is End Date a weekend?
- CASE WHEN
datepart(dw, @enddate) in (1, 7)
THEN
1
ELSE 0
END

--Remove whole week weekends.
-
CASE WHEN
datediff(d, @startdate, @enddate)>7
THEN
CASE WHEN
datediff(d, @startdate, @enddate)/7 > 0
THEN (datediff(d, @startdate, @enddate)/7) * 2
ELSE 0
END
ELSE 0
END

-- Remove a weekend from an incomplete week
-
CASE WHEN
datediff(d, @startdate, @enddate)%7 > 0
THEN
CASE WHEN
datepart(dw, @startdate) + (datediff(d, @startdate, @enddate)%7) = 8
THEN 1
WHEN
datepart(dw, @startdate) + (datediff(d, @startdate, @enddate)%7) > 8
THEN 2
ELSE 0
END
ELSE 0
END


SELECT @workdays

"Measure not the work until the day's out and the labor done."-Elizabeth Barrett Browning

Roll call - when all members are present

Had to write a neat query that was a bit of fun-

A protocol is a set of services. A clinic offers services, independently of protocols. Should a clinic offer all those services, it satisfies a protocol. Return a list of all satisfied protocols given a single clinic.

(Pardon the spacing and syntax, but blogspot nukes my tabs and spaces. I must figure out a better way to post code.)

SELECT
p.ID
FROM
dbo.Protocol p
WHERE
p.IsActive = 1
AND p.ID NOT IN (
SELECT
ps.ProtocolID
FROM
dbo.ProtocolServices ps
LEFT JOIN
dbo.ClinicServices cs
ON
ps.ServiceID = cs.ServiceID
and cs.IsActive = 1
and cs.clinicid = 987
WHERE
ps.IsActive = 1
and cs.ID is null
)

What the query does is to say - give me all Protocols, except the ones where the service list is not satisfied. The subquery looks at the join tables (they are named just as they behave, per naming conventions) and determines any Protocols where the Clinic is not satisfying all Services - that is, where there is a null in the LEFT JOIN on ClinicServices.

Now, here's the fun part.

The developer I was working with, despite all his talents, is a groupie for lambda expressions, which I understand are some sort of dark magic concocted in obsidian towers for purposes dire and contradictory to the good will of databases.

Despite all that hyperbole, here's what he came up with to convert the above query to the below nonsense, err, λ expression. From what he explained to me, Lambda expressions are dynamic and use the declaratives seen below while LINQ is more hard coded. I welcome further input with the caveat that LINQ, EF and other systems for generating bad TSQL are systema non grata around here. :)

var protocols = Protocols.Where(p => p.IsActive);
var activeProtocolServices = ProtocolServices.Where(ps => ps.IsActive);
var clinicServices = ClinicServices
.Where(cs => cs.IsActive)
.Where(cs => cs.ClinicID == 987);
var allProtocolServicesForClinics = activeProtocolServices.Join(clinicServices, ps => ps.ServiceID, cs => cs.ServiceID, (ps, cs) => ps);
var protocolServicesNotApplicableForClinic = activeProtocolServices.Except(allProtocolServicesForClinics);
var protocolsForClinic = protocols.Except(protocolServicesNotApplicableForClinic.Select(ps => ps.Protocol));