Thoughts grow to tweets, then blogs, then they just die

I’ll often want to Tweet something, but feel the need to explain further in a second Tweet. But something in that will need explaining, so it occurs to me that I really need a blog post. But then I realize I should probably post this as 2 or more separate posts to isolate ideas and keep them self contained and just link between them.

Then I realize I’ve got work to do, and drop it till I have time …
… which never comes and my thought dies having never lived.

*Even this one tweet expanded into a blog post!

… so I can worry about curly braces

I just wanted to take this Remembrance Day opportunity to thank all the Canadian soldiers, past & present, living & dead, along with those of our allies, for your dedication & sacrifice.

Thank you for fighting for our liberties so we can worry about the more important things like; litterbugs, dynamic vs. static programming languages, and the endless irrelevant debates about curly braces.

Canadian National War Memorial - Tomb of the Unknown Soldier

This image taken by Andrew Moor

How to enforce a foreign key constraint against multiple tables

I am building a web app with Ben Alabaster, and one of the requirements is for the user to be able to flag items for moderators. So the user can flag entity A, entity B, entity C, etc…

So I created a single flag table.

Flag Table

Which I then tried to tie it to the entity tables, hoping for something like

Ideal Foriegn Key Relationships

Where all the foreign key relationships were from [flag].[entity_id] to [EntityX].[id]

Then when I wanted the top 10 flags from a particular entity (B in this case), I could run a query like

select top 20 e.[name], count(*) "count"
from entityB as e
   left join flag as f
    on f.entity_id = e.id
where f.entity_type='B'
group by e.[name]
order by count(*) desc

Unfortunately, if you were to create the above table relationship, and run the following inserts

insert into EntityA( id, name) values (1, 'EntityA');
insert into EntityB( id, name) values (2, 'EntityB');
insert into EntityC( id, name) values (3, 'EntityC');
insert into EntityD( id, name) values (4, 'EntityD');

The following statement

insert into flag(entity_id, flag_reason) values(5, 'Testing without a valid FK value.');

would fail as expected, as expected, with the following error. “The INSERT statement conflicted with the FOREIGN KEY constraint “FK_flag_EntityA”. The conflict occurred in database “test”, table “dbo.EntityA”, column ‘id’.”

But

insert into flag(entity_id, flag_reason) values(1, 'Testing the FK to entity A.');

would also fail, which was undesired, with the following error: “The INSERT statement conflicted with the FOREIGN KEY constraint “FK_flag_EntityB”. The conflict occurred in database “test”, table “dbo.EntityB”, column ‘id’.” +

So, my options with regards to referential integrity are :

1. Ditch the referential integrity, which I am vehemently opposed to. ++
2. Create multiple flag tables, each with the exact same schema, but a different Foreign Key relationship, which just seems wrong.
3. Managing referential integrity via triggers.

While I’m not a big fan of triggers, the ‘Managing referential integrity via triggers.’ option seems like the only tolerable one. So I added the [entity_type] column to my flag table.

Flag Table With Entity Type

Removed the relationships

No Relationships

And wrote the following trigger to manage the foreign key relationship.

-- =============================================
-- Description: maintain referential integrity on
-- a column which is a FK for different tables
-- =============================================
CREATE TRIGGER flag_entity_id_fk
ON flag
AFTER INSERT,UPDATE
AS
BEGIN
declare @entity_type char(1);
declare @entity_id int;
declare @cnt int;

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 -- get info
 select @entity_type=entity_type,
   @entity_id=entity_id,
   @cnt=0
 from inserted;
 
 -- check if records exist
 if 'A' = @entity_type
 begin
  select @cnt=count(*)
  from entityA
  where id=@entity_id;
 end
 else if 'B' = @entity_type
 begin
  select @cnt=count(*)
  from entityB
  where id=@entity_id;
 end
 else if 'C' = @entity_type
 begin
  select @cnt=count(*)
  from entityC
  where id=@entity_id;
 end
 else if 'D' = @entity_type
 begin
  select @cnt=count(*)
  from entityD
  where id=@entity_id;
 end

 -- records exist? exit
 if 0 < @cnt
 begin
  return;
 end

 -- no? error
 raiserror( 'Unable to find foriegn key match on entity type ''%s'', id ''%d''.', 16, 1, @entity_type, @entity_id);
 rollback transaction;
END

Now, when you run

insert into flag(entity_type, entity_id, flag_reason) values('B', 5, 'Testing without a valid FK value.');

The trigger doesn’t find a match in the appropriate table, rolls back the insert, and gives you a descriptive error message.

Unable to find foriegn key match on entity type ‘B’, id ‘5′.

However, a good value is accepted.

insert into flag(entity_type, entity_id, flag_reason) values('B', 2, 'Testing without a valid FK value.');

I’m still not happy with this approach, but it does seem to be the lesser of all the evils. Please let me know with a comment if there is another option I’ve overlooked. Thanks.

* Frankly I was surprised it even compiled.
+ Unless of course you were unfortunate enough to test this in a coincidental situation where all tables happened to contain the id of every test you ran.
++ Yes ‘vehemently’

EDIT (11/10/2009) : It just occurred to me that this article does not take into account what would happen if the entity tables were to delete a row which this table was pointing to. When I designed my tables this was taken into account, but since we are not planning to allow actual deletions, it was left out. However, if you were to implement this strategy, where entities could be deleted, a delete trigger would need to be created for each entity table.

How to hook up NUnit as an option in the ASP.NET MVC application wizard

Ben Alabaster and I have started a project, lets call it Project X for now (original eh? .

Anyway, we have decided to blog about its development. I will be posting something in the next few days, and Ben just published his first post “How do I hook my version of nUnit into the ASP.NET MVC template?“. Here’s a blurb:

If you’ve been looking for a way to integrate nUnit into your ASP.NET MVC 1.0 template – that is, when you create a new ASP.NET MVC application and it asks you if you’d like to create a test project, nUnit shows up in the list along with the usual Visual Studio Unit Test option.

There are a number of longwinded ways of doing things. There’s also a relatively simple way touted on the Visual Web Developer Team Blog which I’ll spare you the headache of running it and finding the same problems I did….

Ben’s post can be found on his blog endswithsaurus.com

Investigating the relationship between estimation accuracy and task size

Yesterday on StackOverflow Johannes Hansen asked

What is the acceptable upper limit of time allocated to a single development task?

I answered with

If you track your estimate/actual history, you can probably plot hours by accuracy and figure out exactly what number is appropriate for your team.

My advice sounded so good I thought I’d try it myself. So I opened bug tracker where I keep track of my probable and actual times and exported my closed bugs to Excel. I cleaned up a bit, by removing any rows with either a 0 probable or actual time, then created a chart.

Now when I conceived of this idea, I was expecting something like

Well I wasn’t expecting the plots to be that dense, or to accelerate above 200% so fast, but let’s just say, that general look would have been pleasing to my eye.

Here’s what I got instead.

Now, I’ve got to say, is NOT what I was expecting at all. You can kind of see a very dense block under 4 hours and 100%, but doesn’t tell us very much with regards to the relationship between estimation accuracy and size of the tasks. So, I then threw a Linear Regression Trendline on the chart hoping it would illuminate an ascending trend. Instead it contradicted my assumptions by declining, suggesting the larger the task, the more accurate I am … which isn’t true at all.

Maybe it’s the outliers. Maybe it’s the weird changes outside of normality causing it to look so horrible. So I sorted the data by the accuracy percentage, dropped the top and bottom 5 percent, redrew the chart and got this.

Still obvious relationship between the estimated task size and estimation accuracy. But at least my trendline is no longer declining. By flat lining, it’s now suggesting there is no relationship between estimation accuracy and task size.

… hmmm … bugs are included in my data. I wonder if that could be having an effect? I’ve been estimating approximate times bugs will take to resolve for my manager. Most of these bugs have been estimated before even investigating the cause, so that’s not really the same as estimating a defined task. What if I remove them?

I went back to my original data dump, removed all bugs, tickets, and questions so I was left with only new tasks and changes. I again removed the bottom & top 5% and recharted.

Well, I’ve finally got an ascending trendline suggesting my estimates are weaker as tasks get bigger, which is what we expected to happen.

Conclusion: I’m still not very happy with the scatter chart. I still believe it should look closer to my initial assumptions of what this chart should have looked like. This suggests to me that I need to take another look at my data collection if it’s going to be useful to me at all.

Feed back and constructive criticism welcome.

Two Rules for a Happy Consulting Client while Charging an Hourly Rate

Some of my projects are a fixed rate and some are hourly. If the scope of the project is small enough to accurately estimate, risks are minimal, and nailing down requirements is practical, I may propose a fixed price. Working on fixed price projects are a piece of cake, I go home, work on it, provide periodic updates via email, phone, or in person. When I finish it I install it and give them a CD with all the binaries, documentation, and source code.

However, I’ve found working on an hourly rate to be a little different than doing all the above while just counting my hours. There is a lot of distrust with hourly professionals which is only compounded with a mysterious process like software development. After all how do they know you aren’t home watching soap operas, working on your own projects, the project of another client, or even sitting on the beach? How can they tell they aren’t being over charged? The problem with trust, is even if you are honest, a false perception can still destroy the relationship.

I know this too well. Once in the late 1990s I was working from home, charging hourly, and I got a call from one of the newer partners of my biggest client (only client at that point). While talking I rebooted to clean out my system* and when the system came back up it played the 20th Century Fox theme, a configuration change I thought was very cool, and he said ‘Are you watching TV?’ I said ‘No. Why?’ … Seriously, I didn’t even making the connection. Two days later I was called in for an unscheduled meeting where my invoices were questioned, I heard a lot of “we’re not accusing your of inflating your hours…”, and the next day, feeling insulted and unappreciated, I resigned. I didn’t make the obvious connection until much later. It doesn’t matter that I was under charging both in terms of my rate and what I charged for, and would never inflate my hours! It doesn’t matter, because the perception was corrupted. It was a tragic misunderstanding since I loved working there and provided a competent, yet naively discounted service.

That’s when I came up with rule # 1
1. Work at the client site when charging hourly.

While following this rule gave my clients assurance that I was actually working, it still left them in the dark as to my effectiveness and what exactly I was doing. I soon realized that working on site isn’t enough. I need to communicate my challenges and accomplishments more effectively as well.

So I came up with rule # 2
2. Provide detailed invoices.

Until this point, my invoices had the typical, single ‘All services rendered’ line item. I changed it to include every single thing I did. I’m not kidding. Here is a sample from an invoice I wrote a couple years ago, after establishing this rule:

---

App F – Investigate & Fix bad XXXXX data

App A – Altered data tables to use the datetime data type for the create_dt & update_dt fields. This is needed to have a granular time for list updates. Communication /w Coder X and answering his questions. Conversation with Coder X about Stored Procedures. Discussions with Coder X about the stored procedures. Generating Business objects.

Feature X – Add Save & Save to Profile to the IFeatureX specification. Alter the Feature X specification document to include autosuggest searching ability in the IFeatureX interface. Reviewed Coder Xs progress.

Planning – Discussions with Manger Y about the purchase of App R, project status, and got permission to take the API documentation for App R off-site for review.

Website – Added new user.

---

Notice how it’s written for the target audience (a non-programmer, IT manager in this instance)? Notice all the detail? Notice I explained why I was changing the data types? Notice the descriptive verbiage (investigated, fixed, altered, reviewed, etc…)? Notice how even something as basic as adding a new user to the website, a 5 minute task is included? Notice I confirmed my permission to take confidential documentation off premises?

There is some terminology which might be unfamiliar to my client, but these terms were introduced to the client before hand, so they understood every thing said on that invoice when it showed up in their inbox.

All this detail may seem like overkill, but it gives the client a level of transparency into the mystical world of software development. It provides a record of activities, allowing the client to feel in control, knowing the priority decisions they made are being acted upon as agreed. It reveals a shadow of tangible evidence on an outwardly invisible service. But most of all, it gives the client comfort and I believe raises the level of trust.

I should also point out, the invoice sample above is not based on a 50-60hr work week, but from a week where I put in 14 billable hours. A 50-60hr work week would usually be 500 words or more.

… hey! Don’t freak out, you can write a detailed invoice without spending your weekend on the first draft. Here’s a brief list of things you can do to reduce the time you spend writing monster invoices:

1. Maintain a detailed time log filled out as you do things. This takes discipline, but almost no time.
2. Make detailed source control comments and do a report at the end of the time period.
3. Make detailed bug tracker notes and list all the bugs/tasks/tickets you worked on during the time period. You can just list the bug ids, but I usually write something like ‘Investigated and resolved ticket # N – User receives 404 error when clicking customer link’.

The key to any of these is to write with your client as your intended audience, this way you just cut and paste into your invoice.

More than a decade after reluctantly getting into consulting, I’ve realized that Perceived value is directly related to the quantity and quality of communication with the client. An invoice can be a key communication device with your client and a powerful marketing tool if you make the effort.

*It was Windows 95 after all. At that point I was rebooting 10 times a day at least.

Copyright © John MacIntyre 2009, All rights reserved
WARNING – All source code is written to demonstrate the current concept. It may be unsafe and not exactly optimal.

A very simple Pair Programming IP Rights Agreement

Once in 1998, I sat down with my manager (the only manager I’ve ever had who could program), and we banged out some code for about 2 days. It was a very fast paced synergistic activity where one idea fed another and at the end of 2 days our initial idea morphed into something completely different and a heck of a lot better.

Well, tonight 11 years later, I’ve convinced my colleague Ben Alabaster to come over and pair program. I don’t know how it will go, I’ve got high hopes, but I am confident at the end of the night both Ben and myself will be a little better as programmers, and might have even started something worth finishing.

But two things I do know: 1) if we come up with something good, we’re both going to want to use it. And 2) if we ever get to the point of needing an agreement outlining our IP rights, it will be too late to draft one. So, Ben & I threw together some basic rules yesterday. Frankly, I’m surprised I couldn’t find any on the net already, maybe I over think this stuff more than most people, or perhaps it’s because I just didn’t look that hard.

So here’s what we agreed to:

1. Each of us, individually, is free to use any programming concept shared, discovered, or created.
2. Each of us, individually, is free to use anything we cocreate as part of a larger project with a significant amount of additional functionality. This can be a personal project, business project, or consulting project.
3. Each of us must agree to release any code or binaries either as a commercial product or open source. Each of us will share any credit and/or financial profits equally.

I’d love to hear other people’s perspective and comments about this.

Copyright © John MacIntyre 2009, All rights reserved

11 Personal Programming Assumptions That Were Incorrect

Today I got side tracked and spent an unreasonable amount of time on StackOverflow.com. One of the questions I was looking at was What is your longest-held programming assumption that turned out to be incorrect?

Many of the answers immediately resonated with me, like Instantsoup’s answer That people knew what they wanted and JohnFx’s awesome answer about comparing his knowledge to the collective knowledge of all other programmers. Other answers reflected a poor initial understanding of the language or technology, many of these I was fortunate enough to not relate to.

As you can imagine, I immediately started coming up with my own answers, so I continued reading to make sure they weren’t already there. But as I read, I came up with more initial assumptions which proved to be false. I thought I’d pick out the best, and answer with that one, but realized I had a whole blog post!

So without further ado; here is my list of assumptions about programming and the industry which proved to be incorrect:

1. The customer and user are the same person.
   In consulting with custom in house software this is (almost?) never the case. The customer has their eye on the budget and many won’t give 2 cents to make something easier for the user.
2. You isolate and kill all bugs without exception
   Truth is; there will always be bugs, and what gets fixed is an ROI decision.
3. Writing beautiful software as an act of craftsmanship
   It’s taken a LONG time for me to get it through my head that it’s just another financial investment, where you want to get as much as possible while giving as little as possible. However, although I know it intellectually, I still don’t ‘get it’.
4. Working 24/7 would be rewarded
   Truth is, working all the time will burn you out, lower your productivity, and cause you to make stupid mistakes which reflect on your professional abilities.
5. Vendors can be believed
   In my experience, don’t believe the product will integrate as seamlessly as they say without getting their API documentation and building the whole integration mentally first.
6. You are not actually working from the monitor.
   With today’s GUI platforms, this is largely abstracted out, so you can actually program believing that a textbox is a physical entity. But when you are managing your own graphics, that textbox isn’t going to capture your keyboard presses and display them, unless you provide the illusion. It’s actually up to you to capture the keyboard input, and adjust the memory buffer containing your screen image with the newly typed letter in the control. This was a real mind bender for me, when I first got into this.
7. That I wasn’t a very good programmer
   Like JohnFx’s answer, I was unsure about my abilities. I don’t have a C.S. degree, and was overwhelmed by the depth and breadth of the knowledge out there. But the more people I meet, the more I realized, I might actually be pretty good. Don’t get me wrong, under malicious attack, there are many who could expose and exploit the holes in my knowledge, but in my general area (business software), I’m finding that I’m not too bad.
8. You need to / should grok a language or tool before you even start.
   Groking is a powerful way to start with a new language or tool. But in reality, especially with consulting, there isn’t always time. If you can hack something together in a completely unfamiliar language/tool/framework/paradigm/etc in 5 days, and it would take somebody who knows it cold 1 day, but it would take you a month to grok it … you hack. You may not want to, but from a business perspective … that’s the correct answer.
9. You don’t say you know something unless you’ve grok’d it.
   Well, you don’t need to have too many conversations with too many programmers to know this idea is not wide spread.
10. Other programmers saw beauty in their work.
    Personally, a well written piece of software is similar to a piece of art. It’s my form of artistic impression. It’s my baby. However, there are many people in our industry who do this 9-5, and appear to get no more enjoyment out of it, than I got out of a factory job I had in my teens.
11. The best programmer is recognized
    In retrospect this is almost laughable, at least in consulting! If programmer A writes a wiz bang sub system, takes 2 weeks, it’s flexible to extend, and the core system never requires maintenance. Programmer A would not be held nearly in as high regard as programmer B who takes 1 week, hacks together a piece of garbage which is a nightmare to extend, and is maintained with multiple bugs for it’s life time. This is unfortunate, but in my experience, only a very capable manager would know the difference.

Copyright © John MacIntyre 2009, All rights reserved
WARNING – All source code is written to demonstrate the current concept. It may be unsafe and not exactly optimal.

AboutMe.xml

I have a pet peeve and like most pet peeves it’s an irrelevant petty little annoyance, not quite a huge, humanity, oppressing problem.

My pet peeve is filling out the same information; name, address, city, etc… on paper forms. All that standard information at every doctor’s office, school, activity registration form for my kids, etc… I mean why do I need to keep writing this stuff? And why does somebody else have to take the time to retype it into their system?

Really! In all seriousness … what a waste of time! 5 minutes I’ll never get back, every time I start a new relationship with any organization.

But wait … I have a vision! Not a big glorious, save humanity vision, it’s more of a save each person 5 minutes of writers cramp, kind of vision. Yes! That kind of glorious vision!

I was originally inspired with this in the mid 1990’s. It started out as a question; why can’t doctor’s receptionist retrieve this information from the province when they scan my health card. But since the likelihood of getting the government to add an API for this is slim, it was reduced to something simpler. Like; Why can’t I hand the receptionist at my new doctor a diskette with an ‘aboutme.txt’ file on it, where she can load it into her PC, and give me my diskette back? This would free me up to spend an extra 3-5 minutes browsing the 4 year old magazines during my 76 minute wait to see the doctor.

Over the years, this vision has transformed from an aboutme.txt file on a 3.5” diskette to an aboutme.ini file on a diskette to an aboutme.ini file on a website to an aboutme.html file on website to an aboutme.xml file on a website to an aboutme.xml file on a USB memory stick. I’m not even going to go into ideas I had for RFID, bar coding, or carrying around printed labels in my wallet.

I’ll agree; this isn’t a big problem, but it’s an irritating little annoyance which can be easily overcome with a very simple programming solution. Surely, this would become a reality. Surely, this simple idea would be recognized by others, and implemented.

But alas, the obvious was never realized and because it would be impractical for any organization to expect you to have this aboutme.* file in your back pocket when nobody else had one or was asking for it. It’s the typical chicken / egg scenario; you need one to start the other.

But now I’m inspired again … by Open ID, or possibly another similar centralized authentication mechanism.

When I log into a new site via MyOpenID, I can chose the persona I want revealed to the site I’m logging into for the first time. One of these personas could easily contain standard address information like that required in the types of situations listed previously.

As Open ID reaches critical mass, with more people understanding and adopting it, providing and/or recommending software functionality to accept basic information via an Open ID login will become more realistic.

It’s easy enough to imagine a plausible working process, so I won’t bore you with that. However, there would be serious security concerns regarding logging into a critical authentication mechanism like Open ID from a shared kiosk, so the user would want to log in via their personal cell phone (or laptop or PC or …). And mainstream user adoption has a long way to go before something like this would even be offered, not because of technology, but due to slowly shifting paradigms.

There are obstacles to overcome before this could ever become a reality, but with centralized authentication schemes like Open ID, expecting most people to have an electronic copy of their basic information available will eventually be reasonable, and generic business software applications will start consuming that information.

And one day, hopefully before I die, I won’t have to fill out another one of those stupid forms.

Copyright © John MacIntyre 2009, All rights reserved

WARNING – All source code is written to demonstrate the current concept. It may be unsafe and not exactly optimal.

6 simple steps to a stress free database change deployment

A few years ago, I was contracted to work on an internal web application in a large Fortune 500 company. Our app was constantly in use, had thousands of users, was fairly active, and had a rather large database (one table had over 300 million records).

I was fortunate enough to work beside a DBA who had among other tasks, the responsibility of running deployment scripts against production. We talked occasionally, and he’d tell me about some of the deployment scripts he would run and the programmers he ran them for. He’d get programmers whose scripts never fail or report errors and would actually request the output text to confirm the DBA did their job. Then there were the programmers who would work against development, and when it was time to deploy, they’d attempt to remember all the subtle database changes made along the way and whack together an SQL script to repeat them. My friend was often rejecting scripts which were obviously never run before and sometimes he’d have to run expedited scripts, because the first was missing a necessary column or two.

Deployments can be a real headache at the best of times, but especially when schema updates to a production database are involved. Don’t get me wrong, you usually have a backup to fall back on, but how long will that take to restore? … Really, you don’t want to resort to the restore, have the database offline for that long, or have your name associated to it.

So gradually I evolved a process which has kept me sane and confident when deploying schema changes to production servers, even on large, sensitive, and active databases.

