May 02 2016

How to Convince the Boss to Send You to PASS Summit

August two years ago I originally posted, Make the PASS Summit Work for Your Employer. After conversations at several SQL Saturdays over the last couple of months, I decided to refresh and update that original content and post it again.

I keep hearing how the job market has changed. That companies just don’t want to pay for training any more. However, I don’t recall any of my employers in the past ever actively wanting, desiring, begging me, please, oh, please, can’t you go out to a little training? In fact, for the most part, I pretty much always had to beg the boss to send me out to training. I had to sell it. I don’t think that’s a new development. Let’s review the selling points to help you convince the boss.

My Knowledge Base

That’s the easy one. Tell the boss, “I’ll learn more.” Maybe this one is obvious, but you should talk to your boss about the addition of more skills to your skill set, an improvement of your overall knowledge and, by extension, your worth to the company. There is a ton of excellent learning opportunities at the Summit covering the entire length, breadth and depth of Microsoft’s Data Platform and it’s attendant products. These sessions are lead by some of the most knowledgeable and skilled people in the industry. Further, they’re practically slavering at the bit to have you ask your difficult question so that they can exercise their skills and expand their knowledge by helping you. You can learn more, faster, at the PASS Summit than almost anywhere. That’s going too help your employer because you will be a better employee.

Our Current Problem

Just about every year in the 6-8 weeks leading up to the PASS Summit, I would start collecting questions. What particular pain points are we experiencing with Microsoft Data Platform products that are so severe I should grab 10 minutes with a Microsoft engineer to talk about? Oh, didn’t I mention that fact? Yeah, the guys who built the product are frequently at the Summit. You can take your immediate problems straight to these people. Further, there’s likely to be an MVP or MCM standing near by who might be able to help out too. Or, you can try the Customer Advisory Team (CAT) who always have a number of representatives there. In short, you can get pretty close to premier support without wasting a premier support ticket. All the vendors of all the tools you’re using are also there, frequently with some, or all, their development staff. Need some help with that software you purchased, go and get it.

Our Future Direction

Your company needs to make decisions about their technology future. You’ve seen the marketing hype. Now, what do the people who are working with the newest stuff every day have to say? Can you get more information by attending sessions that are not put on by Microsoft on emerging technologies? Yes, frequently. That’s not to say that a Microsoft session by the people who built the product won’t be useful too. The PASS Summit is the place to see this. Microsoft doesn’t just develop things and then toss them over the fence to see what works (mostly). Instead, they have companies and individuals working with them all the time to develop new directions for the product. Those people and organizations are frequently at the Summit, displaying new stuff on the vendor floor or giving presentations about the new directions they’re taking the technology. You can get a better understanding if your company’s plans are going to work well going into the future. Even if the plan is best summed up as “We’ll sit on SQL Server 2000 until it rots around our ears.” Others are doing it too. Find out how it’s working out for them. Or, why they finally decided to upgrade, maybe even moving to Azure.

Our Team Skill Set

Most companies are not going to want to send all of the database development team, DBA team, or development team away for a week. Instead, they’ll send one or two people from each team (maybe less). So your team loses out, right? Wrong. Two things. First, coordinate. If you have more than one person from your company at the event, make sure that you cover as many sessions as you possible can. Don’t overlap. When I was working on a team heading to the Summit we would divide up sessions to make sure things got covered that the company needed or that we needed as individuals. While I may want to see speaker X do her session on indexing again, my co-worker has yet to see it, so I’ll send them. And make sure you have a couple of sessions picked for a time period because the session you’re in could be a bad choice. If a session isn’t for you, for any reason, just walk out. Before you go, if you’re the only one going, head around to the teams and see if they have a request for a session that you can attend. This is a chance to enhance your image within the organization and make your boss look good by offering to help others. Send them links to the event schedule so that they can pick and choose. Finally, teach. You just spent a week getting data dumped into your brain. Teach it to your team. We made a pact that anyone who went off to a week of training had to present 2-3 sessions to the team from that event. You can even purchase the event DVD and show sessions to your team in meetings.

NOTE: This is not to say, steal these slide decks to become your internal training resource, unattributed to the original presenter. That is a bad thing.

My Retention

Who do you want to work for? The employer that says, “Heck no you can’t go to the PASS Summit. You’ll meet people and figure out that our company stinks and you’ll try to get a new job, or you’ll learn more and be more valuable and we’re not about to raise your pay.” Or, the employer who says, “Yeah, sure you can go this year. Let’s document what you’re going to learn and how it’ll help the company.” OK, it’s not going to be that easy. You may have to agree not to leave the company for a year or something afterwards. Be cautious about exactly what kind of strings get attached, but also be aware of the fact that the company is investing in you and would probably expect to get something for that investment. Just be sure it’s fair to both you and them.

I get it that some employers are smaller and just can’t foot the bill for this. See if they’ll meet you part way. You pay for the trip and lodging and they pay for the Summit, or vice versa. It can also be about timing. You’ve got a major software release that’s going to prevent you from going. I almost missed a Summit myself because of this. It’s just not always possible, but a good employer will find a way to make it possible, occasionally. If there is literally no support, of any kind, ever, you’re either working for a not-for-profit or, maybe, the wrong company.

I’ll Be On Call

Be on call. Carry the laptop with you. Keep your phone charged (ABC = Always Be Charging). Don’t enjoy the evening festivities too much (and yes, there are parties at the PASS Summit). Be a responsible employee. I’ve had to walk out of great sessions because of calls from the office. I missed half a day because of a failed deployment. But I was online and available, not falling off the face of the planet just because I was at the Summit. Make the commitment to be available as needed by your employer. Demonstrate that commitment by being available. However, as with all things, there has to be a happy middle, assuming a non-destructive total emergency, they should leave you alone for little stuff so that you can attend sessions and network. That’s why they sent you in the first place.

My Notes

Take lots and lots and lots of notes. You can type them into OneNote or EverNote or whatever. Or you can scribble them into your tablet or onto notepads. Anything that works. But write stuff down. Write lots of stuff down. Write down what you’re thinking about the information as well as details said by the speaker that may not be visible on slides or in code. Write down what you talked about with that lady from that vendor on the back of their card. Take notes while talking to the Microsoft engineer or CAT member. Then, turn the notes over to your employer. They act as an additional knowledge base about the event. It’s one more resource that you’re bringing back to your team, showing the enhanced value that you’re providing.

Our Swag

Bring home a t-shirt or two for those people who couldn’t go. If there’s a particularly cool piece of swag, give it to the boss or have it as a raffle at the team training event for the best question. Share the stuff you get as well as the information you get. A friend of mine and I once collected 56 t-shirts and a stack of other swag (and had a heck of a time getting it all back on the plane) which we then spent almost two weeks handing out in the office to our team, development teams, managers and systems people, etc. It made us look good and cost us nothing but a little time on the vendor floor. It’s silly, but it works. If nothing else, it shows the boss that you’re thinking about your team and the company while you’re away.

My/Our Network

Network. That means not being “that person.” That person is the one who comes to the event, shows up for all the sessions, doesn’t ask questions or talk to a single person all day, then leaves and goes to their hotel room (and then usually goes home saying “Wow, that was a waste of my time”). There are large numbers of opportunities to network. Waiting in line to register, turn and talk to someone. Ask questions of the presenter during their session AND follow-up afterwards (although, let them get unplugged and out of the way of the next speaker). Go to the vendor floor where you should talk to the vendors as well as others. Attend the First-Timers event. Go to the Birds of a Feather lunch. Wear a kilt on Day 2 of the Summit (SQL Kilt Day, you’re reading the words of the founder of the event). Attend the Women in Technology Luncheon. Track down all the places where people are getting together and talking. Go to them. Get together. Talk.

I’m an introvert (people laugh when I say it, but it’s true). I recharge with alone time, not at parties. I get being an introvert. But the PASS Summit is not recharge time. If you’re not almost literally crawling out of the venue on Friday afternoon, you’re doing it wrong. The flight home should be the most relaxing plane flight you’ve ever had because you’ll pass out before take-off and wake up when the wheels touch down.

Take the time and trouble to begin to build your network. And remember, a network is not a series of authors or MCMs or MVPs that you can call. It’s a collection of people, some may be presenters/authors/etc., but the best are probably doing the same job you do but for a different organization. Talk to everyone. Build that network.

How does your network help the company? Remember that you don’t know everything. You can’t. However, you can know the people who do know things that you do not. That effectively expands your knowledge set. That makes you more valuable for your organization.


As you can see, going to the event could be a ton of work. In fact, if you’re focused on maximizing the returns for your organization, it will be. You’re going to be working just as hard at this event as you do in the office. It’s all about showing the organization that they will receive benefits by sending you. They will profit from the expenditure. Never lose sight of the fact that it has to be a partnership with the business. You need to benefit as much as they do from the experience. The fact is though, if you follow all my suggestions, you will benefit, and you will deliver worth to your org.

Apr 22 2014

I Am Better Than You

That is a patently false statement and total BS. It sure does crawl up your spine though doesn’t it? Why then do we need to do this?

I read an article, “How DevOps is Killing the Developer,” and, frankly, was a little put off by this:

Good developers are smart people. I know I’m going to get a ton of hate mail, but there is a hierarchy of usefulness of technology roles in an organization. Developer is at the top, followed by sysadmin and DBA. QA teams, “operations” people, release coordinators and the like are at the bottom of the totem pole. Why is it arranged like this?

Because each role can do the job of all roles below it if necessary.

Nice to know I’m almost as good as a developer. Now, I could go off on a “bash the developers” rant, but I think that would be seriously silly and counter-productive, not to mention completely against my beliefs. I really do like, admire, respect, developers. I also respect system administrators and SAN admins and QA people, hell, project managers. You can go find a web site or a blog specializing in any of these IT disciplines and locate the “DBAs are better than developers” article or the “QA people save the universe” article or “Thank the gods project managers keep all you poo flinging monkeys in line” article. They’re out there. And every single one of them is wrong.

My actual job title these days is Product Evangelist, but I’ve spend the last 15 years as a DBA, database developer and data architect. I feel like I have a handle on that job. I’ve specialized around the Microsoft stack, not because of any sort of religious beliefs, but because it pays the bills. Before that I worked as an application developer. Before that I was in tech support. And you know what, at no point in my career was any of the jobs I did literally more important than the others. You know why? They’re all in support of the same thing: the company succeeds.

Now, fine, developers are smart people. No question, no argument. Developers are capable of learning other jobs (I’m living proof). But you know what, so are people in all those other positions. How do I know this? Because I’ve worked with the QA person turned developer. She was a GREAT QA person. She was so great because she learned the full stack. She developed an understanding of databases and systems and code in support of doing a better and better job at QA. Then, she finally decided she wanted to fix the code a little earlier and switched over to development full time. I’ve also met the QA person turned sysadmin. I’ve met the developer turned SAN admin. The system admin turned DBA, the DBA turned developer, a million and one support desk people turned developer/dba/admin. Every one of these people followed similar trajectories. They started learning the full stack and found areas where they could specialize while using their knowledge of the full stack to make each position they were in better.

But all these jobs and all these people are all, or all should be, focused on one thing, helping the business to do what the business does, for most businesses, support the customer.  Regardless, the focus needs to be on the goals of the organization, not on the purity of a job, a process, a software stack, or a system. Purity and perfection are dangerous concepts within IT. We need to keep our focus where it belongs, not on MY code or on MY database or on MY servers, but on OUR BUSINESS.

And DevOps (I wish the term didn’t have such bad connotations), is about breaking down communication barriers, not just putting all the work on one person/team. Again, focus back on the business and what the business does.

So no, I am absolutely not better than you (the title is just click-bait). I’m not. But, you’re not better than me either. If my saying that makes you angry, maybe you need to reexamine your assumptions.

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


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:

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.