About Greg Moore

Founder and owner of Green Mountain Software, a consulting firm based in the Capital District of New York focusing on SQL Server. Lately I've been doing as much programming as I have DBA work so am learning a lot more about C# and VB.Net than I knew a couple of years ago. When I'm not in front of a computer or with my family I'm often out caving or teaching cave rescue skills.

“It’s a Jump to the left…

… and then a double-hop to the right.” Or something like that.

I’ve commented before on the fact that I’m a consultant. I enjoy it. People will ask me what I do, and it varies. At one client they refer to their VB app as “the database” and because they found an ad of mine on Google where I talked about database administration, they hired me. About 80% of the work I do for them is actually on the VB app or related, very little is actually what I’d consider traditional database work. But that’s ok, they’re a pleasure to work with and I enjoy the work. Another client I recently worked with, asked me to help them conduct an audit of their web based product and help them with some steps to make it more secure. I was more than happy to help.

And then there’s my largest, by far, client. I actually get to do a fair amount of work that most of my #sqlfamily would recognize as “database work”. But there, perhaps more than any other, I describe my duties as “DBA and other duties as assigned.”  So between the work at this client and all my other clients, I’m often jumping or stepping around stuff.

The Double-Hop

This time though I was asked to double-hop. What is that exactly? It’s an issue that has to do with how Windows can pass security credentials from one server to another. This article, while old, describes it well. This was essentially the situation my client was trying to solve: Users needed to use their Active Directory (AD) Credentials to log into the Reporting Server (RS_Server) and run a report that in turn accessed data on a separate database server (DB_Server), and thus, the double-hop. Now, from my point of view, this isn’t really database work, but since the reporting server talks to the database server it was dropped in my lap under “other duties as assigned.”

Now, honestly, this SHOULD be simple to solve. It wasn’t. One reason was in part because, like many companies, this client has a separate team that handles much of their infrastructure needs, such as AD requests. And they have to go through tickets. To be clear, I support this concept, in theory. In practice, it can often take 2-3 weeks for even simple requests to go through. This meant that my first attempt at solving the double-hop failed. Their IT department did exactly what I requested. Unfortunately there was a typo in my ticket. So it failed. So round two. And round two didn’t work. Nor did round three. At this point though it wasn’t due to typos or mistakes on my end.

I started reading every article I could. My great editor at Red-Gate, Kathi Kellenberger has one, and trust me I wasn’t too shy to ask at that point! But nothing was working. I even asked another DBA at the client (they actually head up a different group and is their lead DBA). She pointed me to one of her people saying, “talk to him, he solved it.” I did, and he hadn’t. His solution was the one we were trying to avoid (basically using a fixed user in the datasource).

Frustration Sets In

I was getting frustrated. Fortunately at this point I started to exploit a loophole in the ticketing process. Since the problem wasn’t being fixed, I was able to keep it open and ended up getting assigned someone from their IT group who was as interested in fixing this as I was. This meant rather than “open a ticket, wait 1-2 weeks, have ticket be closed as complete, test, find out it failed, rinse” we could now actually schedule Zoom sessions and make changes in real-time. AND…. nothing we tried worked.

At this point you’re probably saying, “Yeah, yeah, get to the point. Did you solve it?”  The answer is yes, but I wanted you to feel a bit of my pain first, and I needed to make this post long enough to make it worth posting.

A Solution!

Now, let me say, I wish I could write out an exact recipe card solution for you. For various reasons, I can’t. But bear with me.

Finally, we found an additional resource in the IT group who had solved this before. His first recommendation was yet again, the solutions I mentioned above. He saw they didn’t work, agreed we were trying the right thing. So he said, “well let’s try a solution known as “Resource-based Kerberos Constrained Delegation“. This didn’t work at first either.

But then he suggested that we turn on 128 and 256 bit encryption on the DB_Server SQL account. Bingo that worked. Mostly. More on that in a second.

So here’s the setup we ended up with.

  1. RS_Server – running reporting services under an account domain\RS_Server_Service
  2. DB_Server – running SQL Server under an account domain\DB_Server_Service
  3. Setup some SPNs
    1. MSSQLsvc/DB_Server domain\DB_Server_Service
    2. MSSQLsvc/DB_Server.domain.com domain\DB_Server_Service
    3. Note in this case you do not appear to need one on the RS_Server side.
  4. Run a Powershell script
    1. $FEIdentity1 = Get-ADUser -Identity domain\RS_Server_Service
    2. $BEIdentity = Get-ADUser -Identity domain\DB_Server_Service
    3. Set-ADuser $BEIdentity -PrincipalsAllowedToDelegateToAccount $FEIdentitity1
  5. At this point things should have worked, but they didn’t until we then enabled the encryption options:

    encryption options for Kerberos

    Kerberos Encryption

  6. Then on our test box, things magically worked! Ok, not quite magically, but things worked. We had a solution.

And I was even more ecstatic when later that day, I tested this on a second report server box we had and it too suddenly was working without any changes. And this was a box where we had NOT even setup an SPN for the original double-hop solution, so I was pretty confident that the Resource-based Kerberos Constrained Delegation was working. In addition, in the rsserver.config file, the only authentication enabled was NTLM.

The next step was to try this on a production server. In that case, I did have to reconfigure the service it was running under to use the domain account domain\RS_Server_Service.

And… my test failed.

I was at wit’s end. I couldn’t quite figure out what was different. I checked my service names, my SPNs, the rsserver.config file, and more. Nothing was working. I took a break and came back and had an idea. In the datasource I changed it from:

Data source=DB_Server;Initial Catalog=TestDB

to

Data source=DB_Server.domain.com;Initial Catalog=TestDB

Bingo, it worked! A little digging confirmed my suspicion. This client actually has multiple DNS domains and the ordering and like under the TCP/IP settings was different on this box from the other two boxes. And that made the difference.

Sure enough when I tried deploying to a fourth box, I had the same issue, but changing it to the Fully Qualified Domain Name (FQDN) solved my issue.

So, my take-aways for this week:

  •  Resource-based Kerberos Constrained Delegation may be a better solution at solving the Double-Hop solution than the solution generally proposed.
  • Once you’ve setup the “target” SQL Server service account and source Reporting Server Service accounts, additional reporting servers can be added to the mix without needing assistance from a domain admin.
  • It appears you still need an SPN (well multiple) for the SQL Server itself.
  • You need to run a PowerShell Script to setup the accounts. Note that if you run it again, it overwrites the old settings, so you need to add ALL of the source accounts in a single step.
  • Depending on your domains security setup, you may need to enable 128/256 bit Kerberos authentication.
  • DNS resolution may determine if you can use just the NetBIOS name or the FQDN in your data sources.
  • This solution will NOT work if you need to cross domains or have more complex setups, but in general, it can be simpler to setup and to maintain, especially if you have limited access to making changes to AD in the first place.
  • My reading indicates this only works on Windows 2012 and beyond. But you shouldn’t be running older versions of Windows in any case!

If I get the time and energy, I may setup a test environment in my home lab to further experiment with this and write up better demos, but for now, use this as you can. Hopefully it’ll save you some of the stress I experienced.

And that’s it from here, back to other duties as assigned.

 

 

The Customer is Always Right?

You’ve heard this adage before. Some often believe it. And honestly, there’s a bit of truth to it. But the truth is, the customer pays your bills and if they stop paying your bills, they’re no longer your customer.

I was reminded of this actually during the testimony of Dr. Fauci before the Senate a few weeks ago. To be clear, neither the Senate nor the CDC is a customer here, nor is the President of the United States. But, I think it ties into the thesis I want to make.

Over the past few months there’s been a lot of discourse over whether states should shut down, for how long and how and when they should open up. At the extreme ends you have folks who seem to argue for a continual shutdown to save as many lives as possible and the people who seem to argue that the economy is far more important and that any shutdown is a bad idea.

Dr. Fauci has been accused of wanting to “quarantine the entire country” and is the subject of a hashtag campaign, #faucifraud. During the Senate hearing, Senator Rand Paul took several swipes at Dr. Fauci including a statement implying that some people might treat Dr. Fauci as the end-all. Finally, with only 32 seconds left, Dr. Fauci gave his reply:

“Well, first of all, Senator Paul, thank you for your comments. I have never made myself out to be the end all and only voice in this. I’m a scientist, a physician, and a public health official. I give advice, according to the best scientific evidence. There are a number of other people who come into that and give advice that are more related to the things that you spoke about, about the need to get the country back open again, and economically. I don’t give advice about economic things. I don’t get advice about anything other than public health. So I wanted to respond to that.”

I think this was an incredible reply and one that I think behooves any consultant to keep in mind. Dr. Fauci politely but firmly refutes Senator Paul’s comment about being the end all and then points out what his qualifications are. He then suggests that there are other experts, in other fields, who should be consulted. He then reiterates the limitations of his advice.

As a consultant, this mirrors my own experience. A client may ask me to recommend a HA/DR strategy for them. I might go ahead and recommend some sort of Always On Availability Group with multiple synchronous replicas in one data center and then an asynchronous replica to a second data center. I might then recommend daily backups to tape with the tapes taken off-site. Everything would be encrypted and we would test failovers on a regular basis. With that, the proper selection of hardware, a proper deployment setup, and a completely developed runbook for various scenarios, I could probably guarantee them nearly perfect uptime.

Then, the CFO steps in and points out that their budget is only 1/20th of what I had planned around and that trying to spend more would bankrupt the company.

Then the VP of Sales points out that the business model of the company is such that in reality, they could operate for several hours of downtime and while it might hurt business a little bit, it wouldn’t bring them to a halt.  In fact, they suggest that the order system just be done with a bunch of Excel spreadsheets that accounting can true up at the end of the month. After all, they just want to focus on sales, not on entering data into the system.

Finally the CEO steps in. They decide that it’s true, the company can’t afford a 24/7 HA/DR setup that is the envy of NASA, at least not at this time. Nor do they think the VP of Sales plan has much merit since it won’t allow future growth into online sales and while it might be easier for the salespeople to just jot down stuff, it would mean hiring more people in accounting to figure out the data at the end of each month.

Instead, they direct the CTO to work with all the parties involved to develop a system that can have 3 hours of downtime, but costs no more than 1/10th of what I proposed, and  that also incorporates features that allow them to move to a more advanced HA/DR setup down the road and also will eventually allow for online sales.

So who was right? Me at my extreme of a huge investment in hardware, licenses and resources, or the VP of Sales who wanted to do the whole thing using some Excel spreadsheets.

Both and neither. Either of our ways would have worked, but neither was the best solution for the company.

I think good experts realize exactly where their expertise begins and ends. My role as a consultant is to provide the best advice I can to a company and hope that they take my advice, at least as it is applicable. And I should understand that every situation is different. In these cases, the customer is always right. Their final decision might not be what recommended, but ideally they’ve taken it into consideration.

Finally though, I have to recognize that there are situations where I may have to withdraw myself from the situation. In the above scenario, I crafted a situation where compromise is not only a viable option, it is perhaps the best option. But there are times when compromise is not an option. If a potential client came to me and they were dealing with PII data and refused all advice in regards to encrypting data and other forms of data security, it would be in my best interests to simply say, “here, the customer is wrong” and recuse myself. So in some cases, the customer may be outright wrong, but they should also stop being my customer at that point and would no longer be paying me.

So, do we re-open the country completely or do we shutdown everything until the fall? Honestly, I’m glad I’m not the one making that decision. I don’t think there’s a single answer for every community. But I think the best leaders will take into account the best advice they can from a variety of experts and synthesize the best answer that they can and adjust it as more data and experience come to light. It’s simply not practical to prevent every possible COVID-19 death. But it’s also not ethical to re-open without a plan or even thought as to the impact.

Neither extreme is fully right.

 

Advanced Braining

I’m currently reading the tome The Power Broker by Robert Caro. For those not familiar with it, it’s the Pulitzer Prize winning biography of Robert Moses. “Robert who?” you may be asking? Robert Moses, perhaps more than any single person literally shaped New York City in the mid-20th Century. Due to his power, he was responsible in NYC alone, for getting the Triborough Bridge, Brooklyn-Battery Tunnel, West Side Highway, Cross-Bronx Expressway, and many other large scale projects built. He outlived a number of borough presidents, mayors, governors and even Presidents. Arguably, for decades he was the most powerful man in NYC, at least in terms of how many was spent and what projects were completed. In many ways he was a visionary.

