articles

Articles of SQLschool.gr Team

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

Antonios Chatzipavlis

Wednesday 16 January 2013

Χθες το βράδυ ένας αγαπητός φίλος και συνάδελφος αντιμετώπιζε ένα πρόβλημα κατά την προσπάθεια του να υλοποιήσει ένα 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

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.