A simple powershell script to look up server hardware and OS information

# 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

# build custom array to get some key properties in a single row
$server_summary = New-Object PSObject

Add-Member -inputObject $server_summary -memberType NoteProperty -Name Manufacturer -value $hardware.Manufacturer
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Model -value $hardware.Model
Add-Member -inputObject $server_summary -memberType NoteProperty -Name HypervisorPresent -value $hardware.HypervisorPresent
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Bios -value $Bios.Name
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OS -value $OS.Caption
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSArchitecture -value $OS.OSArchitecture
Add-Member -inputObject $server_summary -memberType NoteProperty -Name CPUs -value $CPU.count
Add-Member -inputObject $server_summary -memberType NoteProperty -Name PhySicalMemory_GB -value $total_memory_gb
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSVersionNumber -value $OS.Version
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMajorVersion -value $OS.ServicePackMajorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMinor -value $OS.ServicePackMinorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name LastBootUpTime -value $OS.LastBootUpTime

# Display the data on the screen

# But first, lets up the buffer size so we can see the complete output on the screen
$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (500, 3000)

"summary"
"======="

$server_summary | ft -AutoSize

""
"Detailed Properties"
"==================="

"Hardware:"
$hardware       | ft -Property *

"Bios:"
$Bios           | ft -Property * 

"Operating System:"
$OS             | ft -Property *

"CPUs:" 
$CPU            | ft -Property * 

"Physical Memory:"
$PhysicalMemory | ft -property *

A simple powershell script to look up server hardware and OS information

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 -Property Capacity -sum).sum
$total_memory_gb = $total_memory / 1024 / 1024 / 1024

# build custom array to get some key properties in a single row
$server_summary = New-Object PSObject

Add-Member -inputObject $server_summary -memberType NoteProperty -Name Manufacturer -value $hardware.Manufacturer
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Model -value $hardware.Model
Add-Member -inputObject $server_summary -memberType NoteProperty -Name HypervisorPresent -value $hardware.HypervisorPresent
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Bios -value $Bios.Name
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OS -value $OS.Caption
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSArchitecture -value $OS.OSArchitecture
Add-Member -inputObject $server_summary -memberType NoteProperty -Name CPUs -value $CPU.count
Add-Member -inputObject $server_summary -memberType NoteProperty -Name PhySicalMemory_GB -value $total_memory_gb
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSVersionNumber -value $OS.Version
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMajorVersion -value $OS.ServicePackMajorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMinor -value $OS.ServicePackMinorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name LastBootUpTime -value $OS.LastBootUpTime

# Display the values

# First, lets up the buffer size first so we can see the complete output on the screen
$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (500, 3000)

"summary"
"======="

$server_summary | ft -AutoSize

""
"Detailed Properties"
"==================="

"Hardware:"
$hardware       | ft -Property *

"Bios:"
$Bios           | ft -Property * 

"Operating System:"
$OS             | ft -Property *

"CPUs:" 
$CPU            | ft -Property * 

"Physical Memory:"
$PhysicalMemory | ft -property *

---

Caveat:

That worked on most servers but on some I ran into an error with the CIM.

So I tried the solution suggested by the error message, which is to run the winrm quickconfig on the remote computer.  That threw message that the "WinRM service is already running on this machine", so maybe there is a firewall that is blocking it.

So I decided to go back to the good old faithful WMI for those servers. The powershell methods are still interchangeable between CIM and WMI so all it took was to do a global search/ replace for  Get-CimInstance / Get-WmiObject.

# Specify the server name here

$server         = "server1"


# pull all the information
$hardware         = Get-WmiObject -ClassName Win32_ComputerSystem -ComputerName $server
$OS               = Get-WmiObject -ClassName Win32_OperatingSystem -ComputerName $server
$CPU              = Get-WmiObject -ClassName Win32_Processor -ComputerName $server
$PhysicalMemory   = Get-WmiObject -ClassName CIM_PhysicalMemory -ComputerName $server
$Bios             = Get-WmiObject -ClassName Win32_BIOS -ComputerName $server

$total_memory = ($PhysicalMemory | measure-object -Property Capacity -sum).sum
$total_memory_gb = $total_memory / 1024 / 1024 / 1024

# build custom array to get some key properties in a single row
$server_summary = New-Object PSObject

Add-Member -inputObject $server_summary -memberType NoteProperty -Name Manufacturer -value $hardware.Manufacturer
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Model -value $hardware.Model
Add-Member -inputObject $server_summary -memberType NoteProperty -Name HypervisorPresent -value $hardware.HypervisorPresent
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Bios -value $Bios.Name
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OS -value $OS.Caption
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSArchitecture -value $OS.OSArchitecture
Add-Member -inputObject $server_summary -memberType NoteProperty -Name CPUs -value $CPU.count
Add-Member -inputObject $server_summary -memberType NoteProperty -Name PhySicalMemory_GB -value $total_memory_gb
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSVersionNumber -value $OS.Version
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMajorVersion -value $OS.ServicePackMajorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMinor -value $OS.ServicePackMinorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name LastBootUpTime -value $OS.LastBootUpTime

# Display the values

# First, lets up the buffer size first so we can see the complete output on the screen
$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (500, 3000)

"summary"
"======="

$server_summary | ft -AutoSize

""
"Detailed Properties"
"==================="

"Hardware:"
$hardware       | ft -Property *

"Bios:"
$Bios           | ft -Property * 

"Operating System:"
$OS             | ft -Property *

"CPUs:" 
$CPU            | ft -Property * 

"Physical Memory:"
$PhysicalMemory | ft -property *

Simple enough?  Nah... but I am sticking with the title!

SQL Script to Create, Test and Verify Event Notifications

Event notifications are kinda like a trigger in the sense that they respond to specific event, specifically in response to DDL statements and SQL Trace events.

The major difference between the triggers and event notifications is that the triggers are fired in synchronous mode to execute a specific code within the same session and transaction. Whereas, the event notification do not execute any code, it only sends information in asynchronous mode which then can be logged and acted upon later on.


Here is a snippet from the Microsoft Documentation that explains the differences:

Event Notifications vs. Triggers

The following table compares and contrasts triggers and event notifications.

Triggers	Event Notifications
DML triggers respond to data manipulation language (DML) events. DDL triggers respond to data definition language (DDL) events.	Event notifications respond to DDL events and a subset of SQL trace events.
Triggers can run Transact-SQL or common language runtime (CLR) managed code.	Event notifications do not run code. Instead, they send xml messages to a Service Broker service.
Triggers are processed synchronously, within the scope of the transactions that cause them to fire.	Event notifications may be processed asynchronously and do not run in the scope of the transactions that cause them to fire.
The consumer of a trigger is tightly coupled with the event that causes it to fire.	The consumer of an event notification is decoupled from the event that causes it to fire.
Triggers must be processed on the local server.	Event notifications can be processed on a remote server.
Triggers can be rolled back.	Event notifications cannot be rolled back.
DML trigger names are schema-scoped. DDL trigger names are database-scoped or server-scoped.	Event notification names are scoped by the server or database. Event notifications on a QUEUE_ACTIVATION event are scoped to a specific queue.
DML triggers are owned by the same owner as the tables on which they are applied.	The owner of an event notification on a queue may have a different owner than the object on which it is applied.
Triggers support the EXECUTE AS clause.	Event notifications do not support the EXECUTE AS clause.
DDL trigger event information can be captured using the EVENTDATA function, which returns an xml data type.	Event notifications send xml event information to a Service Broker service. The information is formatted to the same schema as that of the EVENTDATA function.
Metadata about triggers is found in the sys.triggers and sys.server_triggerscatalog views.	Metadata about event notifications is found in the sys.event_notifications and sys.server_event_notifications catalog views.

---

In the following sample SQL script, I am creating an Extended Event to capture  ALTER TABLE events in a database.

-- Create a brand new database for the testing purpose
use master
go
if db_id('temp_event_notification_test_929368242990-321836') is not null
  drop database [temp_event_notification_test_929368242990-321836]
go
CREATE DATABASE [temp_event_notification_test_929368242990-321836]
GO


