Press "Enter" to skip to content

Category: Administration

Setting a Default Database for SQL Server Logins

Published 2020-12-01 by Kevin Feasel

Adrian Buckman shows us an issue with using a database other than master for a SQL Server login’s default:

This is one of them little options that I see which quite often gets little consideration or gets set to a user database without consideration of what the consequences may be if that DB becomes unavailable. There are going to be situations where setting a default other than master is essential and there are going to be situations where leaving as master suits best and this comes down to the individual requirements of each login, Recently I had to fix an issue with user connectivity for a single login, the user was getting failed connections when trying to connect to the SQL server when trying to access one of their legacy databases , everything appeared fine – User account was enabled the password hadn’t been changed and was therefore correct, the database they were trying to access was up and accessible but the SQL error log highlighted the real issue.

Click through for more details.

Comments closed

Kusto Queries in Azure Data Studio Notebooks

Published 2020-11-30 by Kevin Feasel

Julie Koesmarno shows off the Kusto Query Language magic in Azure Data Studio notebooks:

To do this, you’ll need to ensure that you have Kqlmagic installed. See Install and set up Kqlmagic in a notebook. Then in a notebook, you can load Kqlmagic with %reload_ext Kqlmagic in a code cell.

The next step is then in a new code cell, you can start connecting to a Log Analytics workspace. There are three ways to do so (roughly – as I’m also learning in this space too):

1. Using Azure Active Directory Device Login authentication.
2. Using Az CLI login
3. Using Client Secret

Read on for one example using Azure AD authentication.

Comments closed

Thinking about Virtual Log Files

Published 2020-11-25 by Kevin Feasel

Julie Behrens, via Kevin Hill, covers the concept of virtual log files:

It is especially evident there is an issue with VLFs when SQL Server takes a long time to recover from a restart. Other symptoms may be slowness with autogrowth, log shipping, replication, and general transactional slowness. Anything that touches the log file, in other words.

Read on to understand how to figure out if you have a problem with virtual log file counts and a resolution.

Comments closed

Monitoring Availability Groups via DMV

Published 2020-11-20 by Kevin Feasel

Rajendra Gupta continues a series on Availability Groups:

In the previous article, Explore dynamic management views for monitoring SQL Server Always On Availability Groups, we explored the DMV’s for the Availability group for Windows Failover Clusters.

This article takes a further step and covers useful DMV related to availability replica and databases. Let’s start our journey with this article.

Click through for the article.

Comments closed

Target Groups in Elastic Jobs

Published 2020-11-16 by Kevin Feasel

Reitse Eskens shares some more information about elastic jobs in Azure:

In one of my previous blogs, I wrote about how to create an elastic job agent when you need the SQL Agent functionality on Azure. You can read that one here.

This morning, I needed a job to update the stats on a database, but on just one database within the “instance” on Azure. But my first group contained all the databases, and the Ola Hallengren script isn’t available on all databases and the credential I’m using to execute the jobs doesn’t have access to all the databases.

Read on to learn how Reitse solved the problem.

Comments closed

Using Linked Servers to Scan SQL Server Instances

Published 2020-11-13 by Kevin Feasel

Kenneth Fisher has a plan:

I recently did a post on testing a linked server where I said I would explain why I wanted to make the test. Basically I needed to scan a few hundred instance names and do the following

– Check if the instance is one we have access to or even exists? If not make a note of the error so we can tell the difference.
– Collect information like instance size (total size of all databases), CPU count, memory count etc.
– Collect a list of database names on the instance, their status, size, etc.

I’m of mixed feelings about this. On the one side, I appreciate that it’s pretty simple and does what is promised: a quick and dirty method for reaching a SQL Server instance (or something which might be one). On the other hand, it feels like this is trying to be the combination of two or three things which do the job better:

  • Central Management Server, for managing the SQL Server instances available to you. This is where you’d put confirmed instances and start running checks like Kenneth wants.
  • Nmap, for determining whether there are servers which are not part of your CMS listing. Nmap can check for availability on port 1433 and see if that’s a SQL Server instance on there, as well as hitting the SQL Browser service for instances not on 1433 (and assuming you leave the Browser on).
  • A Powershell script to combine these together, or at least a script in some language which does a decent job with looping. Even better if it offers parallelism.
Comments closed

Monitoring Kafka Metrics with Prometheus and Grafana

Published 2020-11-12 by Kevin Feasel

Murat Derman shows how you can use Prometheus and Grafana to track vital measures on an Apache Kafka cluster:

You can add  scrape_interval parameter in your configuration by default it is  every 1 minute  scrape_interval: 5s

Prometheus has its own query language called promql. You can learn more about this language from this here https://prometheus.io/docs/prometheus/latest/querying/basics/ 

There are lot of metrics you can define for Kafka. I will mention  a few  of them in this article

Read on for a breakdown of some of these measures.

Comments closed

Infrastructure Notes for RMDBS on Azure VMs

Published 2020-11-11 by Kevin Feasel

Kellyn Pot’vin-Gorman takes a look at some of the hardware choices you have in Azure, focusing on what works for relational database management systems:

The truth is, its often a combination of database and infrastructure issues that are the cause.  Although many of you may want me to dig into database performance data, I’m actually going to first focus on infrastructure, as it’s the area that most aren’t privy to for Oracle, or for that matter, any database on Azure IaaS.

The topic of infrastructure is an essential one for any database running in IaaS and even more so VMs on Linux, which can be a bit foreign for the Microsoft data specialist.  Yes, this may be intimidating when doing the shift to Linux and understanding some of the nuances to running a database on Linux, but understanding the infrastructure is a key to removing it from the scenario.  Hopefully these tips will assist you, no matter if you’re running Oracle, (MySQL, PostgreSQL or SQL Server) on Linux VMs on Azure IaaS.

Click through for some guidance on the topic.

Comments closed

Exporting SQL Server Configurations

Published 2020-11-11 by Kevin Feasel

Garry Bargsley shows how you can export configuration settings for your SQL Server instances:

Have you ever deleted a login by mistake from a hastily typed TSQL script or dropped a list of logins because the “Business” said they are not used anymore? Have you ever made a change to a SQL Server Agent job and then it failed on the next execution. What about that time you changed the Database Mail profile on all of your servers and left your personal account in the script instead of the DBA distribution list.

While each of these examples is not life-threatening, they will strike fear in you depending on how prepared you are to recover the items in question.

This is the type of thing you’d want to store in source control, too. That way, you have a record of changes over time.

Comments closed