Most Active Speaker

Magnus Ahlkvist

Magnus Ahlkvist

SQL Server specialist, Data Platform MVP, SQL Community organizer. Paid work out of Transmokopter SQL AB.

Enköping, Sweden

Magnus works as an independent SQL Server consultant at his company Transmokopter SQL AB, with everything from on-premise to cloud databases, from database design and T-SQL programming to infrastructure planning and administration, from transaction intensive databases to data warehousing workloads.
When paid work stops, community work starts. Magnus is a long time co-leader for SQL Server User Group Sweden, he's organising SQL Friday every Friday noon and he's speaking and volunteering on data events, small and large. In October 2020, Magnus was awarded the Microsoft Data Platform MVP award.

Awards

Area of Expertise

  • Information & Communications Technology

Topics

  • SQL Sever
  • performance tuning
  • Database Administration
  • T-SQL
  • Data Platform
  • Microsoft Data Platform
  • PowerShell

Test Driven Database Development

Test Driven Development, or TDD, means we start with unit tests, even before writing our code. When done properly, TDD leads to higher quality systems. It gives us good test coverage, it's automated and it gives us good regression testing.

To isolate the unit tests, so that they only verify the behaviour of an invidiual method or function, everything outside of the function is mocked. This is a really good idea. If we write a function ConvertCurrency and that function calls service GetCurrencyRate(date,currency), create a mock service which always returns the same value.

How about unit tests for the service GetCurrencyRate? The service probably gets the currency rate for a certain date/currency combination from a database, perhaps through the stored procedure call Finance.GetCurrencyRate. And just like the unit tests for ConvertCurrency uses a mock service for GetCurrencyRate, the unit test for GetCurrencyRate will use a mock for the stored procedure Finance.GetCurrencyRate.

But who will test the stored procedure Finance.GetCurrencyRate?

In this session we will talk about test driven database development. How we can apply the TDD methodology to database development. We will look at the most common unit testing frameworks and how they integrate with database development environments. We will look at unit test design and how we can isolate unit tests to only test the logic of an individual statement, view, stored procedure or user defined function.

New and improved features in SQL Server 2022

On May 24, Microsoft announced the public preview of SQL Server 2022. In this session, we will look at the new and the improved features in SQL Server, including but not limited to:

- Azure SQL Managed Instance link - Use Azure as your DR "site", or use this feature to migrate your on-prem SQL Server to Azure SQL Managed Instance.
- New programmability features in T-SQL
- New and improved Intelligent Query Processing features.

Everything you need to know about Temporal Tables

Temporal Tables (or system versioned tables) were introduced in SQL Server 2016 as a way to automatically version rows within a table.

In this session I will present:
- Create a new table which is system versioned
- Start system versioning on existing tables.
- Transform a main/history-table pair from application versioned to system versioned.
- How to query using the SYSTEM TIME AS OF extensions to SELECT queires.
- How to use the Row Start and Row End attributes for delta loads into a data warehouse.
- How to load Type 2 Slowly Changing Dimensions using system versioned tables
- Performance considerations for your history table.

DBA? Sure. But you're also a developer

I accidentally became a DBA over 20 years ago. My responsibilities are pretty much the same today as then:
1) Backup the database.
2) Move the backup file as far away from the data center as you possibly can.
3) If you have time, improve things, work on security, help developers tune their queries, create some indexes. But never forget 1 and 2.

However, the tools I use are slightly different today than they were 20+ years ago. What I did in Enterprise Manager can today be automated. Microsoft developed Sql Server Management Objects (SMO) and PowerShell. Chrissy LeMaire and a whole bunch of amazing individuals give us dbatools for free, VSCode is a thing, git repositories are literally just one click away etc.

In other words - if you used to be a DBA, you're now a developer.

In this session, we will go through some essential tools for DBAs:
- git
- vscode
- dbatools
- release pipelines

Containerize your application and SQL databases

With SQL Server running on Linux and .NET Core being a cross platform framework, it's now possible to run what used to be windows only systems on pretty much any platform. Using containers, we can be up and running with the infrastructure for a whole system within minutes.

In one hour, I will show you a couple different ways to configure containers for a data driven application and some ways to deploy the application. You will learn how to add functionality to existing container images. We will deploy systems to our own laptop and to Azure Container Instance and we will briefly look at network security inside a container group.

This session is focused on test environments most of all, but much of what is being said is valid for production environments as well, only you'd have to think a bit more about redundancy, persistance and security in production.

You don't need to know much about containers to appreciate the contents of this session. But it's good if you have at least a hunch about application development, and some knowledge about SQL Server.

Build an Availability Group in ten minutes using SQL containers

If you're using availability groups with readable secondaries, you probably want readable secondaries in dev, test, system test, acceptance test and production. However, duplicating the infrastructure to make that possible may or may not be exactly what your employer or client wants to pay for.

In this 10 minute lightning talk, I will create two SQL containers, within the same docker network, and create a clusterless availability group with one container as primary and one container as readable secondary, and run a simple PowerShell test-client to run queries against the write-replica and the read-replica.

Since it's a 100% scripted solution, it will also work to create this AG in a pipeline, so that you can create it, run automated tests and throw it away after tests have been run, to be able to run unit tests and low level system tests automagically.

RBAR Bad. Sets good.

I will show some way too common uses of RBAR (Row by Agonizing Row) processing and show different examples of how they can be replaced with a set based approach.
We will look at performance comparisons and talk about code maintainability. Most of the examples are war stories from my 20 years working with databases. I may or may not be responsible for many of the bad examples myself.

Are you drunk SQL Server? Where did that estimate come from?

If you write T-SQL queries on a regular basis, you will have seen unexpected query execution plans, making you wonder "Hmm.. Why did SQL Server decide to make one million lookups against this 100 row table instead of just scanning the whole thing once?".

In this session, we will look at some really strange rowcount estimates and figure out how SQL Server came up with them.

Statistics, an unreliable friend

You learned that your statistics should be regularly updated. You even implemented Ola Hallengren's maintenance scripts. That should be enough, right? What if it's not?
Join me on some head-ache, a dive into statistics histograms, fun with flags and a praise to the evolution of SQL Server optimizer.
Much of this session is about the "ascending key problem", and how Microsoft have made shanges to the SQL Server optimizer to improve, but not completely eliminate the problem.

My romantic relation with Powershell generally and DBATools especially

I was very late to adopt Powershell in my day-to-day work as a DBA. I did some copy/change "programming" with Powershell. But it wasn't until I got in touch with DBATools that I really understood the power of Powershell.
In this session, I will demo some of the reasons I really love Powershell and DBATools.
I will cover a brief overview of DBATools and then jump into some scenarios where DBATools really helped me, including:
- Automatic restore testing
- Homecooked log shipping
- Migration work

Eight hours of work in 20 minutes.

A case study of how a monthly data load has evolved, from just loading data into a table, through some indexes being disabled before loading and enabled after data is loaded, to partitioning the table and loading into a staging table. The last changes to the solution has taken loading time from eight hours down to 20 minutes, saving a whole day of work for the staff checking and refining the data.
The presentation also includes some reflections on how partitioning the table has changed how both T-SQL and Entity Framework code is being written, to take advantage of partition elimination.

Data Saturday Oslo 2023 Sessionize Event

September 2023 Oslo, Norway

Data Saturday Gothenburg 2023 Sessionize Event

August 2023 Göteborg, Sweden

Data Saturday Rheinland 2023 Sessionize Event

June 2023 Sankt Augustin, Germany

Data Saturday Stockholm 2023 Sessionize Event

May 2023 Stockholm, Sweden

Data Saturday Oslo 2022 Sessionize Event

September 2022 Oslo, Norway

Data Saturday Gothenburg 2022 Sessionize Event

September 2022 Göteborg, Sweden

DataGrillen 2022 Sessionize Event

June 2022 Lingen, Germany

Data Saturday Stockholm 2022 Sessionize Event

May 2022 Stockholm, Sweden

PASS Data Community Summit 2021 Sessionize Event

November 2021

Data Saturday Oslo - Virtual Sessionize Event

September 2021

Virtual 2021 Data.SQL.Saturday.LA Sessionize Event

June 2021

Malta Data Saturday 2021 Sessionize Event

April 2021

Data Saturday #5 Redmond 2021 Sessionize Event

April 2021

SQL Server Virtual Conference Sessionize Event

January 2021

Humanitarian conference for earthquake victims in Croatia Sessionize Event

January 2021

SG SQLPASS #DataSessions Season 2 Sessionize Event

October 2020

Magnus Ahlkvist

SQL Server specialist, Data Platform MVP, SQL Community organizer. Paid work out of Transmokopter SQL AB.

Enköping, Sweden