go backsqlschool blogs list

Dealing with error 1418 when trying to start a Database Mirroring session

by Antonios Chatzipavlis

Χθες το βράδυ ένας αγαπητός φίλος και συνάδελφος αντιμετώπιζε ένα πρόβλημα κατά την προσπάθεια του να υλοποιήσει ένα database mirroring και μάλιστα στο azure (αν και αυτό δεν έχει καμιά σχέση με το πρόβλημα του απλά το αναφέρω για τονίσω ότι όλο και περισσότερος κόσμος πηγαίνει σε αυτό).

Ο συνάδελφος κατά την προσπάθεια του να ξεκινήσει το database mirroring και ενώ είχε κάνει όλα τα προκαταρκτικά βήματα σωστά (μπορείτε να βρείτε αυτά σε παλαιότερα μου posts [1],[2] και webcast) λάμβανε το εξής μήνυμα:

Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://xxxx:5022" can not be reached or does not exist. Check the network address name and reissue the command.

Κλασικό μήνυμα όταν προσπαθούμε να υλοποιήσουμε database mirroring χωρίς οι εμπλεκόμενοι SQL servers να είναι σε domain και τα services τους να ξεκινάνε με domain account καθώς θα πρέπει να γίνει δημιουργία του database mirroring με την χρήση certificates.

Αυτό ακριβώς είχε και ο αγαπητός συνάδελφος. Είχε δύο SQL Servers δεν υπήρχε domain και τα services αυτών ξεκινούσαν με το local service account.

Αν και αμέσως δεν τον οδήγησα στην παρακάτω λύση που είναι και η μοναδική λύση για την περίπτωση που θέλω να κάνω database mirroring χωρίς domain καθώς δεν είχα έτοιμο σε ένα script όλη την υλοποίηση, προσπαθήσαμε να το λύσουμε με άλλους τρόπους οι οποίοι φυσικά απέτυχαν.

Έφτιαξα με μεσοβέζικο τρόπο ένα script το οποίο και του έδωσα για να κάνει σωστά την δουλειά του την οποία και έκανε, αλλά αποφάσισα αυτό να γίνει σωστά. 'Ετσι μετά από μερικά revisions είναι το παρακάτω φυσικά με τα απαραίτητα σχόλια και οδηγίες.

 

-- =========================================================================
-- ON PRINCIPAL SERVER
-- =========================================================================
-- On Principal Server (master database)
-- Create the database master key
-- Change the Password with something else more stronger

USE MASTER;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';
GO

-- On Principal Server 
-- Create a Certificate

USE MASTER;
GO
CREATE CERTIFICATE PrincipalServerCertificate
    WITH SUBJECT = 'Principal Server Certificate';
GO

-- On Principal Server 
-- Create a database mirroring endpoint using the certificate.

CREATE ENDPOINT DBMirrorEndpoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING
    (
        AUTHENTICATION = CERTIFICATE PrincipalServerCertificate,
        ENCRYPTION = REQUIRED ALGORITHM RC4,
        ROLE = ALL
    );
GO

-- On Principal Server 
-- Backup the certificate and copy it to the Mirror Server

USE MASTER ;
GO 
BACKUP CERTIFICATE PrincipalServerCertificate     
    TO FILE = 'C:\TEMP\PrincipalServerCertificate.cer';
GO

-- =========================================================================
-- ON MIRROR SERVER
-- =========================================================================

-- On Mirror Server **ATTENTION**
-- Create the database master key
-- Change the Password with something else more stronger

USE MASTER;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';
GO

-- On Mirror Server **ATTENTION**
-- Create a Certificate

USE MASTER 
GO 
CREATE CERTIFICATE MirrorServerCertificate 
    WITH SUBJECT = 'Mirror Server Certificate';
GO

-- On Mirror Server **ATTENTION**
-- Create a database mirroring endpoint using the certificate.
-- If you are using named instances in the same server you must change
-- the LISTENER_PORT to something else

CREATE ENDPOINT DBMirrorEndpoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING
    (
        AUTHENTICATION = CERTIFICATE MirrorServerCertificate,
        ENCRYPTION = REQUIRED ALGORITHM RC4,
        ROLE = ALL
    );
GO

-- On Mirror Server **ATTENTION**
-- Backup the certificate and copy it to the Principal Server

USE MASTER 
GO 
BACKUP CERTIFICATE MirrorServerCertificate 
    TO FILE = 'C:\TEMP\MirrorServerCertificate.cer';
GO

-- =========================================================================
-- ON WITNESS SERVER
-- =========================================================================

-- On Witness Server **ATTENTION** (if exists)
-- Create the database master key
-- Change the Password with something else more stronger

USE MASTER;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';
GO

-- On Witness Server **ATTENTION**
-- Create a Certificate

USE MASTER 
GO 
CREATE CERTIFICATE WitnessServerCertificate 
    WITH SUBJECT = 'Witness Server Certificate';
GO

-- On Witness Server **ATTENTION**
-- Create a database mirroring endpoint using the certificate.
-- If you are using named instances in the same server you must change
-- the LISTENER_PORT to something else

CREATE ENDPOINT DBMirrorEndpoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING
    (
        AUTHENTICATION = CERTIFICATE WitnessServerCertificate,
        ENCRYPTION = REQUIRED ALGORITHM RC4,
        ROLE = ALL
    );
GO

-- On Witness Server **ATTENTION**
-- Backup the certificate and copy it to the Principal AND Mirror Server

USE MASTER 
GO 
BACKUP CERTIFICATE WitnessServerCertificate 
    TO FILE = 'C:\TEMP\WitnessServerCertificate.cer';
GO


-- =========================================================================
-- ON PRINCIPAL SERVER
-- =========================================================================

-- DON'T FORGET YOU MUST HAVE COPY HERE THE CERTIFICATES 
-- FROM MIRROR AND WITNESS SERVERS

-- Create SQL logins and users for Mirror and Witness Servers
-- and associate the certificates with them
-- Change Passwords to something else

USE MASTER;
GO
CREATE LOGIN MirrorServerLogin WITH PASSWORD = 'Password';
GO
USE MASTER;
GO
CREATE LOGIN WitnessServerLogin WITH PASSWORD = 'Password';
GO
USE MASTER;
GO
CREATE USER MirrorServerUser FOR LOGIN MirrorServerLogin;
GO
USE MASTER;
GO
CREATE USER WitnessServerUser FOR LOGIN WitnessServerLogin;
GO
USE MASTER;
GO
CREATE CERTIFICATE MirrorServerCertificate
    AUTHORIZATION MirrorServerUser
    FROM FILE = 'C:\TEMP\MirrorServerCertificate.cer';
GO
USE MASTER;
GO
CREATE CERTIFICATE WitnessServerCertificate
    AUTHORIZATION WitnessServerUser
    FROM FILE = 'C:\TEMP\WitnessServerCertificate.cer';
GO
-- Grant CONNECT permission on the login 
-- for the remote mirroring endpoint.
USE MASTER
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndpoint TO MirrorServerLogin; 
GO
USE MASTER;
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndpoint TO WitnessServeLogin; 
GO


-- =========================================================================
-- ON MIRROR SERVER
-- =========================================================================

-- DON'T FORGET YOU MUST HAVE COPY HERE THE CERTIFICATES 
-- FROM PRINCIPAL AND WITNESS SERVERS

-- Create SQL logins and users for Principal and Witness Servers
-- and associate the certificates with them
-- Change Passwords to something else

USE MASTER;
GO
CREATE LOGIN PrincipalServerLogin WITH PASSWORD = 'Password';
GO
USE MASTER;
GO
CREATE LOGIN WitnessServerLogin WITH PASSWORD = 'Password';
GO
USE MASTER;
GO
CREATE USER PrincipalServerUser FOR LOGIN PrincipalServerLogin;
GO
USE MASTER;
GO
CREATE USER WitnessServerUser FOR LOGIN WitnessServerLogin;
GO
USE MASTER;
GO
CREATE CERTIFICATE PrincipalServerCertificate
    AUTHORIZATION PrincipalServerUser
    FROM FILE = 'C:\TEMP\PrincipalServerCertificate.cer';
GO
USE MASTER;
GO
CREATE CERTIFICATE WitnessServerCertificate
    AUTHORIZATION WitnessServerUser
    FROM FILE = 'C:\TEMP\WitnessServerCertificate.cer';
GO
-- Grant CONNECT permission on the login 
-- for the remote mirroring endpoint.
USE MASTER
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndpoint TO PrincipalServerLogin; 
GO
USE MASTER;
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndpoint TO WitnessServeLogin; 
GO


-- =========================================================================
-- ON WITNESS SERVER
-- =========================================================================

-- DON'T FORGET YOU MUST HAVE COPY HERE THE CERTIFICATES 
-- FROM PRINCIPAL AND MIRROR SERVERS

-- Create SQL logins and users for Principal and Witness Servers
-- and associate the certificates with them
-- Change Passwords to something else

USE MASTER;
GO
CREATE LOGIN PrincipalServerLogin WITH PASSWORD = 'Password';
GO
USE MASTER;
GO
CREATE LOGIN MirrorServerLogin WITH PASSWORD = 'Password';
GO
USE MASTER;
GO
CREATE USER PrincipalServerUser FOR LOGIN PrincipalServerLogin;
GO
USE MASTER;
GO
CREATE USER MirrorServerUser FOR LOGIN MirrorServerLogin;
GO
USE MASTER;
GO
CREATE CERTIFICATE PrincipalServerCertificate
    AUTHORIZATION PrincipalServerUser
    FROM FILE = 'C:\TEMP\PrincipalServerCertificate.cer';
GO
USE MASTER;
GO
CREATE CERTIFICATE MirrorCertificate
    AUTHORIZATION MirrorServerUser
    FROM FILE = 'C:\TEMP\MirrorServerCertificate.cer';
GO
-- Grant CONNECT permission on the login 
-- for the remote mirroring endpoint.
USE MASTER
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndpoint TO PrincipalServerLogin; 
GO
USE MASTER;
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndpoint TO MirrorServeLogin; 
GO


-- =========================================================================
-- ON PRINCIPAL SERVER
-- =========================================================================

-- Take backups of database and copy them to mirror server
USE MASTER;
GO
BACKUP DATABASE DemoDB TO DISK = 'C:\Backups\DemoDB_Full.bak';
GO
BACKUP LOG DemoDB TO DISK = 'C:\Backups\DemoDB_Log.bak';
GO

-- =========================================================================
-- ON MIRROR SERVER
-- =========================================================================

-- Restore with NORECOVERY the backups from Principal Server

USE MASTER;
GO
RESTORE DATABASE DemoDB
    FROM DISK = 'C:\Backups\DemoDB_Full.bak'
    WITH NORECOVERY
GO
RESTORE LOG DemoDB
    FROM DISK = 'C:\Backups\DemoDB_Log.bak'
    WITH NORECOVERY
GO


-- =========================================================================
-- ON MIRROR SERVER
-- =========================================================================

-- Configuring the Mirroring Partners 
ALTER DATABASE DemoDB
    SET PARTNER = 'TCP://YourPrincipalServerName:5022';
GO

-- =========================================================================
-- ON PRINCIPAL SERVER
-- =========================================================================

-- Configuring the Mirroring Partners 
ALTER DATABASE DemoDB 
    SET PARTNER = 'TCP://YourMirrorServerName:5022'; 
GO  
                
ALTER DATABASE DemoDB 
    SET WITNESS = 'TCP://YourWitnessServerName:5022' ;
GO



Ημερομηνία: 16 January 2013 10:42
Share it:

Αφήστε το σχόλιο σας - Leave your comment

Τα σχόλια έχουν κλείσει.
Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.


newsletter subscription

Εάν επιθυμείτε να λαμβάνετε ενημέρωση από εμάς, δώστε μας το e-mail σας.
PASS chapter logo
Official Professional Association for SQL Server (PASS) chapter for Greece
Join to PASS