go backarticles

Articles of SQLschool.gr Team

How to setup a Clusterless SQL Server 2017 Availablity Group

Kyriakos Petsalaris

Το Always On availability groups το γνωρίσαμε πρώτη φορά με την κυκλοφορία του SQL Server 2012(11.X).

Σε όλους μας είναι λίγο πολύ γνωστό ότι το συγκεκριμένο feature με την συντομογραφία (AG) είναι μία λύση υψήλης διαθεσιμότητας και Disaster Recovery που μπορούμε να έχουμε σε ένα enterprise περιβάλλον.Θα λέγαμε οτι ήρθε σαν μία πολύ καλή εναλλακτική λύση του deprecated εδώ και πολλά χρόνια ‘Database Mirroring’ με την διαφόρα οτι ηταν αναγκαία και μέχρι τον SQL 2016 η ύπαρξη του ‘Windows Server Failover Clustering (WSFC)

Δεν θα επεκταθούμε περισσότερο σε αυτό μιας και οι αναφορές στο πώς υλοποιέιται μία τέτοια λύση ειναι γνωστή και έχει αναλυθεί στο παρελθόν με λεπτομέρεια στο sqlshool.gr

Στην συνέχεια ο SQL Server 2014 και ο 2016 ήρθε με πολλες βελτιωμένες λειτουργίες στο AG οπως ο μέγιστος αριθμό των node που μπορεί να υποστηρίξει, το Azure Virtual Machine Replica , το hybrid Availability Group για τον 2014 και αντίστοιχα για τον 2016 το Round-Robin load balancing σε readable secondaries ,το direct seeding σε νέες replica βάσεις, το AG domain independence και πολλές ακόμα αλλαγές που δεν είναι ο σκοπός του άρθρου να αναλύσουμε και να επεκταθούμε.

Τον Απρίλιο του 17 ο SQL Server 2017 άλλαξε τα δεδομένα σε αυτά που γνωρίζαμε μέχρι τότε με ακόμα περισσότερα Functionalities και βελτιώσεις στο κομμάτι AG όπως το Minimum Replica Commit,το Windows-Linux cross-OS migrations και το read-scale support without a cluster ή την ύπαρξη AG χωρίς το προαπαιτούμενο το να έχεις εγκατεστημένο Windows Server Failover Clustering (WSFC).

Read-Scale Availability Group

Το read-scale Availability Group δημιουργήθηκε κυρίως για να εξυπηρετήσει την ανάγκη του να έχουμε στο περιβάλλον μας AG με πολλαπλά replica nodes είτε on-premises είτε σε απομακρυσμένα σημεία (local ή geo replication) με σκοπό να αποφορτίσουν τον Primary Server από βαριές εργασίες που τρέχουν οι εφαρμογές όπως είναι το Reporting την εκτέλεση διαφορών εργαλείων όπως τα Analytics Tools R,python,Spark .

Σε καμιά περίπτωση το read-scale AG δεν πρέπει να την δείτε σαν μια Hight Availability λύση, αλλά σαν μια λύση που μπορεί να γίνει spread η πληροφορία σας πολύ γρήγορα από replica σε replica node με τον μηχανισμό round-robin.Στο read-scale AG επίσης ,ελλείψει του cluster ρυθμίζουμε τον Listener με την IP του Primary node μιας και δεν υπάρχει η δυνατότητα του Automatic Failover όπως γνωρίζαμε παρά μόνο η επιλογή του manual Failover without data loss ή Force failover με την πιθανότητα απώλειας δεδομένων.

Επίσης στην περίπτωση του Failover θα πρέπει ο Listener να ρυθμιστεί εκ νέου στο νέο Primary Node.

Για το παράδειγμά μας έχω ετοιμάσει δύο SQL instance node τον SQL01 και τον SQL02 σε περιβάλλον με DC κ SQL Server 2017 εγκατεστημένο με μία database η οποία είναι υποχρεωτικά σε Full Recovery Mode.

Step-1

Στο step-1 ενεργοποιούμε μέσα από τον SQL Server Configuration Manager το AG feature και στα δύο Node .

Step-2

Στην συνέχεια και στο step-2 δημιουργούμε το Availability Group με την CREATE AVAILABILITY GROUP.Θα παρατηρήσετε ότι το CLUSTER_TYPE είναι none .Το DB_FAILOVER είναι και αυτό OFF όπως και η παράμετρος FAILOVER_MODE είναι Manual εφόσον δεν υπάρχει εγκατεστημένος κάποιος cluster για να κάνει το automatic Fail-Over.Θα κάνω εδώ μια αναφορά για την παράμετρο REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT (image-1) που έρχεται σαν καινούρια λειτουργία (και θα μείνει) .Ουσιαστικά εδώ δηλώνουμε τον ελάχιστο αριθμό των secondary replica node που πρέπει να γίνει transmit to t-log buffer (log block) πριν γίνει flush με την διαδικασία commit στον δίσκο (Write-Ahead-Logging (WAL)).To SEEDING_MODE είναι AUTOMATIC για την direct δημιουργία της βάσης στον SQL02 node.

Step-3

Με το script στο step-3 δημιουργούμε τα certificates για τα endpoint μας με την αφού πρώτα δημιουργήσουμε το master key στην master database και με την CREATE ENDPOINT.

Step-4

Στο επόμενο step-4 δημιουργούμε το trust relationship μεταξύ των δύο Node δημιουργώντας ένα νέο Login και user για το κάθε Node εκατέρωθεν.Tα Login και στα δύο Node πρέπει να έχουν το CONNECT permission και ορίζεται όπως θα δείτε στο Script με το GRANT CONNECT ON

Step-5

Στο step-5 δημιουργούμε τον (dummy) Listener και του δίνουμε ένα όνομα δηλώνοντας την IP του Primary Node .Η ύπαρξη του Listener είναι σημαντική και μόνο αν θέλουμε να ενεργοποιήσουμε το Read-Only routing .

Step-6

Στο τελευταίο script και το step-6 δημιουργούμε τα read-only routing όπως θα κάναμε και με την παρουσία του cluster αν τον είχαμε εγκατεστημένο στο σύστημά μας.

Step-7

Με το script-7 μπορείτε να τσεκάρετε το stαte του AG και να πάρετε χρήσιμες πληροφορίες και για τα δύο Node. To clusterless AG setup έχει ολοκληρωθεί.

Να τονίσω πάλι εδώ ότι δεν είναι μία High Availability λύση αλλά είναι μία επιλογή που αν χρησιμοποιηθεί για τον σκοπό που δημιουργήθηκε θα σας λύσει πραγματικά τα χέρια

Steps


Image-1 - Step 1
image

Image-2 - Step 2
image

CODE #1

Step-2
CREATE AVAILABILITY GROUP [AG_CLUSTERLESS]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE,
CLUSTER_TYPE = NONE,
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0)
FOR DATABASE [LabDatabase]
REPLICA ON N'SQL01\SQL01' WITH (ENDPOINT_URL = N'TCP://SQL01.IMATIC.local:5022', FAILOVER_MODE = MANUAL, 
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)), N'SQL02\SQL02' WITH (ENDPOINT_URL = N'TCP://SQL02.IMATIC.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)); GO Step-3 -- ==================================== -- Execute the following code on SQL01 -- ==================================== USE master GO -- Create a database master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passw0rd1!' GO -- Create a new certificate CREATE CERTIFICATE SQL_Certificate_SQL01_Private WITH SUBJECT = 'AG_CLUSTERLESS_Certificate_Private - SQL01', START_DATE = '20160101' GO -- Backup the public key of the certificate to the filesystem BACKUP CERTIFICATE SQLBAG_Certificate_Node1_Private TO FILE = 'c:\temp\AG_CLUSTERLESS_Certificate_SQL01_Public.cert' GO -- Create an endpoint for the Availability Group CREATE ENDPOINT AG_CLUSTERLESS_Endpoint STATE = STARTED AS TCP ( LISTENER_PORT = 5022 ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE SQLBAG_Certificate_SQL01_Private, ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES ) GO -- ==================================== -- Execute the following code on SQL02 -- ==================================== USE master GO -- Create a database master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passw0rd1!' GO -- Create a new certificate CREATE CERTIFICATE SQL_Certificate_SQL02_Private WITH SUBJECT = 'AG_CLUSTERLESS_Certificate_Private - SQL02', START_DATE = '20160101' GO -- Backup the public key of the certificate to the filesystem BACKUP CERTIFICATE SQLBAG_Certificate_Node1_Private TO FILE = 'c:\temp\AG_CLUSTERLESS_Certificate_SQL02_Public.cert' GO -- Create an endpoint for the Availability Group CREATE ENDPOINT AG_CLUSTERLESS_Endpoint STATE = STARTED AS TCP ( LISTENER_PORT = 5022 ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE SQLBAG_Certificate_SQL02_Private, ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES ) GO Step-4 -- ==================================== -- Execute the following code on SQL01 -- ==================================== -- Create login for the SQL02 node CREATE LOGIN sql02Login WITH PASSWORD = 'passw0d11!' GO -- Create user for the login CREATE USER sql02User FOR LOGIN SQL02Login GO -- Import the public key portion of the certificate from SQL02 CREATE CERTIFICATE SQL_Certificate_SQL02_Private AUTHORIZATION SQL02User FROM FILE = 'c:\temp\AG_CLUSTERLESS_Certificate_SQL02_Public.cert' GO -- Grant the CONNECT permission to the login GRANT CONNECT ON ENDPOINT:: AG_CLUSTERLESS_Endpoint TO SQL02Login GO -- ==================================== -- Execute the following code on SQL02 -- ==================================== -- Create login for the other node CREATE LOGIN SQL01Login WITH PASSWORD = ' passw0d11!' GO -- Create user for the login CREATE USER SQL01User FOR LOGIN Node1Login GO -- Import the public key portion of the certificate from the other node CREATE CERTIFICATE SQLBAG_Certificate_SQL01_Public AUTHORIZATION SQL01User FROM FILE = 'c:\temp\ AG_CLUSTERLESS_Certificate_SQL01_Public.cert' GO -- Grant the CONNECT permission to the login GRANT CONNECT ON ENDPOINT:: AG_CLUSTERLESS_Endpoint TO SQL01Login GO Step-5 ALTER AVAILABILITY GROUP [AG_CLUSTERLESS] ADD LISTENER N'PrimaryListener' ( WITH IP ((N'192.168.1.7', N'255.255.255.0') ) , PORT=1433); GO Step-6 USE [master] GO ALTER AVAILABILITY GROUP [AG_CLUSTERLESS] MODIFY REPLICA ON N'SQL01\SQL01' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'sql02\sql02:1433')) GO ALTER AVAILABILITY GROUP [AG_CLUSTERLESS] MODIFY REPLICA ON N'SQL01\SQL01' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL01\SQL01',N'SQL02\SQL02'))) GO USE [master] GO ALTER AVAILABILITY GROUP [AG_CLUSTERLESS] MODIFY REPLICA ON N'SQL02\SQL02' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'sql01\sql01:1433')) GO ALTER AVAILABILITY GROUP [AG_CLUSTERLESS] MODIFY REPLICA ON N'SQL02\SQL02' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL02\SQL02',N'SQL01\SQL01'))) GO Step-7 SELECT ar.replica_server_name, adc.database_name, ag.name AS ag_name, drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant, drs.synchronization_health_desc, drs.recovery_lsn, drs.truncation_lsn, drs.last_sent_lsn, drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time FROM sys.dm_hadr_database_replica_states AS drs INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id ORDER BY ag.name, ar.replica_server_name, adc.database_name;

Kyriakos Petsalaris


Kyriakos Petsalaris

Kyriakos Petsalaris

Kyriakos is a Multifaceted Information Technology professional with a background in Information Technology Management. An experienced database and systems administrator, who also establishes clear objectives for team members and enforces deadlines to keep projects on track. Well versed in personnel training and supporting servers,Azure based Systems and Virtual Enviroments. He can do a bit of everything, but he has a passion for SQL Server and love troubling resolving live site incidents as well engineering continuous improvements to avoid repeat incidents. He is also skilled with writing, performance-tuning and debugging T-SQL, and developing automated and scalable maintenance/monitoring procedures to ensure optimal uptime across multiple systems. He has 20+ years experience as a Systems Engineer with specific expertise in SQL Server (from SQL 2005 to 2019). He works with both on-premise and Azure-based, customer-facing services including several Microsoft’s Products and have experience collaborating remotely with global teams in Bahrein,South Africa, Egypt and England.

  

Relative Articles

Comments

user-gravatar

On 28 Apr 2020 @ 12:45 PM Athos K. MCP,MCSA wrote:

I just arrived at this article through SQLschool.gr Search. This is one of the best articles on commenting. I enjoyed it a lot. Carry on writing such useful stuff.

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.