Here’s my simple 6 step process for deployment sanity. (FWIW-I’ve never called it my ‘6 step process…’ before writing this post.

Step 1 – Create a central shared directory for all scripts

The first thing you do is store all database changes as scripts in a central shared directory.

I usually store the scripts in a directory structure like
\projects\ProjX\DeploymentScripts\dbname.production\
\projects\ProjX\DeploymentScripts\dbname.archive\deployed.YYMMDD\

Where the ‘dbname.production’ folder stores currently pending scripts. ‘dbname.archive’ stores previously deployed scripts each in their own dated subdirectory.

Step 2 – Create scripts for all changes

Any and all changes are scripted and stored. If you use a tool to adjust your database schema, then you export the generated SQL and run that exported SQL instead of running the tool directly against your database.

Keep scripts in order and don’t change old scripts. If … ok, when you make a mistake, don’t try to go back to an older script and change it, just write new one to undo the mistake.

Each script is saved in the ‘dbname.production’ folder with the following naming convention:

dbname.##.user.descr.sql

where:

dbname : the database to deploy it to (mostly so you can see it in your Query Analyzer title bar)
## : the sequence number to deploy it in (just increment the highest number)
user : initials of the programmer who wrote the script
descr : a brief description of what the script does

Here are some examples
HR.01.jrm.AdjustedTblEmployees_AddedStartDateColumn.sql
HR.02.jrm.AdjustedProc_sp_GetEmployees.sql
HR.03.jrm.AdjustedProc_sp_ActiveEmployees.sql

Step 3 – Number your scripts

As you may have noticed in step 2; scripts are numbered in the order they are created so object dependencies exist when run. Do you like dependent objects to exist? Yeah? … me too.

In case you’re wondering, the single digits are prefixed with ‘0’ so they will sort in Windows explorer properly. And there is only room for 2 digits since you will rarely if ever create more than 99 scripts before deploying.

Step 4 – Keep track of what has been deployed to where

Ok, so you’ve got your ‘dbname.production’ directory with 10 SQL files in there, you’ve got 3 developers each with their own copy of the database, a testing and/or business validation environment, and production.

Sooo … what’s been deployed to where? It’s pretty easy to lose track of what got deployed to validation last week. … Sorry … did we deploy 5? Or did we stop at 4?

I toyed with a few different ways of doing this, but finally settled on a very obvious and simple solution; keep an empty text file for each database, updating the number each time it’s run. Since I want them to sort intermingled with the script files, I use a similar naming convention:
dbname.##.zzz–DEPLOYED-TEST

Here’s some examples mixed in with the previous examples:
HR.00.zzz–DEPLOYED-DevBox2
HR.00.zzz–DEPLOYED-DevBox3
HR.01.jrm.AdjustedTblEmployees_AddedStartDateColumn.sql
HR.02.jrm.AdjustedProc_sp_GetEmployees.sql
HR.02.zzz–DEPLOYED-TEST
HR.03.jrm.AdjustedProc_sp_ActiveEmployees.sql
HR.03.zzz–DEPLOYED-DevBox1

Notice how they’re sorted amongst the scripts and it’s obvious what got deployed? I could probably delete the entire text of this post, other than that sample, and you could figure out my deployment process. … But since I enjoy typing … I’ll continue.

When you finish executing your scripts, you would increment the number to reflect the last script run on that database.

Step 5 – Execute scripts in order

Obviously.

Step 6 – Post deployment clean up

When the scripts are deployed to production, you would create a new deployed.YYMMDD subdirectory with today’s date in the dbname.archive directory and move all the SQL files from the ‘dbname.production’ directory. Then Renumber the deployment marker files back to 00.

This can be incorporated into almost any database schema change deployment process, and isn’t specific to SQLServer. After all I started evolving this process while working on an Oracle database.

It has occurred to me that this may be an old school idea and modern database schema diff tools may provide a way to avoid this kind of preparation. But unless you are working as a sole developer; I can’t imagine a diff tool making your life easier than what I’ve outline above.

I hope your next deployment is less of a nail biter.

Copyright © John MacIntyre 2009, All rights reserved

WARNING – All source code is written to demonstrate the current concept. It may be unsafe and not exactly optimal.