Voice of the DBA

Tag: administration

Scaling Up Monitoring

Monitoring databases is important when it’s the systems that are in production. Operations departments know that catching issues early, being proactive, and having data to troubleshoot issues make their job easier. Not having these things makes their job much more stressful.

Most of us work with data in some way and the availability of that is important. Certainly, security, integrity, and performance matter as well, but availability is key. Many organizations don’t have any monitoring systems set up. Instead, they troubleshoot problems when someone files a ticket or calls. I’m amazed at this, though I know building and managing a monitoring system is hard and purchasing third-party products can be outside of your budget. Still, having something in place makes everyone’s job easier.

If you decide to build a system, then you can do it in many ways. I saw a description of how Amazon built a monitoring system for their Prime Video service. This is a more complex system than many of us deal with for databases, but I did find it interesting that they chose a distributed architecture that used multiple components. It didn’t scale, so they started to move from small functions, almost like microservices, to a bit more monolithic structure.

I am not saying that microservices or functions or serverless are bad choices. They meet certain needs, and they can work very well. Azure SQL Database Serverless can work well in some situations. However, I do think that this was a case of engineers trying to be too clever and making assumptions about production loads from PoC-type experimentation.

I would say that far too many software engineers think that their solution will scale without actually testing it. Too often their view is if it works here, it will work there, but the history of software has shown that working on my machine doesn’t mean working on another. That’s why we use Continuous Integration: for independent validation and verification. This is also a problem when databases are involved, as the level of data used for development and testing doesn’t do a good enough job of predicting how the system works under load. We need better test data management, which is becoming a whole new category of software practices and tools.

We should ensure we include good instrumentation in our software for monitoring purposes, but we should also ensure that we start monitoring and evaluating how our system will perform in test and development environments, as that’s the idea of shift-left. Lastly, I think monitoring in production is important, but I wouldn’t build another system. I admit I’m biased, as I work for a company selling monitoring software. However, I also think the build v buy debate doesn’t make sense here unless your staff has a lot of spare time to spend maintaining a homegrown system. I’d like to think most of them have better things to do.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

May 31, 2023
Validating Password Expiration

I would guess that the majority of instances I’ve had to manage in my career were those that I didn’t initially install and configure. I’ve inherited more instances than I would bother to count, and I often need to double-check what’s been done in the past. As noted in the series on new jobs from Tracy and Josephine, there are a lot of settings to check and adjust to meet your standards.

While backups are often my first priority, security is second. I usually want to know who the sysadmins are and ensure systems are patched and configured to reduce the attack surface area. There is one other security check that I think I haven’t always been overly concerned about checking: password expiration.

There was a post from Steve Stedman recently that mentioned the way to alter logins and ensure they have CHECK_EXPIRATION set ot on, which ensures that passwords expire and need to get changed. This is especially important for sysadmins. I try to ensure those accounts in that role are secured with AD, but there have been times when SQL accounts are used. Usually, I disable sa, but I’ve seen other accounts, especially those used by monitoring systems who seem to think sysadmin is required. It’s not.

I don’t know that I’ve run queries to check the value in the is_expiration_checked column is appropriately set. If it’s not, then Steve’s post above will help you change those logins. That’s a handy script to have set up and use to ensure that all logins have this set. In fact, this is one of those areas where new logins could be created by junior administrators and not set the option. Perhaps this is something you want to run on a regular basis, perhaps weekly, to ensure that if any new SQL logins are created, they are done so with the password expiration set.

Ideally, no one would ever create logins without expiration set, but sometimes things happen. I’ve seen monitoring systems set up with sysadmin privileges and passwords that never expire. A surefire way to dramatically increase the risk to your database systems. It would be better to have a known, consistent process for setting up accounts. Some companies have specific scripts, or snippets, that administrators use when tickets are filed. One customer of mine had even linked a script to a Slack command in a sysadmin channel. Only admins could use this channel, but they could use Slack to kick off scripts to create logins, add roles, and force password changes.

No matter how you choose to handle security at a process level, it is important to include monitoring and remediation for issues. Mistakes will get made, settings altered, and exceptions approved. Sometimes we can fix things, sometimes we cannot, but knowing what our environment looks like and where we have potential issues is important not only for getting the work complete but getting the approvals to make changes that ensure better security. My recommendation is that you ensure you have a way to regularly check your systems, automatically fix issues where appropriate, and report on those that need additional approvals.

Steve Jones

May 27, 2023
Restore One Backup From Many in a Device–#SQLNewBlogger
I wrote recently about finding multiple backups in a file. This post looks at how to restore one of those. The one you choose.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Setup

In the previous post, I did these things:
- took a backup
- added a table and data
- took a second backup
- truncated the table
- took a third backup
If I restore the default last backup, I get my table without data. You can read that post to see how I got here.

Let’s restore things.

Restoring a Backup

I cheat with restores. I remember some syntax, but typing it in and trying to remember the order is a pain, even with SQL Prompt. So I click restore database in SSMS and fill out the dialog. I pick the device and when I change the name in the Destination database, the file names change. Once I have the dialog below, I click “Script” at the top.

This gives me code in a new window. In my case, I get this code:
```
USE [master]

RESTORE DATABASE [sandbox2] 

FROM  DISK = N'D:\SQLBackup\sandbox.bak' 

WITH  FILE = 3,  

MOVE N'sandbox' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\sandbox2.mdf',  

MOVE N'sandbox_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\sandbox2_log.ldf',  

NOUNLOAD,  STATS = 5
```
By default, this gives me file=3, which is the third backup. If I run this and then query the new database, I see this:

That’s what I expect. The third backup had the table with no data. Let’s restore the second one. First delete the database and then change File=3 to File=2. Once I run the restore and the same query, now I see data:

If I restore file=1, then there is no table.

Alter the FILE parameter to pick the backup in the file.

SQL New Blogger

This post took less than the 10 minutes of the previous post. I basically restored my database a few times with a query. The code was a couple minutes to generate and modify in SSMS, and this writeup was short.

The key was doing this immediately after the previous post and reusing the setup and code. Plus, the concept was in my mind.

As with the previous post, this is a good way to show knowledge and learning, and in this case, 20 minutes got me two posts.
May 17, 2023
What Backups Are In This File?–#SQLNewBlogger
I had a question on multiple backups in a file and had to check my syntax. This post shows how to see which backups are in a file.

Note: Don’t do this. Put backups in separate files.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Setup

I have a sandbox database. I made a backup of this.
```
BACKUP DATABASE [sandbox] TO  DISK = N'D:\SQLBackup\sandbox.bak' 
   WITH NOFORMAT, INIT,  
   NAME = N'sandbox-Full Database Backup', 
   SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
```
Note I used INIT, which will ensure this is the only backup in this file.

I then changed something, in this case, I made a new table (I was testing things for Rich).
```
CREATE TABLE testforrich (myid INT)
GO
INSERT dbo.testforrich (myid) 
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM sys.columns AS c
GO
```
I then ran another backup. However, this time I wanted to append to the existing file.
```
BACKUP DATABASE [sandbox] TO  DISK = N'D:\SQLBackup\sandbox.bak'
  WITH NOFORMAT, NOINIT,  
  NAME = N'sandbox-Full Database Backup', 
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
```
The NOINIT keyword is in here, which appends the backup to the same file. In essence, sandbox.bak will then contain two different backups in one file. For this test, I then made another change and another backup.
```
TRUNCATE TABLE testforrich
GO
```
```
BACKUP DATABASE [sandbox] TO  DISK = N'D:\SQLBackup\sandbox.bak'
  WITH NOFORMAT, NOINIT,  
  NAME = N'sandbox-Full Database Backup', 
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
```
Now I have three backups in the file.

Checking Contents

If I were to click the restore item in SSMS and pick the file, I see this:

Note that the position is listed as “3”, which means this is restoring the newest (most recent) backup by default. I don’t seem to be able to edit this, though if I click timeline and change the time, I can get a different backup. I see different backups in there:

However, when are those backups? This timeline isn’t great.

I can use RESTORE HEADERONLY. The command I ran is:
```
RESTORE HEADERONLY FROM DISK = 'd:\sqlbackup\sandbox.bak'
GO
```
This gives me all three backups, which are shown as different positions in the file.

From here, I could perform a restore with a different backup if I needed to.

SQL New Blogger

This was a quick post that I wrote after I spent 5 minutes creating a test for something. I grabbed my code, took a few screen shots, and it took about 10 minutes to assemble this.

Easy for you, and this shows a potential interviewer or manager that you can dig into a small issue, learn, and solve it. Try it for yourself and write a blog post.
May 10, 2023