SQL Server Patching – server won’t start

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]
CREATE ROLE [DatabaseMailUserRole] AUTHORIZATION [dbo]
USE [msdb]
ALTER AUTHORIZATION ON SCHEMA::[DatabaseMailUserRole] TO [DatabaseMailUserRole]

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

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

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.


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.

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!