bibi92
asked on
powershell script to connect standalone or cluster SQL
Hello,
How can I use this script for connect to standalone or cluster SQL :
Thanks
bibi
How can I use this script for connect to standalone or cluster SQL :
$list_inst = (get-service -Name "MSSQL*" -exclude "*Launcher*" | Where-Object {$_.status -eq "Running"})
foreach($inst in $list_inst) {
$inst_name = $inst.name.replace("MSSQL$","")
try
{
$cluster=get-wmiobject -class "MSCluster_Cluster" -namespace "root\mscluster" -computername "$mach_name" -ErrorAction Stop | select -ExpandProperty Name
}
catch [Exception]
{
echo "$cluster" | out-file $LOG_FILE -ErrorVariable ERR1 -encoding "default" -append -ErrorAction silentlycontinue
}
function GetClusterSQL
{
param($cluster)
gwmi -class "MSCluster_Resource" -namespace "root\mscluster" -computername $cluster | where {$_.type -eq "SQL Server"} | Select `
@{n='ServerInstance';e={("{0}\{1}" -f $_.PrivateProperties.VirtualServerName,$_.PrivateProperties.InstanceName).TrimEnd('\')}} |
Select -ExpandProperty ServerInstance
}
if($cluster -ne $null) {
$list_vsql = GetClusterSQL $cluster
}
if($cluster -eq $null) {
$ser_name = (Get-WmiObject Win32_Computersystem).Name
}
if($inst_name -eq "MSSQLSERVER") {
$server = $ser_name
}
if($cluster -ne $null) {
foreach($vsql in $list_vsql) {
$server = $vsql
[string] $ser_name = $server.split("\")[0]
}
}
$server = $ser_name + '\'+$inst_name
function IsMSSQLUp([string] $server)
{
trap {
write-host "Message: " $_.Exception.Message ;
return $false ;
} ;
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=$server;Database=master;trusted_connection=true;"
$Connection.Open()
return $true;
$Connection.close()
}
$IsMSSQLUp = IsMSSQLUp([string] $server)
}
Thanks
bibi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER