I am Berke

Linked Server és Read-Only Routing

janos@nospam.iamberke.com — Tue, 22 Dec 2020 11:34:00 +0000

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:

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:

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

Mennyi CPU kell egy Availabity Group esetén

janos@nospam.iamberke.com — Wed, 22 Apr 2020 11:20:00 +0000

2012 óta, mióta az SQL Server 2012 Enterprise Edition-ben megjelent az Availability Group, azóta folyamatosan megkapom a következő kérdéseket:

Miért nem lehet több száz adatbázist egy szerveren Availability Group-ba tenni?
Minek ennyi CPU?

A válasz nem triviális, ellenben számolható.

A Microsoft ajánlása, hogy maximum 100 adatbázis és összesen 10 AG legyen egy fizikai gépen, lásd itt. Akkor ezzel le is zártam, kész, ez az ajánlás :).

Persze, hogy nem csak ennyi, hanem nézzünk mögé kicsit. Kezdjük az alapoknál: minden SQL Server un. thread-eket (nem, nem fordítom le) használ a feladatok elvégzéséhez, ezek száma pedig függ a CPU magok számától! Kezd érdekes lenni, vajon mennyi az annyi?

Alapvetően 512 thread van, majd

<=4 mag esetén marad az 512 thread
>4 és <=64 esetén 512+(logikai magok -4)*16
>64 esetén 512+(logikai magok -4)*32

Ezek 64 bites verzióra igazak, részletek itt.

Vegyünk egy 16 magos rendszert, ekkor a thread-ek száma: 512+(16-4)*16=704

Ebből mennyit használhat maximum csak az AG? A dokumentáció szerint a max worker száma -40, azaz 704-40=664. Igen ám, de akkor a lekérdezéseknek mi marad? Rövid válasz: szinte semmi és ez rossz!

Menjünk vissza az eredeti kérdésre, hogy miért nem lehet több száz adatbázist AG-ba tenni és miért mondja a Microsoft, hogy maximum 100 adatbázis és 10 AG.

Tételezzük fel a következőt: van 100 adatbázis, 1 AG, 2 secondary replica. Ebben az esetben mennyi lesz a minimum pool méret? Az alábbi képlet megadja:

(DC*(LCT+(LST*SRC)))+MHT

DC: adatbázisok száma,
LCT: log capture thread, minden primary replica adatbázis használ 1 darabot,
LST: log send thread, minden másodlagos replica adatbázishoz tartozik 1 darab,
SRC: secondary replica darabszám,
MHT: message handler thread, 1 darab/ AG

Ezek alapján lássuk a számítást (100*(1+(1*2)))+1=301, azaz a 704 thread kb. 43%-a csak az Availability Group használatában lenne!!! Ez csak a primary oldal, a secondary esetén másként kell számolni, főleg az SQL Server 2016-os verziótól, ahol már van parallel redo is.

Lássuk egy élő példán is a különbséget: az alábbiakban 2 darab SQL Server, 1 primary és 1 secondary replica thread felhasználása látszik, 100 darab adatbázis és 120 egyidejű felhasználó esetén.

Ez egy 8 magos rendszer, ahol 576 thread van, majd 201 thread elmegy az Availability Group miatt.

Ugyan ez 20 adatbázis és 120 felhasználó esetén:

Ugye jól látszik a különbség: a töredéke ment el, azaz a lekérdezéseknek bőven maradt.

Még két másik metrikát is érdemes megnézni:

HADR Task Count: az első esetben ez 150K, míg a másik esetben 15-25K között van
CPU kihasználtság: természetesen itt is van különbség, az első esetben 20% körüli, míg a másik esetben ez átlag 8-12% volt.

Jól látható, hogy mennyire fontos a tervezés során ezt is figyelembe venni, nem feltétlen csak a kötségeket. Sajnos volt rá példa a munkáim során, amikor a költség felülírta a technológiát, a vége természetesen incidens lett.

Végezetül: mindig számoljuk ki, hogy mennyi CPU-ra van szükség Availability Group esetén és ehhez adjuk hozzá az alkalmazás követelményekben megadott CPU számot.

SQLSaturday Budapest 2020

janos@nospam.iamberke.com — Thu, 2 Jan 2020 16:34:00 +0000

UPDATE: tekintettel a járvány miatt kialakult helyzetre és az ajánlások figyelembe vétele miatt, a rendezvényt elhalasztjuk!

Ismét jó hírrel szolgálhatok: 2020. március 28.-án ismét lesz SQLSaturday. Az eseményre itt lehet regisztrálni: https://www.sqlsaturday.com/957/EventHome.aspx

DCOM objektum keresése

janos@nospam.iamberke.com — Mon, 30 Dec 2019 11:59:00 +0000

Sok esetben küzdök az alábbi vagy hasonló hibaüzenetekkel:

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID 
{D63B10C5-BB46-4990-A94F-E40B9D520160}
 and APPID 
{9CA88EE3-ACB7-47C8-AFC4-AB702511C276}
 to the user NT AUTHORITY\SYSTEM SID (S-1-5-18) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

Az állandó "harcomat" a dcomcnfg.exe-vel untam meg, hogy GUID alapján megtaláljam, mely alkalmazás jogaival van baj. Helyette az alábbi PowerShell script-et használom:

$strComputer = "." 
$appid = '{9CA88EE3-ACB7-47C8-AFC4-AB702511C276}'
 
Get-WMIObject -class "Win32_DCOMApplication" `
-Namespace "root\CIMV2" `
-ComputerName $strComputer `
-Filter "AppID='$appid'" | SELECT *

A vicc az, hogy SQL Server esetén, bizonyos esetekben, elég sok hasonló (más GUID) üzenettel találkozom. (éljen a Next, Next Finish :-) )

SQL Server hálózati beállítások

janos@nospam.iamberke.com — Mon, 25 Feb 2019 09:46:00 +0000

Talán az egyik legproblémásabb területe az SQL Server konfigurálás során a hálózati beállítás. Aki először találkozik a termékkel, de később is :), problémát szokott jelenteni, hogyan lehet engedélyezni az SQL Server database engine távoli elérést is. Anno, a 2005-ös verzió esetén már az volt a Microsoft mondása, hogy csak azok a szolgáltatások és protokollok vannak engedélyezve, amire valóban szükség van. Alapvetően a TCP kapcsolatok csak az Enterprise és a Developer verziók esetén vannak alapból engedélyezve, a többi verziónál le vannak tiltva. Ezt a telepítés során, amennyiben config file vagy parancssori telepítés van használatban, a TCPENABLED="1" beállítással lehet engedélyezni. Ilyenkor az összes, a telepítés során megtalált IP címen, hálózati kártyán figyelni fog valamilyen porton az adatbázis szolgáltatás.

Alapvetően a protokollok engedélyezése az SQL Server Configuration Manager segítségével történhet, az alábbi képen látható helyen, a default instance esetén:

Igen ám, de vajon melyik IP címeken figyel a szolgáltatás? Ha a TCP/IP-n megnézzük (jobb gomb, properties), akkor alapból a Listen All = Yes.

Ebben az esetben, minden, még az APIPA és a Local-Link Address is használatban van. Ez azt jelenti, hogy bármely címen tud fogadni kéréseket az SQL példányunk. Ezt pedig a beépített TCP endpoint-on keresztül teszi.

Látható mind az SQL Server Configuration Manager-ben és az SSMS-ben is, hogy vannak itt ás protokollok is. Én most csak a TCP/IP-re fókuszálok. A TCP endpoint-ok egyébként lekérdezhetők az alábbi lekérdezéssel:

select * from sys.tcp_endpoints

Az eredménye (az én demó gépemen):

Az összes endpoint pedig ezzel kérdezhető le:

select * from sys.endpoints

Itt az eredmény:

Ezek részletezése is megérne pár szót, de most menjünk vissza inkább arra az esetre, amikor is IP címet változtatok és/vagy új hálózati kártya vagy IP cím kerül megadásra. Ugye alapvetően az összes címen figyel a szerver, ami látszik is a SQL Server Configuration Manager alkalmazásban:

Látszik, hogy az IpAll fix porton, a 1433-as TCP porton figyel. Itt fontos megemlítenem, hogy dinamikus portot nem állítunk sose, így a TCP Dynamic Ports üresen marad. ez ugye azért is érdekes, mert több SQL példány esetén akkor mi legyen? Természetesen adunk neki egy másik portot! Most ne menjünk abba bele, hogy mi van akkor, ha külön IP címeken de ugyan azt a portot akarjuk adni. Ilyen esetekben a névfeloldással (is) lehet "némi" probléma, arról nem is beszélve, hogy mennyire megnehezíti az üzemeltetést és a nagy rendelkezésre állási megoldások konfigurációját.

A másik, amit észre lehet venni a fenti képen, hogy az IP9 ( a 9. ip cím) Active = Yes, azaz létezik, ez mindig Yes, illetve Enabled = Yes, azaz ezen a címen biztos fogad kéréseket a szolgáltatás.

Akkor most "csavarok" egyet rajta: mi a helyzet, amikor csak egy adott IP címen akarom, hogy fogadjon kéréseket a szolgáltatás? Ilyen esetben annyi a dolgunk, hogy:

Listen All = No
az összes olyan IP címnél, ahol nem akarjuk, hogy a szolgáltatás fogadja a kéréseket legyen az Enabled = No,
az egy vagy több IP címen, ahol akarjuk, hogy válaszoljon a szolgáltatás, ott az Enabled = Yes.

Ugye nem is nehéz :). Akkor most ismét "csavarok" egyet rajta: mi a helyzet, amikor egy új IP cím jön a képbe és csak azon szeretném, hogy figyeljen az adatbázis szolgáltatás? Hát igen, ez az az eset, ami már nem annyira triviális. Az SQL Server telepítője a telepítés idején beállításra került hálózati címeket olvassa fel csak, amennyiben változás van ezekben, már nem követi vissza, azaz nem töröl és nem vesz fel újabb címeket! Ilyen esetekben nincs más megoldás, mint a Registry kézi beállítása: a HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL<verzió>.<instance név>\MSSQLServer\SuperSocketNetLib\Tcp\ kulcs szerkesztésével lehet ezt elérni. A verzió 13 SQL 2016, 14 SQL 2017 esetén pl. Az instance név, amit megadtunk a telepítés során, illetve default instance esetén az MSSQLSERVER.

Erre készítettem egy PowerShell scriptet, ami megcsinálja a szükséges lépéseket:

Letiltja a Listen All beállítást, azaz csak egy címen fog figyelni a szolgáltatás
Amennyiben fel van véve már az IP cím, akkor csak azt fogja engedélyezni Enabled = Yes, a többit letiltja, azaz Enabled = No
Amennyiben ez egy új IP cím, akkor felveszi és a 2. pontban megadott konfigurációt állítja be.

A scriptet mindenki saját felelősségére használja, érdemes kipróbálni egy teszt környezetben, mielőtt éles szerveren is használatban lenne. Íme a script:

$InstanceName = "MSSQLSERVER"
$Version = "13" #SQL2016 13, SQL2017 14
$InstanceIP = "10.10.10.12"

#set ListenOnAllIPs 0, Enabled 1
Write-Host "Setting up ListenOnAllIPs 0, Enabled 1"
$regTcpRoot = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL' + $Version + '.'+ $InstanceName + '\MSSQLServer\SuperSocketNetLib\Tcp\'
Set-ItemProperty -Path $regTcpRoot -Name "ListenOnAllIPs" -Value 0
Set-ItemProperty -Path $regTcpRoot -Name "Enabled" -Value 1

# get tcp information from registry
$regTcpIPs = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL' + $Version + '.'+ $InstanceName + '\MSSQLServer\SuperSocketNetLib\Tcp\*'
$objReg = Get-ItemProperty $regTcpIPs 

#set tcp properties
$objReg | ForEach-Object { 
                            if ($_.IpAddress -eq $InstanceIP) 
                            { 
                                Write-Host "Setting up " $_.IpAddress
                                Set-ItemProperty -Path $_.PSPath -Name "Enabled" -Value 1
                                Set-ItemProperty -Path $_.PSPath -Name "Active" -Value 1
                                Set-ItemProperty -Path $_.PSPath -Name "TcpPort" -Value 1433
                                Set-ItemProperty -Path $_.PSPath -Name "TcpDynamicPorts" -Value ""
                            } 
                            else  
                            {
                                Write-Host "Setting up " $_.IpAddress
                                Set-ItemProperty -Path $_.PSPath -Name "Enabled" -Value 0
                                Set-ItemProperty -Path $_.PSPath -Name "Active" -Value 1
                                Set-ItemProperty -Path $_.PSPath -Name "TcpPort" -Value 1433
                                Set-ItemProperty -Path $_.PSPath -Name "TcpDynamicPorts" -Value ""
                            }
                            
                          }

#add missing IP for virtual host name ($InstanceIP) if not exist
if (($objReg | Where-Object { $_.IpAddress -eq $InstanceIP }) -eq $null)
{
    Write-Host "Setting up new ip: " $InstanceIP
    $regNew = $objReg.PSParentPath.GetValue(0) + '\IP' + $objReg.Count
    New-Item -Path $objReg.PSParentPath.GetValue(0) -Name ('\IP' + $objReg.Count) | Out-Null
    Set-ItemProperty -Path $regNew -Name "Enabled" -Value 1
    Set-ItemProperty -Path $regNew -Name "DisplayName" -Value "Specific IP Address"
    Set-ItemProperty -Path $regNew -Name "Active" -Value 1
    Set-ItemProperty -Path $regNew -Name "IpAddress" -Value $InstanceIP
    Set-ItemProperty -Path $regNew -Name "TcpPort" -Value "1433"
    Set-ItemProperty -Path $regNew -Name "TcpDynamicPorts" -Value ""
}

A scriptben definiált változók jelentése:

$InstanceName: ez az SQL példány neve, pl. a default instance esetén ez MSSQLSERVER
$Version: SQL Server fő verzió száma:
- SQL 2017: 14
- SQL 2016: 13
- SQL 2014: 12
$InstanceIP: ez lesz az az IP cím, amin a szolgáltatás figyelni fog, és csak ezen.

SQL Server 2019 bejelentés + CTP

janos@nospam.iamberke.com — Mon, 24 Sep 2018 14:47:00 +0000

Ma az Ignite konferencián bejelentették az SQL Server 2019 verziót, aminek a publikus CTP-je elérhető a http://www.microsoft.com/sqlserver2019 oldalon.

Újdonságok röviden

Talán a legérdekesebb az Apache Spark^TM and Hadoop Distributed File System (HDFS) integráció, amivel "big data cluster" építhető. Erről a Microsoft további, részletes információkat fog adni a jövőben. Én nem ezekre fókuszálnék, hanem a core database engine szolgáltatásokra, újdonságokra:

Intelligent Query Processing: az SQL Server 2017-ből ismert AQP további szolgáltatásokkal bővült.
Persistent memory support: itt az NVDIMM modulokra van utalás. SQL Server 2016-os verziótól lehetett egy olyat csinálni, hogy a tranzakciós log tail része egy ilyen DAX filerendszerre formázott modulon van, ezzel a tranzakció áteresztő képesség növekedett. Ettől a verziótól már nem csak ez, hanem teljes adatbázisok is kerülhetnek ilyen modulokra.
Lightweight query profiling: ezzel a futási tervek elemzése lehet könnyebb, hasonló, mint a Live Query Statistics.
Always Encrypted with secure enclaves: ez egy kiterjesztése az SQL Server 2016 verzióban bevezetett Always Encrypted szolgáltatásnak. Lényegében további védelmet ad (szoftveres és/vagy hardveres) malware és magasabb jogosultságú felhasználókkal szemben.
Data Discovery and Classification: gyakorlatilag új metaadatok az adatok osztályozásához, illetve megfelelőség kezelésekhez, mint pl. GDPR, PCIDSS.
Resumable online index: SQL Server 2017-ben az index rebuild volt megállítható, mostantól a létrehozás is.
SQL Graph fejelsztések: T-SQL MERGE és edge constraint
UTF-8 támogatás: :)

Ezen túl van még elég sok (van ami még nem publikus :)), az érdekesebbekről külön bejegyzést is fogok írni.

A kedvencem: mostantól már Java kód is futtatható, mint ahogy a 2016-tól R, 2017-től a Python is bekerült :) Vajon mi lesz a következő ;).

OnPrem adatbázisok Azure költség becslése migrálás előtt

janos@nospam.iamberke.com — Fri, 24 Aug 2018 12:51:00 +0000

Augusztus közepén megjelent a Database Migration Assistant újabb verziója (innen letölthető a v4.0). Ez a verzió már nem csak egy-egy adatbázis szerver verzió frissítésben, Azure költözésben tud segíteni, de már becsült költséget is meg tudja mondani egy adott környezetre vonatkozóan. Lássuk is, hogyan lehet ezt használni.

Előkövetelmények, teszt környezet

A teszt környezet egy Windows Server 2016 lesz SQL Server 2016-os verzióval. Ezen túl az alábbi beállításokat kell elvégezni:

winrm beállítás: szükség van a szerveren a remote winrm elérésre. Itt nem részletezem a miérteket, sem a biztonságot, ahhoz hogy működjön az eszköz az alábbi parancsot kellett elevált módban futtatni:
```
winrm quickconfig -transport:https
```
.NET 4.5.2 vagy újabb, 64 bites változat telepítése.
Minden script és parancs a DMA mappájának a kontextusában kell fusson, ami az én esetemben: C:\Program Files\Microsoft Data Migration Assistant

Szerver adatgyűjtés

Ahhoz, hogy árakat és szolgáltatási szinteket tudjon becsülni az eszköz, előbb adatokat kell gyűjtenie. Az adatgyűjtés egy PowerShell script segítségével történhet a kiértékelés pedig két módon:

Offline
Online

Adatgyűjtés

Az alábbi PowerShell script fogja begyűjteni a szükséges adatokat a szerverről.

.\SkuRecommendationDataCollectionScript.ps1 ` 
-ComputerName SQL2016 `
-OutputFilePath C:\temp\DMA\counters_SQL2016.csv `
-CollectionTimeInSeconds 120 `
-DbConnectionString "Server=.;Initial Catalog=master;Integrated Security=SSPI;"

A paraméterek jelentése:

ComputerName: ez annak a gépnek a hálózati neve, amelyiket vizsgáljuk.
OutputFilePath: ez lesz majd a kimeneti csv file, amiben az összegyűjtött performance counter és egyéb szerver információk lesznek.
CollectionTimeInSeconds: ez lesz a performance counter-ek mintavételezési ideje. Itt minimum 40 perc az ajánlott, de a tapasztalat azt mutatja, hogy ettől több időre van szükség egy pontosabb becslésre.
DbConnectionString: a ComputerName-ben megadott SQL Server példány eléréséhez szükséges connection string. Lásd még korlátok.

Offline kiértékelés

Az offline kiértékelés azt jelenti, hogy a telepített verzióban elérhető adatok alapján végzi el a költség becslést. Ehhez az alábbi parancsra van szükség:

DmaCmd.exe 
/Action=SkuRecommendation 
/SkuRecommendationInputDataFilePath="C:\temp\DMA\counters_SQL2016.csv" 
/SkuRecommendationTsvOutputResultsFilePath="C:\temp\DMA\prices.tsv" 
/SkuRecommendationJsonOutputResultsFilePath="C:\temp\DMA\prices.json" 
/SkuRecommendationOutputResultsFilePath="C:\temp\DMA\prices.html" 
/SkuRecommendationPreventPriceRefresh=true

Természetesen ezeket egy sorba kell tenni, akkor le is fog futni. A paraméterek jelentése:

Action: ez a SkuRecommendation kell legyen.
SkuRecommendationInputDataFilePath: az adatgyűjtés során generált csv file elérési útvonala.
SkuRecommendationTsvOutputResultsFilePath: ez lesz a tsv fomrátumú kimeneti file elérési útvonala.
SkuRecommendationJsonOutputResultsFilePath: ez lesz a json fomrátumú kimeneti file elérési útvonala.
SkuRecommendationOutputResultsFilePath: ez lesz a html fomrátumú kimeneti file elérési útvonala. Én ezt fogom majd megmutatni itt.
SkuRecommendationPreventPriceRefresh: amennyiben ez true, akkor offline, a DMA által ismert árakkal fog dolgozni.

Természetesen nem kell minden kimeneti file típust megadni, én most megadtam. A html output pedig ez lett:

Pár érdekes dologra szeretném felhívni a figyelmet:

Amennyiben szeretnénk egy provisioning PowerShell scriptet előállítani, a subscription id, resource group, server name, admin username és password megadása után a Generate Provisioning Script gomb megnyomásával ezt is elő lehet állítani (működik!).
A régió váltás is lehetséges, ebben az esetben az adott régióra vonatkoztatott árak jelennek meg.
Lehet állítani a szolgáltatási szinteken is, ebben az esetben az árak változhatnak.
Ennek a bejegyzésnek az írásakor a Microsoft ad kedvezményt, abban az esetben, ha van már SQL Server licensz. Ekkor be kell pipálni az "I already have a SQL Server License" melletti checkbox-ot, majd a provisioning script-be is bekerül a AzureSqlDbProvisioningDatabaseLicenseIncluded=true kapcsoló a DmaCmd.exe-hez. Így a számlázásnál ezt is figyelembe fogja venni. Ez utóbbit még nem ellenőriztem, pár napos ez a verzió ;).

Online kiértékelés

Hol is kezdjem, a dokumentáció nem igazán bőbeszédű pár paraméter hogyanjára, így ezt nekem kellett kitúrni, nem volt egy egyszerű eset, de lehetetlen sem :) Online kiértékelés alatt azt kell érteni, hogy az Azure előfizetésünk szerinti árakon ad költség becslést, ehhez azonban hozzá kell csatlakozni az előfizetésünkhöz. Lássuk előbb a DmaCmd parancsot:

DmaCmd.exe /Action=SkuRecommendation 
/SkuRecommendationInputDataFilePath="C:\temp\DMA\counters_2016.csv" 
/SkuRecommendationTsvOutputResultsFilePath="C:\temp\DMA\prices_online.tsv" 
/SkuRecommendationJsonOutputResultsFilePath="C:\temp\DMA\prices_online.json" 
/SkuRecommendationOutputResultsFilePath="C:\temp\DMA\prices_online.html" 
/SkuRecommendationCurrencyCode=EUR 
/SkuRecommendationOfferName=MS-AZR-0044p  
/SkuRecommendationRegionName=WestEurope 
/SkuRecommendationSubscriptionId=<subscriptionid> 
/AzureAuthenticationInteractiveAuthentication=true 
/AzureAuthenticationClientId=<azureadappclientid> 
/AzureAuthenticationTenantId=<tenantid>

Itt nem minden paraméter raktam be, csak azokat, amiket most itt meg szeretnék mutatni. A teljes paraméter lista itt elérhető. Lássuk a fenti paramétereket:

SkuRecommendationInputDataFilePath: az adatgyűjtés során generált csv file elérési útvonala.
SkuRecommendationTsvOutputResultsFilePath: ez lesz a tsv fomrátumú kimeneti file elérési útvonala.
SkuRecommendationJsonOutputResultsFilePath: ez lesz a json fomrátumú kimeneti file elérési útvonala.
SkuRecommendationOutputResultsFilePath: ez lesz a html fomrátumú kimeneti file elérési útvonala. Én ezt fogom majd megmutatni itt.
SkuRecommendationCurrencyCode: az árak pénzneme. Itt most EUR lesz.
SkuRecommendationOfferName: árazást befolyásoló ajánlat kódja. Itt most a free trial kód van beírva, de a teljes lista itt elérhető.
SkuRecommendationRegionName: Azure adatközpont régió meghatározása, ami szintén befolyásolhatja az árakat.
SkuRecommendationSubscriptionId: ez az Azure előfizetés azonosítója, lásd később.
AzureAuthenticationInteractiveAuthentication: ebben az esetben egy felugró ablakban fogja bekérni az előfizetéshez tartozó/szükséges felhasználó azonosítót és jelszót. Fentebb írtam, hogy több paraméter is van, létezik tanúsítvány alapú hitelesítés is. Itt most az interaktív hitelesítést választom.
AzureAuthenticationClientId: ez lesz az Azure AD application azonosítója, lásd később.
AzureAuthenticationTenantId: ez a tenant id, lásd később.

Van itt pár paraméter, amivel először nem nagyon tudtam mit kezdeni és/vagy fogalmam sem volt, hogy honnan kellene elővarázsolni. Lássuk ezeket.

SkuRecommendationSubscriptionId

Ezt az információt az Azure portálon lehet megtalálni, ehhez be kell jelentkezni a https://portal.azure.com oldalon, majd a bal oldali menüben ki kell választani a Subscriptions menüpontot és a jobb olalon a megjelenő előfizetések listájában a megfelelő sorból a subscription id oszlopa kell. Lásd alább.

AzureAuthenticationClientId

Ezt az információt már nem volt olyan könnyű előszedni, de ez is az Azure portálon belül, a https://portal.azure.com bejelentkezés után meg lehet találni. Jajj, nem is :), meg kell csinálni, alkalmazás regisztrációt kell csinálni és jogosultságot is kell adni. Ezt az alábbiak szerint lehet megcsinálni.

Először az Azure Active Directory menüpontot, majd az App registration-t kell kiválasztani, ott pedig a New application registration gombot kell megnyomni.

Ezek után meg kell adni az alábbi információkat:

Name: ez lesz az alkalmazás neve, én most azt adom neki, hogy DMAv4.
Application Type: itt a Native opciót kell kiválasztani.
Redirect URI: ide szinte bármit beírhatunk, ebben az esetben nem lényeges. Én most http://DMAv4 adok meg.

Ezek után a Create gomb megnyomása után létrejön az alkalmazás.

Amint az alkalmazás létrejött, meg is kapjuk a hiányzó adatot az AzureAuthetnicationClientId paraméterhez, amit az alkalmazás listában az Application ID oszlopban találunk meg. Fontos, hogy válasszuk ki a szűrésnél az All apps opciót is.

Ezzel meg is van ez a paraméter is, de még jogot is kell adni. Ehhez az alábbi lépésekre van szükség:

Nyissuk meg az előzőekben létrehozott alkalmazást.
Kattintsunk a Settings gombra, majd a Required Permission menüpontra.
A Required Permission részen az Add gombot is nyomjuk meg.

Ezek után válasszuk ki a Windows Azure Service Management API-t, majd nyomjuk meg a Select gombot.

A Select Permissions résznél válasszuk ki a jogosultságot.

Végül a Grant Permission gomb megnyomásával lehet érvényre hozni.

Ezzel a megfelelő jogosultság is rendelkezésre áll a DmaCmd-nek.

AzureAuthenticationTenantId

Ezt az információt is az Azure portálon lehet megtalálni, ehhez be kell jelentkezni a https://portal.azure.com oldalon, majd a bal oldali menüben ki kell választani az Azure Active Directory menüpontit, majd azon belül a Properties és ez lesz a Directory ID. Lásd alább a képen is. Ugye milyen könnyű volt :)

Korlátok

Sajnos a v4.0 verziónak vannak korlátai:

named instance nem támogatott (még), ez a DbConnectionString paramétert érinti.
régebbi SQL verziók esetén a performance counter adatgyűjtés hibára futhat, mivel olyan méréseket állítana be, ami a régebbi verziók esetén még nincsennek.

Konklúzió

Alap esetekre nagyon jó lehet ez az eszköz, főleg mert nem igényel "túl sok" időráfordítást, ellenben a korlátok miatt elég nehézkes lehet a használata. Várhatóan a visszajelzések mentén, lesz újabb verzió, ami már "minden igényt" ki fog elégíteni. Részemről jónak tartom az eszközt, de nem feltétlen olyan hatékony, mint egy teljes workload analízis. Erre meg mindig azt kapom, hogy a többségnek elég lesz ez is. Valóban, lehet így van, de ettől függetlenül a kellő körültekintés fontos, mert SLA és pénzügyi oldalról is igen érzékenyen érintheti az adott ügyfelet.

Zárásként, határozottan ajánlom az online kalkulációt, mert akkor kapjuk a pontosabb becslést az árakra, mindig az aktuális árakkal dolgozik ebben az esetben.

Lekérdezések futásának nyomonkövetése

janos@nospam.iamberke.com — Sat, 4 Aug 2018 15:13:00 +0000

7 évvel ezelőtt írtam egy cikket már erről, hogyan lehet állapot információt kliens oldalon kiiratni. Ami miatt ismét előjött, hogy ma is próbálkoztam egy nagy batch futtatásakor, de a PRINT parancsot használtam. Sajnos ennek van egy pici szépséghibája: akkor jeleníti meg, adja vissza a szöveget, amikor az adott batch/lekérdezés lefutott. Például:

PRINT 'valami';
WAITFOR DELAY '00:00:05';
PRINT 'valami';

Ez az első PRINT utasításnál sem írja ki, megvárja, míg a batch befejeződik. Nem túl jó. Kicsit változtatok.

PRINT 'valami';
GO
WAITFOR DELAY '00:00:05';
PRINT 'valami';
WAITFOR DELAY '00:00:05';
PRINT 'valami';

Itt a GO elválasztó miatt az első PRINT kiírásra kerül, a másik kettő csak 10 másodperc után. Helyette inkább a RAISERROR lenne a jó, ha egy batch-en belül akarom követni, hogy hol tart a kód. Pl.:

RAISERROR('valami', 10,1) WITH NOWAIT
WAITFOR DELAY '00:00:05';
RAISERROR('valami', 10,1) WITH NOWAIT
WAITFOR DELAY '00:00:05';

Ebben az esetben az első kiírás rögtön megtörténik, majd a második 5 másodperc után, nem a végén. Fontos, hogy kell a WITH NOWAIT.

Újra Data Platform MVP lettem

janos@nospam.iamberke.com — Mon, 2 Jul 2018 09:58:00 +0000

Július 1 a Microsoftnál mindig is egy érdekes dátum volt, ilyenkor hirdetik ki, hogy kik lettek az új vagy a régiek közül kik lettek megújítva az MVP (Most Valuable Professional) programban. Én azon szerencsések közé tartozom, akiket díjazott a Microsoft, idén már 4. alkalommal. Sajnos a program változásai miatt a magyarországi MVP közösség 18 tagjából csak 11-en maradtunk. Erről többen is írtak blog bejegyzést, pl Gál Tamás (itt), az egyik legrégebbi, ha nem a legrégebbi MVP, illetve Réczi Gábor is (itt).

SQL Server Error Log betöltése adatbázisba

janos@nospam.iamberke.com — Sun, 17 Jun 2018 13:18:00 +0000

Szeretem az SQL Server Error Log-ot, azonban van vele némi probléma. Nem mindig azon a szerveren kellene megnéznem és/vagy keresnem a tartalmában, ahol az van, így jó lenne, ha be lehetne tölteni ezt adatbázisba. Igen, tudom, ott van az xp_readerrorlog és az sp_readerrorlog, de ezeknél egy komplexebb szűrés nem nagyon lehetséges. Arról nem is beszélve, hogy vannak olyan bejegyzések, amik több sort foglalnak el. A másik problémám ezekkel (is) az volt, hogy több szerverről, akár több tucat error log betöltése az SSMS-be nem igazán lehetséges. Éppen ezért készítettem egy PowerShell scriptet, amit kiraktam a Github-ra, ezen a linken elérhető: http://bit.ly/ErrorLogParser.

A paraméterek megadása után az eredmény már önmagáért beszél: az alábbi lekérdezéssel pl az error bejegyzéseket kerestem meg és rendeltem össze a hibaüzenettel.

SELECT [log_date]
      ,[source]
      ,[message]
	 ,CAST(SUBSTRING([message], 7, CHARINDEX(',', [message])-7) AS int) AS ErrorCode
	 ,m.text
  FROM [dbo].[ErrorLog] e
  JOIN
    sys.messages m ON m.message_id = CAST(SUBSTRING([message], 7, CHARINDEX(',', [message])-7) AS int) AND [language_id] = 1033
  where message like '%error:%' and [source] <> 'Backup'

Tudom, ez a kód nem a legszebb, de ez egy készülőben lévő eszközöm első lekérdezése, van még mit faragni rajta, de mégis, még mindig jobb, mint amikkel szoktam éles környezetben találkozni :D

A lényeg: ez a kód működik SQL Server 2005 verziótól egészen a mai napon publikusan elérhető verziókkal,de Azure nem :)