Press "Enter" to skip to content

Category: Administration

Working with App Locks in SQL Server

Published 2020-01-10 by Kevin Feasel

Nisarg Upadhyay explains how to use two built-in stored procedures to take and release application locks:

For example, I want to prevent users from executing the same stored procedure at the same time. To demonstrate the scenario, I have created a stored procedure named procInsertEmployees, which inserts data into the tblEmployee table. I want to make sure that no one can access the stored procedure until the stored procedure inserts the data in the tblEmpoyee table. Moreover, I have added a waitfor delay ’00:00:15’ statement to simulate the blocking for the 15 seconds.

Application locks also allow for more complicated scenarios and you can get a bit creative when assigning resources (such as combining a process name and a parent ID).

Comments closed

Delegating Authentication using Managed Service Accounts

Published 2020-01-08 by Kevin Feasel

Jamie Wick helps us solve the classic Kerberos double-hop problem:

If the Report Server service doesn’t have permission to delegate to the SQL Server, it will try to connect anonymously (step 4 in the diagram above). Which results in this login error:

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: <Client IP Address>]

Historically report server and SQL server services, that needed the ability to delegate authentication to other servers, were configured to run using an Active Directory user account. Enabling delegation on these accounts was simply a matter of setting the Trust level on the Delegation tab of the account’s properties (with Active Directory Users & Computers).

But Jamie is here to show us a better way.

Comments closed

Generating an Email List from Active Directory Users

Published 2020-01-08 by Kevin Feasel

James Livingston takes us through an interesting solution to a common problem:

If you’ve ever performed some impactful maintenance on a SQL Server, you probably notified users. If you’re great at documentation and already know exactly who to contact, this script isn’t for you. If you don’t have a user email list, this script will create it for you!

I used to manage 500 SQL Server instances and there was daily maintenance\changes going on constantly. I wrote this PowerShell script to automatically create an email list for me. This PowerShell script gathers the login information from an instance of SQL Server and then pulls their email address from Active Directory.

Read on to see the script in action.

Comments closed

Checks After a DBA Leaves

Published 2020-01-06 by Kevin Feasel

Thomas Rushton has some thoughts on a resiliency plan for DBA departures:

Transferring database ownership should be relatively straight-forward and risk-free. Except there are certain applications that depend on the database being owned by a sysadmin, or by a particular account (sa), or by their own service account, rather than checking for membership of the db_owner role… So make sure you test your changes.

Read the whole thing.

Comments closed

Negative Port Numbers in SQL Server

Published 2020-01-02 by Kevin Feasel

Jason Brimhall takes us through a bit of a mystery:

Take quick note of the port number I have circled in red. This doesn’t match the original query at all. In fact, it doesn’t come anywhere close to the actual port number. In addition, the port number shown here is a negative value. Obviously a negative port is not correct as TCP/IP ports only range from 0-65535. So what is happening here?

Read on for the answer.

Comments closed

A Diagnostic Book for SQL Server

Published 2020-01-02 by Kevin Feasel

Emanuele Meazzo has a new years’s gift for us:

Welcome to 2020! I wanted to start this year by giving to all my fellow consultants another way to troubleshoot our beloved SQL Servers; I’ve already talked about diagnostic notebooks in the past, and now, since Azure Data Studio has implemented the feature, I wanted to group them into a Diagnostic Book.

As the name implies, a jupyter book is no other than a collection of notebooks (and markdown files) that groups everything in a coherent space, with an index and navigation options alike.

I think this sort of collection of notebooks (a, uh, note-book), if put together well, makes it easier to learn a new environment and understand key problems than a big Scripts.txt file or a folder full of scripts.

Comments closed

Auditing Index Changes

Published 2019-12-27 by Kevin Feasel

Jason Brimhall gives us a solution to track index changes:

In the previous article, I showed a quick and dirty method to try and capture the indexes that may have been just created. We all know that in the hectic life of the DBA, it is easy enough to forget the indexes created more than just a few hours in the past.

In that article, I mentioned that the solution provided was far from a complete solution to the entire problem. A bigger part of the picture involves audits to know when and by whom indexes were created, altered or dropped.

Keeping track of this kind of thing is important, particularly in an environment where performance suddenly changes even though the devs totally didn’t touch anything, promise.

Comments closed

Monitoring Oracle on Azure

Published 2019-12-27 by Kevin Feasel

Kellyn Pot’vin-Gorman covers several tools which are available for working with Oracle databases in Azure:

The Oracle SQL Developer product has come a long way since it’s inception and much of that credit needs to go to the incredible team at Oracle, including those that are prevelant in the Oracle community, Like Jeff Smith, Kris Rice and Ashley Chen.  Their willingness to listen to the Oracle community and turn their needs into features has been one of the critical reasons for the product success.

Although this product is more focused towards the developer, unlike the previous three, I want to point out a few areas that hopefully will convince you there are more similarities than differences.

The shortest version of this is “the same tools as exist on-prem” but if you don’t know that answer, Kellyn’s got you covered.

Comments closed

No-Longer-Necessary Trace Flags

Published 2019-12-20 by Kevin Feasel

Monica Rathbun points out some of the trace flags which are no longer important in SQL Server:

If you have ever attended one of my performance tuning sessions, you know I tend to talk about  trace flags.  Trace Flags can help fix performance issues and some are now defaulted in later SQL Server versions. In my opinion, when a trace flag’s behavior defaulted in a version, then you should potentially put them in place within environments that do not have them implemented. Below, are a few of these particular traces flag along with Microsoft’s definition of what each trace flag does, taken straight from MS documents.  I have also included a brief commentary on each one.  As with any change, you should be sure to thoroughly test before implementing these trace flags into any production environment.

Read the whole thing, especially because at least one of them is still optional and defaulted to off (but able to change at a different scope).

Comments closed

Managing On-Prem Data Gateways

Published 2019-12-19 by Kevin Feasel

Jamie Wick has some advice for managing Azure on-premises data gateways:

When organizations become aware of the On-premises Data Gateway application, there can be an explosion in the number of gateways that are connected to the tenant. Some people will invariably install a ‘temporary’ gateway on their local computer while they investigate/develop a new PowerBI report, PowerApp or Azure Automation process. All of these ‘temporary’ gateway instances can create problems when the systems that use them are moved into production and remain connected to the ‘temporary’ gateways.

In a previous post, I wrote about how to install and use an On-premises Data Gateway to connect on-site data with Azure-based apps (PowerBI, PowerApps, etc). Now we’re going to look at how to curtail the sprawl of gateways that may be occurring and how to remove any existing gateways that are deemed to be unnecessary.

Sprawl happens, but Jamie shows you how to limit it.

Comments closed