Anthony Nocentino shows us how we can use kubectl logs to read the SQL Server Error Log:
We can use follow flag and that will continuously write the error log to your console, similar to using tail with the -f option. If you remove the follow flag it will write the current log to your console. This can be useful in debugging failed startups or in the case below, monitoring the status of a database restore. When finished you can use CTRL+C to break out and return back to your prompt.
Read on for a brief demonstration.
The SQL Engine we are running in Azure SQL Database is the very latest version of the same engine customers run on their own servers, except we manage and update it. To update SQL Server or the underlying infrastructure (i.e. Service Fabric or the operating system), we must stop the SQL Server process. If that process hosts the primary database replica, we move the replica to another machine (requiring a failover).
During failover, the database may be offline for a second and still meet our 99.995% SLA. However, failover of the primary replica impacts workload because it aborts in-flight queries and transactions. We built features such as resumable index (re)build and accelerated database recovery to address these situations, but not all running operations are automatically resumable. It may be expensive to restart complex queries or transactions that were aborted due to an upgrade. So even though failovers are quick, we want to avoid them.
Read on to see how they do it. There’s no on-prem analogue yet, though perhaps that will come in time.
Andy Levy wraps up a Q&A series on dealing with thousands of databases:
When you started, did you know what your position was going to look like 1 month, 6 months, 1 year, 5 years from then? How accurate has that been so far?
I’ve only been at my current job for about 2 1/2 years, but I can speak to the shorter intervals. I’m going to be intentionally vague in spots here as I don’t want to disclose too much.
And if you’d like to hear Andy talk about migrating 8000 databases, Carlos Chacon and I interviewed Andy for the SQL Data Partners podcast.
Here’s a little tidbit I wanted to share regarding the Perform Volume Maintenance Tasks security setting. In the SQL Server world, this is often referred to as IFI. On more recent versions of SQL (SQL 2012 SP4 or later, I believe), you can verify if IFI is enabled or not for the database engine logon account by checking the error log.
That’s one, but click through for the technique you can easily script out.
Slava Murygin doesn’t like severity 20 errors just popping up for no good reason:
Yesterday I’ve needed to use Dedicated Administrator Connection (DAC) once in a while, and because I have all kinds of notifications in my system, I immediately got an “Severity 20” alert.
As you probably know, Severity 20 Errors “Indicate system problems and are fatal errors” (See books online: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-2017)
Even though “Severity 20” does not indicate any problems with data and belong only to a user process it is still worth to investigate the problem.
Read on to see the cause of Slava’s problem and how there’s no way to fix it in SSMS.
Andy Levy has some Q&A about dealing with large numbers of databases on a single server. Part one:
What was the most difficult challenge faced initially with a large environment and how does that challenge relate to now?
For me personally, it was just getting a handle on how to deal with this many databases because I didn’t “grow up” with the system. I walked into an environment with a lot of established tools and procedures for performing tasks and had to learn how those all fit together while also not breaking anything. You don’t want to be the person who walks in the door, says “why are you doing things like this, you should be doing it this other way” and then falls victim to hubris. If something seems unusual, there’s probably a reason for that and you need to understand the “why” before trying to change anything.
How large is the team that manages the databases? Is the knowledge shared and everyone can work on everything or do these people fill niches?
There are two of us. We each have a few specialties but we aren’t “territorial” and we try to share as much as possible. If we aren’t both directly involved in a given project, we keep each other in the loop as it progresses.
Stay tuned for part 3.
The configuration for a cluster rolling upgrade allows for mixed Windows Server versions to coexist in the same WSFC. This is NOT a deployment method. It is an upgrade method. DO NOT use this for normal use. Unfortunately, Microsoft did not put a time limit on how long you can run in this condition, so you could be stupid and do something like have a mixed Windows Server 2012 R2/2016 WSFC. Fire, ready, aim. The WSFC knows about this and you’ll see a warning with an Event ID of 1548.
Read on for a summary of what Allan has learned in doing this.
Jeff Iannucci takes us through power settings within T-SQL:
Well, not exactly, but it’s definitely like that. The default Power Setting is “Balanced” which means during periods of lower activity the clock speeds of your CPUs are reduced to conserve power and save your battery.
Apparently all Windows installations think they are on laptops. SPOILER ALERT: your database servers are probably not laptops.
Jeff has a T-SQL script to fix this. Unfortunately, it won’t fix the other power-based performance killer: power settings in BIOS.
Dennes Torres gives us a reminder of what the world was like before a new feature in SQL Server 2019:
Once upon a time a SQL Server version that hadn’t row locks. The minimal level of lock was page lock, every time you want to lock a record, an entire page was locked.
At that time we were between the devil and the deep sea: if we choose a clustered index with an ascending key we would create what was called a Hot Spot, all the records would be inserted on the same page, creating a bottleneck. On the other hand, if we create a clustered index with a non-ascending key, we would suffer from index fragmentation and page splits, having huge admin trouble to find out the correct fill factor for each index in order to support the period between the re-index job without too many page splits.
Dennes covers the specific case which this feature intends to cover and how we got there.
Thomas Rushton shows how to filter any stored procedure’s result set and uses sp_who as an example:
sp_who– useful – up to a point. Particularly when the server is busy, and you’re looking for something specific (eg to see if certain processes are out of a database before running an update)If the server is busy – don’t you wish there was a way to run something like
sp_who WHEREdbname = 'foo'Yeah. Unfortunately, it doesn’t work like that.
That doesn’t work, but Thomas shows you what does.
Kevin Feasel
2019-09-16
Administration, Containers
No Comment