\nI've noticed that some folks have written PowerShell scripts that execute against a list of servers. In the scripts, they read the names of the servers from XML files. But the SQL Server PowerShell provider in RC0 has a useful "component" called SQLRegistration; its "path" is SQLSERVER:\\SQLRegistration that they can use instead.\n<\/p>\n
\nThis path permits enumeration and manipulation of the (SQL) servers and server groups that are defined using SQL Server Management Studio. The SQLRegistration path is not specific to server or instance, but to the SSMS user that's signed on. For example, on the same physical machine, SQLRegistration for the Windows user bobb reflects bobb's settings in SSMS. SQLRegistration for user mary (on the same machine) would reflect mary's SSMS settings. There is no SQLSERVER:\\SQLRegistration\\{machinename}\\{instancename} path, just SQLSERVER:\\SQLRegistration.\n<\/p>\n
\nSQLRegistration contains two "subdirectories", called "Central Management Server Group" and "Database Engine Server Group". These are equivalent to SSMS' "Central Management Servers" and "Local Server Groups" folders, respectively.\n<\/p>\n
\nSo rather than write custom XML files that hold information about groups of servers you can use the built-in Registration store. You can import and export registration information to keep team members' view of SQL Servers and groups consistant using SSMS.\n<\/p>\n
\nBecause I've been writing PowerShell scripts against the provider's Policy and DataCollection stores, I thought it would be fun to program SQLRegistration. I found the Microsoft.SqlServer.Management.Registration namespace in BOL (which is RC0 is really called Microsoft.SqlServer.Management.Smo.Registration, BOL says this will change before RTM) and was about to code against the RegisteredServer and ServerGroup classes, when I realized that Registration was a simple hierarchies of servers and groups. Just like directories and files. And the built-in groups each have a "mode" property that's value is "d". Registered Servers have a blank mode property. Hmmm…\n<\/p>\n
\nSo, at the PowerShell prompt
\n> cd 'SQLSERVER:\\sqlregistration\\Database Engine Server Group'
\n> new-item MyNewGroup\n<\/p>\n
\ncreates a new directory (ServerGroup) named MyNewGroup. Well then, a registered server should be a file.\n<\/p>\n
\n> new-item MyNewServer -itemtype file\n<\/p>\n
\nNew-Item : SQL Server PowerShell provider error: This provider only supports the
\ncreation of new Server Groups or Server Registrations. Please specify "directory"
\nto create a new Server Group or "registration" to create a new Server Registration.\n<\/p>\n
\nSo this should work?\n<\/p>\n
\n> new-item MyNewServer -itemtype registration\n<\/p>\n
\nNew-Item : SQL Server PowerShell provider error: Please specify a connection string using the -Value parameter.\n<\/p>\n
\n> new-item MyNewServer -itemtype registration -Value "server=mynewserver;integrated security=true"
\n>\n<\/p>\n
\nYep. This creates a new server registration using the connection parameters that you specify. How cool is that? No other part of the SQL SERVER PowerShell provider that I'm aware of supports the new-item cmdlet\/operation. Because the SQLRegistration represent fairly simple items and hierarchies new-item is supported here. No custom programming needed (although you can use the SMO classes if you'd rather), just treat it as you would file system.\n<\/p>\n
\nA couple of caveats. In RC0 SSMS and PowerShell SQLRegisgtration sync up when you open SSMS but if you're using both at once, the sync to SSMS isn't immediate. Sometimes refresh doesn't appear to show the changes you made in PowerShell. Eventually it will show them if you refresh multiple times or restart SSMS. And after deleting a Server Registration in SSMS, I still had to run "rm MyNewServer" in PowerShell to get rid of it there. Also, I use "integrated security=sspi" rather than "integrated security=true" in my ADO.NET connection strings. This seems to bother SSMS which reports an error in configuration. Use "true" rather than "sspi" when creating registrations through PowerShell.<\/p>\n","protected":false},"excerpt":{"rendered":"
I've noticed that some folks have written PowerShell scripts that execute against a list of servers. In the scripts, they read the names of the servers from XML files. But the SQL Server PowerShell provider in RC0 has a useful "component" called SQLRegistration; its "path" is SQLSERVER:\\SQLRegistration that they can use instead. This path permits enumeration […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21,29],"tags":[],"class_list":["post-685","post","type-post","status-publish","format-standard","hentry","category-powershell","category-sql-server-2008"],"yoast_head":"\n