Tuesday, 14 June 2016
SQL Queries in PowerShell
OK, So Backup guys needed to run a SQL Query to find out which Node of an Availability Group is hosting the Primaries.
So two things here, I needed a SQL Script to give me that information and then a way of running a SQL Script in Powershell and returning the result.
I found the following two scripts online.
$ServerName = "."
$DatabaseName = "master"
$Query = "SELECT HAGS.Primary_Replica FROM Sys.dm_hadr_availability_group_states HAGS INNER JOIN sys.availability_groups AG ON AG.group_id = HAGS.group_id WHERE AG.name = 'AAG_Name'"
#Timeout parameters
$QueryTimeout = 120
$ConnectionTimeout = 30
#Action of connecting to the Database and executing the query and returning results if there were any.
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables
This script is wonderful, it can be used to run any SQL Statement in powershell and return the results in a Table.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment