Magnus Ahlkvist
SQL Server specialist, Data Platform MVP, SQL Community organizer. Paid work out of Transmokopter SQL AB.
Enköping, Sweden
Actions
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.
Links
Awards
Area of Expertise
Topics
SQL Server Worst Practices
My Database Journey started with Borland Paradox, via Microsoft Access to SQL Server. I wasn’t very good with Paradox. Somewhere while working with Access I think I understood the basics of data modelling for an rDBMS. I got things to work, but seriously I wasn’t very good. Moving over to SQL Server made me realise something. SQL and SQL isn’t always the same. And since I didn’t even understand best practices in MS Access, my knowledge didn’t really translate well to SQL Server.
This session is about mistakes and misunderstandings. Most of the mistakes and misunderstandings are my own. Having misunderstood things, leading to having made mistakes, is part of the reason I now consider myself pretty good with SQL Server.
What you will get if you attend this session is:
- Me making fun of myself and my mistakes and misunderstandings.
- Consequences. Real ones and theoretical ones.
- How I should have done instead (and how you should do to avoid making the same mistakes I did)
Automation for DBAs and database developers - One Day Training Day
When I started as a developer and an accidental DBA, most of my administration routines and my deployments to production were done manually. Tests were more or less exclusively end-to-end-tests, performed by clicking in the UI.
Since then a lot of things happened. When DBAs used to take care a dozen SQL instances, they now have hundreds. Databases grew bigger and code is deployed to production much more often.
On this training day, I will share my experience with automation for DBAs and database developers. This includes:
- Automating SQL Server health checks using PowerShell, dbatools and dbachecks
- Database projects to enable CI/CD for SQL Server databases
- Azure pipelines for automation and scheduling
- Automated database tests using containers and database projects and tSQLt.
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
Data Saturday Gothenburg 2023 Sessionize Event
Data Saturday Rheinland 2023 Sessionize Event
Data Saturday Stockholm 2023 Sessionize Event
Data Saturday Oslo 2022 Sessionize Event
Data Saturday Gothenburg 2022 Sessionize Event
DataGrillen 2022 Sessionize Event
Data Saturday Stockholm 2022 Sessionize Event
PASS Data Community Summit 2021 Sessionize Event
Data Saturday Oslo - Virtual Sessionize Event
Virtual 2021 Data.SQL.Saturday.LA Sessionize Event
Malta Data Saturday 2021 Sessionize Event
Data Saturday #5 Redmond 2021 Sessionize Event
SQL Server Virtual Conference Sessionize Event
Humanitarian conference for earthquake victims in Croatia Sessionize Event
SG SQLPASS #DataSessions Season 2 Sessionize Event
Magnus Ahlkvist
SQL Server specialist, Data Platform MVP, SQL Community organizer. Paid work out of Transmokopter SQL AB.
Enköping, Sweden
Links
Actions
Please note that Sessionize is not responsible for the accuracy or validity of the data provided by speakers. If you suspect this profile to be fake or spam, please let us know.
Jump to top