Voice of the DBA

Author: way0utwest

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
Moving Lookup Data with ReadyRoll

I’ve been using ReadyRoll for a small project and wanted to move some lookup data. There are some tables where I want to keep data in sync across environments, so if my build and release pipeline can do this, great. ReadyRoll makes this easy, and this is a short post to show how.

The Current Environment

I’ve got a table in my dev database that has SQL Server versions. I can see the list here:

In my QA and staging systems, I see this (only one is shown, but it’s the same in both).

The goal is to have this data migrate with other changes in a build and release pipeline.

Adding Data to the Project

I’ve already built this as a ReadyRoll project and set up a build and release pipeline in VSTS. Now I want to include my data.

In the ReadyRoll tool window, I have refreshed the project and I see everything in sync. That’s good, and that’s how I want to be starting a small section of development.

I want to find my table, so I need to expand the “Identical Objects”. When I do that, I see a list of all objects in the database.

I can scroll down to find my table. When I do, I’ll right click it to get a few options. One of these is “Include Table Data”. We want to pick this one.

Once I do that, I get a note to refresh again. This is so RR can determine what table data needs to be added to a migration script.

My changes show that I need to get 11 rows of data into a new script.

I click Import and generate script, which will build my migration script and add it to the project. In my case, this is script 4.

Note that this script has a few things in it, based on a call to SQL Data Compare in the background. First, it sets a dateformat. Next, it does the insert for an empty table, since that is the situation I’m in. Last, this uses the SET IDENTITY_INSERT option.

If you don’t like these options, change them here. You can alter this script to suit your environment. Remove the IF, let the identities be what they are, make the changes that matter to you.

The only thing I’ll do is click the script name twice to edit it.

The numeric sequencing is important. The rest, not so much. I’ll choose something simple here.

Build the Project

I can build locally, and I always should to be sure things work. Once I see this and I’ve tested a few things, I’ll let the “system” do more work.

The nice thing about ReadyRoll is that I can include my database project alongside application projects, if I want. For application developers or hybrid developers, I can use a consistent interface for saving changes to version control.

In Team Explorer, I’ll check changes, add a comment, and then Commit and Push.

If I go to VSTS, I’ll see the build in progress. I’m using a local build agent and usually within 15 seconds, my build will start. Sometimes it’s really fast.

If I click the build number, I can see I’ve barely caught this before it finished.

The build completes, so now I need to check releases. I have a CD trigger that will deploy to a local QA instance when the build succeeds. If I go look at the release, I’ll see that’s occurred.

This worked, so let’s check the QA instance. As you can see, my data was deployed.

If you were watching closely, you see I have a small data issue. I need to correct that, but that’s for another post.

ReadyRoll is a part of the SQL Toolbelt, and if you’re a customer, I’d urge you to start a PoC and see what you think of the tool. ReadyRoll Core, with limited features, is included with Visual Studio Enterprise, and if you’re a customer of Microsoft, you can try that. We also have 14 day trials if you’d like to do this on your own.

February 8, 2018
The Learning Choice

I advocate learning constantly for those of us in technology. This might be especially important for data professionals as we are at the nexus of so muc activity these days. Data is important, our systems are under scrutiny for security, there is an every growing set of applications that want to connect, and we often struggle to keep up in a fast-paced, DevOps world. It feels like my plate is overflowing with the need to understand PowerShell, JSON, the cloud, Hadoop and its myriad of related technologies on the Microsoft Stack, machine learning, data science, statistics, and when I have time, make sure I’m up to date with SQL.

Recently I’ve run across a few pieces that ask the question about where to spend time. There’s a blog on R v Python (I’ve seen quite a few of these) and a thread on deciding if ML skills are something a database developer should learn. These are just a few of the ones I’ve seen in the last month, as I guess lots of people are thinking and writing about New Year resolutions and goals.

We have a limited amount of time for learning each day, week, month, or year. We might be able to increase that for a short time, perhaps months, but all of us need some balance in life. Given that there’s a limited amount of time, and that we need some focus and practice over time to absorb skills, where do you spend your effort in learning?

Jeff Moden is an advocate of you sharpening your T-SQL skills with deep learning, as he sees this as one of the main reasons that applications perform poorly (and people post in our forums). Others lean towards adding new skills, perhaps adding SSIS proficiency or transitioning your analysis skills to Extended Events. Still others might seek to become more generally rounded by spending time on C# or Python.

There are also other views, such as focusing on the skills you need in your current position. If you’re not happy, maybe you want to devote learning effort to get a job in an area where you’re passionate. Or maybe you just want to make more money, and are willing to spend time in those technologies that are paying more.

The choice is up to you, and I can’t give you the answer that suits your path. You must find a balance between work and the rest of life, and make the choices that best fits you. I don’t want to imply this is easy. Thinking about the things that really matter to you and affect how you approach each day is a difficult exercise. My advice is that you go in one of two ways. If your career is something that you feel passionate about, focus your learning in a way that will help you find a job that brings you purpose. If you just need a job to pay the bills, learn things that increase your earnings, but ensure that you have family, faith, hobbies, or some other endeavor outside of work that brings purpose and fulfillment to you.

Steve Jones

The Voice of the DBA Podcast

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

February 8, 2018