Today we are going to be looking at SQL Diagnostic Manager, more specifically, the capabilities that SQL Diagnostic Manager has in the area of script scripting or management of the tool through PowerShell commands. So, first of all, we need to log in to the server where the PowerShell snap-in for SQL Diagnostic Manager resides, and that is going to be the server where your SQL Diagnostic Manager management service resides. So I have already brought up that server and so what I am going to do is open up PowerShell. Down here at the bottom we have the PowerShell button that we can click on which will bring up the little window.

A couple of things we are going to do in preparation. First, we need to add the PowerShell snap-in for SQL Diagnostic Manager then we need to establish kind of a workspace or what we call a drive location for weight where we can execute the little commandlets from. And then we can actually perform the management of SQL Diagnostic Manager and that management will include features such as adding instances, removing instances, adding users, setting instance properties and configurations, and even establishing maintenance mode. So that is what we are going to do today is just kind of step through a couple of the commands here.

So first thing I am going to do is add the snap-in for SQL Diagnostic Manager. So what we will do is type in add PS snap-in a PowerShell snap-in and the name of the snap-in is going to be SQL DM snap-in, just like that. Hit enter we should see it go to the next line. Next, we need to build a workspace or what we call the drive for this snap-in. So I am going to type in now […] SQL DM drive.

I am going to give it a friendly name, and I will call the name something, like SQL DM. Nice and simple. Now the next part of this command, we need to establish what instance it is going to connect to and the name of the SQL DM repository. You will hear me refer to SQL Diagnostic Manager, SQL DM, for short today. So SQL DM repository is the typical default name of the database where the data resides, but you may have given it a different name in your environment, but I will just go with the default today. So, first the instance, and then the repository. So we have new SQL DM drive the name of that drive with the instance in the repository so we hit enter and we should see that it now creates that drive location called SQL DM with the root of SQL DM colon backslash.

Next, we need to change into that directory, so let us go with a CD command – SQL DM colon backslash. Alright, that is done. No errors at this point. It is gone to the second line, meaning it has accepted that command. So the next part is perhaps to get some help on what are the available commands. To do that, we can do a Get Help and SQL DM. This will bring up the available command lets that a part of that PowerShell snap-in called SQL DM snap-in. Now you have these available commands or command lets. If you want to go further you can get help on any of these commands, just type get help space and then the name of the command let and you can even do a dash detailed if you want to get more detailed information, including some examples of syntax for the use case.

I am going to stick with two particular commands to keep our video kind of to the point today. I am going to add an instance, and I am going to put an instance in maintenance mode.

So first let us go with adding an instance. As you can see through the list we have new, we have removed, and we have set as the beginning of some of these commands. So I am going to go with first the new SQL DM monitored instance. Let us type that – new SQL DM monitored instance. That is going to be the command that we call. Then we want the path, so we are going to do path. The path is going to be what is the instance that we are actually adding, what is the instance name. If you expect that some of your instances that you add in this path, and if you can add multiple instances just make sure you have the parentheses around this path, but if any of those instances may have an awkward naming standard then we will put in here escape for any of those types of characters and SQL DM name and then you can put the name of your instances but first we put the parameter of dash name and then we can put our instances with commas between the instances. I am just going to add one of my instances web SQL dev 02. Then what type of authentication. I am going to leverage Windows authentication today because that is going to essentially use the credentials of the SQL Diagnostic Manager collection service to make that connection. You could also put in the SQL Authentication option, the sa [system administrator], and then supply or whatever your credentials are. When you actually put in a SQL account, it will prompt you for the password for that, but for today we will put in Windows authentication, all one word. Now hopefully we have this all correctly listed, and so when I hit enter, I should see that instance added behind this scene here in my list of servers. It might take a moment, but we should see it, so let us go ahead and hit Enter.

Okay, so it displays some of the details and then on the left-hand side, let us give it just a moment. In fact, we might even hit the refresh on the console. There it is. So now we have officially started monitoring web SQL dev 02. And as you can see, it takes just a moment for it to register, but it is now plugged in at this point.

So that is how you would add an instance. You can play around with all of these different commandlets. I am going to do one more here. I am going to put maybe this SP server one instance into maintenance mode. So to do that let us go back over to PowerShell. And we are going to type in the maintenance mode. Again, if you go in and do the Get Help on the sets equal DM monitored instance command let, it will give you different options for different commands that you can send different parameters that go along with that command let that you can send to that instance. Today I am just simply going to put an instance in maintenance mode. So let us go with set SQL DM monitored instance. We will go with the path again. We will put the escape in just depending on what types of names you have for different SQL instances and when I say characters, like what I mean is if it is not the default instance and you have a backslash then you might want to consider doing the escape. And we will go with escape dash SQL DM name then dash name again, and the name of the server which is going to be sp serve 01 like that. And again whatever additional parameters we are going to go with maintenance mode always, which is one of the parameters that goes along with that set SQL DM a monitored instance command let. So when I hit enter, it does its thing, and we should see over here on the left-hand side that SP server 01 goes into maintenance mode. That would be evident by a little gear icon that shows up on the instance. As you can see now SP server 1 is in maintenance mode. So the way I have been interacting today with the PowerShell commandlets, has been through the PowerShell interface but as you can see this would provide an easy way to build out scripts and basically administer your deployment of SQL Diagnostic Manager without having to actually bring up an interface, which would make it easier just from an automation perspective or adding many instances at the same time. That is something that you might do with the PowerShell interface as well.

Hopefully, this video has been helpful to show you both how the PowerShell commandlets can be used but also how it can be beneficial to you on a day to day basis. Also, you can refer to the documentation or to a sales engineer in the company to give you more information or assistance or call tech support for assistance as well.