Voice of the DBA

Author: way0utwest

Hardware Issues

My backup drive failed last night. I was struggling with a few VM issues and when I finally resolved a few, I went to back up the VM in case I had more. I plugged the external, 2.5” SSD into my laptop and got nothing. No response, no new drive in Explorer.

That’s not what I want to see, though I’m glad it was a backup drive, and not one I needed for presenting. Not much I could do at night, on the road, but in the morning I resolved to get a new one.

I did have a California geek moment, heading to Fry’s for the first time to replace my drive. I’ve read about, and heard about the store for years. I’ve passed them in Northern California, but never gone in.

It was a bit of a mistake, since I was late getting back to our Red Gate event. I got a little entranced with the various displays and options in there. It’s like a Best Buy++, combined with a Radio Shack and more. Microcenter in Denver is similar, but Fry’s has more.

I decided to go mSata rather than a 2.5” one, mostly for space and weight. It’s amazing to me how small things have gotten. I got the Samsung 840EVO 500GB drive and a small case.

I carry a screwdriver, but the case came with a tiny one that I used to mount the mSata drive. How small is this? Small.

The picture above shows my hand, a 16.9 oz water bottle, my Arc mouse, and the 500GB mSata drive in an enclosure.

500GB!

I could probably carry 4TB worth of these one hand. For $249 for the drive and $20 for the case, it’s amazing. I’ve got a nice backup for multiple copies of my VMs, which is very handy and cost effective.

If you haven’t tried mSata, take a look. I’m not sure I’d look at any other formats for portable storage.

April 10, 2015
April Blogging Challenge 2 – Primary Key in CREATE TABLE
The April Blogger Challenge is from Ed Leighton-Dick and aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

Designating a Primary Key at Design Time

In the first post on this subject, I noted that I often add a primary key after the fact, using ALTER TABLE. However I’ve been wanting to build the habit of adding the PK at design time, inline with the CREATE TABLE statement, so I decided to look up the syntax and start practicing.

The inline code adds a CONSTRAINT clause into the table definition. I can do this in two ways, the most common is after I’ve built all the columns, I add CONSTRAINT, a name (PKUsers) then the PRIMARY KEY keyword with the optional clustered/nonclustered designation, and finally the column(s) in parentheses

Here’s my new code:

CREATE TABLE Users ( MyID int IDENTITY(1, 1) , firstname varchar(250) , lastname varchar(250) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (MyID) ); GO

The better way, IMHO, is to do this inline if you have one column. I know this isn’t consistent, but I can easily see the constraint this way.

CREATE TABLE Users ( MyID int IDENTITY(1, 1) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (MyID) , firstname varchar(250) , lastname varchar(250) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) ); GO

This is more difficult to see if you have multiple columns, but you can do this:

USE sandbox; GO CREATE TABLE Users ( MyID int IDENTITY(1, 1) , firstname varchar(250) , lastname varchar(250) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (firstname, lastname) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) ); GO DROP TABLE dbo.Users; GO

Simple and easy to find the PK now, it’s named correctly, and it’s easy to read.

References

A few places I used
- https://msdn.microsoft.com/en-us/library/ms189039.aspx
Quick and Easy Blogging

This post occurred to me while I was writing my first post. Actually, this occurred to me first, but I realized that I often do the ALTER method in post 1, so I wrote that first.

Changing to this post required using the same MSDN article, dropping the table, rewriting the DDL code, and testing it. About 5 minutes for this one because it was based on a previous post.

Look for the other posts in the April challenge.
April 9, 2015
State v Migrations

Most people work in evolutionary databases. By that I mean a database where you have some schema, and over time you are altering that schema. You might be adding columns to tables or views, changing stored procedure or function code, or something else, but you aren’t really rebuilding the database from scratch on a regular basis. Certainly some people sell software and a database that’s always being rebuild and upgraded from state X to state Y, but that’s a much more complex issue.

For those of us that work with these evolving databases, we really have two choices in how to work on upgrades. We can store each change to the database as a script and ensure we run the correct scripts in the correct order (discarding those that aren’t needed) when we deploy changes. I’ve done that before, and it can work, but this approach requires I’ve have good control of production to prevent changes from being made in that environment that aren’t also made in development. This is the approach advocated by Paul Stovell, of Octopus Deploy.

The other approach is to look at the state of development at some point in time, compare that to production (with a tool like SQL Compare) and then generate a script that makes the changes needed. This is how lots of people deploy their changes today, though this approach isn’t without its own issues. NOT NULL columns, renames, and more can cause problems with this approach. There are ways around these issues, but they require some work.

Ultimately the problem of smoothly deploying changes to databases requires a bit of discipline from the DBAs and developers. Tools can help, and they certainly can reduce the work involved, but good habits and a consistent process are important to ensure that changes are made smoothly. The one thing that helps you find problems with your process and code is testing, which is something I’d recommend you implement no matter what method you choose for deploying your changes.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

April 9, 2015
Release Wednesday

I’m glad that someone else is adopting Release Wednesdays. I was surprised to see the post from Red Gate, though I shouldn’t have been. I knew we were releasing regularly and some groups were aiming for weekly releases. There was easily a 1 in 7 chance of them choosing Wednesdays, which has a special meaning for me.

I worked at a startup company where we released every Wednesday. We worked in one week increments and developed an application across a couple years that evolved and morphed to meet differing business needs in the education market. We went from working with digital sales, to including physical products, to subscriptions. Our customers went from individuals to corporations, to departments in corporations, and all along the way we managed front end and database changes in one (or sometimes two) week increments.

We didn’t practice Agile or Scrum or anything formal. We worked as closely as 10 people could, coordinating and communicating to get features implemented every week. We started releasing on Wednesday nights with the lead developer and myself staying late at the office Wednesday nights (usually with another developer or two for company) for an hour. That was reduced to phone calls where we deployed from home (and fixed problems) in real time. Eventually we built such a well engineered process, with thorough testing, that our deployments were completed in minutes with a few chat messages to verify we’d both finished our tasks.

We did that for over 18 months with only one or two rollbacks, and without the all night frantic coding sessions to fix issues that I’d experienced elsewhere. It wasn’t simple, but it wasn’t hard. It required preparation, planning, a strong culture of professionalism, and support from our CTO.

I see the same types of culture at Red Gate, and I’m not surprised by the rapid releases, nor the success we’re having in evolving, updating, and patching our products over time. I’m excited to see how much quicker we can move and how we will continue to build better software, faster.

One Wednesday at a time.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.7MB) podcast or subscribe to the feed at iTunes and LibSyn.

April 8, 2015