Complicated linked servers and a bunch of head-desks: T-SQL Tuesday #104
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:

Open the linked server and look up the remote server name (remember 8 different remote servers because it’s 8 different environments). Add the username and password to the linked server. It’s not created yet, but I’ve got the properties open so why not. 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

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

Open the linked server and look up the remote server name (remember 8 different remote servers because it’s 8 different environments). Make sure that the linked server is pointing to the listener (except in the few cases where it’s not an AG). Add the username and password to the linked server. It’s not created yet, but I’ve got the properties open so why not. 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. Go to the remote server and look up all of the secondaries. On the primary create the server principal (login) and password and grant the database permissions (user). Script out the login with the SID and password hash. 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 a30 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.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

tags: linked,remote,different
分页:12
转载请注明
本文标题:Complicated linked servers and a bunch of head-desks: T-SQL Tuesday #104
本站链接:https://www.codesec.net/view/588657.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(42)