Press "Enter" to skip to content

Category: Administration

Auto-Failover Groups and Grace Periods

Published 2021-04-02 by Kevin Feasel

Taiob 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 closed

VMware In-Guest Time Synchronization

Published 2021-04-02 by Kevin Feasel

David 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 closed

Using Azure Functions to Tag Resources

Published 2021-04-01 by Kevin Feasel

Jess 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 closed

Finding Securables for a Database Role

Published 2021-03-30 by Kevin Feasel

Jack 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

Managing tempdb Growth

Published 2021-03-24 by Kevin Feasel

Monica Rathbun shares part two in a series:

As you learned in part one of this Mastering TempDB series, TempDB is a global resource used for many operations within SQL Server. You create and allocate temporary user objects such as temporary tables and table variables, rebuild indexes with SORT_IN_TEMPDB=ON, use it for version stores (RCSI), internal objects (worktables, spools, group by, order by) and even DBCC CHECKDB just to name a few. All these operations require space to be allocated in the TempDB database. At times, these operations can result in TempDB growing rapidly, which, in turn, can fill up the file system and cause failures. In this article, you will learn how to fix an overgrown TempDB file that has resulted in it running out of space.

It beats my answer, which is to rip the server out of the rack and chuck it in the ocean.

Comments closed

More MSDB Cleaning

Published 2021-03-23 by Kevin Feasel

Just when you thought you were done with cleaning MSDB, Eitan Blumin brings you back in for more:

As part of its regular, ongoing, day-to-day activities, your SQL Server instance would naturally collect historical data about its automated operations. If left unchecked, this historical data could pile up, leading to wasted storage space, performance hits, and even worse issues.

MSDB would obviously be collecting data about the SQL Agent job executions. But there are also a few other types of historical data that needs to be cleaned up once in a while.

Click through for part 2 in the series, which covers a half-dozen more things.

Comments closed

Viewing SQL Server Logs on Linux

Published 2021-03-19 by Kevin Feasel

Jack Vamvas answers a question:

Question: How can I view SQL Server Logging on Linux ?

Answer: SQL Server on  Windows – logs details into SQL Server Error logs and Application Logs. Windows event viewer details are available in the Windows Event Logs via the event viewer or Powershell Get-EventLog

The Windows answer is pretty easy for SQL Server DBAs, as we’ve lived in it for so long. Click through for the Linux answer.

Comments closed

The DBA Role Over Time

Published 2021-03-18 by Kevin Feasel

Brent Ozar has started a retrospective. The first post covers “What would you say you do here?”:

When the database goes down, the business stops. If you’re selling things online, and your web site goes down, that’s an emergency. Even if it’s not down – if it just slows down – that can be an emergency too. DBAs help prevent that problem.

When someone accidentally deletes data, the business can stop. Unfortunately, in all too many companies, there are too many folks with access to that data – and those folks are often untrained. They’re just human, and they make mistakes. Database administrators help recover from that problem.

The second post looks at the lack of progress in many areas:

When I started working with databases, we had to:

– Provision the right CPUs and memory
– Provision the right storage throughput and size
– Install the database
– Configure the database
– Protect it – which sounds easy, but that also includes designing the right high availability and disaster recovery solution
– Design the right tables, and put indexes on them based on how we wanted to query the data
– Write fast, accurate queries to load & retrieve data
– Troubleshoot the whole thing when it went wrong

Today, decades later…yeah.

We’re still doing all of that stuff. It’s just that we’re cave-people using rocks for tools instead of banging on things by hand.

I’ve got some thoughts on this but they won’t fit on this stamp. I’ll have to put them together some other day.

Comments closed

Tracking Azure Resources with Tags

Published 2021-03-18 by Kevin Feasel

Jess Pomfret explains the value of tags:

One of the vital parts of this learning and experimenting needs to be cleaning up after myself.  We all know the risks of leaving things running in Azure- it’s likely to drain your training budget pretty quickly.  To be fair, this is also a good lesson for real world scenarios. Getting used to turning off or scaling down resources based on need is a good way to reduce your Azure spend.

This brings me to one morning last week. I logged in to the portal and got a pop up that my credit was down to under $5, which is not what I was expecting. I started looking around and wondering what I’d left running – it isn’t always easy to spot though.

Read on to see how tags can help with this, as well as other forms of cloud governance. If you remember to set them, that is.

Comments closed