Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

An instance of SQL Server that is designated as a Central Management Server maintains server groups that contain the connection information for one or more instances of SQL Server. Transact-SQL statements and Policy-Based Management policies can be executed at the same time against server groups. You can also view the SQL Server log files on instances that are managed through a Central Management Server. Versions of SQL Server that are earlier than SQL Server 2008 cannot be designated as a Central Management Server.

33 questions
8
votes
1 answer

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 the collation of the server acting as the CMS,…
John Eisbrener
  • 9,547
  • 6
  • 31
  • 65
7
votes
2 answers

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\account'. Reason: Failed to open the explicitly…
jsauni
  • 1,070
  • 1
  • 11
  • 15
6
votes
2 answers

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, create a Central Management Server and server groups. For…
Fulproof
  • 1,392
  • 2
  • 26
  • 36
5
votes
2 answers

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 and the CMS itself. I'm a bit confused since the…
Paweł Czopowik
  • 203
  • 3
  • 8
4
votes
3 answers

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 I want to do it in an automated way, like a job,…
Ebrahim
  • 103
  • 1
  • 2
  • 9
3
votes
1 answer

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 Directory Integrated Authentication. Is there a way…
Mikeyg36
  • 133
  • 4
3
votes
0 answers

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 SQL authentication with the same login. That login has the…
Magier
  • 4,827
  • 8
  • 48
  • 91
3
votes
2 answers

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 instances queries. But before making the move to…
Mbuyig
  • 31
  • 1
3
votes
2 answers

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 in versions from 2000 to 2008. Among them you will…
user4659
2
votes
1 answer

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 containers on those pages. The actual data for a block…
Evert
  • 121
  • 3
2
votes
2 answers

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 server including the master. I was able to create…
Paweł Czopowik
  • 203
  • 3
  • 8
2
votes
1 answer

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 forgot to do before SQL Collation change was…
Ali
  • 345
  • 3
  • 17
2
votes
1 answer

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 log sizes, backup history, index fragmentation,…
Matt J
  • 33
  • 4
2
votes
1 answer

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 add servers to the group and not do it manually?
sql_girl
  • 345
  • 3
  • 8
  • 18
2
votes
1 answer

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 with a query (or action), in an unattended (non-SSMS,…
Eric Higgins
  • 2,689
  • 1
  • 19
  • 25
1
2 3