Voice of the DBA

Author: way0utwest

Validating a Set of Database Scripts using DLM Automation

The basis of all the DLM Automation from Redgate is a series of PowerShell cmdlets. They might look intimidating or confusing, but they aren’t. This is part of a series of posts that examine how you use each one.

Previously I looked at New-DatabaseConnection. In this post, I’ll go through Invoke-DlmDatabaseSchemaValidation. This is the cmdlet that one uses to check if your set of scripts will actually produce a database. This is equivalent to the “build” plugin that exists for a few platforms.

The way this works is that the location of the database scripts is passed to this object through a pipe. This will then validate the scripts on LocalDB with a build of the database and the static data scripts. If this works, then an output object is returned.

A Quick Build

Let’s see how this works. I have a valid database folder on my computer. This has all my object code in subfolders, including static data in the data folder. I want to validate this folder.

I can do that with this code. I’ll pass the location of the scripts into the cmdlet.

$output = “e:\Documents\GitHub\SimpleTalk_Devlopment\ScriptFolder” | Invoke-DlmDatabaseSchemaValidation

When I do this, a LocalDB instance is created and the code validated. I get a message to that effect. The output variable has the confirmation message.

This means the code is valid. However, does this really work? Let’s edit some code and see. I’ll change the code for a procedure. Here’s the original GetCountryCodes.sql.

Let’s change this to top 100 and add an ALTER, but I’ll get an extra comma in there. This is no longer valid SQL.

Let’s re-run the build. We now see this has failed with an error, and the file is the one I edited:

This is a quick look at builds, but there is more that can be done. You can specify the server and database to be used, combining this with the New-DlmDatabaseConnection I previously wrote about.

I urge you to experiment with this cmdlet if you want to perform your own builds.

November 28, 2016
Hire the Right People

I’ve seen this list of why projects fail going around the Internet for awhile (recently from Brent Ozar Unlimited). There are a number of items in here, and if you substitute some other field for the data scientist part, you might have seen some of these issues as well. One of the items that really struck me was number 5, which is titled: You shouldn’t have hired scientists. The other part of the slide is that ETL needs data engineers (whatever those are) and reporting needs BI analysts.

I with that we had good ideas and definitions of what different jobs are supposed to do in a company, and what skills are helpful. The more I look at job descriptions and responsibilities, the more I find that DBA, analyst, data xxx-anything are just amorphous concepts that vary dramatically from organization to organization. In fact, since the title that one has often determines pay scales, I think that we start to covet titles and work towards getting a title, regardless of the work we do. This isn’t helpful for anyone, least of all HR departments that try to match salaries to value and pay for particular jobs.

There isn’t a great solution here. Our industry is young and changing more rapidly than any other in history. New jobs are created out of thin air, to meet the changing face of software development and system administration. Build master, data scientist, blockchain engineer, and a few others didn’t exist when I started working in technology. Whether those jobs are needed in an organization might not relate to whether or not anyone has those titles, or if they perform the tasks we might expect of those positions.

I think that many of us are expected to be able to perform any task tangentially related to our position. Or we should be able to learn it quickly. DBAs should understand replication as easily as they might Availability Groups and configuring Extended Event sessions. A developer should be able to write C# as well as T-SQL. Both should be able to use SSIS to import and export data. While I’m sure many of us could do those tasks at a rudimentary level, are we really competent and capable at each of those tasks? Maybe enough for our organizations, maybe not.

When we embark on projects, there may be needs to accomplish tasks outside of the core competency of our staff. That’s fine, and since many people in technology are willing (and excited) to learn new skills, this situation may be perfectly acceptable for our project. When there are core skills critical to the project, such as a deep understanding of large scale ETL processes, we are probably better off hiring people that have those skills or investing heavily in just-in-time training for existing staff. Hoping that the average DBA or C# developer can just “pick up some tips” is a recipe for project failure.

Steve Jones

November 28, 2016
Quick PoSh Kills with Stop-DbaProcess

I’ve been trying to get used to using the dbatools cmdlets in PoSh. They help me learn some PowerShell, but they also make some things easier. As a part of my practice, I’m documenting the various items I’ve played with. This post looks at Stop-DbaProcess.

Killing Spids

At first I didn’t think much of this cmdlet, because I need to know a spid right? The time it takes me to run sp_WhoIsActive or sp_who2, find a spid, and decide to kill it is most of the time. A quick “kill xx” is easy in SSMS.

This cmdlet does more, which makes it really handy for me. I know it’s just issuing a kill in the background, but it does have some features that perhaps make this more enticing, especially during times where I might need to quiesce a server and remove a number of users.

The first thing that comes to mind is that I can quickly kill all the users using a particular program. For example, I had a demo app with a generic connection to SQL Server. When I ran it, there were multiple connections to SQL Server. If this had been behaving badly, perhaps with a long running query, I’d have had to kill both of these to ensure I got the right one. In a demo, perhaps “kill 60” and “kill 62” is quick enough.

I could have done this, though.

Way more typing, right? Sure it is. There’s an advantage, which I could have used more than a few times in my career. I didn’t have to look up the Spid with PoSh. In various jobs where I’ve had poorly behaving applications, I’d have to get a list of processes, find the numbers, and kill each of them individually. Easy as a one-off, harder (and annoying) when you are interrupting work regularly to repeat a tedious process.

With Sop-DbaProcess, I can keep this script ready and have it kill all the connections using that program name, whether there is 1 or 100.

What’s more, I have more options to exclude particular spids or logins, limit this to databases, run across multiple hosts, etc.

Simple, easy, and useful in some situations. I wouldn’t kill a single process with this, but I’d certainly want to use this if I had to repeat myself over and over.

Give dbatools a try and see where you might start finding PoSh to be useful while administering SQL Server.

November 25, 2016
Escaping a Dollar Sign in PoSh–#SQLNewBlogger

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

I’ve been working more with PowerShell lately and ran into a problem I thought would be easy, but it wasn’t. So I decided to blog what I learned.

Assigning a String

I had a string that I wanted to use in a command. In this case, the Start-Service command. Here’s what I wrote:

Start-Service -Name “SQLAgent$$SQL2016_QA”

This didn’t work, mainly because of this error.

PoSh thinks my string is “SQLAgent\”, not “SQLAgent$SQL2016_QA”. That’s strange, at least to me. Let’s try a variable.

OK, I know I need to escape the dollar sign. I ran a quick Google search, because that’s way quicker than looking through documentation and found a piece on escaping strings. It mentions the backtick (`) as the character to use. Let’s try that.

It works. Now to try to start the service.

No error, but it is it running? Yes.

This is a quick look at string work in PoSh. The more I try to automate work and get things to run themselves, the handier I find PoSh. Since I tend to work with named instances, this was valuable.

#SQLNewBlogger

This was one of those items that I spent about 5-10 minutes figuring out and then another 10 minutes shooting screens and duplicating my work. The writeup was easy, and it will help me remember how to do that in the future.

November 23, 2016