Voice of the DBA

Tag: SQLNewBlogger

Adding Analysis Services (SSAS) to your SQL Server instance–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is a fairly simple task, but recently I needed to test something in SSAS. I hadn’t installed this on my machine, so I had to add it. It’s simple, but worth a quick post.

The way to add SSAS (Analysis Services) to your instance is to run the setup program for SQL Server. When you do this, pick the Installation tab from the left menu.

From here, I want the first item (New SQL Server stand-along installation or add features to an existing installation). SSAS is a new feature.

When this starts moving, be sure you stop on the Installation Type screen and move the radio button to the “Add features” item.

The next screen is where you will check the Analysis Services box.

You will then get an SSAS config screen. In my case, I was testing something with the Tabular model. If you don’t know which to choose, you need to research this. Here are a couple links:
- BOL
- MSSQLTips
You also need an account here. I typically add my local account here for dev machines.

Don’t forget to reset the data directory if you need to do this.

That’s it. Let the setup run and you’ll have SSAS installed.

How do you check? That’s another post.

SQLNewBlogger

This is a really simple task, but it’s something that you should be familiar with. I needed to do this, and just documented my task, this one simple thing.

This can drive interviewers to ask about how I did this, why, what I tested, etc. This helps control the interview and the direction in which someone might query your knowledge.

As you do small tasks, write about the specific task. If you want to cover what you did after this task, write a second post.
May 13, 2020
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