SQL Server 2022 AlwaysOn New Features including Availability Group Details

catalogueHow to create an availability group?How to use including availability groups?3. Metadata synchronization verification3

The disaster recovery function of SQL Server has always been weaker than that of Oracle and MySQL, unable to automatically synchronize metadata (users, login names, permissions, SQL proxy jobs, linked servers), resulting in manual synchronization of metadata in master and msdb before switching between mirror libraries or AlwaysOn. Until the release of the 2022 version on November 16, 2022, the functionality of including availability groups was added to AlwaysOn, which solved the long-standing issue of; Unable to automatically synchronize metadata&# quo; The issue of.

Including availability groups is a new feature released by AlwaysOn availability groups in SQL Server 2022, which supports:

• Manage metadata objects (users, logins, permissions, SQL agent jobs, etc.) at the availability group level and instance level.
• The dedicated availability group includes system databases such as master and msdb.

How to create an availability group?

Include availability groups in a regular failover cluster (which can be workgroups or domains)Building a good foundationWhen creating an availability group, select" Contained" Or&quo; Including”, Other operations are no different from the consistency group in the previous version.

You can also use the command line to create a consistency group, adding the CONTAINED option in WITH

CREATEAAVAILABILITYGROUPMROBOTOWith (Automated_BACKUP_PREFERENCE=SECONDARY, FAILURE-CONDITION_LEVEL=3, HEALTH_CHECK_TIMEOUT=600000, CUSTER_TYPE=WSFC, CONTAINED -- including consistency groups) FORDATABASETESTREPLICAON '2022 NODE01'With (ENDPOINT_URL='TCP:/2022 NODE01:5022', AVAILABILITY-MODE=SYNC HRONOUT-COMMIT, FAILOVER-MODE=AUTOMATIC, BackUP-PRIORITY=30, SECONDARY_ROLE (ALLOW_CONNECTIONS=NO,READ_ONLY_ROUTING_URL='TCP://2022-NODE01:1433'),PRIMARY_ ROLE(ALLOW_CONNECTIONS=READ_WRITE,READ_ONLY_ROUTING_LIST=('2022-NODE01')),SESSION_ TIMEOUT=10,		SEEDING_ MODE=AUTOMATIC),'2022-NODE02'WITH(ENDPOINT_URL='TCP://2022-NODE02:5022',AVAILABILITY_MODE=SYNCHRONOUS_COMMIT,FAILOVER_MODE=AUTOMATIC,BACKUP_PRIORITY=30,SECONDARY_ROLE(ALLOW_CONNECTIONS=NO,READ_ONLY_ROUTING_URL='TCP://2022-NODE02:1433'),PRIMARY_ ROLE(ALLOW_CONNECTIONS=READ_WRITE,READ_ONLY_ROUTING_LIST=('2022-NODE01')),SESSION_ TIMEOUT=10,		SEEDING_ MODE=AUTOMATIC); GOALTERAVAILABILITYGROUPMRROBOTOADDLISTENER'MRROBOTO_ LSNR'(WITHIP(('192.168.1.128'),('255.255.255.0')),PORT=1433); GO

After creating a consistency group, two databases will be automatically created for the AG: [AGNAME_master], [AGNAME_msdb], and a listener will also be created, which looks like there are two more databases than a regular consistency group: AGNAME_ Master and AGNAME_ msdb

How to use including availability groups?

It should be noted that including availability groups has been improved at the metadata synchronization level, which is the improvement we mentioned earlier at the master and msdb levels. Therefore, when using availability groups for maintenance of login names and jobs,Need to use listener IP to connect to the containing availability groupInstead of using the IP address of the server where the primary instance is located. If you use the IP address of the server where the primary instance is located to connect to the availability group to create a login, it will only be saved on the local database instance and will not be automatically synchronized to the included availability group.

3. Metadata synchronization verification

3.1 Users, Login Names, and Permissions

1. Connect to the primary node using an availability group listener, regardless of switching to AG01_ Master or master, actually both use AG01_ Master database

C:UsersAdministrator> Sqlcmd-H192.168.1.128-Usa-Y30 Password: 1> select@@servername; 2> Go --------2022-NODE01 (1 line affected) 1> useAG01_ master2> Go has changed the database context to 'master'. 1> SELECTDB_ ID()AS[DatabaseID],DB_ NAME()AS[DatabaseName]2> GoDatabaseIDDatabaseName -1 master (1 row affected) 1> usemaster2> Go has changed the database context to 'master'. 1> SELECTDB_ ID()AS[DatabaseID],DB_ NAME()AS[DatabaseName]2> GoDatabaseIDDatabaseName -1 master (1 row affected)

2. Login name for creating the test database

1> CREATELOGIN[test_admin]WITHPASSWORD=N'test.123',DEFAULT_ DATABASE=[test],CHECK_ EXPIRATION=OFF,CHECK_ POLICY=OFF2> GO1> USE[test]2> GO has changed the database context to 'test'. 1> CREATEUSER[test_admin]FORLOGIN[test_admin]2> GO1> USE[test]2> GO has changed the database context to 'test'. 1> ALTERROLE[db_owner]ADDMEMBER[test_admin]2> GO1> selectname,dbname,sid,createdatefromsys.sysloginswhereloginname='test_ admin'2> gonamedbnamesidcreatedate-----------------------------------------------------------------------------------------------------------------test_ admintest0x8CEFB4D480A8E54F97C86ADF9E6934FD2022-10-1814:40:02.913

3. Connect to the auxiliary node and check if the metadata in the consistency group is synchronized (it is recommended to use the SSMS tool to query, sqlcmd needs to use the - Q parameter to write the statement in advance)

• Using SSMS Tools

• Using sqlcmd with - Q parameter

C:UsersAdministrator> sqlcmd-S192.168.1.128-Usa-dAG01_ Master-Y30-KReadOnly Q "select @ @ servername; select name, dbname, sid, createdeatefrom AG01_master. sys. sysloginswhereloginname='test_admin '" Password: --------2022-NODE02 (1 line affected) namedbnamesidcreatedate_ Admintest0x8CEFB4D480A8E54F97C86ADF9E6934FD2022-10-1814:40:02.913 (1 row affected)

You can also use test_ The admin user logs in directly to the secondary node to verify whether the metadata has been synchronized to the secondary node.

C:UsersAdministrator> sqlcmd-S192.168.1.128-Utest_ Admin test Y30 Kreadonly password: 1> select@@servername2> Go --------2022 NODE02 (1 line affected) 1> select*fromFoo2> GoBar --------1 (1 row affected) 1> insertintoFoovalues(2)2> Go message 3906, level 16, status 2, server 2022-NODE02, row 1 cannot update database 'test' because the database is read-only.

Metadata: Users, login names, and permissions are synchronized normally

3.2 Linked servers

1. Created a linked server in the main node containing the availability group using the SSMS tool

2. Query the linked server status of the auxiliary node, and from the view, it can be seen that the linked server has been synchronized and is available

C:UsersAdministrator> sqlcmd-S192.168.1.128-Usa-Y30-dAG01_ Master KReadOnly Q "select @ @ servername; select name, data_sourcefrom AG01_master. sys. serverswhereis_linked=1; SELECTBarFROM [NODE02 FROM125]. test. dbo. Foo" Password: --------2022 NODE02 (1 line affected) namedata_ Source -------- NODE02-FROM125192.168.1.126 (1 line affected) Bar --------1 (1 line affected)

3Using SSMS Tools

C:UsersAdministrator> Sqlcmd S192.168.1.126 Usa Y30-Q "AlteraVAILABILITYGROUP [AG01] FAILOVER" Password: Enter password

Metadata: Link server is synchronized normally

3.3 SQL Agent Job

Change the proxy services of two nodes to automatic start mode, create jobs on the primary node, and verify whether the jobs are automatically synchronized to the secondary node

1. Create a job for the main node, and send it to the msdb of the test database every 10 seconds_ Insert the current server name and timestamp into the test table.

2. Auxiliary node checks job synchronization: The auxiliary node automatically synchronizes the job, with the same job ID but its origin_ Server (initial server) is the host name of the node where it is located

3. Test whether the main node fails over to the auxiliary node and verify whether the job can be executed normally. If it can be executed normally, msdb_ The server column of the test table will be changed to the node host name after failover: this will automatically change from 2022 NODE01 to 2022 NODE02

4. After switching the AlwaysOn primary node, the job will only run on the primary node and will not be executed on the secondary node.

Metadata: SQL agent job is synchronized normally, and after switching, the job is only executed on the main node

4 Includes availability group summary

1. For availability groups, all their metadata is stored in the availability group name_ Master and availability group names_ In these two databases, msdb.

2. For database instances, the availability group name containing the availability group_ Master and availability group names_ These two databases, msdb, are ordinary user databases that can be read and written, but it is not recommended to store user production data;

3. To connect to an availability group, you must use a listener connection that includes the availability group, and the other connection parameters are the same as in the previous version.

This design solves the metadata object synchronization problem that SQL Server has long been criticized for. Enable users, logins, linked servers, and jobs to synchronize between the primary and secondary nodes in the consistency group.

This is an article about SQL; Server  2022  That's all for the article on AlwaysOn's new features, which includes an introduction to availability groups. More related SQL  Server  2022  AlwaysOn content, please search for previous articles of Script Home or continue browsing related articles below. We hope everyone can support Script Home more in the future!