Press "Enter" to skip to content

Curated SQL Posts

Automated File Cleanup with Powershell

Published 2021-04-05 by Kevin Feasel

Jeffrey Hicks does some spring cleaning:

Springtime is approaching in North America. Where I live, the snow has finally melted and we have blue skies with warmer temperatures. Of course, this means Spring Cleaning. Time to clear out the winter debris and spruce up the house. For me, this is also a good time for some computing housecleaning as well. I don’t know about your Windows environment, but I tend to accumulate a lot of junk. Most of the time I don’t see it, but I know it’s there. While the junk normally doesn’t have a negative impact, I think mentally, I like clearing things out and tidying up. So I pulled out some older PowerShell code, freshened it up, and now I have a set of tools for clearing out junk and temporary folders. Let me show you what I came up with.

Click through for the scripts.

Comments closed

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

What to Do if a Database Isn’t Synchronizing

Published 2021-04-02 by Kevin Feasel

Lee Markup has some advice:

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

Fixed Server and Database Roles in SQL Server

Published 2021-04-02 by Kevin Feasel

Greg Larsen takes us through the built-in set of server and database roles in SQL Server:

Managing Security for SQL Server is extremely important. As a DBA or security administrator, you need to provide access for logins and database users to resources within SQL Server. SQL Server has both server and database resources where access might need to be granted. Access to these resources can be granted to either individual logins or database users or can be granted to roles, for which logins or database users can be members. Granting access via a role is known as role-based security.

There are two types of roles: fixed or user-defined. In this article, I will discuss the different fixed server and database roles provided with SQL Server and how these roles can be used to support role-based security to simplify providing access to the different SQL Server resources. In a future article, I will discuss user-defined server and database roles.

Click through for more information. Or just give everybody sysadmin. I mean, that’s probably fine, right?

Comments closed

Power Query and the First Day of the Week

Published 2021-04-02 by Kevin Feasel

Ed Hansberry decrees that henceforth, Thursday shall be the first day of the week:

By default, Power BI starts its week on Sunday, but that doesn’t work for every situation. Some companies prefer their week start on Monday, and I recently helped someone that needed their week to start on Thursday. Fortunately, by tweaking your date table in Power Query, you can make this change with no calculations at all.

Click through to see how.

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

Retrieving Counts of Cosmos DB Collections

Published 2021-04-01 by Kevin Feasel

Manoj Pandey shows how you can retrieve counts of records in Cosmos DB using the .NET client:

Here in this post we will use C# .net code (for beginners like me) to see how to:
1. Connect to a Cosmos DB instance
2. Get list of all Databases in a Cosmos DB
3. Iterate through all the Databases and get the list of all Collections (or Tables)
4. Get COUNT of all documents/items (or records) in these Collections

Click through to see how.

Comments closed

Building a Better Random

Published 2021-04-01 by Kevin Feasel

David Fowler wants to generate a random number:

You see the problem? Well in the post that I mentioned, I adressed this issue and offered a solution using CHECKSUM() and NEWID().

That’s ok but can be a bit cumbersome. I recently stumbled across a function that I hadn’t realised was there before (and of course this is where you all tell me, we’ve known about that all the time), CRYPT_GEN_RANDOM().

My expectation with this was that CRYPT_GEN_RANDOM() would generate according to a uniform distribution. Performing a simple experiment with about 4 million entries against random numbers 0-255 (1 byte), I can confirm that this does approximate a uniform distribution.

Comments closed

When You Can’t Win for Trying: SQL Agent Failures

Published 2021-04-01 by Kevin Feasel

Garry Bargsley troubleshoots a strange issue:

What are your troubleshooting steps when a job failure is reported?

1. Open SQL Agent Job History for the failed job
2. Look at the SQL Server Log
3. This was an Ola job, so look at the CommandLog table
4. Look at the text log file stored on the file system
5. Open the job step and get the code being executed and run in a new query window

Now that you and I have performed the same steps and found no smoking gun, where do you go next?

But what happens when all of your indicators look fine, yet the job is still failing? Read on for one possible answer.

Comments closed