Solved

powershell script to connect standalone or cluster SQL

Posted on 2013-01-22
2
702 Views
Last Modified: 2013-01-23
Hello,

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)
}

Open in new window


Thanks

bibi
0
Comment
Question by:bibi92
2 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 38806443
0
 

Author Closing Comment

by:bibi92
ID: 38809247
Thanks bibi
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
A brief introduction to what I consider to be the best editor for PowerShell.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now