AlwaysOn SQL-klusteri ilman domainia (ja jopa DNS:ää)


Aina ei ole sitä Active Directoryä välttis saatavilla, eikä aina niin kamalasti huvittaisi asentaakaan. Standalone -SQL-palvelin kyllä pelittää ihan työryhmäasetuksissakin, mutta joskus olis niin mukava kun sais jotain niitä edistyksellisempiäkin toimintoja ajettua - kuten nyt vaikka AlwaysOn-tyyppistä klusteria, jossa pääasiallisen OLTP-puolen hoitaa yksi primary-palvelin ja sieltä data sitte pussahtelee verkon yli secondary-palvelimiin. On sitte hienoo kun ydinjytky putoaa ykköskonesalin päälle tai zombiet tukkivat salin ilmastoinnin, failoverin jälkeen secondary palvelee toisessa konesalissa kuin vettä vaan.

Kuin ollakaan, sehän nykyään onnistuu, kunhan tarpeeksi modernit versiot ohjelmistoista ovat käytössä. Joten ei muuta kun labraamaan!

Mikä lentää, mikä ei

Kuvaus

Palvelimien asennus ja konfigurointi

HOSTS (koska meillä ei ole DNS:ää vaikka pitäis)

10.0.0.10 SQLMaster.cluster.local
10.0.0.11 SQLSecondary.cluster.local
10.0.0.20 alwayson.cluster.local
10.0.0.21 adventure.cluster.local

Rekisteriasetusten sorvaaminen

Molemmilla noodeilla on ajettava powershell-komento rekisterin politiikan muuttamiseksi (että etäältä yhteyttä ottava Administrator-käyttäjätunnus suostuu toimimaan). Komento täytyy antaa Administrator-powershellissä (start as Administrator)

new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1

Klusterin luominen

Tämän jälkeen voidaan luoda klusteri. Tämä ei onnistu GUI:n avulla (Failover Cluster Manager), vaan se on tehtävä PowerShellistä käsin. FCM:llä on AD-kytköksiä eikä klusterin luonti onnistu kun tunnuksia ei päästä sijoittamaan AD:hen.

new-cluster -name alwayson -Node SQLMaster,SQLSecondary -StaticAddress 10.0.0.20 -NoStorage -AdministrativeAccessPoint DNS

Tässä kohden klusterin pitäisi olla pystyssä ja sen resurssien näkyvissä. Kirjoitettaessa PowerShellissä komento "get-clusterresource" pitäisi komennon palauttaa "Cluster IP Address" ja "Cluster Name", joiden tilan tulisi olla online. Jos ei, jotain meni kivasti pieleen.

Noin vinkkinä, PowerShellissä on sitten komennot "remove-cluster" ja "clear-clusternode", jos pitää aloittaa alusta ja jos vaikkapa toinen noodi on ollut poistuvinaan klusterista mutta sisältää silti joitakin konfiguraatiojäämiä jotka uusintakierroksella estävät klusterin muodostamisen.

SQL:n asennus

Asennetaan SQL Server 2016 Enterprise edition. Ennen asennusta suositellaan asennettavaksi MSVCRT, koska muutoin SQL:n setup saattaa poksahtaa pahasti. Lisätietoa linkistä:

https://support.microsoft.com/en-us/help/3164398/critical-update-for-sql-server-2016-msvcrt-prerequisites

Asennus sinänsä menee kuin mikä hyvänsä standalone-SQL:n asennus. Tärkeää on muistaa valita Mixed Mode Authentication, sillä tulemme käyttämään VAIN SQL-logineita.

Asennuksen valmistuttua täytyy SQL:lle kertoa että saattaapi olla AlwaysOn -toimintoja käytössä. Säätö tehdään SQL Configuration Managerilla - SQL Server Services, valitaan serveri-instanssi, hiiren oikealla "properties".

Kun toimenpiteet on tehty - ja SQL asennettu molemmille noodeille ja tehty samaiset säädöt, voidaan aloittaa varsinainen kantajumppa.

Kantojen luonti/palautus

Palautetaan kanta SQLMaster-koneelle. Allaolevat komennot ajetaan siis ensimmäisellä noodilla. Toissijaiselle noodille kannat palautetaan tällä koneella tehdyistä uusista backup-tiedostoista (tämä selostetaan myöhemmin tekstissä).

RESTORE DATABASE AdventureWorks2016CTP3 FROM DISK = 'C:\Setup\AdventureWorks2016CTP3.bak' WITH RECOVERY
RESTORE DATABASE AdventureWorks2012 FROM DISK = 'C:\Setup\AdventureWorks2012.bak' WITH RECOVERY

Vaihdetaan molempien tietokantojen recovery modeliksi Full Recovery, sillä replikaatio perustuu transaktiologeihin. Simple-moodissa olevia kantoja ei voi replikoida.

ALTER DATABASE [AdventureWorks2016CTP3] SET RECOVERY FULL 
GO
ALTER DATABASE [AdventureWorks2012] SET RECOVERY FULL 
GO

Vaihdetaan kannan omistajaksi sa-tunnus (koska käytämme SQL-logineita).

USE AdventureWorks2016CTP3
GO
EXEC dbo.sp_changedbowner 'sa'
GO
USE AdventureWorks2012
GO
EXEC dbo.sp_changedbowner 'sa'
GO

Endpointit ja sertifikaattien käyttö autentikoinnissa

Luodaan uusi master key SQLMaster-koneella.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'str0ng_p@ssw0rd'
GO

Luodaan uusi sertifikaatti, kesto 10 vuotta.

CREATE CERTIFICATE SQLMaster_prv_certificate
WITH SUBJECT = 'SQLMaster_prv_certificate',
START_DATE = '20170303',
EXPIRY_DATE = '20270303'
GO

Otetaan sertifikaatista backup tiedostoon paikalliselle levylle, että saamme sen myöhemmin siirrettyä toiselle noodille.

BACKUP CERTIFICATE SQLMaster_prv_certificate
TO FILE = 'C:\Setup\SQLMaster_prv_certificate.cert'
GO

Luodaan endpoint käyttäen sertifikaattia autentikaatioon. Endpointin nimeksi tulee "alwayson_Endpoint". Tällä ei ole tekemistä klusterin nimen kanssa, joka sattumalta on sama. Nimi voi olla mikä hyvänsä.

