Press "Enter" to skip to content

Author: Kevin Feasel

Lessons Learned from Creating Database Projects

Published 2023-02-21 by Kevin Feasel

Olivier Van Steenlandt shares some hard-earned knowledge:

Almost 5 years ago I made the switch from “traditional” database development using SQL Server Management Studio to a more flexible way of development by using Database Projects and Source Control. In the first few years, I worked with BitBucket as my code management system and for 2 years I’m using Azure DevOps. In my spare time, I’m using GitHub as well.

During this transition, I came across a couple of bumps, because I wasn’t familiar with Database Projects and I only had a notion about Source Control (Git). In this blog post, I will describe my journey and the lessons learned during the process.

Click through for several tips.

Comments closed

Getting Started with Azure Synapse Analytics

Published 2023-02-21 by Kevin Feasel

Shabnam Watson gets us started with Azure Synapse Analytics:

In this blog post, I show you how easy it is to start an Azure Synapse Analytics workspace (instance) and use its Serverless SQL Pool engine to analyze sample publicly available data. As you will read shortly, Azure Synapse Analytics provides many compute engines for different use cases. The easiest one to get started with is its Serverless SQL Pool since every Azure Synapse Analytics instance comes with one already created and ready to use. It also does not have any cost unless if you use it which makes it very attractive to those who have a limited Azure budget.

Click through to see how to create a workspace, load some data, and query it via the serverless SQL pool.

Comments closed

The Observer Effect with Extended Events

Published 2023-02-21 by Kevin Feasel

Jonathan Kehayias measures the measurer:

SQL Server 2022 offers a new feature enhancement to Extended Events that allows it to now track the performance and publishing metrics of the events that have been enabled in an event session that is running on the server. Four new columns were added in the sys.dm_xe_session_events DMV in SQL Server 2022 that provide additional information about the event publishing performance metrics when an event session is running:

This fits more in the “wacky ideas” category than a sensible thing to do, but it can give you a better idea of how expensive certain events are.

Comments closed

Checking for Permissions on a Database User

Published 2023-02-21 by Kevin Feasel

Chad Callihan keeps misplacing those permissions:

I recently encountered an unusual permissions issue with multiple databases. New databases were not including all of the permissions that were supposed to be set following database restores. At the time, I wasn’t sure if the permission was being granted and then revoked or not granted at all. I wanted a script I could run to definitively show that permissions did exist and also have proof for myself that, if permissions seemingly vanish later on while testing, I know they were present at one point in time.

Click through to see what Chad plans to use to see if permissions disappear later. This will work with directly granted permissions on a user, so you will miss out on some chained permissions coming as a result of being in a Windows group or user-defined application/database role.

Comments closed

Delta Lake Support in Azure Stream Analytics

Published 2023-02-20 by Kevin Feasel

Emma An makes an announcement:

Delta Lake has gained popularity in recent times due to its unique features and advantages over traditional data warehouse and other storage formats. For those already using traditional data storage format or moving to a lakehouse architecture, Delta Lake can offer several compelling benefits that can further enhance the performance and capabilities of their data pipelines. Many Azure services are integrated with Delta Lake, and now you can use Azure Stream Analytics to write in Delta format.

In this blog, we will explain the native support of Delta Lake in Azure Stream Analytics, that can help users take their workload to the next level, providing a seamless and scalable solution for large-scale data processing and storage. It is easy to start, taking only a few clicks to create an end-to-end pipeline, and write to either a new or existing Delta table stored in Azure Data Lake Storage Gen2.

This is a nice addition to Stream Analytics and Emma shows two ways you can write out results in Delta Lake format.

Comments closed

Tracking Azure SQL Managed Instance Logins

Published 2023-02-20 by Kevin Feasel

Michael Bourgon watches the doors:

This is a barebones Xevent (Extended Event) . I’m not using blob storage, just the existing “eh, you have a couple hundred meg worth of ring buffer you can use”. But I needed to see who was using one of our dev instances. I thought we’d moved everything over to the Azure SQL DBs, since there’s a ton of reasons to do so.

Read on for the event definition, as well as Michael’s thoughts on Azure SQL Database versus Azure SQL Managed Instance.

Comments closed

ORDER BY Clause in Subqueries and SSMS Warnings

Published 2023-02-20 by Kevin Feasel

Ronen Ariely explains a warning message:

Warning: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself. Click CANCEL to discard your modifications. Click OK to save the view.

Read on for the full context of when you might see this warning message in SQL Server Management Studio.

Comments closed

First Thoughts on Azure Hyperscale Serverless

Published 2023-02-20 by Kevin Feasel

Reitse Eskens shares some thoughts:

As some of you know, I’ve written a series of blog posts on Azure SQL Databases and there’s an accompanying session that I had the honour of presenting a number of times.
Now Azure keeps developing new offers and one of these went in public preview February 15th. An offer I hadn’t seen coming. You can read the introductory post here.

It’s the Azure Hyperscale Serverless option.

Read on for Reitse’s impressions from the preview. This wasn’t a torture test but did provide an overview of how to create and load data into the database. Reitse also calculates the cutoff point when you should switch from Serverless to traditional Hyperscale, so check that out as well.

Comments closed

Capabilities (and Limitations) of Power BI Migration Tools

Published 2023-02-20 by Kevin Feasel

Chris Webb talks tools:

I have so many customers wanting to migrate from legacy BI tools to Power BI. They are concerned that their current BI tool has an uncertain future. Licence renewals are looming and in the current economic climate organisations are looking to save money. Power BI is not only a lot cheaper than other BI tools, it’s a better tool overall and since Microsoft continues to make big investments in it then migration is clearly a no-brainer.

As a Power BI consultancy owner I have a problem though: I don’t have enough skilled people working for me to keep up with all this demand. What’s the answer? I know! Let’s build a tool that can help migrate all these legacy reports to Power BI!

The result is that, so far this year, I’ve seen or heard of five or six different Power BI migration tools built by various consultancies. That’s great and here at Microsoft we’re naturally supportive of our partners and want as many people to use Power BI as possible. I have reservations about some of these tools though, and these reservations fall into two categories.

Read on for Chris’s take on the topic.

Comments closed

Notifying when MCR Has New SQL Server Images

Published 2023-02-20 by Kevin Feasel

Andrew Pruski builds an alert:

A while back I wrote a post on how to retrieve the SQL Server images in the Microsoft Container Registry (MCR).

It’s pretty simple to check the MCR but what about automating that check to alert if there are new images present? Let’s have a look at one method of doing just that with a powershell script using the BurntToast module, and a scheduled task.

Click through for the process and keep those Docker images up to date.

Comments closed