Azure Arc-enabled Data Services – Cloud Summit 2021

Also, yesterday I was able to speak at an international community event – the Cloud Summit – about Azure Arc-enabled Data Services, of course mainly about how to operate an Azure SQL Managed Instance in your own data center. As already at DataSaturday #14 in Oslo, I started with the various options of Azure Arc and presented the individual components of the environments, what is needed, and how this can be deployed relatively quickly. In the end, there was again a detailed demonstration of how you can deploy a data controller on Kubernetes and how you can then roll out a managed instance or PostgreSQL Hyperscale using Azure Data Studio.

I was pleased to be part of the Cloud Summit 2021 event, which lasted several days. The lecture itself was a very successful one from my point of view; everything worked, time was a bit short, but you can work on it / improve it in the future.

With this short article, I just wanted to put the slides online and thank the organizers and sponsors of the Cloud Summit very much!

Cloud Summit 2021 – Bjoern Peters – Azure Arc-enabled data services

Data Saturday #14 – Oslo 2021 – Introduction into Azure Arc Data Services

Today the time had come – I was finally able to speak at Data Saturday (formerly SQLSaturday) in Oslo … that was finally not due to the organizers but rather to me or the pandemic, since I hadn’t dared to give lectures until 2020 Keeping English, I hadn’t submitted any sessions for Oslo either. I would have loved to have been there, of course, because Norway and Oslo are beautiful; I now also know several community members in the region that I haven’t seen for a long time. Still, this year it had to stay virtual for the time being, maybe yes, in person again next year.

But now to my lecture on the introduction to Azure Arc or Azure Arc Data Services, which was on Saturday, September 4th, 2021, at 9:45 am. Azure Arc itself is, first of all, a platform, a service that Microsoft provides in Azure to manage and administer the administration of various environments – hardware, virtual machines, Windows, Linux, AWS, GCP, or whatever – in a central location.

Introduction into Azure Arc

This means that you can now, for example, connect your on-premise Windows or Linux servers to the Azure portal to administer them from there. Or from the multitude of services from the cloud, such as security or compliance, these benefits come with Azure Arc, in which you can integrate your on-premise systems into the analyzes of these Azure services. In addition, automation such as patching or backups can be created and administered from the Azure portal or other Azure services, which means a significant advantage in the standardization of systems and processes.

And not only with on-premise servers or systems, but also with multi-cloud and “disconnected” systems, including Kubernetes clusters or SQL servers

  • Azure Arc-enabled servers
  • Azure Arc-enabled Kubernetes
  • SQL Server auf Azure Arc-enabled Servers
All applications, systems, and environments are centrally united.

Azure Arc Data Services

Azure Arc Data Services is understood to mean the possibility of running an Azure SQL Managed Instance or a Postgres Hyperscale on “any” Kubernetes cluster; whether this Kubernetes cluster is in your own data center or another cloud does not matter. The main thing is it has to be a CNCF-certified Kubernetes cluster.

However, to operate a managed instance in your own data center, you have to follow several steps … on the one hand, you should turn the Kubernetes cluster into an Azure Arc enabled Kubernetes; on the other hand, you have to have a “connector/agent” on this cluster roll out the data controller. This data controller establishes the interface between Azure, the Azure portal, and the SQL Managed Instance. Now you can deploy and use a fully functional SQL Managed Instance on this cluster in various ways – Azure Portal, Azure CLI, or Azure Data Studio.

Deployment of an Azure SQL Managed Instance on a data controller
on your own Kubernetes cluster

From a personal point of view, it is a great story to have a central point of contact for all services and applications used and thus to use the additional (security-enhancing) features from Azure on all systems. All in all, an improvement for the company!

My slides and further links


Microsoft Documentation

If you want to try out this possibility of the rollout of Azure Data Services yourself, then I can only recommend everyone to deal with Microsoft’s Jumpstart scenarios; here, prefabricated environments are rolled out with which you can then easily test what works how where:

All that remains to say is THANK YOU to all listeners and the organizers; it was a great pleasure and a lot of fun. #CommunityRocks

South Florida Data Geeks Saturday 2021

Today, Saturday, I was allowed to give another lecture, this time, it was halfway around the world in the wonderfully warm and relaxed South Florida, to the data geeks community. This is the eleventh year that they are organizing their “SQL Saturday,” on which there are usually ~ 700 participants on-site who deal with the Microsoft Data Platform topics and learn about various subjects in their free time. This year all Data Platform speakers around the world were called upon to submit their sessions. Happily, 92 speakers responded to this call and did not make it easy for the organizers to select the most interesting and exciting presentations from the 180 proposed topics. I submitted several sessions on Azure SQL Databases and Azure Arc Data Services.

