VSTS:DB

squeaky wheel… yada, yada

Well, I guess if you complain about something long and hard enough, things happen.  Sometimes, things happen that have nothing to do with your complaining, but the coincidental correlation is so strong that your mind draws associations for it (which sometimes leads to fodder for blog posts).

BTW, sorry for not posting much lately; my personal life has gotten rather complicated lately, so I’ve been avoiding the computer after hours.

Anyway, I’ve been struggling for some time now with Visual Studio for Team Systems: Database Edition, particularly in a scrum environment, and it just seems that I’m not making much headway with it.  It’s just a foreign model of usage, and I’m still struggling with the tool. It’s kind of like learning to drive a car after driving a golf cart; the basic principles are the same, but there’s a heckuva lot more options.

Anyway, Shawn Wildermuth to my rescue; his company, Agilitrain, is hosting a series of workshops on Agile Database Techniques using Visual Studio 2008 Database Edition; I told my boss about it, and even though training money is pretty tight right now, I got approval to go within minutes (that says a lot about my boss, or it may say something about his opinion of me and the need for training).  Anyway, here’s the course description:

Agile Database Techniques

This course provides students with the knowledge and skills to properly manage the SQL Server database development lifecycle in an agile environment. The student will learn how to manage changes to the database structure, ensuring quality through T-SQL unit testing, and how to automate the building and deploying of SQL Server databases. Intended for developers and administrators already comfortable with the concepts of designing, programming, testing, and deploying SQL Server databases, this course focuses on using Visual Studio Team System 2008 Database Edition to maximize productivity while minimizing mistakes.

Price: $1995.00 (USD)
Pricing may be different in locations outside the United States
Language(s): English
Length: 3 Days

Pre-requisites:

Before attending this course, the student should have experience developing and maintaining SQL Server 2000, 2005, or 2008 databases. It would also be beneficial if the student has worked in a team-based software development project and is familiar with their organization’s Software Development Life Cycle.

I’ve also been notified that Nob Hill Software has released their latest version of Randolph (an automatic versioning software for databases) with full support for TFS.  Unfortunately, I haven’t had a chance to install and test it yet (I’ve actually been working on an Analysis Services project; looks like I’m becoming a BI guy), but once I do, I’ll post a review here and at the AtlantaMDF Review site.

SCRUM, Source Control, and the SQL Server Developer (Part 2)

So my last post focused on the modifications my shop has made to our implementation of Scrum without a lot of details about how we manage our code changes.  This post is intended to explain how we set up source control to support the scrum process.

Source control is one of those poorly-defined practices; it’s something we know that we should be doing as a developer, but it’s not always done as a DBA.  In fact, I would guess that many shops that have DBA’s doing most of their reporting and data integration tasks don’t practice source control methods at all; if they do, it’s typically in the form of scripting out the entire database or relying on differential backups.  Separating the data structure from the data itself is often difficult to do, so I am sure that many teams don’t do it at all.

We’re currently using Visual Studio for Team Systems with Team Foundation Server as our source control repository; it’s been a steep learning curve for those of us who came from a Visual SourceSafe background, and applying that knowledge to the unfamiliar context of a new UI (VSTS:DB Pro) and concept (source control for databases) has been more than challenging, particularly since we’ve adopted a new development method (Scrum) as well.  It’s take a lot of discussion (sometimes quite heated) to get where we are today, especially since there’s not a lot of “best practices” discussion for source controlling SQL Server out there.

The biggest challenge has been the concept of branching and merging; TFS recognizes that you may have multiple development lines going on, especially among multiple developers.  When do you split code out to work on it?  When do you put it back together?  How does this affect deployment of new features, vs. the release of patches to fix the currently deployed line of code?

For us, we decided to address our database source control thusly:

  1. We set up the initial databases in source control in our Main branch.  The Main branch is supposed to represent the heart of our code; it’s the final resting place of changes before they go to QA. 
  2. From Main, we branch off a line of code for each PBI (Product Backlog Item; see my first post) that we’re working on.  If a PBI spans multiple databases, we include a copy of each database under that PBI.  Our cookie trail in TFS looks something like this:
    • Main…Database1
    • Main…Database2
    • Development…PBI 1…Database1
    • Development…PBI 1…Database2
    • Development…PBI 2…Database1
  3. Once a PBI is ready to be deployed, we merge the databases for that PBI back to their original sources; if another developer merges in their changes which would cause conflict, those conflicts have to be resolved before we allow the final merge (this means that if Dev1 working on PBI 1 has some changes that would affect Dev2 working on PBI 2, those changes are discovered at the second merge).
  4. We also have a line of code that represents Productions deployment; at the beginning of each Sprint, we branch from the Main line into Production and then do a schema comparison with production to ensure that the Production line actually resembles Production.  In case of a needed patch, we patch the production line, deploy it, and then merge those changes back into the Main line.  If there are conflicts, we have to find them and resolve them.

There are some drawbacks; merging a patch into Main doesn’t always go smoothly, since we have to track down who is responsible for what changes.  Documentation in the procs helps, but we need to do a better job (especially when those changes involve objects that are not easily commented; like tables).  Furthermore, when it comes time to deploy from the Main branch, if QA decides that a feature isn’t ripe yet, then we have to do some legwork to back it out.  All in all, however, it works, despite the bumps along the way.

I’d be interested in hearing how others are doing source control for their database scripts; please feel free to comment below.

Death by a thousand cuts…

This is has been an awful week; things have just not gone as planned from the get-go.  I’m gonna run through several issues in this post, and perhaps someone will find some value in them; lessons learned may help others avoid the pain of my ways.

 

VSTS: DB Pro

This week, I’ve spent way too much time just trying to figure out how to use Visual Studio; I’ve mentioned in previous posts that I’m a database developer, but that I’ve spent most of my career working with the standard SQL Server DBA tools: Query Analyzer & Management Studio.  When we migrated to TFS, my manager encouraged me to adopt VSTS:DB Pro as a method of developing, and I’ve put some real effort into learning how the system works.  I’m slowly becoming accustomed to it, but there’s still some very quirky things that I’ve yet to figure out.

Issue 1: Logins.  Whenever I import a database into a database project, I get a long list of errors because we have Windows accounts associated with users in the database; since those users don’t exist on my laptop, the project throws all kinds of ugly errors.  Googling the error reveals very little information; the only method I’ve found to eliminate the errors is to drop the associated users and schemas, which of course, can lead to VERY BAD THINGS in deployment scripts.  I’m sure there is a method to resolve it; I just haven’t found it yet.

Issue 2: The Silent Source Control Failure.  Most of my projects involve references to vendor databases; we have a policy of not modifying vendor databases, so when I need to write a stored procedure that accesses data from a third-party source, I create a linked database that sits on the server beside the vendor database, and include my proc in there.  I don’t touch their code, but am able to customize my queries to pull data.  When setting up projects in VSTS:DB Pro, I usually make my custom database the primary project, and include a database reference to a project based on the vendor db.  This usually works out OK, but I ran into an error yesterday where the project wizard would complete, but the project was unable to load.  Nothing I did would fix it; I finally removed the vendor db from the solution, and was able to load my project (albeit with tons of errors because of the lack of references).

The problem? One of the stored procs in the vendor db has an extremely long name (nearly 100 characters); combined with the path to the workspace, the name of the file exceeds the 255 character limit for Windows file management.  TFS apparently doesn’t know how to tell me that the file name is too long, so it just refuses to open.  Try wasting 2 hours of precious development time tracking that down, and you can imagine how frustrating that is.

 

Haste makes waste.

A few months ago, we made a decision to adopt a version of Scrum to standardize our development processes; we’ve made up some of our rules along the way, and for the most part, we’ve done OK with it.  However, the pressure to show progress and meet the needs of the organization means that sometimes I’ve made the wrong decision in my desire to please our Product Users.  One case was today;  one of our rules is that we a) strive to only deploy once a month (at the end of a sprint), and b) if we need to deploy an emergency patch, we only deploy on one day of the week (Wednesdays).

