If I could win the DBA In Space contest, I’d be all over it like white on rice. But I can’t win (and yes, that grinding sound you hear is my teeth, not tectonic plates). But, one of my friends might. I already know several of them that have entered.

Maybe you can’t win either (what’s up with Tennessee anyway), or maybe you don’t want to win (I don’t understand this stance, but I know it’s out there). But you know what? Your friends probably do want a chance to slip the surly bonds of earth.

Give it to them.

It’s easy.

Step 1: Get five (5) friends to register for the contest.

Step 2: After they register, send their names & email addresses to dbainspace@red-gate.com.

Done. Send in five and make sure you include your name and address on the third because Red Gate will send you the really cool t-shirt you see on this post.

Surely I’m not the only one who grew up watching this stuff late on a Saturday night, dreaming of my chance. Share this opportunity.

First, let me thank Erin Stellato (blog|twitter) and all the volunteers for running such a great event. Nicely done.

This event was to be my very first walk on to the public stage as a Red Gate employee, so I was excited about getting there. About 10 minutes before I left for the airport, I got a call from the airline. My flight had been cancelled, but no worries, they rescheduled everything for me. Instead of flying to LaGuardia and then on to Cleveland, I would fly past Cleveland to Chicago and then back to Cleveland, getting there at 7:30PM instead of 4:30PM. Ah well. What are you going to do. I’d prefer not to go to Chicago, but everything should be fine…

The plane was supposed to leave Chicago at 5:05 PM. At 4:45 it was delayed to 5:30PM because they were working on the plane. Not a problem. Then it was delayed to 6:00PM then 6:45, then 7:30… and yes, I was waiting all that time at the airport. Finally, at 8:00PM they announce they have the plane ready. We just have to wait for the crew to do a safety check. Then, word comes at 8:30PM that they don’t have a flight attendant for the flight… in one of the world’s largest airports there is no flight attendant available. OK. Fine. They go to find one. At 9:30PM, the flight gets canceled. Why? Because they managed to find one flight attendant, yes, just one, but she was “too tired” to fly again. There were no other flights from this airline and they were not supporting transfers to another airline and they were not refunding tickets. And that, as they say is that. I was stuck in Chicago overnight.

No worries though right? They’ll schedule us on a flight first thing in the morning… Yeah, sure, as long you define “first thing” as 11:20AM. Which would have put me into the Cleveland Airport at about 1:30PM. I wouldn’t get to the event unitl 2PM or later. I would have missed the lunch time presentation I was supposed to give for Red Gate on SQL Monitor, let alone missing all the time to network with the attendees and presenters. There was no way I was missing this. So I called a couple of car rental agents.

Alamo came through. That’s right, I hopped in a car at 10PM at night and drove straight across Illinois, Indiana, and half of Ohio in order to get to Cleveland. It was a six hour trip. I drank so much coffee along the way I thought I might actually lose the car as I vibrated between the molecules of the seat and fell onto the pavement. But it was all good. In fact, I got to make some squished penny’s for my kids at the rest stops along the way (where I got rid of the old coffee & got new coffee).

At 4:30AM I was in my room. I slept for two hours and then went to the event, on time.

The venue was a community college that was pretty well set up for the sessions. It worked well for the 150 or so attendees. I didn’t get to any sessions in the morning, but I got the chance to speak with a number of attendees. My lunchtime session went fine and was well attended. After lunch I went to one session, but was honestly have a hard time staying awake, despite the fact that it was an excellent session from Mike Walsh (blog|twiter) and Erin Stellato on vendor/dba relations, so I didn’t attend any more. Better to stay on my feet & keep moving. Finally 4:00 came and I did my presentation. There were about 30 people in attendance and I thought it was a great audience. They seemed engaged and interesting. Hopefully you got something out of the session.

Then it was off to return the rental car to the airport, oh, during the 4 inch snow storm that had started up while we were having fun inside. I got lost twice because the car rental place is like, 15 miles away from the airport and not well marked, but finally got that job done and got a ride back to the after-party where I had a good burger and a good beer along with good conversation to finish out the day.

Thanks again to everyone involved with SQL Saturday #60. Now I have a great story to tell about my first day of work with Red Gate Software.

A question came up on the SQL Server Central Forums, how could you use Red Gate SQL Compare to automate creating scripts for all your databases at once. What’s the first thing that popped into my head? Yep, Powershell.

SQL Compare Pro comes with a command line utility that allows you to automate just about everything you can do with the GUI. I like to use it when I’m setting up deployment mechanisms for systems because it makes one click deployments possible. I won’t go into all the methods of that type of automation here. For a lot more information on that topic, check out the Team-Based Development book.

If you’re interested in exploring how you can use the command line, just type this:

sqlcompare/?

Now is a good time to note that I’ve set SQL Compare’s location within my path because I use it so often from various locations in the disk, that having to type the full path every time I want it, is just too much.

Here is the PowerShell script:

$Server = "grant-red1\gfr1"
$Path = "c:\dbscripts"

Invoke-Sqlcmd -Query "sp_databases" -ServerInstance $Server | ForEach-Object {Invoke-Expression "sqlcompare.exe /s1:$Server /db1:$($_.DATABASE_NAME) /mkscr:$($Path)\$($_.DATABASE_NAME) /q" }

It’s just a single line of code, not counting setting the variables. I’ll be making it into a full blown script and posting it on the Red Gate site (location to be determined) so you can just call it passing variables. In the mean time, let me explain what I’ve done.

First, I use the Invoke-Sqlcmd utility to pass the query sp_databases to the server. This will retrieve the result set which consists of DATABASE_NAME, DATABASE_SIZE, and REMARKS for all user databases on the instance and any databases available through a gateway on that instance. From there I simply use the pipe, “|”, to pass the results to a ForEach-Object loop. Yes, I’d prefer not to use a loop, but this is a DOS command line utility, not a PowerShell commandlet, which means it’s not aware of the beauties of the pipe. Invoke-Expression is used to call sqlcompare.exe for each row within the result set. I use the abbreviation for the parameter /Server1, /s1, to set the instance through the variable $Server. I then use the abbreviation of the parameter /Database1, /db1, and the database name came from my object, and the column name, $_.DATABASE_NAME. Then the magic. The parameter /MakeScript, I again abbreviated to /mkscr, along with a path, in which I included the database name in order to separate out each database, was all that was needed to complete the command.

In short, three PowerShell commands and one call SQL Compare’s command line are all that are needed.

When you run this script, you’ll get a folder for each database and inside that will be a complete set of scripts for recreating the database. You can use this to script out your databases for inclusion in a source control system, or for whatever else you might need.

This example is just a small taste of what’s possible to automate using the command line utility in SQL Compare. Further, you can see that it’s very possible, easy even, to integrate SQL Compare with the primary means of server automation within Microsoft today, PowerShell.

---

PS: I won’t normally be posting much Red Gate stuff on my personal blog. I’ll be doing that inside the official Red Gate area where there are lot more eyeballs looking at it. But, until we get that all set up, I really wanted to share this little tid bit.

Don’t get me wrong. It’s been great. The beta tests. The advanced notices. The opportunity for feedback. It’s been a great relationship, a fantastic friendship. Look, it’s not you, it’s me. I want more. I want to take our relationship to the next level.

That’s why I’m excited as can possibly be to announce that I’m no longer in the Friends of Red Gate program because I’m now a Red Gate employee. That’s right, I’m colored Red! This is an amazing opportunity to step up my game to a whole new level and I’m thrilled to be able to take advantage of it.

I have a fascinating title: Product Evangelist. I’m going to be traveling around the country to various events, SQL Saturdays (Cleveland & Chicago already booked), SQL Rally, the PASS Summit, others, presenting the same old stuff I’ve always presented, performance tuning, development processes, etc. I’ll just also be presenting Red Gate software. I’ll still be writing the same kinds of articles and blog posts I always have, plus I’ll be working on articles and blog posts for Red Gate. I’ll still be writing the books, plus I’ll write more for Red Gate. I’ll actually still be involved with the Friends of Red Gate too, just from the other side now.

I ended up with 131 posts (1 late hit, sorry), not counting any duplicates with Brent or Steve. It was clear that people really took this thing seriously and put thought and effort into the posts. I wish I had about 60 iPad’s to give away instead of only 10. But, decisions had to be made. The point system is utterly meaningless, but, points were awarded for pithiness (I warned you), for a well-turned phrase, and as much as I’m loathe to admit it, for poetry (just because of the work involved). Originally, I planned on just randomly picking the winner. But, I only had 131 entries, and you all worked so hard, I decided to read them all and work hard for you in return. This really was a lot of fun for the time of year. Thank you all for taking part. Thanks to Brent Ozar (blog|twitter) and Steve Jones (blog|twitter) for their hard work behind the scenes. Thank you Red Gate, the SQL Monitor Team, the DBA Team, and the Friends of Red Gate, for sponsoring such a wonderful contest and for picking this blog as one of the three.

In no particular order, here are my top 10 posts, and the winners of the iPad and SQL Monitor license:

Gianluca Sartori - For “Sometimes I ask myself if you’re married with me or with your boss”
Allen Kinsel - ‘Cause I identify with the need to monitor the teenage daughter
Gary L - Because, just because
Tim - Because he avoided the use of Nantucket
Wendy - For “play file tetris to keep your apps running” because it’s a game I play too
Carla Johnson - To help
Matt Whitfield - Haiku, what else can I say
Jane - While not a tea fan, I support the effort here
Dave Mulanaphy  – How could I not support the efforts here
Allen White - He said PowerShell

Tomorrow I might pick a different 10. These were all excellent posts. Congratulations to the winners.

The new book is available in a free download from Red Gate. I had a lot of fun working on the three chapters that I did for this book. The topics I received are near & dear to me because of all the time I’ve spent working on getting databases into source control and automating (as much as possible) the deployment of databases out of source control. Everything I go over in the book might not be 100% applicable to your situation, but I’m pretty sure almost anyone involved in database development and deployment can find some useful information there. For those who are interested, I don’t just cover Red Gate tools in the book either. There’s quite a bit of time spent describing how to automate deployments using Visual Studio Team System 2010. I also go through ways you can collect and manage code snippets (the best being to use SQL Prompt, but there are others).

I’ve been reading the rest of the book and it’s filled with excellent information too. Yeah, you may already have a naming standard or you might already feel that you’ve got your schema well in hand and you might not see the utility of testing databases. But, read through this book and I’ll bet you pick up one or two things in almost every chapter. The guys who worked on this, Phil Factor (blog|twitter), Alex Kuznetsov (blog) and Mladen Prajdic (blog|twitter), are extremely smart and very informed on all the topics they tackled. There really is something in this book for everyone… unless you’re that guy that is sitting all alone and does everything for the company. Most of the rest of us work on teams, even if the teams are small. Small or large, that’s who this book is written for.

If you want to move your database development game up another notch, I strongly recommend taking a look

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.

Because I do. I’m working on a set of tests for an article comparing TOP, MAX & ROW_NUMBER. I have a simple data structure and I need a bunch of data in order to create my tests. I wanted that data to be distributed a certain way, to mimic some production system behavior I’ve seen in the past. Last night I got it all set by mucking about with the seed values of the various columns to get it just right and load up millions of rows in only a few minutes and doing this all on my lap top. Great tool!

I just received word from Rachel Hawley over at RedGate that SQL Data Generator has been released. I’ve been using the beta over the last few months and I’ve found it incredibly useful for setting up tests and playing around with my database designs, seeing how different data loads will be distributed through the indexes, etc. It’s quick to use but fairly powerful and flexible and, frankly, pretty inexpensive. I strongly recommend it.