SQL Server 2016 SP1 CU2 AlwaysOn AG Secondary always disconnected 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:


dm_hadr_availability_group_states:

dm_hadr_availability_replica_states:

dm_hadr_database_replica_states



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?
CREATE AVAILABILITY GROUP [AG_SQL]
FOR 
--DATABASE SetupAG


If this is what you're using (copied from your question) then I would expect it to say disconnected. You've created an empty availability group (no databases, or database only on the primary) so there is nothing to send or receive other than some metadata about the AG.

The fact that it works when you choose automatic seeding means you're adding a database into the AG, it's now no longer empty and should show connected (if it truly is connected).


  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'


This is because there is no database on the secondary server, which completely falls inline with what you've posted. The "database" shows up under the Availability Groups folder on the secondary because that's the metadata of the Availability Group, not because the database is on the secondary.

The reason it "works" with automatic seeding is because automatic seeding creates the database and seeds it on the secondary for you versus what you're doing now which is creating the AG but not preparing the database on the secondary. Thus, no database on secondary.


  Is this a bug in SQL Server 2016 SP1 Standard CU2 or is there something wrong with my T-SQL statement?


Neither, it's a process issue where the secondary wasn't prepared properly.