SQL Server Q&A

Symptoms
Consider the following scenario:You have a database mirroring session in Microsoft SQL Server 2005. The database mirroring session does not have a witness. You set up a witness server. On the principal server and on the witness server, you do not use DNS together with DHCP for name resolution. Instead, you specify that a host file should be used for name resolution. On the principal server instance, you try to set the witness.In this scenario, you receive the following error message:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://ServerName:port’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
Resolution
A database mirroring session must use a fully qualified domain name (FQDN) to resolve the name of the witness server.

Symptoms
Consider the following scenario:You have a database mirroring session in Microsoft SQL Server 2005. The database mirroring session does not have a witness. You set up a witness server. On the principal server and on the witness server, you do not use DNS together with DHCP for name resolution. Instead, you specify that a host file should be used for name resolution. On the principal server instance, you try to set the witness.In this scenario, you receive the following error message:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://ServerName:port’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
Resolution
A database mirroring session must use a fully qualified domain name (FQDN) to resolve the name of the witness server.

Symptoms
Consider the following scenario:You install Microsoft SQL Server 2005 or 2008 on a Windows Server 2003 system. You apply the security template “System Cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing” on that system.
In this scenario, if you try to run the Database mirroring Wizard for SQL Server, you will get the following error message
 SQL Server cannot create the mirroring endpoint, ‘Mirroring’.
Additional information:
Create failed for Endpoint ‘Mirroring’. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The RC4 encryption algorithm is not supported when running in FIPS compliance mode.
(Microsoft SQL Server, Error: 28078)
Note: The “System Cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing” can be enabled in the SSLF security template provided by Windows Server 2003 security guide.
Resolution
To resolve this, you must note the ports that the principal, mirror, and witness are using.  Then you have to manually create the endpoints on each of the instances specifying the appropriate port for the appropriate role.
For  example, if you are configuring your Principal server on port 5022, you can run the following command on the principal server.
Create ENDPOINT Mirroring  
State = Started
as TCP (LISTENER_PORT = 5022) 
–Uses port 5022.You would change this as appropriate for each role (Principal, Mirror, or Witness)
FOR DATABASE_MIRRORING (AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE=ALL)
 
Note: You must repeat this command on each of the servers involved in the mirroring configuration.