Shulou(Shulou.com)06/01 Report--

2.3.2 create AG

Preparatory work

1. To update the machine name of each node, you must meet:

15 characters or less.

Is the only one in the network.

You can modify the machine name with the following statement:

Sudo vi / etc/hostname

two。 Configure the resolution of hostname and IP address

The hostname and IP address are usually registered with the DNS server. To further ensure that multiple nodes in the same AG can communicate with each other, we modify the Hosts file at each node using the following command:

Sudo vi / etc/hosts

You can view the ip address with the following command

Sudo ip addr show

Or

Sudo ifconfig

After modification, you can use the ping command to try ping hostname, and you must return the corresponding real IP address, that is, the Hosts file cannot contain corresponding records like hostname and 127.0.0.1.

Enable AG and restart mssql-server

Enable AlwaysOn AG on the SQL Server of all nodes, and then restart the mssql-server service:

Sudo / opt/mssql/bin/mssql-conf set hadr.hadrenabled 1sudo systemctl restart mssql-server

Enable AlwaysOn_health extended event session

Open the session on each node to help diagnose the root cause when troubleshooting an availability group:

ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON); GO

Create users for database mirroring endpoint access

CREATE LOGIN dbm_login WITH PASSWORD ='*; CREATE USER dbm_user FOR LOGIN dbm_login

Create a certificate

The SQL Server service on Linux uses certificates to verify communication between mirror endpoints. Connect to the primary SQL Server instance. The following Transact-SQL script creates the master key and certificate. Then back up the certificate and use the private key to protect the file. Update the script with a strong password.

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='*'; CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';BACKUP CERTIFICATE dbm_certificateTO FILE =' / var/opt/mssql/data/dbm_certificate.cer'WITH PRIVATE KEY (FILE ='/ var/opt/mssql/data/dbm_certificate.pvk',ENCRYPTION BY PASSWORD ='*')

Copy the certificate and private key to the same location on all available copies of the server.

Cd / var/opt/mssql/datascp dbm_certificate.* root@****:/var/opt/mssql/data/

On each target server, grant the mssql user access to these files.

Cd / var/opt/mssql/datachown mssql:mssql dbm_certificate.*

Create a certificate on the secondary server

The following Transact-SQL script creates a master key and certificate based on the backup created on the master SQL Server copy. Update the script with a strong password. The decryption password is the same as the password used to create the .pvk file in the previous steps.

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='*'; CREATE CERTIFICATE dbm_certificateFROM FILE ='/ var/opt/mssql/data/dbm_certificate.cer'WITH PRIVATE KEY (FILE ='/ var/opt/mssql/data/dbm_certificate.pvk',DECRYPTION BY PASSWORD ='*')

Create database mirroring endpoints on all nodes

(optional) can include the IP address LISTENER_IP = (0.0.0.0). The listener IP address must be an IPv4 address. You can also use 0.0.0.0.

If the configured node is a configuration-only copy, the only valid value is ROLE = WITNESS.

For the SQL Server 2017 release, the only authentication method that supports database mirroring endpoints is CERTIFICATE.

CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = *) FOR DATA_MIRRORING (ROLE = ALL,AUTHENTICATION = CERTIFICATE dbm_certificate,ENCRYPTION = REQUIRED ALGORITHM AES); ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;GRANT CONNECT ON ENDPOINT:: [Hadr _ endpoint] TO [dbm_login]

Create an AG on the primary node

Three synchronized copies

CREATE AVAILABILITY GROUP [ag1] WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL) FOR REPLICA ONN''WITH (ENDPOINT_URL = SYNCHRONOUS_COMMIT,FAILOVER_MODE = EXTERNAL,SEEDING_MODE = AUTOMATIC), N''WITH (ENDPOINT_URL = Numbtcp) SYNCHRONOUS_COMMIT,FAILOVER_MODE = EXTERNAL,SEEDING_MODE = AUTOMATIC), N''WITH (ENDPOINT_URL = Numbtcp) SEEDING_MODE = AUTOMATIC) ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE

Two synchronized copies

CREATE AVAILABILITY GROUP [ag1] WITH (CLUSTER_TYPE = EXTERNAL) FOR REPLICA ONN'node1' WITH (ENDPOINT_URL = Naturtcp WITH 5022 ENDPOINT_URL = SYNCHRONOUS_COMMIT,FAILOVER_MODE = EXTERNAL,SEEDING_MODE = AUTOMATIC); ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE

Two synchronous copies and configuration-only copies

CREATE AVAILABILITY GROUP [ag1] WITH (CLUSTER_TYPE = EXTERNAL) FOR REPLICA ONN'' WITH (ENDPOINT_URL = Numbtcp peg); ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE); ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE

Add a secondary copy to the AG

Execute on all secondary copies:

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL); ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE

Add a database to an availability group

Ensure that the databases added to the availability group are in full recovery mode and have valid log backups.

CREATE DATABASE [db1]; ALTER DATABASE [db1] SET RECOVERY FULL;BACKUP DATABASE [db1] TO DISK = northward AVAILABILITY GROUP Varmsql AVAILABILITY GROUP AVAILABILITY GROUP [ag1] ADD DATABASE [db1]

Verify that the database has been created on the secondary server

Execute on each secondary server:

SELECT * FROM sys.databases WHERE name = 'db1';GOSELECT DB_NAME (database_id) AS' database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.