Questions tagged [central-management-server]
SQL Server Central Management Server is a feature that allows an instance of SQL Server that is designated as a Central Management Server to maintain server groups containing the connection information for one or more instances of SQL Server.
32
questions
0
votes
1
answer
24
views
Run Policy Based Management On Central Management Server with Code
I have a jump\terminal server to administer 100 SQL Servers and want to evaluate my locally created policies across all the servers by using the Central Management Server but WITHOUT:
creating the ...
- 1,528
0
votes
0
answers
26
views
SSRS - Query CMS and Get Disk Space
We're using SQL Server 2019
I have CMS setup and have a query that returns current disk space usage on each registered instance. I can run the query successfully from CMS and get the results I'm ...
- 309
0
votes
1
answer
469
views
Schedule SQL Jobs from Central Management Server
I've set up a Central Management Server on a SQL Server 2016 instance. I'm able to manually run queries against multiple servers, but I want to be able to run automated maintenance scripts.
Is there ...
- 103
0
votes
1
answer
394
views
Unable to view Registered Servers on Central Management Server
I have a MS-SQL instance configured to be a CMS for 50+ instances of MS-SQL Server 2008-2017. When checking the msdb.dbo.sysmanagement_shared_registered_servers_internal I see a list of the servers ...
1
vote
1
answer
509
views
SQL Server - Unable to Enlist Target to SQL Server Agent (MSX) after Collation Change
After changing the Collation of SQL Server to Latin1_General_CI_AI from QL_Latin1_General_CP1_CI_AS I'm trying to add the server back to MSX as target in CMS but Enlisting is failing. One thing I ...
- 195
1
vote
1
answer
83
views
Ola Scripts installation in "Multi-server" Jobs Folder [closed]
I am installing the Ola scripts on a Central Management Server for multi-server administration. How can I get the jobs to show up under "Multi-server" Jobs Folder instead of the "Local Jobs" folder?
- 11
0
votes
1
answer
188
views
Gathering sp_BlitzFirst data into a centralized database [duplicate]
I have setup a Central Management Server and registered all of my SQL servers (prods and devs). I want to save the results of sp_BlitzFirst from running it against all of my servers to a centralized ...
- 45
1
vote
1
answer
816
views
How to list Extended Properties for all databases
I am currently working on task where we use Central Management Server and Server Group for easy access to our SQL server boxes.
I am trying to query the extended properties for all databases on ...
- 115
0
votes
2
answers
649
views
What firewall ports should be opened for central management server
Within my environment we currently have a MSSQL installation which serves as the Master in a Multiserver Agent environment (MSX). Due to this server being within a tightly controlled network the ...
- 283
1
vote
2
answers
78
views
Registered Servers without the ability to connect to Multiple Servers at once
I'd like to provide a "directory" of all the different SQL Servers we have our different DEV, TEST, PROD environments by setting it up in Central Management Servers.
Another DBA said to be cautious ...
- 125
1
vote
2
answers
92
views
Is it possible to run query against only part of the Central Managed Servers?
For example, I want to run this query to get the status of some servers having high availability group set up:
SELECT s.role_desc, g.name, r.replica_server_name , GETDATE() as checkTime
FROM ...
- 31
2
votes
1
answer
179
views
Central Management Server - Query against host server
I like the idea of setting up a Central Management Server to run queries against other servers that I have to maintain. I'd like to be able to run one query to get information from all servers, like ...
- 33
2
votes
1
answer
2k
views
How to populate central management server registration from a list
I need to delete users from all the instances in my domain.
In order to do that, I want to create a CMS group and execute queries on it.
Problem is I have more than 150 servers to add. How can I bulk ...
- 345
0
votes
1
answer
29
views
update table with replace string1 string2
I need replace string1 to string2 in column big tabe.
My table weight 1.7GB. For update I will be use:
UPDATE table SET column = REPLACE( column, 'search', 'replace' ) ;
So, I have several questions:...
Pavel Gromov
3
votes
1
answer
1k
views
Can I use SQL Server Authentication for Central Management Servers?
I have set up a Central Management Server in SQL Server. When I try to add a new Registered Server to this Central Management Server, I only have the options to use Windows Authentication or Active ...
- 133
1
vote
1
answer
3k
views
List the CMS SQL Server Instances using Powershell
I need to run a set of Powershell commands from my Central Management Server onto all of the SQL Server Instances that it contains. Can someone let me know how this could be achieved?
I need to loop ...
- 111
3
votes
0
answers
657
views
SQL Server Management Studio creates 18456 login error using registered server group
I have been using "Local Server Groups" registration for quite a while now with SQL Server 2008 Management Studio.
I have created a group with 5 registered servers. They are registered using ...
- 4,717
1
vote
1
answer
344
views
Does anyone know where the SQL Server Connection Properties are stored?
When you add a Server Instance to Central Management Servers or even your local instance of SSMS, if you click on the connection properties tab you can select the default database you'd like to ...
- 11
8
votes
1
answer
157
views
Case Sensitive Ordering within the CMS Server List
I've got to believe there's a reason for this, but for the life of me I cannot figure it out. It looks like ordering of Server Names within any Central Management Server is Case SENSITIVE, regardless ...
- 9,242
3
votes
2
answers
86
views
CMS (Central Manamenent Server) help/guidance?
We have just recently come across the Central Management Server feature in SQL2008.
We currently have a number of environments and think that this feature could really help us in running multiple ...
- 31
4
votes
3
answers
3k
views
Collect data with central management servers and keep them in local table
I have 50 servers. I manage them from a central management server on SQL Server 2014 Enterprise Edition. I don't use repository.
I could open a query and get result for each one at the same time. But ...
- 103
1
vote
1
answer
885
views
How many database users/admins should be created for a website?
I have created a CMS website that only had one database connection for both the admin area and the user area and it only had one user("root") with no password, and now I want to upload it, but I am ...
- 125
-1
votes
1
answer
793
views
Querying multiple servers via CMS: When does [Server Name] become 'real'?
Maybe a silly question. I set up an CMS earlier this year, which has improved my work day immeasurably. Servers are split into appropriate groups, but occasionally I only need to target some servers ...
- 37.6k
2
votes
1
answer
1k
views
How do I add versioning to this CMS I'm designing?
As you can see, my CMS consists of several widgets (for example an Articles widget), of which you can create several instances, which I call blocks, and those blocks are linked to pages and the ...
- 121
2
votes
2
answers
1k
views
Multi-server administration - force poll on target fails, server needs to be registered locally without FQDN
I'm experimenting with multi=server administration on my SQL lab. Running SQL 2012 Enterprise SP1 on Windows 2008 R2 on a domain.
I've created a domain account for my sql server agent user on each ...
- 203
5
votes
2
answers
511
views
Central Management Server - can it manage itself?
I'm setting up a SQL Server 2012 lab on Windows 2008 R2 which has 5 SQL Servers. I registered SQL1 as my central management server and created a group under this server that includes all the servers ...
- 203
0
votes
3
answers
483
views
SQL Server Facets: "Linked Server" exist but "Registered Server" doesn't - why?
I'd like to monitor multiple SQL Server instances.
Something similar to what is described in "Monitor Your SQL Server Linked Server Configurations with Policy Based Management" but instead of using "...
6
votes
2
answers
6k
views
What's the point in SQL Server Central Management Server?
Online MSDN article "Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio)" (SQL Server 2012) states:
"Note
Before you execute the following procedures, ...
- 1,382
1
vote
1
answer
6k
views
Planning simple CMS: How much relation?
I'm currently in the process of designing the database for a rather minimalistic Content Management System I'm working on alone. It should allow scalability while remaining speed, especially on boxes ...
- 111
7
votes
2
answers
860
views
Login failure error log when querying Central Management Server group
I am querying a group under a Central Management Server, which results in the following being logged to the SQL Server Error Log.
Error: 18456, Severity: 14, State: 38. Login failed for user
'domain\...
- 1,030
2
votes
1
answer
2k
views
Execute Stored Procedure against CMS from within a SQL Server Agent job
Is it possible to execute a stored procedure (or any T-SQL) against a Central Management Server (CMS) via a SQL Agent job?
The idea here is to exercise the CMS to hit all the registered instances ...
- 2,679
3
votes
2
answers
691
views
Using Central Management Server to provide convincing performance statistics
Central Management Server
The company I work offers dedicated VMware hosted servers to its Windows clients. Among those Windows clients, there are essentially 60 single instance SQL Servers ranging ...
- 9,600