Editorials

Version Does Matter

by Ben Taylor

Version Does Matter
I received an Email from Michelle Poolet today. Michelle has a business providing support for SQL Server databases. As a result, she works with many different customers, each with unique problems and solutions. They also have many of the different versions of SQL Server available from Microsoft today.

Michelle writes:
Let me tell you about an incident that happened yesterday, and I’d like to know if you’ve heard of this happening more frequently, now that we’re supporting so many versions of SQL Server.


Company A contracted company B to write a new web application and database. When the contact was signed, both Company A and Company B were using SQL Server 2005 as a development/production platform.


Recently Company B has been delivering interim results that fail to even load on Company A’s SQL Servers (still 2005). You know what happened…at some point in the last 2-3 months, Company B upgraded its computers to 2008R2. Company B assumed, since the database they’ve developed was listed as "2005 compatibility mode" on their systems (2008R2), that a backup would restore onto a 2005 instance of SQL Server. This was a critical miss-assumption, and which, obviously, they did not test before sending to Company A.


In addition to this lack of fundamental understanding regarding the differences between the various versions of SQL Server, Company B did (in my mind) the unforgivable…they used R2 functions and features (data types, specifically) for a web app and database that would be deployed in a 2005 environment. Of course, nothing works now.


Production instances of SQL Server are the reality we all face. I, personally, just retired my last copy of SQL Server 6.5 in the spring of 2011. I support a spectrum of production versions, from 2000 (yes, you read that right) through 2008R2…that’s 4 versions, with 2012 right around the corner.

Here are process steps I have found useful when working with outside teams, regardless of who employs the team members.

  1. Packaging – Make sure your package works in the targeted environment. Ok, Company A developed in 2008 RD. They performed a database backup. But, they clearly did not attempt to restore the database in a 2005 instance of SQL Server. So, they have no validation of their package.
  2. Deployment – Don’t deploy software upgrades directly in your mission critical production system first. Deploy to QA system first…if all is well, then you push to your production system. I try to follow this process even with completely internal software development. Dev->QA->Production. Always! You may do it fast, but you always follow the same process.
  3. Back Out Plan – Whenever doing a production deployment, Always have a back out Plan with specific steps already determined (and hopefully validated). Then when things blow up in your face, you have don’t have to scramble to figure out how to back it out.
  4. If possible, do not program in a version of SQL Server for a target implementing an older version. I’m not talking about service packs. I’m talking about versions that have specific feature differences. It is simply a bad practice. This can even be the case when you are working on the same release. I know a fellow who lost 3 months of development time because he used fuzzy logic in an SSIS package, because the developer version of SQL Server supported all the enterprise functionality, only to find the SQL Server Standard 2005 didn’t support fuzzy logic in SSIS.

Michelle asks:
With the plethora of SQL Server versions, have you seen an increase in failure to pay attention to one of the most basic rules of programming, which is, what platform am I developing for? Have you seen this on the increase? Or is this just business as usual? You can share your thoughts by sending me an Email at btaylor@sswug.org.

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
How to Shard MySQL Databases (Part 2)
Learn how to complete the database sharding process by splitting data and updating your application code.

Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ

SQL supports a concept called all-at-onc… (read more)

Featured Script
How to strip selected chars from a string (numerics or alpha)
demo how to strip all non-numeric values from a string, or the reverse… (read more)

Tags:
Author: Ben TaylorBen is a Relational Database veteran starting in the early eighties on DEC Vax database systems such as Bloom and Harvest. He has specialized in SQL engines for more than two decades, with a primary focus on Microsoft SQL Server. Ben has worked with small and large databases for both OLAP and OLTP working on systems for many industries such as Medical (Medical Claims and Eligibility Warehouse, HMO Provider Management, Enrollment), Financial (credit card processing, mortgage, stock portfolio management), Manufacturing (Assembly Line and Product Assemblies), Sales, Auto Auctions and more.