A Fine Slice Of SQL Server
Jack Vamvas has a cmdlet for us:
Question: I have an SQL Server Instance – currently configured with a Dynamic Port. I’d like to change the setting from a Dynamic port configuration to a Static port configuration , using Powershell.
I want to change to a static port as we need to set up some Firewall rules , and using a static port will be much easier
How can this be done?
Click through for the answer.
Comments closedI have long advocated for the use of Extended Events. I’ve been posting all sorts of blog posts on how to implement them, how they present unique opportunities for new and interesting data, and how they do so much more than the old trace events, yet, put less of a load on the system. All of that is true, until we hit Azure SQL Database.
Now, don’t get me wrong, Extended Events are still awesome, amazing and wonderful. It’s just that, Azure SQL Database is going to force us to hop through a few hoops. I want to be up front with these… I’m trying to find the right word here, challenges? Maybe. Frustrations? Yeah, kind of. Limitations? Again, sort of, but not quite. We’ll settle on as neutral a term as possible: differences. For the moment.
Read on for the first part in an ongoing series.
Comments closedThe Hadoop in Real World team shows us the proper way to remove a node from a Hadoop cluster:
This post will list out the steps to properly remove a node from a Hadoop cluster. It is not advisable to just shut down the node abruptly.
Node exclusions should be properly recorded in a file that is referred to by the property dfs.hosts.exclude. This property doesn’t have default value so in the absence of a file location and a file, the Hadoop cluster will not exclude any nodes.
Read on for more information, including what happens if you simply turn off the node.
Comments closedJack Vamvas is hunting deadlocks:
An application using SQL Server as the database backend was experiencing some application rollbacks. I decided to investigate the SQL Server to identify any errors which could be correlated to the application timeouts experienced by the users.
I started reviewing the errors in the Extended Events system health logs, which are normally running by default on a SQL Server. They have a ton of useful information . I noticed a steady stream of deadlocks . This is the code used to create a permanent table to store the deadlock details , for review by the application team.
Click through for the script.
Comments closedDaniel Hutmacher shares some hard-earned wisdom:
I’m a complete beginner at Linux, so I should preface this post with the fact that these are my humble notes after hours of pulling my hair. It’s not really a fully-fledged how-to article, and there are lot of things I’m not covering. But I figured it may help someone out there at some point.
Also, different Linux distros and versions will behave differently, so your mileage will most likely vary.
For the purposes of this post, I’m on Red Hat Enterprise 8.3.
Note that this is using a Linux-based client, rather than talking about SQL Server on Linux.
Comments closedEric Cobb has a Powershell snippet for us:
I recently had a case where I needed to compare the Active Directory Groups of 2 users and determine which AD Groups both users were in. As it turns out, with a little prep work this is really easy to do in PowerShell.
Click through for the setup and the process.
Comments closedJack Vamvas answers a question:
I use Powershell extensively to manage SQL Server and the Windows OS. A common problem is to identify location of the Windows Recycle Bin and clear the contents down – particuarly if there is a space issue.
How can I locate the Windows Recycle Bin and clear it down?
Read on for the Powershell v5 solution as well as the solution which works for earlier versions.
Comments closedAlex Stuart hits a weird error:
Conversion/overflow errors aren’t that unusual – normally a data flow broken by some unexpected data (“no, there’s no chance that field would ever have a character in it”), or perhaps a column hitting max size (“INT will be enough for years, like, 5 years. I’ll have left the company by then”)
But that wasn’t the case here – the package and user tables involved were checked by the dev team and there was no possible overflow. I’d checked system databases for maxed-out identity columns and found nothing. Heads were scratched.
Read on for the post-head-scratch answer.
Comments closedForrest McDaniel leaves us with a head injury:
Now, I may not be an expert, but sqlmin!Spinlock sounds like…a spinlock. This thread has been spinning for over a minute, never returning to a waiting state, because something else is holding the spinlock resource.
Thankfully, helpful friends alerted me to a blog that revealed the value of an acquired spinlock “is the Windows thread ID of the owner.” Meaning I might be able to find the cause.
Read the whole thing.
Comments closedThomas Williams has a multi-part series. Part 1 is a big picture primer:
It would be nice if every problem, now and in the future, was automatically monitored by an intuitive, cheap, flexible tool that only raised the alarm when there was a real problem, at the right time, to the right people.
However, in reality, monitoring is a game of whack-a-mole, built on an ad-hoc collection of tools and technologies, requiring know-how to interpret the often puzzling situations which may be high priority – or just background noise.
Part 2 continues the big picture look:
Monitoring is here to stay. There’s never enough disk space, memory, CPU, network throughput. If they ever existed, perfect conditions don’t persist, whether because of a downstream system failing, newly-discovered security issue, change in process, extraordinary amount of load etc. And, systems grow and change to meet new requirements, so yesterday’s monitoring may not meet tomorrow’s uptime goals.
Following on from part 1, here are some further “big picture” considerations for automated monitoring for the DBA:
Part 3 gets to ideas on instrumentation:
With some of the broader ideas out of the way, if there’s no monitoring in place and I was the “accidental”/”default” DBA, there are some metrics I’d definitely want to monitor. However, there’s a ton of real-time metrics I haven’t included below like CPU & memory use, number of current connections, disk I/O as at right now. They’re probably not good candidates for “roll-your-own”-type monitoring.
I consider “alerts” in the table below to include an e-mail, SMS or dashboard item that aims to trigger a response to fix. The collection frequency especially is just a guide – closer to real-time might be desirable, but the trade-off is impact on servers and connected apps.
Check out all three posts so far in the series.
Comments closed