Magnus Ahlkvist

In this one hour session, you will learn the basics about SQL Server database projects (SSDT and/or SDK Style projects). Topics covered: - State based vs migration based deployment - How Microsoft has implemented support for state based deployment using SQL Server database projects in Visual Studio.

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?".

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.

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.

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.

I am a SQL Server DBA. I don't know how it happened, I just know I used to be a Windows developer and now I am a SQL Server DBA. This means I spent years and years learning how to use tools like SQL Server Agent and SQL-scripts to (sort of) automate tasks like index maintenance, backups, restore tests and more. The same way I don't really know how or when I transitioned from developer to DBA, I'm not sure when it happened that I almost stopped using SQL Server Management Studio. Instead I find myself using PowerShell for just about everything SQL Server related. In this session, I will show some examples of how to combine meta data repos in git, Azure Pipelines and self-service api's to manage a large number of databases in a large number of production and test environments. All of course driven by PowerShell and some fantastic modules developed by the PowerShell community. The key is meta data. By documenting your servers and your SQL Server instances, automation and abstraction is possible. This gives you as an admin or DBA more freedom to move things around without ruining the day for your developers or users.

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.

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.