Giving Blood and Pride Month

I gave blood yesterday. It got me thinking. First, let me show a few screenshots:

male blood donor shot 1

7 Male Donor #1 screen shot

female blood donor shot 1

Female Donor #1 screen shot

Let me interject here I’m using the terms Male and Female based on the criteria I selected in the American Red Cross’s Fast Pass screen. More on why I make that distinction further on. But first two more screen shots.

female blood donor shot 2

Pregnancy question highlighted for female

male blood donor shot 2

No pregnancy question for males

Now, on the face of it, this second set of questions especially almost seems to make sense: I mean if I answered Male early on in the questionnaire, why by asked about a pregnancy? But what I’m asked at the beginning is about my gender, not my actual child-bearing capability. Let me quote from Merriam-Webster:

2-b: the behavioral, cultural, or psychological traits typically associated with one sex

Or from the World Health Organization:

Gender refers to the roles, behaviours, activities, attributes and opportunities that any society considers appropriate for girls and boys, and women and men. Gender interacts with, but is different from, the binary categories of biological sex.

Who can be pregnant?

So above, really what the Red Cross is asking isn’t about my gender, but really my ability to be pregnant. Now, this is a valid medical concern. There are risks they want to avoid in regards to pregnant women, or recently pregnant women giving blood. So their ultimate goal isn’t the problem, but their initial assumption might be. A trans-man might still be able to get pregnant, and a trans-woman might be incapable of getting pregnant (as well as a cis-woman might be incapable.) And this is why I had the caveat above about using the terms male and female. I’m using the terms provided which may not be the most accurate.

Assumptions on risk factors

The first set of images is a problematic in another way: it is making assumptions about risk factors. Now, I think we can all agree that keeping blood borne pathogens such as HIV out of the blood supply is a good one. And yes, while donated blood is tested, it can be even safer if people who know they are HIV or at risk for it can potentially self-select themselves out of the donation process.

But…

Let me show the actual question:

Male Male 3 month contact question

Question 21, for Men

This is an improvement over the older restrictions that were at one year and at one point “any time since 1977”. Think about that. If a man had had sex with another man in 1986, but consistently tested negative for HIV/AIDS for the following 30+ years, they could not give blood under previous rules. By the way, I will make a note here that these rules are NOT set by the American Red Cross, but rather by the FDA. So don’t get too angry at the Red Cross for this.

The argument for a 3 month window apparently was based on the fact that HIV tests now are good enough that they can pick up viral particles after that window (i.e. at say 2 months, you may be infected, but the tests may not detect it.)

Based on the CDC information I found today, in 2018, male-to-male sexual contact resulted in 24,933 new infections. The 2nd highest category was heterosexual contact (note the CDC page doesn’t seem to specify the word sexual there.) So yes, statistically it appears male-male sexual contact is a high-risk category.

But…

I know a number of gay and bisexual men. I don’t inquire about their sexual habits. However, a number are either married or appear to be in monogamous relationships. This means if they want to give blood and not lie on the forms, they have to be celibate for at least 3 months at a time!  But hey if you’re a straight guy and had sex with 4 different women in the last week, no problem, as long as you didn’t pay any of them for sex! I’ll add that more than one gay man I know wants to give blood and based on their actual behavior are in a low risk category, but can’t because of the above question.

Why do I bring all this up at the end of Pride Month and what, if anything does it have to do with database design (something I do try to actually write about from time to time)?

As a cis-het male (assigned at birth and still fits me) it’s easy to be oblivious to the problematic nature of the questions on such an innocuous and arguably well-intended  form. The FDA has certain mandates that the Red Cross (and other blood donation agencies) must follow. And I think the mandates are often well-intended. But, there are probably better ways of approaching the goals, in the examples given above, of helping to rule out higher-risk donations. I’ll be honest, I’m not always sure the best way.  To some extent, it might be as simple as rewording the question. In others, it might be necessary to redesign the database to better reflect the realities of gender and sex, after all bits are cheap.

But I want to tie this into something I’ve said before: diversity in hiring is critical and I think we in the data world need to be aware of this. There are several reasons, but I want to focus on one for now.

Our Databases Model the World as We Know It.

The way we build databases is an attempt to model the world. If we are only aware of two genders, we will build our databases to reflect this. But sometimes we have to stop and ask, “do we even need to ask that question?” For one thing, we potentially add the issue of having to deal with Personally Identifiable Information that we don’t really need.  For another, we can make assumptions: “Oh they’re male, they can’t get pregnant so this drug won’t be an issue.”

Now, I’m fortunate enough to have a number of friends who fall into various places on the LGBTQIA+ (and constantly growing collection of letters) panoply and the more I listen, the more complexity I see in the world and how we record it.

