Voice of the DBA

Tag: Azure

Cleaning Up Azure MS Defender Billing

I have a few Azure subscriptions, which are fairly small and not used that often. Mainly I do some SQL Server or Azure SQL experimentation. Sometimes I might create another resource, but I try to not waste resources and disable them quickly (or drop them) when I’m done.

However.

I hadn’t done much in the last couple of months on Azure. For some reason, I decided to check my bill. What I found was that there were charges for $35-ish/month. This is within my free credit limit as an MVP, but that seemed high. I have a few serverless dbs and some storage, but I was expecting more of a $15 charge.

I decided to investigate. I started with the Billing section, where I saw that Microsoft Defender was swamping the charges in my bill. I don’t remember enabling this, and typically skip this stuff since I work with test data and nothing sensitive.

As you can see, the Defender items are charged, but not linked to a resource?

WTF? How do I debug this? I have a small subscription, maybe a few dozen resources. Imagine you had a larger estate? I decided to ask Copilot. I get this result:

OK, environment settings, let’s check the items under here.

Now, where is this enabled? I checked the Defender properties for the subscription.

I went into the various Azure SQL databases, clicked the Security section for Defender and I keep seeing nothing enabled.

I’m ready to open a case, and waste the days and then multiple contacts trying to work through this with some customer service person in another time zone, who reads docs.

Digging further, I keep clicking around, Copilot being of no use here. I eventually start clicking in every resource, and I get to the logical server for my Azure SQL Databases. Here’s where I see it, at the server-level.

I don’t think I turned this on, but perhaps I tested something.

In any case, I click settings and then disable Defender.

I’m less concerned that this got turned on, because I might have done that. I would love some auditing of actions to determine this, as I could see someone else in my team (if my team were bigger than me and the 3 dogs) trying this out. I’d want to know who to talk to about changing settings.

More, I’m annoyed that the bill doesn’t link to resource settings, or that this being enabled at the logical server level isn’t reflected and noted at the database level.

This was a 45 minute investigation that should have taken 3, so a word of warning to you Azure users. Be careful what you enable, and when you have billing issues, check the resource group and logical servers, in addition to your databases.

November 2, 2026
Serverless Gets Faster

When the Azure SQL Database serverless option was introduced, I was a bit disappointed that I couldn’t get the database to pause any sooner than 1 hour. That meant I needed to ensure clients didn’t access the system for an hour, but also, that I burned an hour of compute after the last access.

Recently I saw an announcement that this time frame has come down to 15 minutes. While this might seem like a very simple change from a technical standpoint (just alter a timer option), I’m sure there was more work needed. I’m also sure there was a lot of debate on the sales/marketing side to decide if this would lose a lot of revenue.

I’m sure this costs Azure some compute revenue in the short term, but it might also create opportunities from customers who consider using this in new situations since it can shut down quickly. I certainly think this makes the use of an Azure SQL database for QA/staging type work more attractive. This might also get more people to take a look at serverless and realize the auto-scale benefits are pretty cool.

My request would be to drop this down to 5 minutes and increase the range of auto-scale as well. Maybe allow me to go from 2-16 vCores if needed with corresponding memory jumps. I don’t know I need this by the minute, but I would like to have things shut down fairly quickly if we stop a workload and aren’t using the system.

I’d also like a better retry on startup other than trapping an error on the client and re-sending my request to connect. It’s just embarrassing that we still have that happening for a cloud PaaS service.

Steve Jones

October 12, 2024
Creating a Synapse Workspace
As part of my job, I needed to research how a few things work with Synapse and Fabric. The latter includes the former, mostly. I decided to setup a workspace and a do some experimentation.

My first stop was the Stairway to Synapse Analytics. I started with Level 1, since I needed to create something.

In the portal, I created a new Synapse Analytics resource. I won’t go into details, but basically create a new resource and search. Read the article to see how this works. From there, I used a resource group I had for work stuff. I also had to create a new Data Lake Storage Gen2 account. I didn’t bother with the Managed Resource Group.

I added a user account and password and then clicked Create. I went to the summary page, where I could see the small cost for serverless. I clicked “Create” again to start the deployment.

The deployment failed.

I was leaning towards this not being helpful, but I went into the details of the error message and found something interesting. My subscription couldn’t deploy in this region (westeurope).