CREATE ENDPOINT alwayson_Endpoint
STATE = STARTED
AS TCP
(LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING
(AUTHENTICATION = CERTIFICATE SQLMaster_prv_certificate,
ROLE = ALL,
ENCRYPTION = REQUIRED ALGORITHM AES)
GO

Tässä vaiheessa siirrytään toiselle koneelle (SQLSecondary) ja tehdään suurin osa asioista samalla tavalla kuin ensimmäisellä noodilla, mutta toisin päin (luodaan sertifikaatit ja listenerit ristiin).

Luodaan uusi master key SQLSecondary-koneella.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'str0ng_p@ssw0rd'
GO

Luodaan uusi sertifikaatti, kesto 10 vuotta.

CREATE CERTIFICATE SQLSecondary_prv_certificate
WITH SUBJECT = 'SQLSecondary_prv_certificate',
START_DATE = '20170303',
EXPIRY_DATE = '20270303'
GO

Otetaan sertifikaatista backup tiedostoon paikalliselle levylle, että saamme sen myöhemmin siirrettyä ensisijaiselle noodille

BACKUP CERTIFICATE SQLSecondary_prv_certificate
TO FILE = 'C:\Setup\SQLSecondary_prv_certificate.cert'
GO

Luodaan endpoint käyttäen tämän koneen sertifikaattia autentikaatioon. Endpointin nimeksi tulee jälleen "alwayson_Endpoint".

CREATE ENDPOINT alwayson_Endpoint
STATE = STARTED
AS TCP
(LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING
(AUTHENTICATION = CERTIFICATE SQLSecondary_prv_certificate,
ROLE = ALL,
ENCRYPTION = REQUIRED ALGORITHM AES)
GO

Ajamalla select * from sys.endpoints voidaan tarkistaa että molemmille koneille on syntynyt oikean näköinen endpoint, jonka tyyppi on "DATABASE_MIRRORING"

Noodien tarvitsemat käyttäjätunnukset

Seuraavaksi luodaan käyttäjätunnuksia niin että noodien SQL:t voivat kirjautua toisiinsa. SQLMasterille luodaan käyttäjätunnus SQLSecondaryä varten ja päinvastoin.

Luodaan SQLMaster-koneessa käyttäjätunnus:

CREATE LOGIN SQLSecondaryAcc WITH PASSWORD = 'str0ng_p@ssw0rd'
GO
CREATE USER SQLSecondaryAcc FOR LOGIN SQLSecondaryAcc
GO

Tässä vaiheessa kopioidaan SQLSecondary-koneelle tallennettu sertfikaatti SQLMasterin levylle, kansioon C:\Setup. Sertifikaatti lisätään autentikaatiota varten. Alla olevat komennot ajetaan siis yhä SQLMaster-koneessa.

CREATE CERTIFICATE SQLSecondary_prv_certificate
AUTHORIZATION SQLSecondaryAcc
FROM FILE = 'C:\Setup\SQLSecondary_prv_certificate.cert'
GO

Annetaan tunnukselle oikeus kytkeytyä endpointtiin.

GRANT CONNECT ON ENDPOINT::alwayson_Endpoint TO SQLSecondaryAcc
GO

Ja sama toisin päin. Palataan SQLSecondary-koneelle, ja tehdään tunnukset SQLMasteria varten.

CREATE LOGIN SQLMasterAcc WITH PASSWORD = 'str0ng_p@ssw0rd'
GO
CREATE USER SQLMasterAcc FOR LOGIN SQLMasterAcc
GO

Ja nyt kopioidaan SQLMaster-koneelle tallennettu sertfikaatti SQLSecondaryn levylle, kansioon C:\Setup. Jonka jälkeen em. sertifikaatti tuodaan SQL:lle.

CREATE CERTIFICATE SQLMaster_prv_certificate
AUTHORIZATION SQLMasterAcc
FROM FILE = 'C:\Setup\SQLMaster_prv_certificate.cert'
GO

Ja kuten toisellakin noodilla, annetaan tunnukselle oikeus kytkeytyä endpointtiin.

GRANT CONNECT ON ENDPOINT::alwayson_Endpoint TO SQLMasterAcc
GO

Kantojen valmistelu

Tässä kohden luodaan uudet varmistukset kannoista. Kannoista ei ole luotuna transaktiologien backuppia, ja vähintään yksi sellainen on oltava ennen kuin Availability Grouppia voidaan muodostaa (niin että kanta on em. AG:n sisässä). Ajetaan siis full backup ja transaktiologien backup SQLMaster-koneella.

BACKUP DATABASE AdventureWorks2016CTP3 TO DISK = 'C:\Setup\AdventureWorks2016.bak'
GO
BACKUP LOG AdventureWorks2016CTP3 TO DISK = 'C:\Setup\AdventureWorks2016.trn'
GO
BACKUP DATABASE AdventureWorks2012 TO DISK = 'C:\Setup\AdventureWorks2012.bak'
GO
BACKUP LOG AdventureWorks2012 TO DISK = 'C:\Setup\AdventureWorks2012.trn'
GO

Tässä vaiheessa luodut kantabackupit kannattaa siirtää SQLSecondary-koneen Setup-hakemistoon odottamaan kohta tapahtuvaa palautusta.

Availability Groupin luominen

Kun backup on tehty, voidaan luoda SQLMaster-koneella Availability Group. Lisäämme AG:hen toistaiseksi vain yhden kannan (AdventureWorks2016CTP3)

CREATE AVAILABILITY GROUP AdventureAOAG
WITH
(	AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
	DB_FAILOVER = OFF,
	DTC_SUPPORT = NONE)
FOR DATABASE [AdventureWorks2016CTP3]
REPLICA ON
'SQLMaster' WITH
(	ENDPOINT_URL = 'TCP://sqlmaster.cluster.local:5022', 
	FAILOVER_MODE = AUTOMATIC, 
	AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	SECONDARY_ROLE
	(ALLOW_CONNECTIONS = READ_ONLY)
),
'SQLSecondary' WITH
(	ENDPOINT_URL = 'TCP://sqlsecondary.cluster.local:5022', 
	FAILOVER_MODE = AUTOMATIC, 
	AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	SECONDARY_ROLE
	(ALLOW_CONNECTIONS = READ_ONLY)
)
GO

Noin huomiona - SECONDARY ROLE (ALLOW_CONNECTIONS = READ_ONLY) tarkoittaa sitä, että replikoitu kanta siellä toissijaisella palvelimella hyväksyy kyselyt jotka erikseen julistavat itsensä read-tilassa (Application-intent: ReadOnly). Management Studio ei tälläistä tee, joten jos kytkeydyt sillä suoraan toissijaiseen kantaan ja yrität katsoa kannan ominaisuuksia (tai ajaa vaikkaba backuppeja secondaryltä), eipä onnistu. Jos haluat toissijaisen kannan luettavaan tilaan sovelluksen aikeista välittämättä, ALLOW_CONNECTIONS = ALL tekee tämän tempun.

Käytökselle on syynsä. Jos ALLOW_CONNECTION = READ_ONLY on päällä, nimenomaan listeneriin saapuvat kyselyt voidaan routata eri koneille sen perusteella aikooko sovellus kirjoittaa ja lukea (ohjataan primary replicalle) vai pelkästään lukea (ohjataan secondary replicalle). Reitityskäytöstä voi muuttaa, mutta se on oma juttunsa. Jos ALLOW_CONNECTION = ALL, täytyy applikaatio ohjata suoraan fyysisesti secondarylle (käyttämällä palvelimen nimeä, ei listenerin nimeä).

Vaihtoehto 2: Luodaan AG automaattisella seedauksella jolloin restore-operaatioita ei tarvitse tehdä - kunhan AG:lle sallitaan noodeissa minkä tahansa kannan luominen. Alla olevassa esimerkissä on käytössä kolmen noodin ympäristö.

CREATE AVAILABILITY GROUP AdventureAOAG
WITH
(	AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
	DB_FAILOVER = OFF,
	DTC_SUPPORT = NONE)
FOR DATABASE [DeleteMe]
REPLICA ON
'Node1' WITH
(	ENDPOINT_URL = 'TCP://Node1.cluster.local:5022', 
	FAILOVER_MODE = AUTOMATIC, 
	AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	SECONDARY_ROLE
	(ALLOW_CONNECTIONS = READ_ONLY),
	SEEDING_MODE = AUTOMATIC
),
'Node2' WITH
(	ENDPOINT_URL = 'TCP://Node2.cluster.local:5022', 
	FAILOVER_MODE = AUTOMATIC, 
	AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	SECONDARY_ROLE
	(ALLOW_CONNECTIONS = READ_ONLY),
	SEEDING_MODE = AUTOMATIC
),
'Node3' WITH
(	ENDPOINT_URL = 'TCP://Node3.cluster.local:5022', 
	FAILOVER_MODE = AUTOMATIC, 
	AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	SECONDARY_ROLE
	(ALLOW_CONNECTIONS = READ_ONLY),
	SEEDING_MODE = AUTOMATIC
)
GO

 

Mutta eteenpäin.

SQLSecondary-koneesta käsin liitymme edellä luotuun Availability Grouppiin. Jos automaattinen seedaus on käytössä, annetaan AG:lle myös luoda kantoja - kaikissa koneissa. Grant-komento pitää antaa siis erikseen myös primäärinoodilla, josko vaikka joskus primary-role siirtyy muualle.

ALTER AVAILABILITY GROUP [AdventureAOAG] JOIN
GO
ALTER AVAILABILITY GROUP [AdventureAOAG] GRANT CREATE ANY DATABASE
GO

Sikäli kun emme käytä automaattista seedausta: Kun muistimme kopioida SQLMasterissa tehdyt kantojen backupit SQLSecondary-koneelle, nyt on aika palauttaa ensimmäinen kanta SQLSecondaryllä. Huomio! Kanta palautetaan WITH NORECOVERY-optiolla, joten se jää ikuiseen palautustilaan. Jos transaktiologeja on useampia, kaikki palautetaan NORECOVERYssä. Tämä siksi että peilaus itse asiassa lähinnä soittelee ensisijaiselta replikalta transaktiologeja jatkuvasti toissijaisiin kantoihin, eikä tämä "palautus" ikinä valmistu.

RESTORE DATABASE AdventureWorks2016CTP3 FROM DISK = 'C:\Setup\AdventureWorks2016.bak' WITH NORECOVERY
GO
RESTORE LOG AdventureWorks2016CTP3 FROM DISK = 'C:\Setup\AdventureWorks2016.trn' WITH NORECOVERY
GO

SQLSecondary-palvelin on jo liitetty Availability Grouppiin mutta juuri palautettua kantaa ei vielä. Joten:

ALTER DATABASE AdventureWorks2016CTP3 SET HADR AVAILABILITY GROUP = AdventureAOAG
GO

Toisen kannan lisääminen jo luotuun Availability Grouppiin

Ensin lisätään kanta Availability Grouppin sillä koneella jossa ensisijainen replika sijaitsee (tässä tapauksessa kyseessä on siis AdventureWorks2012-kanta joka on palautettu vasta SQLMaster-koneelle). Ajamme siis SQLMasterissa komennon

ALTER AVAILABILITY GROUP AdventurAOAG ADD DATABASE AdventureWorks2012;  
GO

Tämän jälkeen siirrymme toissijaiselle noodille (SQLSecondary), ja palautamme siellä kantavarmistuksen transaktiologeineen (kun muistimme kopioida varmistustiedostot SQLMaster-koneelta):

RESTORE DATABASE AdventureWorks2012 FROM DISK = 'C:\Setup\AdventureWorks2012.bak' WITH NORECOVERY
GO
RESTORE LOG AdventureWorks2012 FROM DISK = 'C:\Setup\AdventureWorks2012.trn' WITH NORECOVERY
GO

Kriittistä oli siis NORECOVERYn käyttö. Tämän jälkeen yhä SQLSecondaryllä lisäämme kannan Availability Grouppiin kuten ensimmäisenkin kannan kohdalla tehtiin:

ALTER DATABASE AdventureWorks2012 SET HADR AVAILABILITY GROUP = AdventureAOAG
GO

Listenerin luominen

Jotta Availability Grouppiin päästään järkevästi kiinni yhden nimen takaa, täytyy sille luoda listener - käytännössä siis nimi ja IP-osoite, johon asiakasohjelmistot ottavat yhteyttä. Nimi siirtyy Availability Groupin mukana noodilta toiselle jos palvelu yliheitetään (kuten tavanomaisella Failover Clusterillakin). Luomme "adventure" -nimisen listenerin ja annamme sille osoitteen, aliverkon maskin ja portin. Portti on SQL:n oletusportti.

ALTER AVAILABILITY GROUP AdventureAOAG 
      ADD LISTENER 'adventure' ( WITH IP ( ('10.0.0.21','255.255.255.0') ) , PORT = 1433 ); 
GO

Ja siinä se sitten. Yliheittoa voi kokeilla Failover Cluster Managerista käsin, ja konnektioita listeneriin Management Studiosta.

Jälkikirjoitus: jos haluat muuttaa secondary replicat lukukelpoisiksi (ilman read intenttiä), se käy komennolla

USE [master]
GO
ALTER AVAILABILITY GROUP [AdventureAOAG]
MODIFY REPLICA ON 'SQLMaster' WITH
	(SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [AdventureAOAG]
MODIFY REPLICA ON 'SQLSecondary' WITH
	(SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO

Komennot kannattaa ajaa SSMS:n ollessa kytkeytyneenä listeneriin.

HEALTH_CHECK_TIMEOUT ja muu säätö

Virtuaalikoneilla ajettaessa AlwaysOn saattaa joskus reagoida turhankin herkästi pieniin verkko- tai host-nypyihin, ja lopputuloksena on tarpeeton yliheitto. Asiaa voi fiksata kasvattamalla timeout-arvoa. Arvo annetaan millisekunneissa. Health check on Failover-klusterin tapa katsoa onko SQL-palvelu kunnossa.

AVAILABILITY GROUP [AdventureAOAG] SET (HEALTH_CHECK_TIMEOUT = 20000);

Failover-klusterin herkkyyttä yliheittoon ja muiden noodien vastaamiseen normaalitilanteessa voi myös säätää powershellistä (oletusarvo on 10s). Tämä arvo on siis se jonka jälkeen aloitetaan WFC:ssä noodien voting-prosessi siitä kenenkä pitäisi palvelua ajaa.

(get-cluster).SameSubnetThreshold = 20

AlwaysOnin sisällä tapahtuvan replikoinnin herkkyyttä virheille voi säätää AG:n arvoista. Alla sorvataan kolmen noodin ympäristöä. Oletusarvo on 10 sekuntia. Kaikilla noodeilla tulisi olla sama arvo. Tämän kanssa kannattaa olla jonkin verran varovainen - suuret arvot tuottavat äkkiä muita ongelmia.

https://blog.dbi-services.com/sql-server-alwayson-and-availability-groups-session-timeout-parameter/

 

USE [master]
GO
ALTER AVAILABILITY GROUP [AdventureAOAG]
MODIFY REPLICA ON N'Noodi1' WITH (SESSION_TIMEOUT = 14)
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [AdventureAOAG]
MODIFY REPLICA ON N'Noodi2' WITH (SESSION_TIMEOUT = 14)
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [AdventureAOAG]
MODIFY REPLICA ON N'Noodi3' WITH (SESSION_TIMEOUT = 14)
GO