I wanted to take my list of servers that I have in a database and put them in a CMS so I could have them all listed and accesibile for other DBA’s to use that do not know powershell. This is a cool and powerfull thing, I just prefer powershell. Anyway I thought since I already have a listing of all the instances in my db it should be simple to throw together a script to register these. I did a bit of searching and I found this link and took some coed from it and modified it a bit to work for me.
So I found that this did not work just right and I had a big problem with named instances. When I would pass a server with a named instance the ‘\’ in the name really gave me issue and I figured out that I needed to use the HEX for the ‘\’ and that turns out to be ‘%5C’ So below is what I came up with and it works well now.
$devservers = invoke-sqlcmd -serverinstance SERVERNAME -database DBNAME -query “select i_name from instances i, Versions v where i.I_ID = v.I_ID and SQL_Version like ‘%2008%’”
cd ‘sqlserver:\sqlregistration\central management server group\SERVERNAME01%5CINSTANCENAME\2008′
foreach ($server in $devservers)
$name = $server.i_name
$name1 = $name
if ($name1 -like “*\*”)
$name1 = $name1.replace(“\”, “%5C”)
new-item $name1 -itemtype registration -value “server= $name1;integrated security=true”
I hope that will help some of you out there and please hit me up here with a comment or on twitter @smithco32 if you have any questions