OK, go back, recreate the resource in UKSouth. That worked fine and I saw my resources.

At this point, I had a resource.

Adding Data

The next step for me was to add some data. I grabbed a few csv files and uploaded them to my storage account. Lots of querying in Synapse is through external tables to flat files, so I picked some files I can query.

Once these were uploaded, I was next interested to see if I could query this data. In my main Synapse resource, I see some endpoints.

I copied the serverless one and then opened SSMS. I put this, my user and pwd, and got connected. I was hoping that @@Version would tell me I was connected to Synapse, but I got the same results I get some Azure SQL Database, albeit with a different timestamp. However, ServerProperty() helps.

That works, what about my data? Let’s try a query from Level 2.

I’ll take this query and run it. I’ve adjusted this from the values in Level 2, which actually uses the Synapse Workspace explorer online.

–retrieve data from csv file
```
SELECT
TOP 100 *
FROM
OPENROWSET(
BULK 'https://synapsesqlprompt.blob.core.windows.net/sqlpromptfs/solar_2024_01.csv',
FORMAT = 'CSV',
        HEADER_ROW = TRUE,
        PARSER_VERSION = '2.0'
    ) 
WITH (
    [Time]  DATE,
    [System Production (Wh)] VARCHAR (100)
    ) AS [result]
```
It fails.

Hmmm, let’s try that in the browser. Here it works.

The error is with a credential.

There is a quickstart online, and that query works. However, there is a note in the query that I need a credential if my file is protected.

The article had this link and used the sample code to create a credential:
```
CREATE CREDENTIAL [https://synapsesqlprompt.blob.core.windows.net/sqlpromptfs]

WITH IDENTITY='SHARED ACCESS SIGNATURE'

, SECRET = '';

GO
```
Now I can run my original query and it works.

That was a pretty cool exercise for me to get started. In less than 30 minutes I was able to create a Synapse workspace, add some data, and query it.

Now to learn a bit more about how this works, and to use Flyway to deploy some objects.
August 26, 2024
Managed Instance Impressions

Several years ago, I heard about a new product coming in Azure that would provide an IaaS (infrastructure as a service) VM to run SQL Server, with Microsoft managing most of the admin tasks for the instance, like patching and backups. That didn’t seem like a big load to me, and I wondered if anyone would actually pay for this product. After all, don’t most companies find managing patches and backups fairly easy to manage?

That product became Azure SQL Managed Instance, and I’ve been surprised at the adoption. Quite a few clients have adopted this as a way to lift and shift (mostly) to the cloud in an easy fashion without the restrictions of Azure SQL Database. This looks like a “normal” on-premises SQL Server, and there are both high-performance (Business Critical tier) and average-performance (General Purpose tier) versions of the product that let you choose what level of price/performance you need to achieve.

I’m curious today, and I have a question. What are your impressions of Managed Instance (MI)? Whether you use it, you have heard of it, or if you just read this description. Give me a few thoughts on whether this makes sense, performs well, or has issues you need (or wish would be) addressed.

I’ve heard there are some issues with I/O, but I also have clients who find it performs very well for them. I hear similar things from on-premises SQL Server instances, so I often think that either the software is designed well or the hardware architecture doesn’t match the workload. There have been a lot of enhancements to MI since its release, including the ability to backup and restore to/from SQL Server 2022.

There is even an offer from Microsoft that lets you try out MI for free (for a period of time). This is a way for you to test migrate a database to the cloud and measure the performance. You might need to do some work to measure your current performance in a way that lets you determine how MI stacks up. You will also need to do some financial number crunching to decide whether there is an ROI that makes sense. If you do that, be sure you reach out to your internal finance people to understand the differences between CapEx and OpEx expenditures for your analysis. Paying $50k a year for an MI license isn’t the same as spending $50k for a server and hosting.

I’m not sure what I think of MI. Like many offerings, I think there are places where it makes sense and places where it doesn’t. It’s not a simple decision for me as an abstract question. For specific situations, I might lean one way or the other, but I’d want to do some workload analysis to justify or discard my initial thoughts.

Share your thoughts and impressions today. You might help some of us learn more about why we might or might not use MI. You might even help clarify your own thoughts by writing them down.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

May 27, 2024