Post reply

Central Management Servers - automated retrieval from registered servers

  • July 4, 2019 at 9:44 am

    #3659999

    Dear all,

    I'm a DBA for several years no and never had the need to use CMS until now.

    The SQL-environments have set up to use one CMS and this is pretty handy (but also tricky as well).

    Exploring CMS I noticed that you can open a 'new query' from the group you declared - write or copy/past a query and execute it for all registered servers.

    I just wonder if it is possible to write a stored procedure who collects data from the registered servers in CMS and store it in a table/database.

    The reason is I like to know is that the SQL servers we use in our environment are completely shut-down for AD-HOC queries, there is no possible way to use OPENROWSET of OPENQUERY, the login-modus is set to DOMAIN-USERS only - so LINKEDSERVERs will not work because thye only work with a SQL-LOGIN (and this is not allowed) and IMPERSONATION within the configuration is not working either because the domain-user used is not recognised although it is present on the targetserver !!!!

    So the only way to reach the other servers is bij using CMS bu tthis goes (standard) just on-way as far as I can tell now !!!!

    Can someone shed a light on this?

    Thanks in advance,

    Guus Kramer

    The Netherlands

  • July 4, 2019 at 9:59 am

    #3660001

    hi Guus

    Utrecht here 🙂

    Connection via CMS is the same connection as you do using sqlcmd, for example.

    Servers registered in CMS are stored in two tables in msdb :

    • dbo.sysmanagement_shared_registered_servers_internal
    • dbo.sysmanagement_shared_server_groups_internal

    Link:

    https://www.mssqltips.com/sqlservertip/2397/sql-server-central-management-servers-system-tables/

    Connection through linked servers is the same, however you have to use AD authentication (SQL logins are disabled, right?) and for that KERBEROS & SPNs should be configured properly.

    So, in your case I see the following solution :  powershell script which reads above mentioned tables in msdb and connects to the needed SQLs in a cycle.

    example:

    https://www.red-gate.com/simple-talk/sysadmin/powershell/using-a-server-list-to-control-powershell-scripts/

     

     

     

     

  • July 4, 2019 at 10:05 am

    #3660002

    gkramer 23701 wrote:

    I just wonder if it is possible to write a stored procedure who collects data from the registered servers in CMS and store it in a table/database.

    In case linked servers work, it is possible to create such stored procedure and create/drop generic linked server pointing to appropriate target sql inside it, query it and save data into a table.

     

    Apart from that, working with CMS and high amount of servers registered (700+) I found that sometimes it's hard to find a SQL server in the CMS tree (servers are grouped by env, edition, version, product, etc).  So, I created SSRS report which shows me where a SQL server is in the tree (parameter of the report).  Wildcard search is also possible.

    If interested, I will share it.

    Regards,

    Andrey.

    • This reply was modified 4 years, 10 months ago by  Andrey.

  • July 4, 2019 at 10:17 am

    #3660005

    Andry,

    Thanks for the quick reply.

    You mentioned Powershel but all I had ever had to do I could manage with TSQL (often using XP_CMDSHELL to work on commandline, OPENROWSET/OPENQUERY and LINKEDSERVER).

    PS is new to me so I have to dig in so if you have some basic examples please let me know....

    (and we are still running version PS4 on our servers and I was told there will be no upgrade to PS5 !)

    I'll read the links you send me and investigate.....

    Guus

    (Located in Tilburg)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply