The Multifunctioning DBA

Sep 9 2011   9:21PM GMT

Powershell Register Servers to CMS

Colin Smith Colin Smith Profile: Colin Smith

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.

http://ask.sqlservercentral.com/questions/475/powershell-register-servers-how-do-i-add-a-connection-property

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”)
}
$name

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: