Connecting PowerShell scripts to a database gives us a huge buster to process automation and processes/systems monitoring.
We can apply it to many different solutions as well as bypass, extend, or improve the functionality of other systems.
A few practical/real examples how this can be applied :
- to store a script data and configuration (it is more flexible than hard-coded parameters or configuration (.ini) files,
- read and process data from other systems (great for processes automation),
- monitor other systems activity and alert users and admins when some events occur,
- enter manipulated/calculated data directly to other systems’ databases.
I also created a solution that monitors all configuration changes in different systems and creates an external audit trail functionality that can detect changes (and send a notification) applied directly to the database.
But let’s talk about efficient technics of working with databases from PowerShell.
MS SQL Server is a very popular database system from Microsoft. Accessing SQL Server data from PowerShell gives us the possibility of data processing and data monitoring. There are a few methods of accessing data on SQL Server, but I will describe the easiest and most efficient one (in my opinion).
Module installation
MS SQL Server module for PowerShell is available at PowerShell Gallery and you can easily install it with the following cmdlet:
Install-Module -Name SqlServer
Use example
The basic use of the module is very similar to SQLite. First, you have to load the module:Import-Module SqlServer
After that, execute any SQL statement (Select, Insert, Update, Delete, …) using:$results = Invoke-Sqlcmd -Query "<query>" -ConnectionString "<connection_string>"
The connection string can look for example like this:Server=<server>;database=<db_name>; User ID=<user>; Password=<password>;ApplicationIntent=ReadOnly
Like in the SQLite example, I recommend converting the returned results to the array of objects adding [pscustomobject[]] before $results.
The example of the source code can look like this (replace the italic text with your connection data):
Import-Module SqlServer
[pscustomobject[]]$results = Invoke-Sqlcmd -Query "SELECT * FROM Customer" -ConnectionString "Server=SERVER1;database=CRMdb; User ID=user1; Password=password1;ApplicationIntent=ReadOnly"
if ($results.count -gt 0) {
Write-Host "Numer of selected records:" $results.count
} else {
Write-Host "No records found"
}
I hope that this post will be helpful in database access directly from PowerShell.
If you like it, feel free to share.