This is not to say that you must go out instantly and hire 20 different DBAs, each representing a different identity. That’s obviously not practical. But, I suspect if your staff is made up of cis-het men, your data models may be suffering and you may not even be aware of it!

So, listen to others when they talk about their experiences, do research, get to know more people with experiences and genders and sexualities different from yours. You’ll learn something and you also might build databases. But more importantly, you’ll get to know some great people and become a better person yourself. Trust me on that.

 

 

 

Yesterday was NOT a “Monday”

Last week I wrote about how the previous day was A Monday and by that I didn’t just mean by its position on the calendar.

Well I’m here to say that while yesterday as Monday, it was distinctly NOT A Monday. Part of my morning habit when I get up is to check the emails from my largest client. There are of course a bunch of processes that run overnight that I need to check on and fix and rerun if there’s a problem.

I wrote last week about how one ETL job had failed and I had to fix it. I fixed it, but Thursday, Friday and Saturday it failed again due to a related but different issue. Saturday I implemented a change that I was confident would solve the problem. Here I am 3 days later and I can say that it’s been running well since.

Another process that had broken last Monday ran fine. It was off to a good start.

A bit later in the day I received a copy of the weekly audit report. This shows security items that need to be reviewed and mitigated. There was a huge drop in issues from the previous week and one whole column had disappeared. This was a great day.

Later in the day I was able to complete some VB.NET code for an internal web app I maintain for the customer and add new functionality, and too boot, I found a far simpler way to implement what I want than I had started with.

About the only real issue yesterday that was work related was that the promised production data for a new project didn’t show up until dinner time. But, hey that’s on teh customer.

So, not every Monday is necessarily A Monday. Some are distinctly better.

Yesterday was “A Monday”

Yesterday was a Monday. I don’t just mean it was Monday, but it was in the Garfield comic sense of things A Monday.

As a consultant, I’ve come to expect certain patterns in my work load. For one client, I know approximately every 2 months, over 2 weekends I’m going to have to patch their SQL Servers. I know certain passwords will need to be updated quarterly or annually. And I know sometimes I’ll have A Monday.

Yesterday was one of those. I woke up, checked my email and noticed two jobs had not run. So I logged in and it appeared that the PowerShell script on each server had hung. I killed it and tried to rerun it, but got an error. This wasn’t entirely surprising. This script, in its first part downloads a file from a 3rd party vendor and last week for example, their SFTP server had been down. At first I expected this to be the problem again. But further testing showed I was getting inconsistent errors. Finally the script ran. But, what normally took about 20 minutes to download, took about 2 hours. We learned later the vendor had done an upgrade to their product over the weekend. This shouldn’t have impacted their SFTP server performance, but here we were. Today (Tuesday) the process took 20 minutes again and is back to normal. Chalk yesterday’s issue up to being A Monday.

Then I took a look at another job that had failed. This one is purely internal. Basically SFTP a file from a Linux server to a NAS for a backup. A quick check showed that the NAS share was inaccessible. Reporting this triggered an avalanche of emails back and forth. The most interesting line basically came down to “Yes, the internal IT team did a migration of the NAS, but the migration was supposed to be completely transparent to the users.” Famous last words in my book. Actually, honestly, what I decided was more disturbing was that the failure was on the new NAS device apparently due to a typo. To me, this means, most likely, all the old shares were recreated on the new device by hand, rather than using a script that read out the old shares and recreated them. In any event, the problem was solved, the job was rerun and the backup created on the now new NAS. Chalk that one up to being A Monday.

Then one of the developers for one of the platforms at this client emailed me and said, “Hey database FOO is in recovery mode, what happened?” This one, fortunately I knew exactly what the problem was. Unfortunately I knew it was my fault. We had decided to reconfigure that database to be a log-shipped copy of the main database and I had set it up over the weekend. I had simply forgotten to set it up to place itself in Stand-by/Read-only mode after it had applied the most recent logs. I’ll chalk that one up to it being A Monday.

All of the above was taken care of before 10:00 AM. The rest of the day was filled with a variety of other issues and items, including looking at a Hyper-V host machine with 16 physical CPUs with hyperthreading turned on hosting 4 VMs, 1 with 4 vCPUs allocated, and the other 3 with 8 each. They’re having performance issues. I’m still tackling that one. Looking at that happened on Monday, but it’s not A Monday issue, it’s been an ongoing issue for months.

So what was it about this particular Monday, or Mondays in general?

Well in this case, all 3 of my early AM issues had one thing in common: upgrades or changes made over the weekend. I’m not going to debate the value or wisdom of the timing here, but just note, that on the particular Monday, it wasn’t just one issue, but three. It was definitely A Monday. But I survived as did my customer.

Now back to my regularly scheduled workload.

 

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!

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!)