Egy újabb érdekes dolgot szeretnék megosztani, mégpedig: linked server-t hogyan irányítsunk egy SQL Server Availability Group Read-Only replica szerverre. Amibe most nem mennék bele teljes részletességgel, hogy az Availability Group esetén ezt hogyan kell beállítani.
Ami viszont fontos:
- két szerverem van, ami AG-ben van, CONTOSOSQL2 és CONTOSOSQL3,
- van egy AG-m, aminek van egy Availability Group Listener-e, ami szükséges a read-only routing-hoz, mert csak akkor működik, ha ezen keresztül kapcsolódok + kell egy connection string beállítás, az ApplicationIntent=ReadOnly, illetve meg kell adni mindig az adatbázis nevét, ami az adott AG-ben van,
- már van előre beállított read-only routing: amikor a CONTOSOSQL2 a primary replica, akkor a CONTOSOSQL3 a readable repica, és fordítva,
- a linked server-t a CONTOSOSQL1 szerveren állítom be, ami egy stand-alone telepítés.
Rém egyszerű igaz? :) Lássuk.
Windows hitelesítés alkalmazása
Az első példa során Windows hitelesítést fogok alkalmazni, ami az alábbiakat feltételezi:
- Kerberos van, azaz van jól beállított SPN,
- az érintett szervereken van jogosultságom.
Ehhez az alábbi kódra lesz szükség, hogy létrehozzunk egy működő linked server-t, readable replica szerverre irányítva:
GO
EXEC master.dbo.sp_dropserver @server=N'ReadOnlyLinkedServer', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ReadOnlyLinkedServer',
@srvproduct=N'',
@provider=N'SQLNCLI11',
@datasrc=N'AG1',
@provstr=N'ApplicationIntent=ReadOnly;',
@catalog=N'AdventureWorks2012'
GO
A paraméterek jelentése:
Tesztelni is kellene, hogy valóban működik e. Ehhez az OPENQUERY (Transact-SQL) - SQL Server | Microsoft Docs rowset function-t fogom használni, amit a CONTOSOSQL1 szerveren futtatok, Windows hitelesítéssel:
GO
select *, SUSER_SNAME() AS [localuser] from openquery([ReadOnlyLinkedServer], 'select @@servername AS [servername], SUSER_SNAME() AS [remoteuser], auth_scheme from sys.dm_exec_connections where session_id = @@spid')
Az eredmény önmagáért beszél:
Látszik, hogy:
- KERBEROS van!
- át lettem irányítva a readable replica szerver felé.
Ez így igazán csodálatos, vajon mi van akkor, amikor SQL hitelesítést szeretnék?
SQL hitelesítés alkalmazása
Itt rögtön kétfelé kell bontani a dolgokat:
- SQL hitelesítéssel akarok menni a linked server felé,
- SQL login-nal akarok linked server-hez köthető lekérdezést futtatni.
A második esetben, mivel a linked server Windows hitelesítést használ, az alábbi hibaüzenetet kapom:
.
Msg 7437, Level 16, State 1, Line 44
Linked servers cannot be used under impersonation without a mapping for the impersonated login.
Ezzel most nem foglalkoznék, erre is megvan a megoldás.
Nézzük az első esetet: ehhez az alábbi kód futott.
GO
EXEC master.dbo.sp_dropserver @server=N'ReadOnlyLinkedServerSQL1', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ReadOnlyLinkedServerSQL1',
@srvproduct=N'',
@provider=N'SQLNCLI11',
@datasrc=N'AG1',
@provstr=N'ApplicationIntent=ReadOnly;',
@catalog=N'AdventureWorks2012'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ReadOnlyLinkedServerSQL1',@useself=N'False',@locallogin=NULL,@rmtuser=N'login1',@rmtpassword='123456'
GO
A varázslat az sp_addlinkedsrvlogin részben lenne. Itt adom hozzá azt az SQL login-t, ami a távoli szerveren elérhető és joga is van az adatbázishoz. Itt ismét futtatom a teszt scriptet:
GO
select *, SUSER_SNAME() AS [localuser] from openquery([ReadOnlyLinkedServerSQL1], 'select @@servername AS [servername], SUSER_SNAME() AS [remoteuser]')
Ennek az eredménye:
Látható, hogy az SQL login volt használatban a távoli elérésnél, de az én Windows loginom indította.
Most megpróbálom egy helyi SQL login nevében is futtatni.
GO
execute as login = 'login1'
select *, SUSER_SNAME() from openquery([ReadOnlyLinkedServerSQL], 'select @@servername, SUSER_SNAME()')
revert
Itt az alábbi hibát kapom:
.
Msg 7416, Level 16, State 2, Line 47
Access to the remote server is denied because no login-mapping exists.
Ezt megint el kell engednem, nem akarok login mapping-et.... helyette megcsináljuk rendesen, hogy:
- SQL login esetén is menjen, illetve
- Windows hitelesítés esetén is!
Ez annyit jelent, hogy mindegy, hogy aki kezdeményezi a lekérdezést SQL vagy Windows login, mindegyik esetben a megadott SQL login-t használja a linked server kapcsolat kiépítése során. Ehhez az alábbi linked server-t hozom létre:
GO
EXEC master.dbo.sp_dropserver @server=N'ReadOnlyLinkedServerSQL', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ReadOnlyLinkedServerSQL',
@srvproduct=N'',
@provider=N'SQLNCLI11',
@datasrc=N'AG1',
@provstr=N'Server=AG1;User ID=login1;ApplicationIntent=ReadOnly;',
@catalog=N'AdventureWorks2012'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ReadOnlyLinkedServerSQL',@useself=N'False',@locallogin=NULL,@rmtuser=N'login1',@rmtpassword='123456'
GO
Itt a paramétereknél az alábbi fontos dolgokat kell kiemelnem:
- provstr: ide egy connection string részletet kell megadni, a fenti módon. Ez lesz ami miatt elkezdi "rendesen" használni
Itt megint futtatom a teszteket, először Windows majd SQL login segítségével:
GO
select *, SUSER_SNAME() from openquery([ReadOnlyLinkedServerSQL], 'select @@servername, SUSER_SNAME()')
GO
execute as login = 'login1'
select *, SUSER_SNAME() from openquery([ReadOnlyLinkedServerSQL], 'select @@servername, SUSER_SNAME()')
revert
Az eredmény:
Az első esetben az én Windows felhasználóm indította a lekérdezést, majd a login1 SQL login segítségével lépett be a távoli szerverre, a második esetben egy SQL login indította a lekérdezést, illetve ebben az esetben nem jött elő a login-mapping probléma.
Ezzel a megoldással több, nem is kicsi rendszernél működő read-scale kialakítást csináltam, ami teljesen jól működik. Az egy másik kérdés, hogy a linked server pontosan mire és milyen funkcióra van használva, nem mindig ez a megoldás. A read-only routing miatt viszont ez egy nagyon jó skálázási technika, amivel terhlés elosztást lehet csinálni. Itt csak a licensz költség lehet a nagyobb kihívás. ;)