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

DataSaturday-Oslo-2021-Azure-Arc-Data-Services.pdf

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:

https://azurearcjumpstart.io/overview/

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

Why is it called “SQL aus Hamburg” or “Nord DBA”?

Actually, I should have written this post years ago 😉 to shed light on the history or the genesis of this blog. But as the saying goes … it’s never too late.

At that time, I took my first initiatives and activities with the German SQL Server community, attended the first events, and made the first contacts. Unfortunately, I realized that my knowledge of the SQL Server was relatively low even after more than 10 years. I wouldn’t say I liked this myself, and I have set the goal to deal more with the product, basically, exactly the story of why I am active in the community. At the time, I was employed as a database administrator and, together with my colleague Thomas, looked after our customers’ SQL servers. Thomas knew a lot about SQL Server from other areas, and I already knew a lot, so we complemented each other wonderfully. Together we wanted to run this blog and write about our daily experiences, problems, or challenges. Since we both came from Hamburg – in a larger team spread across Germany – and it was our main task – the SQL Server – the search for a name was relatively easy …

Due to my active self-employment at that time, I already had a corresponding webspace, and the domain “SQL-aus-Hamburg.de” was quickly registered. The first blog post wasn’t long in coming, and then it got a little quieter around our blog 🙁

We had probably thought it would be easier, and the work demand grew… after such a long time; I can’t say why we didn’t write anything anymore… we just weren’t that far back then… Thomas always had good ideas and gave me the headlines, so to speak… but unfortunately, he never wrote any post himself (shouldn’t be a reproach!)… and then everything turned out differently… Thomas died unexpectedly and suddenly. So after that, I had to continue this blog under the same name.

For me, this blog is still, as you can see with the last posts on the Azure SQL Database Refresh, simply a notebook in which I review my own experiences for myself so that I might find them at some point or remind me when I was able to solve a specific problem successfully.

Why SQL-aus-HH ???

In social media, you can find me under @SQL_aus_HH; For the German-speaking reader, no problem at all to draw a connection between Hamburg and HH… initially, I had never toyed with the thought that at some point, I would also write articles in English or I even give talks at international events… so here again to clarify for the “foreigners.”

Hamburg is a Hanseatic city and therefore bears the abbreviation HH for “The Hanseatic City of Hamburg” on the German license plate.

As early as the Middle Ages, merchants earned a golden nose with long-distance trade. The Hanseatic League was particularly successful. This is what an alliance of cities and trade associations called itself about 700 years ago. The Hanseatic League was closed to simplify trade between the members, the Hanseatic cities. At that time, numerous tariffs, different currencies, and units of measurement made long-distance trade difficult.
[…]
Ultimately, only Hamburg, Bremen, and Lübeck officially bore the title of Hanseatic city. Hamburg and Bremen have partly retained their Hanseatic independence to this day. Besides Berlin, they are the only German cities that are also separate federal states. Their license plates also indicate the medieval city federation: HH stands for the Hanseatic city of Hamburg and HB for the Hanseatic city of Bremen.

http://stadtgeschichtchen.de/artikel/stadtgeschichte/was-ist-eine-hansestadt/
http://stadtgeschichtchen.de/artikel/stadtgeschichte/was-ist-eine-hansestadt/

Since this blog was supposed to be run by two people at the time, I had also thought about a name for my “personal appearance” or wanted to be a little more “creative” in connection with a favicon for the blog … than “Northern DBA” … please don’t ask me about the reasons/thoughts that led to this name … I can’t say it anymore. Still, this favicon has been around for a good 10 years as a “distinguishing mark” for this blog.

If you have any questions about the genesis of my blog … write to me.

SQL Server High Availability – DBCC 2021 – Part 1

Any one of my customers has asked, or should have asked, “do I need a certain level of high availability for my SQL server or one of my SQL servers?” Regrettably, these deliberations often come to a halt when it comes to the budget… Naturally, such HA-solutions are more expensive, and depending on the solution chosen and the environment’s configuration, appropriate licenses must be made available. However, if one calculates the costs that will occur in the “worst-case” scenario during or as a result of an outage, it should be clear that these costs are reversible.

I had the opportunity to give a speech about it last week at the Data Blaster Community Conference 2021 (thank you, SQLPASS Deutschland), and here is a summary of what I spoke about.

I’ve shown right from the start how high such costs can be, and I’ve done so with the help of a Percona online calculator. The SQL Server goes down for 1-2 hours, 5 employees are assigned to the Restore, each earning $100,000 per year, and 100 additional employees cannot work correctly, earning an average of $50,000 per year. In the following screenshot, you can see the numbers that can occur during an emergency, during recovery, or as a result of the trouble.

Why does SQLServer High Verfuegbarkeit matter? - Percona Calculator

With total costs of about 8.000.000 Euros for a 2 hour SQL Server outage, I’d consider spending an additional 250.000 Euros for the installation to add a second server and equip it with the required licenses. But, as I’ll explain in the following sections, such high availability can be seen in the SQL Server setup dialog, but first, let’s clear up the terminology… 😉

Disaster Recovery vs. High Availability

On wikipedia.de, the term “high availability” is described as follows:

High availability (HA) is a characteristic of a system that aims to ensure an agreed level of operational performance, usually uptime, for a higher than standard period.

https://en.wikipedia.org/wiki/High_availability

In comparison, the concept of disaster recovery.

Disaster Recovery assumes that the primary site is not recoverable (at least for some time) and represents a process of rebuilding data and services to a secondary survived site, which is opposite to restoring back to its original place.

https://en.wikipedia.org/wiki/Disaster_recovery

When it comes to high availability, it’s all about keeping the time of an outage or downtime as low as possible so that all connected systems can keep running as quickly as possible without data loss or manual intervention. Naturally, before the installation, you should think about the process design, the goals to be achieved, and the need.

  • What is the purpose of the business?
  • What would it cost the company if they are unable to work?
  • Is there, on the whole, a compromise between technology and business? Does it really have to be the minimum downtime (<10 seconds), or is it possible that maximum downtimes of less than a minute are sufficient?
  • What is it that technology can accomplish?
  • Knowledge, processes, and all those must all be followed to the letter of the law?

And only after these questions have been answered can you think about the actual solution for high availability and how it can/should be implemented.

The AlwaysOn Failover-Cluster (FCI)

High Availability - SQL Server - AlwaysOn Failover Cluster

We now come to one of the possible solutions for achieving high availability of SQL Servers inside your own datacenter, the Windows Failover Cluster (basically identical to a Linux Failover Cluster, but you should refer to the documentation of the respective distribution and cluster software).

Initially, a Windows Failover Cluster is created from two Windows Servers with the additional installed Failover-Cluster feature, or, to put it another way, both servers are logically (and in AD) connected. Both are familiar with one another and are now aware that they belong together, exchanging several relevant health-status information. This Windows Failover Cluster is given its own IP address and DNS name and a Cluster-Named-Object (CNO) in Active Directory, which later “manages” the cluster.

I can’t say anything about the required storage requirements because it varies depending on the environment… Whether it’s a SAN, NAS, NFS, HCI, or something else, it’s always about a storage system that can give both servers access. Microsoft adds the following:

Contrary to the availability group, an FCI must use shared storage between all nodes of the FCI for database and log storage. The shared storage can be in the form of WSFC cluster disks, disks on a SAN, Storage Spaces Direct (S2D), or file shares on an SMB. This way, all nodes in the FCI have the same view of instance data whenever a failover occurs. This does mean, however, that the shared storage has the potential of being the single point of failure, and FCI depends on the underlying storage solution to ensure data protection.

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver15&WT.mc_id=DP-MVP-5002576#FCIelements

Now you can start installing SQL Servers; on Node 1, you’ll set up the foundation for a SQL Server Failover Cluster (Installation of a Failover-Cluster), while on Node 2, you’ll only add a node to an existing SQL Server Cluster.

Depending on how this cluster is set up – whether it’s only one instance or many – SQL Server licenses must be acquired. You can choose between two operating modes: active/passive or active/active. When using active/passive, SQL Server instances can only run on one cluster node, but they can run on both nodes for a limited time in the event of a failure. When using active/active, all nodes can be used equally and for an unlimited amount of time, which is especially useful when dealing with several instances because it allows some kind of load-balancing.

If a node in the cluster breaks, the second node can no longer communicate with the other side and tries to get the broken services into his side as quickly as possible and start them there.

Unfortunately, a hybrid scenario is not possible due to the lack of scalable resources on-premise and in the cloud. However, a Failover-Cluster can be built in the Azure Cloud as an alternative. To do so, you’ll need a Proximity Placement Group, Managed Disks with the “Shared Storage” feature enabled, and the two virtual machines described above, whose installation and operation are essentially identical.

High Availability - Azure SQL Server Failover Cluster - DBCC2021

More on SQL Server high availability will be covered in a next blog post.

Aha effect when setting SQL instance parameters with dbatools

In the last week, I installed several SQL Servers for one customer and had to install and configure them all identically. What is closer here than to do this with a Powershell script, so I made the “effort” and the individual steps of the SQL Server configuration with dbatools to realize. I do not want to go into all the steps of the configuration in this post, but show only a part of it, which brought me a specific “aha effect”.

As part of the SQL Server configuration, the default values of “MaxDop” and “Cost Threshold for Parallelism” should be adjusted. Setting MaxDoP using Powershell or dbatools is relatively easy as there is a separate command for this, but for the “Cost Threshold for Parallelism” dbatools has a “work-around”. Unfortunately, there is not (yet) direct command for this.

Set-DbaMaxDop -SqlInstance sql2008, sql2012

This command-line sets the value of Max DOP (Maximum Degree of Parallelism) to the recommended value for SQL Server instances SQL2008 and SQL2012. Always in connection with this configuration parameter is the “Cost Threshold”, which still set to 5 by default.

All SQL Server instances at once …

To configure all SQL Server instances relatively quickly and easily, I have the command “Get-DbaRegisteredServer” (as an alias of Get-DbaRegServer) made. As preparation for this, I have created the necessary servers (here 2 servers with 3 instances each) as “Registered Server” on all servers in the SQL Server Management Studio and was then able to access it with Powershell aka dbatools.

According to dbatools documentation, this command retrieves a list of SQL Server objects stored in locally registered groups in both the Azure Data Studio and the central management server.

originell from dbatools.io - Many thanks to Chrissy

With this command and the possibility to pass the objects from the result object as a pipeline, you can do beautiful things like configuring the value for “MaxDoP” on all servers or instances in a command-line…

Get-DbaRegisteredServer | Set-DbaMaxDop

However, now to my aha effect with another command line 😉

Cost Threshold For Parallelism

As indicated above, the adjustment is only a work-around with dbatools and not with a dbatools command, this I use now “Set-DbaSpConfigure”. Of course, I could also configure the “MaxDoP” with this command, but then I have to provide for the previous determination and calculation of the respective value for MaxDoP, so determine the existing cores, these match the Best Practice and the value of one Pass variable to the set command. I set these values to 40 in 98% of all instances (unless the customer or the application wants something else), so I do not need any logic here.

Following my above logic or the documentation, I tried it with the following command line:

Get-DbaRegisteredServer | Set-DbaSpConfigure -Name 'CostThresholdForParallelism' -Value 40

This command brought me to an (at first sight) incomprehensible error (even my attempt to pass the value as a string was not successful):

WARNING: [13:02:23][Set-DbaSpConfigure] Value out of range for Server1\Instanz1 ( <-> )
WARNING: [13:02:23][Set-DbaSpConfigure] Value out of range for Server2\Instanz1 ( <-> )
WARNING: [13:02:23][Set-DbaSpConfigure] Value out of range for Server1\Instanz2 ( <-> )
WARNING: [13:02:23][Set-DbaSpConfigure] Value out of range for Server2\Instanz2 ( <-> )
WARNING: [13:02:23][Set-DbaSpConfigure] Value out of range for Server1\Instanz3 ( <-> )
WARNING: [13:02:23][Set-DbaSpConfigure] Value out of range for Server2\Instanz3 ( <-> )

Unfortunately, I have not found the reason for this, and maybe someone else can bring me closer to the phenomenon… maybe this is by design or even a “bug”…

However, I’ve been so successful with pipelining before that I’ve used that too… so let’s first find out all the SQL instances, then find the current parameter for the “Cost Threshold For Parallelism” on those instances, and then set it to the new value 40.

Get-DbaRegisteredServer | Get-DbaSpConfigure -Name 'CostThresholdForParallelism' | Set-DbaSpConfigure -Value 40
ComputerName  : Server1
InstanceName  : Instanz1
SqlInstance   : Server1\Instanz1
ConfigName    : CostThresholdForParallelism
PreviousValue : 5
NewValue      : 40

ComputerName  : Server2
InstanceName  : Instanz1
SqlInstance   : Server2\Instanz1
ConfigName    : CostThresholdForParallelism
PreviousValue : 5
NewValue      : 40

ComputerName  : Server1
InstanceName  : Instanz2
SqlInstance   : Server1\Instanz2
ConfigName    : CostThresholdForParallelism
PreviousValue : 5
NewValue      : 40

ComputerName  : Server2
InstanceName  : Instanz2
SqlInstance   : Server2\Instanz2
ConfigName    : CostThresholdForParallelism
PreviousValue : 5
NewValue      : 40

ComputerName  : Server1
InstanceName  : Instanz3
SqlInstance   : Server1\Instanz3
ConfigName    : CostThresholdForParallelism
PreviousValue : 5
NewValue      : 40

ComputerName  : Server2
InstanceName  : Instanz3
SqlInstance   : Server2\Instanz3
ConfigName    : CostThresholdForParallelism
PreviousValue : 5
NewValue      : 40

Also, already I have found out something great for myself and am more enriched by experience in dealing with dbatools!

I love this Powershell module, which allows me to customize, optimize, and automate many (almost anything!) Things around and around SQL Server. I like to use it (as you can see my other blog posts) and meanwhile with all my clients. THANKS to @Chrissy and the many other contributors who are taking the trouble to make this community tool!

Photo by Ben White on Unsplash