Logs Do Not Care About Your Feelings or Emotions

I spend a lot of time looking at logs. Over the years, I analyze more Windows Server Failover Cluster (WSFC), Windows Event, and SQL Server logs than most.

That said, I want to address something I run into from time to time. For whatever reason, people seem to think what logs say is a personal attack on them, their skills, and/or something adjacent. Let me explain.

Log Fundamentals

I always say this: “If you are reading cluster logs, you are in a bad place.” One could argue reading any log, especially on a frequent basis, means you are not where you want to be. Logs are not light summer beach reading.

Proactive monitoring often uses logs to pop up events or information. Monitoring must be tailored to your day-to-day IT requirements. Trawling through multiple logs and correlating events to determine why your AG is down is a vastly different exercise than just seeing if your CPU spikes.

A log’s job is to document what is going on for a particular widget and often includes associated things. For example, a WSFC’s log contains information about networking as it relates to the WSFC. The WSFC log does not measure network performance from the various WMI counters but it may inspire you to go look there if an issue is seen.

Log Findings

Whenever I troubleshoot issues, often part of that exercise is to report findings. Sometimes that is a call, other times a summarized e-mail. For others, it is a detailed report that not only covers the “why”, but also how to mitigate or improve the situation. In other words, it depends.

As with most things, there are good and bad ways to convey a message. Unfortunately, the baby can be very ugly even in the best of scenarios. In those situations, there is no (easy) way to sugar coat that message. The core problem is that once human beings are involved, feelings and emotions come along for the ride. Deliver findings in a humane way – leading with “you suck at your job” is not going to help matters. If your findings are meant to hurt others, rethink your position because karma comes back around.

Information presented in a log has no opinion nor is said information alternate facts. Hopefully, the log output does not contain the words “you are a moron” or “you suck” (if so, find better vendors). Some AI aside that is probably in the works somewhere (hello robot overlords!), when a log documents that the server lost connectivity to the network, that’s all it is saying.

A problem documented in a log is not a referendum on your job as an administrator or your skills. It is most likely an indicator that something must be addressed. The widget and its log do not know why the network dropped out from underneath at a given moment. Knowing the network drop happened and its timeframe points you in a direction to resolve the issue. That may mean correlating other logs and their events. After that you may investigate if this was transient/one time or something that has happened multiple times. Let the information lead you on a treasure hunt.

If any of the above resonates with you, here’s how you can address this unique challenge.

What Can You Do?

First, don’t be defensive. Many companies have walls and divisions that exist between groups or organizations. This phenomenon often leads to finger pointing in times of crisis. Chances are if you are in a server down situation, multiple people/groups will need to work together to resolve the issue. Put egos and agendas aside. Never make others jump through hoops to prove “it’s not my problem”. Everyone is on the same team – Team <insert name of company here>.

Second, remember troubleshooting is a logical exercise. You start with logs or a set of assumptions and you either prove them true or false based on the data you have. Sometimes people know right away what an issue is. This is because they have either seen it before or they have years of experience that quickly rules out noise. Do not dismiss instinct; it is a very real thing.

Example One

DBAs are often the default blame acceptors since the problem will show up in SQL Server first even if it started somewhere else in the chain. If an Always On Availability Group (AG) goes down because the underlying network drops out, it’s not SQL Server nor the WSFC or Pacemaker’s fault. The network team will most likely need to investigate what is happening and resolve the situation.

Example Two

DBAs often must go out of their way to prove performance problems (physical or virtual) are not things like a query issue. Of course, performance problems manifest themselves in SQL Server, but what is the actual cause? For example, a noisy neighbor on a virtualization host can impact another VM. The problem shows up in SQL Server, but it is something the virtualization administrator will probably need to address in some way – not the DBA. Work together, not with two middle fingers held high by each camp.

Summary

When it’s time to look at what the logs tell us, have an open mind. Sometimes it’s not our problem and we can breathe a sigh of relief. Other times the spotlight is directly shining on you. Both are ok. Deal with it and move on. Making it personal is not a winning scenario.

Have you encountered situations like this? Chime in below.

January 5, 2023

Clusters Do Not Replace a Proper Backup and Restore Strategy

I need to address a common misconception in this blog post. To paraphrase, this is what I have heard from many for the past 20+ years:

If I have a Windows Server Failover Cluster/Availability Group/Failover Cluster Instance, I do not need backups.

The Rackspace Ransomware Incident

A few weeks ago, Joey D’Antoni (Blog | Twitter) wrote an article for Redmond Magazine about the recent Rackspace ransomware incident. The bit from the article stuck out at me and the genesis of this blog post was the following quote from Josh Prewitt, Rackspace’s Chief Product Officer:

“The way the environment is architected is it takes advantage of the native clustering that’s built into Exchange. We’ve got multiple copies of everything, and Exchange is going to naturally distribute that out to other servers within the cluster. And so everything would have had at least three copies, depending on the datacenter that was in.”

System Design for Availability and Resiliency

Redundancy is one of the keys to availability/resiliency from a technology standpoint. In that aspect of his statement, Prewitt is 100% correct. Multiple copies of data is good. However, the whole solution must be properly architected end-to-end. The solution is based on quicksand if something is amiss underneath. Inside the very top layer, things may look and feel fine even when they are not. A good example is a set of virtual machines that inside are configured as an Always On Availabilty Group (AG). The AG may be functioning properly but the person responsible for its administration may have no idea the infrastructure underneath is crap. I refer to this as “the illusion of availability”.

A good example of the illusion of availability is that all of your “redundant” systems (physical or virtual) are connected to the same storage array. That storage array is now a single point of failure. This exact scenario is something I’ve seen many times in my life as a consultant. I do not know how Exchange is architected at Rackspace so I cannot comment on how it is architected nor will I speculate.

An aspect that people do not often link to availability is security. What happened to Rackspace is not unique. Ransomware is infiltrating companies, governments (local or otherwise), and more on an increasingly frequent basis for the past few years. Ransomware is a topic for another time.

Not only does proper end-to-end system design matter, but also understanding what you are implementing is important.

Clustering Exchange

There are two forms of clustering in Windows Server: network load balancer (NLB) and one for availablity called a Windows Server Failover Cluster (WSFC). A WSFC is the underpinning of both of SQL Server’s Always On features, AGs and Failover Cluster Instances (FCIs), as well as Exchange’s database availability groups (DAGs). Do not confuse Exchange DAGs with AGs or its variant, distributed AGs. Unfortunately, some call distributed AGs a DAG as well (please don’t do that).

Features work how they are designed. An AG, DAG, or FCI may not be able to fail over to another node depending on various factors. This means these features will not solve all your issues nor initiate world peace.

From the quote I used above, it seems like Prewitt may not have done a deep dive into what a DAG may or may not protect. I cannot tell you how many times I’ve had conversations with customers over the years where they assumed how AGs and/or FCIs (and by association, WSFCs) worked and I had to set them straight. Were they happy? No. I cannot say this is 100% the case with the Rackspace incident, but it feels that way to me.

Always Plan For The Worst Case Scenario

Let me be clear: features like Exchange’s DAG or SQL Server’s AGs and FCIs are great. Implement one (or more) if it is the right fit for your availability/business continuity needs. Rackspace was not incorrect in deploying an Exchange DAG.

If the event is not catastrophic, an automatic or manual failover from a DAG, AG, or FCI will get you up and running quickly. If a catatastrophic event occurs, all bets are off. At that point, the only thing that may save you is good ol’ backup and restore.

When using third party providers, check their terms of service (TOS). A TOS is a legal, not a technical, document. Make sure the TOS meets your company’s needs. For example, Rackspace’s Mail Hosting Terms documents the mail service’s service level agreement (SLA). They do make provisions if maintenance will take more than 20 minutes. However, the onus is on the customer to know the longer maintenance window is happening by checking Rackspace’s site.

Figure 1. Rackspace’s documented SLA for mail

The Bottom Line

First and foremost, tested backups and a plan to restore them is the fundamental building block of any availability (and ransomware) strategy. This is true no matter what cool feature you implemented in the platforms you use. Ensure the backups are stored elsewhere, too.

Be able to measure backup and restore success. Do you have documented recovery time objectives (RTOs) and recovery point objectives (RPOs)? If not, start the process to document them today. You may need different RTOs and RPOs depending on th type of downtime event. These goals need to balance things such as staffing, skills, the needs of the business, cost, and more.

Customers put trust in companies where they are consuming services or products. Rackspace hopefully learned from this incident and will have a better Plan B.

Finally, Another aspect which I discussed in my previous post which covered the Southwest Airlines woes over the holidays in December 2022 is that Rackspace could potentially take hits here to both reputation and their bottom line. If customers had to migrate to other mail platforms because of this incident, will they come back?

Stay safe out there!

December 29, 2022

A Lesson for IT – Don’t Be Southwest Airlines

Whether you live in the United States or not, by now you have probably heard about what is going on (or not, as the case may be) with Southwest Airlines (SWA). I was away for the holiday weekend visiting a friend and on the way back, even the employees of the other airline I was flying were talking about it and how the systems had basically melted down. As I say often, you want to be reading the news, not making the headlines and certainly not drawing the attention and ire of the US Department of Transportation (See their Twitter posts starting around December 26. Some examples: this thread, this thread, this Tweet, and this thread.).

What Happened?

From the outside looking in as someone who is a business continuity expert, this seems like it was a perfect storm of bad things converging at the same time. In the past week they have cancelled over 5,000 flights leaving passengers stranded, angry, and often in bad scenarios. Why are/were things so bad? Three quick points:

Mother Nature and the storms/weather that hit the US around the holiday and affected some of their busiest airports.
Not having a hub and spoke model for planes and people to be able to easily move the chess pieces around for things like weather events. This speaks to process and configuration as we think about it in IT.
Fragile, legacy IT systems still involved in day-to-day operations. In the case of SWA, there are systems that deals with flight and crew management. This problem is our good friend technical debt.

I feel bad for everyone involved – the customers affected, the employees who have to deal with the situation (especially the frontline ones who will feel the brunt of the customer wrath), and everyone in between.

Let me be clear: I don’t think SWA sought out to ruin people’s travel around a holiday nor was it their goal to draw the attention of the US Government. This is the reality of business and IT – things happen, often at inconvenient times. People are affected by said event. Hilarity does not ensue.

Let’s Talk Technical Debt

You’re never a hero proverbially saving $1 now when it will cost you $10 to deal with whatever that problem is later. Kicking the can down the road is a flawed, dangerous IT strategy. I’ve addressed tech debt and other related issues before (selected posts: “Technical Debt – The (Not So) Silent Crisis“, “Outages In An Increasingly Connected World“, “Security Is An Availability Problem“, and “Another Day, Another Outage“) so if you want to know the basics in more detail, read those.

SWA did upgrade some systems a few years back to give “the carrier more flexibility to improve the Customer Experience and enhance revenue performance.” Clearly the “Customer Experience” has been top notch over the past week. When you’re not flying and have to reimburse customers and figure things out, you LOSE money, not enhance revenue performance.

Availability goals should always be based in reality with real world data. How much does downtime cost the business – literally? What penalties – financial or otherwise – will be incurred? Does our solution mitigate those risks? It seems as if SWA either did not properly assess risk or worse, care. If it ain’t broke, don’t fix it, right? Wrong. According to this CNN report, SWA underinvested in its operations. Basic communication – including phone systems – were not working. Communication is crucial when the excrement hits the fan.

Andrew Watterson, SWA’s Chief Operating Officer, blamed the outdated scheduling software in a company call. The quotes from the call in the CNN article are telling.

I get that for large companies it’s hard to rip out existing systems, especially when you cannot tolerate much – if any – downtime. I spent the better part of the past 25+ years helping customers architect solutions (and will continue to do so at Pure) that perform well, are secure, and resilient/highly available. Choices have consequences and customers need to make the right ones especially when sunsetting older solutions that are very important. Always be looking forward.

How Do You Avoid Technical Debt?

I have worked with enough customers over the years to know that most people reading this blog have at least one legacy system hanging around. You know the one. It’s that system that if you look at it sideways, it acts up. That’s the one (or ones) you need a plan for sooner rather than later.

Being honest, tech debt is hard to avoid 100% of the time but you need to try. Be proactive, not reactive. Know when things like SQL Server, Windows Server, and other third party software are out of support. There are many nuances to dealing with technical debt which also includes ensuring that all staff has training and their skills are modernized. Technical debt is a people issue, too.

Know your core functionality and what you need to achieve. Getting lost in whiz-bang, fancy features and analytics does not mean a hill of beans if your company’s core goals are not met. In the case of SWA, they cannot move people from Point A to Point B. Let’s not even get into the potential hit to their reputation and bottom line that comes along with a failure of this magnitude.

Don’t become the next headline. Planning for obsolescence as soon as a system is brought online is really the exercise that needs to happen. If you do not bake obsolescence in as a feature from day one, you may be the next SWA or worse; events like this can take the business out permanently, too. Unemployment is not the goal.

What are your thoughts? Have you been in similar situations and if so, how did you get past the issue(s)?

December 20, 2022

The Only Constant Is Change

I hope everyone is having a good holiday season.

Since the beginning of my career in the 1990s, one tenet is still true: the only constant is change. Many of us joke that X number of years of experience is not the same year of experience X times. Careers are a journey, not a destination. You learn. You grow. Some things in life and work are cyclical and yes, some of the challenges we face are the same or similar. Heaven knows “everything old is new again” is true when you have been around as long as I have, but how we approach and deal with challenges is often completely different as times and technology has changed.

When I went independent in 2007 with Megahirtz and later also founded SQLHA, each was and is a unique experience. I’m proud of both companies. In 2007, I had been a consultant for more than seven years as an employee at both Microsoft and Avanade. By the end, I was basically doing my own business development at that point and decided it was “now or never”. In my mid-30s, I took a leap of faith to invest in myself. What I thought might be a few years at most turned into over 15. I exceeded my expectations and smile when I think of the success I’ve achieved.

This year, I took another leap of faith. I made the difficult decision to step back from consulting to become an employee again. It is never an easy decision to make when you have a company, customers, and a business partner. Once again, I’m betting on myself to succeed in a new chapter of my life.

What Will I Be Doing?

My role is a Technical Evangelist for FlashArray at Pure Storage. My day-to-day tasks speak to everything I bring to the table – not just my technical SQL Server expertise. Being able to use the depth and breadth of my skills was important to me wherever I landed as I’m more than a SQL Server availability guy. I’m thrilled to be joining Pure and so far, they seem happy I’m there, too.

How Did It Happen?

The process was organic. Anthony Nocentino (Blog | Twitter) and I had a conversation at an event this fall. The topic of me looking was not an agenda item; it just came up naturally. Turns out it was perfect timing for everyone involved. I owe Anthony a huge thank you. Never underestimate the power of networking at events. At one point I may blog about my job search and all its ups and downs.

What About SQLHA?

Max and I talked with customers before I published this blog post. I strive to be open and honest in my dealings with people. They know I’m stepping back from consulting and the day-to-day management of SQLHA’s business. Max continues to work with our existing customers. There’s a bit of a transition period that I cleared that with Pure.

What About My Relationship with Max?

I’ve known Max for over 20 years going back to when we were both blue badge employees at Microsoft. We are still good friends and that has not changed. There was no need to go through this process with two fingers held high in the air. I did not find the job at Pure and spring it on him. It was important Max was on board before I started to look for the right position as this impacted him, too.

What Changes?

Honestly, not much. I’ll still be that business continuity, infrastructure, virtualization, cloud, and more pedant (and smartphone curmudgeon) you all know and love – and sometimes love to hate. I’ll continue to speak at events including SQLBits in Wales in March 2023 where I have a Training Day “Architecting Scalable, Available, and Manageable SQL Server Deployments” on Wednesday, March 16. Not all of my speaking will be under the Pure banner. My book and public training are still on the radar map for 2023. SQLHA (the website and the company) will be around. SQLHA.com has been my Internet home for over 15 years and will continue to be. It will be the home to my blog and I’ll probably post the occasional video to the SQLHA YouTube channel. You can also find me on Twitter (for now, at least), LinkedIn, Mastodon (@SQLHA@techhub.social), and Counter Social (@SQLHA@counter.social).

I wish everyone the best as the curtain draws on 2022, and hope 2023 is even brighter for everyone.

September 13, 2022

Contained SQL Server Agent Jobs in SQL Server 2022

This blog post is part of T-SQL Tuesday #154.

I previously wrote about Contained AGs in SQL Server 2022 and demonstrated how to create a contained login. In this blog post, I’m going to talk about contained SQL Server Agent jobs because just like logins, they are a bit confusing from an administative standpoint in their current pre-release implementation (this blog post was written using SQL Server 2022 RC0 using SSMS 19 Preview 3).

Create a Contained SQL Server Agent Job

As of the writing of this blog, contained SQL Server Agent jobs can only be created using Transact-SQL. If this changes, I will update the blog post accordingly.

Remember per my other blog post, the key is to do this in context of the contained AG. An example is shown in Figure 1.

Figure 1. In context of the contained AG.

The job I created inserts a random character every minute into the table TestTbl.

USE ContainedRobotoAG_msdb;
GO

EXEC sp_add_job
   @job_name = N'Insert Value Into ContainedRobotoAG TestTbl'; 
GO

EXEC sp_add_jobstep
   @job_name = N'Insert Value Into ContainedRobotoAG TestTbl',
   @step_name = N'Insert Value'
   @step_id = 1,
   @subsystem = 'TSQL',
   @database_name = N'ContainedAGDB1',
   @command = N'INSERT INTO TestTbl (TestVal) VALUES (CHAR (ROUND(RAND() * 93 + 33,0)))',
   @on_success_action = 1,
   @on_fail_action = 2,
   @retry_attempts = 5,
   @retry_interval = 5;
GO

DECLARE @schedule_id int;
EXEC sp_add_schedule
   @schedule_name = N'Every Minute',
   @enabled = 1,
   @freq_type = 4,
   @freq_interval = 1,
   @freq_subday_type = 4,
   @freq_subday_interval = 1,
   @freq_relative_interval = 0,
   @freq_recurrence_factor = 1,
   @active_start_date = 20220913,
   @active_end_date = 99991231,
   @active_start_time = 0,
   @active_end_time = 235959,
   @schedule_id = @schedule_id OUTPUT
SELECT @schedule_id;
GO

EXEC sp_attach_schedule
   @job_name = N'Insert Value Into ContainedRobotoAG TestTbl',
   @schedule_name = N'Every Minute';
GO

EXEC sp_add_jobserver
   @job_name = N'Insert Value Into ContainedRobotoAG TestTbl';
GO

The Challenge of Contained SQL Server Agent Jobs

Just like with logins, the SQL Server Agent job is not visible in SSMS. Like creation right now, it is just Transact-SQL. An example is shown in Figure 2.

Figure 2. Contained SQL Server Agent jobThis has some nasty side effects.

Someone could create a duplicate job at the instance (not AG) level. I tested this – see Figure 3. It’s not only doable, but the possibilities of how that can go wrong is endless.
Figure 3. Duplicate jobs – one in the contained AG, one at the instance level
The only way right now to see if a contained Job ran successfully (one time or recurring) is via T-SQL. See Figure 4 for an example. Many admins have scripts as part of their processes (hello DevOps folks!), but I know just as many or more who rely on SSMS to see the status of a job. What if a backup job created in context of a contained AG is failing and no one catches it because it’s not visible?
Figure 4. Job status
Somewhat associated with #1, I see a potential security hole here. What if someone creates a nefarious SQL Server Agent job in a contained database which does nasty things and no one knows about it and what it is doing? The implications – especially because Agent jobs can be more than just T-SQL – are not good.

The Bottom Line

I still like contained AGs, but this post highlights the biggest blind spot and challenge with them: administration. My hope is that SSMS is updated by the time SQL Server 2022 is released to make them not only easier to create and manage, but make SQL Server more secure.

12 3 4 Next ›Last »

Log Fundamentals

Log Findings

What Can You Do?

Example One

Example Two

Summary

The Rackspace Ransomware Incident

System Design for Availability and Resiliency

Clustering Exchange

Always Plan For The Worst Case Scenario

The Bottom Line

What Happened?

Let’s Talk Technical Debt

How Do You Avoid Technical Debt?

What Will I Be Doing?

How Did It Happen?

What About SQLHA?

What About My Relationship with Max?

What Changes?

Create a Contained SQL Server Agent Job

The Challenge of Contained SQL Server Agent Jobs

The Bottom Line

Popular Posts

Categories