Voice of the DBA

Tag: SQLNewBlogger

Dropping a Database Now–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the things I find myself doing often with demos is dropping databases. I may create and drop databases often to try some technique out or practice a skill. I’ve done this for years, going back to SQL Server 4.2, using small databases as quick lab spaces.

A common problem for me is that often a database is in use, often because of another connection from ADS or SSMS. This is actually a common problem for many people in teams as well, when someone else might be holding an open connection. If you’ve tried to run a simple DROP DATABASE, you know this doesn’t work. In the SSMS GUI, we have a “Close existing connections” option, but nothing with the DROP DATABASE command.

The best solution I’ve found is on StackOverflow. Set the db to single user, which allows you to use the WITH ROLLBACK IMMEDIATE option. This terminates any existing connections and rolls back their transactions.

Here’s the syntax:
```
ALTER DATABASE ClientDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
```
If I do that, I can then run this, without any issues.
```
DROP DATABASE ClientDemo
```
SQLNewBlogger

This is a fairly simple task, and one I’ve done often. I’ve never written about it, mostly because I never took the time. Solving this issue was something that took about 5 minutes of Internet research. I had to check that the DROP command hadn’t added the option. I know this has been discussed, but apparently, never added.

In any case, since I had to do this for work, I made a new notes, snapped a screenshot, and saved a few links. I do this with OpenLiveWriter. Later I took 10 minutes and typed this up, including this section on how and why.

This is a quick way to show some knowledge, problem solving, cement learning, and give the next interviewer something to ask you about. Give it a try.
April 27, 2020
Cleaning Up Registered Servers

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

I was working with Get-DbaRegServer from dbatools and got some errors. When I dug through the errors, I noticed some interesting items listed in my server list. This is what I saw:

While all the servernames are valid, the names are a little silly. I initially thought some of these were the reason for my errors with the cmdlet, but they weren’t. Still, I want to remove a few of these.

I can likely find these in the registry or elsewhere, but there’s an way way to see this: SSMS. Under the View menu, there is a Registered Servers item.

Clicking this will get me a new pane, where the Object Explorer lives.

If I expand this, I see a few categories, including my Azure Data Studio (ADS) connections and groups. It’s a nice way to see what’s registered.

For the Local Server Groups for SSMS, I can highlight an instance and click “delete” to remove it. You get a confirmation dialog, but it’s removed. I can this in PoSh. Instance001 and 002 are gone.

I can’t, however, remove the ADS ones. I get this with a right click (nothing with Delete).

The integrations between SSMS and ADS do not extend to altering the connections.

Simple stuff, but handy to have around, especially over time as you add lots of registrations over time.

SQLNewBlogger

This took about 3-4 minutes to figure out as a part of other things I was doing, but I made a few notes and then spent 10 minutes writing this up and taking screenshots.

This is a good place to start blogging if you want to get going.

April 13, 2020
Getting Your dbatools Version–#SQLNewBlogger

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

I was trying to test something recently and it didn’t work. One would think that I would remember to check versions, but I didn’t before posting on Twitter. I did get a response from @dbatools, and one of the questions was which version did I have.

I wasn’t sure, and wasn’t sure how to check, so I had to do some Googling around. This post notes what I found and how I got the version.

I have known that I can get the PowerShell version from $psversiontable. I can run that and see info about PowerShell..

This doesn’t get me a view of modules I’ve installed. I tried Get-Help, but it wasn’t what I wanted.

I then tried a search and stumbled upon Get-InstalledModule, which worked well, but it has a lot of info. When I run this, I get lots of data.

Fortunately, it’s alphabetical and I don’t have a lot of modules that are named with something after “d”, so I could easily find the data.

I know there’s an Update-Module and Install-Module, so I tried Get-Module. That actually works. I thought about this later, after I’d actually gone through the searching and posting results.

As with anything, there are a couple methods here, but certainly it’s good to be able to solve a simple problem like this one.

SQLNewBlogger

This was something I figured out in about 2 minutes. Writing this post took longer, but I bet I now remember this. It also might be something I get asked in an interview.

Try it yourself today. Start a blog and start documenting what you learn.

April 6, 2020
Installing PowerShell for SQL Server – #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

PowerShell is the hot new scripting language for working with processes outside of an application. It’s cross platform, and it has a lot of capabilities for working with SQL Server. The way that this gets installed has changed, so this is a quick post to ensure others know how to do this.

Running a Command

I know there is an Invoke-SqlCmd cmdlet in PowerShell. On a new laptop, I tried to run it and had issues.

SQLPS is the old module, and SqlServer is the new one. The error message says to try importing the module, so let’s do that.

That doesn’t work either. Hmmm. I think I need to install this. To do that, I need an elevated command prompt. I’m using ConEmu, and I can restart this session as an admin. Or you can use the start menu to find cmd and start this as an admin:

Once you have an elevated prompt, start PowerShell and then run this:
```
Install-Module SqlServer
```
This will install the module if you answer the prompts correctly. I had to use the -AllowClobber parameter as I had some conflicting things installed from dbatools. I’ll likely update those after this with their own AllowClobber.

Once this is done, you should be able to use the cmdlets. First you import the module with this:
```
Import-Module SqlServer
```
Then you can run code:

SQLNewBlogger

After getting a new laptop, I needed to set a few things up. PowerShell was one of those. As I started to do this, I grabbed screenshots to document the process for my blog. I then built this post in about 10 minutes.

You could do this as well, to round out the knowledge you gain as you do something similar, and show you’re familiar with these concepts.
December 27, 2019