Vision: SQL Homeostasis

This is a simple powershell script to query and display hardware and OS information from a remote computer. It uses CIM (Common Information Model) that is available since Powershell version 3 and is the recommended direction.  Please see the following article on why "we" should use CIM instead of the WMI. https://devblogs.microsoft.com/scripting/should-i-use-cim-or-wmi-with-windows-powershell/ # Specify the server name here $server = "server1" # pull all the information $hardware = Get-CimInstance -ClassName Win32_ComputerSystem -ComputerName $server $OS = Get-CimInstance -ClassName Win32_OperatingSystem -ComputerName $server $CPU = Get-CimInstance -ClassName Win32_Processor -ComputerName $server $PhysicalMemory = Get-CimInstance -ClassName CIM_PhysicalMemory -ComputerName $server $Bios = Get-CimInstance -ClassName Win32_BIOS -ComputerName $server $total_memory = ( $PhysicalMemory | measure-object...

You may have situations where the CPU % usage is well below the alert threshold but still queries are running slow because they are waiting for CPU to be available. This script creates an alert to send out email if the number of queries waiting for CPU exceeds the threshold.  Please update the value for the  @ operator  variable to whatever is the email operator you have setup in the SQL Server Agent. And since I am testing I am using the threshold value of 10. You may want to lower that after testing in your environment. Lastly, Since I did not want to get bombarded with emails, I am using the 900 seconds (15 minutes) delay of between alert emails. Please feel free to adjust it to your needs. USE [msdb] GO declare @ operator varchar ( 500 ) -- email operator name declare @ threshold int -- number of queries waiting for the CPU declare @ delay_between_email_alerts int -- this value is in seconds declare @ drop_alert_if_exists bit ...

Most of the times I use the CSV files whenever I need to import or export SQL Server data. And then if I need to do further analysis on the data or simply beautify the results to share with the users or colleagues, I simply open the CSV file in Excel and do the rest of the work manually. But what if I could just skip the CSV and export data directly to Excel format? Wouldn't that save me time and efforts and also help me to automate if I wanted to? No surprise that there is indeed a powershell module for Excel at the Powershell Gallary site. https://www.powershellgallery.com/packages/ImportExcel/5.2.0 You can import the module directly from there or do the manual download. I decided to use the import method. For that I would need to have the PSGallary as one of the registered repositories in the PowerShell If you don't already have registered the Powershell Gallary as one of the repository, there are couple methods depending on the PowerShell version you have. I have the 5.x ve...

If you are new at your job or a new client you would like to gather and review the sql server meta data to understand the environment and get up to speed as quickly as possible. Most of us are already familiar with the DMVs, system functions, procedures etc. to gather the SQL server metadata. And if you want to gather the information for all your SQL servers, you could run a multi-server query against all through the Central Management Server. In fact, in newer versions of SSMS you don't even need the CMS, you just register all your sql instances in the Local Server Groups. So from that perspective this post is not adding much values except maybe that it is another alternative to SSIS or other ETL tools to capture the meta data on regular basis. If nothing else I hope you find this educational regarding how to use powershell to interact with sql servers. <# Export SQL Server properties, settings and configuration values to CSV files #> # name of the sql server instance you wo...

At one of my clients I received an email from one of the IT Project Managers asking a simple question: "Can you please let us know which databases reside on the server below, Server1?" First thought in mind, well from what particular sql instance on that server? At that point I was not even sure if that server has multiple instance, is it a stand alone sever or a node/virtual name of a cluster server, alwayson cluster etc... But I kept that thought to myself. Now, I could launch SSMS, connect to the sql instance, query the sys.databases and get requested information. But I don't know the instance name top of my head. So I would need to RDP into the server or look up the meta data somewhere. Instead of that, I decide to launch the Powershell and issue this command: Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "Server1" It has only once sql instnace, great. Then I issued the following c...