Op 25 april 2024 hadden we bij TRES onze studiedag. Na de module van de TSQL-statements en de algemene module over Azure SQL ben ik gestart met het volgende onderdeel, namelijk "Explore SQL Server 2022 capabilities".
Laten we wel zijn, als developer ben je overdag aan het werk en doe je vaak "dezelfde dingen". Want dingen anders doen, zaken uitzoeken, dat kost extra tijd en dan is de vraag of dat loont: keer je toch weer terug naar wat je altijd al deed of vind je informatie waarmee je wat kunt?
Omdat je vaak met Entity Framework werkt, ben je een beetje "los gekomen" van de daadwerkelijke database. Maar de ontwikkeling staat ook bij Microsoft niet stil. Als je niet weet wat er nieuw bij gekomen is, welke verbeteringen er zijn, dan kun je ze ook niet gebruiken.
Daarom is het mooi dat Microsoft via Learn een "learning path" voor SQL Server 2022 beschikbaar stelt, 6 modules en in totaal ruim 6 uur nodig om het door te nemen. Dat kan ik (natuurlijk) niet allemaal na die andere 2 modules ook nog op de studiedag doornemen, dus daar gebruik ik mijn "wekelijkse woensdagavond" voor. Dat is een uurtje. Dat betekent dat ik de komende 5 weken bezig ben om deze module af te ronden. Dus mensen die zich vervelen of een beetje nutteloos zitten te gamen, wordt een (echte) developer en je hebt daar geen tijd meer voor ;)
Module 1 - introductie
Dankzij de vorige learn-module weet ik dat je SELECT @@version uit kunt voeren.
Daarmee krijg ik terug dat we op dit moment gebruik maken van SQL Server 2019.
In deze module gaan we kijken naar SQL Server 2022. Deze versie kan net als 2017 en 2019 op Windows en Linux draaien, op virtuele machines en "in de cloud".
De verschillende edities:
- SQL Server Evaluation, om 180 dagen te kijken of het was is. Niet bedoeld voor productie.
- SQL Server Development, ook bedoeld om te testen, geen tijdslimiet, niet bedoeld voor productie.
- SQL Server Express, geen betaalde licentie, mag in productie gebruikt worden. Heeft limieten qua gebruik (cpu, geheugen, grootte db).
- SQL Server Web, een low-cost oplossing voor web-hosters en Web Value Added Providers (VAPs).
- SQL Server Standard, bevat een groot deel van de functionaliteit, maar niet alle. En ook wat limieten (cpu, geheugen).
- SQL Server Enterprise, het volledige pakket.
Als je de exacte specs wilt weten, dan kun je dat op deze pagina nalezen.
Wil je alles qua licenties weten, dat kun je hier online nalezen.
Verschillen ten opzichte van oudere versies;
- Cloud connected
- Built-in query intelligence
- Core engine
- Data virtualization
- Transact-SQL (T-SQL) enhancements
Via de release-notes kun je nalezen wat er allemaal met deze versie gebeurt;
Er zijn ook zaken verwijderd of uitgeschakeld:
- R, Python en Java runtimes. Machine Learning Service zit er nog in, maar daarvoor moet je jouw eigen packages toevoegen.
- Polybase Hadoop Connectivity met Java. Feature is verwijderd. Met ODBC drivers of de nieuwe REST API kun je nog Polybase services gebruiken.
- Polybase scale out groups. Feature is verwijderd. Query's met externe tabellen of OPENROWSET kunnen gebruik maken van de ingebouwde functies van SQL Server.
- Machine Learning Server. Deze dienst is "retired" in juli 2022. Daarom is deze feature nu verwijderd.
- Distributed Replay. Niet langer beschikbaar.
- Stretch Database. De functie is "deprecated" en wordt in een volgende versie verwijderd. Mocht je het gebruiken, migreer naar een nieuwe oplossing.
SQL Server is hybride. We zien een afbeelding met Self-hosted integration runtime, maar ook met Azure Arc agents en de Azure extension voor SQL Server.
En de blokjes erboven, Azure SQL Managed Instance, Azure Synapse Analytics, Microsoft Purview, Azure Entra, Microsoft Defender voor SQL.
Allemaal zaken die betrekking hebben op SQL Server 2022.
Doordat zaken met Azure verbonden kunnen worden, vraagt dat ook extra software en/of configuratie;
- Distributes Availability Group, als de boel down gaat, disaster recovery met behulp van de Link feature voor Azure SQL Managed instances vergt dat je een Distributed Availability Group opzet. SQL Server Management Studio (SSMS) biedt je een grafische interface om je te helpen met de configuratie.
- Self-hosted integration runtime. Dat is een Windows programma wat in je eigen netwerk geïnstalleerd moet worden. Hiermee kun je verbinden met SQL Server for Azure Synapse Link.
- De Azure extension for SQL Server, deze gebruik je om Microsoft Purview, Entra authenticatie, defender te kunnen gebruiken.
Mocht je meer over Link voor Azure SQL Managed instance willen weten, op deze pagina staat meer uitleg.
De Azure Synapse Link is bedoeld om je data bijna real-time over te zetten van je SQL Server naar een Synapse omgeving.
Mocht je meer willen weten over Azure Synapse Link voor SQL Server, dan kun je hier meer lezen.
Microsoft Purview is bedoeld om wettelijke regels af te dwingen, dat kan met policies. Meer informatie over Microsoft Purview voor SQL Server 2022 kun je hier nalezen.
Net als Azure SQL Managed Instance en Azure SQL Databases ondersteunt SQL Server 2022 de nieuwe syntax voor CREATE LOGIN, zodat EXTERNAL PROVIDER gebruikt kan worden (dat is een Microsoft Entra-account).
Meer over de koppeling tussen SQL Server en Entra is hier na te lezen.
Je kunt je SQL Server 2022 beschermen met Microsoft Defender.
Deze biedt:
- Vulnerability assessment, checkt je configuratie gebaseerd op standaarden: CIS en FedRAMP.
- Advanced threat protection, SQL injection, verdachte logins, brute-force attacks.
Meer over Defender voor SQL kun je hier nalezen.
Performance.
De Query Store houdt automatisch de historie bij van query's, plannen en runtime statistieken.
Data is in de tijd te bekijken, waardoor je patronen van gebruik en wijzigingen in het query plan kunt zien.
Meer informatie hierover kun je hier nakijken.
Voor SQL Server 2022 moest je expliciet de Query Store aanzetten, via ALTER DATABASE.
In 2022 staat dit standaard aan! Bij een restore van een "oude database" wordt deze instelling overgenomen van hoe het "toen stond".
Mocht je de optie niet willen gebruiken, dan kun je via ALTER DATABASE de optie weer uitschakelen.
Met Query Store hints kun je de performance verbeteren. In de pagina wordt MAXDOP genoemd (die ken ik niet). In een schema eronder zie ik Degree of Parallelism genoemd worden en snap ik wat de betekenis is ;)
Meer informatie over Query Store Hints kun je hier nalezen.
Standaard in een "always on" availability groep wordt alleen de primary replica in de gaten gehouden. In 2022 kun je met ALTER DATABASE ook read-only query's op de secundaire replica's monitoren.
Alle performance-informatie wordt in de primaire replica opgeslagen.
Opmerking: hiervoor is Trace flag 12606 nodig.
Als je meer over de Intelligent Query Processing zaken wilt weten dan kan dat via deze pagina.
Als je een oude versie geüpgrade hebt, en in een compatibility-level zit, ook dan kun je nog van sommige zaken gebruik maken.
Hier kun je nalezen wat je kunt gebruiken.
In T-SQL heb je functies om analytische workload te berekenen met PERCENTILE_CONT en PERCENTILE_DISC.
In 2022 komt daar APPROX_PERCENTILE_CONT en APPROX_PERCENTILE_DISC.
Die functies gebruik je als je heel-heel-heel veel data hebt. De functies werken sneller, 1.33% error-rate en 99% waarschijnlijkheid.
In 2022 heb je Optimize plan forcing. Als een query al een keer uitgevoerd is, kun je die compilatie-steps in de Query Store opslaan zodat volgende executies sneller verlopen.
Voor meer informatie kun je hier de documentatie nalezen.
Compatibility level 140 of hoger.
Hier wordt gesproken over "memory grant feedback", waarschijnlijk het beschikbaar stellen van geheugen (?). Dit werd gedaan op basis van de laatste keer dat de query uitgevoerd is, nu wordt een percentiel gebruikt. Memory grant feedback werd in de cache opgeslagen, daar wordt nu de Query Store voor gebruikt. Hiervoor kun je functie sys.query_store_plan_feedback gebruiken.
Meer informatie kun je hier nalezen.
Compatibility level 160 of hoger.
Iets wat ik zelf ook wel eens voorbij heb zien komen, parameter sniffing. Bij stored procedures kon 1 plan in de cache opgeslagen worden. Maar dat is niet altijd het meest optimale plan. Nu kunnen er meerdere versies bijgehouden worden. Op deze pagina is er meer informatie over na te lezen.
In SQL Server 2014 is een nieuw Cardinality Estimation model geïmplementeerd. In sommige gevallen werd een beter query-plan uitgewerkt, maar vaak was het resultaat trager. En wilden mensen eigenlijk het oude model weer gebruiken, wat kon met bepaalde trace flags of query hints.
Met de Query Store in 2022 worden hints opgeslagen om de zaken weer te versnellen.
En daar kwam DOP/MAXDOP al voorbij, het parallel zaken kunnen uitvoeren. Zoals hier genoemd "een complexe en moeilijke actie". In 2022 kan de optimizer gebruik maken van DOP feedback om de parallelle efficiëncy te berekenen. Hiervoor moet je Query Store actief hebben, het compatibility level minimaal 160 en een database met de naam DOP_FEEDBACK.
Via de view sys.query_store_plan_feedback kun je zelf bekijken wat de waardes zijn. En de meest recente DOP van een query via de last_dop kolom in de views sys.dm_exec_query_stats en sys.query_store_runtime_stats. Als je meer informatie hierover wilt, dan kun je dat hier terugvinden.
Het volgende blok gaat over security, scalability en availability. Als je wilt weten wat er allemaal nieuw in 2022 is, dat kun je hier nalezen.
Het eerste is security. We zien hoe "Ledger for SQL Server" werkt. Elke tabel die je als een "updatable ledgertabel" aanmaakt zorgt dat er ook een historie tabel en een ledger view aangemaakt wordt.
Alle aanpassingen in de ledgertabel worden in de historietabel bijgehouden. De view toont je de huidige data met de historie.
Je kunt ledger tabellen ook aanmaken als "append-only". Deze accepteert alleen INSERT statements.
Informatie hierover kun je nalezen in de Ledger-documentatie.
Er zijn nieuwe serverrollen aangemaakt om te zorgen dat mensen op basis van "least privilege" toegang krijgen. Zo is er de ##MS_ServerPerformanceStateReader## die alle performance-metrics kan raadplegen, maar geen zaken kan die de sysadmin wel kan. De rollen die je in 2022 beschikbaar hebt, dat kun je nalezen in de online documentatie.
De connectie met de database kan nog "sterker". Door in je connectiestring encrypt=strict toe te voegen wordt TDS 8.0 gebruikt.
Voordelen hiervan:
- TDS is volledig beschermd onder het TLS protocol
- TLS 1.3 wordt nu ondersteund
- Applicaties moeten een certificaat aanleveren, wat een man-in-the-middle aanval voorkomt
Meer informatie hierover kun je hier nalezen.
Schaalbaarheid.
Redelijk zware acties, zoals het opstarten of afsluiten van de database, backup, restore, dat vraagt een complete scan van de buffers in de buffer pool.
Op systemen met veel RAM (bijvoorbeeld 1 TB) kan dat lang duren. In 2022 worden standaard meerdere threads gebruikt om zaken parallel uit te voeren en die tijd in te korten.
Als je tijdelijke tabellen e.d. gebruikt, daarvoor wordt de tempdb-database gebruikt. Bij intensief gebruik kan dat vertragend werken, er wordt over PAGELATCH waits gesproken.
In 2022 is hier een stuk optimalisatie voor doorgevoerd waarmee dit opgelost moet zijn.
Om je database snel te houden heb je indexen en statistieken, die ook zo nu en dan bijgewerkt moeten worden.
Als je in 2022 de asynchronous statistics update database optie actief hebt, dan kan 2022 de ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY configuratie gebruiken om te zorgen dat die acties uitgevoerd worden in een "drukke database" op rustige tijden.
Availability - beschikbaarheid.
Contained availability groups
Met always on availability groups heb je de hoogste beschikbaarheid. Echter, alleen de data wordt gesynct. Dus SQL Agent Jobs, logins e.d. moeten "met het handje" gesynct worden. Met contained availability groups heb je dat probleem niet meer. Meer informatie is hier na te lezen.
Cross-platform snapshot backups
Je kon altijd al snapshots maken, maar daarvoor had je een VDI (Virtual Device Interface) nodig. In 2022 zijn snapshot backups ingebakken door ALTER DATABASE met SUSPEND_FOR_SNAPSHOT_BACKUP uit te voeren.
SQL Server stelt dan het wegschrijven van data naar database en transactie logbestanden uit. Hierdoor krijg je een consistente backup. Met deze methode kun je snapshots maken op Windows, Linux zonder dat je afhankelijk bent van VSS, SQL Writer of VDI. Hier kun je meer informatie vinden.
Intel QuickAssist backup compression
Bij het maken van een backup met Intel QuickAssist draag je de compressie over naar de Intel QuickAssist hardware, zodat er meer CPU beschikbaar is voor query's en applicaties.
Meer informatie is hier te vinden.
https://learn.microsoft.com/en-us/sql/relational-databases/integrated-acceleration/overview
Data virtualisatie
Vanaf SQL Server 2016 wordt polybase ondersteund. Hiermee kun je data buiten SQL Server benaderen, via OPENROWSET en CREATE EXTERNAL TABLE.
SQL Server 2019 voegde hier ODBC-drivers aan toe.
SQL Server 2022 biedt nu ook ondersteuning aan REST API interfaces voor Azure Blob Storage, Azure Data Lake Storage en S3 compatibele bronnen.
Bij die S3 kun je denken aan AWS, Dell, Hewlett Packard. Hier kun je daar meer over lezen.
Informatie over Blob Storage.
Informatie over Azure Data Lake Storage.
Informatie over S3 compatibele opslag.
We noemden al OPENROWSET en CREATE EXTERNAL TABLE. In 2022 komt daar nu CREATE EXTERNAL TABLE AS SELECT aan toe.
De nieuwe externe tabel wordt gevuld met data die in je SELECT opgevraagd wordt. Die data kan dus uit deze SQL Server-database komen, maar dus ook uit een (andere) externe bron.
SQL Server 2022 voegt ook de ondersteuning voor nieuwe bestandsformaten toe. We hadden al XML en JSON, nu hebben we ook:
Schijnbaar heb je ook BACKUP TO URL en RESTORE FROM URL statements.
In 2022 is daar (extra) ondersteuning voor S3 aan toegevoegd, hier kun je daar meer over lezen.
In SQL Server 2022 zijn behoorlijk wat toevoegingen gedaan.
Onder andere ondersteuning van JSON, "core functies" en tijdgerelateerde data.
Hierbij de lijst zoals die getoond wordt:
- ISJSON
- JSON_PATH_EXISTS
- JSON_OBJECT
- JSON_ARRAY
- GREATEST
- LEAST
- STRING_SPLIT
- TRIM functions
- DATETRUNC
- IS [NOT] DISTINCT FROM
- WINDOW clause
- LEFT_SHIFT
- RIGHT_SHIFT
- BIT_COUNT
- GET_BIT
- SET_BIT
- DATE_BUCKET
- GENERATE_SERIES
- FIRST_VALUE
- LAST_VALUE
In het laatste scherm, de samenvatting worden nog een aantal linkjes gedeeld. Mogelijk handig om later nog terug te lezen:
Keep up with all the latest on SQL Server 2022: link.
Get all the details about SQL Server 2022 in the official documentation: link.
Download and try out SQL Server 2022: link.
Read details about SQL Server 2022 new features from the engineering team blog: link.
Get more materials from the self-paced or instructor led SQL Server 2022 workshop: link.