Voice of the DBA

Tag: syndicated

Create a Linked Server: #SQLNewBlogger
I had a customer recently that was asking about Linked Servers and some development advice. I was going to show them a few things and realized I hadn’t created a linked server in my demo environment, so I did it and decided to create a quick post on this.

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

The Scenario

I have a few demo instances of SQL Server in my local environment: Aristotle and Aristotle\SQL2022. In this case I was connected to the named instance, and decided to create a connection to Aristotle. As you can see, I don’t have any linked servers in the named instance.

To create a linked server, I can use this simple code:
```
EXEC master.dbo.sp_addlinkedserver   
     @server = N'Aristotle',   
     @srvproduct=N'SQL Server';  
GO
```
This creates the linked server (as you can see below), with a number of defaults. In this case, the security is made with whatever login queries the linked server.

You can see the security properties here:

This might be OK in your enviroment, or it might not be. Perhaps you need to ensure everyone querying the remote server uses the same login. In which case, the sp_addlinkedserver procedure doesn’t do this. You would need to use sp_addlinkedsrvlogin to do that. That’s for another post.

NOTE: Be sure you understand what a linked server does, how to use it, and the downsides. There are many and this can slow down your application or overload servers

I can test this connection by right clicking the Linked Server in SSMS:

This works, as expected.

I can also run a query through the linked server, using 4-part naming with the linked server, then the database, schema, and table. This also works:

That’s it to get started. I recommend you be careful when using linked servers as this creates a bit of a tight coupling and makes development harder. I might recommend you get away from querying database server to database server when possible and let an application do this work if it’s possible.

SQL New Blogger

Linked Servers aren’t that common, but they aren’t rare. This is a skill that SQL Server people should have and understand a bit about. This post is very basic, but it provides a jumping off point where I could write a number of other posts related to linked servers and perhaps guide an interviewer along a path of asking me about them. I certainly showcase some knowledge here if someone asks me if I’ve ever created one.

This post took me about 10 minutes to test and write, and you could probably do this in your environment. You don’t even need to servers, as you could create a loopback linked server.
May 5, 2025
Advice I Like: Examining My Past Self

If you are not embarrassed by your past self, you have probably not grown up yet. – from Excellent Advice for Living

While I’d like to think most of us are true to who we are and act well in most situations, the reality is that we’re always learning and growing, and we will make mistakes. If we don’t find some of them embarrassing in our current world, we likely haven’t grown much.

This isn’t being embarrassed by everything, but by some things. I would think many of us make mistakes, say things, do things, treat others poorly in the moment and we wish we hadn’t.

I think a healthy part of life (or family or community, or organization or country or whatever) is making some mistakes, growing and learning not to do the same thing, or a worse thing. Becoming a better person is growing up and that should mean reflection on the past, with some embarrassment over how the past played out.

At the same time, this doesn’t mean that you hold your past self to a higher standard. I recognize that it’s unlikely in many cases that I should have known/done/acted/said something better. I’m just embarrassed now.

Examples for me include how I treated many girls/women, with not enough respect and with my own selfish goals of sex in mind. Poorly treating some coworkers that didn’t know as much as I did, with arrogance or derision. Certainly plenty of times I was upset with my children for what I now see were not good reasons.

I’m mostly happy with who I’ve been, but I’m also embarrassed by some of my actions.

I’ve been posting New Words on Fridays from a book I was reading, however, a friend thought they were a little depressing. They should be as they are obscure sorrows. I like them because they make me think.

To counter-balance those, I’m adding in thoughts on advice, mostly from Kevin Kelley’s book. You can read all these posts under the advice tag.

May 2, 2025
DevOps Day – Atlanta

No tour this year, but Redgate does have a few DevOps events scheduled. I’m hoping for more, and the first one for me this year is Atlanta. You can register for the DevOps Day Atlanta Workshop on May 15, just a few weeks away.

I’ll be there to set the stage and give a high level view of how Redgate approaches better database code management and deployment with Flyway. We’ll also have a whiteboarding session if you have questions to solve some architectural challenges.

If you can make it, please register today and come enjoy a day with us.

May 1, 2025
Index Maintenance Can Change NORECOMPUTE Settings
I had a customer recently ask about a change in one of their constraints on production, where a new option appeared when they went to deploy some changes from QA. They asked how this could happen, and I’ll show how in this post.

Suppose I create a table like this in a development environment.
```
CREATE TABLE [dbo].[Logger](
     [LogID] [INT] NOT NULL CONSTRAINT LoggerPK PRIMARY KEY,
     [LogDate] [DATETIME] NULL,
     [LogMsg] [VARCHAR](2000) NULL
     )

GO
```
I (hopefully) have a process to get this to production (version control, automation, etc.). Once in production, if I were to script this on SQL Server 2022, I’d get this from SMO.
```
CREATE TABLE [dbo].[Logger](
     [LogID] [INT] NOT NULL,
     [LogDate] [DATETIME] NULL,
     [LogMsg] [VARCHAR](2000) NULL,
  CONSTRAINT [LoggerPK] PRIMARY KEY CLUSTERED 

(
     [LogID] 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
```
This looks different, but really this includes the defaults that existed in dev, and also in production. Hopefully all my SETtings and configuration is the same, to ensure no surprises.

Now, let’s imagine a DBA has some index maintenance, perhaps Ola’s scripts or some other script that works through all tables and indexes. If a DBA decides they’d like to edit the script to change a setting, they might end up running this code for my Logger table:
```
ALTER INDEX ALL ON dbo.logger

REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
```
There’s a small change in here from the defaults, which I’d see if I were to run a SQL Compare comparison. Now I’d see this type of change, which might not be a problem, but it might be an issue where each deployment wants to reset this setting.

If you don’t think this is a big deal, here’s the deployment code:

I would not want this going through my deployments. And it might if our team were no diligent in looking at the deployment script.

Be explicit with defaults, and be careful about making changes in production. You might end up creating problems in your update process if you don’t feed these changes back to development.
April 30, 2025