The working DBA is likely to have a large number of servers to look after. Sure, you’ve got some production servers, but once you add in the test servers, development servers, staging servers, standbys and other odds and ends, you are up in the hundreds. I defy anyone to maintain or monitor that lot manually, by disconsolately clicking and tapping away in SSMS, Server by Stultifying Server. (SBSS). No, be realistic, you need help, and one of the first steps is to get used to using Registered Servers, and in particular, Central Management Servers. Suddenly, with the latter, you have a list that every person and process can share, with different ways of sorting and categorizing servers .
Let’s imagine that you’d like to search through a number of servers to find all the copies of AdventureWorks. You could, of course, log into each server in turn and run this code:
|
1 2 |
USE master SELECT name FROM sysdatabases WHERE name LIKE 'Adventure%' |
..but it is a lot easier to run this automatically on all the servers that you’ve registered in SSMS. Here, you’ll notice that a server instance can sometimes appear several times in a registration in your SSMS, and so we cope with that by getting a unique list.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
$sql = "Use master select name from sysdatabases where name like 'Adventure%'" Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers #now fetch the list of all our registered servers get-childitem 'SQLSERVER:\sqlregistration\' -recurse|where {$_.GetType() -notlike '*ServerGroup*'} | Select servername, connectionstring | sort-object -property servername -unique | foreach-object { try { $conn = new-Object System.Data.SqlClient.SqlConnection($_.connectionstring) $conn.Open() | out-null } catch {"Could not connect to SQL Server instance '$_.servername': $($error[0].ToString() + $error[0].InvocationInfo.PositionMessage). Script is aborted" exit - 1 } try { $cmd = new-Object System.Data.SqlClient.SqlCommand($sql, $conn) $rdr = $cmd.ExecuteReader() } catch { "Could not execute SQL batch '$sql': Error message received: $($error[0].ToString() + $error[0].InvocationInfo.PositionMessage). Script is aborted" exit - 1 } While($rdr.Read()){ "$($rdr['name']) on $($_.servername)" } |
This routine is reusable and can be adapted for many tasks. It just runs the SQL you provide on all your registered servers. You just need to change the loop that creates each line of the report. You’ll notice that it will run on servers that use windows authentication as well as those that use SQL Server authentication
Why use Registered Servers?
If ever you need to perform actions on, or gather information about, a number of SQL Server instances, then you probably want to use Registered Servers. Whether you are using SSMS’s Object Explorer, executing Transact-SQL statements, performing Policy-Based Management, or maybe scripting everything in PowerShell, then it is a very effective way of working. There are certainly simpler ways of maintaining lists of database servers for a wide range of purposes, but none are this versatile. You can import and export lists, and perform operations on every list member, but what clinches it for me is that the same hierarchical list can be used from within SSMS, TSQL or from .NET SMO. Within the registered servers, there are, of course, the Local Server Groups, but these merely represent the Servers that a particular user has registered on a particular machine. The more useful type of registered server store is the Central Management Server Store. These stores can be shared between different users who have the correct roles assigned to them on the server. They can also be accessed by scheduled tasks and ad-hoc automated tasks. They are represented as tables on a designated Central Management Server. The only disadvantage of Central Management Serves is that SQL Server Security cannot be used since passwords are not stored, so automatically accessing servers outside the domain is more complicated in scripts and impossible from SSMS. For this, you’ll need to use Local Server Groups and synchronize them by hand.
Within SSMS, you can access a number of Central Management Servers.
The relationship between the two basic types of Registered Server list can be represented like this.
What is a Central Management Server
A Central Management Server does nothing more than store a hierarchical list of the names and Aliases of instances of SQL Server in tables within the server. It is just like a file hierarchy except that the directories are called groups. They can be saved as XML to a file, and loaded from a file. You can access or update the list via TSQL.
Actions that are taken in SSMS on Registered Servers, such as executing SQL statements, connecting to all instances, or viewing the SQL Server log files, act on all servers in the server group. This includes connecting and performing whatever actions you specify at the same time.
Within scripting, you can access the contents of the group, or all the contents under that group recursively. This means that you can, if you take care with the categorization of your servers, perform some operations, such as checking that backups worked, on all your servers, and perform other checks such as CPU usage only on production servers, or automatically put a day’s changes in the development servers into source control.
The Central Management Servers can easily be accessed from SMO and PowerShell. Both the Local Server Groups and the Central Management Servers can be accessed from SQLPS in PowerShell. There are two main groups in the 'SQLSERVER:\sqlregistration\' path
- Central Management Server Group
- Database Engine Server Group
The 'SqlServer::SQLSERVER:\sqlregistration\Central Management Server Group' contains the Central Management Server groups whereas the ”SQLSERVER:\sqlregistration\Database Engine Server Group‘ path contains the local Server groups.
If you are scripting, then the Central Management Server Group is likely to be visible to your scripts. For your local Server Groups to be visible, you have to have your scheduled tasks using your login and credentials. By using the Central Management server, you can set up a special login with the correct role to run the script, which is much more satisfactory.
Permissions to access Server collections
If you only need to connect to, and read, the server collections in a central management server, then you just need membership in the ServerGroupReaderRole for the server that hosts the list. Only members of the ServerGroupAdministratorRole role are allowed to edit the contents of central management server.
When you execute queries in SSMS for all the members of a Central Management Server group, each query will execute in the context of the user, by using Windows Authentication. The information can include connection information, but unlike the Local Server Groups, this isn’t currently used.
Where is the information, and how can you access it.
Centralized Management Server details about servers and groups are stored within the msdb of the Central Management Server in the …dbo.sysmanagement_shared_registered_servers_internal
… and the …dbo.sysmanagement_shared_server_groups_internal
… tables.
Information about local groups is held in a file called RegSvr.xml, held in one of a number of places depending on the OS and version of SQL Server
e.g. C:\Documents and Settings\[USERNAME]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSvr.xml, C:\Users\[USERNAME]\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml, or C:\Users\[USERNAME]\AppData\Local\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml
Accessing the information Via SQL
You can read information and modify existing information directly in SQL if you have access to the Central Management Server. Here is a routine to view the servers currently registered and the groups they belong to.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT TheGroup.name AS [Server Group], TheGroup.[Description] AS [Group Description], TheServer.name, TheServer.server_name AS [Server name], TheServer.[description] AS [Description] FROM msdb.dbo.sysmanagement_shared_server_groups_internal TheGroup LEFT JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal TheServer ON TheGroup.server_group_id = TheServer.server_group_id WHERE TheGroup.server_type = 0 --only the Database Engine Server Group AND server_name IS NOT null ORDER BY [Server Group], [Server name] |
You can, of course, update these tables directly, (they have constraints to prevent you doing anything silly), and for the seasoned DBA, this will be by far the easiest way of maintaining the lists. You can import a list of two hundred servers in milliseconds believe me. However, doing it via SSMS allows you to manipulate the hierarchy more easily, and gives you access to the file export and import facilities.
Here is a simple SQL Update statement to change a description of a group
|
1 2 3 |
UPDATE msdb.dbo.sysmanagement_shared_server_groups_internal SET DESCRIPTION = 'List of servers that are polled for a backup task.' WHERE NAME='TestCell' |
You’ll notice that the description fields are NVarchar fields of 4000 characters. A lot of information about your servers can be stored here, and there isn’t much that can be done to restrain you, beyond appealing sense of relational purity, from packing in a sorts of information about the server, and using it to do advanced filtering of servers, once the mechanism of hierarchical groups becomes too constraining.
Here is a simple way of inserting lists of servers. You’ll have to imagine a whole long list of servers. Obviously, you have to specify what group you want them in rather than put them all in ‘DevelopmentServer’.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
DECLARE @CurrentServerGroup INT --id of the group where you want to place them IF NOT EXISTS --does the DevelopmentServer group exist already? (SELECT * FROM msdb.dbo.sysmanagement_shared_server_groups_internal WHERE name LIKE 'DevelopmentServer') --should test only for same parent! INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal (name,Description, Server_Type, parent_ID, is_system_object) SELECT 'DevelopmentServer','all the servers under developers desks',0,1,0 --server type 0=DatabaseEngineServerGroup, id="and" it isnt a system object SELECT @CurrentServerGroup= server_group_id --and remember the ID FROM msdb.dbo.sysmanagement_shared_server_groups_internal WHERE name LIKE 'DevelopmentServer' INSERT INTO msdb.dbo.sysmanagement_shared_registered_servers_internal (Server_Group_ID, name, Server_name, description, Server_Type) SELECT @CurrentServerGroup, 'Dee', 'PhilFactor.com\SQLthumper', 'The Shared Development server', 0 UNION ALL SELECT @CurrentServerGroup, 'Dozy', 'PhilFactor.com\SQLbasher','The integration server', 0 UNION ALL SELECT @CurrentServerGroup, 'Mick', 'PhilFactor.com\SQLfighter','The sandbox', 0 UNION ALL SELECT @CurrentServerGroup, 'Tich', 'PhilFactor.com\SQLbruiser','Test server', 0 |
Note that Microsoft don’t make this sort of method public, and may change the database tables in future releases. If you want a routine that will work in subsequent versions of SQL Server, you’re better off with using an SMO-based routine to populate and maintain the lists. However, if you export the list before you upgrade, then you are likely to be safe from losing your data.
Accessing a Central Management Server in SSMS.
You can connect to several Central Management Servers at once.
Most of the time, you’ll probably connect to one or more existing Central Management Servers. In order to connect to a central management server, you simply
- Click on the View menu in SQL Server Management Studio, and click Registered Servers.
- In the browser pane, right-click on ‘Central Management Servers’ and left click on the ‘Register Central Management Server …,’
- When the dialog box appears, fill everything in, hit ‘save’, and you’ve registered the server.
Creating a Central Management Server in SSMS
If you haven’t got a Central Management Server, then you’ll need to designate an instance of SQL Server as a central management server in SQL Server 2008 or 2012 by using SQL Server Management Studio. You can even use SQLExpress for this server, though you wouldn’t be able to rum policy-based Management from it.
- In the View menu In SQL Server Management Studio, click on ‘Registered Servers’ item.
- In the Registered Servers Explorer, expand Database Engine, right-click Central Management Servers, point to New in the context menu, and then click Central Management Servers.
- In the New Server Registration dialog box, register the instance of SQL Server that you want to become the central management server.
- In the Registered Servers Explorer, right-click the central management server, point to New in the context menu, and then click New Server Group. Type a group name and description, and then click OK.
- In Registered Servers, right-click the central management server group, and then click New Server Registration.
- In the New Server Registration dialog box, register instances of SQL Server that you want to become members of the server group.
- After you have registered a server, the central management server will be able to execute queries against all servers in the group at the same time.
PowerShell Scripting with centrally-registered servers.
You can choose to use SQLPS or do without it when accessing servers. Here is a routine for listing all the logins for all the servers in a group without SQLPS. You will, of course, need to change the name to your CMServer, and change the name of the group from ‘MyProductionServers’ to the group you want a list of logins from!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
#Load SMO assemblies $CentralManagementServer = "PhilFactor.com\MyCMServer" $MS='Microsoft.SQLServer' @('.SMO', '.Management.RegisteredServers', '.ConnectionInfo') | foreach-object {if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) {"missing SMO component $MS$_"}} $connectionString = "Data Source=$CentralManagementServer;Initial Catalog=master;Integrated Security=SSPI;" $sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString) $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection) $CentralManagementServerStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn) $My="$ms.Management.Smo" # $CentralManagementServerStore.ServerGroups[ "DatabaseEngineServerGroup" ].ServerGroups[ "MyProductionServers" ].RegisteredServers| Foreach-object {new-object ("$My.Server") $_.ServerName } | # create an SMO server object Where-Object {$_.ServerType -ne $null} | # did you positively get the server? Foreach-object {$_.Logins } | #logins for every server successfully reached Select-object @{Name="Server"; Expression={$_.parent}}, Name, DefaultDatabase , CreateDate, DateLastModified | format-table |
If you wish to do an operation on all your servers, or all the servers in subgrpups of the group you choose , a slight modification will give you what you want
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
#Load SMO assemblies $CentralManagementServer = "PhilFactor.com\MyCMServer" $MS='Microsoft.SQLServer' @('.SMO', '.Management.RegisteredServers', '.ConnectionInfo') | foreach-object {if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) {"missing SMO component $MS$_"}} $connectionString = "Data Source=$CentralManagementServer;Initial Catalog=master;Integrated Security=SSPI;" $sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString) $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection) $CentralManagementServerStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn) $My="$ms.Management.Smo" # $CentralManagementServerStore.ServerGroups[ "DatabaseEngineServerGroup" ].GetDescendantRegisteredServers() | foreach-object {new-object ("Microsoft.SqlServer.Management.Smo.Server") $_.ServerName } | # create an SMO server object Where-Object {$_.ServerType -ne $null} | # did you positively get the server? Foreach-object {$_.Logins } | #logins for every server successfully reached Select-object @{Name="Server"; Expression={$_.parent}}, Name, DefaultDatabase , CreateDate, DateLastModified | format-table |
If you use SQLPS, it becomes even easier, ( change the path ‘SQLSERVER:\sqlregistration\Central Management Server Group\PhilFactorsCMS\Backup\' to whatever your CentralMS group is
|
1 2 3 4 5 6 7 8 9 |
Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers #now fetch the list of all our registered servers get-childitem 'SQLSERVER:\sqlregistration\Central Management Server Group\PhilFactorsCMS\Backup\' -recurse | Foreach-object {new-object ("Microsoft.SqlServer.Management.Smo.Server") $_.ServerName } | # create an SMO server object Where-Object {$_.ServerType -ne $null} | # did you positively get the server? Foreach-object {$_.Logins } | #logins for every server successfully reached Select-object @{Name="Server"; Expression={$_.parent}}, Name, DefaultDatabase , CreateDate, DateLastModified | format-table |
…and you can use the same code to do the Local Server Group. All you need to do is to change the path for the get-childitem cmdlets.
You may be wondering to yourself ‘this is a lot of trouble just to get a list of all the logins’. If so, then pull yourself together and think. This is good for any server-based operation. In a previous article, I showed how one routine could be used for a large variety of tasks. If you use something like the routine I showed you then, with the use of centrally-registered servers, then there is no limit: You can script anything you can do in SSMS.
If you wish to export the entire list of centrally-managed servers, and their hierarchy, you can save it as an XML file from SSMS. You can even save it via PowerShell and SMO (I’d forget to do it via SSMS). This routine saves only the contents of the 'MyGroup‘ group and its groups. You’ll need to alter the name of the server and the serverGroup, of course.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
#Load SMO assemblies $CentralManagementServer = "PhilFactor.com\MyCMServer" $MS='Microsoft.SQLServer' @('.SMO', '.Management.RegisteredServers', '.ConnectionInfo') | foreach-object {if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) {"missing SMO component $MS$_"}} $connectionString = "Data Source=$CentralManagementServer;Initial Catalog=master;Integrated Security=SSPI;" $sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString) $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection) $CentralManagementServerStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn) $CentralManagementServerStore.ServerGroups[ "DatabaseEngineServerGroup" ].ServerGroups[ "MyGroup" ].Export('e:\MyGroupcmsExport.xml', [ Microsoft.SqlServer.Management.RegisteredServers.CredentialPersistenceType ]::PersistLoginNameAndPassword) "did that work" |
No longer do you need to perform your routine tasks Server by Stultifying Server. (SBSS).
Load comments