Welcome to our virtual education. In this video we are going to talk about the different ways. And options you have when adding instances to SQL Diagnostic Manager. Now there is two different ways to do this. What we are going to show you first is how to mainly add instances the […]. But you can also add instances through PowerShell. And I will pull up a screen in a minute that shows you the different options for not only what you can do when adding instances, the diagnose window to PowerShell. But things that you can modify in diagnostically related to the instances that have already been added. So if you are a brand new customer. And you have not added instances to SQL Diagnostic Manager. Yet it is pretty easy. The splash screen that opens up the first time you install the console. It will ask you to add instances. If you have already gone past that screen to add new ones you can just right click on all servers. Manage SQL Server. And it will bring up a wizard that first off shows you which instances are there already. But then at the bottom you can say Add Now. The first question that really talks about is. How are we going to connect to the SQL Servers in question. Windows authentication is really referring to the account that is running the SQL Diagnostic Manager collection service. So when he did the install it would have asked you to specify an account to run that service as. And in that case it would be that same account. Now if you are working in a non-trusted environment trying to monitor. And then since that may use in a different domain or does not use Windows authentication. You can certainly use SQL Server authentication to do that. And then you have the option to encrypt the connection from SQL Diagnostic Manager collection service to the instances being monitored as well. You may do that if you are going across the way. And to do monitoring. But generally speaking if you are inside a firewall I do not think that this is a primary option that most of our customers use. Now the next page is actually where will identify instances. As you can see here. One of the servers in my environment is already in a list. So it is going to use the browser service to discover any instances in the environment that are obviously discoverable. If you do not see anything in there. It is not always surprising. Sometimes [you know] we just cannot find those SQL Servers very quickly in SQL Diagnostic Manager. In that case you can always type in any instance name that you wish. Now if you notice it says type semicolon separated names. So what I could say it is like SQL one semicolon SQL two semi colon SQL three. And that is just going to push down as individual instances. Right. So if you already have a list of what you want to monitor. As long as that is comma separated you can paste it in there. And it is going to know that pretty quickly. Let me remove these real fast. So we are going to add this step three box. Now the next step is helping us understand what type of SQL Server it is that you are going to monitor. This one happens to be an on-premises virtual machine. Really simple. But if you were doing something like RDS [Amazon Relational Database Service] or as your even Linux for that matter you would want to tell us what that is. Now notice this says RDS [Amazon Relational Database Service]. If you are talking about monitoring a virtual machine. That is out in the cloud. Be that. And Azure Amazon or any other [you know] hosting provider that that you may be leveraging. You do not have to indicate anything other than Windows. Right. So if it is in the cloud. And it is running on a machine that is [you know] your virtual machine with your SQL Server instance on it. We would treat that as if it was on premises. But if you are doing something a little bit different like RDS [Amazon Relational Database Service] or as your database [you know] databases of service versus a virtual machine to run again Microsoft data center. Then you would want to indicate that same with Linux. The reason why is there are different things that we collect in those environments. And there are things that we do not collect right. That we do collect. And we do not collect. All right. So mine for example is not going to work on a Linux environment. So you do want to let us know what type of system it is. And then we will talk about collection intervals. And query monitoring. Now polling intervals. Generally speaking, the default is six minutes. That is what you will see here. What that is talking about is how often we go out. And we get out of the OS [operating system] level. In the SQL level performance data. Most environments I think six minutes. Since in some environments though it does not. Right. So if you have servers that are high visibility servers that are having a lot of issues. And you want more resolution. And to what is happening in those SQL Servers. You may drop that down to [you know] one or two minutes. In other environments that are running perfectly normal. When you are really looking at SQL Diagnostic Manager. More for long term trending analysis. […] planning. General alerting. Maybe fifteen to twenty minutes is appropriate. I think the max number here is thirty minutes. And the lowest number it can be below one minute. But I do not think most of our customers are going to go below a minute. And part of the reason for that is there is other features like query monitoring activity monitoring. That can help fill in those gaps between the polling intervals. For example, […] on during here. And you have some different ways to do this. But the idea of query monitoring is to basically capture. And let SQL Diagnostic Manager know about any poor-performing SQL that is running between the polling intervals. Most of the time that is done to extend advance for Query Store. Depending on the version of SQL that you are monitoring. On older versions of SQL 2000 to 2005 for example. We would be leveraging a server-side trace. But for the new versions of SQL 2008 above. You can use extended events. 2016. And above you can use Query Store. I think those would be a preferable option than the server-side trace. Now you also can define down below what your considerations are for poor performing. Right. So the default is anything over five seconds in duration. You could have parameters around I/O [input. And output] or CPU [central processing unit] that you want to look at as well. And you can go into the advanced functionality here. And include or exclude applications databases or texts that maybe it is irrelevant or exclusively relevant to your […]. The query […]. So again we are monitoring really does help you better understand what you are pulling in a boy is. And the reason why I say that is at six minutes you may not see every query in that six minute pulling in a bowl. But any query over five seconds is being picked up in a different collector the query monitor collector therefore you will have a record of it regardless of when you are pulling it of all fires. So it may. And honestly if I am adding twenty instances maybe I do not go ahead. And enable this out of the box. But again maybe you do. So this is an optional step you do have the ability to turn on cream on during after this manually you can also trigger it by the way to alerting. So there is some flexibility on enabling this. After you have actually added the instance to the server. To diagnose mine or excuse me. So the next thing that we are looking at. And this is for on premises or virtual machines running SQL and Windows. We are going to be collecting OS [operating system] level performance data through […]. And by default at least generally speaking what we do again is leverage the SQL Diagnostic Manager collection service account. To do that with my monitoring. You do not have to do that. So you could put in [you know] domain slash username that you want to use for that with my collection. In some environments though where there is a firewall between the SQL Diagnostic Manager collection service. And the instance that you are going to monitor that is preventing my data coming through the firewall. You could opt for the middle option here which is to leverage only automation. It is a setting within SQL Server. So it is still procedures that can be enabled within SQL Server. That basically allows SQL to access OS [operating system] level data like mine. So we would leverage that. And those firewall consideration environments. So that the data comes. My data comes directly to the SQL Diagnostic Manager collection service through the SQL Server port. So you do not have to have any other ports open other than [you know] 1433 or whatever you run SQL Server on. So that is an option for firewall bound environments. And then not that we would recommend this. But you do not have to collect OS [operating system] level performance data. So if you if you are just not interested [you know] it is level CPU [central processing unit]. And disk. And memory. And then you do not have to turn that on. But the default generally works for most environments. I would go ahead. And try this at least. If it does not work you can always change it on an individual instance by instance basis. And then the last big piece of this Add Wizard is talking about alert templates as well as tags. Now alert templates. The idea here is what is your threshold for what is good and bad per metric. And out of the box there are some different templates that you can work. Critical only performance templates. Things like this that you can work from. You can also create your own. But if you are brand new customer you are probably not going to have templates other than the default. So you can pick and choose which ones you want. So critical only I think is good for your baseline kind of initial proof of concept. And you can always ramp up those [you know] the detail of your metrics later on. But I think critical only is a good starting point for most environments. And then the other important piece of this the screen is talking about tagging. Is basically logical groups. So if you look over here in my environment. There is Austin, development, production, SharePoint. These are different groups of instances that I can leverage when doing things like maintenance mode, alert templates, alert responses, security, reporting. So the tags themselves can simplify management over time. And you can create them. Obviously you will not have them if you are a brand new customer. But you can create as many tags as you wish. And it is also important to understand that these are non-exclusive. So [you know] prod one for example could be in Austin. And in production. And in SharePoint. So it could be a member of three different tags if that is required. So creating tags may not be something you do initially in your initial POC [proof of concept]. But over time it is something that you will want to ultimately incorporate into your monitoring. Because it will simplify management. And then lastly when he had finished it will ask you to test. And generally what you are looking for is a green check mark. Right so it is testing that new instance. It knows what it knows. It has availability to. It was able to do a static initial data collection. When I could apply it is not going to be added to the to the list. So you will see here it says initialize. And it will take a minute or so for it to do its initial get data gathering. So it is looking at [you know] what […] is that [you know]. What version of SQL. How many processors, CPU [central processing unit]. Counting up the database. Is pulling in the general information about the SQL Server. So that when we click on it. We are actually having data to work with. So mainly adding instances to the environment. It is relatively simple. Once it has been added. You can always go in here. And tweak a little bit. So maybe all of our servers or most of our servers. We want to […] at that six-minute polling interval. But this one is a little bit different. We want to drop that down to two. Maybe we have decided that we want to put this in a couple different tags that we did not do initially. You can also go in. And do things like initialize VMware monitoring. So if you are not familiar with that diagnostic. There does have the ability to measure certain VMware or Hyper-V specific metrics coming from in the case of VMware from VCenter. So if you wanted to go that next level. It is pretty simple. You would say New. And what this is talking about is in this case we are talking about VMware where is VCenter. So you put in either the IP [Internet protocol address] or the fully qualified domain to VCenter. You can name it whatever you want. And then you need a read account. So you do not need the administrator. Once that is done it will let you link that. So you will hit Link down below. And you will start getting data back. So it is a relatively simple process. But it gives you that extra bit of information that help you understand performance a little bit more. So manually adding instances is relatively simple through the GUI [graphical user interface]. You also do not have the ability through PowerShell to not only add instances with the PowerShell command New SQL Server […] instance right here. So it is a relatively simple syntax [you know]. What instance you want to monitor. What authentication type. You can put in tags [you know]. There are good examples in here you can work from. So you certainly have the ability to add an instance quickly. But you can also do other things with PowerShell. Right so you can add different drives. If you had to mainly supply mount points. You can add them users. You can grant permissions application security. You can customize your monitoring with […] as well. So you can add instance names. Friendly names tagging. Remove tags. Schedule maintenance mode. There is a lot of very interesting options available to you via PowerShell. So if you are one of those customers that [you know]. Maybe you are monitoring two hundred instances from the get-go. But you keep adding new SQL Servers to your environment every week, every month. Part of your deployment scripting for SQL could include PowerShell command lets for SQL Diagnostic Manager. So that once that instance is automatically created in your enterprise it is automatically added to [Simple Network Management Protocol] at the same time. So I hope this was useful for you guys today. Obviously if you have more questions get in contact with us through your account managers. And thank you for your time.
Topics : Database Diagnostics,Database Monitoring,Database Performance,SQL Query Performance,
Products : SQL Diagnostic Manager for SQL Server,