Tag Archives: videos

ServerLabels in Minion Backup

There’s a great way to increase the effectiveness of your backup and HA strategy: use the ServerLabel feature in Minion Backup.

The problem with most backup solutions is that they don’t take AG failover into account.  Here’s a common scenario to show you what I mean.

Let’s say you’re backing up to \\BackupNAS\SQLBackups.  Most of the time, your backup routine will append the server name and probably the database name to the path.  There are other things that can get added, but we’ll keep this simple.  So your backup path winds up looking like this instead: \\BackupNAS\SQLBackups\Server1\MyDB.  The problem comes when you’re running an AG and you’re either taking backups on different nodes or when your backup node fails over and the backups continue on a different node.  Either way you’re stuck with your backups being in two different locations.  Here’s what I mean.

Before Failover:

Full backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB

After Failover: Log backup – \\BackupNAS\SQLBackups\Server2\MyDB Log backup – \\BackupNAS\SQLBackups\Server2\MyDB Log backup – \\BackupNAS\SQLBackups\Server2\MyDB Log backup – \\BackupNAS\SQLBackups\Server2\MyDB

Fail Back to Original Node:

Diff backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB

So you can see that there are different backups in different locations.  And the log chain starts on Server1, then moves to Server2, and then back to Server1.  This can make it very difficult to build a restore statement if you don’t really know where your files are going to be.  And also, if you look above you’ll also see a diff backup was taken once it failed back to Server1.  But if Server1 wasn’t the primary node, then the diffs would be taken on another server which would add a 3rd one into the mix.

This exact scenario is what we have solved in Minion Backup.  With MB you can define a ServerLabel that gets used instead of the server name.  Let’s say we define the ServerLabel to be ‘AGListener1’.  We can do that with a simple update statement like this:

 

UPDATE Minion.BackupSettingsPath

SET ServerLabel = ‘AGListener1’

 

Now every backup on your server is going to use this ServerLabel instead of the server name.  Here’s what the failover scenario above looks like with this new ServerLabel.

 

Before Failover:

Full backup – \\BackupNAS\SQLBackups\AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB

After Failover: Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB

Fail Back to Original Node:

Diff backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB

Problem solved.  Now no matter which node you backup on, the files go to the same location.  And why not?  They’re the same database aren’t they?  So why complicate things by having them go to separate locations just because your AG failed over.

And it’s not just for AGs.  You can use a ServerLabel for any server you like.  Say you want to associate it with the DNS name of the server instead, or the application name.  That’s just as easy.  ServerLabel is here to give you a customizable server name to use in your path.

And it doesn’t stop there.  You can setup a ServerLabel for specific databases on a server, or even for specific backup types or backup types for specific databases.  It’s very flexible.

It’s such a tiny, unassuming feature, but it can have tremendous effects on your backup scenario.  You can see this http://midnightdba.itbookworm.com/Video/Watch?VideoId=428, and our other Minion Backup videos at http://midnightdba.itbookworm.com/Minion/Backup.

Move DB Files with Regex

Hey guys, I just posted a new vid on how to use Regex to format DB files so you can easily move them.
The situation is this… you’ve got lots of DBs you want to move to a new drive or to a new server, and you need to write the ALTER DATABASE commands to move all the files, then you need to script the move statements at the cmdline like Powershell. The problem is to be effective, you need some code to parse the filename from the rest of the path.
Here I’m going to show you how to use Regex to parse that out so you can easily build the statements you need. I can write the script to move hundreds of DB files in under a minute. Come watch.
http://midnightdba.itbookworm.com/Video/Watch?VideoId=407

And here’s the demo code so you can follow along.
MoveDBFiles

Powershell till you drop

Ok, well I’ve been very busy again and released 3 new PS vids today. 

There’s one on dropping tables.  I use regex to make this happen, so even if you’re not interested in dropping tables, you can come learn how to do a simple regex.

http://midnightdba.itbookworm.com/VidPages/PowershellDropTables/PowershellDropTables.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellDropTables/PowershellDropTables.wmv

 

The next one is on truncating tables.  Here I just limit it by a specific schema.

http://midnightdba.itbookworm.com/VidPages/PowershellTruncateTables/PowershellTruncateTables.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellTruncateTables/PowershellTruncateTables.wmv

And the last one is just damn good.  I show you how to get sp_configure functionality in PS.  There’s a trick to it so don’t discard it cause you think you can figure it out on your own. 

http://midnightdba.itbookworm.com/VidPages/PowershellServerConfigSettings/PowershellServerConfigSettings.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellServerConfigSettings/PowershellServerConfigSettings.wmv

 

Get IP and DNS in Powershell

Hey guys, I posted a new video last night on how to get IP and DNS info from your servers.  I know there are more ways to do it so if you guys have a way you like better send it to me and I’ll make another vid.

http://midnightdba.itbookworm.com/VidPages/PowershellGetIPandDNS/PowershellGetIPandDNS.aspxhttp://midnightdba.itbookworm.com/VidPages/PowershellGetIPandDNS/PowershellGetIPandDNS.aspx

 

Hey, how about some new Powershell vids?

I’ve been a busy little guy this week.  I’ve posted 4 new videos.

The first one is on cycling the SQL error log from Powershell.  Well, not really, you’re really deploying the solution to other boxes using Powershell.
You can see it here:
http://midnightdba.itbookworm.com/VidPages/PowershellCycleErrorLog/PowershellCycleErrorLog.aspx

The next one is about reading the SQL error log from Powershell.

You can see it here:  http://midnightdba.itbookworm.com/VidPages/PowershellReadErrorLogs/PowershellReadErrorLogs.aspx

The next 2 are a short series on changing DB permissions in Powershell.
You can see them here:

http://midnightdba.itbookworm.com/VidPages/PowershellChangeSQLPermissions/PowershellChangeSQLPermissions.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellChangeSQLPermissions2/PowershellChangeSQLPermissions2.aspx

Changing Job Step Properties in Powershell

I read a blog today from my good friend @SirSQL (Nic Cain) where he was talking about how to change retry attempts on all the jobs on a server. Well, technically it’s the job steps that have the retry attempts, not the jobs themselves. And whenever I see something like this, my fabulously huge MCM brain tries to turn it into Powershell. So I put my fingers to the keys and like 10mins later I had a workable script. And while Nic’s solution is perfectly good T-SQL, it’s another one of those things that highlights the power of the shell because I only need 3 lines of code to do it.

The easiest way to do this is through SSMS. Just start PS from the Jobs node.

And that’ll give you a blank SQLPS window that’s already in the jobs node.

Since we’re in SQLPS we don’t need to load the assembly, but I’ll do it anyway to show you how cause you might not be in SQLPS when you do it. It won’t hurt anything to load it again. But that’s one of the things that SQLPS does for you; It loads these assemblies.

Here’s how you load the assembly and set a variable to the JobStep object type.

?View Code POWERSHELL
1
2
[reflection.assembly]::loadwithpartialname("microsoft.sqlserver.smo")
$js = new-object microsoft.sqlserver.management.smo.agent.jobstep

Now we have our new $js var (stands for JobStep) we can fill it with the steps of all the jobs on the server. However, first, let’s take a look at the members of our new var.

?View Code POWERSHELL
1
$js | gm

Now you’ll get a good listing of all the members so you’ll know what you can and can’t do with it.

Towards the bottom you’ll see the property we’re interested in here: RetryAttempts. Now we just have to go through all the steps and change the retries to what we want. Here I’ll change it to 5 just because it’s a nice round number. You’ll be surprised how easy it is to do this. I’ll go ahead and tack it onto the partial script above and this will become our entire script.

?View Code POWERSHELL
1
2
3
4
5
[reflection.assembly]::loadwithpartialname("microsoft.sqlserver.smo")
$js = new-object microsoft.sqlserver.management.smo.agent.jobstep
 
$js = dir | %{$_.enumjobstepsbyid()}
$js | %{$_.RetryAttempts = 5;$_.alter()}

Ok, that’s all we need to do to change the RetryAttempts property for all the jobs on the server. But we’re not done talking about this… not even by a longshot.

First, in line 4 notice I call the EnumJobStepsByID() method on each item of the dir. This is how I populate the $js var with all of the job steps. What this line says is list all of the jobs (using dir) and then for each one, get a list of its steps and put it in $js.

Line 5 runs through each of the job steps in $js and actually performs the work of setting the RetryAttempts to our new value. And remember, jobs have an Alter() method, and typically whenever something in PS has an alter method it likes you to use it. if you don’t the changes will take effect in your PS session only and will not be pushed to the server. So call the Alter() method.

Now, I know what you’re saying… how would you do it for only some of the jobs? Because so far we’ve assumed that you want to apply the changes to every job on the server. Well, there are 2 ways to do that and they’re incredibly easy.

First, you can limit the data from the dir cmd in line 4. It could look like this:

?View Code POWERSHELL
1
2
3
4
5
[reflection.assembly]::loadwithpartialname("microsoft.sqlserver.smo")
$js = new-object microsoft.sqlserver.management.smo.agent.jobstep
 
$js = dir | ?{$_.Name -match "maint"} | %{$_.enumjobstepsbyid()}
$js | %{$_.RetryAttempts = 5;$_.alter()}

Take note of the new line 4. I’ve just added a where clause to the pipeline so now only jobs with the word “maint” in their names will be in the list.
The 2nd way is just as easy, but you do it at the job step level. if you remember from above when we looked at the methods for $js there was a property “Parent”. This is the parent job name for the step. So all you have to do is add the where clause to the $js instead and you’ll achieve the same thing.

?View Code POWERSHELL
1
2
3
4
5
[reflection.assembly]::loadwithpartialname("microsoft.sqlserver.smo")
$js = new-object microsoft.sqlserver.management.smo.agent.jobstep
 
$js = dir | %{$_.EnumJobStepsById()}
$js | ?{$_.Parent -match "maint"} | %{$_.RetryAttempts = 5;$_.alter()}

Now the new line 5 reflects our easy change.

There are so many uses for this code it’s incredible. There are plenty of properties to change and so many ways to limit the result set. Let’s say you have a maint routine on all of your servers and you want to alter the command that the 1st job step runs. That’s very easy. Instead of changing the RetryAttempts property, just change it to the Command property like this:

?View Code POWERSHELL
1
2
3
4
5
[reflection.assembly]::loadwithpartialname("microsoft.sqlserver.smo")
$js = new-object microsoft.sqlserver.management.smo.agent.jobstep
 
$js = dir | %{$_.enumjobstepsbyid()}
$js | ?{$_.Parent -match "maint"} | %{$_.Command = "My new code.";$_.alter()}

There’s nothing here Nic couldn’t change his script to do pretty easily, but this is much shorter and easier to read I think. It also has one huge advantage… I can run it on multiple servers without much effort. I’m not going to go down that road in this blog because I’ve posted many solutions with that code in it already so it wouldn’t be anything for you to add that to this script.

So anyway, thanks Nic for letting me use you as the base for this post. Your solution is fine, I just prefer the look and feel of 4 lines of code.

I’ve also got a companion video for this post:

http://midnightdba.itbookworm.com/VidPages/PowershellChangeJobStepProperties/PowershellChangeJobStepProperties.aspx

SSRS Videos

I posted 2 new vids this week on SSRS.  The first is a data-driven subscription primer that’ll get you up and running with not only what it takes to set it up, but the different methods you have for sending the results exactly the way you want them.  I even go out into Profiler to show you the effect it has on the system.  It’s about 30mins, and you can find it here:  http://midnightdba.itbookworm.com/VidPages/SSRSDataDrivenSubscription/SSRSDataDrivenSubscription.aspx

The 2nd vid is about deploying reports from VS.  This is a common question I get from noobs and because it’s not obvious how it works, I went ahead and filmed it.  I also cover one of the common problems you have when deploying reports using this method and how to solve it.  It’s about 4mins and can be found here:
http://midnightdba.itbookworm.com/VidPages/SSRSDeployReports/SSRSDeployReports.aspx

 

 

Changing user passwords

Most of us have sa and have for a long time, so exactly what it takes for an end user to change his own password isn’t typically a concern for us. And since it came up at work yesterday I decided to make a video about it.
So if you’d like to see what it takes for a user to be able to change his own password, you can see the new vid here:
http://midnightdba.itbookworm.com/VidPages/ChangeUsersPword/ChangeUsersPword.aspx