Add Replica from Node 1 Can’t Find Node 2 in Always ON AG Lab

availability-groupssql serversql-server-2017

I am trying to set up an Always ON availability group lab on virtual machines, with Windows Server 2016 and MS SQL Server 2017.

While creating a new Availability Group on node 1 by the wizard, I meet difficult that the system can not find the node 2 in the step of validation.

The errors show as:

  1. 'The following required directories do not exist on replica WINN02\SQL_AG_02 : C:\Program Files\Microsoft SQL
    Server\MSSQL14.SQL_AG_01\MSSQL\DATA.
    (Microsoft.SqlServer.Management.HadrModel)'

and

  1. 'The primary server 'WINN01\SQL_AG_01' cannot write to '\Winn01\nzfagsharefolder\BackupLocDb_cbbf8fb2-f6e8-4aa9-8995-675364932027.bak'.
    (Microsoft.SqlServer.Management.HadrModel)

Additional information

Backup failed for Server 'WINN01\SQL_AG_01'.
(Microsoft.SqlServer.SmoExtended)

An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot open backup device
'\Winn01\nzfagsharefolder\BackupLocDb_cbbf8fb2-f6e8-4aa9-8995-675364932027.bak'.
Operating system error 5(Access is denied.). BACKUP DATABASE is
terminating abnormally. (Microsoft SQL Server, Error: 3201) For help,
click:
http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3076&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476'.

Attached the error details:

enter image description here

The problem seems about connection, so I made some connection test.

  1. When I try to connect instance node 2 via computer name, in node1, it shows connection error. Attached the screenprint:

    enter image description here.

    If I change it the computer name of node 2 into IP address, it can be connected easily.

  2. If I ping the address and computer name in cmd of node 1, both of them are successful. Which means the connecting condition is good. Attached the screenprint:

    enter image description here

I am stuck. Please help me, thank you!

Best Answer

There are a couple of different questions here.

Question #1

  1. 'The following required directories do not exist on replica WINN02\SQL_AG_02 : C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_AG_01\MSSQL\DATA.

While I would not put any database files on the C:\ drive, the error is saying that the

C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_AG_01\MSSQL\DATA

folder does not exist on the WINN02 VM, resulting in the database files not being found.

Resolving he issue

You should move these database files first to a non c:\ disk and then check that both folder structures are the same with

SELECT DB_NAME(database_id), physical_name FROM SYS.MASTER_FILES

If your C:\ drive is full, your VM's are done for. Resulting in a crash and possible corruption.

Question #2

Cannot open backup device '\Winn01\nzfagsharefolder\BackupLocDb_cbbf8fb2-f6e8-4aa9-8995-675364932027.bak'. Operating system error 5(Access is denied.). BACKUP DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3201)

This error happens due to the ag creation wizard trying to take full & log backups of the databases to later restore on the secondary instance.

Resolving he issue

You should look into granting the service accounts for both instances the FULL control permission on the \Winn01\nzfagsharefolder\location. And make sure that your user account has enough rights (sysadmin).

Question #3

The problem seems about connection. So I made some connection test. 1. When I try to connect instance node2 via computer name, in node1, it shows connection error, attached the screenprint

The connection issues from node1 to node2 are a different problem.

There are a few things to check, but if you can connect via IP you could look into DNS name resolving.

Resolving he issue

Using the FQDN to connect, looking into the tcp settings settings in the configuration manager such as the port used (E.G. changing dynamic ports to static ports).

Another solution could be enabling the browser service.

Related Question