Linked Server és Read Only Routing

2020-12-22 · 4 perc olvasás · sql SQL Server SQL Server 2012 SQL Server 2014 SQL Server 2016 SQL Server 2017 SQL Server 2019 SQL Server 2022 SQL Server 2025 ·

Megosztás:

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:

1EXEC master.dbo.sp_dropserver @server=N'ReadOnlyLinkedServer', @droplogins='droplogins'
2GO
3EXEC master.dbo.sp_addlinkedserver @server = N'ReadOnlyLinkedServer',
4                                   @srvproduct=N'',
5                                   @provider=N'SQLNCLI11',
6                                   @datasrc=N'AG1',
7                                   @provstr=N'ApplicationIntent=ReadOnly;',
8                                   @catalog=N'AdventureWorks2012'
9GO

A paraméterek jelentése:

provider: a driver, amit használunk. Nem minden driver támogat minden connection string paramétert! Lásd: Driver and client connectivity support for availability groups - SQL Server Always On | Microsoft Docs
datasrc: ez lesz az Availability Group Listener neve,
provstr: ide jön az ApplicationIntent (FIGYELEM: multi-subnet cluster esetén a MultiSubnetFailover=true beállítás is!!!! + driver: Driver and client connectivity support for availability groups - SQL Server Always On | Microsoft Docs)
catalog: az adatbázis neve, amire a read-only routing elérést szeretnénk alkalmazni. Igen, ahány adatbázis van az AG-ben, annyi linked server-re lesz majd szükség.

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:

1select *, 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:

1Msg 7437, Level 16, State 1, Line 44
2Linked 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.

 1EXEC master.dbo.sp_dropserver @server=N'ReadOnlyLinkedServerSQL1', @droplogins='droplogins'
 2GO
 3EXEC master.dbo.sp_addlinkedserver @server = N'ReadOnlyLinkedServerSQL1',
 4                                   @srvproduct=N'',
 5                                   @provider=N'SQLNCLI11',
 6                                   @datasrc=N'AG1',
 7                                   @provstr=N'ApplicationIntent=ReadOnly;',
 8                                   @catalog=N'AdventureWorks2012'
 9GO
10EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ReadOnlyLinkedServerSQL1',@useself=N'False',@locallogin=NULL,@rmtuser=N'login1',@rmtpassword='123456'
11GO

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:

1select *, 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.

1execute as login = 'login1'
2select *, SUSER_SNAME() from openquery([ReadOnlyLinkedServerSQL], 'select @@servername, SUSER_SNAME()')
3revert

Itt az alábbi hibát kapom:

1Msg 7416, Level 16, State 2, Line 47
2Access 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:

 1XEC master.dbo.sp_dropserver @server=N'ReadOnlyLinkedServerSQL', @droplogins='droplogins'
 2GO
 3EXEC master.dbo.sp_addlinkedserver @server = N'ReadOnlyLinkedServerSQL',
 4                                   @srvproduct=N'',
 5                                   @provider=N'SQLNCLI11',
 6                                   @datasrc=N'AG1',
 7                                   @provstr=N'Server=AG1;User ID=login1;ApplicationIntent=ReadOnly;',
 8                                   @catalog=N'AdventureWorks2012'
 9 
10EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ReadOnlyLinkedServerSQL',@useself=N'False',@locallogin=NULL,@rmtuser=N'login1',@rmtpassword='123456'
11GO

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:

1select *, SUSER_SNAME() from openquery([ReadOnlyLinkedServerSQL], 'select @@servername, SUSER_SNAME()')
2GO
3 
4execute as login = 'login1'
5select *, SUSER_SNAME() from openquery([ReadOnlyLinkedServerSQL], 'select @@servername, SUSER_SNAME()')
6revert

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. ;)