Voice of the DBA

Tag: administration

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
The Choice of SQL Server Version

Every quarter Brent Ozar publishes some data from his SQL ConstantCare® service. This is a service where companies contract with Brent to install a service on their instance, collect data, and give them simple, short daily emails on things they should check. It’s a good service for companies who don’t employ skilled DBAs and may relay on a developer or sysadmin to manage a SQL Server instance. While this is a self-selecting group of organizations, across his 3,100+ monitored servers, there are likely trends that could apply to the world of SQL Servers in general. After all, for every gung-ho, let’s-upgrade DBA, there’s probably a sysadmin with a similar mindset.

In any case, the summer 2023 report form Brent shows that SQL Server 2022 adoption has slowed. His report is down, though I doubt anyone downgraded. Perhaps someone was testing and added a 2022 server in the spring they removed. Or maybe they tried 2022 and then went to the cloud. He does show 2019 growing and 2016 shrinking, which dovetails with what I see from my memory of various questions at SQL Server Central. I see people asking about moving from older versions to 2019 much more than 2022.

I wonder what that is? Brent thinks this is because people standardized on 2019 installs and haven’t moved to 2022. So anyone adding new instances likely uses an image/setup/process for 2019. That matches with a few of my customers, who haven’t had some of their install or security processes updated and are still adding 2019 instances. I think that’s short-sighted as 2019 is 4 years old, but I also understand that people get busy and updating anything for a new version isn’t a priority.

There have also been some problems with updates, and Brent thinks companies are skipping 2022. I don’t know, but I do wonder what you think about your estate and how things are changing. I assume if you are still running 2014- at this point, you’ll just live with the server as long as you can. I hope you’re at least on a VM so you can restore quickly if there are issues (assuming you back up VMs).

If you run 2016/2017, are you looking to upgrade? Considering 2022 or stick with 2019? Or kick the can and hope that SQL Server 2024 or 2025 will be better? Actually, take a guess as well on the next release date. I’ll take a page from Brent and run a contest for you to guess the next release date.

Steve Jones

July 22, 2023
Bare Metal

At the first job I had as a DBA, I had to build a new server. This was in the days of SQL Server 4.2, and I was combination DBA, sysadmin, and general help desk at a small company. With a software developer consultant, we ran some tests on various machines and then ordered a collection of parts from Compaq. Back in this time, they would only customer parts of the server. We unpacked our boxes with the server, extra drive bay, various SCSI drives, and extra RAM. We assembled and tested the machine and eventually put it into production.

In the years since then, I got out of the hardware business and left that to others. For awhile I worked in organizations with IT staff dedicated to building machines, but at some point we stopped doing that. The growth of VMWare and other hypervisors changed the paradigm for most organizations. For more than a decade, all the servers I’ve connected to are virtual machines running on hardware that my employer or a cloud provider owns and manages as a node in a cluster.

Early on there were concerns about the overhead of using hypervisors and virtual machines. When many early workloads moved to VMs, lots of organizations left database servers on bare metal to squeeze every bit of performance out of the system that was possible. Over the years, improvements in hypervisors as well as the software used to connect storage and networks together seem to have rendered those conversations obsolete.

Or have they?

Today I’m wondering if any of you still have servers on bare metal. Are there systems that you continue to install the OS and database server software directly on the hardware? Or are there systems where you still ensure that there is only one VM, the database server VM, on a hardware node?

The cloud has changed a lot of these conversations, since everything is a VM. Even many local data centers will rent you a VM, something that wasn’t possible when we first moved SQL Server Central out of a friend’s basement into a co-location facility. Back then we owned our servers and I installed them in data center racks. I would like to think we’d have moved our site to the cloud in some way over time, though maybe not. Maybe I’d still be managing a couple servers in a room in Denver.

Are any of your organizations still running legacy systems in the same way you would have in 1999? Let us know today.

Steve Jones

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

July 10, 2023