2

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_group_states

  • dm_hadr_availability_replica_states: dm_hadr_availability_replica_states

  • dm_hadr_database_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?

  • 2
    Can you not use automatic seeding option and initialize secondary before hand and see if this works ? – Shanky Sep 8 '17 at 7:08
  • @shanky: without the automatic seeding option it works – Frederik Vanderhaegen Sep 8 '17 at 7:17
  • @shanky: very strange behavior, I've deleted the AG without 'automatic seeding' and re-created it with 'automatic seeding' => now it works perfect. – Frederik Vanderhaegen Sep 8 '17 at 7:24
  • I was not quite sure but I have faced this issue and informed to MS as well. They did not gave much attention though – Shanky Sep 8 '17 at 7:53
  • Thank you for updating the question with more information. could you should the output of sys.dm_hadr_database_replica_states but run it on the secondary along with an output of sys.databases where database_id > 4? Again, it looks like there is no database on the secondary replica. – Sean Gallardy Sep 12 '17 at 16:49
2
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.

  • even when I add a database to the AG it stays disconnected. It works when I first create the AG without 'automatic seeding' and then re-create it with 'automatic seeding' – Frederik Vanderhaegen Sep 11 '17 at 6:15
  • @FrederikVanderhaegen Please add to your question the output of the following DMVs when it isn't working: sys.dm_hadr_availability_group_states , sys.dm_hadr_availability_replica_states , sys.dm_hadr_database_replica_states . – Sean Gallardy Sep 11 '17 at 15:32
  • I've updated my question – Frederik Vanderhaegen Sep 12 '17 at 14:19
  • It only works with automatic seeding when I first execute the same statement without automatic seeding. I then need to remove everything of the created AG, next I re-execute the same statement with automatic seeding and everything works. Executing the statement first with automatic seeding doesn't work. Or do I misunderstand your answer? – Frederik Vanderhaegen Sep 13 '17 at 14:43
  • You can't create the same AG, so either you execute the create AG with automatic seeding and give the replicas the proper permissions to the AG to create or you don't. @FrederikVanderhaegen – Sean Gallardy Sep 13 '17 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.