Scripting Made Easier

I was recently asked to write a quick blog for a local Tech Group. You can read it here. I commented on some trends in SQL Server there. But I wanted to add a thought on a particular positive trend I’ve been seeing in the design of SQL Seerver itself.

As regular readers will know I’m a huge fan of PowerShell and have been using and writing about it more and more. But, I came across a requirement last week where PowerShell would have been overkill. Basically, my client and I had identified a number of databases that had the wrong owner. We wanted to change them over to the proper owner.

Now, being old school, my first reaction was to call sp_changedbowner. Any DBA who has been around for enough years has probably seen this and used it. But, if you look at the most recent webpage from Microsoft, you’ll notice a light blue box that has a warning:

 Important

This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER AUTHORIZATION instead.

Now on one hand, since this was a one-off script, I could have safely used sp_changedbowner. But there is an issue with it. It has to be run in the database you want to change the owner of. There is no parameter to specify the database. Now to change the owner on a single database, that’s not an issue. Go into the database and run the sproc.

But, when you might have a dozen databases, that becomes a lot of work.

Now, my first inclination for something like this is to write a query similar to:

select name, ‘sp_changedbowner ”newowner”’ from sys.databases where owner_sid != SUSER_ID(‘oldowner’);

This would generate a nice list, except that we’d still have to go into each database and execute the command. Now, I could get a little smarter and write something like:

select ‘use ‘ + name + ‘ sp_changedbowner ”newowner” ‘ from sys.databases where owner_sid != SUSER_ID(‘oldowner’);

This has the advantage of putting in the USE command so I can change databases, but I still would have to manually insert carriage/return linefeeds.

So it’s a slight improvement, but only a slight one. Now, I could resort to PowerShell, get a list of databases and loop through them with a ForEach, but as I said, I wanted to keep this simple as it was basically a one-off (well really a three-off since I had to do it on DEV, UAT, PROD) but the point was still the same, I wanted to keep it fairly simple and try to keep it all in T-SQl.

But, if we go back to the notice that Microsoft I quoted above, we end up with a better solution.  Alter Authorization lets you put the name of the database right in the command.  So, something like this will work quite smoothly.

select ‘alter authorization on database::[‘+name+’] to newowner’ from sys.databases where owner_sid != SUSER_ID(‘oldowner’);

This results in a column that I can then cut and paste into a new window and execute without having to make any edits. It’s also a solution I know will work in the future.

I’ll add a final way some DBAs might have approached this and that is with the undocumented stored procedure sp_msforeachdb. However, I try to avoid that as much as possible because it is in fact undocumented and honestly, I find the syntax a bit confusing at times.

Anyway, the real point I wanted to make in this post was not necessarily how to change the DB owner, but to make an observation. Over the years, I’ve noticed that Microsoft has been improving T-SQL and making tasks that once were unncessarily complicated easier to script out. It’s easier to change a DB owner now than it was to do so years ago. This is evident in other areas of SQL Server. There are many items that previously were easier done via SSMS or perhaps could pretty much only be done by SSMS that can now be done via script (and of course pretty much any action you can do in SSMS now has a “Script” option so you can script it out, modify it if needed and save it.)

So, my pattern lately has been, “script everything” and Microsoft has certainly been making that easier!

What you’re Doing Right Now…

… is important. And no, I don’t mean reading my blog. While it’s gratifying to me that you are reading it, that’s not exactly what I mean.

I’ve seen some memes on Facebook with the theme of “If you don’t come out of this lockdown with a new skill or having learned something it doesn’t mean you’ve lacked time, it means you lacked motivation.”

Now, for some, this may be true. I know I’ve taken time lately to attend several webinars and have recently finally gotten around to signing up for the Khan Academy (currently working my way through the circulatory system). But, and I want to stress this, EVERYONE is different.

Recently I got a Facebook message from a close friend, “Hey, can I call you?” We spoke for 45 minutes. Between the usual shit going on her life, just everything else was getting to her.

My wife and I are fortunate, our kids are older and can pretty much handle their own schooling on their own. And we both are still working, so finances are not a problem.

But, I know others, who for example have grammar school children at home who are suddenly thrust into becoming full-time teachers. I know others who have lost, or shortly will lose their jobs. They’re getting by day by day.

Some folks are taking walks to cope. Others baking (it’s one of my coping mechanisms, has been since college) to cope.

Some are just getting by.

But for all of us, what we’re doing right now is important, to us. So please don’t be quick to judge others for what they are or are not accomplishing. And, IF you can, be there for others. And if you need to, ask others for support.

We’re all in this together.

Social !Distancing

As some of my #SQFamily knows, Mike Walsh has been hosting a Friday afternoon Zoom “social hour”. It’s a chance for all of us who are social distancing to hang out, chat and basically socialize.

This past Friday, and then Monday night was a real reminder of how much I love my #sqlfamily.  Earlier in the day, my client asked me to come up with a script to back up some SSAS databases. A bit surprising to me, there isn’t the robust built-in support for this like there are for the normal databases. No problem some googling quickly brought me to an article on how to do it using PowerShell by Jana Sattainathan. Now as any of my readers know, I’m a huge fan of PowerShell, so this was a great solution. I had to tweak it a bit for my needs, but not much.  So I was already happy that someone else had done the work for me. For one thing it meant I could finish my day a bit earlier and get on the Zoom call with #SQLFamily.

Well, what do you know, but during the call as we’re talking Kevin Hill happens to mention he’s having trouble with a script to backup SSAS databases. I immediately chimed in, “hey, I’ve got a working script, let me email it to you!”  Ten minutes later he was up and running and contacted me back. Turns out he had found the same article by Jana but got hung up on the fact Get-ChildItem there needs DEFAULT for the $instancename when dealing with the default instance. As most of my readers would understand, usually if you’re referring to the default instance, you don’t need to explicitly reference it. I had encountered this issue writing one of my first articles for RedGate.

So I’d like to say I solved Kevin’s problem, but truth is it really goes back to Jana.

Meanwhile on the call, there was a discussion of the work being done by #SQLFamily for the @FoldingatHome project, being led by Glen Berry I believe. Others, led by Tim Radney are printing clips to help hold N95 masks on. (If I’m leaving out folks or getting names or credit wrong, please let me know).

Monday night we hosted our local SQL Server User Group meeting, but virtually. We had between 9-12 people on line at any time, which is not bad considering that’s about 1/2 of what we usually have in person. We had no specific topic, other than simply catching up on each other and how folks were dealing with the current crisis. Meanwhile I learned of at least one other virtual User Group meeting going on. Our #SQLFamily is still finding a way to share knowledge at this time.

So for all these reasons, I love my #SQLFamily (but still insist they stay at least 6′ away for now!)

Pushing Solutions, not Products

Earlier this week, the governor of New Jersey put out a call for more COBOL programmers. Everything old is new again. Last time I remember such a call was around the year 2000. That said, while I never had the opportunity to learn COBOL, I’m amused by this. It reminds me of a quote I heard in college about Fortran and how one expert didn’t know what language engineers would be programming in in the 21st Century, but they’d call it FORTRAN.

But, I highlight these two languages because the truth is, they are the exception. In reality one has to constantly keep learning. The times, they are a changing as a poet once said. Fortunately for me I’ve been busy during this Covid-19 lockdown, but even still I have free time (some who read my blog may argue too much time!) That said, I’ve been trying to take more time to catch some webinars and to learn new skills.

Over the past few weeks I’ve got a couple of SQL PASS WIT Webinars under my belt. Last week however, I took advantage of Redgate’s Streamed event. (full disclosure: Redgate does pay me for the articles I write for Simpletalk but what I write here is not paid for by Redgate in any way).

There were a lot of great webinars and I did not catch all of them, so please don’t take my lack of mentioning any as a comment on their quality. There were also some I could only listen to partly as I was actually doing work at the time.

First off, I started with Kendra Little‘s session using git for database development. I’m still moving in this direction and it gave me a good insight into what I’m doing right and moreover what I’m doing wrong and how to improve it. I recommend this session to anyone trying to get version control into their database development.

Unfortunately I had to split attention to Grant Fritchey‘s session on learning to effectively use Extended Events (I do have to do billable work from time to time) but did catch some good stuff. Again, if you haven’t played with Extended Events, please do! I recently used them to help debug an issue I was having with a client and their Reporting Server (yes! you can write them for an SSAS instance!) Go Team #ExtEvents.

Andy Mallon’s session on shortcuts for the DBA was excellent and seemed to generate the most feedback in the chat window. I suggest you go to his page and find his print-out for keyboard shortcuts for SSMS. It’ll save you a lot of time. That said, watch the video if you can and see how well Kendra Little did on her “job interview”. (To be fair, I suspect most of us would have done about the same!)

Steve Jone’s session on unit tests was good, at least what I caught of it. Again, client work got in the way. I may go back to specifically watch this one.

After that, I had time to catch Grant Fritchey’s session on SQL Injection. It still amazes me how many programmers STILL write code so susceptible to this. He had a lot of great examples and offered some solutions. Note there’s no single right answer, but there’s definitely a lot of lousy answers.

Friday brought Rob Sewell speaking about SQL Notebooks and using Jupyter. I haven’t used this yet, but it’s on my list for the year.

Again, a great presentation by Grant Fritchey, this time on convincing the DBA to support DevOps. I’m come back to this in a bit.

I think the highlight of Friday was the costumes. In honor of SQLBits which was postponed this year, several of the presenters wore costumes. I think Steve Jones, with his hat, wig, and glasses won in the pure costume category. (You’ll have to check out the videos). But, that said, Kendra took the overall prize with her corgi Freya on her back in a pack. There was just something so wonderful watching her talk about index tuning as she’d casually feed a carrot over her shoulder.

Again there were other sessions and speakers, and even if I didn’t mention them, their presentations were top notch and worth the watch. Again, you can go to: https://www.red-gate.com/hub/events/redgate-events/redgate-streamed/ and catch them o demand. I recommend it.

One of the overarching themes I picked up on was an emphasis on DevOps and using both tools and processes to achieve a successful DevOps environment. Note that I think both are critical. One can have all the best tools, but without good processes, not much will be accomplished. Honestly, one take away I got was I’d rather have good processes and develop my own tools than have tools, but no process. This focus makes sense given Redgates focus on DevOps.  I now in the past I’ve made the mistake of simply thinking of them as a company that sells some cool tools.

I want to close with saying, one thing I appreciate about the #SQLFamily and Redgate does this well, is generally members focus more on solving problems than pushing specific products. I’ve attended more than one webinar hosted by RedGate where other than mentioning them as a sponsor, their name hasn’t come up at all. I’ve seen other members of #SQLFamily do the same thing. They may work for a company that provides tools and solutions, but if you use #sqlhelp on Twitter, you’ll find almost always it’s people there are about solving your problem, not pushing their software or solution.

So that was how I spent part of last week in lock-down. How about you?

P.S. I also made some boule bread to with the homemade chili on Saturday. It was a winner in the Moore House Hold.