Não é possível adicionar testemunha à minha configuração de espelhamento

Question

Estou tentando configurar o espelhamento com uma testemunha usando meus 3 servidores dedicados executando o SQL Server 2008 R2. Eu configurei tudo conforme descrito neste post do blog

O espelhamento parece funcionar bem entre o servidor principal e o secundário. Mas quando eu executo o último comando para adicionar a testemunha

alter database citec_vc_prod set witness = 'TCP://witness_address:witness_port';

Recebo o seguinte erro após alguns segundos:

Msg 1456, Level 16, State 3, Line 1 The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness_address:witness_port'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

Além disso, o log de eventos no principal mostra o seguinte erro:

The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness_address:witness_port'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

Eu já desliguei o firewall da testemunha e fui telnettado com sucesso do principal para o ponto de extremidade de espelhamento do servidor testemunha para ter certeza de que não há nenhum problema de conectividade. Ainda assim, recebo este erro.

Minha configuração é a seguinte:

Principal: SQL Server 2008 R2 Standard 10.50.1617
Mirror: SQL Server 2008 R2 Standard 10.50.1617
Witness: SQL Server 2008 R2 Express 10.50.2500

Cada um dos servidores está em um grupo de trabalho diferente.

Por favor me ajude gurus do servidor SQL, você é minha única esperança!

Edit: Aqui está um log completo dos comandos T-SQL que eu usei para configurar o espelhamento. Quando a primeira tentativa falhou com o problema descrito acima, apaguei tudo e comecei do zero. O mesmo problema voltou a ocorrer.

-- PRINCIPAL
create master key encryption by password = 'mypassword';
GO
create certificate "server1.ourdomain.com_cert" with subject = 'server1.ourdomain.com certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO 

Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate "server1.ourdomain.com_cert", encryption = disabled,  role = all);
GO
Backup certificate "server1.ourdomain.com_cert" to file = 'f:\cert\server1.ourdomain.com_cert.cer';
GO 

-- MIRROR
create master key encryption by password = 'mypassword';
GO
create certificate "server2.ourdomain.com_cert" with subject = 'server2.ourdomain.com certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate "server2.ourdomain.com_cert", encryption = disabled,  role = all);
GO
Backup certificate "server2.ourdomain.com_cert" to file = 'f:\cert\server2.ourdomain.com_cert.cer';
GO 

-- WITNESS
create master key encryption by password = 'mypassword';
GO
create certificate "witness.ourdomain.com_cert" with subject = 'witness.ourdomain.com certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate "witness.ourdomain.com_cert", encryption = disabled,role = witness);
GO
Backup certificate "witness.ourdomain.com_cert" to file = 'd:\cert\witness.ourdomain.com_cert.cer';
GO 

-- PRINCIPAL again
create login "server2.ourdomain.com_login" with PASSWORD = 'mypassword';
GO
create user "server2.ourdomain.com_user" from login "server2.ourdomain.com_login";
GO
select * from sys.certificates
Create certificate "server2.ourdomain.com_cert"
Authorization "server2.ourdomain.com_user"
From file = 'f:\cert\server2.ourdomain.com_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [server2.ourdomain.com_login];
GO
------
create login "witness.ourdomain.com_login" with PASSWORD = 'mypassword';
GO
create user "witness.ourdomain.com_user" from login "witness.ourdomain.com_login";
GO
Create certificate "witness.ourdomain.com_cert"
Authorization "witness.ourdomain.com_user"
From file = 'f:\cert\witness.ourdomain.com_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [witness.ourdomain.com_login];
GO 

