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.
Confio Ignite: Part II
I’m continuing to evaluate Confio’s Ignite database monitoring tool. I’ve had it collecting data on a couple of production servers for about a week now. Based on what I’ve seen so far, it’s looking like a pretty good piece of software.
Breaking with the usual tradition, I’m going to talk about the things I’m not crazy about with the software, before I start singing its praises. The first thing, that I thought was missing, but is actually just hard to find, is the ability to look at the query information that Ignite collects, broken down by database. It looks like you should be able to get to it by looking at the Databases tab, but instead you have to first drill down into a time-period, then select specific databases within that time period, which will show you the queries by database. I know that in my environment, it’s this database listing of queries that is probably going to get used a lot. Tracking it down required help from the Confio team, which they quickly provided. They also showed me a way I could run queries to get this from their database so that I could create a report.
Speaking of reports, because they support Oracle & DB2 as well as SQL Server through their interface, no Reporting Services. OK, I recognize that I’m more than a bit Microsoft-centric when I say this, but I’d like to see SSRS reports so that I can manipulate things the way I want to. Again, not the end of the world, but it’s just something I don’t like. Because the data store is available though, I can get in there and create my own reports to my heart’s content and, if I like their report, I can always run a trace to capture the query and see how they built it so that I can build one of my own that mirrors it. I think they provide a mechanism for customizing reports by building some XML that will add it to the interface, which is a bit of a pain, but shows they’re on top of things.
One other thing that bothered me as I worked with Ignite is that, in the real time monitoring section, it was hard to find my way to the list of locks. It had the list, but it just wasn’t obvious how to get to them and this is something I’m used to looking at when I’m worrying about real time performance issues.
Right, enough talking about things I don’t like. See this? I love this:
That’s a break down, by database, by day, of the cumulative waits on the system. Yeah, that little pink database is causing all sorts of problems on one of my production systems. I actually already knew this was a problematic database, but I wasn’t that precisely aware of when and how it had issues. You can drill down to see the same thing for a given day:
And it’s not just the pretty pictures either, showing that most of our production load is in the morning, but that there are some odd spikes at midnight, 4AM & 7PM, but there’s data available behind the graph. If you drill down and hover over the graphs, pop-ups like this one appear:
(Names have been blacked out to protect my job)
And it’s the focus on wait times and types provided by the trending views and reports that make this a very strong tool. Ignite also collects pretty standard performance metrics, buffer cache hit ratio, memory paging, etc. And you can customize which of those metrics it collects and which ones you display, all on your own. But almost any decent monitoring tool does that. I use a tool that does that, and might even do that a bit better. No, what seperates this tool from the pack is that ability to identify the wait states and the query that is causing them. That’s what will get you excited when you’re using Ignite.
It also has a little ping mechanism that shows response time on the server, a helpful and friendly little reminder that all performance isn’t around what’s happening on SQL Server, but what’s happening across the enterprise.
Big chunks of the interface are customizable and you can add or drop things using the little icons on the right side of the picture above.
I can keep going with all the stuff that’s good, because it’s a long list, but I’ll summarize what I like so far. I like the focus on wait states, a lot. I like the focus on query performance. Between those two I feel like I’m getting the majority of what I need from the tool, and more than I get from other, similar, monitoring tools. The fact that it does all the other standard data collection on top feels like gravy. The problems I have with the software so far are either personal issues, Reporting Services, or fairly minor problems. I have a monitoring product running against the same servers as Ignite and I haven’t seen any impact from Ignite’s monitoring showing up there, so it looks like this is a benign monitoring software.
For my final installment, I’m going to look up the skirt of Ignite and see what the underlying data structure looks like. The ability to gather and present data is all well and good, but more important is the ability to take that data and make serious use of it through reports or by building other data structures out of it for long term storage and reporting.
Database Design Process
Buck Woody recently asked a question; how do you design a database. He outlined the process he followed and asked four questions about how each of us do our work:
- What process do you follow?
- How important are the business requirements?
- What tool do you use to create the design, do you need it to diagram, do you even care about diagrams?
- What’s your biggest pain-point about designing?
Funny enough, I haven’t done a full on database design in over a year. My company just finished about 6 years of very hard-core engineering work, designing and building or redesigning and building, the majority of our data input and collection systems. Then, I was doing lots of design work. Now, we’re either largely in maintenance mode for most of those systems, or the few new major projects we’re working on are using CRM, no database design, or hoping that database design will never, ever matter and using nHibernate to build the database on the fly, based on the object model (this, by the way, is still in super-double-secret probation development mode. I haven’t seen what they’re producing). All that means we’re doing very little design work now. That will change.
Process
The process I follow isn’t radically different from Buck Woody’s. I get the business requirements, which are hopefully written on something more substantial than a cocktail napkin, and with more detail than will fit on one, and I read them. Or I meet with the business people and ask lots and lots of questions, which I’ll probably do even if I have written requirements. Sometimes, especially at my current company, I’ll get a full logical diagram from another team. I’ll still basically do the same design work, even if I have a logical model, but I’ll use it as a reference point to double-check my understanding of the requirements. From there:
- Identify the nouns. Figure out what kinds of objects, or things, or widgets that we’re talking about needing to store.
- Figure out the relationships between the widgets. What’s a parent and what’s a child and which of the widgets is related to many other widgets or to one other widget, etc.
- Lay out the attributes for the widget, meaning the columns in the table. Big points here include getting the data type correctly identified and figuring out which of the attributes are required and which are not. Further, which of the attributes come from pick lists, which means look-up tables.
- Identify the natural key. I’m with Buck, most of the time I use an alternate, artificial key (yeah, frequently an identity column), but I want to know the natural key so that I can put a unique constraint on the table, in addition to the primary key. This is a vital, but often missed step, in terms of the business processes being modeled.
- Figure out where I’m going to put the clustered index. Usually, but not always, this will be the primary key, but I do it as a fundamental part of the design. That means, as a fundamental part of the design, I think about the most common access path for the data. That’s going to be where the cluster needs to be.
How Important Are the Business Requirements
Buck, I respect you and I like you and I hate to do this, but you’re kidding with this question, right? No requirements, no database. The requirements are all. The most important reason you want the requirements written down is because that means that business at least thought them through, all the way, one time. It’s your best bet that you’re going to deliver something that slightly resembles what the business wants and needs. I live and breathe by the requirements. When someone from the business corrects my design, “Oh, that widget is related this thingie, not that watchamacallit,” I get them to change the requirements to reflect that new information. This way, when someone wonders why I did what I did, they’ll always be able to see the requirements the way I saw them.
Tools
I use Embarcadero’s ERStudio. I know there are other ER tools on the market, but I fell in love with this one on the day I needed to change the data type on a column that was in about 30 different tables and I did it in a couple of minutes using their scripting tool. I’ve been using it ever since, and we’re talking well more than ten years now. It’s just great. I only use it for the initial design and for documentation after the design. Once the design is done, the first time, and a physical database is constructed, I don’t work from the ER diagram to do builds and deployments, I work from source control. Do I have to do it like this? No, but I really enjoy the power of an ER tool while I’m doing the design because it lets you do a lot of changes, quickly and easily without having to rebuild a database over & over.
Biggest Pain Point
The largest pain point has to be changing requirements. Change happens. I embrace it. I’ve worked on agile projects and I like the general approach and mind set. And yet, changing databases is hard. It’s not so bad when you’re in the strict, isolated, ER diagram only stage, but as soon as you’ve built the database, even one time, change gets difficult. It’s not so bad if you work closely with the design, test & development teams and get their buy-in, early, that test data must be tossed & rebuilt with each fundamental design change. But that’s a hard agreement to get and so you end up spending a lot of time trying to retain the test data AND fundamentally redesign the data structure. This is not an enjoyable combination.
That’s about it. I would say the one thing I try to do, and it’s not easy, is be open to doing silly stuff. I try, and I don’t always succeed, to let the business or developers or logical modelling team make silly choices after I carefully tell them why they’re silly and the likely outcome. I do this because fighting them on every single silly decision is a losing proposition. Plus, it saves you for the fights against the stupid, as opposed to silly, things that come along occasionally.
Editorial on SQL Server Central
My first one over there. It’s discussing whether or not you should do two things, build your own monitoring tool, come out in particular favor of one tool or suite of tools from a single vendor. Please read it and watch the video. And, even more importantly, leave a comment in the discussion.
Pay for it!
For those searching for the string “sql prompt 3.8 | rapidshare,” that happened to somehow come by my blog, twice, I just looked it up on the web site. SQL Prompt is only $195. If you’re professional enough to need it, surely you can afford to pay the licensing fee. Seriously now. It’s not like Red Gate is some gigantic corporate entity that would be incapable of noticing that you just robbed them of a single license. Reg Gate is a company so small and personal that I, who have never worked for them a day in my life, have met the CEO. Great guy too.
I do understand where you’re coming from. Long, long ago I worked in tech support and only did programming on the side. I “borrowed” a copy of the programming language I was using back then because I flat out couldn’t afford it and I needed time at night to practice. I appreciate that. But this tool is not an entire programming language, it’s a utility designed to make your full-time programming life easier. I understand why you want it, because, frankly, it works really well. But, since I know, if you need this tool, you can afford to pay for it, just pony up the cash. It’s not going to break the bank. Skip buying a copy of Halo Wars this week and pick it up used next week when you can save about $15.
Just stop trying to steal from a hard working bunch of people who’ve earned the right to charge you one heck of a lot more than they’re charging. Loser.
New England Data Camp Evals
Anyone reading this who attended the New England Data Camp and filled out an eval, for any of the sessions, thanks. For those 63 evals between the two sessions that I received, thanks. Here are the aggregates on my sessions:
Using Visual Studio Team System Database Edition:
Average of Knowledge | 8.344827586 |
Average of Presentation | 8.482758621 |
Average of Preparation | 8.103448276 |
Average of Interesting | 8.172413793 |
Average of Overall | 8.275862069 |
Number of Submissions | 29 |
Understanding Execution Plans
Average of Knowledge | 8.647058824 |
Average of Presentation | 8.617647059 |
Average of Preparation | 8.705882353 |
Average of Interesting | 8.529411765 |
Average of Overall | 8.625 |
Number of Submissions | 34 |
These are all on a scale of 1-9. I’m really quite happy with the results. Here are the average results for all the speakers and all the sessions at the Data Camp:
Total Average of Knowledge | 8.407843 |
Total Average of Presentation | 7.912109 |
Total Average of Preparation | 8.130859 |
Total Average of Interesting | 7.962891 |
Total Average of Overall | 8.096004 |
Total Number of Submissions | 515 |
Overall, both sessions beat the average. My knowledge level was marked down a bit on the Visual Studio session and I attribute that (mostly) to a lack of rehearsal and preparation. I changed that slide deck just the week before the Data Camp and it showed. Same problem with the Visual Studio session regarding preparation. What practices and rehearsals I had done were on my desktop at work. I found out that morning that my laptop didn’t have the GDR release installed, so I had to RDP to my desktop. It created several technical issues. I’m glad that people picked up on it. It really does keep me honest. I guess the session on execution plans was well received (despite the fact that I kept saying page when I meant leaf when referring to an index structure, bleh).
There were some really nice comments, thanks everyone. A couple of the comments on the Visual Studio session talked about market penetration and the readiness of the tool set. I had about 60 people in the audience and only three (3!) were using the tool. More were using the Team Foundation System, but not to the extent we use it where I work. I don’t think that’s because the tool isn’t ready (although I think it has a few shortcomings in & around deployments, especially incremental deployments) but rather the fact that it costs a bloody fortune. Few individuals can afford it and not that many companies are going to be willing to pay for it, especially in this economy. Other than that, no suggestions for improving the presentation, despite the fact that I got marked down a bit on this one. I’ll take the preparation more seriously next time.
I only got one negative on the Understanding Execution Plans session and, unfortunately, it’s only marginally useful. One person gave me a 2 on “Interesting” (in a sea of 9’s a few 8’s and two 7’s). This person wanted to see a session on query tuning and optimization. But, that’s just not what the session is about, at all. So it’s hard to take this as a mechanism for improving my presentation on what is an execution plan and how do you read one. However, it does let me know that I should probably try to come up with some kind of performance tuning & tips session that I can give from the new book. Unfortunately, this is such a full field with great presenters like Gail Shaw already showing exactly what I’d show (except better) that I’m not sure what to do about it. I need some idea to drive the session, a hook like Gail’s “Dirty Dozen” (fantastic name). I’m thinking about this one.
Anyway, there are the results, all out in the open. Thank you again for sending in your evals (even the 2 was very helpful) and your comments. The compliments were extremely nice to read, thank you.
UPDATED: Typed Gail’s name wrong AND forgot to link to her site.