-- Enable the service broker if its not already
if not exists (select * from sys.databases where name = '[temp_event_notification_test_929368242990-321836]' and is_broker_enabled = 1)
ALTER DATABASE [temp_event_notification_test_929368242990-321836] SET ENABLE_BROKER; 
go

-- set the trustworth property ON
if not exists (select * from sys.databases where name = '[temp_event_notification_test_929368242990-321836]' and is_trustworthy_on = 1)
ALTER DATABASE [temp_event_notification_test_929368242990-321836] SET TRUSTWORTHY ON;
go

-- check if there is already a service broker end point running
if not exists (select * from sys.service_broker_endpoints where type_desc = 'SERVICE_BROKER' and state_desc = 'STARTED' )
BEGIN
    -- check if there is a SB endpoint with same name
    if not exists (select * from sys.service_broker_endpoints where NAME = 'en_service_broker_929368242990-321836')
 BEGIN
    -- check to make sure the tcp port is not already in use
    if not exists (SELECT * FROM SYS.tcp_endpoints where port = 5122)
   CREATE ENDPOINT [en_service_broker_929368242990-321836]
   STATE = STARTED
   AS TCP (  LISTENER_PORT = 5122)
   FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS  );
    else
   raiserror('Error: An end point cannot be created. Please check if there is already one with same port.', 16,1)
 END
 else
    raiserror('Error: An end point cannot be created. Please check if there is already one with same name.', 16,1)
END
GO

USE [temp_event_notification_test_929368242990-321836]

go
CREATE QUEUE [ent_929368242990-321836] ;  
GO  
CREATE SERVICE [ens_929368242990-321836]  
ON QUEUE [ent_929368242990-321836]  
(  
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]  
);  
GO  

CREATE ROUTE [enr_929368242990-321836]  
WITH SERVICE_NAME = 'ens_929368242990-321836',  
ADDRESS = 'LOCAL';  
GO  

CREATE EVENT NOTIFICATION [enen_929368242990-321836]  
ON DATABASE  
FOR ALTER_TABLE  
TO SERVICE 'ens_929368242990-321836',  'current database'

-- Test
-- Trigger the event notification
if object_id('entt_929368242990-321836') is not null
drop table [entt_929368242990-321836]
go
create table [entt_929368242990-321836] (i int)
go
alter table [entt_929368242990-321836] add b int
go

-- verify that the event notification was captured

SELECT TOP (1000) *, casted_message_body = 
CASE message_type_name WHEN 'X' 
  THEN CAST(message_body AS NVARCHAR(MAX)) 
  ELSE message_body 
END 
FROM [temp_event_notification_test_929368242990-321836].[dbo].[ent_929368242990-321836] WITH(NOLOCK)

if @@ROWCOUNT = 0
   RAISERROR('Error: Something is not right. Event notification was not captured.', 16,1)
else 
   SELECT 'Success!' Msg
go

-- clear the queue
RECEIVE * FROM [ent_929368242990-321836]
go

SELECT TOP (1000) *, casted_message_body = 
CASE message_type_name WHEN 'X' 
  THEN CAST(message_body AS NVARCHAR(MAX)) 
  ELSE message_body 
END 
FROM [temp_event_notification_test_929368242990-321836].[dbo].[ent_929368242990-321836] WITH(NOLOCK)


/* CLEAN UP
USE [temp_event_notification_test_929368242990-321836]
go
if exists (SELECT * FROM sys.event_notifications where name = '[enen_929368242990-321836]' and parent_class_desc = 'DATABASE')
DROP EVENT NOTIFICATION [enen_929368242990-321836]  ON DATABASE;  
go

if exists (select  * from sys.routes where name = '[enr_929368242990-321836]' and address = 'LOCAL')
DROP ROUTE [enr_929368242990-321836] 

if exists (SELECT * FROM sys.services where name = 'ens_929368242990-321836')
DROP SERVICE [ens_929368242990-321836]
GO
if exists (SELECT * FROM sys.service_queues where name = 'ent_929368242990-321836' and schema_id = 1)
DROP QUEUE [dbo].[ent_929368242990-321836]
GO
use master
go
if db_id('temp_event_notification_test_929368242990-321836') is not null
  drop database [temp_event_notification_test_929368242990-321836]
go

*/

blah blah....

Get email alert when number of queries waiting for CPU exceeds thresold

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          -- drops and recreates the alert if already exists

--  Assign default values to variables
set @operator                   = 'DBA'
set @threshold                  = 10
set @delay_between_email_alerts = 900
set @drop_alert_if_exists       = 0


declare @sql_add_alert nvarchar(4000)
declare @sql_add_notification nvarchar(4000)
declare @sql_drop_alert_if_exists nvarchar(4000)

if @drop_alert_if_exists = 1
begin
  if exists (select * from msdb..sysalerts where name = 'Alert: Number of processes waiting for CPU exceeded thresold')
    EXEC msdb.dbo.sp_delete_alert @name=N'Alert: Number of processes waiting for CPU exceeded thresold'
end

set @sql_add_alert =
'EXEC msdb.dbo.sp_add_alert @name=N''Alert: Number of processes waiting for CPU exceeded thresold'', 
  @message_id=0, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=' + cast(@delay_between_email_alerts as nvarchar(10)) + ', 
  @include_event_description_in=1, 
  @category_name=N''[Uncategorized]'', 
  @performance_condition=N''Wait Statistics|Wait for the worker|Waits in progress|>|' + cast(@threshold as nvarchar(10)) + ''', 
  @job_id=N''00000000-0000-0000-0000-000000000000''
'
print @sql_add_alert 
exec(@sql_add_alert)
set @sql_add_notification = 
'EXEC msdb.dbo.sp_add_notification 
                @alert_name=N''Alert: Number of processes waiting for CPU exceeded thresold'', 
                @operator_name=N''' + @operator + ''', 
                @notification_method = 1
'
print @sql_add_notification
exec(@sql_add_notification)
go

Powershell one liner to export data directly from SQL Server to Excel

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 version and used the following command to register it.

Register-PSRepository -Default

If that does not work then try the full command:

Register-PSRepository -Name "myNuGetSource" -SourceLocation "https://www.myget.org/F/powershellgetdemo/api/v2" -PublishLocation "https://www.myget.org/F/powershellgetdemo/api/v2/Packages" -InstallationPolicy Trusted

Next I checked and it was now registered as an Un-Trusted source. Either I would need to add the -Force parameter to the Import-Module command or it will prompt me to confirm if I trust the source.

I decided to update the InstallationPolicy for it by issuing the command Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted to update that setting.

So far so good, great.

I copied the powershell command provided at the Powershell Gallary site  to import the module.

Right off the bat I get the following error message.

Now I could relaunch the powershell as Administrator but since I am only testing/playing around, I decided to take up the second suggestion in the error message and add the -Scope Currentuser to the import-module command.

Install-Module -Name ImportExcel -RequiredVersion 5.2.0 -Scope CurrentUser

Success!

And finally, the one liner to export the query output to Excel.

Send-SQLDataToExcel -Connection "Server=SQLServer1\SQL2016AG01;Trusted_Connection=True;" -MsSQLserver -SQL "select @@servername SQLServer, name DBName from sys.databases" -Path sc.xlsx

At the minimum, you need to specify the Connection string, connection type (MsSQLServer), SQL query and the Excel file name.

Here is another example of same with some additional parameters.

Send-SQLDataToExcel -Connection "Server=SQLServer1\SQL2016AG01;Trusted_Connection=True;" -MsSQLserver -SQL "select @@servername SQLServer, name DBName from sys.databases" -AutoSize -BoldTopRow -FreezeTopRow -TableName Test -TableStyle "Light13" -WorkSheetname Test -Path sc.xlsx

If you would like to explore all the parameters, options etc. of Send-SQLDataToExcel look up the complete help file in the powershell:

get-help Send-SQLDataToExcel

Partial Screen Shot:

And if you would like to explore all the things you can do with the ImportExcel module:

Get-Command -Module ImportExcel

OR

(Get-Module ImportExcel).ExportedCommands

I must admit that this is a two or three pages blog for something I claim to be a one-liner. But we need Excel module in order for the one liner to work. I did not have it in my PowerShell so I also included the steps I followed to get it.

SQL Server Metadata using Powershell

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 would like to gather information about
$instanceName = "SQLServer1\SQL2016AG01"

# the folder where the csv files will be stored, by default it will be the current folder
$folder_path = ""

if ($folder_path -eq "" -or $folder_path -eq $null)
{
        $folder_path = (Get-Item -Path ".\").FullName
}

"Folder Path: " + $folder_path

# variables to hold the names of the csv files where information will be stored

$export_file_name_info     = $folder_path + "\" + ($instanceName -replace '\\', '_') + '_info.csv'
$export_file_name_settings = $folder_path + "\" + ($instanceName -replace '\\', '_') + '_settings.csv'
$export_file_name_config   = $folder_path + "\" +  ($instanceName -replace '\\', '_') + '_config.csv'


# Load the sql server assembly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# connect to the sql instance and gather some basic info
# Notice that I have not specified SQL credentials as I would like to use the current user's windows credentials
$instance_object = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$host_name       = $instance_object.NetName
$host_ip         = ([System.Net.Dns]::GetHostAddresses("$host_name") | ? {$_}).IPAddressToString
$db_count=$instance_object.Databases.count

"Host Name: "  + $host_name
"IP Address: " + $host_ip
"Databases: "  + $db_count

# Load up all the information into arrays
$sql_instance_info     = $instance_object.Information.Properties |Select Name, Value 
$sql_instance_settings = $instance_object.Settings.Properties |Select Name, Value 
$sql_instance_config   = $instance_object.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue

$sql_instance_info
$sql_instance_settings
$sql_instance_config

<#
Here I can simply export these arrays to respective CSV files but then it will be in the tabular format.
But I want the information to be displayed in wide (transposed) format.
That way if I want to gather information on multiple servers and stored in the same files, I can do that.

It takes little bit of additional coding but I think the results are worth it.
#>



# SQL Server properties

$info = New-Object -TypeName PSObject

$info | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$info | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$info | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count

foreach ($info_value in $sql_instance_info) 
{


    $Name  = $info_value.Name
    $value = $info_value.Value
    $info  | Add-Member -MemberType NoteProperty -Name $Name -Value $value
}


# SQL Server settings

$settings = New-Object -TypeName PSObject

$settings | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$settings | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$settings | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count


foreach ($settings_value in $sql_instance_settings) 
{
   $Name     = $settings_value.Name
   $value    = $settings_value.Value
   $settings | Add-Member -MemberType NoteProperty -Name $Name -Value $value
}


# SQL Server configuration (sp_configure)
# we need two custom objects here to store the configured as well as run values from sp_configure
$config_value   = New-Object -TypeName PSObject
$run_value      = New-Object -TypeName PSObject


$config_value | Add-Member -MemberType NoteProperty -Name ValueType -Value "Config Value"
$run_value    | Add-Member -MemberType NoteProperty -Name ValueType -Value "Run value"

$config_value | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$config_value | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$config_value | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count

$run_value    | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$run_value    | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$run_value    | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count


foreach ($c in $sql_instance_config) 
{
   $DisplayName  = $c.DisplayName
   $Description  = $c.Description
   $RunValue     = $c.RunValue
   $ConfigValue  = $c.ConfigValue
   $config_value | Add-Member -MemberType NoteProperty -Name $DisplayName -Value $ConfigValue
   $run_value    | Add-Member -MemberType NoteProperty -Name $DisplayName -Value $RunValue
   
   
   
}

# combine the config and run values in a single array
$config = @($config_value,$run_value)


# export the arrays into respective CSV files

$info     | Export-Csv -Path $export_file_name_info -NoTypeInformation
$settings | Export-Csv -Path $export_file_name_settings -NoTypeInformation
$config   | Export-Csv -Path $export_file_name_config -NoTypeInformation

Powershell script to get list of databases on a server

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 command to grab the databases on that list and send him the results.

Get-SqlDatabase -ServerInstance Server1

That last command requires the SqlServer module loaded, which I already have in my powershell startup.

Import-Module -Name SqlServer

But to add bit more value to this blog, I decided to turn this into a small powershell script.

PS variable $server_name is where you specify the server  name where you would like to look up database names. If that value is not specified, it will use the local computer name.

try
{

        Import-Module -Name SqlServer

        $server_name = "Server1"


        # if $server_name string is empty or null then use the current computer name
        if ($server_name -eq "" -or $server_name -eq $null)
        {
                $server_name = $env:computername
        }

        "Looking up SQL Services on: $server_name"
        $sql_services = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "$server_name" -ErrorAction Stop
        

        if ($sql_services.PSComputerName -ne $null)
        {

                # Display sql services on the screen

                $sql_services  |  select-object -Property PSComputerName, @{n="sql_instance";e={$_.Name -replace "MSSQL\$", ""}}, Name, ProcessID, StartMode, State, Status, ExitCode, PathName | ft -AutoSize


                foreach ($sql_service in $sql_services) 
                {

                        ""
                        $instnace_name = $sql_service.Name -replace "MSSQL\$", ""
                        "Instnace Name: " + $instnace_name

                        if ($instnace_name -eq "MSSQLSERVER") 
                
                        {
                                $sql_connection = $sql_service.PSComputerName
                        }

                        else 
                        {
                                $sql_connection = $sql_service.PSComputerName + "\" + $instnace_name
                        }

                        $sql_connection + ": " + $sql_service.State

                        if ($sql_service.State -eq "Running")
                        {
                                Get-SqlDatabase -ServerInstance $sql_connection
                        }
                        else
                        {
                                "Skipping $sql_connection as its not running..."
                        }

                }
        }
}

Catch
{
        (Get-Date).ToString() + ": Error Occurred" 
        throw $_  
        return
}

I have done some testing but a thorough testing.  Please test and let me know your thoughts/comments.

Also, I have not tested this on sql servers hosted in Linux.

Powershell script to get list of databases on a server

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 keep 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 command to grab the databases on that list and send him the results.

Get-SqlDatabase -ServerInstance Server1

That last command requires the SqlServer module loaded, which I already have in my powershell startup.

Import-Module -Name SqlServer

But to add bit more value to this blog, I decided to turn this into a small powershell script.

PS variable $server_name is where you specify the server  name where you would like to look up database names. If that value is not specified, it will use the local computer name.

try
{

        Import-Module -Name SqlServer

        $server_name = "Server1"


        # if $server_name string is empty or null then use the current computer name
        if ($server_name -eq "" -or $server_name -eq $null)
        {
                $server_name = $env:computername
        }

        "Looking up SQL Services on: $server_name"
        $sql_services = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "$server_name" -ErrorAction Stop
        

        if ($sql_services.PSComputerName -ne $null)
        {

                # Display sql services on the screen

                $sql_services  |  select-object -Property PSComputerName, @{n="sql_instance";e={$_.Name -replace "MSSQL\$", ""}}, Name, ProcessID, StartMode, State, Status, ExitCode, PathName | ft -AutoSize


                foreach ($sql_service in $sql_services) 
                {

                        ""
                        $instnace_name = $sql_service.Name -replace "MSSQL\$", ""
                        "Instnace Name: " + $instnace_name

                        if ($instnace_name -eq "MSSQLSERVER") 
                
                        {
                                $sql_connection = $sql_service.PSComputerName
                        }

                        else 
                        {
                                $sql_connection = $sql_service.PSComputerName + "\" + $instnace_name
                        }

                        $sql_connection + ": " + $sql_service.State

                        if ($sql_service.State -eq "Running")
                        {
                                Get-SqlDatabase -ServerInstance $sql_connection
                        }
                        else
                        {
                                "Skipping $sql_connection as its not running..."
                        }

                }
        }
}

Catch
{
        (Get-Date).ToString() + ": Error Occurred" 
        throw $_  
        return
}

I have done some testing but a thorough testing.  Please test and let me know your thoughts/comments.

Also, I have not tested this on sql servers hosted in Linux.