Voice of the DBA

Author: way0utwest

Door Number One for T-SQL Tuesday #99

This is a good month for T-SQL Tuesday. It’s the end of single digits for this blog party, which is amazing. I can’t believe we’re 99 months in. I’m sure Adam Machanic is proud of his efforts here.

Aaron Bertrand hosts this month, and he’s given us a choice: door #1 or door #2. I’m choosing door #1.

Daddy’s Girl

I grew up in Virginia Beach, going through high school and playing elementary school volleyball where you could only score when you served. I loved the game, and eventually started to play on the beach during the summers at college and after I graduated. The game faded for me as I got married, had kids, and got busy with my career.

When my daughter was 8, I signed her up for a recreational league. They needed help coaching, and I volunteered. With another parent, we coached the same group of 7 or 8 girls for 4 years, 8 seasons, and had a great time. A few of us parents also got involved in adult leagues, playing at various levels.

When my daughter turned 13, she moved to a competitive league, as I watched. Across the years, I’ve watched my daughter play and love the game. As she became more independent, I started to spend time helping other teams, learning the modern game with rally scoring, liberos, and more.

This year I’ve taken on coaching a team of 14 year olds. Two nights a week, some Saturdays, and as I’m doing less traveling for work, I’m spending my weekends coaching at tournaments, and having a great time. As my body ages, and I spend less time at my own activities, I’ve turned to coaching others, and I love it. It’s a great way for Coach Steve to spend some time away from work.

February 13, 2018
Migrating MySQL Databases

A little out of my area, but I need to move the tsqltuesday.com database to a new service. The company running the db decided that they don’t want to keep a lower level consumer type tier. Since their minimum plan is now $20/month, I decided to look elsewhere. My first move was to try Azure Database for MySQL. This is a quick look at moving the MySQL data.

I have MySQL Workbench, so I started that up. I saw a note that this contained a migration wizard. I first created a MySQL database in Azure, which is really simple. Then I created two connections from MySQL Workbench. One to the existing database and one to my new one.

The next step was to start the Migration Wizard. This is under the Database menu.

As with all wizards, this opens with a welcome page. I clicked Start Migration to get going.

The wizard is fairly simple. Once I had this working, I walked through the wizard, choosing my source and destination. This was a simple process and things connected up once I’d opened the firewall.

After verifying connections, I selected everything. Since this is a WordPress site, it’s a fairly small schema and set of objects.

I basically clicked next, next, and let everything transfer. This only took about 5 minutes.

Once this was done, I queried the database, and everything was working. I saw my data and it appeared complete. No errors, so I assumed things were correct. Row counts seemed to verify this.

This was pretty simple, perhaps even easier than using SSIS and the Copy Database Task since this was built in.

Now to figure out how to re-point the application.

February 12, 2018
Production Scripts

One of the most useful parts of SQL Server is the SQL Agent scheduler. Over the years I’ve used this subsystem to automate work and ease the administrative burden of running a SQL Server instance. At times I’ve even used my SQL Agent to fire off business reports and alerts to nontechnical people that might need to take some action based on the data in our system.

One common task that I see SQL Agent used for is to backup a database using some method (Ola’s scripts, SQL Backup, etc.) and then delete the oldest backup. This is a common way of ensuring that you keep xx amount of backups around for your business. Often if the first step (the backup) fails, then the second step (delete backup files) doesn’t run. There are times where the execution choices between the steps aren’t configured or get changed, and you run into the situation that Paul Randall describes in issue #170 of the SQLskills newsletter. The backup step starts to fail, but the deletes keep running until you don’t have any more backups stored.

How does this happen? It’s surprisingly easy because most people don’t really treat their SQL Agent scripts and code like other code. This often isn’t tested well, and rarely tested across time and with unusual conditions. After all, who wants to muck with the time on a production server to be sure that your Agent job works across days? Who wants to force a backup to fail to see how the job handles an issue? Who wants to double check their code when BACKUP is fairly simple syntax and a few quick tests of the delete code works with text files renamed with .bak extensions?

Do you treat your SQL Agent jobs like the production code that they contain? You should. In fact, moving to a more reliable, repeatable, DevOps style environment means that any code in an Agent job needs to be version controlled, it needs to be tested, and it should be a part of some (hopefully, automated) deployment process that ensures that changes to the code are recorded and you are confident of which version of code is on your system.

SQL Agent is a powerful tool, but it’s also one that should be treated like a production system. Downtime and simple errors from careless scripting shouldn’t be tolerated. We should, and can, do better.

Steve Jones

The Voice of the DBA Podcast

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

February 12, 2018
What to Read in 2018

Last week I was looking for Database Weekly links and ran across Gail Shaw’s list of books from 2017. It’s an interesting list of fiction, and I might pick up a few this year if I run out of ideas. This reminds me of Paul Randal’s review (done annually) of his reading. I usually find a book or two in there that I’d like to read to enjoy a break from life or expand my horizons.

I was thinking about this as I’ve seen a few other posts from software and database users with books that they recommend. This was an interesting list from Xiaohan Zeng and I like this general list from John Sansom for SQL Server. I’ve run across a few others, especially from all the learning plans that people built for T-SQL Tuesday #97.

I have no shortage of fiction that I enjoy reading on a regular basis. This is a way for me to get a break from life, and I’m usually working my way through some book. This month I read the Will Robie series from David Balducci (1-4) and then started re-reading the Jesse Stone series by Robert Parker and others. Those are my downtimes and breaks from life. In the professional area, I’ve been working my way through R in a Nutshell as well as Thoughtful Machine Learning in Python. The latter has been ongoing for some time, and moving rather slow. It’s a tough one to go through.

There are classic texts in our industry, as well as some really interesting ones that many of us might enjoy. Perhaps there is something you’ve read in the last year you recommend, or there are some you want to tackle. Today I’m asking for your reading recommendations.

In our careers, I know many of us like shorter, focused posts, some like videos, and others prefer learning in person from others. Those are all great ways to learn, but I still like working through a book to either focus my learning or escape from life. Let us know today what you plan to read this year or what you’d recommend for others, either to help them become better database developers or just enjoy a good story.

Steve Jones

The Voice of the DBA Podcast

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

February 9, 2018