May 2, 2008 at 11:39 pm
I wasn't sure where to post this because it's really a question about internals.
What I would like to do is programmatically register a server to management studio.
The reasoning: I am in an environment where I manage roughly 60 production instances and we are adding more at a fairly decent rate. We have a database layout where we make it part of our initial setup to add the server to a centralized admin instance/dba database/instance table. What I am interested in trying to do is to be able to read from that table to populate my registered server list.
Some of the details I store on the table are: production (yes/no), active (yes/no), functional business group (ie customer service, accounting, payroll, etc.)
The ideal way for me to put them in my registered servers list would be something like:
Production
Customer Service
SQL 2000
instance_name_01
SQL 2005
instance_name_19
... and so on through the instances. Doing so manually would take time to get that in there, but we already have those details stored on our tables, so that's why I thought there may be someone out there who dabbled into this.
I found the Smo function to enumerate all of the registered servers, but that class doesn't allow the addition of new servers.
It very well may not be possible, but I wanted to see if anyone else ever had this idea or not?
With more and more servers coming online, and with other team members setting them up, I would really like to have a way to dynamically keep my registered server list up to date.
One thought I did have was to enumerate through the table to generate a similar XML formed file that the import/export functionality provides such as this:
Group name="Local Machine" description="">
Server name="(local)" description="">
ConnectionInformation>
ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907
ServerName>(local)
AuthenticationType>0
UserName />
Password />
AdvancedOptions>
PACKET_SIZE>4096
CONNECTION_TIMEOUT>15
EXEC_TIMEOUT>0
ENCRYPT_CONNECTION>False
AdvancedOptions>
ConnectionInformation>
Server>
Server name="divwl-92ts1d1" description="Local instance - 'divwl-92ts1d1'">
ConnectionInformation>
ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907
ServerName>divwl-92ts1d1
AuthenticationType>0
UserName />
Password />
AdvancedOptions />
ConnectionInformation>
Server>
Group>
I had to take out the other brackets to get it to show the code. Sorry about that.
Any other thoughts/ideas would be helpful 🙂
Thanks to all,
Steve
May 4, 2008 at 2:32 pm
To add a server to a registration group in SMO, you first make (" as New") a ServerGroup object with the name of the ServerGroup, then you make a RegisteredServer object with that group and name, then you call its Create() method.
Here is what I use for something similar. You should be able to adapt it to your own needs:
Imports Microsoft.SqlServer
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Server
Imports system.Data
Module Util
Public Sub Servers_List(ByVal lstOut As IList)
Dim rss As RegisteredServers.RegisteredServerCollection = SmoApplication.SqlServerRegistrations.RegisteredServers
Dim rse() As RegisteredServers.RegisteredServer = SmoApplication.SqlServerRegistrations.EnumRegisteredServers
Dim rsgs As RegisteredServers.ServerGroupCollection = SmoApplication.SqlServerRegistrations.ServerGroups
Dim sg As New RegisteredServers.ServerGroup("AutoRegistered")
'sg.Parent = rss.Parent
If Not rsgs.Contains("AutoRegistered") Then
sg.Create()
Else
sg = rsgs.Item("AutoRegistered")
End If
lstOut.Clear()
For Each rs As RegisteredServers.RegisteredServer In rse
lstOut.Add(rs.Name.ToUpper)
Next
Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)
If dt.Rows.Count > 0 Then
For Each dr As DataRow In dt.Rows
'If dr!Instance IsNot DBNull.Value Then
' lstOut.Add(dr!Name & "\" & dr!Instance)
'Else
If Not lstOut.Contains((dr!Name).ToString.ToUpper) Then
lstOut.Add(dr!Name.ToString.ToUpper)
Dim rs As New RegisteredServers.RegisteredServer(sg, dr!Name)
rs.ServerInstance = dr!Name
rs.Create()
End If
Next
End If
End Sub
End Class
(edit: added vb formatting tag. Nice!)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 4, 2008 at 9:14 pm
Thanks RBarry! I will give that a shot.
May 4, 2008 at 10:25 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply