Voice of the DBA

Author: way0utwest

Up, Up, and Away

I’m leaving tonight.

Not forever, but for a couple of weeks. While that’s not a big deal for many people, I’ve rarely been away from home for more than a week. My longest business trips have been 11 days. Short, I know, but that’s the way I like things. Many of my colleagues think nothing of taking trips for multiple weeks, traveling the entire time. However, even during my six week sabbatical, I was at home the majority of the time. Perhaps that will change as my children get older since my wife likes to travel, but we’ll see. Certainly I’ve started to get used to living out of a suitcase, working on a laptop, and having minimal material things around. I expect the next few years will have me working out of our horse trailer as my wife teaches clients or participates in races.

I’m looking forward to the trip, which will be a dichotomy of both relaxation and busy work times. I first start off with a week’s vacation in Scotland with my wife. She’s never been, and it’s been nearly 30 years for me, so we’ll take some time and wander around the Edinburgh area. I’m looking forward to the chance to wander around without an agenda and enjoy another culture. I’ve gotten a few ideas from friends on places to visit, and might even enhance the adventure by renting a car. I’ve driven (briefly) on the left side of the road, but it was a stressful experience.

After a week of vacation, my wife and I will part at Heathrow airport, flying in opposite directions. While she returns to Denver, I’m going East, to Oslo for SQL Saturday #532. This is my first trip to Norway, and I’m looking forward to it. I arrive early Friday, so I have the chance to wander around a bit before the event Saturday. Then it’s back to the UK early Sunday since I’ll probably need to do laundry and get ready for a work week.

My second week will be in the Redgate Software offices, but it’s a busy week. This is our Down Tools Week, with a number of projects that I’m interested in. I had hoped to be able to focus on one all week, but that won’t be the case. I’ve got SQL in the City preparations to begin, as well as a trip to London to deliver a talk on Tuesday at the London SQL Meetup. I suspect I’ll be doing something different each day, including some catchup with the great work our development teams are doing on various products.

I finish off the trip at SQL Saturday #520 in Cambridge, where I’ll be giving a short, quick keynote to kick off the day. That’s exciting, and will make for a long day as I also have two more sessions to deliver. Then it’s back down to Heathrow that evening to begin my journey home.

I am very fortunate to be able to travel around the world, speaking about SQL Server and meeting some amazing data professionals. It can be hard, but it’s always enjoyable and I look forward to shaking hands with many new and current friends on this journey.

Steve Jones

The Voice of the DBA Podcast

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

August 26, 2016
Better Coding

When the census was last taken in the US, it was still mostly a paper affair. My wife and I sat down with the form, filled it out, and mailed it back to the US government. While much of the data is publicly available, the process of gathering the data was primarily an analog process. That was in 2010, and governments are trying to do things differently. I suspect our 2020 census in the US will be mostly a digital effort, though I hope the companies setting up the applications learn from other countries’ issues.

Australia had their census this year, 2016, and they planned on having most citizens complete the form online, with a requirement to opt-in to get the paper version. However, things didn’t go smoothly. There was a DDOS attack on the night the census was set to collect data. There were also concerns from many people over data privacy. All in all, not a great showing for the Australian Bureau of Statistics and their IT contractors. Things got worse in the weekend after the census was scheduled when a few university students built a website designed to handle a larger load than the government over a weekend for substantially less money.

To be fair, the university students’ site wouldn’t necessarily meet all the requirements for privacy and data security. It also doesn’t seem to have the validation and verification that a website needs. Not to mention a database setup to keep the data for later analysis. Those things would certainly need to be added and tested, but this does show that sometimes there are better ways to develop software than the way that has been done in the past. Large government projects get bogged down, perhaps worse than many corporate projects, but I’m not sure. Perhaps we just need people that code better, and certainly, people that manage developers better.

Certainly the cost of projects for the things like the census might not go down much. After all, I’m sure a fair amount of the cost charged by IBM goes back to the salespeople and engineers responsible for landing the contract. However, I also think that far too often there is too much overhead in large projects starts to obscure the most important part of any software system: it needs to work. I would prefer that even long term, large scale projects be developed in an agile fashion, and take on beta testers throughout (public or private). Let actual users play with the site in advance, report bugs, simulate a load, even watch the site develop in real time.

And when you’re ready to deploy, from experience, I can tell you it’s not too hard to delete all that transactional data before you go live.

Steve Jones

The Voice of the DBA Podcast

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

August 25, 2016
SQL Saturday–Viking Edition

I’m off to the Viking Ship Museum for SQL Saturday #532. Actually, the Viking Museum will (hopefully) be a side trip on Friday before the actual event. The actual training will be at the Noroff campus in Oslo.

This is my first trip to Norway, and while it will be quick, I’m looking forward to wandering another new country for a bit. I’m honored to be on the speaker list at this event, which is the third Oslo SQL Saturday (#414, #317).

As with most of the SQL Saturdays, there’s quite a selection of sessions to choose from. I’ll be talking about your career, as well as Continuous Integration for databases. However there are Query Store talks, Power BI, HA, PoSh, Azure, R, and more.

I know many of you don’t live in Oslo, but it’s close to much of Europe, and if you’re looking for a quick getaway to improve your career, come on over on Sept 3. If you want more training, there are 4 pre-cons to choose from, all taught by some highly skilled SQL Server pros that are worth the effort to see.

Once again, I’m excited for another SQL Saturday, and hope to see some of you there.

August 25, 2016
Output into a Table–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I often see people struggling to use triggers for auditing, or having issues with building them to handle multi row updates. However, there’s another choice: the OUTPUT clause.

Not many people use this clause, but it’s a great way to access the virtual inserted and deleted tables in your code.

Suppose I have a simple insert statement like this one:

I want to ensure that I get the data inserted into an audit table. Certainly I could have code that does the insert into two places, like this:

But it can be hard to get developers to use procs like this, they might forget or need to build dynamic SQL. There are the other issues of maintenance where I might update the first INSERT, but forget the second.

OUTPUT allows me to add a clause in my DML statement. I put this before the FROM clause, or in this case, before the Values clause. Then I include values from inserted or deleted along with any scalar values. That looks like this:

However, this returns the data to the console. What I’d really like is to put this into a table. For that reason, I then add an INTO clause, with my table name. Now when I execute this, I get the data from the inserted table added to my EmpAudit table.

A basic, quick look at the OUTPUT clause.

SQLNewBlogger

I ran into an issue with OUTPUT and realized that I hadn’t ever covered this basic concept for myself. As I learned a few things, I decided to write about OUTPUT. This took me about 10 minutes to cover the basics, and was part of a 30 minute session writing a few more pieces on OUTPUT.

August 24, 2016