Press "Enter" to skip to content

Category: Administration

Automate Availability Group Failover for SSISDB 2012 and 2014

Published 2021-06-01 by Kevin Feasel

Alex Stuart shows how to fail over SSISDB in SQL Server 2012 or 2014:

Hopefully not many people are still configuring SSIS instances on SQL 2012 or 2014 – especially HA instances – but if you are, this post is for you.

If you’re running SQL Server 2016 or above, having the SSIS catalog function correctly in an AG is supported by built-in functionality to manage the DMK (database master key). In 2012/2014 however there is no such support. Without intervention this makes SSISDB unable to be opened after a failover, because the DMK isn’t open – leading to errors such as “Please create a master key in the database or open the master key in the session before performing this operation.”

Read on to see how to resolve this error, and then how to do this automatically.

Comments closed

Determining the xp_cmdshell User

Published 2021-05-26 by Kevin Feasel

Kenneth Fisher asks the important Stockdale questions (Who am I? What am I doing here?):

It works when I run it this way™ but not when I run it through xp_cmdshell!

It’s a permissions issue. When you run xp_cmdshell you are running under the ??? account.

Little bit more detail. The extended stored procedure xp_cmdshell creates a windows command shell. This shell has to be run under a windows/active directory account. Obviously you can’t get access to a windows resource (a directory for example) using a SQL Server login. The trick is to be able to tell them what account xp_cmdshell is using within that shell. There are two possibilities here.

Read on to learn about those two possibilities.

Comments closed

Testing TLS Protocol Versions with cURL

Published 2021-05-25 by Kevin Feasel

Anthony Nocentino has a tip for us:

Ever need to set your web server a specific protocol version of TLS for web servers and need a quick way to test that out to confirm? Let’s check out how to use curl to go just that.

This code here uses curl with the parameters --tlsv1.1 --tls-max 1.1, which will force the max TLS protocol version to 1.1. Using the --verbose parameter gives you the ability to see the TLS handshake and get the output sent to standard out.

Also, check the comments for a very helpful addendum.

I should note that cURL is built into Windows 10 as of v1803, and it’s been a part of MacOS and Linux for a long, long time.

Comments closed

Network Configuration with Powershell

Published 2021-05-25 by Kevin Feasel

Patrick Gruenauer shows how to configure network settings on a Windows machine using Powershell:

Today I would like to give a tutorial for a basic network configuration on Windows systems using PowerShell. We start with a fresh installation of a Windows operating system.

Read on for a fairly simple example, one which lays out the foundation for more complicated scenarios such as configuring networking settings for an Availability Group.

Comments closed

Ignoring Backups in the SQL Server Error Log

Published 2021-05-21 by Kevin Feasel

Garry Bargsley has a solution to an annoyance:

Whether you are new to SQL Server or a seasoned veteran, you will notice odd behavior in the SQL Server Error Log. When a database backup is performed, an entry is put into the SQL Error Log. The SQL Server team decided to log successful backup messages to the Error Log. If you ask most technology professionals, you will find that logging successful events are not really a common occurrence.  This behavior causes a bloated Error Log that can make it hard to find what you need quickly.

Luckily, that same SQL Server team built in a solution to this situation.

Read on to see what the solution is, as well as how to use it.

Comments closed

sqltop — SQL Server Process Viewer

Published 2021-05-21 by Kevin Feasel

Mark Wilkinson has a big announcement:

Hey folks! I’m proud to announce the first open source release of my sqltop tool! sqltop is an interactive command-line based tool to view active sessions on a SQL Server instance. In this post I’ll talk about why I wrote the tool, why I chose to write it in PowerShell, and walk through some of the challenges I faced during development.

I’ve had a chance to see this in action and it’s really cool. I’m glad Mark was able to get this open-sourced, so go check it out.

Comments closed

Running SQL Server Containers from Scratch

Published 2021-05-18 by Kevin Feasel

Andrew Pruski tells us there is no spoon:

I’ve been interested (obsessed?) with running SQL Server in containers for a while now, ever since I saw how quick and easy it was to spin one up. That interest has led me down some rabbit holes for the last few years as I’ve been digging into exactly how containers work.

The weirdest concept I had to get my head around was that containers aren’t actually a thing.

Containers are just processes running on a host that implement a set of Linux constructs in order to achieve isolation.

So if we know what constructs are used…shouldn’t we be able to build our own container from scratch?

Read on as Andrew breaks out the three necessary constructs and dives into it.

Comments closed

The Basics of Finding Blocking

Published 2021-05-17 by Kevin Feasel

Alex Stuart has a way to find blocked processes:

So we need monitoring and alerting on it. Enterprise monitoring tools can do this, and do it well – but if you don’t have one, or don’t have enough licenses for your entire estate, you’ll need to roll your own. (OK, or copy someone else’s if you don’t need the learnin’). This post will demonstrate a basic method for detecting blocking and alerting based on a given threshold.

Read on for the process.

Comments closed

Types of Memory Contention

Published 2021-05-14 by Kevin Feasel

Erik Darling is overdrawn at the memory bank (which was, sadly, not a very good MST3K episode):

Whomever decided to give “memory bank” its moniker was wise beyond their years, or maybe they just made a very apt observation: all memory is on loan.

Even in the context we’ll be talking about, when SQL Server has lock pages in memory enabled, the pages that are locked in memory may not have permanent residency.

If your SQL Server doesn’t have enough memory, or if various workload elements are untuned, you may hit one of these scenarios:

There are three of them, which is really that there are two of them but they can join forces in an effort to make your life a pain.

Comments closed