One of our users found an issue, and a production DBA researched it and made a recommendation for a relatively simple code change; I was out of the office Monday, so I only saw the recommendation on Tuesday.  The issue was important, and the code looked good, so I thought I could get away with rolling it out today; unfortunately, I didn’t do due diligence on the code, and neglected to check for dependencies on that particular proc.  We deployed my change today at 8:30 AM; by 3:30 PM, I was rolling it back.  Luckily, the only side effect was that we generated a few extra tickets for our clients, but still, it was a painful experience.

The one thing I want to carry away from this experience is that as our system becomes increasingly complex, the less likely it is that a simple change can be made in less than a day. The code may be simple, but the context of the code in terms of surrounding processes SHOULD warrant time for thorough review.  I need to quit reacting to the deemed urgency of the issue, and think about long-term stability instead.

 

Even the best deployments can have issues.

Overall, I think our deployment process works well, but even a good process can have points of failure.  Even though we have several sets of eyes examining all of our code before it goes into production, things can still slip by.  For example, at 3:45 this afternoon, I was informed that a stored procedure in production was not returning all of the data; the procedure pulls data from a third-party database, and makes certain assumptions about the nature of that data.  In this case, we have a standard naming syntax for all of the devices we manage; ClientID, followed by a space, then device name (e.g., 999 Joe’s Server).  The stored proc had a where clause that ended with LIKE ‘[0-9][0-9][0-9] %’; the missing row of data had a device name with the space omitted.

Now, it wasn’t entirely my fault because I had written the code to spec; however, I should have anticipated that the naming convention could be slightly off (and still be parseable; if they had a 2-digit client id, it would be a different story); in the end, our company looked bad to a client because of  typo, and that’s not good.  Easy to fix the code, but hard to regain the trust of the client.

 

Tomorrow WILL be a better day.

I’m not much for mumbo-jumbo, but I do believe that life is what you make of it.  If I go back to work tomorrow and assume that it’s going to be a bad day, then I’ll probably be proven right.  I’m changing my attitude as of this moment, and I choose to believe that the priority issue that I address tomorrow (while it may not be the issue I want to work on) will be the issue I was meant to work on. 

Rube Goldberg would be proud…

This post is a bit of a rant; I spent the whole day trying to resolve a curious chain of events.  Basically, I wasted a day of development troubleshooting something stupid; hopefully, others will learn from my mistakes, and avoid the pain and suffering I experienced.

It all started with SQL Server 2008; we’re looking at migrating to it sometime this year, and I finally begged the production guys into letting this developer get a copy of it installed on my machine.  Seems to make sense, since I don’t know, I WRITE THE CODE THAT THEY RUN.  (sorry, a bit tense).  Anyhoo, I started the install.  All was going well, apart from the unusual hiccup with my Logitech web cam.  I eventually got it installed, started to play around with it, and then I noticed that I only had 1 GB of disk space free.

Sidebar: remember when 1GB was a HUGE amount of space?  Unfortunately, it ain’t much now, and my laptop is starting to suffer from too-much-crapitis.

Realizing that I was running out of space, I made the extremely stupid decision to uninstall SQL Server 2005 from my hard drive and free up a few gig.  SQL 2008 should be sufficient right?  I do very little development on my local box (we have dev servers for that), so I thought I would be safe.  How wrong I was.  The uninstall took about 30 minutes, a reboot later, and I get bugged by Dan about an issue with one of the database projects I checked in using VSTS:Database Professional.  I fire up Data Dude, and basically my laptop shot me the bird.  Apparently, VSTS REQUIRES an installation of SQL Server 2005 to run; SQL 2008 won’t do.

Sidebar: I realize that there is a new version of VSTS:Database Professional that doesn’t have this requirement, but until it’s official, I can’t have it (company policy).  Make it official, Microsoft, and you won’t get rants like these.  I’m warning you!  (insert mental picture of an ant shaking his fist at an elephant).

Since I had limited space to install, I chose to install SQL Server 2005 Express edition.  All went well, until the installer required a version of MSXML 6, and then it puked.  Why?  Because Windows XP Service Pack 3 installs a higher version of MSXML 6 then SQL Server 2005 does, and the SQL Server 2005 can’t write over the newer version, but nor will it accept the newer version as valid.  This is the computing equivalent of saying “I like cherry pie and I like whipped cream, but I don’t like cherry pie with whipped cream”.  I couldn’t install SSE 2005 until I uninstalled MSXML 6, and I couldn’t uninstall MSXML 6 without uninstalling Windows XP SP3.

Sidebar: Insert mental picture of ant and elephant again, only this time the ant is banging his head on the desk and crying while the elephant leaves a large steaming deposit nearby; the elephant’s not cruel, just oblivious to the ant’s peril.

Luckily, I found this post at support.microsoft.com, describing the use of the Windows Installer CleanUp utility to remove MSXML 6, without uninstalling XP SP3.  Dodged a bullet there, I thought to myself; instead, I stepped into a lawnmower blade.  I got SQL 2005 Express installed, fired up VSTS:DB Pro, and realized that I HAD THE WRONG VERSION (missing service packs; I had version 611, VSTS:DB Pro needed 612).  No big deal, right?  I’ll run Windows Update, get the missing service packs (since I was now on an old version of MSXML 6 anyway); bad idea.

Apparently, there’s been a lot of patches since XP SP3 was released, including a Service Pack for SQL 2008 (the cog that started this pinwheel); 900 Meg of patches to download, which wouldn’t be such a big deal except for the fact that my company has my connection capped at 512K.

Sidebar: The ant has just realized that it’s not entirely the elephant’s fault.  The queen ant has also made some strange policy decision that must be followed.  However, it’s much easier to blame the elephant, because it’s lonely without a job or an ant hill.  Stupid elephant; wise queen ant.

Anyway, at 3PM this afternoon (I clock out at 4PM),  I was able to answer Dan’s question.  Looking forward to another productive day tomorrow.

Visual Studio For Team Systems: Database Professional

I’ve been struggling with VSTS:DB for some time; I love the idea of source controlling my development databases (right now we’re just using backups to prevent code loss), but I’ve not been able to really get my head wrapped around how VSTS:DB works. Even though I consider myself a developer, I guess I’m still a DBA at heart. Visual Studio is like a foreign language to me; I can pretty much figure out the equivelent of “where is the bathroom?”, but I wouldn’t consider myself fluent in it.

It’s obvious that VSTS:DB was designed for developers; they use VS terminology like solutions, projects, and references, but leave out common administrative tools like execution plans. The goal is to develop “off-line”, and then apply changes from source control to your development environment (so that additional application testing can be done); the model is that the source control is the truth, not the dev environment. Of course, if you’re at all remotely interested in this topic, there are better resources than I that describe this (but not many; see below).

Here’s the challenges I see with VSTS:DB

Poor documentation: I found a couple of forums, and one book that actually mentions DataDude. The online help files are OK, but help files aren’t how-to’s; they’re designed to respond to a specific question.

Reverse deployment: The product assumes a particular model of deployment; changes flow from a dev environment to source control, and are ultimately deployed to a production environment. However, in our shop, we often face the challenge that a stored procedure may need a performance tweak (such as an index hint); our datasets are so huge that the dev environment can’t always see those scenarios. A production DBA will make a change to code, but that change doesn’t get propogated back into source control, and we lose vision on it (unless we do a manual review of the change scripts and catch it).

The database-as-project metaphor: When application developers work with Visual Studio, a project represents a compiled piece of code (like a dll or an application). If they make changes, redeployment means replacement; the old dll is overwritten by the new dll. Databases obviously don’t work that way, and even though the tool seems smart enough to change CREATE scripts to ALTER scripts, I’m still not sure that’s the best way to deploy something. What if you’re working on some stored procs that aren’t ready to deploy? You can manage what gets deployed by doing a schema comparison, but it just seems klunky.

Adding features mid-release: Really not a challenge per se, but it is one of my pet peeves. Microsoft just released an RTM version of the GDR (General Redistribution Release) in November. According to the developers, it’s a “complete new release”, with several fundemental changes to the architecture. Although the official version is not out yet, it does mean that we have to be very precise when comparing what version of VSTS:DB you’re working on (are you on 2008 or 2008 GDR?).

Anyway, I hope that I can work my way through some of these challenges, and I’m sure that my perspective will change as I become more familiar with the way the VSTS works, but for now, I’m frustrated. I’m hoping that as I being detailing this journey, those frustrations will resolve themselves.