SQL Modernization and Cloud Migration Talk in Calgary

On 28 February 2017, I will be speaking at Microsoft Calgary as part of a roadshow for AMTRA Solutions. Lunch will be provided.

You can reserve your place for my session on Eventbrite, and there is no charge. The event starts at 9am.

The venue is at 110 9th Avenue SW (Suite 800), Calgary AB, T2P 0T1.

I will be discussing some of the new features of SQL Server and Azure SQL Database, in the context of modernizing your data platform infrastructure,  and there will be live demos.

After lunch, fellow MVP Kevin Kaminski will be discussing Windows 10 and its place in a modern and secure network. I’ll be staying to watch that session, which you can book for here.

Please stop by!

2016 in review

Lots of interesting things happened this year in the world of data.

As 2016 draws to a close, I’d like to encourage you to find a piece of Azure SQL Database, or SQL Server, and play with it, understand it, and teach someone else how it works.

One of the projects I’m looking forward to in 2017, is dealing with a massive volume of data from Internet of Things (IoT) devices. SQL Server seems up to the task.

See you on the other side!

A look back, and a look forward

Time flies. My father used to wear a t-shirt that claimed, “When you’re over the hill, you pick up speed.”

I’m turning 40 in a few days. I still feel like a teenager in many respects.

SQL Server, released in 1989, is 27 years old now. It’s about the same age I was when a career change became necessary. I became a lecturer, briefly, then a high school teacher, and a tutor on the side, sharing my knowledge, all the while learning how people learn.

Combined with my so-called “train the trainer” learning a decade prior in college and the help desk support job from around the same time, it allowed me to unlock ways to explain technology to people. In so doing, I realised that I had a real arrogance about technology. Whereas I wanted people to bend to its ways, I realised that it was technology that had to adapt. Now when I build products, the aim is to be as simple as possible.

When I went back into the tech world after teaching (for money, as it turns out—it’s always about the money), I tried to keep this new perspective. We need to review our thinking and assumptions more often than we do.

SQL Server, and Azure’s Platform as a Service, have come a long way in a relatively short amount of time. Things we had to worry about as little as four years ago are being made much easier by the newest versions. Self-tuning databases, the Query Store, In-Memory OLTP, and recent changes to SQL Server 2016’s Service Pack 1 are all things Microsoft has learned and borrowed from competitors and customers alike, to produce probably the best relational database engine currently on the market.

Microsoft challenged their own assumptions. The market required it. Five years ago it was unheard of, the thought of Azure hosting Linux and Windows equally, with NoSQLs and Red Hats and Oracles and SAP HANAs, oh my! We even have Linux on SQL Server now. I still don’t know what it’s for, but it’s really very clever how they did it.

(Full disclosure: I didn’t know what cameras on phones were for, either.)

I’m a technology Luddite. I literally break technology more often than I care to admit, though admittedly not because I think it’s taking my job. I call myself a living edge case and a human beta tester. It’s usually accidental, but if something is going to break, it’ll break around me.

As a result, while I tend to have the latest shiny computers and electronics, I write my important notes in a Moleskine notebook I carry with me. It was a gift from my spouse and has Smaug on the cover. It has no lines on the pages, because sometimes I need to sketch things in meetings. I prefer a resilient hard copy any day of the week, and flipping back the pages of the book has a certain feeling that technology can’t touch.

When SQL Server 2000 was released, Microsoft’s marketing claimed that the database server could look after itself. Of course this turned out to be false, and I was still working with 2000-era databases until quite recently. Marketing played nicely into my bank account, thank you.

However, the trend towards not having to manage databases is continuing apace. Azure SQL Database takes much of the administrative burden away so that most people who need a database can get one easily, without concerning themselves about maintenance tasks and how the data drive was formatted. Technology is getting better. It is adapting better to people.

Looking back on 2016, I recognize my only fondness for past eras of computing is borne by nostalgia. SQL Server 2016 is the best version of the stand-alone product. Azure SQL Database keeps getting new features so frequently that it obsoletes my blog posts, sometimes only days later.

