Voice of the DBA

Tag: Azure

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
Database Mirroring is Back in Azure SQL Database

Database mirroring was a cool feature in SQL Server 2005. I guess it’s still a feature, though it’s listed as deprecated in the documentation. There is still a mirroring dialog in the SSMS database properties dialog in more recent versions of SQL Server, but I don’t know if there is a good reason to use mirroring over Availability Groups.

That’s why I was surprised to see a public preview announcement of Azure SQL Database Mirroring to Microsoft Fabric announcement. Apparently you can easily move Azure SQL Database data to Fabric and have it written to Delta Parquet tables in OneLake. No ETL, no need to do the data conversion yourself, or at least not much of an effort. I suspect you still need to understand this and do some configuration for how your Parquet files will get written.

If you go through the documentation, it’s interesting (and annoying) to me that the docs keep saying replication. I hate when we’ve overloaded terms as mirroring and replication mean specific things in SQL Server, so I wish they would use some other term (copy, extract, ??) to describe what is happening.

Whether this is useful to analytic workloads remains to be seen. I am curious what the people working with Fabric think of this feature. I wonder if this is useful, or if this might cause headaches or performance issues. Since this feature is in preview, I doubt anyone knows yet, but it will be interesting to see how this compares with Synapse Link and if it is more or less helpful.

Moving data around for different purposes has always been a challenge. I know that some people might feel the costs involved in this aren’t worth it. I know a lot of technical people that would say “I could do that.” I have no idea what they costs are. but I know that a lot of people have spent a lot of hours managing ETL packages and adjusting them as schemas change and new requirements appear. I get the appeal of Synapse Link and this new Mirroring to Fabric features.

I do wish this type of feature was more solidly built into the SQL Server instance. I suspect it will come at some point, and it’s being tested and baked in Azure first. However, I hope that if/when it comes, that the feature has good tooling and some polish to its operation. We’ve had too many features in SQL Server that are partially built, with limited tooling, and a lack of performance characteristics that many of us would desire.

Steve Jones

March 30, 2024