SqlServer-Baselining-Grafana

If you are a developer, or DBA who manages Microsoft SQL Servers, it becames important to understand current load vs usual load when SQL Server is slow. This repository contains scripts that will help you to setup baseline on individual SQL Server instances, and then visualize the collected data using Grafana through one Inventory server with Linked Server for individual SQL Server instances.

Navigation

• Sample Live Grafana Dashboards
  • Live Dashboard - Basic Metrics
  • Live Dashboard - Perfmon Counters - Quest Softwares
• Portal Credentials
• How to Setup
  • Part 01 - Setup Baselining of SqlServer
  • Part 02 - Configure Grafana for Visualization on baselined data

Live Dashboard - Basic Metrics

You can visit http://ajaydwivedi.ddns.net:3000 for live dashboard for basic real time monitoring.

Live Dashboard - Perfmon Counters - Quest Softwares

Visit http://ajaydwivedi.ddns.net:3000 for live dashboard of all Perfmon counters suggested in SQL Server Perfmon Counters of Interest - Quest Software.

Portal Credentials

Database/Grafana Portal	User Name	Password
http://ajaydwivedi.ddns.net:3000/	guest	ajaydwivedi-guest
Sql Instance -> ajaydwivedi.ddns.net:1433	grafana	grafana

How to Setup

Setup of baselining & visualization is divided into 2 parts:-

• Part 01 - Setup Baselining of SqlServer
• Part 02 - Configure Grafana for Visualization on baselined data

Part 01 - Setup Baselining of SqlServer

1. Create [DBA] database using below script. Make sure to change the data and log files path in above script before execution. Ideally, you want to put each filegroup file in separate disk.

   • DDLs/SCH-DBA-database.sql
     

2. Download/Copy below files from path NonSql-Files to local directory where perfmon data collector files will be generated. Say, E:\Perfmon\ on SQL Server box. This directory should have at least 4 gb of size.
   

   • DBA_PerfMon_NonSQL_Collector_Template.xml
   • perfmon-collector-logman.ps1
   • perfmon-collector-push-to-sqlserver.ps1
   • perfmon-remove-imported-files.ps1

3. Create required objects in sequential order of scripts as mentioned below:-

   1. DDLs/SCH-tables-views.sql
   2. DDLs/SCH-create-functions.sql
   3. DDLs/SCH-usp_collect_performance_metrics.sql

4. Create WhoIsActive capturing using below script. Avoid running commented code that creates agent job.

   • DDLs/SCH-Job-[(dba) Collect Metrics - WhoIsActive].sql

5. Prepare perfmon data collection:-
   

   1. Setup Perfmon data collector using downloaded script perfmon-collector-logman.ps1. Make sure to open script and change value for variable $collector_root_directory as per Step 2). Save it.

   # Original line in script
   $collector_root_directory = 'D:\MSSQL15.MSSQLSERVER\MSSQL\Perfmon';
   # Update line as per need
   $collector_root_directory = 'E:\Perfmon';

   2. Create ODBC Data Source for SqlInstance. This should be done only once for each Windows Server box. In case of multiple SQL Server instances, choose one instance as ODBC destination.

   # create dsn for Sql Server instance 'localhost' with windows authentication and default to [DBA] database
   Add-OdbcDsn -Name "LocalSqlServer" -DriverName "SQL Server" -DsnType "System" -SetPropertyValue @("Server=localhost", "Trusted_Connection=Yes", "Database=DBA")
   

   3. Push Perform data collector data to SqlServer using relog & dsn.

   • NonSql-Files/perfmon-collector-push-to-sqlserver.ps1

6. Setup Default Mail profile

   1. Make sure a public profile is set as 'Default profile' in Database Mail using Profile Security.
      
      
      
   2. Make sure Mail profile is set for SQL Server Agent under Mail session.
      
      
      

7. Create database login/user [grafana] for executing queries from Grafana portal. This user needs [db_datareader] on [DBA], and VIEW SERVER STATE permissions.

   • Permissions/SCH-grafana-login.sql

8. IMPORTANT: In case of multiple SqlInstances installed on same server, Perfmon collector data should be imported into one instance only.
   

   For example, on SQL Server box 'SQL-A.Lab.com', I have one named instance 'SQL-A\V17' installed along side with default instance 'SQL-A'. So I import the Perfmon collector data into default instance 'SQL-A', and read same data on named instance using views created with help of Linked server of default instance.

   
   

   Execute below script on NAMED instance to create these views required to read data:-

   • DDLs/SCH-tables-views-Named-Instance.sql

   NOTE:- Make sure to create linked server for main instance in named instance, and replace the linked server name in above script code before executing.

9. Execute below script to Create Sql Agent jobs that Collect/Purge performance data. Jobs have default schedule of every 10 seconds.

   • DDLs/SCH-Jobs-All-[(dba) Collect Metrics].sql

   This will create sql agent jobs with name like '(dba) Collect Metrics - ******'
   

   Make sure jobs [(dba) Collect Metrics - NonSqlServer Perfmon Counters] & [(dba) Collect Metrics - Purge Perfmon Files] execute successfully. These jobs may require script path change to point to directory set in step 2, & 5.a.
   

   This completes part 01 of setting up baselining for SQL Server

Part 02 - Configure Grafana for Visualization on baselined data

For Grafana, I am using one SqlInstance as my Inventory (central) server. What this mean is, on this server, I'll create linked servers for all the server that required monitoring using Grafana.

1. Setup Inventory server. Select one server as Inventory. Create linked Server for each instance that require monitoring through Grafana on your central server using 'Microsoft OLEDB Provider for SQL Server'. Here make sure to utilize readonly sql authenticated login [grafana] we created in part 01 step 7.

   
   

2. On grafana portal, create data source named 'Inventory' with details of above step, and [grafana] login.

   
   

3. Finally, Create dashboards by importing below *.json files

   • NonSql-Files/Grafana - Monitoring - Live - External Share.json
   • NonSql-Files/Grafana - Monitoring - Perfmon Counters - Quest Softwares - External Share.json

   This should create the grafana dashboard according to settings of above json files.

   Thanks 😃. Subscribe for updates 👍