Voice of the DBA

Tag: administration

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
The Time to Patch

Microsoft has spent years working on building a reliable and dependable patch process for their software. While some products have had more sporadic updates, SQL Server has moved to a fairly regular schedule. Not quite a predictable “Patch Tuesday” schedule, but you can count on a CU arriving every month or two for SQL Server.

Most people don’t patch every month, but slowly customers are getting used to regular patches for SQL Server. Microsoft would prefer you use one of their “evergreen” releases, where Microsoft is in control of patching. Azure SQL Database, and Managed Instances are handled this way, but with Azure Arc, you might deploy these inside of your data center and not worry about patching anymore.

Most of us won’t get there anytime soon, and we will need to patch our instances. This week, I’m wondering about your patching process. Not whether you patch or not, but rather the extensiveness of your patching when you do decide to apply a CU.

If you decide to patch a particular version in your environment (2016, for example), how long does it take you to patch all your SQL Servers? Do you even get all systems patched, or are there always lingering systems that can’t be updated because of some dependency?

Maybe one other question might be how long does it usually take you to decide to patch your systems to some level? Or do you just randomly patch instances as needed?

I am a big fan of leaving systems alone that are running well, but it seems the quality of patches from Microsoft has improved over the years. I’m not quite sure I’m at the point where I want to patch everything to month a CU is released, but I do think a regular process is a good idea, and hopefully it’s one that completes all instances for a version in less than 30 days.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

January 3, 2020
Batch Scripting SQLCMD–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wouldn’t do this anymore, but I ran across a post where someone couldn’t use PowerShell in their organization. A poor decision, IMHO, for the Microsoft platform, but it is a restriction. In this case, the user wanted to get a batch file to run a SQLCMD script. This post shows how.

Two Files

Let’s suppose I have two files in a folder. In this case, I have a version.sql file that contains this:
```
select @@version
```
The other is my batch file, which I’ll name runsql.cmd. In this file, I’ll do a few things. First, suppress the code with this:
```
@@echo off
```
That’s just a good habit, though you might leave this out until things are working. Now, I will use a loop to get a list of files with an extension. I use the FOR loop in this way:
```
for %%x in (*.sql) do (
```
This gets a list of all .sql files in the current folder. For each one, we will process all statements inside the parenthesis. The open is on the line above, the close will be below.

The next lines are my sqlcmd call and the various items I need. In my case, I’ll get the instance name as a parameter and use trusted authentication.
```
  sqlcmd -S "%1" -E -i %%x

)
```
I could use other parameters (%2, %3, etc.) to get a user and password if I wanted to. Instead, I’ll get the instance as a parameter, and then pass the filename in to sqlcmd with the –i parameter.

When I run this, with my single .sql file, I see this:

Easy to do, and I could add other .sql files in here if I wanted them to run.

SQLNewBlogger

This was a quick post to write in answer to someone asking a question. I knew about the %1, %2, and searched to find a quick SO post on getting filenames into a variable. It actually took about 5 minutes to research and test (and post) and then about 10 minutes to write this up.

You could do this, showing some knowledge of learning and creating a solution. For extra credit, how can I capture output of this?
November 13, 2019