DallasDBAs.com

Explaining SQL Server in plain english

syndicated

IRL #8: SQL Server Down

by Leave a Comment

Problem:

My PocketDBA® client called in with a non-responsive SQL Server. Application errors, timeout, alerts firing. SQL Server box is up, and the services are running.

Background:

This is a stand-alone bare metal server located at a hosting facility with some local storage as well as NAS storage.  SQL Server 2016 Standard Edition, 8 cores, 128GB RAM.  The primary database is nearing 1TB and is has multiple filegroups (Active, Archive, Indexes, etc.).   This database and apps that touch it ARE the company.

Investigation:

  • Attempts to Login failed – SSMS, Dedicated Admin Connection (taken), SQLCMD
  • Errorlog accessed directly from the file system showed:
    • Log Backup failures
    • Multiple Slow I/O requests: “SQL Server has encountered 112 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\Company_LOG\Company_log.ldf] in database id 42”
    • Sp_whoisactive revealed a long running LOG backup reading from the H drive, writing to the N drive.
    • Windows Resource Monitor revealed Disk H Active Time (%) at 100, Disk Queue length – 0.
  • During this process the database went into suspect mode as it lost its connection to the .LDF completely. Verified the file was still in the expected location.
    • Cannot bring offline or online via any method (due to the above log backup)

 

Process:

  • KILL the Log Backup and disable the job. No change after 30 minutes for the KILL to complete, no additional traffic on this database.
  • Contact the hosting firm to open a critical support ticket to check the NAS.
  • Host confirmed cable settings, O/S settings, network settings and restarted the NAS.

 

Results:

  • After the NAS reboot, everything came up and appeared normal.
  • Brought the database online with no issues and a quick run through the Recovery process

 

Conclusions:

  • Always verify:
    • Services are running
    • Drives are available
    • Connections can be made
    • Sp_whoisactive – run it early in the process
  • Often times, SQL Server is a victim of something else.
  • NAS vs SAN…largely a budget issue but has performance implications
  • Make sure you have the phone numbers handy for anyone supporting your systems and an SLA in place.
  • High Availability is a really good idea. This was a 3 hour outage.

 

Other posts in the IRL – In Real Life series:
https://dallasdbas.com/category/sql/irl/

 

SQL Server is not a Car

by 1 Comment

Lets compare them anyway, just for fun.

  • Both have an engine
  • Both have a monitoring device (Dashboard/Activity Monitor)
  • Both need power (Gas/Electricity)
  • Both need to be told what do to (start/drive, add Databases/code)
  • Both need to be properly secured (Locked / security best practices)

SQL Server maintenance – Car maintenance;

  • Index defragmentation – Fill up on gas. Generally not needed every day, but regularly, depending on your workload/driving habits
  • Backups – Car Insurance. Sure, you can drive without insurance but what if you crash, or someone steals it (ransomware?)
  • Corruption Check – body check – look for rust, corrosion, scratches, etc. Database corruption may not always impact your app, but eventually could lead to data loss.
  • Statistics updating – air pressure in the tires. You should do this every day, but if you want to drive around on 10lbs of pressure, go for it!
  • Index and Query tuning – Oil change. Review your performance metrics regularly as workload and app functionality change. Indexes need tuning to keep the engine from locking up.

What would you add to this list of essentials, to keep your SQL Server/Car running?

Thanks for reading!

Kevin3NF

Reflections on the Software Industry

by Leave a Comment

Every now and then we like to have someone do a guest post, with a topic of their own choosing. This time, we picked Kevin Miller (LI). Kevin is a front-end application developer, and host of the Coder Coversations podcast/YouTube channel. Check out his LinkedIn profile for more! — Ed.

 

I’m a front-end developer by trade for over 7 years, and I’ve been programming for longer than that, but my actual ‘educational’ background is in Accounting. My initial desire was to be in the financial industry, but God had other plans for me, and it led to me finding a home in the software industry.

In other words, since my background is more than just tech, I’m able to see patterns that are emerging in the market and make plans to navigate around them.

Despite what many dream-sellers who are profiting off of getting you into the industry are telling you, the reality is that the software industry is very chaotic and it moves extremely quickly. If you are not carefully and actively crafting your plan of action, you can get stuck in some very undesirable circumstances.

I created this article to outline some of the pitfalls of the industry that you will have to consider so that you don’t burn out or get stuck in perpetual stagnation. Something I like to say is that I love creating software, but I don’t necessarily love the ‘software industry’. Now, let’s move on to the meat of this article.

The number one issue you are likely to face in this industry is instability coming from a variety of sources.

1) Mergers & acquisitions

If you hear these two words, you might think how cool it is going to be for your current company to ‘merge’ with another company and all of the interesting changes that will bring.

The reality is if you hear those two words, layoffs will likely be following soon due to the new singular company ‘slimlining’ and the bigger company putting their people in place in the acquired company. If you are in the acquired company, you may want to begin marketing yourself on Linked in for new opportunities.

2) Rapidly changing technology

Where did the Ruby on Rails developers go? Rails was something I learned when I first startedgetting serious about programming. Thank God I never became a ‘Rails’ developer because I’d

either be a legacy developer stuck working on old apps or I’d be forced to learn a new set of technologies to be competitive in the marketplace.

That leads me to the next point. If you are not willing to continually learn new technologies and to keep your skillset sharp, you can get stuck in this industry very fast. New technologies are coming out all the time and your current tech stack has the very real possibility of becoming old hat quickly.

Either you are willing to adapt or you get left behind.

This is why it’s so important to create a learning regiment outside of work where you deal with technologies that you DO NOT use on your job everyday. You don’t want to become a one trick  pony where you are tethered to one single framework in one slice of programming. You’re a ‘React’ developer?

What happens when React goes out of vogue and some new, hot framework is the rage? If you don’t learn something new, you will get stuck.

3) The AI revolution

AI is changing society rapidly. We have to deal with the effects of AI whether we like it or not.

Many people are terrified of AI eliminating their job prospects and for good reason.

There was a recent Hollywood strike over this same issue. Companies want to take the likeness of these actors and then have AI generate content with that likeness. This allows them to pay the actor once and then generate content into perpetuity using the image of that actor for free or for a low royalty that is significantly less than actually paying the actor.

Now, we see AI not only generating code, but also explaining how it works. The progression in AI’s capabilities is happening at a breakneck speed. Now some of you are wondering if AI will steal  your coding job away with it’s ability to do everything at light speed at a fraction of the cost.

The answer is a very real MAYBE. Let’s be real. Corporations want to save money, so if they can replace workers with AI, then they will because they don’t have to pay AI. If you are mediocre, you are gone.

Right now AI, can generate code to produce an application, but is the application doing exactly what the client wants? Some tweaking by a human will be needed. It will still take a human to tweak and adjust the results to get the application to perform as desired.

The value isn’t in just being able to cobble together a basic application. The real value is in UNDERSTANDING on a deep level how all the technologies you are using works together and how they can be optimized to effectively produce the desired results.

So as always, competence will rule the day. Now is NOT the time to ‘take it easy’ and just coast along because with the aforementioned factors, the market WILL spank you, especially as we head deeper into the economic downturn. Beyond basic coding books, you should be learning about architecture and how to design systems and you should also be working on your soft skills and leadership skills.

The upcoming economic climate will be bad for those who are not serious about their careers, but limitless opportunities abound for those who seriously pursue upgrading their skillset.

Greatly skilled technologists are also at the forefront because they have the technical expertise to utilize all of these technologies to drastically speed up their workflows at a pace the average person cannot match.

Anyhow, thanks for checking out my post. You can find me at https://coderconvos.com or https://www.linkedin.com/in/kevm254/

Test Restore Your SQL Server Databases

by Leave a Comment

Pain Point: Something bad happened and you need to restore a SQL Server database.

Pain Point you didn’t know you had: The backup files are all corrupt due a problem with the storage subsystem.

Solution: Do test restores with one line of code from DBATools.io

Set-DbatoolsInsecureConnection -SessionOnly 

Test-DbaLastBackup -SqlInstance Precision7770-1

You will need a modern version of PowerShell and the DBATools module. Also a destination for the test restores to land.

This video is not sponsored by Sean or Jen from MinionWare, they are just really nice people selling a SQL Server maintenance solution for large enterprises 🙂

Thanks for reading!

Kevin3NF

Storytelling for DBAs and Other Technical Folk 

by Leave a Comment

(for your reading pleasure, written by Jules (L). –ed)

We have all had that sinking feeling of defeat: you’ve just finished explaining a very important and highly technical thing to people who are not in IT, and you can read it in their eyes that they just don’t get it. Half of communication is on the listener’s part, it’s true. They have to pay attention, they have to ask questions, they have to care. But none of that is in your control. All that is within your control is how you present the information. And that’s where storytelling comes in. You may think you don’t need storytelling skills, but the truth is, you are a human, talking to another human, trying to convey a premise, stakes, characters, plotlines, and possible resolutions. That’s a story, and you need to know how to tell it. 

The Hook: Give them something to care about, inspiring curiosity, fear, hope, etc. in plain English. Example: “SQL02 had some problems over the weekend, but I’ve talked with some very smart folks at Microsoft, and we have a clever plan to not only fix it but keep it from happening again.” 

The Stakes: It doesn’t have to be earth-shattering, or even company-ending, for the stakes to be high. But you do have to make them CARE about what’s at stake. Emphasize what’s important about the problem, in non-technical terms. Example: “Getting our data in line with this compliance isn’t just government red tape. It demonstrates our integrity, and it a great sign of reliability to both clients and investors. Failing to do so could make an otherwise minor data breach into national news.”

The Characters: To be sure, your IT systems are characters in your narrative, and communicating their essential identity markers to your audience is key. You don’t have to tell them numbers. Tell them how to feel about those servers: “SQL02 has been unreliable, but we can change that, make it almost as good as SQL09, which as you all know is rock-solid and jet-fast.” Programs can be characters, code can be characters, and certainly your IT crew can be characters (but be nice about them, they’re real people. Bad-mouth SQL02 all you want, you can’t hurt its feelings.) 

The Plot: This is the tricky part, to summarize what may be a complicated and years-long battle with legacy code, difficult people, old hardware, etc. As simply as you can, tell them what the major conflict is, and what is holding you back. Think about it in terms of actions, not thoughts or feelings; think about what is HAPPENING. Keep it to what they need to care about right then, in this story. It may be that the Big Bad Wolf was also the problem in Three Little Pigs, but that doesn’t matter if the story you’re telling is Little Red Riding Hood. Stick to your current narrative. There’s always time to chat about backstory later. 

The Resolution: Now! Tell them what needs to happen! What you need from them! And what it will be like afterwards. Wrap this thing up and bring it home. Again, keep it simple; instead of “I need to patch SQL02 which will require a restart, which will cause x problems,” frame it as “The solution is to patch and restart SQL02, and have a plan we developed ahead of time for handling the outage, after which we will be more stable and secure.” You may or may not include “And we all lived happily ever after.”

So go! Tell the story of your server environment! Work out a plotline of advancements, with a redemption arc story for SQL02! Make your audience CARE! And may we all get more good work done, and be heroic, and ride off into the sunset in our Hondas. 

–Jules

Code That Writes Code

by Leave a Comment

“Work Smarter, not Harder”

We’ve all heard it before, pretty much any job, anywhere.

In our DBA slice of the IT world, this is very relevant to how we manage SQL Servers and the databases on them.

From scripts to check for Bad Things™, to writing efficient code.

In this video, I show a simple way to get SQL Server generate scripts you can run.  In this case I needed to run the same command against a large number of databases. Running it manually, or worse, doing it in the GUI is just not an option.  Enjoy!

The code that I will now use as a starter script:

-- Don't run this if you don't understand it 100%

Select 'ALTER Database ['+[name]+'] set Page_Verify Checksum, recovery Full, auto_shrink off;
go'
From master..sysdatabases
Where DBID > 4

Thanks for reading!

Kevin3NF

Home Blog About Privacy Policy