How to enroll SQL Server instances on multi server into an existing SQL Server Utility (PowerShell)?
Introduction
This PowerShell sample script is used to enroll SQL Server instances on multi server into an existing SQL Server Utility. This script applies to SQL Server 2008 R2 and SQL Server 2012 editions.
Scenarios
SQL Server Utility can manage the SQL Server environment as a whole through the concept of application and multiserver management in it, so after created one utility control point, administrator need to enroll instances of SQL Server into the SQL Server
Utility. If your task included in below two scenarios, this script will be one good assistant.
1.There are multiple managed instances need to be enrolled tothe SQL Server Utility.
2.We can store the server and instance names in one .txt file.
Script
Assuming there are two rows in my E:\Onescript\Instances.txt file.
Server1\instance2
Server2\instance1
Customer can use this script by following below steps:
Step1: Launch the Windows PowerShell.Click
Start, point to
All Programs, point to
Accessories, point to
Windows PowerShell, right-click
Windows PowerShell, and select
Run As Administrator.
Screenshot:
Step2: Locate to the script path and load the script into current session.
Command: . E:\Onescript\enrollinstances.ps1
Screenshot:
Note: Replace the
E:\Onescript\ with your path.
Step3: Call the function EnrollInstances.
Command: EnrollInstances
Screenshot:
Step4: The screen will prompt you enter in the utility instancename.
Command: Server1\instanceutility
Screenshot:
Step5: The screen will prompt you enter in a valid Windows domain account on the instance as the SQL Server Agent proxy account.
Command:
Please Enter the Windows Domain Account:domain\accountname
Please Enter the password: ********
Screenshot:
Step6: The screen will prompt you enter in the full path of the .txt file, this file contains the instances that you would like to add to the utility instance.
Command: E:\Onescript\Instances.txt
Screenshot:
Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.
PowerShell
Edit|Remove
powershell
#Check if the managed instances existed in this SQL Server Utility instance
Foreach ($instance in $instances)
{
if ($instance.Name -eq $svr)
{
$ifExists = $TRUE
Write-host "The instance" $svr " had existed in the utility instance" $svrUtility
}
}
#Check if the managed instances existed in this SQL Server Utility instance
Foreach ($instance in $instances)
{
if ($instance.Name -eq $svr)
{
$ifExists = $TRUE
Write-host "The instance" $svr " had existed in the utility instance" $svrUtility
}
}
Additional Resources