A Fine Slice Of SQL Server
Forrest 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 closedAmit Khandelwal has some good news for us:
We are happy to announce the official production support and availability of SQL Server 2019 package native to Ubuntu 20.04 distribution starting with CU 10. You can read more about the SQL Server 2019 CU 10 release here.
Also click through for some good news around Python 2 dependencies going away.
Comments closedChris Adkin continues a series:
If you have been following this series, you should have:
– a basic understanding of Terraform
– a Kubernetes cluster that you can connect to using kubectl
– a basic understanding of Kubernetes services
– a working metalLB load balancer
– a basic understanding of how storage works in the world of Kubernetes
– a Kubernetes storage solution in the form of PX Store, alternatively you can use any solution (for the purposes of this series) which supports persistent volumes, however to use the backup solution in part 9 of the series you will need to use something that supports CSI
From here, Chris explains the importance of the data controller and then deploys one.
Comments closedI currently manage 5 AlwaysOn Availability Groups. Two are on SQL Server 2014 and overdue for an upgrade, while three of them are on SQL Server 2017. From time to time I have run into a couple of different situations that I needed to troubleshoot and I want to tell you where to look and what to check on in these scenarios. I can’t possibly tell you about everything that could go wrong, but I can tell you about my experience with AlwaysOn Availability Groups and let you decide if that experience helps you or not.
Click through for more.
Comments closedTaiob Ali clears up some misunderstanding:
The auto-failover groups feature for the Azure SQL database can be configured with an automatic failover policy. Azure triggers failover after the failure is detected and the grace period has expired. Grace period is determined by a setting called ‘GracePeriodWithDataLossHours’ that cannot be set under one hour. Why is it not allowed to set a time which is less than an hour? Can your business tolerate the application be down for that period? Should your turn off Auto Fail-over and set it to manual?
I noticed a lot of confusion around this setting, including my own. Some of the confusion is due to a lack of clarity in the documentation. I checked with the Microsoft Azure SQL team, and they are actively working on clarifying some of the questions I raised.
I want to thank Dimitri Furman and Roberto Bustos from the Azure SQL Team for clarifying some of my confusion that I will share here.
Read on for a Q&A style explanation of auto-failover and grace periods.
Comments closedDavid Klee reviews a product update:
I just found out that VMware has updated their in-guest time synchronization with the 7.0 Update 1 release. Previously, we had to manually disable some of the advanced time synchronization ‘features’ that didn’t adhere to the front-end GUI option that said to not synchronize the guest time with the host. For most VMs, it is not that big of a deal, but for SQL Servers running in a highly available configuration, this act could break your availability solution.
Click through to see what has changed in the product.
Comments closedJess Pomfret shows off an interesting way of using Azure Functions to apply tags to resources:
In part one I discussed how useful Azure tags can be, and specifically about how adding a ‘dateCreated’ tag can help you keep track of your resources, and how to find resources with certain tags using PowerShell. Part 2 and 3 are based around the fact that adding the ‘dateCreated’ tag is a great idea, but relying on a human to remember to add it is less than ideal. In part 2 we looked at using Azure Policy to automatically add the tag. Today’s post will cover another option using Azure Functions.
Azure Functions gives us a way of running serverless code, written in a number of different languages, triggered by specific events or timings. Looking through the documentation there are many use cases from processing files to analysing IoT workstreams. Our use case is to run a PowerShell script that tags any resources that are missing the ‘dateCreated’.
Click through to see how.
Comments closedRandolph West reports on a change in SQL Server 2014:
I’ve been doing SQLskills training recently, and Paul Randal (blog | Twitter) reminded our class that zeroing out a transaction log file does not use zeroes (
0x00). Well, not since SQL Server 2014 at any rate.
Click through to see what SQL Server uses instead when “zeroing” out a log file.
Comments closedJack Vamvas answers a question:
Question: I need to extract the securables for a user created SQL Server database role. For example , the Explicit Permissions including the Permission,Grantor,Grant,With Grant and Deny. And also the Securables – Schema,Name,Type
How can I get this information via t-sql?
The only downside in Jack’s query is that it enumerates the securables for the principal. But if the principal is part of an Active Directory group (or multiple groups), this becomes more difficult.
Comments closed