Press "Enter" to skip to content

Category: Administration

Unlocking a Login the Bad Way

Published 2021-07-23 by Kevin Feasel

Kenneth Fisher needs bad things done fast:

We recently had an application login (SQL Server authenticated) in one of our training environments start locking out on a regular basis. I won’t go into why other than to say we did resolve it eventually. This was a major problem that escalated rather quickly up our management chain. We needed to solve it ASAP. And because of that we needed not only a long term solution but a short term one as well. The short term solution involved creating a script that unlocked the login (if it’s currently locked) and sticking it in a job that runs every 5 minutes.

Note: This is not something you should be doing in production! This creates a major security hole.

The process itself isn’t, strictly speaking, a bad idea—for example, maybe you’re testing out some integration and accidentally lock the account. The bad idea is more the script to keep doing this every few minutes.

Leave a Comment

Remote Powershell Management via the ISE

Published 2021-07-23 by Kevin Feasel

Jeffrey Hicks has a use for the Powershell ISE:

Way back before the days of PowerShell Core, or even VS Code for that matter, the PowerShell ISE was the center of my PowerShell world. I spent a lot of time finding ways to make it easier for me to use and to push it to its limits. Naturally, the PowerShell ISE doesn’t play much of a role for me these days. But that may not be true for you. In fact, one area where the ISE can be useful is with remote sessions. 

Read on to see how.

Leave a Comment

The Benefits of Kubernetes for App Hosting

Published 2021-07-21 by Kevin Feasel

Joy George Kunjikkur enumerates reasons why you might want to use Kubernetes to host applications:

I started writing this post 2-3 years back. Mainly when Apache Spark 2.3 started supporting Kubernetes (K8s) in 2018. It was obvious that Kubernetes is taking over app hosting space the same way virtual machines took over physical machines. All are expected to understand where the industry is moving and adopt. Hence I paused this post as there is nothing I need to endorse. But it’s time to resume this post and publish it.

Click through for a slew of thoughts on the topic.

Leave a Comment

Log Shipping Configuration with Powershell

Published 2021-07-20 by Kevin Feasel

Lee Markum needs to do log shipping in bulk:

I recently needed to configure log shipping for multiple databases at once as part of a migration project. I turned to PowerShell to do this.

But before we get to that part, this post assumes that you’ve done the upfront work to create shares for the backups to write to and for the backups to be copied to. This will involve providing the right permissions for the SQL Server service accounts involved in Log Shipping. If you are not familiar with this, that’s perfectly fine. Check out this article in MS Docs first.

Click through for the next steps in the process, including a well-timed Get-Help call.

Leave a Comment

SQL Server Baselines with the TIG Stack

Published 2021-07-15 by Kevin Feasel

Mark Wilkinson combines Telegraf, InfluxDB, and Grafana:

Lots of folks wonder why I would go through the trouble of building out a system when so many vendors have already solved the problem of collecting baseline metrics. The answer at the time was simple: cost. With my setup I could monitor close to 600 instances (including dev) for $3,000 USD per year. That includes data retention of ~2 years! Are there some administration costs as far as my time is concerned? Of course. In the begining things were a little rough as I learned more about InfluxDB, but once things were configured correctly the most work I’ve had to do is to expand the size of the data drive as we started collecting more metrics.

Click through for more info and check out the GitHub repo.

Leave a Comment

Attaching a Database sans Transaction Log File

Published 2021-07-12 by Kevin Feasel

Chad Callihan lost a transaction log file in a boating accident:

What if you’re moving a database to a new server by detaching and re-attaching database files and someone (not you of course) loses the log file? What if an old database needs to be brought online but the person coming to you only has an mdf file? Can you still attach the database in these scenarios? Let’s find out.

Read on for the answers.

Leave a Comment

Extending MDF Files without an Outage

Published 2021-07-09 by Kevin Feasel

David Klee creates some files:

Do you have quite large MDF files on your database? By large, I mean hundreds of gigabytes (or larger). Have you ever noticed that your SQL Server disk stall metrics for these data files are much higher than the storage latency metrics exhibited on the underlying operating system layer? It could be that your SQL Server data files are being hammered too hard and you don’t have enough data files to help the SQL Server storage engine distribute the load. We do this for tempdb, right? Why don’t we do this enough for our user databases as well? It’s easy for a brand-new database from day zero, but what about existing databases that have grown out of control with a single data file attached? Let me show you how to adjust this for existing databases without an outage!

Check it out. This is a part of database administration I’d never really thought much about, so it often ended up being a blind spot for me.

Leave a Comment

Enabling Trace Flags in SQL Server

Published 2021-07-08 by Kevin Feasel

Robert Sheldon performs some level-setting:

SQL Server includes a set of configurable options known as trace flags. You can use trace flags to set server characteristics and control different types of operations. SQL Server offers a wide range of trace flags that let you modify the platform’s default behavior to meet specific requirements. Trace flags can help you when performing such tasks as testing stored procedures, diagnosing performance issues, or debugging complex computer systems. Microsoft Support might also recommend using certain trace flags to address behavior that’s impacting specific workloads. This article explains how to enable SQL Server trace flags.

Click through for the article.

Leave a Comment

Against sp_hexadecimal and sp_help_revlogin

Published 2021-07-07 by Kevin Feasel

Andy Mallon says it’s time to give up a couple of procedures:

We recently ran into some performance problems with our login sync, which is based on sp_hexadecimal and sp_help_revlogin, the documented & recommended approach by Microsoft.

I’ve been installing & using these two procedures since I started working with SQL Server, back at the turn of the century. In the nearly two decades since, I’ve blindly installed & used these procedures, first on SQL Server 2000, and then on every version since… just because that’s the way I’ve always done it. But our recent performance problems made me rethink that, and dive in to take a look at the two procedures to see if I could do better, which made me realize, OHBOY! WE CAN DO BETTER!!

Read on to understand how.

Leave a Comment