Wayne Sheffield (b/t) is our host this month for the long running blog party (104 months now and running!) started by Adam Machanic (b/t). In this case Wayne is asking us to talk about a time when we hit a brick wall, metephorically speaking. At least in part in reference to his difficulties in coming up with a topic. As I read the topic I have to admit I had a really hard time coming up with something to write about. In fact I realized last night I was completely hitting a brick wall on the subject! (yea, that’s not my subject, but I just had to throw it in there)

I finally remembered a project I was working on recently where I hit several long-running stumbling blocks. I had to add a SQL Server Id to provide access through a linked server. Basically, create the SQL Id on the remote instance, then add the username and password into the permissions of the linked server on the source instance. Now, that sounds simple enough, right? Well, technically I was doing this on the same linked server in 8 different test environments. Ok. No problem. Script it out and run the script on each environment. Well, no, there are a few complications. Here is basically the process I ran through:

1. Open the linked server and look up the remote server name (remember 8 different remote servers because it’s 8 different environments).
2. Add the username and password to the linked server. It’s not created yet, but I’ve got the properties open so why not.
3. Go to the remote server and create the username and password.

Again pretty easy, if only each of the remote environments (well, most of them) weren’t parts of an availability group. Ok, so add in a couple of steps

1. Open the linked server and look up the remote server name (remember 8 different remote servers because it’s 8 different environments).
2. Make sure that the linked server is pointing to the listener (except in the few cases where it’s not an AG).
3. Add the username and password to the linked server. It’s not created yet, but I’ve got the properties open so why not.
4. Go to the remote server and look up all of the secondaries.
5. On the primary create the server principal (login) and password and grant the database permissions (user).
6. Script out the login with the SID and password hash.
7. Run that script on each of the secondaries.

Phew, this is getting complicated. But so far so good. Until I found out that the linked server is supposed to point to one of the read-only secondaries. For those of you that don’t know, you can do this by pointing to the listener and telling the connection that your intent is read-only. Now, I don’t know a whole lot about AGs but I was told I needed to add ApplicationIntent=ReadOnly to the connection string for each of the linked servers. Ok, no problem.

1. Open the linked server and look up the remote server name (remember 8 different remote servers because it’s 8 different environments).
2. Make sure that the linked server is pointing to the listener (except in the few cases where it’s not an AG).
3. Add the username and password to the linked server. It’s not created yet, but I’ve got the properties open so why not.
4. Script out a drop and create for the linked server (before actually applying the changes, because why not), add ApplicationIntent=ReadOnly to the connection string in the script (it’s read-only in the properties window) and run the script.
5. Go to the remote server and look up all of the secondaries.
6. On the primary create the server principal (login) and password and grant the database permissions (user).
7. Script out the login with the SID and password hash.
8. Run that script on each of the secondaries.

It’s definitely gotten rather complicated at this point. And remember the username/password combination is different for each environment, most of them are AGs but not all, and just to add to the fun each environment that has an AG has a different number of secondaries.

Anyway, I made my changes to 5 of the environments and went back and tested. 2 of them worked!!! But 3 of them didn’t. So I start checking, piece, by piece. What did I do wrong? Nothing, everything looks right, but it’s not working. Days I spent fiddling with this. Eventually, I had to stop and ask for help. Now normally I have a 30 minute rule. Where depending on the complexity of what I’m doing I go for help after a few hours tops. But in this case the person I needed to ask was in a different time zone. 6 hours off in fact. And they tend to work early in their TZ and I tend to be a bit later in mine. It made it rather hard to get ahold of him. Finally, I did though. We walked through the ones that weren’t working and he pointed out that I’d probably messed up the SIDs on the secondaries. (I had.) It was such a stupid mistake, one I know better than to do. I fixed it and everything worked!

Ok, now on to the last 3. I was super careful this time. Dotted every t and crossed every i. And 2/3 worked. I reviewed and reviewed and couldn’t come up with an answer. So I went for help. Of course, now he’s on vacation for 2-3 weeks. Well, crud.

Fortunately, I had 7/8 environments working. The developers had plenty of space to test now, and the environment that wasn’t working was the last one they’d be trying anyway. So I had some time. I let them know where I was and that I was going to step away for a bit. For the next week/week and a half, I did other stuff, I didn’t even think about this problem. Then, with a clear mind, I went back to it. I rebuilt everything from scratch for that environment. Fifteen minutes later, I had it working. I’m honestly still not sure what I’d done wrong.

Here’s the tl;dr;: When I hit a wall I couldn’t seem to get past I did one of two things. The first time I went and got some help. Then, when help wasn’t available, the second time I backed away completely, let myself have some breathing space, and came back with a fresh set of eyes.