Voice of the DBA

Category: Blog

The 10 Day Cleanse
Disclosure: It hasn’t been 10 days, but I wanted to write something at this point, after 7 days. I know some of you are trying to be more healthy, so I’m posting some thoughts.

Let me cut to the chase, and then some thoughts. First, this is a week for me with no coffee, no alcohol, no soda, and almost no food. I’m amazed I got here. I’ve lost 12+ pounds from when I started.

I’ve been doing the Green Smoothie Cleanse with my wife. For the last seven days, I’ve been subsisting on
1. green smoothies (I get 64oz/day)
2. decaf tea (green and detox)
3. carrots
4. grapes
5. nuts
6. hard boiled eggs
7. peanut butter (and cashew butter, which is really good)
8. water
That’s it. I drink about 4 of these a day:

And these smoothies, usually in 3-4 chunks.

Lovely, huh?

I’m hungry often, but mostly I’m unsatisfied. It’s a mixed bag for me. I’ve lost weight. I think I’m slimmer and healthier, but I also think about food. A lot. All the time.

I also think about how I think about food. I realize it’s comfort for me, and probably need to take less comfort and be more careful.

It’s been an interesting week. Three days to go and then I’ll be back on a semi normal diet. There is a transition with one smoothie and then better eating. I’ll try that a bit, but I spend 10 days traveling for work, which will be tough. At least I’m more aware of what I eat and what I can tolerate with regards to hunger.
February 1, 2017
Disabling #sqlprompt Formatting
I love SQL Prompt, especially the new formatting engine. However, it’s not perfect, and there are times I don’t want code reformatted. One great example is when I write INSERT statements. Here’s what I might write:
```
INSERT dbo.Payments_A
  ( [Month], SerialNumber, DateBegin, DateEnd, paid)
VALUES
  ( 'Mar-15', '0000000000001', '3/16/2015 0:00', '4/10/2015 0:00', 5000.01),
  ( 'Apr-15', '0000000000001', '4/7/2015 0:00' , '4/13/2015 0:00', 0),
  ( 'Apr-15', '0000000000001', '4/10/2015 0:00', '4/30/2015 0:00', 15000.00);
```
I often build a single row, then I might copy/paste to add the other rows and then manually change the data. When I use my reformat command (CTRL+K, Y), I get this:
```
INSERT dbo.Payments_A
(
    [Month],
    SerialNumber,
    DateBegin,
    DateEnd,
    paid
)
VALUES
(
    'Mar-15', '0000000000001', '3/16/2015 0:00', '4/10/2015 0:00', 5000.01
),
(
    'Apr-15', '0000000000001', '4/7/2015 0:00', '4/13/2015 0:00', 0
),
(
    'Apr-15', '0000000000001', '4/10/2015 0:00', '4/30/2015 0:00', 15000.00
);
```
That’s not bad, but it’s hard to read and ends up eating up a bunch of screen space when I try to look at code. What I really want is for that INSERT statement to not be reformatted, even though I may want the spacing and line feeds in other code.

Fortunately, SQL prompt allows me some control. If I highlight my code, I get a little box to the left of the code. There’s a hand with a finger pointing at it in the image below.

If I hit CTRL, I get a drop down (or I can click). In this box, I can type snippets, or words. Note I’ve typed “dis” below, and I see that one of my options is to disable formatting for the selection.

If I select this, I’ll get comments added to my code that SQL Prompt can read.

Now I can hit CTRL_K, Y, and I’ll get all my code formatted, except what’s inside of the comments.

Watch this and a few more SQL Prompt tips from a short video shown at last year’s SQL in the City:
January 31, 2017
#ReadyRoll at #SQLintheCity

When Redgate purchased ReadyRoll a few years ago, I wasn’t sure this was a great idea. After all, SQL Source Control and DLM Automation work really well to bundle up changes in a VCS and easily deploy them.

However, as I worked with Daniel Nolan, the creator, and learned more about the tool, the more I liked it. After all, I’ve worked for a number of companies that used a migration strategy to make changes to our applications. I also like Visual Studio, so this was a great fit for me.

At SQL in the City streamed last year, I presented a short talk on using ReadyRoll to deploy changes. I’ll write more about individual parts of this in 2017, but take a look at this video and see if ReadyRoll works for you.

January 30, 2017
Finding Sysadmins with dba tools–GetDbaRoleMember

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

I’ve been wandering through the dbatools set of cmdlets, trying to see where various cmdlets are useful, and also practicing some PoSh skills.

Recently I noticed there was a Get-DbaRoleMember cmdlet to use. Of course, when I tried to check it, I found an issue. I didn’t have the cmdlet in my system. Autocomplete didn’t find it and running the cmdlet returned an error. I assumed that there had been a dbatools update, and I hadn’t gotten it.

I run into this before, and a quick query to @sqlvariant helped me realize I needed to run update-module, not re-run import-module. I did that and it worked smoothly (I did need to be an administrator to update this).

One I did this, I could run the cmdlet and get data.

Interesting, but this doesn’t seem incredibly useful. After all, I can easily query this in T-SQL, and my monitoring software will check to see if roles change.

However, perhaps I am actually going to write something that checks to see if we have consistent sysadmins on all instances. Or perhaps we’re looking to add a sysadmin to an instance where he/she doesn’t exist. In any case, PoSh is a way to easily connect to multiple instances on many machines in a way that’s more cumbersome in T-SQL or SQLCMD.

I can get the server level roles with –IncludeServerLevel parameter. When I do that, I see my roles and members.

I can also connect to remote servers and get this:

How can I do this in bulk? Well, I can certainly run this multiple times with a list of instances. Let’s make a quick list. I’ll make a quick array:

$instances=”.\SQL2016″,”Atlas”,”Atlas\SQL2016″

Now I’ll use that in a foreach loop, sending the server name to the cmdlet and looking for sysadmins.

And I have the sysadmins on all of my instances. Certainly this isn’t terribly useful by itself, but I can easily add more programming that looks for a user, maybe checks for which machines have a sysadmin, maybe add (or remove) a sysadmin from instances that are out of sync with what I need.

This isn’t one of the cmdlets I’d use the most often, but I can see it being handy when trying to automate some of the permissions checks across multiple machines that I need to keep in sync. At the very least, this could be helpful in comparing permissions between primary and secondary (or DR) nodes.

Note: I haven’t tried it, but I suspect that the migration cmdlets at dbatools would be better for keeping primary and secondaries in sync.

January 27, 2017