For me, it was the first time that I was there in Florida, and hopefully not the last time, because it was a great pleasure for me to be able to do my part. My session on Saturday was about the differences between the skills of a database administrator who “only” operates on-premise SQL servers and a DBA who is only / also in the cloud. In this session, I showed which services are available in the Azure Cloud for the SQL Server DBA and how his work environment and activities change accordingly, which topics he will have to take care of in the future and prepare for it them.

Change your skills – from an onpremise DBA to a cloud DBA

After my brief introduction to who I am and what I do, I first gave a quick overview of Microsoft’s different services in Azure for operating SQL Server databases and how these services differ from on-premise. For the easiest way of migrating from on-prem to Azure, the regular Azure virtual machine is suitable for the 1: 1 migration of a SQL server. If you only need a single database, you would undoubtedly choose the Azure SQL Single Database, but if it should be a complete SQL Server instance (but without VM around it), select the Azure SQL Managed Instance.

But since this is not all and you have to make compromises from time to time, or there are special requirements, there are also “gray areas” or unique solutions such as Azure SQL Hyperscale. I discussed the differences in my presentation before going into the local SQL Server environment differences. So what the future SQL Server DBA should also be able to do, and what topics it should deal with in the future.

Azure SQL - Übersicht über die verfügbaren Services in Azure zum SQL Server
Many thanks to Anna and Bob for these slides 😉

Now that everyone knew which services they can / must look after later, I could also go into the differences between on-premise and Azure and which add focal points need to be considered. In any case, the difference or the differences in the backup procedure had to be mentioned here. Here you may not have to worry about it at all, or what depends on the selected service. The Azure SQL DB, for example, also offers the option of supporting index optimization of indexes. Database monitoring is also changing, as Azure collects a lot of data and makes it available to the portal. And the database administrator has to face significant changes in terms of availability in the network and security. There is a lot to consider and configure here, depending on the company’s requirements or the application.

My presentation

Last but not least, I went into the learning opportunities that Microsoft makes available free of charge to be able to adjust to these new challenges or be able to prepare, which learning modules, learning paths, labs, or workshops are available. I would like to provide the individual links accordingly and my slides for this lecture in the following list.

Learn and train around Azure SQL

I want to thank all participants and the organizers for this successful and exciting event and, of course, their trust!

Azure SQL Database – General Overview – Refresh Part 1

I see the other contributions as a personal refresh of my knowledge of the Azure SQL Database and all its numerous features. After a long time, it was necessary to deal with it in detail again and show my own experience with learning that “writing down” helps remember the content and essential points. So I’ll just use the next (albeit perhaps shorter) blog posts to let you take part in my “refresh.” This is also relevant because the Microsoft product group has set the pace during the past few months and has presented numerous new features or optimizations to the existing ones. (Thanks for that ;-))

Understanding Azure SQL Database

Basically, it is about the Azure service, which provides us with a SQL Server database. There are numerous options for deployment or in the form. Starting with selecting the required service (aka “What do I or my application need?”). Continuing with “How many databases does your (new or existing) application really need?” If it is a further development of your own, you may still influence certain functionalities; this is hardly possible with existing in-house developments or purchased products. Example VMWare … here as a rule, at least 3 databases are required, with Sharepoint even more, with a CRM system may be only one.

But to understand, it is not about, for example, that my client-capable CRM creates a separate database for each client, but instead that only one (!) database is required per client, so no “cross-database” queries are used.

If you should make a statement here, you are one step further. You can at least once decide that Azure SQL Database is the right product. Now comes perhaps the most challenging point, but which (under certain circumstances) is good retrospectively can be adjusted.

Microsoft provides us with a Platform-as-a-Service database service in Azure based on the last stable version SQL Server. We as administrators don’t have to worry about a lot here, as Microsoft already does a lot for us, such as upgrades, backup, or monitoring. I will come back to the individual points later. We don’t have to worry about “high availability” either. All Azure SQL Database Services (depending on SKU and deployment) have at least 99.99% availability and can therefore also be used for critical and high-performance business applications.

As we know it from SQL Server, relational and non-relational data structures can also be used in the cloud, also in-memory technologies.

Choosing options for your requirements

The Azure Service SQL Database can be used in different versions and performance classes to meet almost every requirement.

  • Single or pool database
  • Hyperscale
  • Serverless

Each individual deployment can still be divided into the various performance classes, which I will explain in more detail later. Aspects such as CPU, RAM, database size, and service life play a role here to make an optimal decision.

Azure SQL Database - Representation of the Azure Portal - Selection of the different services

Deployment differences of an Azure SQL Database

If you decide that you only need one database, then the Azure SQL Single-DB (or Singleton) is the optimal one. Azure provides a single database in a shared environment, which is so isolated or isolated that you don’t have to worry about something happening here (i.e., no external access from outside your own database). Each Azure Single DB receives its own compute resources and can then use them exclusively.

For example, suppose you have several small databases with different loads at different times. In that case, you can choose an Azure SQL Elastic Pool. Several individual databases are configured in a pool, which can then share the shared compute resources depending on usage and workload.

Depending on the application’s requirements or the business, both single and elastic pool databases can be dynamically scaled to the workload to be processed.

Microsoft differentiates between serverless and server-based in these performance classes, with server-based also being subdivided into “General Purpose / Universal” and “Business Critical / Company-critical” and further into the Compute’s possibilities -Select resources in vCore- or DTU-based. There is currently no serverless deployment for the elastic pools, but there is a distinction between GeneralPurpose and BusinessCritical and the division into DTU (Data Transaction Units) and vCores.

Azure SQL Database - Representation of the Azure Portal - Selection of the performance classes of Elastic Pools

I’ll get to the availability, monitoring, and backup of Azure SQL databases in one of the other blog posts.

Azure SQL VM – no possibility to configure the disk usage

A customer had asked me the last few days to help him optimize a SQL server on an Azure VM. The customer always had slight performance problems with this SQL server, so he first asked the application manufacturer or supervisor, but they didn’t want to or could help. The only answer they received was that a disk latency of 5 ms is recommended for the DATA disks of the SQL Server… Officially, in Azure, this can only be achieved with ULTRA disks. In this blog post, I don’t want to detail how this might be fulfilled. Just show you an error that I noticed during the analysis.

Starting with a small intro, I talked to the customer and showed him my calculation of what it would cost to equip the server with 2 TB ultra disks and whether it might not be more reasonable and adequate to put the SQL server through its paces and optimize it (also the application or its T-SQL) and in the last step to go to ultra disks if necessary.

The customer agreed, and we have taken the SQL Server under review. Even if they are using a marketplace image from Microsoft (thanks to the product team!), which are already configured very well, there might be some more screws to adjust.

Azure SQL VM and the data disks

If you follow the recommendations for Azure SQL in a virtual machine, you can read the recommendations there:

Stripe multiple Azure data disks using Storage Spaces to increase I/O bandwidth up to the target virtual machine’s IOPS and throughput limits.

In a first effort, we replaced the 1TB disk with several small disks to increase the maximum throughput from 200MB to 625MB. So far, this has been accomplished without any downtime, the customer has been delighted so far. But in this activity, I also came across a message in the Azure portal on this virtual machine or the SQL Server configuration overview, which confused me a little; Google wasn’t a real help.

In the Azure portal, there is the possibility to roll out several extensions for all SQL servers within an Azure VM, which are intended to support the admin-team in managing the VM or the SQL server via Powershell, Azure CLI, or the portal and provide an overview on data from the logs and metrics. Microsoft uses the “SQL Server IaaS Extension Query Service” or “SQL Server IaaS Agent extension” for this purpose. Both collecting data from the virtual machine and the SQL Server and display it in a user-friendly way in the portal. Unfortunately, that was the problem with this customer VM, and these metrics were not 100% available, as the following screenshot shows.

Azure SQL VM - No data in the portal for disk usage

No data on the storage could be determined in the further “Configure” overview, although all extensions were rolled out on the server and did not display any data even after one or more reboots. So I went looking for the cause…

Extension-Log and fn_trace_gettable

As the Microsoft documentation states, there are different modes for different requirements, and of course, you can also notice a local error log, which you can use for troubleshooting. This error log is an XE file and a trace file, neither of which can be read with Notepad only with SQL Server Management Studio… so the easiest way to access the trace file with a T-SQL system function to determine that the system user apparently has no or too few authorisations. Usually, the extension is located directly on the C drive in the directory C:\SQLIaaSExtension, so you have to use this path accordingly in the SQL query.

SELECT * FROM fn_trace_gettable('C:\SQLIaaSExtension\Log\log.trc', default);  

By executing this T-SQL statement to examine the extension log file, I received the following line, among other things:

alter server role [sysadmin] add member [NT Service\SQLIaaSExtensionQuery]

When seeking for the user, I discovered that the customer had deleted this user – for whatever reason (later I found out that this apparently did not happen “by accident”, on other SQL servers in Azure, the user is also missing)

USE [master]

/****** Object:  Login [NT SERVICE\SqlIaaSExtensionQuery]    Script Date: 5/10/2021 3:35:08 PM ******/

/****** Object:  Login [NT SERVICE\SqlIaaSExtensionQuery]    Script Date: 5/10/2021 3:35:08 PM ******/


As a test, I recreated this user, authorized it, and checked in the Azure portal whether there was a change in the representations… Yes, the SQL Server IaaS Agent Extension can now access the SQL Server and read out the necessary and relevant data.

Azure SQL VM - Data in the portal for disk usage