Always On Availabaility Groups and Database Scoped Parameters

System Setup

We use

  • Windows Server 2016 with latest updates
  • SQL Server 2016 SP1 CU2
  • an Active Directory-Detached Cluster
  • an Always On Availabaility Group called DemoAG1 containing just database justdave1
  • An Availability Group Listener called DemoAGL1 on statics ip address 192.168.96.230 port 1445

    • DMV Checks during Failover

    We check the database scoped parameters on the both servers and from the listener

    select * from sys.database_scoped_configurations

    All come back with

    We then make the database scoped configuration parameters difference between primary and secondary

    USE justdave1;
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1 ;  
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=4 ;

    We check when directly connected to 192.168.96.200 machine WIN-AG1 (primary)
    


    We check when directly connected to 192.168.96.201 machine WIN-AG2 (secondary)
    


    We check when directly connected to the 192.168.96.230 (listener) 
    



    We then failover
    

    ALTER AVAILABILITY GROUP DemoAG1 FAILOVER;

    

    We check when directly connected to 192.168.96.200 machine WIN-AG1 (new secondary)
    


    We check when directly connected to 192.168.96.201 machine WIN-AG2 (new primary)
    


    We check when directly connected to the 192.168.96.230 (listener)
    


    Functionality Checks during Failover
    

    We then change the database scoped parameter LEGACY_CARDINALITY_ESTIMATION
    

    USE justdave1;
ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION=ON;

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY
SET LEGACY_CARDINALITY_ESTIMATION=OFF;

    

    We then setup a test for which cardinality estimator is being used
    

    CREATE TABLE job
(
state VARCHAR(20),
city VARCHAR(20)
)

INSERT INTO job VALUES ('Illinois','Chicago')
GO 6000

INSERT INTO job VALUES ('Illinois','Springfield')
GO 3000

INSERT INTO job VALUES ('Hawaii','Honolulu')
GO 1000

SELECT state,city,count(*) FROM job GROUP BY state,city;
SELECT state,count(*) FROM job GROUP BY state;

CREATE INDEX job_ixState ON job(State);
CREATE INDEX job_ixCity ON job(City);

UPDATE STATISTICS job WITH FULLSCAN;

DBCC SHOW_STATISTICS ('job','job_ixState');
DBCC SHOW_STATISTICS ('job','job_ixCity');

    

    We then run a query on the secondary WIN-AG1 which will use the OLD cardiniality estimator.
    

    We see the estimated rows is 5629.1
    


    We then run a query on the primary WIN-AG2 which will use the NEW cardiniality estimator.
    

    We see the estimated rows is 5400
    


    We try from the Always On Availability Group Listener and get estimated rows is 5400
    


    We then failover
    

    ALTER AVAILABILITY GROUP DemoAG1 FAILOVER;

    

    We then run a query on the primary WIN-AG1 which will use the NEW cardiniality estimator.
    

    We see the estimated rows is 5400
    


    We then run a query on the secondary WIN-AG2 which will use the OLD cardiniality estimator.
    

    We see the estimated rows is 5629.1
    


    We try from the Always On Availability Group Listener and get estimated rows is 5400
    


    Conclusion
    

    When we failover if the database scoped parameters are different between the primary and the secondary then the database scoped parameters move with the failover.
    

    If you connect directly to the servers the database scoped parameters will change underneath you!!
    

    If you connect to the listener then the database scoped parameters do not change underneath you.