I am looking forward to the new challenges this brings. I feel overwhelmed sometimes, but I know I’m not alone. There will always be performance issues, and database corruption, and customers not taking or testing their backups. I’ll find a gap there, and as for all the other stuff, I’ll keep learning.

If you would like to chat about this some more, feel free to contact me. I’ve taken a break on Twitter, but you can find me there at @bornsql , and I respond to direct messages.

Do you need access to Production?

During a recent client meeting about a database migration, I realised that I have never logged into a SQL Server on their production environment. My involvement has been strictly dealing with setting up the new environment and log shipping the backups.

I get that I’m not a full-service DBA for this client, but it got me wondering about the many security discussions I’ve seen and participated in, in the past: that not even a junior DBA might need access to production database systems, if it’s not within the scope of his or her work.

From this experience, I can honestly say that my part of the job has not been impacted by not having full access to the production environment.

Have a look at your own network, take the time to think about who has access and who actually needs it. Chances are, developers really don’t need sa on your production database server, and you will be able to reduce the attack surface of your systems.

Remember that many incidents of data exfiltration are internal.

Career Limiting Moves – Dropping a Table

In this new series, I will share some things that took place early in my career that could have resulted in my looking for new work—but didn’t!

This post is also evidence that I haven’t always worked with SQL Server.

I used to work with a PeopleSoft implementation partner, sometime last century. One morning I was on site, at a large technology company in South Africa. My senior consultant was walking me through running a script using a tool called Data Mover, against the production HRMS database.

The script created a new table in a database, copied the data from the old table, fixed up all the referential integrity, and then dropped the old table.

For whatever reason, we ran the script in the Oracle SQL*Plus client, which Wikipedia generously describes as “the most basic” database client.

Cut to the part where we run the script. The DROP TABLE command was run successfully and committed, but the previous step where the data was moved had failed.

The entire table was gone.

The senior consultant who had walked me through that process realised what had happened, made a call to the database administrator, and the database was restoring in short order.

All work that had been done that morning was lost, and it took about 2.5 hours to restore the previous night’s backup.

Moral of the story: make sure you run the right script in the right tool, and make sure your backups are being tested regularly.

Filmmaking as a Metaphor for the DBA

I worked on four films in 2015, three shorts and one feature-length movie, all shot in Calgary where I live. That has resulted in seven IMDb credits for me, someone who earns a living as a DBA.

If nothing else, that experience has scratched an itch I’ve had since I was old enough to wonder what it would be like to act in a movie.

But acting isn’t filmmaking. It’s a very small part of the big picture, along with directing, producing, set building, makeup, lights, cameras, craft services, animal trainers, and so on.

DBAs also do a lot of work behind the scenes to make sure everything works the way it should. The sign of a good DBA is a system that works as expected. The sign of an excellent DBA is recovering from failure, affecting anyone else as little as possible.

Like being an excellent DBA, making films is hard work. Purely from an acting perspective, there are lots of lines to learn, repeating them over and over again, and then having to wait for someone to reset the camera, move some lights or the boom mic, and then do it all over again.

Exactly the same way.

Acting is the antithesis of automation. For example, it can take nine hours to film five pages of a script. Each page in a screenplay equates roughly to one minute of screen time. When I directed our last short, we shot eighteen pages in seven hours. That’s almost unheard of.

In information technology, we are encouraged to automate any repetitive task.

In front of the camera, we can’t automate our lines. Continuity is critical, so that the cup you’re holding at 8:15am during the master shot, is in the same hand at the same line, with the same level of liquid, as the close-up shot at 11pm.

I have also done a little bit of voice acting. Have you seen the film Singin’ in the Rain, starring Gene Kelly and Debbie Reynolds? She plays a voice-over actor who must redo all the voice parts for Jean Hagen’s character, in a process called ADR (automated dialogue replacement) or Looping.

There’s nothing automated about it. You see the scene and the current audio, and get a metronome counting you in for two or three beats, then you record your dialogue, trying to match against the picture. It’s expensive and time-consuming, and never quite matches.

Sometimes you have to do it in voice acting too. Except, excluding some very minor exceptions, there’s no picture to watch yet. You are in a booth, with headphones, a microphone, and pop filter in front of you. In my case, there’s also an HD web cam in there so that the outside world can see in. In other studios, the booth may be soundproof glass and have the recording equipment and director in view. It’s a very lonely space.

Either way, if I have to do ADR for a movie like Debbie Reynolds did, she’d have a picture to lip sync with. In voice acting, if you have to do ADR, there’s no picture. You hear the original track, you get counted in, and then you do your line while the old one is playing in your headphones.

Try recording yourself, playing it back, and then saying the same line over again, exactly the same way.

Being a DBA has a lot of similarities:

  1. Repetitive tasks
  2. Attention to detail
  3. Troubleshooting with no visual guides
  4. Trying to do something complicated while someone is talking in your ear
  5. Someone is always judging you
  6. You have to go with your instincts sometimes.

Someone asked me recently whether I would choose between being a SQL Server professional, or a filmmaker. I answered that I couldn’t choose. They complement each other and keep me sane.

Thanks for reading. If you’d like to comment on Twitter, find me under @bornsql.

Announcing Azure Blob Storage Sync and Restore

Yesterday at SQLSaturday #407 in Vancouver, I announced availability of a free tool to synchronise and restore your SQL Server backups using Azure Blob Storage.

It can work alongside your existing backup process, and leverages Ola Hallengren’s Maintenance Solution.

More about Azure Blob Storage Sync and Restore.

GitHub Page: https://github.com/bornsql/azureblobstoragesync.

Do it now: Date and Number tables

Your production environment needs Date and Number tables, to help you Get Stuff Done in sets instead of RBAR*.

Doug Lane has a video on Why You Simply Must Have a Date Table. I really loved the intro.

A Numbers Table is also very useful for similar reasons. Jeff Moden has been harping on about so-called Tally Tables for years.

Check out Greg Larsen’s post, Creative Solutions by Using a Number Table, for some nifty uses.


* Jeff Moden coined the acronym RBAR, or Row By Agonizing Row.

The curious case of RESOURCE_SEMAPHORE

A customer is running in a SQL Server 2000 environment, 32-bit AWE mode, on 64-bit Windows 2003 R2.

The server has 16GB of RAM and 8 logical CPU cores. It’s running under VMWare. Storage is provided via a (slow) SAN. SQL Server has ~12GB of RAM assigned to it.

During monitoring of long-running queries, we discovered that a frequently-accessed stored procedure (known for being a beast: it contains a WHILE loop and several JOINs) was doing worse than usual.

After adding some indexes, we put in a change to convert the table variable in this stored procedure to a temp table. That was on Tuesday night.

On Wednesday, the wait stat RESOURCE_SEMAPHORE was in the top three (behind OLEDB and CXPACKET). Memory pressure, and so much of it? I had never seen that wait stat get so high.

This morning it was the same: RESOURCE_SEMAPHORE was climbing by the second, and the CPU and I/O was thrashing. Looking in sysprocesses was a vision of CXPACKET and RESOURCE_SEMAPHORE.

While it’s obvious now what caused it, I was completely stumped for the whole of yesterday.

This morning, we rolled back to the table variable version of the stored procedure, because it was causing blocking on the database server.

One minute after rolling back, RESOURCE_SEMAPHORE stopped dead in its tracks. It’s been two and a half hours and it hasn’t changed its value.

So tempdb contention caused memory pressure. Well, sure, it happens, and this is hardly the first time I’ve heard of that, but it demonstrated a significant difference between using a table variable and a temp table.

So while I will stick with recommending a temp table over a table variable in most cases, this is a fine example of “it depends”.