powershell script to connect standalone or cluster SQL

Posted on 2013-01-22
Last Modified: 2013-01-23

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 = $"MSSQL$","")
	$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
    	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;"
		return $true;

	$IsMSSQLUp = IsMSSQLUp([string] $server)

Open in new window


Question by:bibi92
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 40

Accepted Solution

lcohan earned 500 total points
ID: 38806443

Author Closing Comment

ID: 38809247
Thanks bibi

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL ( and MongoDB (…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question