Differences

This shows you the differences between two versions of the page.

--- windows:windowsstart [2013/10/15 19:55] – [Gather SQL Server instances info] correct updateinstance PowerShell script is linked haidong
+++ windows:windowsstart [2018/05/15 03:00] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-===== Gathering Windows and SQL Server metrics data =====
+====== Windows and SQL Server metrics data capturing ======
 Here are step by step instructions on setting up a process to collect Windows and SQL Server related metrics data. Most of the automation scripts are written in PowerShell. To be honest, given a choice, I'd rather write my tools in either Python or Perl. However, on Windows systems, those languages require a separate install, whereas PowerShell is present already. That, plus the tight integration between PowerShell and Windows (and other Microsoft server products), makes starting with PowerShell pretty compelling. I may present the same tools written in Python, Perl, and/or other languages if/when I develop them.
-The whole process is pretty lightweight, and I suggest you start doing this as soon as possible. Over time, you will find the information gathered very valuable and you'd be happy with the fact that you started the collection sooner rather than later.
+Setting this up is pretty easy. Here is a quick overview before we delve into the details:
+  * We will need to create a SQL Server database, JiMetrics, to store metrics data;
+  * The collection process relies on SQL Server Agent;
+  * The domain account SQL Server Agent jobs run under must have:
+    * Administrative access to all server hosts you intend to collect metrics data from
+    * sysadmin access to all SQL Server instances you intend to collect metrics data from
+    * dbo access to the JiMetrics database.
+  * Manual steps needed are minimal. If you create steps in the order that is presented in this page, you will be fine. Each PowerShell script corresponds to one SQL Server Agent job. Inside the PowerShell script, it knows which stored procedure to call, which Hosts are involved, what metrics to gather, and which table to put that metrics into.
+  * Once you've started collecting for a while, you will certainly be able to get a lot out of this information. There are very few tables involved so the database is pretty easy to query against. I suggest you follow the [[db:sqlserver:jimetrics|database diagram]] for help.
-==== Set up a database ====
+The whole process is pretty lightweight, and I suggest you start doing this as soon as possible. Over time, you will find the information gathered very valuable and you'd be happy with the fact that you start the collection sooner rather than later.
-Please [[db:sqlserver:sysmetrics|follow this link]] to create the SysMetrics database.
-==== Fill server Host table ====
+===== Set up a database =====
-Enter Windows servers whose metrics you are interested in gathering. You can do this manually by running SQL statements like this:
+Please [[db:sqlserver:jimetrics|follow this link]] to create the JiMetrics database for metrics data storage. Come back to this page and continue the steps below when the database is ready;
+===== Fill server Host table =====
+Enter Windows server hosts whose metrics you are interested in gathering. You can do this manually by running SQL statements like this:
 <code tsql>
-USE SysMetrics;
+USE JiMetrics;
-GO;
+GO
 INSERT INTO Windows.Host (HostName) VALUES ('WindowsHost1');
-GO;
+GO
 </code>
-Or, if you have a list of servers in a file, it is not hard to import that into the Host table.
+Or, if you have a list of servers in a file, it is easy to import that into the Host table.
-==== Gather Windows server info ====
+===== Setup SQL Server Agent proxy account =====
-Please [[windows:powershell:updatehost|follow this link]] to create a SQL Server job for updating server host info. These include things like domain, OS Version Number, hardware model, hardware vendor, memory size in GB, CPU type, CPU core count. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get.
+Use the domain account mentioned above to create a SQL Server credential, use that credential to create a SQL Server Agent PowerShell proxy account afterwards. Please use this proxy account to run ALL SQL Server Agent jobs' steps that you will create below.
-==== Gather Windows server disk info ====
+===== Gather Windows server and storage info =====
-Please [[windows:powershell:insertstorage|follow this link]] to create a SQL Server job for gathering server host disk information. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get.
+==== Windows server info ====
+Please [[windows:powershell:updatehost|follow this link]] to create a SQL Server job for updating server host info. These include things like domain, OS Version Number, hardware model, hardware vendor, memory size in GB, CPU type, CPU core count, if the host is a VM, etc. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get.
+Manually run the job after it is created. It should succeed if the proxy account has proper access defined above. Run the T-SQL code below to check the results:
+<code tsql>
+USE JiMetrics;
+GO
+SELECT * FROM Windows.Host;
+GO
+</code>
+==== Windows server disk info ====
+Please [[windows:powershell:insertstorage|follow this link]] to create a SQL Server job for gathering server host disk information. Manually run the job after its creation. Run the T-SQL code below to check the results:
+<code tsql>
+USE JiMetrics;
+GO
+SELECT * FROM Windows.Storage;
+GO
+</code>
+===== Gather SQL Server instances info =====
 ==== Discover installed SQL Server instances ====
-Please [[windows:powershell:insertinstance|follow this link]] to create a SQL Server job for discovering installed SQL Server instance(s) on server hosts in the Host table. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get.
+Please [[windows:powershell:insertinstance|follow this link]] to create a SQL Server job for discovering installed SQL Server instance(s) on server hosts in the Host table. Manually run the job after its creation. Run the T-SQL code below to check the results:
+<code tsql>
+USE JiMetrics;
+GO
+SELECT * FROM Windows.Instance;
+GO
+</code>
-==== Gather SQL Server instances info ====
+==== SQL Server instance version, edition, and service running status ====
-After SQL Server instances are discovered in server hosts, it is now time to get some metadata about those instances. This step gathers information like instance edition, version number, service pack level, and whether or not it is running. Please [[windows:powershell:updateinstance|follow this link]] to create a SQL Server job. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get.
+After SQL Server instances are discovered in all active servers in the Windows.Host table, it is now time to find out if the instances are running and their edition level and versions. Please [[windows:powershell:updateinstance|follow this link]] to create a SQL Server job. Manually run the job after its creation. Run the T-SQL code below to check the results:
+<code tsql>
+USE JiMetrics;
+GO
+SELECT * FROM Windows.Instance;
+GO
+</code>
-==== Gather SQL Server database file info ====
+==== SQL Server instance configuration info ====
-After server hosts, hosts' disk information, and SQL Server instance information have been gathered, we drill down one more level to metrics about database files. These include database name, its associated files, file size, free size, max size information, and growth property (percentage growth or not). Please [[windows:powershell:insertdbfilestats|follow this link]].
+Now that we have names of all currently running SQL Server instances, it is time to gather their configuration information, such as its min and max memory usage boundaries, instance level parallelism, CLR usage, et cetera. Please [[windows:powershell:insertinstanceconfig|follow this link]] to create a SQL Server job to gather that data. Manually run the job after its creation. Run the T-SQL code below to check the results:
+<code tsql>
+USE JiMetrics;
+GO
+SELECT * FROM Windows.InstanceConfig;
+GO
+</code>
-==== Gather SQL Server database table info ====
+==== SQL Server instance performance counters ====
-We drill down one more level to metrics about database tables. These include database name, schema name, table name, total row count, data size, and index size. Please [[windows:powershell:inserttablestats|follow this link]].
+To get a good handle on the performance characteristics and metrics, it is important to gather and store performance counters. Please [[windows:powershell:insertinstancedmvperfcounter|follow this link]] to create a SQL Server job to gather that data. Manually run the job after its creation. Run the T-SQL code below to check the results:
+<code tsql>
+USE JiMetrics;
+GO
+SELECT * FROM Windows.InstanceDmvPerfCounter;
+GO
+</code>
+==== SQL Server database file info ====
+After server hosts, hosts' disk information, and SQL Server instance information have been gathered, we drill down one more level to database files. These include database name, its associated files, file size, free size, max size information, and growth property (percentage growth or not). Please [[windows:powershell:insertdbfilestats|follow this link]]. Manually run the job after its creation. Run the T-SQL code below to check the results:
+<code tsql>
+USE JiMetrics;
+GO
+SELECT * FROM Windows.DbFileStats;
+GO
+</code>
+==== SQL Server database table info ====
+We drill down one more level to metrics about database tables. These include database name, schema name, table name, total row count, data size, and index size. Please [[windows:powershell:inserttablestats|follow this link]]. Manually run the job after its creation. Run the T-SQL code below to check the results:
+<code tsql>
+USE JiMetrics;
+GO
+SELECT * FROM Windows.TableStats;
+GO
+</code>