-- MIRROR again
create login "server1.ourdomain.com_login" with PASSWORD = 'mypassword';
GO
create user "server1.ourdomain.com_user" from login "server1.ourdomain.com_login";
GO
Create certificate "server1.ourdomain.com_cert"
Authorization "server1.ourdomain.com_user"
From file = 'f:\cert\server1.ourdomain.com_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [server1.ourdomain.com_login];
GO
-------
create login "witness.ourdomain.com_login" with PASSWORD = 'mypassword';
GO
create user "witness.ourdomain.com_user" from login "witness.ourdomain.com_login";
GO
Create certificate "witness.ourdomain.com_cert"
Authorization "witness.ourdomain.com_user"
From file = 'f:\cert\witness.ourdomain.com_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [witness.ourdomain.com_login];
GO 

-- WITNESS again
create login "server1.ourdomain.com_login" with PASSWORD = 'mypassword';
GO
create user "server1.ourdomain.com_user" from login "server1.ourdomain.com_login";
GO
Create certificate "server1.ourdomain.com_cert"
Authorization "server1.ourdomain.com_user"
From file = 'd:\cert\server1.ourdomain.com_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [server1.ourdomain.com_login];
GO
-------
create login "server2.ourdomain.com_login" with PASSWORD = 'mypassword';
GO
create user "server2.ourdomain.com_user" from login "server2.ourdomain.com_login";
GO
Create certificate "server2.ourdomain.com_cert"
Authorization "server2.ourdomain.com_user"
From file = 'd:\cert\server2.ourdomain.com_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [server2.ourdomain.com_login];
GO 

-- MIRROR again
alter database MyDBName set partner OFF
alter database MyDBName set partner = 'TCP://server1.ourdomain.com:5022';
GO 

-- PRINCIPAL again
alter database MyDBName set partner OFF
alter database MyDBName set partner = 'TCP://server2.ourdomain.com:5022';
GO
alter database MyDBName set witness OFF
alter database MyDBName set witness = 'TCP://witness.ourdomain.com:5022';
GO

Editar 2: Conforme solicitado pelo Remus, anexei um profiler às minhas três instâncias do servidor ao tentar adicionar a testemunha. Eu procurei:

Database Mirroring State Change
Broker:Connection Event

Eu não consegui monitorar "Login do espelhamento do banco de dados de auditoria", pois não consegui encontrar isso na "Lista de eventos" do criador de perfil. Não está na seção "Auditoria de Segurança", é?

De qualquer forma, eu não pude monitorar nenhum desses eventos na testemunha ou no espelho. Eu tentei, mas simplesmente não houve eventos. Houve alguns desses eventos no diretor:

  Database Mirroring Connection                 32  2011-12-09 20:04:07.983 1       39796   2 - Connected   c6b6054d-56c0-4589-922a-29a40fdd9d96            1   0X01        tcp://<MyWitnessIP>:5022    principal.mytopleveldomain.com,3809                                             
    Audit Database Mirroring Login                  30  2011-12-09 20:04:08.133 1       39797   1 - Login Success               1   0X01        tcp://<MyWitnessIP>:5022    principal.mytopleveldomain.com,3809                 1   witness.mytopleveldomain.com_login      CERTIFICATE Microsoft Unified Security Protocol Provider    Initiator       
    Database Mirroring State Change DBM: Synchronized Principal without Witness -> DBM: Synchronizing Principal             27  2011-12-09 20:04:27.980 9       39798               2   1   0X01            principal.mytopleveldomain.com,3809         Zeiterfassung-Staging   0   13                          
    Database Mirroring State Change DBM: Synchronizing Principal -> DBM: Synchronized Principal without Witness             27  2011-12-09 20:04:28.237 9       39799               13  1   0X01            principal.mytopleveldomain.com,3809         Zeiterfassung-Staging   0   2                           
    Database Mirroring Connection   An error occurred while receiving data: '64(failed to retrieve text for this error. Reason: 15105)'.                30  2011-12-09 20:05:42.530 1       39803   4 - Closing c6b6054d-56c0-4589-922a-29a40fdd9d96            1   0X01        tcp://<MyWitnessIP>:5022    principal.mytopleveldomain.com,3809                                             
    Database Mirroring Connection                   30  2011-12-09 20:05:42.533 1       39804   5 - Closed  c6b6054d-56c0-4589-922a-29a40fdd9d96            1   0X01        tcp://<MyWitnessIP>:5022    principal.mytopleveldomain.com,3809                                             


Database Mirroring Connection                   32  2011-12-09 20:04:07.983 1       39796   2 - Connected   c6b6054d-56c0-4589-922a-29a40fdd9d96            1   0X01        tcp://85.214.251.151:5022   srv1.logmytime.de,3809                                              
Audit Database Mirroring Login                  30  2011-12-09 20:04:08.133 1       39797   1 - Login Success               1   0X01        tcp://85.214.251.151:5022   srv1.logmytime.de,3809                  1   wtn.logmytime.de_login      CERTIFICATE Microsoft Unified Security Protocol Provider    Initiator       
Database Mirroring State Change DBM: Synchronized Principal without Witness -> DBM: Synchronizing Principal             27  2011-12-09 20:04:27.980 9       39798               2   1   0X01            srv1.logmytime.de,3809          Zeiterfassung-Staging   0   13                          
Database Mirroring State Change DBM: Synchronizing Principal -> DBM: Synchronized Principal without Witness             27  2011-12-09 20:04:28.237 9       39799               13  1   0X01            srv1.logmytime.de,3809          Zeiterfassung-Staging   0   2                           
Database Mirroring Connection   An error occurred while receiving data: '64(failed to retrieve text for this error. Reason: 15105)'.                30  2011-12-09 20:05:42.530 1       39803   4 - Closing c6b6054d-56c0-4589-922a-29a40fdd9d96            1   0X01        tcp://85.214.251.151:5022   srv1.logmytime.de,3809                                              
Database Mirroring Connection                   30  2011-12-09 20:05:42.533 1       39804   5 - Closed  c6b6054d-56c0-4589-922a-29a40fdd9d96            1   0X01        tcp://85.214.251.151:5022   srv1.logmytime.de,3809  

Espelharei com prazer outros eventos também, mas, por favor, deixe-me saber em qual seção de evento eu posso encontrá-los ao ajustar o profiler.

Editar 3 : Eu tive outra tentativa de criar o perfil da testemunha, desta vez com quase todos os tipos de eventos existentes. E finalmente consegui alguns dados:

Trace Start                     2011-12-10 01:24:08.180                                                                                             
ErrorLog    2011-12-10 01:24:40.40 Logon       Database Mirroring Login succeeded for user 'principal.ourdomain.com_login'. Authentication mode: CERTIFICATE.  [CLIENT: <principalip>]
                29  2011-12-10 01:24:40.400 1   28046   1428                    1   0X01        witness.ourdomain.com,3809          master  0       sa                          
EventLog    Database Mirroring Login succeeded for user 'principal.ourdomain.com_login'. Authentication mode: CERTIFICATE.  [CLIENT: <principalip>]             29  2011-12-10 01:24:40.400 1   28046   1429                    1   0X01        witness.ourdomain.com,3809          master  0       sa  

Em seguida, fiz o mesmo no espelho, mas só tenho esses dois eventos sem importância:

ErrorLog    2011-12-10 01:38:02.14 spid29s     Database mirroring is inactive for database 'DatabaseName'. This is an informational message only. No user action is required.
            sa                      29  2011-12-10 01:38:02.143         
EventLog    Database mirroring is inactive for database 'DatabaseName'. This is an informational message only. No user action is required.          sa                      29  2011-12-10 01:38:02.143     0XA20500000A0000000F000000570049004E003600360036004800520054004B003700540032004800000000000000  
ErrorLog    2011-12-10 01:38:22.40 spid29s     Database mirroring is active with database 'DatabaseName' as the mirror copy. This is an informational message only. No user action is required.
            sa                      29  2011-12-10 01:38:22.407         
