bajohnson


When I configured database mirroring between two servers in separate DMZ regions, I get the following error on the principle server when I execute the ALTER DATABASE xxxxxxxx SET PARTNER = '****':

The server network address %%% can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

Ports have been opened on both machines and I can TELNET both without any problems. I have included the steps that I used.

DATABASE MIRRORING TEST CONFIGURATION

SQL Server 2005 STD

Principal: AA-AAA-AA01
Mirror: BB-BBB-BB07


A. Create certificates for outbound connection on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.

1. In the master database, create a database Master Key.

M:\DBADM\MIRRORING\CREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO


2. In the master database, create an encrypted certificate on the server instance.

M:\DBADM\MIRRORING\CREATE_AA01_MIRRORING_CERT
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
WITH SUBJECT = 'AA-AAA-AA01 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO


3. Create an endpoint for the server instance using its certificate.

M:\DBADM\MIRRORING\CREATE_AA01_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO


4. Back up the certificate to a file and securely copy it to the other system or systems.

M:\DBADM\MIRRORING\CREATE_AA01_CERT_BKUP
BACKUP CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\AA_AAA_AA01_MIRRORING_CERT_BKUP.cer';
GO


B. Create certificates for outbound connection on mirror server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.

1. In the master database, create a database Master Key.

M:\DBADM\MIRRORING\CREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO


2. In the master database, create an encrypted certificate on the server instance.

M:\DBADM\MIRRORING\CREATE_BB07_MIRRORING_CERT
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
WITH SUBJECT = 'BB-BBB-BB07 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO


3. Create an endpoint for the server instance using its certificate.

M:\DBADM\MIRRORING\CREATE_BB07_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = PARNTER
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO


4. Back up the certificate to a file and securely copy it to the other system or systems.

M:\DBADM\MIRRORING\CREATE_BB07_CERT_BKUP
BACKUP CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\BB_BBB_BB07_MIRRORING_CERT_BKUP.cer';
GO


C. Configure server instances inbound mirroring connections on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.


1. Create a login for other system.

M:\DBADM\MIRRORING\CREATE_BB07_LOGIN
USE master;
CREATE LOGIN BB07_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@#';
GO

2. Create a user for that login.

M:\DBADM\MIRRORING\CREATE_BB07_USER
USE master;
CREATE USER BB07_MIRROR_ADMIN FOR LOGIN BB07_MIRROR_ADMIN;
GO

3. Obtain the certificate for the mirroring endpoint of the other server instance.

4. Associate the certificate with the user created in step 2.

M:\DBADM\MIRRORING\CREATE_BB07_USER_CERT_LINK
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
AUTHORIZATION BB07_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\BB_BBB_BB07_MIRRORING_CERT_BKUP.cer'
GO


5. Grant CONNECT permission on the login for that mirroring endpoint.

M:\DBADM\MIRRORING\GRANT_BB07_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO BB07_MIRROR_ADMIN;
GO


D. Configure server instances inbound mirroring connections on master server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.


1. Create a login for other system.

M:\DBADM\MIRRORING\CREATE_AA01_LOGIN
USE master;
CREATE LOGIN AA01_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@#';
GO

2. Create a user for that login.

M:\DBADM\MIRRORING\CREATE_AA01_USER
USE master;
CREATE USER AA01_MIRROR_ADMIN FOR LOGIN AA01_MIRROR_ADMIN;
GO

3. Obtain the certificate for the mirroring endpoint of the other server instance.

4. Associate the certificate with the user created in step 2.

M:\DBADM\MIRRORING\CREATE_AA01_USER_CERT_LINK
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
AUTHORIZATION AA01_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\AA_AAA_AA01_MIRRORING_CERT_BKUP.cer'
GO


5. Grant CONNECT permission on the login for that mirroring endpoint.

M:\DBADM\MIRRORING\GRANT_AA01_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO AA01_MIRROR_ADMIN;
GO


E. Back up the Principal Database and copy backup files to mirror server.


F. Restore the Principal Database on the standby database server (use NORECOVERY).


G. Configure the mirroring partners. Refer to http://msdn2.microsoft.com/en-us/library/ms191140.aspx.


1. On the mirror server instance on BB-BBB-BB07, set the server instance on AA-AAA-AA01 as the partner (making it the initial principal server instance).

M:\DBADM\MIRRORING\ACTIVATE_MIRROR_FOR_AA01
--At BB-BBB-BB07, set server instance on AA-AAA-AA01 as partner (principal server):
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://AA-AAA-AA01.mycompany.com:5999';
GO

2. On the principal server instance on AA-AAA-AA01, set the server instance on BB-BBB-BB07 as the partner (making it the initial mirror server instance).

M:\DBADM\MIRRORING\ACTIVATE_MIRROR_FOR_BB07

--At AA-AAA-AA01, set server instance on BB-BBB-BB07 as partner (mirror server).
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://BB-BBB-BB07.mycompany.com:5999';
GO

3. Execute the following SELECT on both servers to review mirroring information.

SELECT * FROM SYS.DATABASE_MIRRORING




Re: Database Mirroring Error

Warren Lee


Can ping both IP addresses and DNS names in both servers If no, please try to add record in both networks DNS servers or add both SQL servers IP addresses in local hosts files (e.g. C:\Windows\System32\Drivers\Etc\hosts). Moreover, is it opened all SQL required ports in firewall Detail information please refer to "How to: Configure a Firewall for SQL Server Access http://msdn2.microsoft.com/en-us/library/ms175043.aspx". Active Directory ports are required too.





Re: Database Mirroring Error

bajohnson

Thanks for the info. During the meanwhile, I set up transactional replication.






Re: Database Mirroring Error

bajohnson

By the way, the servers have been added to the HOST files on each server. The ports appeared to be opened. I did a netstats -adn in the command prompt. I may stick with replication since I am on a tight deadline. I was hoping to get database mirroring to work in a short time since it appears to be easier in switching back and forth for failover scenerios.

Thanks again.





Re: Database Mirroring Error


Re: Database Mirroring Error

Warren Lee

Did you try to install two SQL servers in same network before






Re: Database Mirroring Error

bajohnson

I wanted to first try going from server to server within the network but I could not find two server with similar configurations. So, I ended up testing database mirroring for the first time between two servers in their own DMZ space.

Thanks.





Re: Database Mirroring Error

Warren Lee

I suggest you setup two testing servers in Virtual Environment, such as MS Virtual and VMware Server. It's free download and use. It easy to learn and test Database Mirroring.

Database Mirroring in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Database mirroring setup in SQL Server 2005

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004,00.html bucket=ETA&topic=301326






Re: Database Mirroring Error

bajohnson

Thanks. I will review the posted website.





Re: Database Mirroring Error

bajohnson

I finally got pass the 1418 error message. The problem was with the way the server name was configured and the HOSTS file on the C drive.