Today, I’m not thankful that it’s Friday, although I am happy about it. Today I am thankful for the wisdom of Grant Fritchey ( b/t ). No Grant didn’t help me with any kind of problem, at least directly. Hell, I have never even met the man in person anyway. No, I’m not some kind of weirdo interwebs creeper either. I do, however, follow Grant’s blog and occasional twitter posts. What can I say, he’s a pretty sharp crayon when it comes to SQL, and as a DBA I like and listen to, sharp crayons. Although today I’m not thankful for Grant’s technical wisdom. Today I am thankful for his wisdom related to making the relationships between the DBAs, devs, IT, and business folks better.

As you know, COVID-19 has upended well…everything. This means, that at my company priorities have been upended as well. So with fast responses to technical and business problems, IT hasn’t had to polish things out and make them experiences seamless. This week, I forgot about that and started letting my ego and my own self-importance get the better of me. I was focused on the problems related to databases and the SQL instances I work with, I forgot about the bigger IT picture. During a SCRUM call, I was a little too honest about my frustration related to connecting to instances in the cloud and how unintuitive access currently is, along with the inability to use our toolsets. It was pointed out to me later, that my frustration was a little too apparent and could have been seen as hostile towards the IT folks on the call.

Own itOwn It

So today, I used some wisdom from both Jocko and Grant, I chose to own it. I decided it was better to apologize for my ego whether it was misconstrued or not and make sure to foster a better relationship between IT and the Data Team. So instead of dismissing it as just as a misunderstanding in a moment, I opted to take 20 minutes to send an apology accepting all of the blame. I’m not posting this as a humble brag about my moment of empathy either, I am posting this to both remind myself I will be better publicly and to reinforce the lesson. The bottom line, it is better to remain humble and approachable than to fracture getting work done because of some unmet, implied expectation that neither side had agreed to.

I will be better.

 

Figuring out Windows FirewallI ran into an issue last week when I was working on upgrading a SQL2014 Availability Group to SQL2019. Nothing went wrong with the actual SQL upgrade, however, post-upgrade I ran into some headaches. Turns out Windows Firewall can bite you in the ass on an upgrade. The reason for the upgrade in the first place was so we could take advantage of automatic database seeding.

Automatic seeding is to be the lynchpin on PowerShell script I’m working on and will post about later. The idea will be to check for new databases added during the day, and then programmatically added them to the secondary later in the day. But that post is for another day.

After the upgrade, I performed a failover test, verified everything was working okay. After failing back over to the original primary, I added a database to the availability group via the wizard and chose automatic seeding, and while that was running I went off to do something else. When I got back, I saw the wizard had completed successfully and the database had been added. Just to make sure though, I checked the secondary and found no database. Say what?

After spending a day looking for failover cluster errors, testing failovers, adding databases to both nodes of the AG to test database creation, and assorted other tasks, I was still no closer to figuring out why the secondaries wouldn’t sync up or why automatic seeding wasn’t working. Keep in mind, all of this worked just fine when the AG was still running as a SQL 2014 environment. I mentioned this to one of our IT folks because I was at loss.

The fix

One of our IT admins found the problem, although it did take them a couple of hours. Turns out, port 5022 was blocked on the secondary replica but why was it blocked when it worked before? I’ll tell you why. When, and this very well could have been me when I built the server originally, the server was built and everything was loaded, a rule was created in the Windows Firewall for inbound connections. Only, instead of creating the rule to use the port number, the rule was created to allow access to the SSMS executable. Do you see what happened?

When I upgraded SQL to 2019, the 2019 executable took over running SQL. The path changed from <disk>:\<install location>\MSSQL12.MSSQLSERVER\blah blah blah to <disk>:\<install location>\MSSQL15.MSSQLSERVER\blah blah blah

The fix was simple, we changed the firewall to allow connections on the AG port. I mean I guess we could have repointed the rule, but then if we ever upgraded that server again…I promise we would forget about this issue and had to repeat the troubleshooting work figuring it out again.

Be humble folks, every so often we manage to shoot ourselves in the foot.