Dynamically Register SQL Instances in SQL Server Management Studio 2008

I’ve previously blog, about programmatically registering SQL instances in SQL Server Management Studio (SSMS) and on Dynamically discovering SQL instances through SMS/SCCM. This post combines the two techniques showing how to register any missing registrations from your SSMS 2008 or simply register all SQL instances in a new group.
 
Copy the following code to a file (for example C:usrbinregister10.ps1):
 
**UPDATED 1/28/2009 to check for existance of server within group before creating ***
 
param($server,$group=’LostAndFound’,$path = ‘SQLSERVER:SQLRegistrationDatabase Engine Server Group’,[bool]$allowDups=$false)
SET-LOCATION $path
if (!(Test-Path $group))
{ New-Item $group }
[bool]$isExists = $false
Get-ChildItem -recurse | where {$_.Mode -eq ‘-‘} | foreach {if ($_.Name -eq $server) {$isExists = $true}}
SET-LOCATION $group
if (!($isExists) -or $allowDups)
{
 if(!(Test-Path $(Encode-Sqlname $server)))
 { New-Item $(Encode-Sqlname $server) -itemtype registration -Value "server=$server;integrated security=true" }
}
 
Next launch the SQL Server 2008 Powershell host, sqlps.exe
 
CD SQLSERVER:
 
Execute the following substituting your ServerIntsance and Database source.
 
Invoke-SqlCmd -Query "SELECT instance_name FROM sms_sql_instance_vw" -ServerInstance ‘Z002SQL1’ -Database ‘DBAUtility’ | %{C:usrbinregister10.ps1 $_.instance_name}
 
The query uses a view against SMS/SCCM data described in the blog post Inventory SQL Server Databases with PowerShell. Since SSMS 2008 allows you to register the same SQL instance multiple times as long as they are not in the same server group, the Powershell script takes parameters to  register all instances. The following registers all SQL instances regardless of whether they are registered or not in a new group called ‘All’:
 
Invoke-SqlCmd -Query "SELECT instance_name FROM sms_sql_instance_vw" -ServerInstance ‘Z002SQL1’ -Database ‘DBAUtility’ | %{C:usrbinregister10.ps1 -server $_.instance_name -group ‘All’ -allowDups $true}
   
 
Advertisements
This entry was posted in PowerShell. Bookmark the permalink.