Tag: administration

Your Security Checkup

Recently I saw an article on Simple Talk, 15 Practical Tips for Securing SQL Server, and I thought that many of these are fairly simple things. Turn off unused features, disable sa, etc. These are things that a lot of people probably ensure are in their SQL Servers builds.

Though, I’m sure a lot of people don’t bother.

Often, I’ve found that different people might be responsible for setting up servers, or they might have rights to change things on existing servers. Over time, what we thought of as a standard often isn’t standard on all instances. Exceptions creep in, perhaps because developers change things when they don’t know better or aren’t thinking of security. Vendor software might have some unexpected requirements for similar reasons that deviate from our standard. We also might change our own standards over time and forget to revisit existing servers.

I wonder how many of you have a security audit procedure in place to re-examine your existing servers. It’s something that ought to be done periodically, like storage management. It isn’t needed every day or week, but a few times a year you might want to ensure things are set appropriately and ready for the next few months.

I’ve been surprised at the number of people that really like the Redgate Monitor Configuration page to keep track of their servers and the deviations their own standard config. It’s also been interesting how many people upgrade to the Enterprise Edition to get the Security features. Tracking these over time can be a pain DBAs want an easy way to do this. In fact, there are so many feature requests for enhancements to security tracking that the devs on RGMEE are very busy.

It’s getting to be the end of the year, and that’s a slower time for many of us. Unless your business is related to the holidays, a lot of people take vacation, we have code freezes, and there’s a little more time for housekeeping. This might be a good time to conduct a little security audit and ensure that your servers aren’t open for attack or making it easy for malicious actors, or naïve but well-intentioned coworkers, to get into systems.

DBATools is a great way to do some changing or enforcing of standards across lots of servers. Even if you can’t change all the settings, you might ensure you have documentation on why that one instance has an sa account enabled. You might also ensure that your security people have signed off on any exceptions.

If nothing else, a good security checkup should include checking your versions and getting up to date on patches.

Steve Jones

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

Note, podcasts are only available for a limited time online.

December 8, 2025

Creating a Striped Backup Set with AI

I needed to test a striped backup, so I decided to ask the AI’s for help.

This is part of a series of experiments with AI systems.

The Problem

A customer wanted to know if we could handle a large striped backup set. In this case, 20 files. I didn’t want to try and do this myself because I was busy at the time, so I thought I could get the AI’s to help me.

I started with this prompt: for a sql server database called “Sandbox” can you generate a backup command that will stripe the backup across 32 files with “sandbox” the current date/time, and a number for the backup file name?

Local Gemma3 LLM

I started with a local model because I had that open. I got a result that looked OK.

Below this I had PowerShell code to help me:

-- Using PowerShell (example):
# $i = 1
# while ($i -le 32)
# {
#   $filename = "D:\Backups\Sandbox\_" + REPLACE(CONVERT(VARCHAR, GETDATE(), 112), ' ', '_') + "_" + $i.ToString("D3").PadLeft(3, '0') + ".bak"
#   # Execute the backup command:
#   Invoke-Command -ComputerName <ServerName> -ScriptBlock {
#       & "backup.exe" -Database "Sandbox" -BackupFile "$($filename)" -BackupType "D" -Init -Format "MEDIANAME = \"Sandbox\""
#   }
#   $i++
# }

This won’t work, proving that there isn’t enough database training in some of these small models. The model does warn me this won’t work as is.

Claude

Since the local model was slow, and didn’t get me what I wanted, I tried Claude. Same prompt, and I got this code, which is nice in that it handles the current date time whenever I run it.

DECLARE @BackupPath NVARCHAR(500) = 'C:\Backups\' -- Change this to your backup directory
DECLARE @DateTime NVARCHAR(20) = CONVERT(NVARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR, GETDATE(), 108), ':', '')
DECLARE @BackupCommand NVARCHAR(MAX)

SET @BackupCommand = 'BACKUP DATABASE [Sandbox] TO '

-- Generate 32 striped backup files
SET @BackupCommand = @BackupCommand +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_01.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_02.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_03.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_04.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_05.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_06.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_07.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_08.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_09.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_10.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_11.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_12.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_13.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_14.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_15.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_16.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_17.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_18.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_19.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_20.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_21.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_22.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_23.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_24.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_25.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_26.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_27.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_28.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_29.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_30.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_31.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_32.bak'' ' +
     'WITH COMPRESSION, STATS = 10'

EXEC sp_executesql @BackupCommand

I could easily then test this by adding a SELECT and commenting out the Backup command. When I do that, I see these results.

Note I’ve started adding some line breaks to the results so I can check things.

Prompt AI

I decided to try Prompt AI as well. I started with this code:

BACKUP DATABASE sandbox
TO DISK = ‘sandbox_20251013_x.bak’ WITH INIT

Then I opened Prompt AI and entered this: expand this backup command to create a striped backup of 32 files, replacing the x with a number for each file

You can see this below:

This got me a quick and dirty backup test.

Then we need to correct the syntax.

Now I have a command.

However, this doesn’t work as I can’t use string concatenation inside the backup command. I asked Prompt AI to generate a string for this instead (from this code) and then sp_exeutesql and got this code:

DECLARE @DateString VARCHAR(8) = CONVERT(VARCHAR(8), GETDATE(), 112) -- Format: YYYYMMDD

DECLARE @BackupCommand NVARCHAR(MAX)
-- Build the backup command as a string

SET @BackupCommand = 'BACKUP DATABASE sandbox TO '
-- Add all backup file destinations

SELECT @BackupCommand = @BackupCommand + CASE
                                              WHEN LEN(@BackupCommand) > 25 THEN
                                                  ', '
                                              ELSE
                                                  ''
                                          END + -- Add comma except for first item
     'DISK = ''sandbox_' + @DateString + '_' + CAST(number AS VARCHAR(2)) + '.bak'''

FROM master.dbo.spt_values

WHERE type = 'P'
       AND number
       BETWEEN 1 AND 32
-- Add WITH INIT

SET @BackupCommand = @BackupCommand + ' WITH INIT'
-- Execute the dynamically built command

--EXEC sp_executesql @BackupCommand

SELECT @BackupCommand

That works.

Summary

Both Claude and PromptAI worked, though Claude worked well with a first prompt. I continue to like the Sonnet model and find it really helpful in saving time.

It took me longer to write this post than generate the code in 3 places and test it. In terms of saving time, Claude would have saved me at least probably ten minutes, maybe more, as I’d spend time making decisions. With Claude, I got the code in under a minute and then needed to test it.

Always test your code. Including your AI-generated code.

October 22, 2025

Sparse Columns Can Use More Space: #SQLNewBlogger
I saw this as a question submitted at SQL Server Central, and wasn’t sure it was correct, but when I checked, I was surprised. If you choose to designate columns as sparse, but you have a lot of data, you can use more space.

This post looks at how things are stored and the impact if much of your data isn’t null.

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

Setting Up

Let’s create a couple of tables that are the same, but with sparse columns for one of them.
```
CREATE TABLE [dbo].[NoSparseColumnTest](
     [ID] [int] NOT NULL,
     [CustomerID] [int] NULL,
     [TrackingDate] [datetime] NULL,
     [SomeFlag] [tinyint] NULL,
     [aNumber] [numeric](38, 4) NULL,
  CONSTRAINT [NoSparseColumnsPK] PRIMARY KEY CLUSTERED 
(
     [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
```
```
CREATE TABLE [dbo].[SparseColumnTest](
     [ID] [int] NOT NULL,
     [CustomerID] [int] NULL,
     [TrackingDate] [datetime] SPARSE  NULL,
     [SomeFlag] [tinyint] SPARSE  NULL,
     [aNumber] [numeric](38, 4) SPARSE  NULL,
  CONSTRAINT [SparseColumnPK] PRIMARY KEY CLUSTERED 
(
     [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
```
Once we have these, I used claude to help me fill this with data. That’s coming in another post, but I uploaded the script here. This is for the SparseTable Test, where I replaced the select on line 59 with NULL values. In the NoSparse table, this selected random data.

If I select data from the tables and count rows, I see 1,000,000 rows in each. However, the Sparse table is all NULL values in these columns.

Checking the Sizes

I can use sp_spaceused to check sizes. The results of running this is below, but here is the summary
- NoSparse Columns – 42MB and 168KB for the index
- Sparse Columns – 16MB and 72KB for the index
A good set of savings. Here is the raw data:

Adding Sparse Data

I’m going to update 10% of the rows to be not null in different columns. Not 10% total, but a random 10% amongst all the columns. Again, Claude gave me a script to do this and I have run it. This is the SparseTest_UpdateData.sql in the zip file above.

After running this, I have 900,000 nulls i the TRackingDate, as well as the other columns. You can see the counts below, and a sample of data.

If we re-run the size comparison, it’s changed. Now I have:
- NoSparse Columns – 42MB and 168KB for the
  index
- Sparse Columns – 33.7MB and 88KB for the index
Not bad, and still savings.

Let’s re-run the update script and aim not for 10% updates, but 65% updates. This gets me to only 315k NULL values in the tables, or a little over 70% of my sparse columns are full of data. My sizes now are:
- NoSparse Columns – 42MB and 168KB for the
  index
- Sparse Columns – 67MB and 192KB for the index
My sparse columns now use more space than my regular columns.

Beware of using the sparse option unless you truly have sparse data. I didn’t test to find out where the tipping point it, but I’d hope it was less than 50% of data being populated.
SQL New Blogger

This is another post in my series that tries to inspire you to blog. It’s a simple post looking at a concept that not a lot of people might get, but which might trigger a question in an interview. That’s why you blog. You can share knowledge, but you build your brand and get interviewers to ask you questions about your blog.

This post took a little longer, about 30 minutes to write, though the AI made it go quicker to actually generate the data for my tables. There were a few errors, which I’ll document, but pasting in the error got the GenAI to fix things.

This post showed me testing something I was wondering about. In a quick set of tests, I learned that I need to be careful if I use a sparse option. You could showcase this and update in 10% increments (or less) and keep testing sizes until you find when there is a tipping point. Bonus if you use a column from an actual table in your system.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-sparse-columns?view=sql-server-ver17
September 10, 2025
Guidelines and Requirements

I saw a post from Brent that Microsoft had changed the default memory guidance. At first glance I read this as they’d changed the default values, which would be interesting. However, this is a guideline, set to 75%. I also saw a few thoughts from Randolph West on LinkedIn, and quite a few comments. The comments were interesting in a few ways.

It is easy to look at 75% and say that won’t work for this server that’s on my mind right now because I keep getting woken up. That might be true. However, the 75% number isn’t a hard requirement. It’s a guideline, a recommendation to ensure you have enough memory for the OS, but you’re trying to use most for SQL Server. Feel free to adjust it if you feel the need.

There are certainly people who will also look at that number and then go to a DBA and say, “you’ve set this to 70% (or 85% or whatever) and that’s not what Microsoft says.” Which isn’t true. What the text says is this under the recommended column: “75% of available system memory not consumed by other processes, including other instances. For more detailed recommendations, see max server memory”

If you go to the “max server memory” section, you see something else. It asks you to monitor before you set this, then do some calculations. Then it says: “This is a generic approximation, and your mileage might vary.”

That’s a great statement. What they’ve written might not work for you. That’s true. Maybe you have little RAM and some other stuff on your server, so 75% might be way too high. Maybe you have 4TB of RAM, in which case, if you blindly set 75% you should be asked to work elsewhere. Anyone managing systems with 4TB of RAM should know how to monitor, measure, and then choose something different, which might be 85% of RAM.

While there might be some requirements for managing database systems, there really are a lot of guidelines. You have to make decisions, which means you need some knowledge on which to make good decisions. If you don’t have that knowledge, or are unsure, ask others, ask the GenAI’s, conduct experiments, test things. That’s the job. Learn what you need to make things run better.

Better being what your clients need, want, and desire.

Steve Jones

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

Note, podcasts are only available for a limited time online.

September 5, 2025

Tag: administration

Your Security Checkup

Creating a Striped Backup Set with AI

The Problem

Local Gemma3 LLM

Claude

Prompt AI

Summary

Sparse Columns Can Use More Space: #SQLNewBlogger

Setting Up

Checking the Sizes

Adding Sparse Data

SQL New Blogger

Guidelines and Requirements