SQL Server PSObject - Working With SQL Server Using PowerShell
SQL Server loves PowerShell, it makes SQL Server DBA life easy and simple. I have seen SQL Server automated with PowerShell to an extent where I stopped using SQL Server Management Studio (SSMS) ever since I started using PowerShell. Database Administrator doesn’t require SSMS all the time to connect to SQL Server if you are accompanying with PowerShell. There are quite a few tools are already available in the internet from dbatools.io, idera PowerShell scripts and etc., but every approach is unique.
SQL Server PSObject, SQL Server functionalities within a single PowerShell object. PSObject, I love the most in PowerShell, you can customise the object of your own choice of properties & methods, and the usage is also as simple as just initiate the object and call the methods of your choice.
I have created a new PSObject with ConnectSQL and ExecuteSQL methods, they are the very basic and predominant functionalities to work with SQL Server.
# Create an object
$SQLServerObject = New-Object -TypeName psobject
And added few essential properties, mainly used to establish the connection to Sql Server…
# Basic properties
$SQLServerObject | Add-Member -MemberType NoteProperty -Name ServerName -Value 'SQLServer' # Server Name
$SQLServerObject | Add-Member -MemberType NoteProperty -Name DefaultPort -Value 1433 # Port
$SQLServerObject | Add-Member -MemberType NoteProperty -Name Database -Value 'master' # Database
$SQLServerObject | Add-Member -MemberType NoteProperty -Name ConnectionTimeOut -Value 15 # Connection Timeout
$SQLServerObject | Add-Member -MemberType NoteProperty -Name QueryTimeOut -Value 15 # Query Timeout
$SQLServerObject | Add-Member -MemberType NoteProperty -Name SQLQuery -Value '' # SQL Query
$SQLServerObject | Add-Member -MemberType NoteProperty -Name SQLConnection -Value '' # SQL Connection
The properties like ServerName, Port, Database and ConnectionTimeout are must to define before you call either connect method or execute method, SQLConnection property holds the sql server connection object to execute the sql queries with execute method. SQLQuery property holds the query text to execute the query against the sql server mentioned in the ServerName property, you can also enter the query while calling the execute method. Ensure the Server is ping-able using the TestConnection method…
# Method to ensure the server is pingable
$SQLServerObject | Add-Member -MemberType ScriptMethod -Name TestConnection -Value {
Test-Connection -ComputerName $this.ServerName -ErrorAction SilentlyContinue
}
Establish the connection and store the connection object in the SQLConnection property of the object.
# Method to establish the connection to SQL Server and holds the connection object for further use
$SQLServerObject | Add-Member -MemberType ScriptMethod -Name ConnectSQL -Value {
[string] $ServerName= $this.ServerName
[int] $Port = $this.DefaultPort
[string] $Database = $this.Database
[int] $TimeOut = $this.ConnectionTimeOut
$SQLConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = "Server = $ServerName,$Port; Database = $Database; Integrated Security = True;Connection Timeout=$TimeOut;"
$SQLConnection.Open()
$this.SQLConnection = $SQLConnection
}
ExecuteSQL method to execute the queries using the connection established using the ConnectSQL method…
# Execute SQL method to execute queries using the connection established with ConnectSQL
$SQLServerObject | Add-Member -MemberType ScriptMethod -Name ExecuteSQL -Value {
param
(
[Parameter(Mandatory=$false)]
[string] $QueryText
)
# Select runtime query / predefined query
[string] $SQLQuery = $this.SQLQuery
if ([string]::IsNullOrEmpty($QueryText) -eq $false)
{
$SQLQuery = $QueryText
}
# Verify the query is not null and empty, then execute
if ([string]::IsNullOrEmpty($SQLQuery))
{
Write-Host "Please add query to this object or enter the query." -ForegroundColor Red
}
else
{
if ($this.SQLConnection.State -eq 'Open')
{
# SQL Command
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = $SQLQuery
$SQLCommand.CommandTimeout = $this.QueryTimeOut
$SQLCommand.Connection = $this.SQLConnection
# SQL Adapter
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SQLAdapter.SelectCommand = $SQLCommand
# Dataset
$DataSet = New-Object System.Data.Dataset
$SQLAdapter.Fill($DataSet) | Out-Null
return $DataSet.Tables[0]
}
else
{
Write-Host "No open connection found." -ForegroundColor Red
}
}
}
And finally return the object…
# Return the object
return, $SQLServerObject
Now, lets see how we can connect to sql server and execute the sql queries…
First, create an object…
PS C:\GitRepo> $SQL = .\Create-SQLServerObject.ps1
PS C:\GitRepo> $SQL
By default it takes the localhost name as servername, default sql server port and master database a default database to establish the connection. Assign a server name and test the connectivity…
PS C:\GitRepo> $SQL.ServerName = 'SQLServer'
PS C:\GitRepo> $SQL.TestConnection()
If the server is accessible, then establish the connection to the SQL Server, if the sql server port is other than default port, then assign the port to the object…
PS C:\GitRepo> $SQL.DefaultPort = 2866 # Just an example
Establish the connection…
PS C:\GitRepo> $SQL.ConnectSQL()
PS C:\GitRepo> $SQL.SQLConnection
Add query text to the object and call ExecuteSQL method…
PS C:\GitRepo> $SQL.SQLQuery = "Select database_id,name from sys.databases"
PS C:\GitRepo> $SQL.ExecuteSQL()
You can also enter the query while calling the method itself…
PS C:\GitRepo> $SQL.ExecuteSQL("Select @@Version as Version")
You can add any number of methods of your choice and customise as per your requirements, you can also execute the *.sql files as well…
The complete code is available in my git repository.
