I'm trying to setup an AlwaysOn AG with T-SQL commands but my secondary is always in a disconnected state (when I failover, my old primary becomes disconnected). This is not an firewall/network issue, I'm able to send messages from SQL1 to SQL2 with a TCP-sender/receiver.
The option New Availability Group... in SMSS also generates the same problem. However when I'm creating an AG with New Availability Group Wizard... it works perfect.
This is the T-SQL statement I'm using:
--on primary CREATE AVAILABILITY GROUP [AG_SQL] FOR DATABASE SetupAG REPLICA ON 'sql1' WITH (ENDPOINT_URL = N'TCP://sql1.mydomain.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, -- BACKUP_PRIORITY = 50, -- SECONDARY_ROLE(ALLOW_CONNECTIONS = NO), SEEDING_MODE = AUTOMATIC), N'sql2' WITH (ENDPOINT_URL = N'TCP://sql2.mydomain.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, -- BACKUP_PRIORITY = 50, -- SECONDARY_ROLE(ALLOW_CONNECTIONS = NO), SEEDING_MODE = AUTOMATIC); GO --on secondary ALTER AVAILABILITY GROUP [AG_SQL] JOIN GO ALTER AVAILABILITY GROUP [AG_SQL] GRANT CREATE ANY DATABASE GO
This is the output from different DMV's:
On the secondary dm_hadr_database_replica_states is empty and the database doesn't show up in sys.databases. When I look in SSMS the database is shown in 'Availability Databases'
Is this a bug in SQL Server 2016 SP1 Standard CU2 or is there something wrong with my T-SQL statement?