Voice of the DBA

Tag: powershell

Moving Tasks with Copy-SqlJob
More dbatools experimentation for me. This is a great set of Powershell cmdlets that solve simple, handy problems. In this post, I want to look at Copy-SqlJob, which will allow me to easily move jobs from one instance to another.

The Job Subsystem

Jobs in SQL Server are part of the SQL Agent subsystem, which means they’re a part of SQL Server, but not really. Maybe the one part of the jobs that is really complex is getting the code for the job. You either need to use an SMO interface and script out the job, or you can decode the tables in msdb. Neither of which is handy.

We do need to move jobs, though. Perhaps the most common places I need to copy jobs around is for a DR (disaster recovery) situation. Clustering will move jobs from node to node as it covers the instance, but all the other technologies (AGs, Log Shipping, Mirroring), all of these only work with databases. So items like jobs need to be manually moved.

That’s not good.

Over time, we’ll edit, add, change jobs, but I’ve never seen anyone do a great job of actually ensuring the DR systems are up to date with jobs, logins, etc. all the time. There’s too much of a manual process.

That can change with Copy-SqlJob. I can now not only copy particular jobs between an instance, I can use a –Force command to ensure the copy takes place. This means that I can easily ensure the most recent version of my job is copied over. Or I can just skip this, and if the job exists, it’s not copied.

Running the Cmdlet

It’s easy to use this cmdlet. It can use Windows Auth or SQL Auth. It’s up to you. The documentation leaves something to be desired, so you’ll need to use the PowerShell Get-Help to learn what parameters exist.

For a simple copy, I’ll move all jobs from one instance to another. Here are the jobs on my primary SQL Server 2016 instance.

These are administrative, backup jobs and index jobs from Ola Hallengren. I don’t have these on my QA instance, which isn’t good.

Suppose this is a new instance and I want to add them? I can use Copy-SqlJob, but I want to know what will happen. I’ll use the –Source and –Destination parameters and then include a –Whatif. This gives me a list of jobs that will be copied, and those that won’t be.

If I use the –Force parameter, you’ll see that all jobs are copied.

Well, not copied, but they will be.

I could limit this to specific jobs. For example, let me just copy over these jobs
- DatabaseBackup – SYSTEM_DATABASES – FULL
- DatabaseBackup – USER_DATABASES – FULL
When I run the command with the –Jobs parameter, I see just these jobs moved:

Let’s actually do that. Once I remove the “”-Whatif” and run the command, I see this:

Note, this doesn’t actually move the dependent objects. Meaning the first job uses this procedure: [dbo].[DatabaseBackup]

I’d have to make sure this is copied separately for the job to actually run and succeed. Right now it will fail with an error that an object can’t be found. However, if you have jobs that you are using in a DR situation, this ensures the job itself is moved over.

Rather than copy the syspolicy_purge_history job, I’ll exclude it. I can do that with the –Exclude parameter. Note that the two jobs I’ve already copied are shown as warnings.

All the parts of the job itself are copied. The description, the owner, the comments, the schedule, the steps. Everything is copied, which makes this perfect for ensuring that when you deploy a job, it can be deployed to all of the places you need it deployed by copying it from the source.

Of course, you’ll also want to ensure you use some method to disable the jobs on the DR instances (along with re-enabling them on failover).
December 2, 2016
Hey Posh, Are My Services Running?–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In a previous post, I looked at escaping strings. The reason I needed to do this was that I was trying to do some automatic work with building and starting SQL Server instances. Part of laying the base for this was checking if services are running, and then perhaps taking action, like starting or stopping.

I knew there was a Get-Service command, and ran that. The output from this is much more than I’d like to consume.

I’d like to limit this to SQL Server services. I know there is usually an MSSQLServer service, but since I tend to use named instances, this doesn’t work. Plus, I don’t want to search for just a particular service. I want all services for SQL Server.

There is a Where-Object command, that allows me to search. There is also a –Like option for comparisons. I’ll structure a command like this:
```
Get-Service | Where-Object ($_.Name –Like “SQL*”)
```
That is less than successful.

Why not? Well, PoSh has some syntax requirements and one of them here is that I don’t want parenthesis, I want curly braces. If I change this, things work better.

If you’re older like me and don’t necessarily read small print easily, this might be one that catches you for a bit. However, notice that I only have my SQLAgent and system services, not the core database engine. My wildcard needs work.

Now I see all my services and I can easily decide if I want to stop, start, restart, etc.

#SQLNewBlogger

This was a quick post. It took me 10 minutes to relearn a few PoSh things and practice and then about 5 minutes to write this.

I’ll remember it, and it shows how I’m building my administrative skills. You should do that as well.
December 1, 2016
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
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