However, as the chapter I’m currently in discusses, he also could be extremely short-sighted. I’ll come back to that in a moment.

In the past week, several small incidents occurred in my life. Separately, they don’t necessarily mean much, but taken together, I realized there was a little theme associated with them.

Last Tuesday I posted an update on my dryer repair and an issue at one of my clients. I described the work incident as an example of the normalization of deviance. A few hours later, someone I’ve known for decades, originally online, but have since met in person, Derek Lyons (who has a great blog of his own on anime, a subject about which I know nothing) posted a reply to me on Facebook and said he had read my article, liked it, but thought I was wrong. I was intrigued. You can see his comment and my reply at the bottom of last week’s post. The general point though is I think he showed my thinking was incomplete, or at least my explanation was. In either case, it made the overall article a better one.

Then on Wednesday, my editor at Redgate, Kathi Kellenberger  emailed me with changes to my most recently submitted article. One of the changes was to the title of the article. Now, I’ve come to value Kathi’s input, but I wasn’t keen on the title change, so I suggested something different. She wrote back and recommended we go with hers, How to Add Help to PowerShell Scripts because she said “How to…” generates more hits on search engines and in fact a previous article of mine How to Use Parameters in PowerShell was one of their most read articles at the time (106K hits and climbing). I went with her advice.

Yesterday, a friend contacted me. He was in the middle of doing grading for his students and the numbers on his Excel spreadsheet weren’t quite making sense. The errors weren’t huge, but just enough to make him go “hmmm”. So, he reached out to me to take a look. After a few minutes of digging I understood what was happening and able to write back to him and give him a better solution.

All these have something in common: the final product was better because of collaboration. This is a common theme of mine: I’ve talked about the chat system I use at RPI, I’ve talked about making mistakes. In general, I think that when trying to solve a problem, getting additional input is often valuable.

So back to Robert Moses. In the early part of his career, before his efforts focused mostly on NYC itself, he was responsible for other projects, such as the Northern State Parkway and the Southern State Parkway and Jones Beach on Long Island. He started his career in a time when cars were mostly a vehicle of the well-off and driving a parkway was expected to be a pleasant experience (hence the name). His efforts were built around more and more parkways and highways.

By the 1950s though, it was becoming apparent to most everyone else that additional highways actually generated more traffic than they routed away from the area surface roads. What was originally considered a blessing in disguise, where a bridge, such as the Triborough would quickly generate more traffic (and hence more tolls) than expected, was soon seen as a curse. For every bridge or tunnel built in or around NYC, traffic increased far more than expected. And this came at a price. Urban planners around the country were starting to see the effects. Efforts to build more bridges or highways to ease traffic congestion were actually creating more. Even in NYC as Moses was planning for his next large projects opposition was slowly building. However, Robert Moses was blind to the problem. By the 1950s and 60s he had so surrounded himself by “yes men” that no dissidence was permitted. In addition, opposition outside of this offices was silenced by almost any means Moses could use, including apparently the use of private detectives to dig up dirt on opponents.

In the current chapter I’m reading, Caro, the author, details exactly how much money the Triborough Bridge Authority (which was in practice, though not theory, under Moses absolute control) and the Port Authority had available for upcoming projects, including the planned Verrazzano-Narrows Bridge. He goes on to explain how badly the infrastructure of the NY Subway system and the LIRR had fallen into disrepair. Caro suggests how much better things could have been had just a portion of the money the TBA and PA had at their authority had been spent on things like the Second Avenue Subway (something that is only now coming to fruition and will take possibly decades more to complete). Part of the issues with the subway system can be lain directly at the feet of Moses due to earlier efforts of his to get the city to fund his other projects. The issues with the LIRR however were more an indirect result of his highway building out into Long Island.

I suspect some of Caro’s claims are a bit idealistic and would have cost more than the projections at the time (like most projects) and while I think most of the projects he touches upon probably should have been built in the 50s (the Second Avenue Subway being one of them and the LIRR East Side Access being another) they weren’t because of a single man who brooked no disagreement and was unwilling to reconsider his plans.

Robert Moses was a man who got things done. Oftentimes that’s a good thing. And honestly, I think a number of his achievements are remarkable and worthy of praise.

But I have to wonder, how much better of a city could New York be, had Robert Moses listened to others, especially in the 1950s and 60s.

