SQL KONFERENZ 2023
September 11-13
Congress Park Hanau
PreCon Workshops
Monday, 11. September 2023
PreCon Workshops
Bob Ward, Principal Architect Microsoft Azure Data | Buck Woody, Principal Applied Data Scientist
Rie Merrit, MVP Data Platform Lead | Ben Weissman, Microsoft Data Platform MVP
The SQL Server 2022 Workshop - Room 5
Immerse yourself in this day-long workshop on SQL Server 2022 including presentations, hands-on exercises, and interactive Q&A taught by the Microsoft engineering team. You will learn all aspects of what is new in SQL Server 2022 including features for hybrid, built-in query intelligence, industry-proven engine, data virtualization and object storage, and new T-SQL capabilities. Attendees will need to bring their own laptop running Windows and will be provided instructions on software to be pre-installed including SQL Server 2022 Evaluation Edition and tools like SQL Server Management Studio. All attendees are expected to have a fundamental working knowledge of SQL Server including the T-SQL language, engine fundamentals, and use of tools like SQL Server Management Studio (SSMS). If you are looking to dive deep into “everything” SQL Server 2022 this workshop is for you.
Mo 10:00 – 17:00
English
Welcome to the SQL Server 2022 Workshop, a complete hands-on interactive experience to learn all aspects of SQL Server 2022.
Prerequisites – Before you attend the workshop, download all the scripts and files for hands-on exercises with one of the following methods:
- Clone the workshop repo with git clone https://github.com/microsoft/sqlworkshops-sql2022workshop.git. Using this method, the scripts will be under folders by module inside sqlworkshops-sql2022workshop\sql2022workshop. You can download git for windows from https://gitforwindows.org.
- download a zip file of the scripts from https://github.com/microsoft/sqlworkshops-sql2022workshop/archive/refs/heads/main.zip. You will need to expand the zip file after downloading. Using this method the scripts will be under folders by module inside \sqlworkshops-sql2022workshop-main\sql2022workshop.
In order to complete the exercises in this workshop you will need the following:
+ A virtual machine or computer running Windows 10, Windows 11, Windows Server 2019, or Windows 2022 with at least 4 CPUs and 8Gb RAM. You will need Administrator rights on the virtual machine or computer. Exercise 3.2 is an advanced exercise in Module 3 that requires 8 CPUs and 24Gb RAM and is not required for attendees and is optional. You can use your computer, local virtual machine, or virtual machine in Azure. NOTE for Linux users: You will be able to go through many of the exercises using Linux as your client and SQL Server. However, some exercises require ostress.exe which is a Windows only program. You can also connect with a Windows client to SQL Server on Linux or a SQL Linux container.
+ You will not need an Azure subscription. Module 2 will be done through demonstrations only. Attendees can go through exercises in Module 2 after the workshop.
- SQL Server 2022 Evaluation Edition from https://aka.ms/getsqlserver2022 or Developer Edition (TCP protocol enabled) with the Database Engine feature installed. o Exercises in Module 5 require the PolyBase Query Service for External Data Feature (using all defaults). IMPORTANT: If you have an instance from a previous version of SQL Server already on your computer or VM that has the Polybase feature enabled you will have to remove that feature from that instance or uninstall that instance. If this is not possible.
You must configure SQL Server for mixed mode authentication. Read more how to do this at https://learn.microsoft.com/sql/relational-databases/security/choose-an-authentication-mode.Named instances are supported but you may need to make some edits to some scripts in some modules where the exercise have you connect to SQL Server.
- Install SQL Server Management Studio (SSMS) latest 18 or 19 build from https://aka.ms/ssms18 or https://aka.ms/ssms19. You may have a better experience in some modules with the new features of SSMS 19.
- Install Azure Data Studio (ADS) from https://aka.ms/azuredatastudio. T-SQL notebooks are used in this course.
- Download the following sample backup files. DO NOT restore these backups. You will do this as part of the exercises in the workshop.
For Exercise 3.0 in Module 3 you will need to download a customized version of the WideWorldImportersDW sample database from https://aka.ms/wwidw_mgf.
For Exercise 3.1 in Module 3 you will need to download a customized version of the WideWorldImporters sample database from https://aka.ms/wwi_pspopt.
For Exercise 3.2 in Module 3 you will need to download a customized version of the WideWorldImporters sample database from https://aka.ms/wwi_dop.
- Download ostress.exe by downloading RML utilities from https://aka.ms/ostress. Install using the RMLSetup.msi file that is downloaded. Use all defaults.
More information
Keep up with the latest information on the workshop at https://aka.ms/sql2022workshop. Please send any questions about the workshop at bobward@microsoft.com or mamathews@microsoft.com.
The SQL Server 2022 Workshop
Adam Saxton (Microsoft) | Patrick LeBlanc (Microsoft)
CANCELLED: Power BI hidden gems
Power BI has many capabilities for business analytics not just for reporting but also managing your data and allowing for scale of items. In this demo-heavy day, you will learn about hidden items that most folks aren’t aware of or just don’t think about. You will learn about how these items work and understand how they can help you leverage them in your solution.
Areas looked at:
- Power Query
- Data modeling
- DAX
- Reports
- Admin and governance
- And more
Mo 10:00 – 17:00
English
Power BI Customer Advisory Team (CAT) | Gabi Münster (Microsoft) | Kasper de Jonge (Microsoft) | Luke Moloney (Microsoft)
Microsoft Fabric – What should you know? - Room 4
Microsoft Fabric is here, and it fundamentally changes our data landscape. It introduces a Power BI like SaaS model for your complete data platform, you now have your Power BI Datasets, OneLake, Lakehouse and Datawarehouse all in a single tenant, with single security and as a SaaS platform. What does this all mean for me as data engineer working with Azure Synapse or Databricks, or as a Power BI developer? What do all these new workloads do? How do I choose? What architecture should I choose? How do I govern and secure it and how does it all tie in together?
In this precon we will answer all these questions and more. After starting with a general overview of Fabric we will dive into each workload in demo heavy sessions. The first part we will be diving into is the hearth of Fabric called OneLake. What is it and how can I secure it. Then we move on to loading data through pipelines, dataflows or spark followed by the logical layers on top: Lakehouse and Warehouse. What are they , how do I choose and what is the preferred architectures will be discussed and shown. We will then visualize this data with Power BI using the new functionality of DirectLake. How does it work and what are some important gotcha’s.
Finally you will want to ensure that your data is secure and compliant in Fabric? We will answer these questions and more. We will show you:
- How do users authenticate, securing it with AAD and external users
- Where and how your data is stored
- How to handle data sovereignty and compliance
- Understand network security both inbound and outbound
- How to administer Fabric as tenant admin
- How to integrate with Purview
- How to govern the usage of data within Fabric.
By the end of this day you will have a clear understanding of what Fabric is and gets you started on your Fabric data journey. Join us and discover the secrets of Fabric!
Mo 10:00 – 17:00
English
Attendee recommendation:
Attendees are expected to have a basic knowledge of:
- ETL and data transformation processes.
- The fundamentals of a star schema including dimensions and facts.
- SQL querying experience (experience writing SQL transformations is not required)
- Basic Azure knowledge of cloud fundamentals (IaaS, PaaS and SaaS) and some Azure core concepts.
Hands-on experience with Azure Synapse and Microsoft certifications are not required.
It is helpful if attendees have knowledge and/or experience with the following:
- Azure Data Factory
- Azure SQL, Azure Synapse SQL, or other Azure database offering
- Power BI
Power BI and Synapse combined - What should you know
DBA in Multi-Galaxien - Room 2
Unsere Welt als DBA war „in Ordnung“, als wir nur eine Umgebung kannten, die OnPrem-Szenarien. Die Welt der Datenbanken hat sich rapide geändert, aber hat sich auch die Welt der DBA verändert?
Dieser Workshop fokussiert auf unsere Arbeit als DBA mit verschiedenen Paradigmen (Cloud/OnPrem) sowie der Gegenüberstellung von verschiedenen Datenbankbanksystemen (SQL Server/Postgres).
- Installation / Konfiguration
- Monitoring
- Backup / Restore
- „Gestern lief die Abfrage aber schneller!“ – warum Abfragen plötzlich langsam werden
- Parameter Sniffing
- Falsche Statistiken
- Query Store (SQL Server)
- Analyse von Wartevorgängen
- Indexierung – Fluch und Segen eines DBA
- Wie erkennt man schlechte Ausführungen von Abfragen
- Welche Indexe sind performant
- Welche Indexe fehlen
- Analysewerkzeuge für Performance-Messungen
- PERFMON
- Extended Events/Profiler
- Portal(e)
- Hochverfügbarkeitslösungen
- Clusterbetrieb
- AlwaysOn Availabilty Groups (SQL Server)
- Replikation
- Q & A
Mo 10:00 – 17:00
Deutsch
SQL Konferenz 2023 in Hanau
Gerhard Brückl, Microsoft MVP | Evandro Muchinski, Microsoft Cloud Solution Architect
Azure Databricks - Workshop - Room 3
Databricks und das Konzept, ein Data Warehouse und einen Data Lake zu einem Data Lakehouse zu verbinden, wird bereits von vielen Unternehmen erfolgreich umgesetzt. In diesem Workshop werden wir erläutern, warum Azure Databricks so erfolgreich ist und die zugrundeliegenden Konzepte und Technologien näher beleuchten. Dazu gehören unter anderem natürlich etablierte Open Source Standards wie Apache Spark und Delta Lake aber auch der Databricks Unity Catalog und Delta Live Tables. An praktischen Beispielen wird gezeigt, wie mit Hilfe von PySpark und SparkSQL klassische DWH und Data Lake Anforderungen auf Azure Databricks umgesetzt und erste Use-Cases realisiert werden können. Es entsteht ein guter Einblick in die Welt des Data Engineerings welcher weiters mit Best Practices und wertvollen Erfahrungen aus der Praxis abgerundet wird.
Der Workshop ist für alle angehenden und fortgeschrittenen Data Engineers geeignet, die mit Databricks ihre nächste Datenplattform in der Microsoft Azure Cloud aufbauen möchten.
- Apache Spark
- PySpark und SparkSQL
- Delta Lake
- Unity Catalog
- Streaming und Delta Live Tables
Mo 10:00 – 17:00
Deutsch