Feb 05 2014

Book Review: The Phoenix Project

Let’s get this straight right up front, the thought of reading a novel that’s about IT is so repellent, so repugnant, just so horribly wrong, that it’s kind of hard to fathom why I would even attempt it. What’s even more difficult for me to fathom is how much I enjoyed this book. Which is a novel. About IT. I can’t figure it out. Maybe I need to start reading more IT novels… no. Let’s hope that’s not actually a thing. On with the review…

The Phoenix Project is a story about a mid-level manager in a large company who has been running part of the IT organization that is a bit of a backwater, maintaining old big-iron systems, VAX, that type of thing. He gets called into the CEOs office and is made VP of IT Operations. But it’s a company that, from what I can tell, has done just about everything it possibly can do wrong in IT. It’s actually quite frightening to read through. I kept flashing back to some of my own experiences and some of the “interesting” choices made by the people running them. I won’t bore you with the entire plot. The gist of it is, things are awful, our hero goes on a quest, vanquishes a dragon and gets the girl. Well, anyway, the IT operations management version of a quest and a communications & process dragon and he was already happily married so there was no girl to woo, but hey, the good guys win, eventually. I won’t reveal anything else about the novel itself. Go read it.

The Phoenix Project is not simply a book though. This is meant to be an introduction to a concept. That concept has a name, frankly, one I hesitate to use because it outright smacks of catch-of-the-day marketing speak, but it does have a useful shorthand ring to it: DevOps. That’s right. This is a book about getting developers and system administrators and DBAs and support people and auditors to hold hands and sing songs. Well, or, just maybe work together in a more efficient and useful fashion. The thing is, it makes sense.

The book lays out all the issues that come about when IT & Devs don’t get along. Each side feels as if the other side is just simply out to get them. Weird rituals get established to hand off software between groups rather than working with each other right from the start of development. The book really rings true. You get a sense that the people who wrote it have been there, and done that, in regards to watching IT shops suffer. Fact is, they have. Gene Kim, Kevin Behr and George Spafford are IT pros and consultants. They’re coming from knowledge and understanding of the suffering that occurs daily within lots and lots of IT shops. They’re making an effort to reach out, to help out. The Phoenix Project is part of their efforts to grab the attention of as many IT people as possible.

It works.

I spent years at my previous job trying to do a better job interacting with the development teams (I’m sorry to say, to mixed results). But I recognized that was what was needed (even if I wasn’t the right person to make it happen). Since starting my other job, we’ve been even more aware of just how important it is for developers and DBAs and all the other IT specialists to find mechanisms to create a straight path from development to production. It’s something we have to do in order to make our businesses that we support more able to respond to the shifting business environment in a (pardon the word) agile fashion. It’s about working towards Continuous Delivery (another book I’m starting). It’s about, until we get a better word for it, DevOps.

This was a good read. It was also a quick read. I finished all 300 pages in about two days. I liked the characters and enjoyed the storyline. Yes, it’s a little simplistic at times. I also got extremely irked at the Yoda character on several occasions (mostly for acting too much like Yoda). No, it’s not SF, but you’ll know the character I mean. I also thought the villains of the book did a little too much mustache twirling. But remember, this is more of a parable than a novel. As a parable, it works. You’ll get the message, and I think it’s an important one for IT people to get.

Jan 15 2014

Database in Source Control

Many years ago, I was working with a great DBA. Seriously, a very smart and capable guy. He told me, “We need to put the database into source control, just like app code.” And I just laughed. Not because I disagreed with him. I knew he was right, but I had tried, several times, to do just that. See, I’m not really a DBA. I’m a developer. I knew that code (and all the T-SQL that describes databases is code) needed to be versioned, sourced, tracked and audited. But great googly moogly, it was not an easy thing to do.

I first tried just exporting the entire database into a script and then occasionally checking that script into source control. Yay! Mission Accomplished… Well, I had a database in source control, yes, but I didn’t have any of the great stuff that went with it, most of all, a way to deploy from source control.

Next, I tried just storing the stuff that changed most, procedures. But, I had to store everything as an ALTER, or, I had to store it all as a DROP/CREATE and store the security settings and extended properties. I tried both. Neither satisfied and it was WAY too easy for someone else to modify a script the wrong way and bring the entire thing crashing down. And, not to mention the fact that any and all structural changes outside of stored procedures had to be built manually, or using a compare tool to generate them (but not the procs, cause we have those in source control, remember) by comparing prod & dev or qa & dev or something & something… Oh yeah, that was fun.

Man, it was painful back then. But now, there are several ways you can do this using Microsoft and/or 3rd party tools.

Why aren’t you?

Seriously, most of you aren’t. I’ve been going all over the country teaching a class on database deployments (next one is in Cleveland if you’re interested) and I know most people don’t put their databases into source control. Of course, I’m pretty sure most people don’t talk to their Dev teams if they can help it, and it does seem like most Dev teams seem to be on less than a perfectly chatty basis with their DBAs. is that the cause? The thing is, you are experiencing pain in terms of time spent, mistakes made, slower deployments, less frequent deployments, possibly even down time, all because you don’t do your deployments right. And deployments start from source control.

Developers have spent the last 30 years or so figuring out better and better ways to arrive at functional code in the hands of their customers (internal or external) as fast as possible, as accurately as possible. Over the same 30 years, DBAs have been figuring out how to better and better protect the information under our charge ensuring that it’s backed up, available, performing well, and always on (to use a phrase). My suggestion to you, data pro, talk to your developers. Figure out what they do and how they do it. Take advantage of their years and years of process improvement and apply what they’ve learned to your database development and deployment.

There’s a new concept growing out there. It’s fairly well established within the *nix communities, DevOps. It’s the realization that the world doesn’t begin and end with your database/server/application. Instead, your database/server/application is completely dependent on the database/server/application that it needs to run. Notice, no one is more important here. We’re talking about creating mechanisms for teams to deliver functionality to their clients (internal or external). And it all starts with the realization that there are parts of the process that some of us are better at than others. Developers know how to develop and deploy within teams. Let’s learn from them. And the start, well, that’s source control.

So, is your database under source control… for real. If not, get it there. The excuses I used to have are gone. That means the excuses you have now are probably gone too.

Fair warning, I may use the term DevOps more in the future.

Sep 19 2013

SQL Lighthouse

Red Gate is constantly experimenting with technology. Because of a long history working within the Microsoft space, a lot of the new experimentation is in and around Azure. One new venture that could be online soon is SQL Lighthouse. It’s a mechanism for dealing with changing structures in an unrestricted Windows Azure SQL Database where you have multiple developers making changes. Potentially, this is pretty interesting. Please follow the link, check it out, and sign up to be alerted when the program becomes available.

Many people are just getting started with Azure, especially wrapping their heads around the concepts of using a Platform as a Service rather than having infrastructure, local, virtual, or on the cloud. If this is you, and you have an MSDN license, you can get that license hooked up with a free Azure account. Then you can start learning where PaaS could serve you well within your environment. Go here to set this up now. You’ll also be entered for a chance to win a rather nifty automobile.

Sep 09 2013

Developers Rate Azure One of Their Favorite Tools

Yeah, Azure.

How we program, what we program and where we program is changing. All the time. This excellent article lays out a bunch of the trends that are going on within software these days. And one of the single biggest parts of this trend is the fact that more and more things are online. In the cloud, if you insist. Clearly, despite unusual (and I would argue, unreasoning) resistance from my fellow DBAs, Azure is absolutely becoming “a thing.”

If you’re like me, as you sit around carefully weaving your buggy whips, you’re also keeping an eye on the road, just in case you start to see more automobiles than horses. Maybe I’m located in a bad spot, but it’s starting to look like a sixteen lane mega-highway outside my door with nary a horse in sight. In short, it’s time to make the leap and start learning the technology around the cloud.

Now, since I’m a Microsoft user, and have been for a very long time, I’m tending to follow along behind their cloud offering. There are positives and negatives around this, but, by and large, I’m seeing the positives far outweighing the negatives. If you want to get started and you already have an MSDN subscription, just link it to an Azure account. It costs nothing, and since you don’t have to supply a credit card and will have a fixed limit on how many resources you can use, it never will cost anything. Go here to make that happen.

The company I work for, Red Gate Software, is pretty serious about supporting the cloud. We have stuff that works with Azure and Amazon and it’s just going to keep expanding because, they’re seeing fewer horses too. For example, we’re actively building development tools to support the kind of high speed, constant, repeatable deployments that are going to be needed by the development paradigms laid out in the article above. And, we’re incorporating those with cloud offerings, today. Here’s a blog post on how to use Deployment Manager to go to Windows Azure SQL Databases. It’s so easy. Now you can implement WASD as part of your development and deployment processes. Because our tool is also managing your app deployments, we’re talking direct integration between application and database. And, best of all, you can couple it in a hybrid fashion, mixing in cloud-based architecture with your local architecture, all through our deployment tools.

If you want to learn more about Azure, Azure Virtual Machines, Azure Virtual Machines running SQL Server and Windows Azure SQL Database, then sign up for my all-day, pre-conference seminar at the 2013 PASS Summit.

Mar 21 2011

Communication

It sure seems like there’s a lot of miscommunication between developers and database specialists. In fact, the communication can become so poor that outright hostility between the groups is common. At the end of the day we are all working towards a common goal, to add value to whatever organization we are working for. It’s a shame that we all lose sight of this commonality and create such a false dichotomy between the groups. I think there are some ways that we, as database specialists, can use to attempt to cross that gap.

Prior to being suborned to the dark side, I was a developer. I had a little over 10 years experience working in VB, Java & C#. I remember, distinctly, cursing our database team for being so problematic about how they did things. They slowed me down. They got in the way. When I had problems they were slow to respond, unless the problems were on production. I know I even instigated a few fights with them in an attempt to get them to move the way I wanted (hard to believe, I know). Then came the day when I shifted over to all database work.

Suddenly, I’m responsible for making sure the production system stays online and that the data is readily available to the business. Now I’m slowing down development, because I want a chance to review their design and validate their code to ensure it’ll work well and not affect production. Now I’m acting as a gatekeeper to prevent unauthorized access to the systems or at least keep people from making any of the 10,001 simple errors that could impact production. Now when a developer wants something fixed in dev, I’m the guy telling them they have to wait because something in production is wonky. And yeah, I’ve instigated fights from this side as I tried to get devs to understand that simply delivering code is not enough and that data persistence is there for a reason (again, shocking I’m sure).

Remember, both of these groups are more right than wrong, and both are working towards that common goal, value for the business. But they really don’t get along. What’s more, what they work on and how they work with it is frequently at odds. Ever heard of the object-relational impedance mismatch? How about the concept that you don’t have a database, but a persistence layer? What about managing data integrity within the application (one of my abiding favorites)? Never heard of those terms or concepts? Then you’re probably a database specialist and you’re probably not talking to your developers. If they haven’t already, they’ll soon be introducing an Object Relational Mapping tool to your enterprise. Best of luck.

A lot of these communication issues probably can’t be solved, but I know of one place where most database specialists are not communicating well with their dev teams, and database guys, it’s your fault. Source Control. Do you think of the structures and procedures within your database as code? You should, because, to a large degree, it is. The Data Definition Language (DDL) calls that make up your tables, views and procedures are code. That code needs to be checked into a source control management system. There, the individual objects can be versioned and managed. There you can create labeled releases of your code. There you can branch your code to create alternate development or support streams that contain variations of your database. There you can merge changes from multiple users and branches into a single main source for deployment to production. There you can keep your database directly in sync with application developers code.

Did you catch that last one? You can become more tightly coupled with your development team. Best of all, you can do this using their tools and their language. This is the communication problem I want you, the database professional to solve. Very few of us database types are using source control these days. This, despite the fact that there are fantastic tools and methods under development from different vendors that directly address the issue of getting and keeping database code within a source control system.

Years ago, when I first made the jump to databases, I was appalled that I couldn’t keep my code in source control. Then, as I worked more and more with databases, despite the problems, I abandoned the idea of managing the code in source because, frankly, it was way too hard. But several years ago new tools appeared on the market to make it possible (if still somewhat painful) to get the database into source control. I’ve been working that way for years now. It has completely eliminated one of the many problems I used to have with developers. They now know that my code is stored with theirs. That my versions are their versions. That their labels are my labels. That we branch the code together. It’s taken completely for granted, and we share a common language about change and deployment.

This has not solved every problem or conflict with database teams I’ve worked with. It has eliminated a source of friction. It has increased communication. It’s something that I could do, and you can do, to get a little closer to your development team. Not to mention the fact that you will now have your databases in a known state, all the time, that you’ll be deploying from a single location, that you can manage access to your code, and all the other things that having your databases in source control will bring.

For more details on the concept of putting your database in source control, and working better within teams in general, I’d recommend reading the SQL Server Team-based Development book. It’s a free download.

Addendum (3/27/2011): If you got this link through an email, could you post a comment below as to which distribution list it’s from? Thanks.

Jul 13 2010

Red Gate SQL Source Control

You just have to love Red Gate tools. They find the small area that they want to cover and then they cover it extremely well. I rave regularly about SQL Prompt and SQL Compare and SQL Search (free one, btw). I’ve got SQL Data Compare and SQL Data Generator open & working on my desk regularly. I’m dabbling in their other tools fairly often as well. I just like Red Gate tools. I guess my constant & consistent praise is why I’m a “Friend of Red Gate.” I like to mention that before I start praising their tools some more, just so no one thinks I’m hiding it. Why would I hide it? I’m proud to say it. I am a Friend of Red Gate! … anyway… where was I… right, new software. I took a small part (a very small part) in the beta for their new software, SQL Source Control. I thought it was pretty cool when it wasn’t quite working right. Well, now it’s out, working very well, and it’s pretty slick.

Basically Red Gate has created a nice tight coupling between Source Control & your database. They currently support Apache Subversion and Microsoft’s Team Foundation Server (TFS). It let’s you create a mechanism for keeping track of your databases in the same way that you track your code. I honestly believe this is a must for any reasonably sized development team (read, more than two). I can expound on why, but instead I’ll just talk some more about SQL Source Control.

First thing you need to know is that it’s hooked into Management Studio. After you do the install, you get some extra windows in SSMS that look something like this:

I’ve scratched out my own server & database names, but you get the idea. The description summarizes it very well. Lots of people can work on the database, save the scripts into source control, and then they can pull that common set of scripts back out to do more work, just like working with code. It really is the best way to develop.

You just have to connect up the database following the directions and you’ll see something like this:

If you can see that, that’s a database (name hidden) that’s been hooked up to source control. Actually, that and the change to the set-up screen are about your only indications that this tool is running. I love the lack of intrusion.

Better still, each time you reconnect the database, as it goes and checks to see if there are updates in source control, you get a little spinning… looks like a yin/yang symbol.

Enough about pretty graphics. How does it work? Extremely well. I started adding new database objects, editing existing objects, and all it ever did was put one of it’s little symbols on the object that I had created or edited, marking it as a change. When I was ready to move the changes to source control, I just clicked on the Commit Changes tab. All the changes are listed and you see scripts showing before & after between the code in the database and the code in source control.

It just works. Same thing going the other way. A database already connected can just pull changes out and apply them. Nothing I did in all my testing hit a snag (granted, I was just working on pretty traditional tables, procedures, indexes, etc.).

The one thing I’ve found that I don’t like is that there doesn’t seem to be a facility for deploying the databases automatically. Instead, I had to create a blank database, hook that to the existing database in TFS and then pull down all the “missing” objects. Hopefully they’ll go to work on a way to automate that soon.

Just to reiterate, the point of the exercise is to get your code (and while you’re developing, a database is as much code as anything written in C#) into source control. Once you’re in source control, you manage your databases just like code, label, version, branch, whatever you need to do to maintain a tight coupling with the rest of the code for the app. SQL Source Control acts as a very fast and simple tool to enable that coupling for you.

Jul 09 2008

The SQL Oath

I’m going to have this tattooed insde the eye-lids of most of our development staff:

SQL Oath

I’m not kidding. Drastic measures are called for.

Mar 21 2008

Top vs. Max

The company I work for has a very well defined need for versioned data. In a lot of instances, we don’t do updates, we do inserts. That means that you have to have mechanisms for storing the data that enables you to pull out the latest version of all the data or a particular version of all the data, or the data at a particular moment in time, regardless of version.

 That means maintaining a version table and a series of inserts into various tables. Some tables will have pretty much a new row for each version, some tables may only have one or two versions out of a chain. With the help of a very smart Microsoft consultant, Bill Sulcius, we have a mechanism that works very well. However, questions about the ultimate tuning of the procedures remain. So we may have a query that looks like this:

SELECT *
FROM dbo.Document d
INNER JOIN dbo.Version v
ON d.DocumentId = v.DocumentId
AND v.VersionId = (SELECT TOP(1) v2.VersionId
                                    FROM dbo.Version v2
                                    WHERE v2.DocumentId = v.DocumentId
                                    ORDER BY v2.DocumentId DESC, v2.VersionId DESC)

There’s a clustered index on the Version table that has DocumentId & VersionId in it. This query works great.

But you can also write the same query to get the same results using MAX or ROW_NUMBER(). What’s more, those work well too, all nice clean clustered index seeks. You can also use CROSS APPLY rather than JOINS. All these appear to work well, but in different circumstances, some work better than others. That makes establishing a nice clean “if it looks like this, do this” pattern for developers to emulate difficult. I’m creating a series a tests to outline as much of the differences as I can. I’ll write it up and submit it all to Chuck over at SQL Server Standard first. If he doesn’t like it, it’s Steve’s. I’ll also post a few tid bits here.