Linked Server és Read-Only Routing

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:

  1. SQL hitelesítéssel akarok menni a linked server felé,
  2. 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. ;)

Add comment