Today’s takeaway? Take the time to listen to input and ask for it. You may end up with a better solution in the long wrong.

 

 

 

Quiet Time and Errors

I wrote last week about finally taking apart our dryer to solve the loud thumping issue it had. The dryer noise had become an example of what is often called normalization of deviance. I’ve written about this before more than once. This is a very common occurrence and one I would argue is at times acceptable. If we reacted to every change in our lives, we’d be overwhelmed.

That said, like the dryer, some things shouldn’t be allowed to deviate too far from the norm and some things are more important than others. If I get a low gas warning, I can probably drive another 50 miles in my car. If I get an overheated engine warning, I probably shouldn’t try to drive another 50 miles. The trick is knowing that’s acceptable and what’s not.

Yesterday I wrote about some scripting I had done. This was in response to an issue that came up at a customer site. Nightly a script runs to restore a database from one server to a second server. Every morning we’d get an email saying it was successful. But, there was a separate email about a separate task that was designed to run against that database that indicated a failure. And that particular failure was actually pretty innocuous. In theory.

You can see where I’m going here. Because we were trusting the email of the restore job over the email from the second job, we assumed the restore was fine. It wasn’t. The restore was failing every night but sending us an email indicating a success.

We had unwittingly accepted a deviance from the norm. Fortunately the production need for this database hadn’t started yet. But it will soon. This is what lead my drive to rewrite and redeploy the scripts on Friday and on Monday.

And here’s the kicker. With the new script, we discovered the restore had also been failing on a second server (for a completely different reason!)

Going back to our dryer here, it really is amazing how much we had come to expect the thunking sound and how much quieter it is. I’ve done nearly a half-dozen loads since I finally put in the new rollers and every time I push the start button I still cringe, waiting to hear the first thunk. I had lived with the sound so long I had internalized the sounds as normal. It’s going to take awhile to overcome that reaction.

And it’s going to take a few days or even weeks before I fully trust the restore scripts and don’t cringe a bit every morning when I open my email for that client and check for the status of overnight jobs.

But I’m happy now. I have a very quiet dryer and I have a better set of scripts and setup for deploying them. So the world is better. On to the next problems!

Checking the Setup

A quick post outside of my usual posting schedule.

I was rewriting a T-SQL sproc I have that runs nightly to restore a database from one server to another. It had been failing for reasons beyond the scope of this article. But one of the issues we had was, we didn’t know it was failing. The error-checking was not as good as I would have liked. I decided to add a step that would email me on an error.

That’s easy enough to do. In this case I wanted to be able to use the stored procedure sp_notify_operator. This is useful since I don’t have to worry about passing in an email address or changing it if I need to update things. I can update the operator. However, the various servers at this client had been installed over a several year period and I wasn’t sure that all of them had the same operator configured. And I was curious as to who the emails the operators went to on those machines.  Now, I had a decent number of machines I wanted to check.

Fortunately, due to previous work (and you can read more here) I have a JSON file on my box so I can quickly loop through a list of servers (or if need be by servers in a particular environment like DEV or QA).

$serverobjlist = Get-Content -Raw -Path “$env:HomeDrive$env:HomePath\documents\WindowsPowerShell\Scripts\SQLServerObjectlist.json” | ConvertFrom-Json
 
foreach ($computername in $serverobjlist.computername)
{
$results = Invoke-Sqlcmd -ServerInstance $computername -query “select name, email_address from msdb.dbo.sysoperators”
write-host $computername $results.name $results.email_address
$results = Invoke-Sqlcmd -ServerInstance $computername -query “select name from msdb.dbo.sysmail_profile”
write-host $computername $results.name `n
}

This gave me a list of what operators were on what servers and who the emails went to. Now if this were a production script I’d probably have made things neater, but this worked well enough to do what I needed. Sure enough, one of the servers (ironically one of the ones more recently installed) was missing the standard mail Profile we setup. That was easy to fix because of course I have that scripted out. Open the T-Sql script on that server, run it, and all my servers now had the standard mail profile.

Once I had confirmed my new restore script could run on any of the servers and correctly send email if there was an error it was time to roll it out.

deploy

Successful deploy to the UAT environment

So one quick PowerShell Script, an updated T-SQL Script and a PowerShell Deploy Script and my new sproc has been deployed to UAT and other environments.

And best of all, because it was logged, I knew exactly when I had done it and on what servers and that everything was consistent.

I call that a win for a Monday. How is your week starting?

 

 

Getting My Hands Dirty…

… and my clothes cleaned. Or more importantly, dried.

Before I was a programmer I worked for my dad in construction over the summers of high school. It was good solid work. I enjoy working with my hands at times. For one thing, you see and feel the results of your accomplishments in a very tangible manner. For another, you generally can measure the impact of your effort.

After my dad died, I wrote about using a drill of his to work on the addition that was to become my office. I liked the heft and feel of it. I knew I was accomplishing something with it. Being a programmer, sometimes it’s hard to experience that. Currently for example I’m working on an ETL script using PowerShell to SFTP down a file, extract it to some tables and then feed it into Salesforce. For me, it’s just a bunch of data. Yeah, there’s some fun challenges; learning how to setup and deal with GPG and designing a robust and secure information because some of the data is sensitive. But, once the project is finished, it’ll run silently and other than an occasional email, I won’t think much about it. It won’t impact my day to day life in any way and I won’t be able to point to it and say, “See, THERE is something I did.”

But, my dryer on the other hand, now that’s different. For awhile now (say at least a year or two) whenever I’ve run a load it’s made a fearsome rumbling sound. It’s been annoying, but we’ve managed to live with it up until 6 or 7 weeks ago. Generally I’d do most of the laundry on Sundays and if there was a load or two left, on Monday while everyone else was out of the house or at school. But obviously things changed. My wife’s office is in the room next to the laundry room. Whereas for me the rumbling was faint and simply background noise, for her it was quite noticeable.  I tried to work the loads around her work schedule, especially since she’s on so many conference calls for her job, but it was getting less and less practical.

It was finally time for me to do something about it. Now, had I been smart, I’d have started the project on a Monday. But, I’m not always that smart. So, Saturday came around and I disassembled the dryer.

I was fairly confident I knew what the problem was. I assumed that either something had wrapped around one of the rollers for the drum, or a bearing in a roller had seized. If it was the former, the fix would be trivial and I’d have the whole thing back together before dinner. If it was the latter, I figured a shot of silicone or other lubricant and I could at least get a few more weeks out of it while I ordered the parts. And since the tight screws were now loosened and I knew how to take it apart, the final fix would go quickly.

Well, as they say, you know what happens when one assumes. I was wrong about the first guess, it was not something as simple as something wrapped around the roller. And I was even more wrong about it being a seized or flattened bearing. See for that assumption to be valid the bearing assembly inside the wheel has to actually exist.

20200502_162747

Bearing Assembly? What Bearing Assembly?

It’s a bit hard to make out, but inside the blue part of the wheel above, and behind the plastic triangle, there is supposed to be a nice little bearing assembly.  There is none.

20200502_163501

Better view of the roller

You can see the wear on the inner hub.  This is what in the trade is called “less than optimal.”

More seriously though, it unfortunately meant that this was not going to be a quick fix. I had been planning on ordering the parts, but this made it a bit more of a rush. The dryer contains four of these rollers and as such I ordered a four pack, since generally my assumption on items like this is is that if one has worn, all four are worn. Now, none of the other three have shown nearly the damage, but figure, I’m in there, I might as well make it right.

What’s most interesting to me, is that there’s literally NO sign of the roller assembly in the dryer. However it got destroyed, it was pretty cataclysmic.

I also took the time to clean out the rest of the interior space and correctly deduced that the moisture sensor was covered with lint. Now that I know where it is, I can keep that clean in the future.

In any case, sometime later this week, I’ll get my package, swap out the rollers and reassemble the dryer and start doing laundry again. Quietly.

But, unlike the ETL I’m writing above, this change will have a direct noticeable impact on my life I’ll be aware of every time I do a load of clothes. I like that.

This week’s takeaway? I do enjoy my job and the challenges that come with it, but there’s something to be said for doing work you can touch and feel and experience the tangible impact.

20200505_090057

My best sourdough yet!

And perhaps I shouldn’t be posting pictures of homemade bread after talking about dirty hands. Don’t worry, I washed my hands!

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.