# Kay On Data > Easier insights with better data, automatization and thought leadership Public Ghost content for AI and LLM tooling. This file includes a bounded export of public pages first, then recent public posts. Append `.md` to any post or page URL to get the content in Markdown (for example, `/example-post.md`). ## Pages ### About Me URL: https://www.kayondata.com/about/ Last updated: 2026-06-14T22:25:30.000Z I help organizations connect reliable data platforms to better business decisions. My current title is MS SQL DBA at a major Swiss bank, and I have worked with data since 2008. My current role is MS SQL DBA, but my work reaches beyond database administration into data strategy, data engineering, and applied data science. I spend most of my time with SQL Server, Azure SQL, Azure SQL Managed Instance, PostgreSQL, and Microsoft Fabric, designing reliable, automated, and scalable data solutions that help people make decisions from better evidence. Language-wise, I am most at home with T-SQL, Python, PySpark, SparkSQL, PowerShell, and R. Automation is a recurring theme in my work. During my years working extensively with SQL Server, I built and maintained automated database and VM environments using PowerShell, and I still recommend [dbatools](https://dbatools.io/?ref=kayondata.com) to anyone working seriously in that space. My strongest interest is the strategic side of data: how quality, architecture, automation, governance, and analytics come together so that humans and AI can rely on data with confidence. I describe that advisory focus on the [Advisory](https://www.kayondata.com/advisory/) page. Spoken languages: English, German and Swiss German. ## Education I hold a Master of Advanced Studies (MAS) in Business Intelligence from [HWZ Zurich](https://fh-hwz.ch/en?ref=kayondata.com). My 2024 diploma thesis explored financial reports with generative AI: possibilities and limits, using Microsoft Fabric, a RAG model, and GPT-3.5. ## Community And Writing I am a [Microsoft MVP](https://mvp.microsoft.com/en-US/MVP/profile/9e29f6c8-c755-ed11-9561-000d3a197333?ref=kayondata.com), a [Redgate Community Ambassador](https://www.red-gate.com/hub/community/ambassadors/ambassador/kay-sauter/?ref=kayondata.com), a [public speaker](https://www.kayondata.com/community-engagements/), and an active community organizer. I founded the [Azure Data User Group Data TGIF](https://www.meetup.com/data-tgif/?ref=kayondata.com), I am one of the founders of [DATA BASH](https://databash.live/?ref=kayondata.com) and Data Platform Conference Switzerland, and I am involved with the [Data Platform Diversity, Equity, and Inclusion Virtual Group](https://www.dataplatformdei.com/?ref=kayondata.com), supporting a more inclusive and diverse data community. I also write regularly about data, engineering, and analytics here on this blog, on [Substack](https://substack.com/home/post/p-190323655?ref=kayondata.com), and as a [LinkedIn newsletter](https://www.linkedin.com/newsletters/kay-on-data-7043592227159207936?ref=kayondata.com). Sharing knowledge, discussing code, and visualizing data are some of my favorite ways to contribute to the community. ## Accessibility This website is built with accessibility in mind, including clear structure, readable contrast, descriptive image text, and keyboard-friendly links. If you notice anything that makes the site harder to use, please use the contact form so I can improve it. ## Beyond Data I am married and live in Zurich, Switzerland. My wife and I are avid travelers, always curious to explore new cultures, perspectives, and ideas. That mindset naturally carries over into my professional life: better data strategy starts with understanding people, context, and the decisions they need to make. ## Talk To Me For advisory work around data strategy and data quality, start with the [Advisory](https://www.kayondata.com/advisory/) page. For speaking, community work, or accessibility, use the [contact form](https://www.kayondata.com/contact/). You can contact me in English or German; my native language is Swiss German. ### Advisory URL: https://www.kayondata.com/advisory/ Last updated: 2026-06-14T22:18:27.000Z ## Data Strategy That Leads To Better Decisions Modern data platforms are rarely limited by the tools alone. The harder question is whether the organization can trust the data, understand the trade-offs, and turn analytics into decisions that move the business forward. My advisory work is for teams that need a clearer path: what to improve first, which platform choices are worth making, where quality issues are created, and how to make data useful evidence for both people and AI. ## Where I Can Help I work with organizations that want to make their data platforms more reliable, more understandable, and more useful for decision-making. - Data strategy and roadmap reviews - Data quality and trust assessments - Microsoft Fabric, SQL Server, Azure SQL, Azure SQL Managed Instance, and PostgreSQL advisory - Analytics architecture and platform modernization - Automation opportunities for data engineering and operations - Practical governance that supports delivery instead of slowing it down - Readiness for AI-supported analytics and decision processes ## My Perspective I have worked with data since 2008, across database administration, business intelligence, data engineering, automation, and applied data science. My background is hands-on and technical, but the advisory focus is strategic: how data platforms become useful evidence for the business. Good data strategy is not only about choosing technology. It is about making clear decisions on ownership, quality, reliability, automation, documentation, and how people actually use data in their daily work. For the personal background behind this perspective, see [About me](https://www.kayondata.com/about/). I bring a pragmatic view from hands-on implementation and community work. I care about solutions that can be operated, explained, improved, and trusted. Education note: Master of Advanced Studies (MAS) in Business Intelligence, [HWZ Zurich](https://fh-hwz.ch/en?ref=kayondata.com). My 2024 diploma thesis examined financial reports with generative AI on Microsoft Fabric, using a RAG model and GPT-3.5. ## Typical Questions - Can we trust the data behind our reporting and AI initiatives? - Where are quality problems created, and how can we prevent them earlier? - Is our data platform helping business teams make decisions, or mainly producing more dashboards? - What data architecture do we need? - What should move to Microsoft Fabric, Azure SQL, or another platform, and what should stay where it is? - Where would automation reduce risk, manual work, or recurring incidents? - Are we doing enough to protect sensitive data? - How do we design governance that people will actually follow? - What does a realistic data roadmap look like for the next 6 to 18 months? ## How An Advisory Engagement Can Look Advisory work can be small and focused, or broader depending on what you need. A useful starting point is often a structured review of your current data landscape, pain points, decision processes, and platform direction. From there, I can help you identify priorities, risks, quick wins, and longer-term architectural choices. The outcome should be understandable for both technical teams and decision-makers. Possible formats include: - A focused strategy call - A platform or data quality review - A workshop with technical and business stakeholders - A roadmap review - A second opinion on a planned architecture or migration - Support for internal presentations about data strategy, quality, or accessibility ## Let’s Talk If you want to discuss data strategy, data quality, Microsoft data platforms, or how to make analytics more useful for real decisions, use the [contact form](https://www.kayondata.com/contact/). ### Archive URL: https://www.kayondata.com/archive/ Last updated: 2026-06-14T22:18:47.000Z Browse Kay On Data articles in chronological order. ### Community Engagement + Speaking URL: https://www.kayondata.com/community-engagements/ Last updated: 2026-06-14T22:18:57.000Z Speaking, organizing, writing, and building community help me connect technical depth with practical decisions. My public work focuses on data quality, Microsoft data platforms, automation, inclusion, and trustworthy analytics for humans and AI. I contribute through talks, user groups, conferences, newsletters, and community organizing. The common thread is simple: better data work should lead to clearer decisions, stronger teams, and more inclusive technical communities. I founded Data TGIF, and I am one of the founders of DATA BASH and Data Platform Conference Switzerland. I am also a Microsoft Data Platform MVP and a [Redgate Community Ambassador](https://www.red-gate.com/hub/community/ambassadors/ambassador/kay-sauter/?ref=kayondata.com). ## Where I Contribute - [Microsoft MVP Profile](https://mvp.microsoft.com/en-US/MVP/profile/9e29f6c8-c755-ed11-9561-000d3a197333?ref=kayondata.com) \- recognition for community contribution in the Microsoft Data Platform space. - [Redgate Community Ambassador](https://www.red-gate.com/hub/community/ambassadors/ambassador/kay-sauter/?ref=kayondata.com) \- part of Redgate's Ambassador community for data platform professionals. - [Data TGIF](https://www.meetup.com/data-tgif/?ref=kayondata.com) \- Azure Data User Group meetup I founded for practical data platform conversations. - [DATA BASH](https://databash.live/?ref=kayondata.com) \- a community conference for data professionals, engineers, and analytics practitioners that I co-founded. - [Data Platform Conference Switzerland](https://databash.live/?ref=kayondata.com) \- a Swiss data platform conference I co-founded. - [Data Platform Diversity, Equity, and Inclusion Virtual Group](https://www.dataplatformdei.com/?ref=kayondata.com) \- supporting a more inclusive and diverse data community. - [LinkedIn Newsletter](https://www.linkedin.com/newsletters/kay-on-data-7043592227159207936?ref=kayondata.com) \- Kay On Data posts and reflections shared with the LinkedIn community. - [Sessionize](https://sessionize.com/kaysauter?ref=kayondata.com) \- my speaker profile and session history. ## Engagement History | Date | Place | Description | Link | | ---------- | ------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 22.10.2025 | In person | Organizing event: Data Platform Conference Switzerland | [databash.live](https://databash.live/?ref=kayondata.com) | | 27.09.2025 | Online | Organizing event: DATA BASH | [databash.live](https://databash.live/?ref=kayondata.com) | | 12.07.2025 | Online | Speaking: Microsoft Fabric Notebook Deep Dive | [Data Toboggan](https://www.meetup.com/data-toboggan/events/308480229/?ref=kayondata.com) | | 10.05.2025 | Online | Speaking: CSV data: easy ingesting, fixing, and monitoring with PySpark | [Microsoft Analytics Community Conference](https://www.meetup.com/india-microsoft-fabric-user-group/events/305970460/?eventOrigin=group%5Fevents%5Flist&ref=kayondata.com) | | 27.02.2025 | Online | Speaking: DevOps, unit testing, and fast load failure fixing with notebooks | [Power BI & Fabric Summit](https://globalpowerbisummit.com/?ref=kayondata.com) | | 26.02.2025 | Online | Speaking: Automate file loads into Microsoft Fabric with PySpark: logging, monitoring, fixing, and more | [Power BI & Fabric Summit](https://globalpowerbisummit.com/?ref=kayondata.com) | | 25.02.2025 | Online | Panel participant: Master admin and governance for Microsoft Fabric | [Power BI & Fabric Summit](https://globalpowerbisummit.com/?ref=kayondata.com) | | 20.02.2025 | Online | Speaking: Automated SQL Server VM deployment on Azure with PowerShell | [GLASS](https://www.meetup.com/glass-greater-lansing-area-for-sql-server/events/306005387/?eventOrigin=group%5Fupcoming%5Fevents&ref=kayondata.com) | | 26.10.2024 | Zurich | Organizing event: Data Platform Conference Switzerland | [Data Platform Conference Switzerland](https://databash.live/?ref=kayondata.com) | | 19.10.2024 | Online | Organizing event and moderating: DATA BASH 2024 | [DATA BASH](https://databash.live/?ref=kayondata.com) | | 30.07.2024 | Online | Speaking: Data Exposed: Generate a report with generative AI | Data Exposed MVP Edition | | 10.07.2024 | Online | Speaking: Deep dive into the ARM structure with PowerShell | Database Professionals Virtual Meetup Group | | 07.07.2024 | Online | Speaking: Querying in Fabric: similarities and differences to SQL Server | Microsoft Excel and Data Analysis Learning Community | | 17.04.2024 | Online | Moderating: Microsoft Fabric Learn Together: Use Apache Spark in Microsoft Fabric | YouTube | | 24.01.2024 | Online | Moderating: Microsoft Fabric Learn Together: Use Apache Spark in Microsoft Fabric | YouTube | | 25.11.2023 | Online | Organizing: DATA BASH, the online live conference for DACH and the world | Welsh Azure User Group | | 08.08.2023 | Edinburgh | Speaking: AI in use for the hearing impaired and deaf people: new frontiers and limits | Data Scotland | | 23.06.2023 | Online | Speaking: Deploy SQL to Azure with PowerShell, Bicep, or Terraform | Azure User Group Iceland | | 21.06.2023 | Online | Speaking: Deploying a SQL Server VM with PowerShell: Wales edition | Welsh Azure User Group | | 17.06.2023 | Bonn, Germany | Speaking: A guide to move your on-prem SQL Server databases to Azure | Data Saturday Rheinland | | 09.05.2023 | Online | Hosting Data Platform Diversity, Equity, and Inclusion Virtual Group talk by Randolph West | Data Platform DEI | | 05.05.2023 | Online | Co-organizer of DPWIT/DEI Mental Health and Wellness Day conference | DPWIT/DEI Mental Health and Wellness Day | | 30.04.2023 | Seattle / Podcast | Interview by Ralph Richter for his podcast MVP Voices | Spotify | | 28.04.2023 | Seattle / YouTube | Interview by Microsoft's Global AI Community Channel during MVP Summit for Humans in AI | YouTube | | 13.04.2023 | YouTube chat | YouTube chat for Finding (Data) Friends, Episode 008 | YouTube | | 18.03.2023 | Newport, Wales, UK | Speaking: From stone age to data-driven tech for ears: a time travel | SQLBits | | 28.02.2023 | Online | Speaking: Deploying SQL Server on Azure VM with PowerShell | Microsoft Data and AI South Florida User Group | | 13.02.2023 | YouTube | Interview by Christian Buckley for #MVPbuzzChat | YouTube | | 08.02.2023 | Online | Speaking: Deploying a SQL Server VM with PowerShell | Southampton Data Platform and Cloud User Group | | 10.09.2022 | Online | Organizing event and moderating: DATA BASH | DATA BASH | | 14.05.2022 | Online | Speaking and volunteering for moderation support. Talk with Deepthi Goguri: Building the bridge from Azure fundamentals to deploying at scale through automation | DataWeekender | | 27.04.2022 | Online | Speaking: Automated provisioning of SQL Server VMs on Azure | Calgary Data User Group | | 29.03.2022 | Online | Speaking: Deploy a SQL Server VM on Azure with PowerShell | M365 North User Group | | 19.12.2021 | Online | Speaking: Automated provisioning for Power BI VMs on Azure | Power BI User Group Karachi | | 20.11.2021 | Online | Co-organizer and moderation of Power BI Fest | Power BI Fest 2021 | | 10.11.2021 | Online | Speaking: Accessibility for hard of hearing and deaf people, together with Craig Porteous | PASS Data Community Summit | | 29.09.2021 | Online | Keynote and moderation | Future Driven Data Summit 2021 | | 16.09.2021 | Online | Chat with conference attendees | Data Platform Summit 2021 | | 15.09.2021 | Online | Member of DEI panel discussion | Data Platform Summit 2021 | | 13.09.2021 | Online | Speaking: Automated provisioning of SQL Server VMs on Azure | Data Platform Summit 2021 | | 18.08.2021 | Online | Speaking: Why closed captions in your public speaking may make a difference | Cloud With Chris | | 13.08.2021 | Online | Speaking: Automated provisioning of SQL Server VMs on Azure | Dativerse | | 15.05.2021 | Online | Speaking and moderating: Automated provisioning of SQL Server VMs on Azure | DataWeekender | | 17.10.2020 | Online | Moderating | DataWeekender | | 02.05.2020 | Online | Moderating | DataWeekender | ## Topics - Data strategy that links platforms to business outcomes. - Data quality for humans, analytics, and AI. - Microsoft Fabric, SQL Server, Azure SQL, and automation. - Community conferences, user groups, and inclusive spaces for data professionals. ## Contact For advisory work around data strategy, see [Advisory](https://www.kayondata.com/advisory/). For speaking or community work, use the [contact form](https://www.kayondata.com/contact/). ### Contact me URL: https://www.kayondata.com/contact/ Last updated: 2026-06-14T22:16:10.000Z Use this form for advisory work, speaking, live captions, community work, or thoughtful blog feedback. It keeps my email address off the public page while still giving you a direct way to reach me. Tell me briefly who you are, what you are working on, and what kind of conversation would be useful. I can reply in English or German; my native language is Swiss German. The contact form is the preferred route. If it is unavailable or you prefer LinkedIn, you can also send me a private message there: [https://www.linkedin.com/in/kaysauter/](https://www.linkedin.com/in/kaysauter/?ref=kayondata.com) ### Live Captions Should Be Normal URL: https://www.kayondata.com/live-captions/ Last updated: 2026-06-14T22:15:52.000Z Every technical talk asks people to process dense ideas quickly. Live captions give the audience another way in, especially when sound, language, room acoustics, accents, fatigue, or hearing access make listening alone harder than it needs to be. At [DATA BASH](https://databash.live/?ref=kayondata.com), we point attendees to live-caption options because access should not depend on luck, perfect audio, or someone being comfortable enough to ask publicly. I want to carry that same idea into my own site and talks: if a session is live, there should be some live-caption path available. The scale is not small. The [World Health Organization](https://www.who.int/en/news-room/fact-sheets/detail/deafness-and-hearing-loss?ref=kayondata.com) says over 5% of the world's population, around 430 million people, require rehabilitation for disabling hearing loss. Looking more broadly, WHO also reports that over 1.5 billion people live with some degree of hearing loss, roughly one fifth of the global population. Captions are essential for deaf and hard-of-hearing people, but the number of people who benefit is much higher. Captions help people following a talk in a second language, people in noisy rooms, people with audio issues, people who process written language more easily, and people who simply missed one dense sentence in a technical explanation. They also help hearing audience members when listening is simply impractical: maybe they do not have headphones with them, their headphone batteries died, or they are somewhere where sound would disturb others. They also help the content itself travel further. Caption files and transcripts turn spoken information into text that can be searched, quoted, translated, summarized, reused, and indexed more easily. [W3C's Web Accessibility Initiative](https://www.w3.org/WAI/perspective-videos/captions/?ref=kayondata.com) explicitly notes that text versions of captions and transcripts can be better indexed by search engines, and [Google's video SEO guidance](https://developers.google.com/search/docs/appearance/video?ref=kayondata.com) is clear that making video content easier to discover and understand is part of good publishing practice. Perfect captions are not always possible. Good intent is better than doing nothing, but it still needs a tested setup. The practical goal is to make live captions part of the default event checklist: planned, announced, tested, and visible to the audience before the talk starts. ## Principles - Offer any usable caption path. Platform captions, browser captions, operating-system captions, or a conference-provided service are all better than leaving the audience without options. - Make the option easy to find. Add caption guidance to the event page, opening slide, stream description, and moderator introduction. - Test it like audio. If organizers check microphones and screen sharing, captions deserve the same rehearsal. ## What Speakers Can Do - Ask the organizer which caption option will be available, and include the answer in your session notes. - Speak clearly, avoid talking over others, and repeat audience questions before answering. - Put important names, commands, URLs, and acronyms on slides so captions do not carry that burden alone. - Share slides or key links before or after the session when possible. ## What Organizers Can Do - Choose a caption approach before the event day and document how attendees can turn it on. - Prefer tools where viewers can enable, disable, resize, or reposition captions themselves. - Tell speakers where captions appear, so they avoid placing important slide content under them. - Keep caption guidance available in recordings, because accessibility should continue after the live stream. ## Online And In-Person Talks For online sessions, start with the event platform. In Microsoft Teams, attendees can turn on live captions themselves from the meeting controls, so the speaker's job is often to mention that option clearly and make sure the organizer has planned the caption path. This is not specific to Teams. Most major online conferencing tools now include some form of live or closed captions, including Zoom, Google Meet, and Webex. The exact controls can vary by tool, license, account settings, and host configuration, so check the platform documentation before the session. There is also an organizer route between those two cases: Windows 11 Live captions or macOS Live Captions can run on a dedicated device and be projected to a screen, so the audience does not need to configure anything themselves. ## Device And Browser Captions When an online event does not provide dedicated captioning, attendees can sometimes use captions built into their own device or browser. Windows 11 includes Live captions, which can be opened from Accessibility settings or with the Windows + Ctrl + L shortcut. On supported Macs, macOS Live Captions can be turned on from Accessibility settings; for quicker access, press Option-Command-F5 to open Accessibility Shortcuts and select Live Captions. If Live Captions is the only selected Accessibility Shortcut, the same shortcut toggles it directly. Chrome also has a Live Caption setting for audio and video content played in the browser. These options are helpful fallback paths, but they should not be treated as a replacement for event planning. If you organize the event, tell people which caption path you have tested and where to find it. For in-person events, consider whether one of these tools can be projected for everyone instead of leaving each attendee to solve it alone. ## Slide Tools ### PowerPoint PowerPoint captions are the cleanest built-in option when you are presenting PowerPoint in person and the organizer does not provide captions. Microsoft's [PowerPoint captions documentation](https://support.microsoft.com/en-us/office/present-with-real-time-automatic-captions-or-subtitles-in-powerpoint-68d20e49-aec3-456a-939d-34a79e8ddd5f?ref=kayondata.com) explains how to enable subtitles, choose the spoken language, choose the caption language, and decide where captions should appear on the slide. Test your microphone and internet connection before the talk, because PowerPoint uses a speech service for this feature. ### Keynote For in-person Keynote talks without organizer captions, use Keynote's captioning support as part of the presentation setup and test it on the projected display before the session starts. Apple's [Keynote captions and subtitles documentation](https://support.apple.com/guide/keynote/add-video-and-audio-tan63d61519a/mac?ref=kayondata.com) explains where the Closed Captions control appears during slideshow playback. For online Keynote talks, prefer the conferencing platform's live-caption option first, because that lets each attendee enable captions for themselves. ## Starting Points - [Windows live captions](https://support.microsoft.com/en-US/accessibility/windows/use-live-captions-to-better-understand-audio?ref=kayondata.com) \- system-level captions in Windows 11. - [WHO hearing loss facts](https://www.who.int/en/news-room/fact-sheets/detail/deafness-and-hearing-loss?ref=kayondata.com) \- global figures for disabling hearing loss and hearing loss prevalence. - [W3C captions benefits](https://www.w3.org/WAI/perspective-videos/captions/?ref=kayondata.com) \- accessibility benefits and why captions/transcripts support search indexing. - [Chrome live captions](https://support.google.com/chrome/answer/10538231?hl=en&ref=kayondata.com) \- browser-level captions for audio and video played in Chrome. - [Microsoft Teams captions](https://support.microsoft.com/office/use-live-captions-in-microsoft-teams-meetings-4be2d304-f675-4b57-8347-cbd000a21260?ref=kayondata.com) \- how attendees can turn on live captions in Teams meetings. - [PowerPoint subtitles](https://support.microsoft.com/en-us/office/present-with-real-time-automatic-captions-or-subtitles-in-powerpoint-68d20e49-aec3-456a-939d-34a79e8ddd5f?ref=kayondata.com) \- native real-time captions and subtitles while presenting. - [macOS Live Captions](https://support.apple.com/guide/mac-help/get-captions-of-spoken-and-computer-audio-mchldd11f4fd/mac?ref=kayondata.com) \- system captions for spoken and computer audio on supported Macs, including shortcut options. - [Keynote captions](https://support.apple.com/guide/keynote/add-video-and-audio-tan63d61519a/mac?ref=kayondata.com) \- Apple's guidance for captions and subtitles during Keynote playback. ## My Position Live captions are part of respectful technical communication. They help more people stay with the talk, ask better questions, and take the ideas back into their work. If you want to make live captions work well for your conference, meetup, or talk, I am happy to discuss a practical setup with you through the [contact form](https://www.kayondata.com/contact/). ## Posts ### A Shout-Out to Synaps Media: My Blog Has a New Home URL: https://www.kayondata.com/a-shout-out-to-synaps-media-my-blog-has-a-new-home/ Last updated: 2026-08-12T06:33:14.000Z For about a year, my blog lived on Hashnode, but I never saw the SEO results I was hoping for. At the same time, my [Substack](https://kayondata.substack.com/) was quietly gaining subscribers without any advertising. So I decided to take back full control, and go self-hosted again. Ghost CMS has been a dream to use so far. The writing workflow feels effortless, the SEO foundation is strong out of the box, and newsletters and RSS are built in. I plan to keep the blog non-paywalled, though I’m considering optional paid memberships to help cover hosting costs. Would you pay for it and if yes, how much? Let me know! Murat Çorlu, founder of [Synaps Media](https://www.synapsmedia.com/?ref=kayondata.com), and his team made the move incredibly smooth. Murat handled the technical setup, listened to what I wanted from the blog, and gave me some great advices that helped me setting the blog up. I also used Codex to help me with the blog design. This post is my thank-you to Murat and the Synaps Media team for helping my blog find its new home. If you’re planning a similar move, I’d happily recommend them. ### AzureSqlVmToolkit Is Becoming Azure Data Lab Toolkit URL: https://www.kayondata.com/azuresqlvmtoolkit-is-becoming-azure-data-lab-toolkit/ Last updated: 2026-07-28T06:28:19.000Z I have been dreaming about this project for years. The actual trigger for finally starting it is AI. Without it, I could not take on a project of this size alone. I want to be transparent about that. Working with [Claude](https://claude.com/product/overview?ref=kayondata.com) and [OpenAI Codex](https://openai.com/codex/?ref=kayondata.com) is what makes it realistic for me to attempt this. The idea has grown from one deployment script into a much bigger project covering several data platforms, deployment engines, assessment, testing, security, cost control, documentation, and catalogs for software and sample data. Codex also helped me research and edit this post. Even this post took me several hours. AI wrote most of it, but I reviewed every part, added sources, and made my own edits until it said what I actually wanted to say. That does not mean I am going to take unreviewed AI output and call it a release. I expect to spend months testing deployments, reviewing code, checking the architecture, and finding the places where an idea looked good on paper but did not survive contact with Azure. What began as [Azure SQLVM Toolkit](https://github.com/kaysauter/azure-sqlvm-toolkit?ref=kayondata.com) has grown into a broader idea: [Azure Data Lab Toolkit](https://github.com/kaysauter/Azure-Data-Lab-Toolkit?ref=kayondata.com). Azure SQLVM Toolkit is not working yet. Before completing it, I decided to rebuild the architecture from the ground up so it can support the broader scope and hold up as I add more ideas later. The original project remains focused on [SQL Server on an Azure virtual machine](https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/?ref=kayondata.com). The larger goal is to build a practical, repeatable, secure, and cost-aware way to create data labs on Azure. I want to describe a lab, see what it might cost, understand the security and licensing implications, deploy it, prove that it works, and remove it cleanly afterwards. I also want to run the same experiment against two platforms and compare the results without rebuilding everything around the database by hand. ## What I want the toolkit to cover I want Azure Data Lab Toolkit to support labs built around: - [SQL Server on Azure virtual machines](https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/?ref=kayondata.com), with [SQL Server on Linux](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-overview?view=sql-server-ver17&ref=kayondata.com) and [SQL Server containers](https://learn.microsoft.com/en-us/sql/linux/containers/overview?view=sql-server-ver17&ref=kayondata.com) planned for much later - [Azure SQL Database](https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-database-paas-overview?ref=kayondata.com) and [Azure SQL Managed Instance](https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/sql-managed-instance-paas-overview?ref=kayondata.com) - [Microsoft Fabric](https://learn.microsoft.com/en-us/fabric/fundamentals/microsoft-fabric-overview?ref=kayondata.com), including workspaces, items, shortcuts, and deployment pipelines - [Azure Database for PostgreSQL](https://learn.microsoft.com/en-us/azure/postgresql/overview?ref=kayondata.com), PostgreSQL in the [official Docker image](https://hub.docker.com/%5F/postgres?ref=kayondata.com), and eventually PostgreSQL on [Kubernetes](https://kubernetes.io/docs/concepts/overview/?ref=kayondata.com) or [Azure Kubernetes Service](https://learn.microsoft.com/en-us/azure/aks/what-is-aks?ref=kayondata.com) - [Azure Databricks](https://learn.microsoft.com/en-us/azure/databricks/introduction/?ref=kayondata.com) and, further down the roadmap, [Azure Cosmos DB](https://learn.microsoft.com/en-us/azure/cosmos-db/?ref=kayondata.com) - storage such as [Azure Data Lake Storage Gen2](https://learn.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-introduction?ref=kayondata.com), [Blob Storage](https://learn.microsoft.com/en-us/azure/storage/blobs/storage-blobs-introduction?ref=kayondata.com), and [Azure Files](https://learn.microsoft.com/en-us/azure/storage/files/storage-files-introduction?ref=kayondata.com) The first target is still SQL Server on an Azure VM. It gives me a useful base for testing deployment, networking, [Azure Bastion](https://learn.microsoft.com/en-us/azure/bastion/bastion-overview?ref=kayondata.com), [Azure Key Vault](https://learn.microsoft.com/en-us/azure/key-vault/general/overview?ref=kayondata.com), backup restore, guest setup, tooling, teardown, and CI/CD patterns. There is also a very practical reason: I work on a Mac, and a Windows lab gives me access to [SQL Server Management Studio](https://learn.microsoft.com/en-us/ssms/faq?ref=kayondata.com#does-ssms-run-on-macos) when I need it. The new project is still at the beginning. Right now it contains an architecture scaffold, documentation, and a backlog, but no deployable cmdlets. It is not production ready. Everything below describes where I want to take it, not functionality that has already shipped. The [Azure SQLVM Toolkit documentation](https://kaysauter.github.io/azure-sqlvm-toolkit/?ref=kayondata.com) will remain online while I build the new project. The [Azure Data Lab Toolkit documentation](https://kaysauter.github.io/Azure-Data-Lab-Toolkit/?ref=kayondata.com) already explains the direction in more detail and includes a short [pitch deck](https://kaysauter.github.io/Azure-Data-Lab-Toolkit/pitch/deck/?ref=kayondata.com). ## Data Labs need Sample Data A lab without data is usually not much of a lab. I want a catalog that can bring in Microsoft samples, community databases, private backups, or files supplied by the user. Community samples and tools will not be mandatory. Planned flags such as `-UseCustomArtifact`, `-CustomArtifactPath`, and `-CustomArtifactUrl` will let people bring their own sample data or software into a lab. Companies and developers will be able to use private backups, datasets, installers, scripts, or internal tools instead of choosing an item from the community catalog. For many testing scenarios, community projects are still a great option. A well-known database or tool can provide a shared baseline for comparisons, and it can also become a useful starting point when developing an in-house solution. Microsoft's [SQL Server samples repository](https://github.com/microsoft/sql-server-samples?ref=kayondata.com) is an obvious starting point. That includes [AdventureWorks](https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/adventure-works?ref=kayondata.com), [WideWorldImporters](https://github.com/microsoft/sql-server-samples/blob/master/samples/databases/wide-world-importers/README.md?ref=kayondata.com), and the older [Northwind and pubs](https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs?ref=kayondata.com) examples. Microsoft's own WideWorldImporters README credits [Greg Low](https://greglow.com/?ref=kayondata.com), [Denzil Ribeiro](https://www.linkedin.com/in/denzilribeiro?ref=kayondata.com), [Jos de Bruijn](https://www.linkedin.com/in/josdebruijn?ref=kayondata.com), and [Robert Cain](https://arcanecode.com/info/?ref=kayondata.com) as its authors. I want those names to stay visible instead of disappearing behind a download link. The community has many excellent sources as well. [Daniel Hutmacher](https://sqlsunday.com/about/?ref=kayondata.com) publishes databases through [SQL Sunday downloads](https://sqlsunday.com/downloads/?ref=kayondata.com), including a [Chicago parking tickets database](https://sqlsunday.com/2022/12/05/new-demo-database/?ref=kayondata.com) assembled from several public data sources and a meteorological database based on data from [SMHI](https://opendata.smhi.se/metobs/introduction?ref=kayondata.com). The [SQLBI Contoso Data Generator](https://www.sqlbi.com/tools/contoso-data-generator/?ref=kayondata.com) comes from the SQLBI team founded by [Alberto Ferrari](https://www.sqlbi.com/author/alberto-ferrari/?ref=kayondata.com) and [Marco Russo](https://www.sqlbi.com/author/marco-russo/?ref=kayondata.com). Brent Ozar provides a useful SQL Server package of the [Stack Overflow database](https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent/?ref=kayondata.com), while the underlying content still belongs to its original authors and remains subject to the [Stack Overflow content license](https://stackoverflow.com/help/licensing?ref=kayondata.com). PostgreSQL has a different community landscape. Its [sample database index](https://wiki.postgresql.org/wiki/Sample%5FDatabases?ref=kayondata.com) is a useful place to start, but projects such as [Pagila](https://github.com/devrimgunduz/pagila?ref=kayondata.com), [Chinook](https://github.com/lerocha/chinook-database?ref=kayondata.com), and [Northwind for PostgreSQL](https://github.com/pthom/northwind%5Fpsql?ref=kayondata.com) are community projects, not official PostgreSQL databases. Credit belongs with their maintainers: [Devrim Gündüz](https://github.com/devrimgunduz?ref=kayondata.com) and the Pagila contributors, [Luis Rocha](https://github.com/lerocha?ref=kayondata.com) and the Chinook contributors, and [Pascal Thomet](https://github.com/pthom?ref=kayondata.com) and the Northwind adaptation contributors. Microsoft also publishes [Fabric samples](https://github.com/microsoft/fabric-samples?ref=kayondata.com), which can become useful starting points for guided Fabric labs rather than anonymous downloads hidden behind the toolkit. Being listed here does not mean a database can automatically be redistributed. Every candidate still needs a review for licensing, attribution, integrity, size, compatibility, and security before the toolkit automates it. ## Review plan before execution The main input will be a schema-validated YAML file that can be read before it is run and kept in source control. Templates will provide easy starting points, and an optional terminal interface enabled with a flag will help people generate the YAML without forcing everyone through a wizard. Experienced users will still be able to use [PowerShell](https://learn.microsoft.com/powershell/?ref=kayondata.com) flags for direct control. The planned `-Plan` and `-WhatIf` flags will answer different questions. `-Plan` will work without Azure access. It will validate the configuration, resolve defaults and derived values, and explain how a small set of base variables becomes the resource names, SKUs, network choices, catalog selections, guardrails, and deployment actions in the final plan. `-WhatIf` will be Azure-aware. After the user signs in, it will compare the resolved plan with the resources that already exist and report what the toolkit would create, reuse, update, or flag as drift. It will not execute those changes. Together, the two flags will make both the toolkit's reasoning and the practical Azure impact reviewable before execution. Before the toolkit deploys anything, it will create a plan showing: - which resources will be created and which existing resources will be reused - what security, identity, networking, and secret-handling choices are being made - what the estimated cost is, including any budget, shutdown schedule, or time-to-live guardrail - which sample databases, tools, and private artifacts will be installed and where they come from - which licenses, third-party terms, integrity checks, or sensitive-data warnings need attention - exactly what teardown will remove later Cost estimation will be an explicit operation with its own flag, and HTML will be available as an output when the estimate or deployment report needs to be shared. The same run will also be able to produce useful console, JSON, and Markdown output. Secrets will go into Azure Key Vault, with managed identities, private networking, and Azure Bastion used where they fit. More permissive choices, including printing a password to the shell, will require an explicit opt-in flag. Only after the plan has been reviewed will deployment begin. Afterwards, probes will test the database, storage, and installed software. Teardown will start by listing what will be deleted and asking for confirmation. ## PowerShell stays, but Bicep and Terraform will follow The architecture has three parts: Core, Providers, and Engines. The **Core** is the reusable part. It reads the YAML, resolves defaults, works with catalogs, creates the plan, handles assessment, cost and lifecycle rules, records run state, and coordinates teardown. A **Provider** knows the details of one target. The SQL VM provider needs to understand images, storage, networking, SQL configuration, backup restore, and guest software. A Fabric provider has a different job again: workspaces, capacities, items, shortcuts, and deployment pipelines. A future Cosmos DB provider would eventually need to understand APIs, throughput, partitioning, and consistency choices. Those differences belong in providers instead of a growing collection of special cases in one script. An **Engine** performs the approved actions. [PowerShell](https://learn.microsoft.com/powershell/?ref=kayondata.com) comes first because I still want people to be able to use a PowerShell command and a YAML file without learning another infrastructure language before building a lab. I am also much better versed in PowerShell, and I think it still has a place here. [Bicep](https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/overview?ref=kayondata.com) and [Terraform](https://developer.hashicorp.com/terraform/intro?ref=kayondata.com) will follow as additional deployment engines or export targets once the Core and provider contracts have proved themselves. The engine will be an explicit flag, not something the toolkit guesses. For teams operating a larger estate, one of those infrastructure-as-code routes will probably be a better fit. PowerShell, Bicep, and Terraform will still work from the same resolved plan instead of quietly producing three different labs. Storage and software catalogs also need provider contracts. ## CI/CD is part of the lab A Git repository alone is not a CI/CD solution. The toolkit also needs repeatable workflows for reviewing changes, testing the module, checking security, approving deployments, collecting evidence, and proving that teardown worked. [GitHub](https://docs.github.com/en/get-started/start-your-journey/what-is-github?ref=kayondata.com) will be the first Git provider, with [GitHub Actions](https://docs.github.com/en/actions?ref=kayondata.com) as the first pipeline engine. Pull requests will validate the YAML schema, run PowerShell and Pester checks, build the documentation, and perform dependency and vulnerability checks. That includes [Dependabot](https://docs.github.com/en/code-security/how-tos/secure-your-supply-chain?ref=kayondata.com) for dependency maintenance and alerts, [CodeQL](https://docs.github.com/en/code-security/concepts/code-scanning/codeql/codeql-code-scanning?ref=kayondata.com) where its supported languages apply, and [Checkov](https://www.checkov.io/?ref=kayondata.com) for infrastructure-as-code scanning once the Bicep and Terraform engines exist. Deployment workflows will use short-lived [OpenID Connect (OIDC) federation](https://docs.github.com/en/actions/concepts/security/openid-connect?ref=kayondata.com) instead of stored cloud credentials, require approval for sensitive environments, retain the resolved plan and reports as artifacts, run post-deployment probes, and record cleanup evidence. A failed deployment will leave enough state behind for investigation and a safe retry rather than simply stopping halfway through. [Azure DevOps](https://learn.microsoft.com/en-us/azure/devops/user-guide/what-is-azure-devops?view=azure-devops&ref=kayondata.com) with [Azure Pipelines](https://learn.microsoft.com/en-us/azure/devops/pipelines/get-started/what-is-azure-pipelines?view=azure-devops&ref=kayondata.com) is the main Microsoft alternative. Later providers can cover [GitLab CI/CD](https://docs.gitlab.com/ci/?ref=kayondata.com), [Gitea Actions](https://docs.gitea.com/usage/actions/overview?ref=kayondata.com), and [Forgejo Actions](https://forgejo.org/docs/latest/user/actions/reference/?ref=kayondata.com). Gitea and Forgejo are particularly interesting for self-hosted labs, where the Git service itself could run in [Azure Container Apps](https://learn.microsoft.com/en-us/azure/container-apps/overview?ref=kayondata.com). Those systems use different workflow syntax, runners, secrets, and approval models. The toolkit will expose the same intent across them: validate, plan, approve, deploy, probe, report, and tear down. Fabric CI/CD is also part of the plan, but I do not yet know whether a complete Fabric CI/CD path can be implemented through infrastructure as code. I want to explore that in the future instead of presenting it as a solved part of the architecture. Whatever route proves practical will still produce the same evidence as the other targets. ## Labs will be easy, but not careless A major goal is to make labs easy without hiding the sharp edges. The toolkit will help people understand whether Azure SQL Database, SQL Managed Instance, SQL Server on a VM, PostgreSQL, Fabric, or Cosmos DB fits the question they are trying to answer. It will also make security, cost, licensing, data sensitivity, download provenance, and cleanup visible instead of deciding everything silently. For SQL Server assessment, the plan is to follow Microsoft-supported routes such as [SQL Server enabled by Azure Arc](https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/overview?ref=kayondata.com) and the [migration component in SQL Server Management Studio](https://learn.microsoft.com/en-us/ssms/migrate/migrate-sql-server-azure-sql?ref=kayondata.com). Compatibility, feature support, security, sizing, cost, and migration readiness will be reported separately. Fabric is another place where guidance matters as much as automation. People regularly have to choose between a [SQL database in Fabric](https://learn.microsoft.com/en-us/fabric/database/sql/overview?ref=kayondata.com), a [Warehouse](https://learn.microsoft.com/en-us/fabric/data-warehouse/data-warehousing?ref=kayondata.com), and a [Lakehouse](https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-overview?ref=kayondata.com). I want the Fabric provider to explain those choices in the context of the lab rather than asking the user to pick a product name without understanding the consequences. That guidance also covers [OneLake shortcuts](https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts?ref=kayondata.com): where the data remains, what the shortcut stores, whose permissions apply, and what freshness means in practice. The provider will guide users through workspaces and items and include a CI/CD route for Fabric. How much of that route can be handled through infrastructure as code remains an open design question that I want to explore. Some of the first Fabric scenarios I would like to explore are [Fabric Unified Admin Monitoring, or FUAM](https://github.com/microsoft/fabric-toolbox/tree/main/monitoring/fabric-unified-admin-monitoring?ref=kayondata.com), the [Rayfin SDK](https://github.com/microsoft/rayfin?ref=kayondata.com), and Microsoft's [Tabular Model Definition Language, or TMDL](https://learn.microsoft.com/en-us/analysis-services/tmdl/tmdl-overview?ref=kayondata.com). If FUAM is already present, the flow will validate it, explain what it found, and ask before attempting to install or repair anything. FUAM was originally developed by [Gellért Gintli](https://github.com/ggintli?ref=kayondata.com) and [Kevin Thomas](https://github.com/kethom-analytics?ref=kayondata.com) and is hosted in Microsoft's Fabric Toolbox. It is a community accelerator, not an officially supported Microsoft product. [Mathias Thierbach](https://github.com/mthierba?ref=kayondata.com) created [pbi-tools](https://pbi.tools/?ref=kayondata.com) and contributes to TMDL; TMDL itself is a Microsoft language. ## Community tools belong in the design I do not want to build a gallery of anonymous `curl` commands. For every catalog item, I need a real home, a maintainer or publisher, a license, a known version, an installation method, and whatever checksum or signing information is available. The entry also needs to say what the toolkit will execute inside the lab. On the SQL Server side, I already have [dbatools](https://dbatools.io/?ref=kayondata.com) in mind. It was created by [Chrissy LeMaire](https://dbatools.io/team/?ref=kayondata.com) and is now maintained by its core team and contributors. I also want to support [Brent Ozar's First Responder Kit](https://www.brentozar.com/first-aid/?ref=kayondata.com), including the `sp_Blitz` family, and [Ola Hallengren's SQL Server Maintenance Solution](https://ola.hallengren.com/?ref=kayondata.com). For semantic models and Fabric-related work, the list includes [Tabular Editor](https://tabulareditor.com/?ref=kayondata.com) from [Daniel Otykier](https://tabulareditor.com/what-is-tabular-editor?ref=kayondata.com), [Measure Killer](https://measurekiller.com/?ref=kayondata.com) from [Gregor Brunner and the Brunner BI team](https://measurekiller.com/about/?ref=kayondata.com), pbi-tools, TMDL, FUAM, and Rayfin. A Windows SQL lab may also need the latest [SQL Server Management Studio](https://learn.microsoft.com/en-us/ssms/install/install?ref=kayondata.com). These are not all the same kind of project. dbatools and the First Responder Kit are open source. [Tabular Editor 2 uses the MIT License](https://github.com/TabularEditor/TabularEditor/blob/master/LICENSE?ref=kayondata.com), while [Tabular Editor 3 is commercially licensed](https://tabulareditor.com/pricing?ref=kayondata.com). Measure Killer has free and paid editions but is not an open-source application. [pbi-tools uses the AGPLv3 license](https://github.com/pbi-tools/pbi-tools/blob/main/LICENSE?ref=kayondata.com). [Current Terraform releases use Business Source License 1.1](https://github.com/hashicorp/terraform/blob/main/LICENSE?ref=kayondata.com); older releases have different licensing. Those distinctions need to remain visible instead of being flattened into the word "free." The catalog will also accept artifacts that are not public. Users will be able to provide a local file or approved URL, optionally add a checksum, and declare that the artifact or data contains sensitive information. Private software will still receive the same review before the toolkit downloads or executes it. The [third-party notices for Azure SQLVM Toolkit](https://kaysauter.github.io/azure-sqlvm-toolkit/third-party-notices/?ref=kayondata.com) show the direction I want to continue: name the real project, link to its home, credit the people behind it, and be clear about its license. ## An open call for projects If you maintain a database, data generator, assessment utility, administration script, Fabric accelerator, observability solution, PostgreSQL project, or another tool that would make a good lab scenario, please tell me about it. I would like maintainers to use these labs to show what their projects can do. You can message me on [LinkedIn](https://www.linkedin.com/in/kaysauter/?ref=kayondata.com) or use the [contact form on Kay On Data](https://www.kayondata.com/contact/). For an open-source project, send me the repository, the correct attribution and license, supported platforms, installation details, and the scenario you would use to demonstrate it. If your project is commercial, please contact me first so we can discuss your idea and what it means to my costs that I'm carrying myself so far. ## What comes next The [Azure Data Lab Toolkit repository](https://github.com/kaysauter/Azure-Data-Lab-Toolkit?ref=kayondata.com) contains the initial scaffold and the dependency-ordered [feature backlog](https://github.com/kaysauter/Azure-Data-Lab-Toolkit/blob/main/FEATURE%5FREQUESTS.md?ref=kayondata.com). The work is also visible through [GitHub issues](https://github.com/kaysauter/Azure-Data-Lab-Toolkit/issues?ref=kayondata.com) and the public [project roadmap](https://github.com/users/kaysauter/projects/6/views/1?ref=kayondata.com). The first milestone is straightforward to describe, even if it is not small: establish the module and provider contracts, then make SQL Server on Azure VM the first fully tested provider from planning through teardown. That includes stronger assessment, cost, testing, and cleanup workflows, as well as the first catalog model for tools, samples, and private artifacts. Only after that path works from beginning to end will the next provider get its turn. Azure SQL Database and Azure SQL Managed Instance come next, followed by Fabric. Bicep and Terraform will come after the plan and provider contracts are stable enough to give all engines the same meaning. The larger goal remains simple: make it easier and more cost-friendly to build real data labs, compare platforms, test ideas, install useful tools, load sample or private data, and tear everything down safely afterwards. I also want the toolkit to keep each lab's settings readable and reusable, so the same definition and decisions can become a starting point for real use cases. That is where Azure Data Lab Toolkit is heading. If you have an idea that I have missed, I would be glad to hear it. You can send me a message on [LinkedIn](https://www.linkedin.com/in/kaysauter/?ref=kayondata.com) or use the [contact form on Kay On Data](https://www.kayondata.com/contact/). ### Publishing Powershell Azure SQL VM Toolkit URL: https://www.kayondata.com/coming-soon/ Last updated: 2026-06-14T22:56:40.000Z In 2021, I wrote a PowerShell script to automate most of the steps for deploying a SQL Server VM on Azure, but not everything. Also back then, the whole deployment process with the script took almost one hour to deploy, including database restores and installing chololatey and some tools. Today, I present a new script that does much more, and is faster, easier to configure thanks to YAML. There is only one manual step left: You still have to log into the VM and start a dbatools script to restore your databases. This is not for lack of my willingess but a limitation of the `Invoke-AzVMRunCommand` method, which means that you'll be the SYSTEM user which is not desirable for database restores. Why YAML you might wonder? A YAML file is very easy to use, and many infrastructure-as-code tools (like Ansible, Terraform, etc.) use YAML for configuration, so people that are usually working on infrastructure will be pleased to see something like this if a DBA gives them a such YAML file. Additionally, it gives you the possibility to touch only the configuration, but run the same code again for a different deployment. And for you, as a SQL developer or DBA, you will gain time, as you won't have to click through Azure pages to configure everything, but just quickly edit the YAML file and run the script. Because most of the settings will be similar for you, you don't have to touch every configuration you've made before, either. You can change the VM size, region, SQL Server version, and more by simply editing the YAML file without touching the script itself. The best part: the entire deployment now takes about 30 minutes from start to finish, including database restores (sample databases by Microsoft; your databases may take longer if they are bigger) and installing tools. In this post, I'll walk through a PowerShell script that deploys a fully configured SQL Server 2022 VM on Azure. It sets up Azure Bastion for secure access, Key Vault for secrets management, Azure Files for persistent storage, and installs development tools automatically. The entire deployment is driven by a single YAML configuration file. ## The Solution: Config-Driven Deployment The solution is split into two files: - **config.yaml**: a declarative configuration file that defines *what* to deploy - **vm\_creation\_with\_bastion.ps1**: the deployment script that makes it happen ### The Configuration File Everything that might change between deployments lives in `config.yaml`: ```yaml resourceGroup: name: "YourResourceGroupName" location: "Switzerland North" tags: Purpose: "Demo" vm: size: "Standard_DS13_V2" image: publisherName: "MicrosoftSQLServer" offer: "sql2022-ws2022" skus: "sqldev-gen2" version: "latest" storage: resourceGroup: "yourstorageRG" accountName: "yourstorageaccount" fileShareName: "yoursqlbackupsharename" driveLetter: "Z" ``` Want a different VM size? Change one line. Different region? One line. Different SQL Server version? One line. No script modifications needed. ## Architecture Overview The deployment creates resources across two resource groups by design: ```plaintext Azure Subscription | +-- Resource Group: YourResourceGroupName (disposable) | +-- Azure Bastion | +-- SQL Server VM (Managed Identity) | +-- Resource Group: yourstorageRG (persistent) +-- Key Vault (secrets: vm-admin-password, storage-account-key) +-- Storage Account +-- File Share (mounted as Z: on VM) +-- restore-databases.ps1 (uploaded by script) ``` **Why two resource groups?** The VM resource group is disposable. You can tear it down and rebuild without losing your Key Vault, storage account, or database backups. The persistent resource group survives VM rebuilds, which matters a lot when you frequently spin up and destroy environments during development. ## Choosing an Azure Bastion SKU Azure Bastion comes in different SKU tiers. Check [https://learn.microsoft.com/en-us/azure/bastion/bastion-sku-comparison](https://learn.microsoft.com/en-us/azure/bastion/bastion-sku-comparison?ref=kayondata.com) for more information. ## Key Design Decisions ### 1\. Idempotent Resource Creation Every resource is checked before creation. Run the script twice and it won't fail or create duplicates. This is crucial for development environments where you might iterate on the config and re-run the script multiple times. ### 2\. Key Vault Soft-Delete Handling Azure Key Vault has a soft-delete feature that retains deleted vaults for 90 days. If you delete a Key Vault and try to create one with the same name, you will get an error: *"The vault name is already in use."* The script handles this by automatically incrementing the name. At the end of the deployment, the script notifies you of the name change so you can update your config for future runs. ### 3\. Cryptographically Secure Password Storage The password is stored in Key Vault immediately and never written to disk. Instead of relying on `Get-Random` (which uses a PRNG), the script generates VM admin passwords using `RNGCryptoServiceProvider` and guarantees complexity requirements ### 4\. Managed Identity for Secrets Access The VM uses a system-assigned managed identity to retrieve secrets from Key Vault. No credentials are hardcoded or stored on the VM. The file share mount script, which runs on the VM, fetches the storage account key at runtime. Even if someone gains access to the VM, they can not extract long-lived credentials. The managed identity token is short-lived and scoped. ### 5\. Persistent File Share Mount The Azure Files share is mounted as a drive letter (Z:) using `New-SmbGlobalMapping`, which makes it available to all users and services on the VM. A scheduled task re-mounts it on every reboot. ### 6\. Automated Software Installation The script installs development tools on the VM via `Invoke-AzVMRunCommand`, defined entirely in the YAML config: ```yaml softwareInstalls: installScript: | choco install vscode git powershell-core tabular-editor -y Install-Module -Name dbatools -Force logonScript: | code --install-extension ms-mssql.mssql ``` Some tools (like VS Code extensions) need a user session, so the script registers a one-time logon task that runs at first login and then cleans itself up. ### 7\. Database Restore Script A `restore-databases.ps1` script is automatically uploaded to the file share. After logging into the VM, you can restore all `.bak` files from the share with a single command: ```plaintext Z:\restore-databases.ps1 ``` It uses [dbatools](https://dbatools.io/?ref=kayondata.com) to iterate over all backup files and restore them to the local SQL Server instance. ## Running the Deployment Prerequisites: - PowerShell 7+ with the `Az` module installed - An active Azure session (`Connect-AzAccount`) Then simply: ```powershell .\vm_creation_with_bastion.ps1 ``` Or with a custom config: ```powershell .\vm_creation_with_bastion.ps1 -ConfigFile "my-environment.yaml" ``` At the end, the script prints the VM credentials and total deployment time: ```plaintext Deployment completed in 00:18:42. VM Login Credentials: Username: youradminusername Password: ``` I am going to publish a YAML writing helper for SQLVMs so that the picking of a SQLVM image is easier and faster, and it'll write the YAML file for you. Stay tuned for that! The full source code is available on [GitHub](https://github.com/kaysauter/azure-sqlvm-toolkit?ref=kayondata.com). ### Deploying Azure SQL VMs with PowerShell Azure Bastion + dbatools URL: https://www.kayondata.com/deploying-azure-sql-vms-with-powershell-azure-bastion-dbatool/ Last updated: 2026-06-30T18:53:45.000Z Update of my older post after the breaking changes by Microsoft! # What happened? About a year ago, my original script from my blog post “Deploy Azure Sql Server Vm With PoSh” ([blog](https://kayondata.com/deploy-azure-sql-server-vm-with-posh?ref=kayondata.com), [substack](https://kayondata.substack.com/p/deploy-azure-sql-server-vm-with-posh)) from 2021 broke due to a change in the underlying model. In last August, [Microsoft updated its documentation](https://learn.microsoft.com/en-us/azure/virtual-machines/windows/tutorial-manage-vm?ref=kayondata.com) regarding deploying Azure [SQL Server VMs on Azure](https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/scripts/create-sql-vm-powershell?view=azuresql&ref=kayondata.com). Therefore, I was able to redevelop the code, and below, you’ll find the new code along with some new updates that Microsoft made. There are some changes around the model of Azure VMs and the model for [Azure Bastion](https://learn.microsoft.com/en-us/azure/bastion/design-architecture?ref=kayondata.com). In the next chapter, you’ll find a script that deploys an Azure SQL Server VM together with Azure Bastion. Azure Bastion is a service by Microsoft that could be seen as something like “Security as Service” by Microsoft. [If you want to use RDP via open internet, make sure you protect your RDP connection](https://www.microsoft.com/en-us/security/blog/2020/04/16/security-guidance-remote-desktop-adoption/?msockid=1319a94e459b6aec03e0bde644ec6b7b&ref=kayondata.com). Azure Bastion covers your RDP connection with a TLS tunnel and it is very easy to use. Another way is to use VPN or a private endpoint, but then you have to manage the network security yourself. If you are just a database engineer / DBA like myself or similar, then possibility don’t know how to do this, and Azure Bastion is an excellent option as it won’t expose your VMs IP-Address to the internet. # For who is this script? If you are a DBA or DB dev at a smaller company or a smaller IT team or just want to have a proof of concept, but your IT team is not able to provide you something like this, this may be for you. Also, if you want to learn Azure Powershell and are a DBA, this is a great start. If you however want to have a solution on a bigger scale, be or adaptions more frequently, you probably will find either [Bicep](https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/overview?tabs=bicep&ref=kayondata.com) by Microsoft or something like [Terraform](https://learn.microsoft.com/en-us/azure/developer/terraform/overview?ref=kayondata.com) by Hashicorp more helpful. I personally started to develop this script a during the pandemic years when I was a DB developer and wanted to have a platform on which a small team could develop new things and showcase it to customers, even give them the VM as a means to try the data warehouse solution we’ve built for a core banking system provider. The execution of the whole script, including the installation of [dbatools](https://dbatools.io/?ref=kayondata.com) takes about 25 mins in total, even though not the whole setup is fully automated yet; I’m working on it to bring it to the fullest automation though. I’ll post a bit more in-depth information about how to adapt this script in future. That post will give you an understanding of the underlying structure that [ARM](https://learn.microsoft.com/en-us/azure/azure-resource-manager/management/overview?ref=kayondata.com) (Azure Resource Manager) gives you to work with PowerShell, but actually also for anything like Bicep and Terraform, as they also have to work with ARM in the end. # The new script I’m not going to give much more details on the script here, but I think I’ve put comments within the script relatively extensively: ```powershell # Existing code $ResourceGroupName = "sqlvm1" $Location = "East US" $ResourceGroupParams = @{ Name = $ResourceGroupName Location = $Location Tag = @{"Purpose" = "Demo" } } New-AzResourceGroup @ResourceGroupParams $SubnetName = $ResourceGroupName + "subnet" $VnetName = $ResourceGroupName + "vnet" $PipName = $ResourceGroupName + $(Get-Random) # Create a subnet configuration $SubnetConfig = New-AzVirtualNetworkSubnetConfig -Name $SubnetName ` -AddressPrefix 192.168.1.0/24 # Create a virtual network $Vnet = New-AzVirtualNetwork -ResourceGroupName $ResourceGroupName ` -Location $Location ` -Name $VnetName -AddressPrefix 192.168.0.0/16 ` -Subnet $SubnetConfig # Create a public IP address and specify a DNS name $Pip = New-AzPublicIpAddress -ResourceGroupName $ResourceGroupName -Location $Location ` -AllocationMethod Static -IdleTimeoutInMinutes 4 -Name $PipName # Rule to allow remote desktop (RDP) $NsgRuleRDP = New-AzNetworkSecurityRuleConfig -Name "RDPRule" -Protocol Tcp ` -Direction Inbound -Priority 1000 -SourceAddressPrefix * -SourcePortRange * ` -DestinationAddressPrefix * -DestinationPortRange 3389 -Access Allow # Rule to allow SQL Server connections on port 1433 $NsgRuleSQL = New-AzNetworkSecurityRuleConfig -Name "MSSQLRule" -Protocol Tcp ` -Direction Inbound -Priority 1001 -SourceAddressPrefix * -SourcePortRange * ` -DestinationAddressPrefix * -DestinationPortRange 1433 -Access Allow # Create the network security group $NsgName = $ResourceGroupName + "nsg" $Nsg = New-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroupName ` -Location $Location -Name $NsgName -SecurityRules $NsgRuleRDP, $NsgRuleSQL $InterfaceName = $ResourceGroupName + "int" $Interface = New-AzNetworkInterface -Name $InterfaceName ` -ResourceGroupName $ResourceGroupName -Location $Location ` -SubnetId $VNet.Subnets[0].Id -PublicIpAddressId $Pip.Id ` -NetworkSecurityGroupId $Nsg.Id # Define a credential object # For a proof of concept or demo, this is fine, but you should really use # a key vault to store your secrets otherwise. $userName = "azureadmin" $SecurePassword = ConvertTo-SecureString 'YourSecretPassword' ` -AsPlainText -Force $Cred = New-Object System.Management.Automation.PSCredential ($userName, $SecurePassword) # Create a virtual machine configuration # Note that you can change eg. SKU to an SQL Server SKU of your choice, eg # standard or enterprise edition. $VMName = $ResourceGroupName + "VM" $VMConfig = New-AzVMConfig -VMName $VMName -VMSize Standard_DS13_V2 | Set-AzVMOperatingSystem -Windows -ComputerName $VMName ` -Credential $Cred -ProvisionVMAgent -EnableAutoUpdate | Set-AzVMSourceImage -PublisherName "MicrosoftSQLServer" ` -Offer "sql2022-ws2022" -Skus "sqldev-gen2" -Version "latest" | Add-AzVMNetworkInterface -Id $Interface.Id # Create the VM New-AzVM -ResourceGroupName $ResourceGroupName -Location $Location -VM $VMConfig # New code to deploy Azure Bastion $BastionSubnetName = "AzureBastionSubnet" $BastionSubnetConfig = New-AzVirtualNetworkSubnetConfig ` -Name $BastionSubnetName -AddressPrefix 192.168.2.0/24 # Update the virtual network with the Bastion subnet $vnet = Get-AzVirtualNetwork -Name $VnetName -ResourceGroupName $ResourceGroupName Add-AzVirtualNetworkSubnetConfig -Name $BastionSubnetName ` -VirtualNetwork $vnet -AddressPrefix "192.168.2.0/24" | Set-AzVirtualNetwork # Create a public IP address for Bastion (standard sku) $BastionPipName = $ResourceGroupName + "bastionpip" $BastionPip = New-AzPublicIpAddress -ResourceGroupName $ResourceGroupName -Name $BastionPipName -Location $Location ` -AllocationMethod Static -IdleTimeoutInMinutes 4 -Sku Standard # Create the Bastion host $BastionName = $ResourceGroupName + "bastion" New-AzBastion -ResourceGroupName $ResourceGroupName -Name $BastionName ` -PublicIpAddressRgName $ResourceGroupName -PublicIpAddressName $BastionPipName ` -VirtualNetworkRgName $ResourceGroupName -VirtualNetworkName $VnetName -Sku "Basic" $StorageAccountName = "kaysbackstorageacc" # Use lowercase letters and numbers only $FileShareName = "sqlbackupshare" # Check if the storage account exists $storageAccount = Get-AzStorageAccount -ResourceGroupName $ResourceGroupName ` -Name $StorageAccountName -ErrorAction SilentlyContinue if (-not $storageAccount) { # Create the storage account if it doesn't exist New-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $StorageAccountName ` -Location $Location -SkuName "Standard_LRS" Write-Host "Storage account '$StorageAccountName' created." } else { Write-Host "Storage account '$StorageAccountName' already exists." } # Get storage account context $StorageAccountKey = (Get-AzStorageAccountKey -ResourceGroupName $ResourceGroupName ` -Name $StorageAccountName)[0].Value $Context = New-AzStorageContext -StorageAccountName $StorageAccountName ` -StorageAccountKey $StorageAccountKey # Check if the file share exists $fileShare = Get-AzStorageShare -Context $Context -Name $FileShareName ` -ErrorAction SilentlyContinue if (-not $fileShare) { # Create the file share if it doesn't exist New-AzStorageShare -Context $Context -Name $FileShareName Write-Host "File share '$FileShareName' created." } else { Write-Host "File share '$FileShareName' already exists." } # install the stuff to the vm $script1 = @" # Install NuGet and dbatools Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force Install-Module -Name dbatools -Force "@ Invoke-AzVMRunCommand -ResourceGroupName $ResourceGroupName -Name $VMName -CommandId 'RunPowerShellScript' -ScriptString $script1 ``` # More on Azure Bastion So Azure Bastion now comes in some different flavors, which is a relatively new offering. Bastion now has some different SKUs respectively tiers: Basic, Standard and Premium. Furthermore, you can choose from a private-only and developer offer. As for a better understanding of the SKUs, I recommend reading the [quite long feature overview table](https://learn.microsoft.com/en-us/azure/bastion/bastion-overview?ref=kayondata.com#sku). The main takeaway is though that if you want to use to an azure sql vm and connect to it via RDP, don’t pick the basic SKU, but a higher tier. The basic SKU won’t support SSL protected RDP connection. # How to use the script I recommend to use Azures Cloud Shell as this is the easiest way to use and you can share the script with your team, too. I like to create a folder with `mkdir` where you can store such kind of scripts. In the following picture, you’ll see how to get to the Azure Editor that is heavily inspired by vscode. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743208815357/ff614716-3992-41f2-a9b3-3bf767575eb8.png) ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743209209797/0b4a990f-9256-4743-b877-c62d39339ec3.png) ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743209867277/92f6e026-b5ec-4c6e-aa1b-60548df7e47e.png) Create a \*.ps1 file with the name you like (see screenshot above, you can use `touch` to create a file). Open it with the editor (mouse click no. 4 in above image and click no. 5 in below image) and copy & paste the script from above inside. You can save the script with \[ctrl\]+\[s\] or via the menu of the editor. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743233967303/f6e356ab-5a6a-423b-ad88-6bb297097c86.png) # More to come for full automation Currently, my script that executes a PowerShell script against the created VM via `Invoke-AzVMRunCommand`, unfortunately does not create a working attached network storage yet. There is an issue that I am still investigating and hoping that I can resolve it in the next few weeks. Once I’ll figure that one out (or if you have a tip for me - please let me know!), then I’ll post it to complete this script. Until then you can use the following post deployment script on the vm directly as a workaround: ```powershell # Map the network drive $connectTestResult = Test-NetConnection -ComputerName sqldatabasebaks.file.core.windows.net -Port 445 if ($connectTestResult.TcpTestSucceeded) { # Save the password so the drive will persist on reboot # For a proof of concept / demo, this approach is fine, # but normally, you should want a key vault to store your secrets! cmd.exe /C "cmdkey /add:`"sqldatabasebaks.file.core.windows.net`" /user:`"localhost\sqldatabasebaks`" /pass:`"YourPassword`"" # Mount the drive New-PSDrive -Name Z -PSProvider FileSystem -Root "\\sqldatabasebaks.file.core.windows.net\sqlbaks" -Persist } else { Write-Error -Message "Unable to reach the Azure storage account via port 445. Check to make sure your organization or ISP is not blocking port 445, or use Azure P2S VPN, Azure S2S VPN, or Express Route to tunnel SMB traffic over a different port." } # copy to mssql folder for restore with dbatools # you may need to adapt the folder path according to your own file storage system Copy-Item -Path "Z:\*.bak" ` -Destination "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup" ` -Recurse # use dbatools for restore dbs Set-DbatoolsConfig -FullName sql.connection.trustcert ` -Value $true # to ensure that dbatools trusts the certificate Restore-DbaDatabase -SqlInstance sqlvm1VM -Path "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup" ``` # How to connect to the VM via Bastion First, go to your resource group and click on the VM so you get into its information window: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743231743288/ba75ab9b-e138-435b-9928-5c02deca3508.png) After this, you have 2 ways to connect to the VM via Azure Bastion, both ways are marked with the click no 7 in the screenshot below. Either you use the left side vertical menu or the horizontal menu. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743232033354/4531743c-ae05-4f31-99b5-f1f9b2f32083.png) After this you get to the menu where you enter the credentials you already used in the script (that is hopefully stored in a key vault). Click on connect. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743232059468/377f55a8-9903-431d-9565-134997ac3309.png) After this, a new browser tab opens and on the top left, you’ll be asked if you allow to transfer text data to the VM, which you want for this script. In the next screenshot, you’ll see “Zulassen” which is German for “Allow”. Click on allow. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743273914122/38fcf565-f5c6-452f-8079-3100fc4cb41f.png) For the backups, I like to use Azure File Storage which is a relatively cheap storage. I use this for SQL Server backups. That storage can be mapped to a network drive on the VM. So in order to retrieve the password and as well the script to do so, lets go to its resource group. I recommend to use a different resource group for the storage than the VM. That allows you to delete the VM from time to time to save costs or for a fresh setup. To do so, you simply can delete its resource group, and because your file storage is in another, your backup resource group will stay. If you want to upload backups to your file storage, you can do this very easy this way: Go to your storage account, and here you’ll have the possibility to click on a button that opens Azure Storage Explorer for you. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743232779712/966b03d3-58f4-4529-8edb-9e9e650b7a08.png) In there, you need to go to your file storage and drag and drop your files to upload. The next screenshots show you how to retrieve the code for your own environment, for your password and your own storage: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743232664980/9393dec3-f773-4a88-ba27-e6a438c30cc8.png) ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743232974918/068d4800-05b0-4ac4-b9a4-4d3b61ebf0b9.png) ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743232988684/4f0fcf66-7990-4df6-97ee-75c381b78692.png) ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743232997411/01dbe48e-f399-4a32-a55b-654e48ac98ee.png) Use this script and overwrite this part in my deployment script, so you’ll have exactly what you’ll need for your own file storage. After you executed the deployment script (takes about 20 mins) copy the post deployment script (the last code block above) and copy it into PowerShell ISE, then run it: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743233003640/19e7317d-c363-42f8-87de-cfe8d834e305.png) ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743233010531/550f1273-6348-4ec6-a5d3-2e68c17c41a7.png) ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743276191570/72533627-b51c-4cdd-b1f1-b7e561711b2c.png) After you’ve run, open SSMS and make sure that you’ll tick “Trust server certificate” as we don’t have a server certificate on this VM. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743233043135/8a4acdd9-9096-4718-8d47-576d7609007d.png) Now, finally, you’ll see that, in my example case, AdventureWorks2022 and WideWorldImporters got restored. The restore part was magically done by dbatools, with just a few lines of code! ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1743233049291/0870390b-3581-4b75-8163-3f480f09cf21.png) Do you have any questions? Any feedback? Could I have done anything better? You want to give input to improve my code? Let me know in the comments or send me a message! ### How to fix the red lines in MS Fabric notebooks URL: https://www.kayondata.com/how-to-fix-the-red-lines-in-ms-fabric-notebooks/ Last updated: 2026-06-16T11:27:07.000Z In my last article ([hashnode](https://hashnode.com/post/cm3opi1kl001309k13zk41cs0?ref=kayondata.com), [LinkedIn](https://www.linkedin.com/pulse/loading-files-automatically-bronze-lakehouse-kay-sauter-wxjdf/?trackingId=two%2BRbHsTNC5XoXNIsTExw%3D%3D&ref=kayondata.com), [Substack](https://kayondata.substack.com/p/loading-files-automatically-to-bronze-lakehouse)) of this series, I covered on how to load csv files in a lakehouse automatically in MS Fabric. In this article, I am going to discuss how we can find and fix errors with notebooks easily. Firstly, we want to get a nice overview over all csv files that failed to load. This can be done in a lot of different ways, but I like to do it this way: ```python # check local log from pyspark.sql.functions import when df_log = df_log.withColumn("Check", when(df_log["row_amount_diff"] == 0, "✅").otherwise("❌")) # these special characters from [Win Key] + [.] or another smiley picker ``` With this, you’ll get this output: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1741816754823/a511308e-d407-4ed5-b8df-c2052f6e5a98.png) If you remember the script in the previous article, the script opened the files first, just to check how many lines it sees in there. Later, the script checks how many lines were loaded and the row amount diff gives back if there was a difference. If yes, it would not be 0, obviously. The last column gives a very nice indication of whether or not a csv file needs attention. If there’s ❌, you probably want to check the line. Note that the columns of a such table are sortable, so you can of course also sort the table after the ❌ signs, and you’ll see all failed loads instantly. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1741817052067/15e5ed93-b744-484b-b4ad-feb6269f9969.png) So next, in the same notebook, I’d like to see if the meta lakehouse actually received the data, too. I use the meta lakehouse for such kinds of events. This serves a lot of purposes: One being able to measure problems in the future. Do always the same files fail? Is there a pattern, maybe they only ever fail on certain dates like every leap year and nobody would know if we wouldn’t have a such log? Or every easter? ```python # check log in meta_lakehouse df = spark.sql(f"SELECT * FROM meta_lakehouse.load_log_bronze order by load_timestamp_utc_bronze desc LIMIT {csv_count}") display(df) ``` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1741817228906/8c095d98-0b14-4aad-b4a6-79fa1e19fb96.png) So here, it is clear that the lakehouse did indeed work as intended, too. Now, lets go back to the code that gave us warning in the first place, I actually have another small trick here: ```python # Print the number of tables that gave warnings if warning_count == 0: print(f"\n\033[92m Number of tables with warnings: NONE - SUCCESS \033[0m") else: print(f"\n\033[91m Number of tables with warnings: {warning_count} → GO CHECK LOG! \033[0m") displayHTML('Click here to get to the notebook to investigate this issue') ``` The last line, the one I added for this post, adds a link to another notebook (i redacted the exact link to my notebook). ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1741817435049/a8c47295-caf3-4298-a134-d85b8cf5a9b1.png) If I click on this link, I get to the next notebook with wich I can investigate the issue. In other words, notebooks are a great way to give me hints on how I can fix an issue! This new notebook has some similar code, but designed to show you which lines are responsible for the failure: ```python from pyspark.sql import SparkSession from pyspark.sql.functions import monotonically_increasing_id # Path to your CSV file csv_file = "Files/Person.Person.csv" # Get the number of lines in the CSV file lines = spark.read.text(csv_file) line_count = lines.count() - 1 # -1 to exclude the header print(f"{csv_file} should have this amount of lines: {line_count:,}".replace(",", "'")) # Read the CSV file with PERMISSIVE mode to get all records df_all = spark.read.options( delimiter=",", header=True, encoding="UTF-8", inferSchema=True, multiLine=True, escapeQuotes=True, mode="PERMISSIVE" ).csv(csv_file) # Read the CSV file with DROPMALFORMED mode to get only valid records df_well = spark.read.options( delimiter=",", header=True, encoding="UTF-8", inferSchema=True, multiLine=True, escapeQuotes=True, mode="DROPMALFORMED" ).csv(csv_file) # Identify malformed records by finding the difference df_mal = df_all.subtract(df_well) # Add a record number to the malformed records DataFrame df_mal = df_mal.withColumn("record_number", monotonically_increasing_id()) columns = ['record_number'] + [col for col in df_mal.columns if col != 'record_number'] df_mal = df_mal.select(columns) # Show the malformed records with their record numbers print(f"The amount of read well formed lines: {df_well.count():,}".replace(",","'")) print(df_mal.count()) display(df_mal) ``` This returns me this: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1741817855706/2eb34a56-28b6-4a31-8c82-b04bf1b968cf.png) So the print output tells me that there was a load failure because of 4 lines. Those 4 lines are visible in the table. Upon investigation of this table, you can notice that something in the column “Demographics” doesn’t add up, hence the failure must have happened in the column before. Most likely, a character is responsible for this, as we see in “AdditionalContactInfo” XML data which is very prone to failures like this. So either a character needs to be taken care of or XML data in general should be attacked differently, eg. in an own table. Another possible solution is to avoid XML altogether and just scrap everything in there in a different way so that XML never gets loaded. I’m sure there could be other ideas for this, too. If you want, let me know in the comments! ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1741818056094/d2152de5-b4d2-422f-a1e1-6821c653d2b1.png) I think this notebook also should give the person who is investigating this issue an idea of other incidents. A such overview is achieved very easily: ```python df = spark.sql("SELECT * FROM meta_lakehouse.bad_records LIMIT 1000") display(df) ``` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1741818421820/84b3a847-74dc-40bc-837e-b179b6fdb6d0.png) So this is another great example of make use of a meta lakehouse that may support you to investigate such issues. If you click on “entire\_record”, you actually indeed can see the entire record: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1741818520440/2d523732-9405-443b-85e9-966c57ed49de.png) So I think I've shown with this that notebooks are a really great way to support people on investigating issues. Meta lakehouses are obviously not meant to be accessible for everyone, but they may have data that business users may find helpful, eg. if you have a Power BI report that you want give an indication that the data is not fresh for this specific report yet. Make use of your own comments and your own failure reports so that you find your issue easier and quicker instead of drowning in error logs. Notebooks do have error logs, and they may be helpful too, but they have log a lot of information that may make it a bit convoluted to point you to your solution to the problem. However, I will cover this in a future post. If you have any questions, I’m looking forward to get them in the comments below! Or if you have any suggestions on what I could improve in them: I am all ears! Cover image credit: created with Microsoft Bings CoPilot. ### I've moved my blog on kayondata.com to Hashnode! URL: https://www.kayondata.com/ive-moved-my-blog-on-kayondata-com-to-hashnode/ Last updated: 2026-06-16T09:05:45.000Z I was actually quite happy with [Hugo CMS](https://gohugo.io/?ref=kayondata.com), for it is a great blog system. Unlike the most popular CMS out here, it doesn’t really need any frequent updates to be secure. It uses Markdown which I love, and I love to use within my favorite code editor, [VSCode](https://code.visualstudio.com/?ref=kayondata.com). However, Hugo had its downsides. Because you have to start a server on your computer to see its results, you always had to use something like `hugo -D` etc. and remember how to create a new blog with something like `hugo new content content/posts/`[my-first-post.md](http://my-first-post.md/?ref=kayondata.com), actually a quite good idea. I would, however, always forget how to create a new post and have to wait to see the post in the browser. Lastly, with Github Actions, I would have to patiently wait until it is uploaded. I was quite liking it however. The reason I made the switch though were two things: I somehow messed up my repo, possibly due to the Hugo generator update (the software you install on your laptop locally). Secondly, I actually wanted to have a blog that people could add comments to, and a subscription newsletter. The comment part could have been solved with Github, but it is convoluted to create and depends on the template you use. The newsletter part however, was much trickier. Some were quite expensive, others had strange limitations. With [Hashnode](https://hashnode.com/?ref=kayondata.com), I finally found something that solves both problems, and even a very nice, easy to use workflow for blogging. I even can use Github again here, I am using it for backing up my blogs. The domain is the same as before: [kayondata.com](http://kayondata.com/?ref=kayondata.com). The move was quite easy, especially with the great support by Hashnode! I received the answers to my questions within a few hours in most cases, but never more a half day! So if you are looking for a possibility for a newsletter outside of my [LinkedIn Newsletter](https://www.linkedin.com/newsletters/kay-on-data-7043592227159207936/?ref=kayondata.com), you can now use Hashnode to subscribe to my posts. Additionally, you can use RSS here, too. I hope you will subscribe to both my newsletters, on [kayondata.com](http://kayondata.com/?ref=kayondata.com) and on [LinkedIn](https://www.linkedin.com/newsletters/kay-on-data-7043592227159207936/?ref=kayondata.com). Credit: Cover picture generated by Microsoft Copilot. ### Loading files automatically to bronze lakehouse URL: https://www.kayondata.com/loading-files-automatically-to-bronze-lakehouse/ Last updated: 2026-06-16T11:16:55.000Z In my last [post on LinkedIn](https://www.linkedin.com/pulse/getting-adventureworks-fabric-kay-sauter-l1qpf/?trackingId=JL%2FNed7HQxigHSrBVOSroQ%3D%3D&ref=kayondata.com), I explained how to export AdventureWorks2022 tables to csv files. If you don’t want to generate them, you can get them [here](https://github.com/kaysauter/getting-adventureworks-to-fabric?ref=kayondata.com), as stated in the last post (after my edit in which I realized I made a mistake). My actual blog [https://kayondata.com](https://www.kayondata.com/) is currently being moved to another place, hence not updated at the moment. Certainly, it is best to avoid CSV files today. However, CSV files are still very common, so I am using this approach first, but I plan on writing other posts on other ways to do this. Another reason why I am using CSV files is the fact that this is also a very nice starting point to learn some basics and to exercise some data engineering stuff. Nevertheless, if you find yourself in a situation of choosing loading directly from a database without files, usually, this is the better route. If you have the possibility to use parquet instead of CSV, usually parquet should be the winner in the decision. That said, let’s start with some code that I’ve written some months back on runtime 1.2 (Spark 3.4, Delta 2.4), but it should work on runtime 1.3 (Spark 3.5, Delta 3.2) as well, according to my test today. The code does quite some stuff, and it expects you to have an existing lakehouse that your notebook is attached to. Moreover it expects you to have a lakehouse called *meta\_lakehouse* which is used to log the data loads and errors. You’ll see an output that returns some simple plausibility checks: ```python import os, time, datetime from pyspark.sql.functions import monotonically_increasing_id, lit, current_timestamp, when, col, regexp_replace, array, concat_ws from pyspark.sql import Row from pyspark.sql.types import StructType, StructField, StringType, IntegerType, TimestampType from notebookutils import mssparkutils # Specify the folder containing CSV files csv_folder = "Files" # Get a list of all files in the folder files = notebookutils.fs.ls(csv_folder) # Filter for CSV files csv_files = [file.path for file in files if file.name.endswith(".csv")] # to test only one file, simply write the name before .csv # Define the schema for the log DataFrame so it has have good column names log_schema = StructType([ StructField("file_name", StringType(), True), StructField("row_amount_file", IntegerType(), True), StructField("row_amount_table", IntegerType(), True), StructField("row_amount_diff", IntegerType(), True), StructField("load_timestamp_utc_bronze", TimestampType(), True) ]) # Define the schema for the malformed records DataFrame so it has good column names malformed_schema = StructType([ StructField("file_name", StringType(), True), StructField("record_number", IntegerType(), True), StructField("entire_record", StringType(), True), StructField("explanation", StringType(), True), StructField("timestamp", TimestampType(), True) ]) # Initialize an empty DataFrame for log entries with the defined schema df_log = spark.createDataFrame([], schema=log_schema) df_mal = spark.createDataFrame([], schema=malformed_schema) df_mal_log = spark.createDataFrame([], schema=malformed_schema) # Initialize a counter for warnings warning_count = 0 # count files csv_files_count = [file for file in files if file.name.endswith('.csv')] csv_count = len(csv_files_count) file_processed_count = 0 # Process each CSV file for csv_file in csv_files: file_processed_count += 1 table_name = os.path.splitext(os.path.basename(csv_file))[0] # Extract table name from the file name (remove ".csv" extension) table_name = table_name.replace(".","_") # remove "." for database schema and replace it with "_" print(f"{table_name}.csv is being processed. Remaining files to process: {csv_count - file_processed_count}") # Get the number of lines in the CSV file lines = spark.read.text(csv_file) line_count = lines.count() - 1 # -1 to exclude the header print(f"{table_name} has this amount of lines: {line_count:,}".replace(",", "'")) # Read the CSV file with PERMISSIVE mode to get all records df_all = spark.read.options( delimiter=",", header=True, encoding="UTF-8", inferSchema=True, multiLine=True, escapeQuotes=True, mode="PERMISSIVE" ).csv(csv_file) # Read the CSV file with DROPMALFORMED mode to get only valid records df_well = spark.read.options( delimiter=",", header=True, encoding="UTF-8", inferSchema=True, multiLine=True, escapeQuotes=True, mode="DROPMALFORMED" ).csv(csv_file) # Identify malformed records by diff it df_mal = df_all.subtract(df_well) # Add a record number to the malformed records dataFrame df_mal = df_mal.withColumn("record_number", monotonically_increasing_id()) columns = ['record_number'] + [col for col in df_mal.columns if col != 'record_number'] df_mal = df_mal.select(columns) # Show the malformed records with their record numbers print(f"The amount of read well formed lines: {df_well.count():,}".replace(",","'")) if df_mal.count() == 0: print(f"\033[92mThe amount of read mal formed lines: {df_mal.count():,}\033[0m".replace(",","'")) else: print(f"\033[91mThe amount of read mal formed lines: {df_mal.count():,}\033[0m".replace(",","'")) # display(df_mal) # Add id column and put it into the front df_well = df_well.withColumn("id", monotonically_increasing_id()) columns_ordered = ["id"] + [col for col in df_well.columns if col != "id"] df_well = df_well.select(*columns_ordered) # Add load_timestamp df_well = df_well.withColumn('load_timestamp_utc_bronze', current_timestamp()) # Write to Delta table df_well.write.format("delta").mode("overwrite").option("overwriteSchema", "true").saveAsTable(table_name) # Check the number of rows in the Delta table delta_table = spark.table(table_name) delta_count = delta_table.count() print(f"{table_name} has this amount of lines in the delta table: {delta_count:,}".replace(",", "'")) # Create a dataframe for the current log entry df_current_log = spark.createDataFrame([( table_name, line_count, delta_count, line_count - delta_count, datetime.datetime.now() # Use current timestamp )], schema=log_schema) # Append the current log entry to the log DataFrame df_log = df_log.union(df_current_log) # Append the current malformed dataframe to a log dataframe # display(df_mal) # df_mal_log = df_mal_log.union(df_mal) # Prepare the malformed records for logging df_mal_log_entry = df_mal.withColumn("file_name", lit(table_name)) \ .withColumn("entire_record", concat_ws("|||", *df_mal.columns)) \ .withColumn("explanation", lit("malformed")) \ .withColumn("timestamp", current_timestamp()) \ .withColumn("record_number", monotonically_increasing_id()) # Select the columns in the desired order df_mal_log_entry = df_mal_log_entry.select("file_name", "record_number", "entire_record", "explanation", "timestamp") # Append the current malformed records to the log DataFrame df_mal_log = df_mal_log.union(df_mal_log_entry) # Compare the counts and print messages if line_count == delta_count: print(f"\033[92mSUCCESS: {table_name}.csv loaded into Delta table {table_name} with matching line counts\033[0m") print(f"\n") else: print(f"\033[91mERROR: {table_name}.csv line count {line_count:,}".replace(",", "'") + f" does not match Delta table line count {delta_count:,}".replace(",", "'") + f" \033[0m") print(f"\n") warning_count += 1 # Write the log DataFrame to the lakehouse log table in the meta_lakehouse database if not df_log.filter(df_log["row_amount_diff"] > 0).rdd.isEmpty(): df_log.write.format("delta").mode("append").saveAsTable("meta_lakehouse.load_log_bronze") print(f"\n\033[93m ALL CSV files LOADED with WARNING into meta_lakehouse.load_log_bronze table.\033[0m") else: print(f"\n\033[92m ALL CSV files SUCCESSFULLY LOADED into meta_lakehouse.load_log_bronze table.\033[0m") # Write the malformed records DataFrame to a new Delta table in the meta_lakehouse database if not df_mal_log.rdd.isEmpty(): df_mal_log.write.format("delta").mode("append").saveAsTable("meta_lakehouse.bad_records") print(f"\n\033[93m Malformed records have been written to the meta_lakehouse.bad_records table.\033[0m") # Print the number of tables that gave warnings if warning_count == 0: print(f"\n\033[92m Number of tables with warnings: NONE - SUCCESS \033[0m") else: print(f"\n\033[91m Number of tables with warnings: {warning_count} → GO CHECK LOG! \033[0m") ``` So if you run this, you’ll see a lot outputs like this: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1731968713774/0b90e070-cc43-442f-96fc-1e488c6f0a60.png) As you can see, it outputs some table names and csv names. The csv-names are not actually very correct here, because the actual csv name is HumanResources.Departement.csv, so there is a dot that derives from the database schema. In this case, I am using a lakehouse without schema (it is still in preview) and the lakehouse does not allow points in a table name, hence I replaced the point with an underscore in line 53 via `table_name = table_name.replace(".","_")`, as also the comment after the very code explains. Now, you could ask why I didn’t want to use shortcuts to load the data - I could have done this and it is a neat trick for many occasions. In this case, I not only wanted to showcase the possibility to use a meta\_lakehouse, but also the possibility to have more control in the data that gets loaded here. For example, I get the possiblity to have some simple plausibility checks that certainly could be done much more sophisticated. This is going to be another blog in future. An interesting code block is here (line 62): ```python # Read the CSV file with PERMISSIVE mode to get all records df_all = spark.read.options( delimiter=",", header=True, encoding="UTF-8", inferSchema=True, multiLine=True, escapeQuotes=True, mode="PERMISSIVE" ).csv(csv_file) # Read the CSV file with DROPMALFORMED mode to get only valid records df_well = spark.read.options( delimiter=",", header=True, encoding="UTF-8", inferSchema=True, multiLine=True, escapeQuotes=True, mode="DROPMALFORMED" ).csv(csv_file) ``` Firstly, we get to give pySpark the possiblity to load all data and to try to figure out what datatypes the fields are. delimiter=”,” tells pySpark that it should use commatas, but you could use semicolon if needed, or another character. `inferSchema=True` is actually superfluous here as the `spark.read.options` uses this per default, but I think it is always a good idea to be explicit. In this case, the code reads the whole file, and for other file formats other than csv, you can tweak this with samplingRatio. There are many other options you can use here, and there’s a quite comprehensive [documentation here](https://spark.apache.org/docs/latest/sql-data-sources-csv.html?ref=kayondata.com). Be aware that inferring a schema based on a whole file isn’t what you want in all cases, especially if your file is too big. If you are aiming for speedy loads, then you should not using inferring schema, but define the targeted schema from the start. In this case, with relatively small amount of data of an AdventureWorks2022 sample data, I think this is a good showcase. By the way, with this you will also be able to get the schema pretty easy. Another trick I am showcasing here is the possiblity to get the data that were malformed reads with a diff (line 84): ```python # Identify malformed records by diff it df_mal = df_all.subtract(df_well) ``` Last thing I want to point out for today is the possiblity to write out statements in colors (line 154): ```python # Write the log DataFrame to the lakehouse log table in the meta_lakehouse database if not df_log.filter(df_log["row_amount_diff"] > 0).rdd.isEmpty(): df_log.write.format("delta").mode("append").saveAsTable("meta_lakehouse.load_log_bronze") print(f"\n\033[93m ALL CSV files LOADED with WARNING into meta_lakehouse.load_log_bronze table.\033[0m") else: print(f"\n\033[92m ALL CSV files SUCCESSFULLY LOADED into meta_lakehouse.load_log_bronze table.\033[0m") ``` The strange code within print(f” ) is a code for coloring text. The difference we see in the warning output and the success output is `[93m` (yellow) and `[92m` (green) At the very end, there’s an output like this: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1731970683000/502acd90-8fad-4397-91fa-0bd4e04b2f42.png) The last line hints that the next blog post will be about how to find the issues easily and how to fix them. Notebooks are great to instruct people on what to do, especially if they have links to the next notebook! Do you have any improvements I could have made in my code? Or do you have a question you want to discuss? Even better, found any mistake? Let me know in the comments! This post was first published on [Fabric community blogs](https://community.fabric.microsoft.com/t5/Data-Engineering-Community-Blog/Loading-files-automatically-to-bronze-lakehouse/ba-p/4289735?ref=kayondata.com) for data engineering. Credit: Cover image was created by DALL-E by Microsoft CoPilot. ### Getting Adventureworks to Fabric URL: https://www.kayondata.com/getting-adventureworks-to-fabric/ Last updated: 2026-06-16T11:25:25.000Z Today, I am starting a series of blog posts on how to get the AdventureWorks database to Fabric. This is a series of blog posts that will cover a journey from exporting the AdventureWorks database to csv files, to getting the data to Fabric Lakehouse and then to the Data Warehouse. Along the way, I will be discussing some approaches and tips. If you don't know the AdventureWorks database, it is a sample database by Microsoft. It is used to demonstrate the capabilities of SQL Server. The database is used in many examples, tutorials and documentation by Microsoft. You can download the database [here](https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms&ref=kayondata.com). There are many versions of it, and i'll be using the AdventureWorks2022 database. I've got my database on a local SQL Server instance. The first step is to export the data to csv files. If you're using a different database, please make sure that it isn't a productive database as exporting all data to csv files can be expensive process. This is not to say that it can't be used in a productive environment, but test it on a non-productive environment first. This way you'll be able to see how long it takes and how much cpu and memory it uses on your system. So, let me walk you through the Python code by the comments within it: ```python # importing some libraries import pathlib as pl import pyodbc import pandas as pd # define the folder path to where the csv files will be exported to folder_path = pl.Path( r"C:\Users\userName\OneDrive\csv" ) # build connection string to the database conn = pyodbc.connect( "DRIVER={ODBC Driver 17 for SQL Server};" "SERVER=localhost;" "DATABASE=AdventureWorks2022;" "Trusted_Connection=yes;" ) # create cursor cursor = conn.cursor() # get all sql server table names qAllTables = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'" table_names = pd.read_sql_query(qAllTables, conn) print(f"table_names:{table_names}") # Loop over all tables, get the data with a SELECT *, put it into a df and # then save the data to csv print(f"Number of tables: {len(table_names)}") for table in table_names["TABLE_NAME"]: if table == "sysdiagrams" or table == "DatabaseLog": # Skip system tables continue query = f"SELECT * FROM {table}" df = pd.read_sql(query, conn) file_path = folder_path / f"{table}.csv" print(f"Saving to: {file_path}") df.to_csv(file_path, index=False) conn.close() # close the connection, always a good move. ``` I am assuming that you already have a Fabric capacity. If you don't you may be eligible for a [free trial](https://learn.microsoft.com/en-us/fabric/get-started/fabric-trial?ref=kayondata.com). In this case, you may want to follow the steps as described on Microsofts documentation website [here](https://learn.microsoft.com/en-us/fabric/get-started/fabric-trial?ref=kayondata.com#start-the-fabric-capacity-trial). Next, go into your Fabric capacity. Here, you should create a new workspace and a new lakehouse: Click on the "Create Workspace" button in the left menu bar (1) which opens a fold-out window. After this, click on the green "+ Create Workspace" button (2) in the bottom left corner. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1724367604236/cdc35ee3-112a-41fe-98da-2bef9af56a74.png) - Next, on the right side, another flap-out window is opening. Here, you can enter your workspace name. The description is optional. If you can, I advise to use the trial capacity that may be available to you. For this click ont he radio button a bit further down in that very window. At the bottom of this very window, you can click on the green "Apply" button to create the workspace. The other options are optional and are not needed for this tutorial. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1724367638294/e96a2e23-96de-40f9-822a-1a7cab75c90c.png) Go into your workspace you just created. Click on "Workspaces" and you'll find the workspace that you've just created in the flap-out window on the left. Next, click on the \[+ New\] button on the left top menu bar. This will open a drop-down window from where you'll be able to choose a new lakehouse. Click on the "Lakehouse" option. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1724367694774/3e502b78-8db7-479e-ac1e-a661b39220c4.png) ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1724367683152/2f265758-1e75-4625-8f55-1e91ff977eef.png) Next, you'll want to enter your lakehouse name into the pop-up window that appeared. I recommend to use a name that is something like adventureworks\_bronze. This makes it easier to understand what this lakehouse contains and that it is a bronze layer of the lakehouse. I'll be explaining the layer concept in a later blog post, but for now, we don't have to worry about that. If you have the option to click on "Lakehouse schemas" that is currently in public preview, I recommend not to choose this. The reason is that it can make the concept a bit more complicated which we don't need for this tutorial. In a later post, I'll cover this, but for now, let's skip this. Click on the green "Create" button to create the lakehouse. Now that we've created it, we can upload our files. In the middle of the screen (1), we have the possibility to upload the files, but we also could make use of a right click on the folder "Files" (2) that you see on the left side of the screen. The advantage the second "method" is that you actually see the folder where your files will be uploaded to. You can create a folder in that folder if you want to. For this tutorial, i am going to keep this simple and will upload the files directly into this root folder. In the flap-out window on the right (3) you can go to your local folder and select all your files you've just created with the Python script above. If you choose the option "Overwrite if files already exist" you can upload the files again and overwrite the existing ones. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1724367774352/5a25e798-7359-410c-9db1-b2d07d97c830.png) So this is for today, I'll be back with the next blog post on this soon! Credit: Cover image was created by DALL-E via Microsofts Copilot. ### T SQL Invite December 2023 URL: https://www.kayondata.com/t-sql-invite-december-2023/ Last updated: 2026-06-16T13:14:21.000Z T-SQL Tuesday No. 169 – December 2023 I’ve got the honor to invite you to the blog party [SQLTuesday](https://tsqltuesday.com/?ref=kayondata.com), tagged as #tsql2sday, for this December 2023\. Many thanks to Steve Jones ([blog](https://voiceofthedba.com/?ref=kayondata.com), [twitter](https://twitter.com/way0utwest?ref=kayondata.com)) for letting me host this month’s blog party! December is the last month of a year. I believe December is the season of being thankful, or at least looking back. I also believe that we generally do not say “thank you” enough to people. So I invite and encourage you to say thank you to anyone who helped you: - Work colleagues or employers - Mentors - Friends - Family Your post does not have to be personal, it can also be just a story you anonymize people so that the reader who it actually matters, will know that you appreciate them. If you even can incorporate some code, please do! Happy blogging and happy SQLTuesday! Please publish your blog article on December 12 2023. ### Fabrics OneDrive URL: https://www.kayondata.com/fabrics-onedrive/ Last updated: 2026-06-16T13:16:16.000Z # OneLake Sorry, my title indeed said "OneDrive" and that is actually by intention. It is really easy to mix them up, just look at this screenshot I did on my Laptop. It shows the file explorer and it very much looks the same. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732647247431/11c3b9a5-372f-46b1-bed7-3a93b5387231.jpeg) OneLakes logo is also shown in the taskbar in the bottom right corner next to the clock in Windows, just like OneDrive. At this point, I want to mention it, since I've fogotten to mention it in the last post. Currently, Microsoft Fabric is still in preview. So what is the big deal with OneLake? Well, if you are using Fabric, you'll notice that OneLake is pretty much the data center for any of Fabrics data experiences: - Data Factory - Synapse Data Engineering - Synapse Data Warehouse - Synapse Data Science - Synapse Real Time Analytics - Power BI You could imagine this as a centre for all data. You want to provide data for Data Science? OneLake can provide you this. You also can use it for your DataLake to source your DWH in Fabric. Actually, you could imagine it as the center of Fabric. # OneLake Shortcuts Microsoft also provides [OneDrive Shortcuts](https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts?ref=kayondata.com). This is a Feature that allows you to unify your data in one place, hence avoiding duplication of your data. It is a virtualization of the data and you can access it from different experiences and places. In other words, you could imagine them as links that point to the actual places of data. I don't want to go much deeper in this, because the previous link to Microsofts documentation is excellent. # OneLake as data driver A lot of companies strive to be a data driven company. Understandably so, because it means that decisions that need to be taken, be it as an analyst, data scientist, data engineer or anyone of the C-level management, will be based on data. OneLake gives transparency to everyone to the structure that data may provide and any data worker can incorporate them in their work. So in my opinion, OneLake helps to centralize data to any organization. Architects and Security Engineers need to define together with the business and other data workers, how to organize the data system in the organization. Once this is done, and [security permissions are set](https://learn.microsoft.com/en-us/fabric/onelake/onelake-security?ref=kayondata.com), I believe, this will propel the mutual understanding of an organizations existing data across departements. So this was about OneLake. In my next post, I will go deeper in Shortcuts to show you how to use them and to glide in to the next topic. Caption for cover picture: OneLakes Logo by Microsoft ### Getting into Fabric URL: https://www.kayondata.com/getting-into-fabric/ Last updated: 2026-06-16T13:34:00.000Z # Finally, I've found time to dig deeper into Microsoft Fabric So finally I've found time to dig deeper into Microsoft Fabric, the newcomer in Microsofts data platform family. I've played a bit with it and I thought I should blog about some distinct differences in between Azure Synapse Analytics and SQL Server. In the beginning, I have to admit, that MS Fabric quite confused me in terms of what it could mean for Azure Synapse. Now that I've got more knowledge through reading into it and more importantly, playing around with it, I can draw some conclusions. But first and foremost, I think anyone should understand that MS Fabric is a SaaS offering, while Azure Synapse is a PaaS offering. This means that inherently, there are different approaches and use cases for both. # Fabric vs Synapse? Anyways, I do not think that MS Fabric is going to replace Azure Synapse. I guess both will co-exist. Just like the way for example, Azure SQL and Azure SQL Managed instance co-exist. They both do not mean that they are going to replace SQL Server, neither will any of them replace each other. There are some hints for this, one being this video: {{< youtube 6BI89Y2S-Oo >}} This video deserves much more attention than it has (shout-out to [Ginger Grant](https://www.linkedin.com/in/ginger-grant/?ref=kayondata.com) for pointing me to this video!). Also the channel needs much more attention than it does have! As I am intending to start a small series about Fabric, I want to begin with some differences, as I mentioned in the beginning of the article. # Roundup - Fabric has in its core, center, and is functioning around one concept: OneLake. I believe this is the thing that makes the magic of all of it possible in the first place. All services around it, they work incredibly smoothly and well together. I really admire that concept. - OneLake inherently improves the concept of having a better convergence of people working with data, one and non-duplicated data. Think about this: Data Scientists, Data Engineers, and heck, Business Analysts can share their data in one place. One place to rule them all (data, not the people 😉). I think that is quite revolutionary if you think about the fact that most data today are pretty much scattered in data silos. Fabric is all about breaking up data silos. - While Fabric as it is today, does integrate many services existing today in one SaaS offering, it does not cover all abilities of each service. Nevertheless, it is a strong, powerful and very enjoyable tool to work with. So this is the start of the mini series in which I will blog about Fabric. I will try to keep it succinct as possible and blog what I've am learning about Fabric or Azure Synapse Analytics, to help you to understand the differences and similarities of them. ### My week at SQLBits 2023: Why You Should send your Employees to Conferences URL: https://www.kayondata.com/my-week-at-sqlbits-2023-why-you-should-send-your-employees-to-conferences/ Last updated: 2026-06-16T20:46:04.000Z > This post was first published on my LinkedIn Newsletter on 20 March 2023. As I'm now tired, but happy and heading back on my bus of national express to Heathrow airport and taking a plane there back to my hometown Zurich, Switzerland, I'd like to wrap up my days here in Wales for the extremely fantastic SQLBits conference. It was amazingly well organized, and I really hope I may return next year! I truly had a blast being here, while learning tons, meeting lots of people and last but not least, speaking here. So, I really want to say a huge thank you to everyone who were somehow involved with #sqlbits2023 that made it a such great success as it is! That includes not only the organizing team (Simon Sabin, Darren Green, Anette Allen (sorry, couldn't find you!), Jonathan Allen, Alex Whittles, Rob Sewell, Tracy and last but not least Sewell (sorry again!)). But this also includes the numerous amout of helpers, volunteers and all the sponsors of SQLBits. Last but not least, all the visitors that made the conference what it was - a truly extraordinary experience and incredibly thrilling, resourceful place to learn. For those who were not as lucky as I was, to be able to go to #SQLBits2023, I absolutely believe that employers should send their fair share of delegation of people to conferences like this. I often get to hear that "you can't measure the value of a conference like this". However, I am sure that many, many things that companies do and believe in, they can't be measured either. Yet, there is undeniably a benefit, for example having a marketing department. For a lot of campaigns, the direct benefit is hard to measure, and many companies choose not even bother to measure them properly, yet there is undeniably a benefit from it. The same is true for people who were being sent to conferences, for they not only learn more about stuff that they may face sooner or later. And it is work, too. Trust me, after the conference, I was worn out, I was tired, even though I had an exciting time. People may have fun at conferences and that is because they love their topic. But they not only learn more about solving problems, but also networking. Networking, talking to people with other insights and ideas, is extremely valuable. If you, as an employer, send your people and yes, paying them to do so, they feel valued, they feel appreciated and taken seriously. They will chat with other experts, and believe me, most of those well known people, MVPs, they do like to chat with others. They are there for exact this reason. Because IT is really a fast pacing world, and if you intend to keep your employees for a longer term, then you need to develop their skills and fantastic conferences like SQLBits do help tremendously. That being said, I want to list some encounters I've had, tell short stories about them and prove that it does make sense, having IT people to network outside their country, and yes, even outside of their IT areas and even to listen to stimulating, non-technical talks that can expand your social skills. Following, I'll name some incredible people I've met: Valerie Junk with whom I've I've enjoyed a great fruitous and interesting discussion over lunch, together with my friend Nikola Ilic. I hope I'll have the pleasure to meet both again in near future! No alt text provided for this image André Kamman who's an incredible person and I would love to meet again! Unfortunately, we didn't have the beer together as i wished to, but lets do that another time! My friend Alpa Buddhabhatti who was truly inspiring! I hope you'll have huge success during your continuing speaking journey! I will definitively continue to watch you doing so! No alt text provided for this image From left to right: Tracy, Nikola, Adriano and I. Adriano da Silva, who I've been friends with for years, but haven't had the chance to meet in person yet, but finally, we did last Tuesday night! Same is true for Tracy A. Boggiano, an amazing and fun person to be around with! We shall meet again online soon for our Virtual DEI Group we're leading together with our friend Deepthi Goguri. We'll also meet at #MVPSummit in Redmond this April! Craig Porteous, Ben Weissman, and William Durkin, great to meet and chat with you again! I hope we'll meet again, perhaps at this years DATA :Scotland? Cathrine Wilhelmsen, we unfortunately didn't have the opportunity to really chat together, my apologies for that! I hope you've loved the swiss chocolate that I could pass to you quickly, though! But perhaps we'll have another chance at the MVP Summit? By the way, I really loved your inspiring talk about pushing yourself outside of comfort zone to learn thing - an amazing presentation! I wish I was as talented as you for giving presentations! Gavita Regunath, Ph.D.👩🏽 🔬📚 whom I also would have loved to talk more, again, perhaps at the MVP Summit in April? Traci and Rob Sewell (sorry, Traci, no Link to you. Couldn't find you on LinkedIn) so amazing people, both of them. Not only having so big hearts and to give to the community, but also so incredibly kind, passionate and knowledgeable. I admire Mathias Thierbach for his sheer determination he must have brought up for publishing his amazing Tabular Definition Language! I've been blown away of your presentation together with Gabi Münster! No alt text provided for this image Picture of the presentation by Gabi and Mathias who will speak in Switzerland on April 6 at PBI User Group Switzerland (online)! Speaking of which, Gabi, Markus Ehrenmueller-Jensen, Denis Selimovic and Nikola Ilic joined up for a picture taken by Reitse Eskens (see topmost picture) - it was the first time ever that the first organization team of DATA BASH 2022 had an assembly of 5 people in one place. I haven't met both Markus and Gabi in person yet before this conference. I could go on much longer and tell you that those encounters were worth every penny I've spent in UK. However, instead of naming more people and putting sharing my experience with them, I'll just list them here. Some of them, I've just met, others, I have known for years, but online only, and again others have been friends for years also in real life. If I forget anyone, please apologize me for that, my mind has failed you in that case. I truly had a blast with all of you and hope to see you soon again or hope to chat with you either here on LinkedIn (PMs are welcome!) or through my other channels like Mastodon. Kristyna Hughes, Andrew Pruski, Reitse Eskens, Pragati Jain, Grace O'Halloran, Mathias Halkjær Petersen, Kurt Buhler, Cláudio Silva, Matt Gordon, Shaun Atkinson (sorry, couldn't find you on LinkedIn!), Johan Ludvig Brattås, Alex Whittles, Mara Pereira, Michael Johnson, Sander Stad, Dinakar Nethi, Nicky van Vroenhoven, Buck Woody, Taiob Ali, Stephanie Locke, Rodney Kidd, Deborah Melkin, Andy Yun, Heini Ilmarinen, Steve Jones, Alexander Klein, Erin Stellato, Kevin Chant, Gethyn Ellis, Justin Bird, Anthony Nocentino, Patrick Leblanc, Adam Saxton, Olivier Van Steenlandt and like I've said, many, many more that just don't cross my mind right now. Now, I am very excited to go home and I look forward to be back with my wife \[who chose not to get tagged\] again. Commentary edit: this post was first posted on LinkedIn, and tags to many people listed here was done. This post is therefore [best read on LinkedIn here](https://www.linkedin.com/pulse/my-week-sqlbits-2023-kay-sauter/?ref=kayondata.com). ### T-SQL Tuesday #159 URL: https://www.kayondata.com/t-sql-tuesday-159/ Last updated: 2026-06-16T20:47:37.000Z My friend, Deepthi Goguri, [invited everyone to blog](https://dbanuggets.com/2023/02/05/t-sql-tuesday-159-invitation-whats-your-new-favorite-feature/?ref=kayondata.com) about two topics as a request for T-SQL Tuesday. T-SQL Tuesday is a once a month occurring event, a so-called blog party, for which someone invites others to blog about a topic. You can learn more about this on this website of [tsqltuesday](http://tsqltuesday.com/about/?ref=kayondata.com#:~:text=T-SQL%20Tuesday%20is%20the%20brainchild%20of%20Adam%20Machanic.,do%20so%20on%20that%20particular%20month%20or%20not.) itself. Deepthi, aka dbanuggets on both [Mastodon](https://tech.lgbt/@dbanuggets@techhub.social?ref=kayondata.com) and [Twitter](https://twitter.com/dbanuggets?s=20&ref=kayondata.com), asked two to write about two topics, I quote her directly here: > Blog about your new favorite feature in SQL Server 2022 or in Azure. Why is it your favorite feature and what are your experiences and learnings from exploring this feature? If you have not explored these new features yet, No worries! Blog about the features you feel interested in exploring.New year, New Resolutions. What are your new year resolutions and how do you keep the discipline doing it day after day? Here are some examples: new hobby, plan to spend more time doing physical activity, wanted to read list of books (Please mention the names so it may also inspire others to read those books), journaling or any other resolutions you plan for this year. So, without any further ado, I'm going to dive into those topics: 1. With SQL Server Version 2022 (Build 16) Microsoft extended the list of its features again, as Microsoft documented it on the learn.microsoft.com-site [here](https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16&ref=kayondata.com). Now, I've got two things that I am really excited about, one smaller but very handy feature: [STRING\_SPLIT()](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16&ref=kayondata.com). This is a very handy function that many developers have been waiting for years! With it, you finally can split strings like you can do it in PowerShell or Python, just to name two of a plethora of programming languages that have a similar function. The second thing I am stoked about is the failover an on prem SQL Server 2022 into Azure Managed Instance! And you can do this back again! That means, potentially, you would not have the need to buy a new server first, but you just could use a Managed Instance momentarily and thus potentially cut costs! If you want to know more about it, I recommend to watch a [video](https://youtu.be/ncF-zFzBDAY?t=269&ref=kayondata.com) on which [Bob Ward](https://www.linkedin.com/in/bobwardms/?ref=kayondata.com) explains this. 1. Now, I don't do new year resolutions because I don't believe in them. However, I do set goals for myself, but they do not depend on a new year, but more a time span. I've got some projects in my pipeline and one of them will mean that I will try to blog more again this year and I hope to be ablet to attend some conferences in person. Last but not least, I am currently planning the next edition of [DATA BASH](https://databash.live/?ref=kayondata.com) and that is as much as I can say currently. So what's your answer to this? If you've just realized that it is T-SQL Tuesday today and you haven't written anything yet, I'll tell you a small secret: you still can publish it until the end of month, nobody is gonna bite you. Of course, the sooner, the better, still 😉. So long, until the next time! ### Sql Tuesday: Azure Data Groups URL: https://www.kayondata.com/sql-tuesday-azure-data-groups/ Last updated: 2026-06-16T21:19:59.000Z Every second Tuesday of a month, there's a T-SQL-Tuesday. This is a blogging event that everyone can participate. [The idea was created by Adam Mechanic, but nowadays, it's hosted by Steve Jones](http://tsqltuesday.com/about/?ref=kayondata.com). For this month, [Rie Merritt called for advices for running a user group](https://techcommunity.microsoft.com/t5/azure-sql-blog/t-sql-tuesday-advice-on-running-a-user-group/ba-p/3213699?ref=kayondata.com). I know, I'm a quite late to join this game. But since I was busy for my university just until this week, I was not able to write for the call more timely - apologies. I hope nobody minds me joining this late. So I've founded an online-only Azure Data Community user group, [Data TGIF](https://www.meetup.com/data-tgif/?ref=kayondata.com), in June last year and have led it ever since. It is still fairly small and only has around 170 members so far, so it is up to you decide whether or not this group is successful. In my opinion, an important factor for a user group is to set yourself apart from others. There are currently [140 Azure Data Tech Groups in 40 countries](https://www.meetup.com/pro/azuredatatechgroups?ref=kayondata.com) worldwide, so especially if your user group are online events, you've got some competition. Get a catchy name and a logo that is unique. My group is called [Data TGIF](https://www.meetup.com/data-tgif/?ref=kayondata.com) and unlike most other groups, it does not just offer a platform for speakers to speak, but it is also a place to meet others. So it is also an networking event. I'm very happy to see that that way, that this group is able to spark talks amongst the attendees and speakers. Oftentimes, this breaks the ice for everyone. And during those talks, oftentimes new questions pop up and get asked to the speakers. Another thing this user group is set apart from others with is the format: it is 30 mins for talks and 30 mins for the networking part. That way, so I like to believe, there is a good balance in between just listening and interaction of every attendee. I am vehemently believing that user groups are a great way to start speaking. So if anyone wants to try to speak, I am more than happy to help and if the speaker wishes, I will try to find a mentor for them. User Groups should be a speaking platform for everyone, from the freshest speakers to the most experienced speakers. Furthermore, try to find channels you're communicating and stick to them. For Data TGIF, I am using 3 channels: [MeetUp.com](https://www.meetup.com/data-tgif/?ref=kayondata.com), [Twitter](https://twitter.com/DataTgif?ref=kayondata.com) and [LinkedIn](https://www.linkedin.com/company/75026820/?ref=kayondata.com). Be consistent in using them and try to get as many connections as you can. Get in touch with the speakers and provide them marketing materials so that they can do marketing themselves, too. Last not least, try to get in touch with the community. So far, I haven't seen anyone of the other community founders or leaders biting me. In fact, I can say that I've grown to be friends with some of them and with some of them, I even have plans for future events. Some other user group organizers will even help you out with growing your user group - a shoutout to [Cloud Data Driven](https://www.meetup.com/cloud-data-driven/?ref=kayondata.com) for this! Good luck in growing your user group and of course, don't forget the most important part in this: have fun! ### Bye-bye 2021 URL: https://www.kayondata.com/bye-bye-2021/ Last updated: 2026-06-16T21:28:46.000Z # Looking back So the year 2021 is history. For me, it was a quite eventful year although exactly one year ago, I hoped that the pandemic situation would improve. It kind of did, true. However I hoped it'd be better than it turned out to be. Nevertheless, I hope on January 1st 2023, I'll have a better lookback to the year 2022 regarding this. In March 2021, I've submitted my thesis for [CAS Machine Learning (German)](https://fh-hwz.ch/produkt/cas-machine-learning/?ref=kayondata.com) at my [University of Applied Sciences in Business Administration Zurich, HWZ](https://fh-hwz.ch/english/?ref=kayondata.com). It got accepted, so I received that first CAS diploma of three. After I've decided to volunteer to moderate some sessions for the both DataWeekenders in 2020, I wanted more. Originally, I planned to start to speak in 2022 after I've finished my studies at University for Applied Sciences HWZ in Zurich where I am pursuing a MAS in Business Intelligence (planning to finish in December 2022). But I got hooked, badly. So I was looking around what it takes to be speaking and also looked for a mentor who I found in [Sander Stad](https://www.dataweekender.com/?ref=kayondata.com). Thank you a lot again, [Sander](https://www.sqlstad.nl/author/sander/?ref=kayondata.com), for your tips and encouragements you gave to me! One of my highlights was truly my speaking debut on [DataWeekender](https://www.dataweekender.com/?ref=kayondata.com) where I also volunteered to be a moderator again. I really am looking forward to be moderating and hopefully speaking on this event again! Since, I got the opportunity to speak on some other conferences and user groups so that actually became a new hobby of mine. Instead of listing all opportunities to speak here, you'll find [on this site](https://www.kayondata.com/speaking/). In June, I've created the [Azure Data Community User Group, Data TGIF](https://www.meetup.com/data-tgif/?ref=kayondata.com). This User Group has an event every month where someone is presenting a session for 30 mins. After the session, the attendees and the speakers have the opportunity to talk to each other and just to do some networking. I'm always excited when we have attendees to meet and talk each others - from around the world. This small event really makes the "SQL Family" closer to a "world village community" and closes a gap many of us feel due to the pandemic. In November, I've submitted my thesis for [CAS Customer Intelligence (German)](https://fh-hwz.ch/produkt/cas-customer-intelligence/?ref=kayondata.com) at my university, received my second CAS diploma in December and shortly after the submission in November, I was given another big opportunity: actually co-organize a whole 24-hours long conference, the [Power BI Fest 2021](https://powerbifest.com/?ref=kayondata.com). It was quite a lot of work and I had fun while working on it. # Outlook If you know me, then you probably know that I'm always having some new projects up my sleeve. I'll tell about one or another soon! I am also working on some new sessions I hope to be select to talk about in this year. For March 2022, I am looking forward to submit my last CAS thesis in [CAS Applied Data Analytics (German)](https://fh-hwz.ch/produkt/cas-applied-data-analytics/?ref=kayondata.com) on which I'm currently working on. In December 2022, I'm hoping to finish my studies at my University. # Thanks I just want thank all people from the SQL Community here who I've met, befriended, had nice conversations or just gave encouragements to me in the last year. Thank you all for your support and I really hope to see you all not just online, but also in person someday! I am optimistic that I will meet some other, new awesome people of the SQL Family and am looking forward to do so! ### Speaking Engagements September 2021 URL: https://www.kayondata.com/speaking-engagements-september-2021/ Last updated: 2026-06-16T21:31:26.000Z This post was originally published on [my employers blog first](https://www.onedigit.ch/details/end-of-summer-break-and-upcoming-events?ref=kayondata.com). September marks the end of this summer but we've got some exciting news about new events that we'll be joining this month. This post is giving you an overview over these! We hope you've enjoyed your summer break and are ready for the new season for many new events! This September '21, I'll have two very interesting events that I will attend as a speaker, panel member or a moderator apart from organizing Data TGIF this Friday: [Data TGIF](https://www.meetup.com/data-tgif/events/278829747/?ref=kayondata.com) This month, we'll have a full hour talk given by the outstanding speaker Tracy Boggiano and I'm very much looking forward to listen to her about Mental Health and Wellness in IT: Safeguarding our most precious resource at the Data TGIF user group I'm organizing! The event is this Friday at 15:30 CEST. [Data Platform Summit 2021](https://dataplatformgeeks.com/dps2021/?ref=kayondata.com) This is one of the major SQL Server / Azure SQL / Power BI events worldwide that will be held from September 13-16 around the clock to serve all timezones with having more than 100 sessions. It also features a pre- (8+9 Sept) and post-conference (20+21 Sept). I am going to speak about Automated provisioning of SQL Server VMs on Azure on Sept 13 at 18:15-19:30 CEST where you will be able to chat with me live. You can sign up for this event here. I'm very honored to be a member of the highlighted panel discussion about Diversity, Equity Inclusion on Sept 15 17:30-18:45 CEST. Finally, I'll also be available for a chat-session on Sept 16 at 14:30 CEST. [Future Data Driven Summit](https://datadrivencommunity.com/?ref=kayondata.com) is a new event that will be held for the first time. It will be a full day event with different types of topics related to Data & AI, DevOps, PowerBI and Data Visualization, Integration & Automation and cloud infrastructure. I am honored to be holding a keynote and moderating for the Future Data Driven Summit which will be on September 29 at 16:00 CEST and has a very intriguing speaker lineup! You can join this event for free here. At the moment, we have more than 1200 people that have signed up! I hope you'll be joining in one or even all of my sessions I'll be organizing, giving or moderating! Like always, if you have any questions, feel free to contact me via my [contact page](https://www.kayondata.com/contact)! ### Why We Like to Recommend Dbatools URL: https://www.kayondata.com/why-we-like-to-recommend-dbatools/ Last updated: 2026-06-16T21:35:10.000Z This post was first published for my employer, OneDigit AG, [here](https://www.onedigit.ch/details/why-we-recommend-dbatools?ref=kayondata.com). [dbatools](https://dbatools.io/?ref=kayondata.com) really has the ability to change a DBAs work life. It is a free, very powerful and feature rich open source PowerShell module that offers you 550+ commands that allows you to take backups, restore them or just do a lot in SQL Server administration that you would need SSMS and some work for. With dbatools, you can do many tasks more and that with just a few lines of code. This post gives you an introduction to dbatools. dbatools is often called the SQL Server Management Studio (SSMS) for PowerShell. So in short, you can manage a SQL Server instance and its databases with PowerShell which also means that you can use scripts to manage them. Just to give some examples on how dbatools can be used so you’ll get a picture how helpful it can be. ```powershell # Simple query with a parameter which could be passed from a variable in the PowerShell code. It also prevents SQL injection. Invoke-DbaQuery -SqlInstance "serverName" -Database "dbName" -Query 'SELECT * FROM Employees WHERE Name = @name' -SqlParameters @{ Name = "Marc Linder" } # Another example with multiple parameters, which could be assigned from variables in PowerShell code before, too. $queryBetween = @{ "StartDate" = $startdate; "EndDate" = $enddate; }; Invoke-DbaQuery -SqlInstance "serverName" -Database "dbName" -Query "StoredProcedure_with_in_between_search" -SqlParameters $QueryParameters -CommandType StoredProcedure # Creates full backups of the two databases dbName1 and dbName2. If you don't specify any database names, it backups all databases on the instance! # Backups will stored in the default backup folder. You can specify the target folder with -Path though. With -Type Diff you also could choose a differential backup Backup-DbaDatabase -SqlInstance "ServerName" -Database dbName1, dbName2 -Path C:\backups\ ``` Very simple, right? And there are 550+ commands like this in total that dbatools provides. Dbatools was invented by Chrissy LeMaire who is a MVP for Cloud and Datacenter Management & also MVP for Data Platform. She develops and maintains it now with Jess Pomfret (MVP Cloud and Datacenter Management) and Shawn Melton (MVP Cloud and Datacenter Management) and with many other contributors. In my experience, dbatools does not only an awesome job for a lot of tasks around SQL Server but is also relatively easy and fast to develop with. It is ideal for almost any problem you may encounter as a DBA. dbatools will truly change your DBA life! With dbatools you don’t develop your hardest parts of the scripts for a DBA problem alone anymore as dbatools is open source (see [code on github here](https://github.com/sqlcollaborative/dbatools?ref=kayondata.com)) and thus tested within the community. A few lines of codes will boost your scripts that you would not be able to write alone. If you’re worried about security, Chrissy also wrote [a very insightful post on dbatools.io](https://dbatools.io/secure/?ref=kayondata.com) on how secure dbatools is – spoiler: its very secure. So if you want to use dbatools, you can for example, just use SQL Server Job Agent which allows you to start your script according your needs, e.g. after a load or taking backups at a past midnight time very easily. Of course, you also can commit the code into a git repo and use it with SQL Server Job. If you like to know more about dbatools, I recommend to [buy and read the book](https://www.manning.com/books/learn-dbatools-in-a-month-of-lunches?query=dbatools&ref=kayondata.com) “Learn dbatools in a Month of Lunches” by [Chrissy LeMaire](https://blog.netnerds.net/resume/?ref=kayondata.com), [Rob Sewell](https://blog.robsewell.com/about/?ref=kayondata.com), [Jess Pomfret](https://jesspomfret.com/?ref=kayondata.com) and [Cláudio Silva](https://claudioessilva.eu/about/?ref=kayondata.com). ```powershell # How to install dbatools: # Via PowerShell Gallery (Win 10+ / Win Server 2016+) Install-Module dbatools # Via Choclatey: choco install dbatools -y ``` More install options are described here and there is also an option with downloading it if your server does not have any internet connection. If you want to update dbatools, it of course also has a very neat tool to update itself: ```powershell Update-dbatools ``` If you need to choose the download option, it may happen that after you’ve installed a newer version of dbatools, you’ll end up having more than just one version of dbatools. In that case, you may want to remove older versions. To do that, you can use the following code as documented here: ```powershell & "$((Get-InstalledModule dbatools).InstalledLocation)\cleanup.ps1" ``` If you have any questions about dbatools, please feel free contact me. I’m happy to answer you! ### Starting DataTGIF URL: https://www.kayondata.com/starting-datatgif/ Last updated: 2026-06-16T21:37:35.000Z This post was first published on my employers website/blog [here](https://www.onedigit.ch/details/first-data-tgif-event-up-on-june-25-2021?ref=kayondata.com). I just started to organize a free to join [MeetUp called Data TGIF](https://www.meetup.com/de-DE/data-tgif/?ref=kayondata.com) that will be scheduled once a month on a Friday afternoon. This MeetUp organizes free data events for Friday afternoons to celebrate the start of the weekend by meeting people to a Microsoft Data Platform talk. If you don’t know the term TGIF: According to Wikipedia, [TGIF stands for Thank God It's Friday](https://en.wikipedia.org/wiki/Thank%5FGod%5FIt%27s%5FFriday?ref=kayondata.com) and is a common expression in English speaking Western countries. It expresses gratification that the working week is nearly over, and a weekend of leisure will soon be here. The goal of this group is to spread knowledge in Microsoft Data Platform and to give opportunities for national and international networking to the Swiss community. Primarily, this MeetUp will be held online so that everyone can participate from their own place. As soon as the pandemic situation will improve, we will probably start to organize in-person meetings. Meetings are held in English so that we may cover all Swiss language regions. The speaker usually will have 30 mins for their presentation and there will be 30-45 mins for networking afterwards although there may be exceptions to this rule. If we host an in-person event, it may have a different format than this. The first speaker will be [Andy Cutler](https://www.datahai.co.uk/about/?ref=kayondata.com) who is an Azure Data Platform professional working predominantly with Azure Synapse Analytics (SQL Pools), Data Factory, SQL Server and Power BI. He holds an MSc in Business Intelligence and Data Mining. Andy will be speaking about **Creating a Logical Data Warehouse using Azure Synapse Analytics Serverless SQL Pools**. We are looking forward to this event and would be happy to see you there! Please come and [sign up for Andys session for free](https://www.meetup.com/de-DE/data-tgif/events/278656966/?ref=kayondata.com)! ### Moving to Hugo URL: https://www.kayondata.com/moving-to-hugo/ Last updated: 2026-06-16T21:39:59.000Z I've moved from WordPress to Hugo. Just because I've never really made very good experience with WordPress. Yes, it is easy, but one has just to hope that all plugins will work together and keeps working together in the future. My move to Hugo was inspired by [Kendra](https://www.littlekendra.com/2021/05/03/moving-from-wordpress-to-an-azure-static-site-with-hugo/?ref=kayondata.com) and [Justin](https://www.justinjbird.me/2021/learning-to-hugo-hello-world/?ref=kayondata.com). Furthermore, the integral usage of Markdown makes it super easy to use code within a blog post. At the moment, it is not possible to comment any posts yet. However, I am going to set it up in the next few weeks. I am planning to set it up with Gitalk, a commenting system that uses Git so that anyone who wants to comment a post needs to have an account. Since my blog targets a quite technical audience, this should not be an obstacle. If you're keen to contact me, you may do so by sending me a message on LinkedIn, my page there is [https://www.linkedin.com/in/kaysauter/](https://www.linkedin.com/in/kaysauter/?ref=kayondata.com). ### SQL Server Management Studio and Azure Data Studio URL: https://www.kayondata.com/sql-server-management-studio-and-azure-data-studio/ Last updated: 2026-06-16T21:43:57.000Z If you’ve installed SQL Server Management Studio (SSMS) recently, chances are that you’ve noticed that Azure Data Studio (ADS) was installed too. Since SSMS Version 18.7, ADS gets installed alongside with SSMS by default. But you might not be familiar with ADS. What is it and how does it set apart from SSMS? And how to install SSMS alone? ## Introduction ADS is a free, [open source](https://github.com/microsoft/azuredatastudio?ref=kayondata.com) and cross-platform SQL code editor with built-in IntelliSense that is also able to manage SQL Server in Azure, so just this description alone sets it quite apart from SSMS. And yes indeed, it is cross platform which means you can use it on Mac OS and on Linux. ## Features that SSMS does not have ADS boasts some features that SSMS doesn’t have. Most important examples are: Extensions ecosystem similar to that one like Visual Studio Code Source control for git out of the box Jupyter Notebooks for SQL, PowerShell, Python and more Dashboard for an excellent overview over your Instance Integrated terminal Excellent in connecting to Azure Dark mode ADS is builds upon the same system as Visual Studio Code (VSCode). Don’t mix it up with Visual Studio – VSCode is an very extensible and powerful editor whereas Visual Studio is a fully fledged IDE. However, if you want, you can use extensions to make VSCode to a very powerful system that is close to an IDE or even meets it. If you’re familiar with VSCode, you’ll be able to adapt to ADS very quickly. ## So why still use SSMS? Well in short, SSMS still has some capabilities that ADS does not have, especially for administering SQL Server Instance. However, for most if not all SQL developments, ADS can do the same as SSMS or even better. When it comes to administering SQL Server though, SSMS is still more powerful. For example, if you want to configure Always On Groups, using Maintenance Plans, PolyBase, or Replication, SSMS is still the winner. Microsoft says that ADS does not replace SSMS. In my opinion, perhaps it will one day, but for the foreseeable future, it certainly does not yet. ## But can I avoid installing ADS when installing SSMS? Firstly, I personally advise to install ADS as well in most DEV environments because it does have some very interesting and useful features. But to answer this question, if you’re installing it via graphical user interface, then you are unfortunately forced to install ADS, too. If you must avoid the installation of ADS, for example because you want to have a hardened PRD, you can avoid it if you install it via command line this way: ```powershell SSMS-Setup-ENU.exe /Passive DoNotInstallAzureDataStudio=1 ``` If I uninstall SSMS, will ADS be uninstalled too? No. If you want to uninstall both, you’ll have to uninstall them separately. This was a short introduction in this topic. If you have more questions, we will be happy to answer hem! ### My journey to my first speaking URL: https://www.kayondata.com/my-journey-to-my-first-speaking/ Last updated: 2026-06-16T21:48:32.000Z To give a speaking in public for the first time is always scary. At the DataWeekender in October last year, Angela Henry who was moderating some sessions together with me, mentioned that there is a website where you can look for mentors and recommended it to me if I would want to pick up speaking. The website is [speakingmentors.com](http://www.speakingmentors.com/?ref=kayondata.com) and was created by [Alex Yates](http://workingwithdevs.com/?ref=kayondata.com), so I’ve later learned. At the end of March this year, I’ve decided that I want to start to speak this year. Originally, I’ve planned to start to do so after my studies for Master of Advanced Studies in Business Intelligence at the [University of Applied Sciences in Business Administration Zurich](https://fh-hwz.ch/english/?ref=kayondata.com). However, I’ve been wanting to start speaking for a longer time, so I’ve just gave it a go after I would finish my first part of this studies for which I received the Certificate of Advanced Studies in Machine Learning just recently. If everything goes as planned, I’ll be finishing my studies in spring 2022. Originally, I’ve planned to start at a [New Stars Of Data](https://www.newstarsofdata.com/?ref=kayondata.com) event in 2022 and this spring, it actually fell in the time when I had to write the thesis for the CAS Machine Learning so that was not an option. But then there was [DataWeekender](https://www.dataweekender.com/?ref=kayondata.com) for which I decided to apply to speak for. So I went to the website for looking a mentor to get me some insights on how one should prepare and deliver a speaking. Through [speakingmentors.com](http://www.speakingmentors.com/?ref=kayondata.com), I quickly found out that [Sander Stad](https://www.sqlstad.nl/?ref=kayondata.com) specializes more or less the same topic I’ve decided to talk about and contacted him. Sander is a very experienced speaker and did give me more than a few tips and tricks on how to improve a talk. Angela was right: [speakingmentors.com](http://www.speakingmentors.com/?ref=kayondata.com) is a very helpful resource you should turn to if you’re deciding to speak for the first time. I would like to thank some people who gave me their support in this little journey: - My employer [OneDigit AG](https://www.onedigit.ch/?ref=kayondata.com) who supported me to start speaking on events - My wife Vivien who supported me by supporting me emotionally and giving me the time to prepare my speaking. - [Sander Stad](https://www.sqlstad.nl/?ref=kayondata.com) for his mentorship, advices and encouragements from his enormous experience he gave me. It was my honor to be your mentee, Sander! And I might add, we actually had some very nice conversations together! I'll owe you more than just a beer! - [Angela Henry](https://sqlswimmer.com/?ref=kayondata.com) for casually pointing me to speakingmentors.com and sparked the idea in me in the first place. Thank you a lot for that! - [Alex Yates](http://workingwithdevs.com/?ref=kayondata.com) for having the idea and actually creating this website with which you may find your future mentor. A nice and funny guy whom I've had the chance to meet over MS Teams once. Thank you! - The guys behind [DataWeekender](https://www.dataweekender.com/?ref=kayondata.com): [Kevin Chant](https://twitter.com/kevchant?ref=kayondata.com), [Gethyn Ellis](https://twitter.com/gethyn%5Fellis?ref=kayondata.com), [Mark Hayes](https://twitter.com/markdataguy?ref=kayondata.com) and [Damir Matešić](https://twitter.com/matesic%5Fdamir?ref=kayondata.com) who gave my the first opportunity to speak publicly. That was a great experience and your event will always be specially remembered! - [Nikola Illic](https://data-mozart.com/about/?ref=kayondata.com) who was encouraging me for speaking almost until I had to switch my microphone on. Thanks! - A huge thanks to the audience I’ve had - I was very nervous, but I hope that wasn't too noticeable! Thank you so much to everybody! Without you, my journey wouldn’t had been half fun! I am still learning and will be continuing my journey. And Alex, should I once feel that I’ve got enough experience, I’ll walk on Sanders path and sign up to your site to be a mentor for a next new speaker. PS: If you’re looking for my DataWeekender session files, [they’re over here](https://github.com/kaysauter/Sessions/tree/main/DataWeekender-15-May-2021?ref=kayondata.com). ### Deploy Azure Sql Server Vm With PoSh URL: https://www.kayondata.com/deploy-azure-sql-server-vm-with-posh/ Last updated: 2026-06-16T22:00:04.000Z This post was first published on my past employers blog [here](https://azure.microsoft.com/en-us/features/storage-explorer/?ref=kayondata.com). I thank OneDigit AG for the permission to publish my article also here. Especially in this time of COVID-19 caused lockdowns, working from home is widespread. Therefore, working together on a developing VM in the cloud may be an attractive solution. In the past few months, I developed a solution to use VMs for such business cases. We also had in our minds that this VM could be a showcase for customers that prefer having their solution on their premises without Azure. Therefore, a VM in Azure in a Infrastructure-as-a-Service (IaaS) solution can make a lot of sense. This can also be a cost-efficient solution for those who want to try out things for a couple of hours. Even if you need this VM for a longer time, the costs are fairly limited, depending on the hardware specifications you are choosing. The following article is split in three parts. In the first part, a script sets up a VM from scratch. The second walks you through the code and will setup Azure Bastion. And the last part will walk you through the installation of some software including Azure Data Studio via Chocolatey and the restore of a sample database with dbatools. The full setup takes a bit more than 1 hour and is set up almost completely automatically. # Pre-requisites Editor and connection to Azure I am using Visual Studio Code with some extensions for Azure. In order to connect to your Azure Account, you need to connect your editor of your choice with [Connect-AzAccount](https://docs.microsoft.com/en-us/powershell/module/az.accounts/connect-azaccount?ref=kayondata.com). ## Files in Azure File Store You will need to upload a backup file to your file [Azure File Store](https://azure.microsoft.com/en-us/features/storage-explorer/?ref=kayondata.com). Azure File Store is a quite cheap storage for storing files like database backups. To upload a backup file, I recommend using [Azure Storage Explorer](https://azure.microsoft.com/en-us/features/storage-explorer/?ref=kayondata.com) with which you can upload your files in a very convenient and secure way. ## Azure Bastion Azure Bastion will be set up this script automatically. However, I at this point, want to give you some resources first why and when you should use Azure Bastion. If you’re using a VM via Internet, it is always a bad idea to use a RDP connection without an encrypted tunnel. https://docs.microsoft.com/en-us/azure/bastion/bastion-overview https://jussiroine.com/2019/09/building-a-secure-remote-access-solution-using-azure-bastion-host/ ## dbatools We are going to install dbatools on the VM after its deployment. At this point, you don’t need to know much about this awesome collection of PowerShell commandlets. It contains 500+ easy and efficient to use scripts that are free and open source. ## Chocolatey We will also install Chocolatey on the VM. Again, at this point, you don’t really need to know much about it as we will just install some software with Chocolatey. It enables you to automatize, download and install many software with few lines of code, making it very quickly for anybody who has to set up a VM (or computers) from scratch. There is also a paid service by Chocolatey, but we will be using the free service offer. # Lets start with the script As an overview the following script does the following tasks: - Creates the VM with SQL Server - Creates Azure Bastion Following you’ll see the entire script and after this script in the second part of this article, I’ll walk you through it. ```powershell # Variables ## Global Settings $Location = "switzerlandnorth" $ResourceGroupName = "bloglab" ## Settings for Storage $StorageName = $ResourceGroupName + "storage" $StorageSku = "Standard_LRS" ## Settings for Network $InterfaceName = $ResourceGroupName + "ServerInterface" $NsgName = $ResourceGroupName + "nsg" $VNetName = $ResourceGroupName + "VNet" $SubnetName = "Default" $VNetAddressPrefix = "10.0.0.0/16" $VNetSubnetAddressPrefix = "10.0.0.0/24" $TCPIPAllocationMethod = "Dynamic" $DomainName = $ResourceGroupName ## Settings for Bastion $publicIpName = "pip" + $ResourceGroupName $BastionName = "Bastion" + $ResourceGroupName $BastionSubnetName = "AzureBastionSubnet" ## Settings for hardware $VMName = $ResourceGroupName + "VM" $ComputerName = $ResourceGroupName + "Server" $VMSize = "Standard_D4s_v3" $OSDiskName = $VMName + "OSDisk" ## Settings for SQL Server licence $PublisherName = "MicrosoftSQLServer" $OfferName = "sql2019-ws2019" $Sku = "SQLDEV" $Version = "latest" # Set credentials for logging into VM $Credential = Get-Credential -Message "Type the name and password of the local administrator account." # Creation of Objects ## Resource Group New-AzResourceGroup -Name $ResourceGroupName -Location $Location ## Storage $StorageAccount = New-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $StorageName -SkuName $StorageSku -Kind "Storage" -Location $Location ## Network $SubnetConfig = New-AzVirtualNetworkSubnetConfig -Name $SubnetName -AddressPrefix $VNetSubnetAddressPrefix $VNet = New-AzVirtualNetwork -Name $VNetName -ResourceGroupName $ResourceGroupName -Location $Location -AddressPrefix $VNetAddressPrefix -Subnet $SubnetConfig $PublicIp = New-AzPublicIpAddress -Name $InterfaceName -ResourceGroupName $ResourceGroupName -Location $Location -AllocationMethod $TCPIPAllocationMethod -DomainNameLabel $DomainName $NsgRuleRDP = New-AzNetworkSecurityRuleConfig -Name "RDPRule" -Protocol Tcp -Direction Inbound -Priority 1000 -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange 3389 -Access Allow $NsgRuleSQL = New-AzNetworkSecurityRuleConfig -Name "MSSQLRule" -Protocol Tcp -Direction Inbound -Priority 1001 -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange 1433 -Access Allow $Nsg = New-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroupName -Location $Location -Name $NsgName -SecurityRules $NsgRuleRDP, $NsgRuleSQL $Interface = New-AzNetworkInterface -Name $InterfaceName -ResourceGroupName $ResourceGroupName -Location $Location -SubnetId $VNet.Subnets[0].Id -PublicIpAddressId $PublicIp.Id -NetworkSecurityGroupId $Nsg.Id ## Compute $VirtualMachine = New-AzVMConfig -VMName $VMName -VMSize $VMSize $VirtualMachine = Set-AzVMOperatingSystem -VM $VirtualMachine -Windows -ComputerName $ComputerName -Credential $Credential -ProvisionVMAgent -EnableAutoUpdate #-TimeZone = $TimeZone $VirtualMachine = Add-AzVMNetworkInterface -VM $VirtualMachine -Id $Interface.Id $OSDiskUri = $StorageAccount.PrimaryEndpoints.Blob.ToString() + "vhds/" + $OSDiskName + ".vhd" $VirtualMachine = Set-AzVMOSDisk -VM $VirtualMachine -Name $OSDiskName -VhdUri $OSDiskUri -Caching ReadOnly -CreateOption FromImage ## Image $VirtualMachine = Set-AzVMSourceImage -VM $VirtualMachine -PublisherName $PublisherName -Offer $OfferName -Skus $Sku -Version $Version ## Create the VM in Azure New-AzVM -ResourceGroupName $ResourceGroupName -Location $Location -VM $VirtualMachine ## Add the SQL IaaS Extension with chosen license type New-AzSqlVM -ResourceGroupName $ResourceGroupName -Name $VMName -Location $Location -LicenseType PAYG ## Create Azure Bastion Host Add-AzVirtualNetworkSubnetConfig -VirtualNetwork $VNet -Name $BastionSubnetName -AddressPrefix 10.0.1.0/27 $VNet | Set-AzVirtualNetwork $publicip = New-AzPublicIpAddress -ResourceGroupName $ResourceGroupName -name $publicIpName -location $Location -AllocationMethod Static -Sku Standard New-AzBastion -ResourceGroupName $ResourceGroupName -Name $BastionName -PublicIpAddress $publicip -VirtualNetworkId $VNet.id ``` I will walk you through some major settings that the code above has so you may alter them accordingly to your needs. ```powershell $Location = "switzerlandnorth" $ResourceGroupName = "bloglab" ``` Since I live and work in Greater Zurich Area, I’ve picked switzerlandnorth as my hosted location for Azure. If you want to pick a different one but don’t know your choices, you can get a list with Get-AzLocation. ```powershell $ResourceGroupName ``` Defines the name of your Resource Group. You can think of it as a container that holds all objects you've got within it. This makes configurations for your objects easier. For example, in order to copy or move your objects from "switzerlandnorth" (Zurich) to "switzerlandwest" (Geneva) would mean that you only need to address the container and every single object within this group. ```powershell $StorageSku = "Standard_LRS" ``` This defines what kind of storage you will use. For a better decision what choices you have, check its documentation. In our example, I’ve picked the standard type. Please note that there may be pricing variations amongst the different types which you may want to find out about here. ```powershell # Set credentials for logging into VM $Credential = Get-Credential -Message "Type the name and password of the local administrator account." ``` Shortly after you start the script, you’ll notice that you need to enter some credentials. This is actually this line that cast this question. In order to access the VM, you need to pass exactly this credentials. In other words, during the creation of the VM, your initial credentials are set so you may access it. ```powershell ## Settings for Network ``` Next, you’ll see some networking configurations. These configurations have to be made because you’ll have a virtual net around your virtual machine. You need it also for Azure Bastion which basically lets you access your VM via a secure tunnel via SSL. Your browser will provide the actual access. I’ll show you more about this later, but for now, it is sufficient to say that Azure Bastion makes it easy for you to tunnel your RDP connection. It is not exactly free but it allows you to create a secure RDP connection if you don’t have the means to create an own VPN tunnel. You can find out more about the pricing for Azure Bastion here. ```powershell ## Settings for Bastion $BastionAddressPrefix = "10.0.1.0/27" ``` In the settings for Azure Bastion, we define some names. I’ve used the pretty standard naming conventions that Microsoft suggests here. The last line in these settings is what I want to emphasize here. It is important to note that $BastionAddressPrefix needs to be at least /27 as Microsoft states in its documentation in the bullet point “Subnet” here. This can be a source of confusion if you don’t pay attention to this. ```powershell ## Settings for hardware With $VMSize = "Standard_D4s_v3" $OSDiskName = $VMName + "OSDisk" ``` With this we define some VM specific “hardware”. Again, with what you decide to go for, the pricing may change. To find out more about this, Microsoft provides a calculator for pricing where you may get your estimates. Some more information about the right sizing for your VM provides Thomas Maurer, a Senior Cloud Advocate at Microsoft. If you want to list which options you have for your preferred location, you can use ```powershell Get-AzVMSize -Location "switzerlandnorth" ``` SQL Server License Since we actually want to have a SQL Server on the VM, we also need to decide what kind of licensing we want. In this installation, we are going to use. If you want to find out what options you have for your preferred location you can type: ```powershell ## Settings for SQL Server licence Get-AzVMImageOffer -Location switzerlandnorth -Publisher 'MicrosoftSQLServer' ``` For more information about the costs and licensing can be found on Microsofts documentation about [Pricing guidance for SQL Server on Azure VMs](https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/pricing-guidance?view=azuresql&ref=kayondata.com). So with the code we have so far, we now configured almost all variables. The rest of the code actually creates the needed objects. If you run the code, you’ll notice that it’ll take about 5 minutes until it continues at some point. The creation of Azure Bastion host takes some while. After the code has completed, after about 20 minutes, you’ll have a fair amount of objects in your resource group, being the VM and the Bastion host amongst them. I will show you how to set up the generated VM with a SQL Database and various software in an automated way. # Starting and setting up the VM Now the VM is deployed and already started. Now is also the moment you’ve been waiting for – you want to access to your VM. In order to do so, go to your Azure portal and find your resource and your VM in there. Once you’re clicked on your VM, you’ll see a screen like below. Here, click on “Connect” as below and choose the option Bastion. ![](https://www.kayondata.com/ghost/Bild1.png) After this, you’ll see a site with a button “Use Bastion” on which you click. In the next window, you need to enter the credentials you’ve set for this VM during the running of the script. Your browser starts to connect to your VM. During that time, you will see a small window asking if you want to allow copy & paste. If you allow it, you can copy & paste text onto the VM. It is not possible to copy files into the VM on this way. However, like I’ve already mentioned in the prerequisites, you can use the Azure File Storage Explorer with which you can use drag and drop to upload files to this File Storage. You can map this storage as a network drive so you can access even larger files very conveniently that way. The next few steps will allow you do so. After your VM started and you see Windows, you might want to have a full screen view on your VM. To do so, click on this >> on the left side ![](https://www.kayondata.com/ghost/Bild2.png) This will slide open a small window like this. In here, you can choose full screen: ![](https://www.kayondata.com/ghost/Bild3.png) And add a File Share: ```powershell # Enabling execution of PowerShell scripts: set-executionpolicy remotesigned -Force # Mounting Azure File Store as cheap data storage $connectTestResult = Test-NetConnection -ComputerName $YourDataStorageName.file.core.windows.net -Port 445 if ($connectTestResult.TcpTestSucceeded) { # Save the password so the drive will persist on reboot cmd.exe /C "cmdkey /add:`"$YourDataStorageName.file.core.windows.net`" /user:`"Azure\sqlsrvbaks`" /pass:`"$YourPassword`"" # Mount the drive New-PSDrive -Name Z -PSProvider FileSystem -Root "\\YourDataStorageName.file.core.windows.net\wideworldimporters" -Persist } else { Write-Error -Message "Unable to reach the Azure storage account via port 445. Check to make sure your organization or ISP is not blocking port 445, or use Azure P2S VPN, Azure S2S VPN, or Express Route to tunnel SMB traffic over a different port." } ``` With this, we are now able to install chocolatey: ```powershell # Install Chocolatey: Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1')) Chocolatey makes it extremely easy to install a lot of software with just one PowerShell line as following: # Install Chocolatey GUI and other software: choco install chocolateygui microsoft-edge tabular-editor daxstudio azure-data-studio git dbatools vscode -y ``` This installs: - [Chocolatey GUI](https://community.chocolatey.org/packages/ChocolateyGUI?ref=kayondata.com) - [Microsoft Edge](https://community.chocolatey.org/packages/microsoft-edge?ref=kayondata.com) - [Tabular Editor](https://community.chocolatey.org/packages/tabular-editor?ref=kayondata.com) - [Dax Studio](https://community.chocolatey.org/packages/daxstudio?ref=kayondata.com) - [Git](https://community.chocolatey.org/packages/git.install?ref=kayondata.com) - [Dbatools](https://community.chocolatey.org/packages/dbatools?ref=kayondata.com) - [Visual Studio Code](https://community.chocolatey.org/packages/vscode?ref=kayondata.com) Of course, you could add / remove the software as you like in that line. The last -y means that you agree to the installs of all listed software. dbatools takes quite some time to be installed (ca. 10 minutes) so don’t be surprised if it seems to be stuck – it is not. With this Tabular Editor, .Net Framework 4.8 gets installed. Therefore we need to reboot the VM: ```powershell # Rebooting Computer to complete installs Restart-Computer ``` You just can keep the browser tab for the VM open until the reboot is done and you can continue. With dbatools being installed, we would like to restore our databases. But, remember – we need to have the backup files first which we need to bring to our VM first? Now it is the time to map Azure File Store to our VM. If you go to Azure Portal, go to ![](https://www.kayondata.com/ghost/Bild4.png) And add a File Share ![](https://www.kayondata.com/ghost/Bild5.png) And click on the created file share next. In the next window, click on Connect which will open a side window. In here, you can define a mapping letter (eg. Z) and it shows a some code with which you can map your file store. ![](https://www.kayondata.com/ghost/Bild6.png) Copy this code (attention: this has a password in it, keep this secret). Paste this into PowerShell ISE or put it into a file and run it on your VM. As a result, you should see something like this: ![](https://www.kayondata.com/ghost/Bild7.png) If you’ve uploaded a file via Microsoft Storage Explorer, you’ll see the file here. In order to restore this file or even more files, you can use the following code: ```powershell # Restore Parameters $SQLBackupSource = "Z:\*.bak" # Restore databases Copy-Item $SQLBackupSource -Destination "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup" -Force -Verbose Restore-DbaDatabase -SqlInstance localhost -Path "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\" ``` In order to shut down your VM via Code or removing it or even deleting the entire resource group with all its objects in it, those few lines may help you out: ```powershell # For stopping this VM: Stop-AzVM -Name $VMName -ResourceGroupName $ResourceGroupName # # For removing this VM: Remove-AzVM -ResourceGroupName $ResourceGroupName -Name $VMName # For removing resource group (and all its objects, including this VM): $ResourceGroupName = "bloglab" Remove-AzResourceGroup -Name $ResourceGroupName -Force ``` This was just a small dive into this topic. Of course it would be possible to automatize this even more in a wholesome DevOps way. It also might be interesting to know that Microsoft offers some tools that may support this idea with: - [Azure Automation](https://docs.microsoft.com/en-us/azure/automation/?ref=kayondata.com) - [Azure Lab](https://docs.microsoft.com/en-us/azure/lab-services/tutorial-setup-lab-account?ref=kayondata.com) - [Azure DevOps](https://azure.microsoft.com/de-de/services/devops/?ref=kayondata.com) If you have a question, feel free to contact me via [LinkedIn](https://www.linkedin.com/in/kaysauter/?ref=kayondata.com)! ### I'm moderating at DataWeekender conference URL: https://www.kayondata.com/im-moderating-at-dataweekender-conference/ Last updated: 2026-06-16T22:02:14.000Z The Dataweekender conference is coming up again this Saturday Oct 17! After the very successful first Dataweekender conference on May 2nd 2020 with more than 1000 sign-ups and with an excellent speaker lineup, the 6 founders of Dataweekender decided to setup another one this year. Just like the first time, I will be moderating again this October 17\. The conference will be held from 9:00 - 19:00 Central European Summer Time. Dataweekender is a free to watch and free of sponsorships virtual conference and anyone can join. If you're interested in the [schedule](https://www.dataweekender.com/schedule?ref=kayondata.com) and if you're hooked up for watching one or more sessions, [sign up here](https://www.meetup.com/de-DE/DataWeekender/?ref=kayondata.com). I'll be on track number 4. The [core organizers](https://www.dataweekender.com/about-us?ref=kayondata.com) of Dataweekender, [Kevin Chant](https://twitter.com/kevchant?ref=kayondata.com), [Gethyn Ellis](https://twitter.com/gethyn%5Fellis?ref=kayondata.com), [Ásgeir Gunnarsson](https://twitter.com/bidgeir?ref=kayondata.com), [Mark Hayes](https://twitter.com/markdataguy?ref=kayondata.com), [Damir Matešić](https://twitter.com/matesic%5Fdamir?ref=kayondata.com) and [Magnus Ahlkvist](https://twitter.com/Transmokopter?ref=kayondata.com) are just like I am, strong supporters of [inclusion](https://www.meetup.com/de-DE/DataWeekender/pages/30310995/CodeOfConduct/?name=CodeOfConduct&id=30310995&ref=kayondata.com). No matter gender, race or disability, everyone should be equally able to participate this very nice conference with 6 tracks and 60 talks. Join us at the conference and have a good time! This post was first published [here](https://dev.to/kaysauter/i-m-moderating-at-dataweekender-conference-146k?ref=kayondata.com). ### Introduction Into Window Functions on SQL Server URL: https://www.kayondata.com/introduction-into-window-functions-on-sql-server/ Last updated: 2026-06-16T22:05:44.000Z ## Why window functions may come in handy Let us dive directly into a real world example. You’ve got a table with salaries of different job functions and you are asked to find out what the n-tiles (quantiles, mean etc.) are of each function within a company. So this means you’ve got many people that hold a position which you want to compare with others. You want to compare salaries of marketing professionals to their peers, but you don’t want to compare said salaries to marketing directors because they have their own salary bands. So that means you need to frame (also called partition) job titles with their own with which you calculate the n-tiles. Without window functions, this could be a quite fussy task. In order to walk through the problem, create a new database, I’m going to name mine FoehnWind \[^1\]. The following script creates a new table and inserts some sample data of 20 rows into it. ## Tutorial ```sql USE [FoehnWind]; GO CREATE TABLE dbo.Employee (ID TINYINT IDENTITY(1, 1) NOT NULL, FirstName VARCHAR(150) NOT NULL, LastName VARCHAR(150) NULL, JobTitle VARCHAR(100) NOT NULL, AnnualSalary INT NOT NULL, CONSTRAINT [PK_dbo.Employee] PRIMARY KEY CLUSTERED(ID ASC) ) ON [PRIMARY]; GO INSERT INTO [dbo].[Employee] VALUES ( 'Ken','Sanchez','Chief Executive Officer', 530000) ,( 'Jo','Berry','Marketing Assistant', 71875 ) ,( 'Lori','Penor','Marketing Assistant', 73398 ) ,( 'Pat','Coleman','Marketing Assistant', 51874 ) ,( 'Stuart','Macrae','Marketing Assistant', 59108 ) ,( 'Mary','Dempsey','Marketing Assistant', 57641 ) ,( 'Wanida','Benshoof','Marketing Assistant', 60779 ) ,( 'David','Bradley','Marketing Manager', 130000 ) ,( 'Jill','Williams','Marketing Specialist', 108117 ) ,( 'John','Wood','Marketing Specialist', 99407 ) ,( 'Sariya','Pit','Marketing Specialist', 95048 ) ,( 'Terry','Eminhizer','Marketing Specialist', 106316 ) ,( 'Michael','Sullivan','Marketing Specialist', 87047 ) ,( 'Ovidiu','Cracium','Marketing Specialist', 107883 ) ,( 'Rob','Walters','Marketing Specialist', 109369 ) ,( 'Garrett','Young','Senior Marketing Specialist', 118051 ) ,( 'George','Li','Senior Marketing Specialist', 117253 ) ,( 'Russell','King','Senior Marketing Specialist', 123362 ) ,( 'Sandra','Alayo','Senior Marketing Specialist', 118185 ) ,( 'Denise','Smith','Vice President Marketing', 250000 ); GO ``` Firstly, lets use a window function which calculates the averages and percentiles of salaries for each job title: ```sql SELECT distinct e.JobTitle ,AVG(e.AnnualSalary) OVER (PARTITION BY e.JobTitle) AS AverageSalary ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY e.AnnualSalary) OVER (PARTITION BY e.JobTitle) as MedianContinous ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY e.AnnualSalary) OVER (PARTITION BY e.JobTitle) as MedianDisContinous FROM dbo.Employee e order by e.JobTitle ``` You perhaps now wondered how the functions `PERCENTILE_CONT()` and `PERCENTILE_DISC()` differ. The discontinous variant returns an actual value that exists in the calculated set and pointing out that this values is the closest one being to the calculative median. The continous percentile in contrast returns a value that is actually the calculated median. For example, Marketing Assistant Stuart Macrae has an Annual Salary of 59’108\. This amount is also the median in this job role, but the calculated median is 59’943.5 so Stuart is slightly below the median but his salary is the closest one to the median. | JobTitle | EmployeeName | AnnualSalary | AverageSalary | MedianContinous | MedianDisContinous | | --------------------------- | ---------------- | ------------ | ------------- | --------------- | ------------------ | | Chief Executive Officer | Ken Sanchez | 530000 | 530000 | 530000 | 530000 | | Marketing Assistant | Pat Coleman | 51874 | 62445 | 59943.5 | 59108 | | Marketing Assistant | Mary Dempsey | 57641 | 62445 | 59943.5 | 59108 | | Marketing Assistant | Stuart Macrae | 59108 | 62445 | 59943.5 | 59108 | | Marketing Assistant | Wanida Benshoof | 60779 | 62445 | 59943.5 | 59108 | | Marketing Assistant | Jo Berry | 71875 | 62445 | 59943.5 | 59108 | | Marketing Assistant | Lori Penor | 73398 | 62445 | 59943.5 | 59108 | | Marketing Manager | David Bradley | 130000 | 130000 | 130000 | 130000 | | Marketing Specialist | Michael Sullivan | 87047 | 101883 | 106316 | 106316 | | Marketing Specialist | Sariya Pit | 95048 | 101883 | 106316 | 106316 | | Marketing Specialist | John Wood | 99407 | 101883 | 106316 | 106316 | | Marketing Specialist | Terry Eminhizer | 106316 | 101883 | 106316 | 106316 | | Marketing Specialist | Ovidiu Cracium | 107883 | 101883 | 106316 | 106316 | | Marketing Specialist | Jill Williams | 108117 | 101883 | 106316 | 106316 | | Marketing Specialist | Rob Walters | 109369 | 101883 | 106316 | 106316 | | Senior Marketing Specialist | George Li | 117253 | 119212 | 118118 | 118051 | | Senior Marketing Specialist | Garrett Young | 118051 | 119212 | 118118 | 118051 | | Senior Marketing Specialist | Sandra Alayo | 118185 | 119212 | 118118 | 118051 | | Senior Marketing Specialist | Russell King | 123362 | 119212 | 118118 | 118051 | | Vice President Marketing | Denise Smith | 250000 | 250000 | 250000 | 250000 | ## Further reading This was just an example of what window functions are handy for. More window functions are documented [here](https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?ref=kayondata.com). Ranked functions are used quite often to find out what happened over time, e.g. if a status of a product changed. If you want to dig even deeper into this topic, I recommend (no affiliation) the book [T-SQL Window Functions: For data analysis and beyond (2nd Edition) (Developer Reference)](https://www.amazon.com/T-SQL-Window-Functions-Developer-Reference/dp/0135861446/ref=sr%5F1%5F1?keywords=T-SQL+Window+Functions%3A+For+data+analysis+and+beyond+%28Developer+Reference%29+%28English+Edition%29&qid=1583591925&sr=8-1&ref=kayondata.com) by Itzik Ben-Gan. He's authored some excellent books with great knowledge. \[^1\]: [Fohen wind](https://en.wikipedia.org/wiki/Foehn%5Fwind?ref=kayondata.com) is a fairly common wind in the European Alps, which is a reference to the popular sample database [Nordwind](https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases?ref=kayondata.com). ### Hello World! URL: https://www.kayondata.com/hello-world/ Last updated: 2026-06-16T22:07:38.000Z This is my first post, testing syntax highlighting here: ```powershell write-output "Hello World" ``` ```t-sql SELECT * FROM TABLE WHERE A = B ``` ```python print("Hello World!") ```