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));

January SQL Server & .NET Joint User Group Meeting

http://batonrouge.sqlpass.org/

January SQL Server & .NET Joint User Group Meeting

Note: This is meeting will be held at the same time as the .NET User Group. However we will break into two sessions after the Lightning Round, which will be held in the Kitchen Area.

There will be great giveaways including Windows 7 Ultimate and Office 2007.

Location: Lamar Advertising

Address: 5551 Corporate Blvd, Baton Rouge, LA 70808

Date and Time: 1/13/2010 5:45:00 PM

Sponsored by: WillStaff Worldwide and Rose Huguet, Realtor

Agenda

5:45 pm - 6-15 pm: Networking and Refreshments

6:15 pm – 6:30 pm: Lightning Round

6:30 pm – 6:40 pm: Introductions

6:40 pm – 8:10 pm: Break into two Sessions (SQL Server and .NET)

8:10 pm – until: Open Forum for questions and Raffle

Lightning Round (In Lamar Kitchen Area)

Presenter: Stephanie Thomas
Stephanie is a Sales Manager for WillStaff Worldwide in Metairie and has extensive experience as an IT recruiter.

Topic: Branding Yourself in a Tough Economy
In a tough economy it is important to prepare yourself for the worst. Stephanie will provide some tips for making yourself more marketable.

Level: Introductory

SQL Server Presentation (In Lamar Kitchen Area)

Topic: Database Normalization/Denormalization

Speaker: Thomas LeBlanc

BIO:

Thomas is a Database Administrator for Amedisys, Inc in Baton Rouge, LA. He has been in the IT field for 20 years starting as a COBOL programmer, graduating to dBase, FoxPro, Visual FoxPro, upgrading to Visual Basic versions 3-6 and even some .NET(C#). Designing and developing normalized database has become his passion. Full-time DBA work started about 9 years ago for Thomas while working at a Paper Mill in St. Francisville, LA continuing with IEM, then Amedisys. Current activity at Amedisys involves 4 other DBAs and a manager supporting 400+ GB databases with replication to a Data Warehouse and DB Mirroring to a disaster recovery site. Performance tuning and reviewing database design and code are an everyday occurrence for DBAs at Amedisys. Thomas’ free time is spent helping those less fortunate and improving his relationship with his family and God.

Overview:

How did the development world conclude that an integer is the best primary key? What is 4th and 5th normal form? What has been added to SQL Server in the 2005 and 2008 to help change the way database design has evolved over the years? Can we still use a VarChar(xx) for a primary key? What is the difference between a lookup and Parent/Child relationship? What is an example of Many-To-Many? This session will go through the history of 20 year s of experience with various database designs – normalize and denormalized. The discussion will include the benefits and forward looking that should be required for using various design techniques.

.NET Presentation (Lamar Media Room)

Topic: The Busy .NET Developer’s Guide to Extending .NET Apps with Scripting.

Speaker: Ted Neward

BIO:

Ted is an independent consultant specializing in high-scale enterprise systems, working with clients ranging in size from Fortune 500 corporations to small 10-person shops. He is an authority in Java and .NET technologies, particularly in the areas of Java/.NET integration (both in-process and via integration tools like Web services), back-end enterprise software systems, and virtual machine/execution engine plumbing.

Overview:

Ever wished you could just put parts of your program in end-users' hands and let them build the infinite little changes they want? Ever thought about how you might make your application more robust by writing less code, not more? Embed a scripting engine into your application--complete with the safeguards necessary to ensure that users can't do anything they shouldn't be able to--and release yourself from the Principle of Perpetual Enslavement. This presentation will describe how to embed a scripting engine, discuss the pros and cons of the various ones available, and how to put enough safeguards around the scripts to make sure that your application can't be hijacked by bad users' scripts.