EventLog    Database mirroring is active with database 'DatabaseName' as the mirror copy. This is an informational message only. No user action is required.            sa                      29  2011-12-10 01:38:22.407     0XA10500000A0000000F000000570049004E003600360036004800520054004B003700540032004800000000000000  

Ainda não vejo nenhuma informação sobre o motivo da adição da testemunha falhar.

    

Answer 1

Você pode anexar o SQL Profiler a todas as três instâncias envolvidas e monitorar esses eventos:

• Login do espelhamento do banco de dados de auditoria
• Alteração de estado do espelhamento de banco de dados
• Broker: evento de conexão

Em seguida, tente novamente estabelecer a sessão de espelhamento. Certifique-se de selecionar todas as colunas ao adicionar os eventos. Comece de um modelo em branco.

2011-12-09 20:04:07.983  Database Mirroring Connection    Connected   
2011-12-09 20:04:08.133  Audit Database Mirroring Login   Login Success
2011-12-09 20:04:27.980  Database Mirroring State Change DBM: 
  Synchronized Principal without Witness -> 
  DBM: Synchronizing Principal             
2011-12-09 20:04:28.237  Database Mirroring State Change DBM: 
  Synchronizing Principal -> 
  DBM: Synchronized Principal without Witness
2011-12-09 20:05:42.530  Database Mirroring Connection   An error occurred... 

Aqui está a explicação para o que acontece:

• às 20:04:07 o principal se conecta com a testemunha
• às 20:04:08 o diretor e a testemunha terminam um handshage bem-sucedido (sucesso de login de auditoria)
• em 20:04:27 o DBM altera o estado de sincronizado sem testemunha para sincronizado
• em 20:04:28 O DBM altera o estado de volta de sincronizado para sincronizado sem testemunha
• às 20:05:42 a conexão com a testemunha expira devido à inatividade e fecha

Esta sequência de eventos indica que a conectividade entre principal e testemunha é funcional. A solicitação para adicionar a testemunha falha e pode haver vários motivos. Eventos semelhantes devem ocorrer no espelho e na testemunha também, não está claro por que você diz que você só poderia capturá-los no diretor.

    

Answer 2

O espelhamento já está declarado e o ESTADO do ponto final da testemunha ESTREITADO? Para verificar: SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
Você também:
1. Criei um endpoint:
CREATE ENDPOINT Endpoint_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 7022)
    PARA DATABASE_MIRRORING (ROLE = WITNESS)
GO

2. você está usando a mesma conta de domínio em todas as instâncias?

3. garantir que as permissões no endpoint estão configuradas para essa conta?
   --Criar um login para as instâncias do servidor parceiro,
   - ambos estão executando como MYDOMAIN \ dbousername:
   USE master;
   GO
   CREATE LOGIN [MYDOMAIN \ dbousername] DO WINDOWS; GO
   --Grant conecte permissões no endpoint para acessar a conta de parceiros | GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring para [MYDOMAIN \ dbousername]; GO
   

Veja aqui para obter mais detalhes.

    

Answer 3

"Witness_address" é um endereço IP ou um nome?

Se é um nome, cada servidor é capaz de resolver o nome de todos os outros servidores? (grupos de trabalho diferentes = sem resolução de nomes = o arquivo DNS ou HOSTS deve ser usado)

    

Answer 4

Eu encontrei o UNBLE configure Witness por um tempo, até que percebi que o certificado que eu usei no Mirror's Endpoint era diferente do & Ponto final das testemunhas.

Depois que usei o mesmo certificado (copiado do Principal para o segundo e terceiro servidores) para os pontos de extremidade do Espelhamento no Principal, no Mirror e no Witness, consegui resolver

    

Answer 5

De acordo com o link ...

To execute a SET WITNESS statement, the database mirroring session must already be started (between the partners), and the STATE of the endpoint of the witness must be set to STARTED.

Por que o seu script tem "alterar o banco de dados MyDBName set partner OFF" logo antes de "set witness"? Isso não destrói o espelhamento?