Voice of the DBA

Tag: administration

Ten Database Commandments

I love the Notorious B.I.G. His music isn’t for everyone, but I enjoy it. This piece takes one of his songs (NSFW – Ten Crack Commandments) and applies the list to databases. Each item from the song is listed, along with the way it might be changed for database work. The list is from Ottertune, which is a service designed to help you optimize your use of AWS RDS and Aurora databases, with AI. If you believe their hype, their service will get better performance and lower costs for you.

In any case, with regard to databases, I suspect that the author is having some fun with the rules, and a few of them made me smile. They are cloud-related, but a few will apply to any environment.

The first one is about budgets, noting you should not disclose your budget, which is in general a good rule whenever you are buying something. Don’t disclose information, and someone working for a vendor of tools, I think that most budget conversations aren’t something technical people should worry about. Decide what value you get from some tool/service/process/etc. and if there are time savings. Then let someone else decide if the ROI is worth the cost.

Rule 2 is a stretch, but I do think that looking at new tools can be helpful. The caveat here is that many people, don’t often learn enough about existing tools to understand if they will solve issues. There are a lot of things you can do with many tools, so don’t just look for some magical new tool. Learn to use your tools well, and if you want to try a new one, keep in mind you will need to invest some time both learning about it and practicing some skills before you really know if it’s helpful. The other thing to remember is every new tool increases the bar for new staff. Try to limit the number of tools you use to those you need.

Security is important and rule 3 is remembering not to give out more permissions than needed, especially to developers. I love this quote: “You don’t want randos at your organization logging in and running queries that they found on Stack Overflow.”

A number of the other rules have to do with performance, which is certainly Ottertune’s business, but these are good basic practices that I see organizations not following. Too often management wants to save money and technical people make decisions that compromise performance. Lots of people overload systems, and then they can’t handle the workload well. I get that many CFOs and others want to see high CPU usage, which means they’re using the resources they pay for, but databases can be very bursty with resource usage. At the same time, you don’t want to over-provision resources because you are scared. Learn to find a balance that doesn’t waste money, but handles your workload well.

Perhaps one of the best things about the cloud is that if we do make bad decisions, we can often provision more resources. It can take time to move to a new tier of service, so don’t expect instance response up or down, but if you need to double the CPUs for month-end processing, you can likely plan for the time to scale up your database instance and then scale down again when all the work is complete.

It’s a fun article, and if you’re looking for some fun Notorious B.I.G.-related database content, grab a cup of coffee when you need a break and a smile.

Steve Jones

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

November 20, 2023
Running a Service

Many of us work for some organization and we’re responsible for managing a database or coding some application or some other data related position. Perhaps we ensure reports work for our users or move data around. In many cases, we have customers, whether they are internal to our organization or perhaps external from another business or even the wider public.

However, many of us see our jobs as a part of the org/business and not that we are actually providing some service to others that we are responsible for. While I’ve certainly felt that way, I learned at some point that if I were an app dev, I ought to treat people using my app as customers, even though they don’t pay me in currency. They often pay me in praise, or better yet, with praise to my boss. As a DBA, I treated both app devs and users as customers. That helped me take pride in my work and learn to view my position from other points of view.

There was a post on learnings from running a SaaS service for a year. While few of us maintain a SaaS service, we could think of the app we build as Software-as-a-service, albeit without any revenue. We could see databases we manage as DBaaS (databases as a service) or PaaS (platform as a service) items.

I think that the way we view the world, or view our situations, can be helpful to us in managing our workloads. We can better triage what is critical, what’s important, and what’s nice to have. We can decide when an issue deserves some to cause some stress and we might give an extra effort, and when something can wait until tomorrow. We can decide when we push back on demands and when we accommodate them.

This approach has also helped me to think about ways to improve the service/software/platform I provide to others in ways that helps my customers, or makes my job easier. This way of thinking leads me to make the situation better when I can. It also helps me to discuss the decisions with others, especially management, in terms that can matter to them. Are we finding ways to improve the overall system?

It doesn’t always work, but it often has for me.

And if you’re interested in your own side project, the journey continues in part two. Perhaps you can spend an hour or two a day and build something that becomes your own business. That’s what happened to me with SQL Server Central.

Steve Jones

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

September 27, 2023
Restore with Standby–#SQLNewBlogger
A customer had a question about restoring with standby, so I wrote a quick post to explain how this works.

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

The Scenario

Sometimes you want to restore part of your data, but you still want the option to continue restores. A classic example of this is when you are restoring a number of transaction logs and want to check the data to find a place where certain values haven’t been changed.

Suppose someone deletes a bunch of data between 10am and 11am from the supplier table. You know that they added “Acme” to this table before the delete. You might restore up to 10am and check the supplier table for the old data and look for Acme. If it’s not there, maybe you restore the 10:05am log backup and check again. If it’s not there, then the 10:10am log, etc.

SQL Server lets you query a restored, but not recovered database with the STANDBY option. If you use NORECOERY, you can’t query the data. I won’t delve into the technical process in this blog, but for now, this is what we want to do: query a restored db, but not prevent future restores.

Setup

First, create a database and then take a backup. I created the “sandbox” database in my system and backed it up. I won’t cover that. Now, let’s set up a restore. I’ll choose a new name, since I’m looking for data. Here’s the statement I picked:
```
USE [master]

RESTORE DATABASE [sandbox4] FROM  DISK = N'D:\SQLBackup\New folder\sandbox_20210308.bak' WITH  FILE = 1,
   MOVE N'Sandbox' TO N'D:\SQLServerData\SQL2017\Sandbox4.mdf',
   MOVE N'Sandbox_log' TO N'D:\SQLServerData\SQL2017\Sandbox4_log.ldf',
   STANDBY = N'D:\SQLBackup\New folder\sandbox_RollbackUndo_2023-08-07_11-51-39.bak',  NOUNLOAD,  STATS = 5

GO
```
This is complex, and I haven’t memorized this syntax. Plus I don’t want to type all those paths. Instead, I use SSMS. I’ll set up the restore there. Here are the three screens. Note in the first, one I’ve pointed to the “Script button”, which is what I pushed to get this command.

I’ve picked the device here and changed the restore to database name.

File options. Make sure the filenames don’t conflict with existing ones.

Set the standby option and remove the tail log backup if this is the same instance as the original database.

Restoring and Querying

When I run this command, I’ll get this type of output. I like getting stats, in case something sticks.

In the object explorer, I’ll also see the database as standby/read-only.

If I open a query window, I can get data from this database.

However, I can’t update tables.

Summary

This is a quick look at how to get a database in standby. I covered one use case above, but not all the ways in which you use standby or what’s happening behind the scenes. If you need to query a database in the middle of the restoring state, use the standby option.

SQLNewBlogger

This post took me about 15 minutes to assemble. I set this up and tested it for a client, then I had to redo the work with screen shots and add the text. It’s not too long, but this is a good example of how I set up a post that leads to others. I need to create these posts:
- what happens with the standby option
- restoring more backups to this database with standby
- restoring more backups to this database without standby
- coming out of standby mode
- automating this to look for a data change
You could do this and showcase your knowledge of this feature and how you might use it
August 30, 2023
Finding Encrypted Stored Procedures–#SQLNewBlogger
I had a client ask about how to deal with encrypted stored procedures in their database. This post looks at how to find them and I’ll have future posts that show how to decrypt these and also how Flyway helps.

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

The Scenario

A client was trying to start putting their code in a VCS, but they ran into the issue that they had procedures which were encrypted. In their case someone had done this in the past and the current staff wanted to know how to get the code from production.

As a setup, here are two procs I’ve created that are encrypted:
```
CREATE PROCEDURE EncryptedOne WITH ENCRYPTION

AS

SELECT 1 AS One

GO

CREATE PROCEDURE EncryptedTwo WITH ENCRYPTION

AS

BEGIN
     DECLARE @i INT = 1;
     WHILE @i < 100
     BEGIN
         SELECT @i = @i + 1
     END
     SELECT @i / 50

END
```
These two procs don’t do anything weird, but if I try to script them like this in SSMS:

I get an error:

The error says that the text for the procedure is NULL.

The text is stored in the sys.syscomments DMV, which we can see below. This is the text that was submitted for procedures without the WITH ENCRYPTION. You can also see a NULL entry for the procedures I created above.

I can filter on this with a
```
  WHERE [text] IS NULL
```
Or I could use ObjectProperty(). This has an IsEncryted parameter I can send in with this code:
```
select name, OBJECTPROPERTY(object_id, 'IsEncrypted') AS Encrypted, OBJECT_DEFINITION(object_id) AS Code

from sys.procedures

WHERE OBJECTPROPERTY(object_id, 'IsEncrypted') = 1
```
Of course the text column isn’t needed as all the code is null here. If I wanted just a list, I’d likely only have the first two columns.

That’s it. With this script I can see those procs which are encrypted. In my case, it’s four.

SQL New Blogger

This is a quick post that shows how to find those procedures (or views) which were created with WITH ENCRPTION. I’ve run into this a few times and while this is a focused, small post, I also took the opportunity to break this into multiple posts rather than doing just one long one.

You could do this as well and showcase how you break a problem down. This took me about 10 minutes to do this post.
August 9, 2023