Voice of the DBA

Tag: administration

Removing a LocalDB Instance

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

This might be obvious and easy, but I spent a couple minutes learning how to remove a LocalDB instance.

I tried to use remote, but that didn’t work:

There error reminded me that in my day, most commands use –? or /? to get help. A lot of CLIs these days use –help (two dashes). That doesn’t work here. But /? does.

This shows me the delete option is the one to use. I tried that, but I needed to stop the instance.

Running stop and then delete allowed this to succeed.

SQL New Blogger

After writing a previous post, which took me 10 minutes or so, I went to clean up my environment. I realized this was easy, but also worth a post about how I learned this.

Just 5 minutes. You could write posts like this to further your knowledge and help your career.

April 6, 2022
Starting LocalDB–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Lots of people have never worked with LocalDB, which is an in-process version of SQL Express. No service account, just a SQL Server instance running with your app. It’s a nice lightweight way to get SQL Server running quickly without a hassle.

This is a SQL Server Express version, but the bare bones for development. This post looks at how you can get this running.

This version of SQL is installed with SQL Express, and with Visual Studio. If you look in this path: C:\Program Files\Microsoft SQL Server\150\Tools\Binn, there is a SQLLocalDB.exe. You can see that here.

This is my SQL Server 2016 version of LocalDB. I can start a new instance by calling this with the CREATE option. I can give this a name as well, as I might want to stat multiple instance for different apps. Here I’ll create an instance called app1.
```
SQLLocalDB create app1
```
I then call the same command, but use START instead of CREATE. You can see this reports as started from the CLI. I also add the INFO call to get status.

Now I can connect. I use (LocalDB)\app1 to connect:

I see I’m connected to a version of LocalDB then:

Now it’s just an instance of SQL Server I can use.

SQL New Blogger

I needed to check something for a customer and realized I hadn’t started LocalDB in a long time, so I needed to check the docs. I spent 10 minutes putting this post together.

An easy type of post for any of you out there. Learn something, try something, write something.
March 28, 2022
Easily Repeating Work

I love this idea from Ken Fisher: saving your work. I don’t act as a DBA anymore, but when I did, I did something similar. We often logged the scripts we used in a file, as a part of a log, so that if we broke something and another DBA got a new ticket, they could check what you had done. Over the years, we tried two different methods. First was using the desktop of the instance itself, since we often went to a room to log into the server in those days

The second way was in an Exchange public folder, where we added a new entry for each day. This way we could note the server and the scripts run. Since most tickets were dated, we could easily find the scripts if we were looking at a ticket. Since a user often updated or re-opened the ticket, we could use the public folder as a central note location from the DBA team. We could even point to this folder for our ISO and SOX auditors to show them what had been logged by people who supported the systems. Not a perfect auditing system, but one that often was accepted by auditors.

However, the one thing missing in there, from my perspective, is version control. While I think it is important to track these scripts in a team of DBAs, I also think we want to ensure that as we grow and change these scripts, we know how and why. Junior people can learn from changes made by senior ones, and if a DBA alters one of these scripts and breaks something, just as a developer might refactor code and introduce a bug or break functionality. After all, these scripts are code.

If there is a problem, we want to be able to roll back, which means that we ought to save these scripts into a repository of some sort. While I like the idea of a share that all DBAs can access, I more like the idea of a (secure) Git repository that can be downloaded anywhere, provides a second backup, and can be audited over time. All of these are important features that any enterprise should implement, especially one that is regulated. We want to protect ourselves if a DBA gets hit by the proverbial bus.

I like collaboration, sharing knowledge, and tracking the work you do in a team. It’s important for raising the skills of everyone on the team and helping new members get up to speed quickly. This facilities consistent results, and if done using a tool like version control, helps ensure that your scripts are backed up in a way that preserves the knowledge in your code through any changes made by the team.

Steve Jones

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

March 28, 2022
Changing the Owner of a Database #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had an authorization issue with my account, and I decided to delete it and re-add it. That’s the subject for another day, but before I could delete it, I had to remove the ownership of some databases. You can’t delete a login that owns databases.

I realized I wasn’t sure how to do this, so I wrote this post.

A Deprecated Proc

There used to be a dbo.sp_changedbowner proc that was used, but I know this is deprecated and it shouldn’t be used. It likely would work fine in SQL Server 2019, but I also know there should be more modern code. I decided to look, as I ought to know what is recommended these days.

In searching around MS Docs, ALTER AUTHORIZATION comes up in the list. I checked, and this allows me to transfer the ownership of a securable, which a database is one of the items in the list. Example F shows what I want to do and uses this code:
```
ALTER AUTHORIZATION ON DATABASE::dbname TO [login]
```
I can replace dbname and login with the values I need.

Which Databases?

I have a lot of databases, and I don’t need to change them all, though I could. In my case, I decided to get a list of databases and owners. If you query sys.databases, there is an owner_sid column. If you join that with sys.server_principals, you can do so on the SID column. This query shows me what I need:
```
SELECT d.[name], sp.[name] FROM sys.databases d
  INNER JOIN sys.server_principals AS sp
   ON d.owner_sid = sp.sid
```
The results are here:

In some sense I hate that “sa” isn’t the default owner, but I get it. There might be a need for other accounts. However, my account is a sysadmin, so my view here is that “sa” ought to be listed.

I digress. Now that I have a list, I can limit it to my account with a WHERE clause. I can take that list of items and build the code. I could use a cursor, but this is a one-off task, so this works:
```
SELECT
                'ALTER AUTHORIZATION ON database::' + d.[name] + ' TO sa;'
              , d.[name]
              , sp.[name]

FROM
                sys.databases d
     INNER JOIN sys.server_principals AS sp
         ON d.owner_sid = sp.sid

WHERE          sp.name = 'ARISTOTLE\Steve';

GO
```
This gives me the code in the results I want to run. I copy paste this and I have a bunch of statements to run.

Despite Grammarly not being happy, this worked fine.

SQL New Blogger

As soon as I realized I needed to do this, I knew there were two posts here. One on the removal and adding back of my Windows account, and the second on this topic (when the first didn’t work).

This took about 15 minutes extra, finding the docs and writing some code, but it’s a good example of where a small situation that occurred helped me find something to write about. Easy for you to take little tasks like this and document your knowledge when you learn something.
March 14, 2022