SQL Server Patching – server won’t start

26. June 202226. June 2022 Björn Peters

Sometimes certain topics come together in a timely manner, in this case, there were several emails from the Data Platform MVP distribution list, several customer reports and also suitable posts in other blogs, so I would like to make a German contribution to this. 😉
It’s about a bug that seems to have been appearing in all SQL Server versions and editions in various constellations for several years. Most DBAs will very likely never encounter this problem, and probably never think of this problem beforehand. If so, here’s an explanation…

When does the error occur? – Starting position
Who does not know it? You initiate the rollout of patches on your Windows servers (including SQL Server Cumulative Updates) and wait for the finished message…. After a while, the WSUS console reports and reports that all patches have been rolled out “successfully”… the departments involved are reported that the rollout process has been successfully completed… but then production comes and reports that production is not restarting…

Or a customer answers with the words:

By the way, we are currently importing a backup because after the Windows update (incl. SQL Server updates) the service for the server (and a few other services) no longer starts…

Now the troubleshooting begins and at some point, you find out that the SQL Server has received the patch and then booted cleanly, but the SQL Server database service was not restarted cleanly… continue troubleshooting and then in the Find the corresponding messages in the SQL Server error log:

Error: 2714, Severity: 16, State: 6.
There is already an object named 'TargetServersRole' in the database.
Error: 2759, Severity: 16, State: 0.
CREATE SCHEMA failed due to previous errors.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Error: 2714, Severity: 16, State: 25.
There is already an object named 'DatabaseMailUserRole' in the database.
Error: 2759, Severity: 16, State: 0.
CREATE SCHEMA failed due to previous errors.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 2714, state 6, severity 25. 
This is a serious error condition which might interfere with regular operation and the database will be taken offline. 
If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. 
Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

All DBAs should be aware that the SQL Server also makes changes to the database structures of the system databases as part of a patch; these changes are usually carried out using T-SQL statements via an SQL file. These scripts should actually work on ALL (!) SQL servers for all customers in all forms, this is exactly where the problem is… there will always be one (or two) systems that deviate from the “standard”… be it ” by accident” or due to an administrative adjustment.

Related to “TargetServersRole” – here it is sufficient to start the SQL service manually with the trace flag 902 and to delete the schema in the “msdb => Security => Schema”.

With “DatabaseMailUserRole” it gets a little more difficult, a little more T-SQL code has to be executed here, because this role cannot be found via the SQL Server Management Studio, since this is an oprhaned user. Pinal Dave has written a good explanation and solution for this:

USE [msdb]
GO
CREATE ROLE [DatabaseMailUserRole] AUTHORIZATION [dbo]
GO
USE [msdb]
GO
ALTER AUTHORIZATION ON SCHEMA::[DatabaseMailUserRole] TO [DatabaseMailUserRole]
GO

With my last correction, the error with the TargetServersRole first appeared, then the SQL Server Engine restarted without a trace flag and ran into an error situation again, this time with the DatabaseMailUserRole, so this error/these errors can also occur one after the other. In both situations, I was able to clean up my customers quite quickly with the help of the blog posts and make the SQL Server running again within a very short time.

Thanks to Tim Wappat for solving and cleaning up the TargetServersRole
https://timwappat.info/post/2019/02/15/SQLUpgradeFailMsdb110_upgradesql (can’t be embedded so nicely 🙁 )

Thanks to Pinal Dave for solving and cleaning up the DatabaseMailUserRole

SQL SERVER – Script level upgrade for database ‘master’ failed – There is already an object named ‘DatabaseMailUserRole’ in the database

Björn Peters

Björn works in Hamburg as Senior Consultant – Microsoft Data Platform and Cloud at Kramer & Crew. He regularly participates in the PASS regional group meeting in Hamburg, the events of the PASS such as SQLSaturday and DataGrillen and organises the Azure Meetup in Hamburg. He is interested in topics such as SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.

Azure Arc-enabled Data Services – Cloud Summit 2021

23. September 202123. September 2021 Björn Peters

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

Björn Peters

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

4. September 20214. September 2021 Björn Peters

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

Björn Peters

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

11. August 202110. August 2021 Björn Peters

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.

Björn Peters

SQL Server High Availability – DBCC 2021 – Part 1

26. May 202126. May 2021 Björn Peters

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.

Björn Peters

SQL from Hamburg

my daily business with MS SQL Servern

Allgemein

SQL Server Patching – server won’t start

Azure Arc-enabled Data Services – Cloud Summit 2021

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

Introduction into Azure Arc

Azure Arc Data Services

My slides and further links

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

Why SQL-aus-HH ???

SQL Server High Availability – DBCC 2021 – Part 1

Disaster Recovery vs. High Availability

The AlwaysOn Failover-Cluster (FCI)

Related posts:

Related posts:

Introduction into Azure Arc

Azure Arc Data Services

My slides and further links

Related posts:

Why SQL-aus-HH ???

Related posts:

Disaster Recovery vs. High Availability

The AlwaysOn Failover-Cluster (FCI)

Related posts: