Error: 1418 fails to connect sql 2008 mirror server

Error 1418, fails to connect sql 2008 mirror server:

  • The server network address can not be reached
  • Alter failed
  • Error 1418
  • Insufficient Log Data
  • Error 1478

Start with: In this example we are inside a DOMAIN. We have a domain user called khan_machine

1)      Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2)      khan_machine is local admin in both machines. Also is a sysadmin user in both sqlservers.

3)      khan_machine  is the MSSQLSERVER service account in both machines

4)      Machine A is gonna be the PRINCIPAL, B the MIRROR.

5)      Log on in my machine as khan_machine

6)      Open Sql server Studio. Connect to both machines using windows authenticacion.

7)      Create a New Database in A (you may need to have sysadmin role to khan_machine using sa) create a table, add some data

8)       Backup the new DB (Full backup) with .bak extension

9)       Backup the new DB (Transaction log) with .trn extension

10)    Copy the both files to a location in B machine.

11)     Restore .bak into a new DB using NORECOVERY option

12)     Restore .trn into the previous DB using NORECOVERY option

13)    Go to Machine A, open a new query

14)    Take a look to the endpoints

Run this syntax in QA:

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15)   Delete existing ones (DROP ENDPOINT [NAME])
16)   Create a new endopoint

 

 

 

Run this syntax in QA:
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17)   Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18)   Do steps 13 to 17 in machine B.

19)   Return to machine A. Query.

20)   Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER =’TCP://MACHINENAME:5023′

21)   Repeat step 20 from MACHINE B pointing to MACHINE A

22)   Note: Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23)   Lets test them: Add some new data in Machine A database.

24)   Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25)   Refresh the 2 databases in the UI you should see the new status and roles changed.

26)   Open Machine B databse, the new data should be there.

To establish any mirroring session (with or without certificates) you must use FQDN (Fully Qualified Domain Names) machine name.domain.com.  ** Before going on, be sure that you can reach the 3 machines with a single PING. Also, is a good practice to ensure that you can reach a telnet call over the FQDN + the port you plan to use (5022 in this example)** 

FIX: Over the post above. When establishing a mirroring session using machine accounts (not certificates, so you are inside a DOMAIN)  ensure your 3 mssqlservices are running under NETWORK SERVICE account

 

För more information have a look here

 

Khan – MCTS

Advertisements
%d bloggers like this: