Press "Enter" to skip to content

Category: Administration

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

An Intro to Backup Strategy with SQL Server

Published 2020-11-10 by Kevin Feasel

Pamela Mooney takes us through backup and restoration as part of a DBA in training series:

A DBA’s job is getting the right data to the right people as quickly as possible.

Consider that first part – getting the right data. How do you do that? By having the data in the first place. The best way to ensure that you have the data is to look at your company’s needs, recommend availability options, and most importantly, do backups. Few things will get a DBA fired more quickly than not having backups available when they are needed. It may be years before you ever have to restore a database from a backup, but the backups had better be there when you need to do one.

Conference sessions and books have been written on the subjects of backup/restores, SLAs, and availability options, and they are so good that I’ll just give you a brief overview here. You’ll gravitate to your need-to-learns soon enough.

This is a nice overview for a beginner. One semi-related piece of advice that I picked up from Sean McCown: know that backup and restore syntax cold. Take backups manually (even if they’re just on a test server intended for this purpose) every day until you feel comfortable typing out backup and restore syntax from memory. There will come a time when three levels of management are standing over your shoulder waiting for a database to restore and SSMS’s GUI is crawling. At that point, knowing the syntax cold will be completely worthwhile.

Comments closed

Accelerated Database Recovery in SQL Server 2019

Published 2020-11-10 by Kevin Feasel

Jamie Wick walks us through the key concepts with Accelerated Database Recovery:

Beginning with SQL Server 2019, Microsoft has redesigned the database recovery process (ie. crash recovery and rollback) to improve availability and performance. This new feature is called Accelerated Database Recovery (ADR).

Prior to SQL Server 2019, recovering a database (after a crash or restart) consisted of 3 phases that followed the ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) recovery model. The phases of this model are Analysis, Redo & Undo. The Analysis phase begins with the last successful database checkpoint and forward-scans the transaction log to determine the state of each transaction. The Redo phase begins with the oldest uncommitted transaction (that was active after the checkpoint) and rolls-forward, bringing the database state to the same point it was at immediately prior to the crash. The Undo phase then goes backward from the end of the transaction log to reverse all transactions that were active (uncommitted) at the time of the crash. With this process, the database recovery time is roughly the same as the longest running query that was active at the time of the crash.

Click through for the full story.

Comments closed

DBCC CHECKDB on Large Databases

Published 2020-11-06 by Kevin Feasel

Aaron Bertrand shares some thoughts on CHECKDB:

We have a lot of data. Some of that data is stored in large databases (dozens of terabytes each). In some shops, this is an excuse to not run integrity checks. We are not one of those shops.

But we don’t run full CHECKDB operations in production; we have a set of servers dedicated to testing our restores and running checks. We follow a lot of the guidance in these articles:

– CHECKDB From Every Angle: Consistency Checking Options for a VLDB

– Minimizing the impact of DBCC CHECKDB : DOs and DON’Ts

– Minimize performance impact of SQL Server DBCC CHECKDB

Read the whole thing, even if you aren’t dealing with 30+ TB databases.

Comments closed

Creating Users in Azure SQL Database

Published 2020-11-06 by Kevin Feasel

Kenneth Fisher takes us through a nuance in adding users to Azure SQL Database:

Awesome! I did say I preferred code didn’t I? I am noticing a slight problem though. I don’t actually have a login yet. So I look in object explorer and there is no instance level security tab. On top of that when I try to create a login with code I get the following error:

Msg 5001, Level 16, State 2, Line 1
User must be in the master database.

Read on for the whole process.

Comments closed

A Primer on Multi-Server SQL Agent Administration

Published 2020-11-05 by Kevin Feasel

Mikey Bronowski gives us a glimpse at the power of MSX/TSX:

The MSX (master server) can be used to define SQL Agent jobs that then will be pushed over to one or more TSX (target servers). It is possible to see the server is either MSX or TSX when looking at SQL Agent in SQL Server Management Studio Object Explorer. The master server can be identified by (MSX) next to the SQL Server Agent. It will also have two subfolders under the Job folder: Local Jobs (for regular jobs) and Multi-Server Jobs.

This is something woefully few database administrators take advantage of. As the number of servers you have to mange increases, ensuring consistency in SQL Agent jobs becomes more and more difficult. MSX/TSX admittedly has some rough edges, but the tool does a lot.

Comments closed