May 13, 2019 at 2:49 pm
So I attended the PASS High Availability webinar last week on "Your AG might not be as "available" as you think" and it was pretty interesting. But one odd thing stood out to me. Apparently the guy who gave the webinar believes most people use linked servers wrong. So I went googling to see if he'd ever blogged about it and couldn't find anything. So in the interests of finding out more about this, does anyone know what this particular subject might be about? What is it about linked servers that people are using wrong? Any thoughts on the subject?
I attended the same seminar, and was wondering the same things.
The whole thing was a bit misleading. It was really about how to sync the jobs between nodes of an AG. I do not, and do not intend to, create linked servers and do this kind of work.
In our case, anything that gets deployed to the primary is also deployed to the secondaries. We have all of this automated with a tool called Octopus. It's PowerShell under the hood.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 13, 2019 at 2:59 pm
I don't think he was talking about using linked servers to update the AG jobs, logins and whatnot. I think he was about to go off on a rant about linked servers that had nothing to do with AGs. At least that's the impression I got.
I really wish I knew what the rant was about because we do use linked servers in our environments (unrelated to the AGs we have set up). I'd like to know what the issue is so I can make my own conclusions about what his issue is.
May 13, 2019 at 3:06 pm
I don't think he was talking about using linked servers to update the AG jobs, logins and whatnot. I think he was about to go off on a rant about linked servers that had nothing to do with AGs. At least that's the impression I got. I really wish I knew what the rant was about because we do use linked servers in our environments (unrelated to the AGs we have set up). I'd like to know what the issue is so I can make my own conclusions about what his issue is.
I don't know anything this person's issue but where I have seen them go what I would consider wrong is putting the link server name in the query. This may seem like it is not a big deal but it makes code promotion nightmarish. What I usually do in these cases is create a synonym to the object in each environment. This way I can write one single query and with zero code changes it can be promoted from dev to qa and prod without changing anything.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2019 at 3:09 pm
Brandie Tarvin wrote:I don't think he was talking about using linked servers to update the AG jobs, logins and whatnot. I think he was about to go off on a rant about linked servers that had nothing to do with AGs. At least that's the impression I got. I really wish I knew what the rant was about because we do use linked servers in our environments (unrelated to the AGs we have set up). I'd like to know what the issue is so I can make my own conclusions about what his issue is.
I don't know anything this person's issue but where I have seen them go what I would consider wrong is putting the link server name in the query. This may seem like it is not a big deal but it makes code promotion nightmarish. What I usually do in these cases is create a synonym to the object in each environment. This way I can write one single query and with zero code changes it can be promoted from dev to qa and prod without changing anything.
That's why we don't use server names as linked server names. We use names (like Reports) that can remain consistent regardless of the environments.
EDIT: The linked server names can be altered if, instead of using "SQL Server" as Server Type, you use "Other data source" and choose SQL Server Native Client X.X as the Provider.
May 13, 2019 at 3:12 pm
Brandie Tarvin wrote:I don't think he was talking about using linked servers to update the AG jobs, logins and whatnot. I think he was about to go off on a rant about linked servers that had nothing to do with AGs. At least that's the impression I got. I really wish I knew what the rant was about because we do use linked servers in our environments (unrelated to the AGs we have set up). I'd like to know what the issue is so I can make my own conclusions about what his issue is.
I don't know anything this person's issue but where I have seen them go what I would consider wrong is putting the link server name in the query. This may seem like it is not a big deal but it makes code promotion nightmarish. What I usually do in these cases is create a synonym to the object in each environment. This way I can write one single query and with zero code changes it can be promoted from dev to qa and prod without changing anything.
I totally agree with what you're saying... to boil it down, people end up using the 4 part naming convention when they should really use only the 2 part naming convention via synonyms. We actually have some very strict rules about that in our Standards documentation.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2019 at 3:36 pm
While we are on the subject of 4 part names and consistency across environments, it reminds me of a DBA who I had the pleasure(!) of following three separate times.
First time I ran into their work, they decided to rename the databases on the 4 environments with a prefix indicating the environment.
PROD_DatabaseName, STAGE_DatabaseName, DEV_DatabaseName, QA_DatabaseName.
This person then instructed the developers to set a variable with the DB name, and use dynamic SQL in all their procs.
Second time, they had created a set of views in one the of the main databases and told the devs to only use the views in their procs. If the devs ran into a view that did not contain the columns, they modified the views or created new ones. Performance was horrible. Everything was this tangled web of views that called table after table in an endless series of joins.
Third time, they were a contract to hire. VERY simple environment. One server, one database. After their first week, there was a system outage in the middle of the day. All hands on deck. They decided that they needed to go for a walk to get some air when this occurred.
The best part? I had interviewed them and offered them a position before I ever saw any of their actual work. Great resume, good references. They turned me down because they didn't want to work that hard.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 13, 2019 at 3:37 pm
That's why we don't use server names as linked server names. We use names (like Reports) that can remain consistent regardless of the environments. EDIT: The linked server names can be altered if, instead of using "SQL Server" as Server Type, you use "Other data source" and choose SQL Server Native Client X.X as the Provider.
If that results in the use of 3 or 4 part naming conventions in the code, that can still cause a problem although that's a heck of a lot better solution than using actual server names.
We do similar... For example, we have linked server names like "ESSRO" and "ESSRW" and they are identical in the Dev, Staging, and Prod environments but point to different servers for each environment. We still have synonyms to allow for 2 part naming in code (just n case) but it makes it so that we don't actually have to change even those during the SDLC.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2019 at 3:42 pm
While we are on the subject of 4 part names and consistency across environments, it reminds me of a DBA who I had the pleasure(!) of following three separate times. First time I ran into their work, they decided to rename the databases on the 4 environments with a prefix indicating the environment. PROD_DatabaseName, STAGE_DatabaseName, DEV_DatabaseName, QA_DatabaseName. This person then instructed the developers to set a variable with the DB name, and use dynamic SQL in all their procs. Second time, they had created a set of views in one the of the main databases and told the devs to only use the views in their procs. If the devs ran into a view that did not contain the columns, they modified the views or created new ones. Performance was horrible. Everything was this tangled web of views that called table after table in an endless series of joins. Third time, they were a contract to hire. VERY simple environment. One server, one database. After their first week, there was a system outage in the middle of the day. All hands on deck. They decided that they needed to go for a walk to get some air when this occurred. The best part? I had interviewed them and offered them a position before I ever saw any of their actual work. Great resume, good references. They turned me down because they didn't want to work that hard.
So (to use and old newstand term)... "Enquiring minds want to know"... have you changed your interview process? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2019 at 4:09 pm
RE: Synonyms and 3 / 4 part naming conventions, is this just a preference or are you noticing performance issues?
And in the end, why does it matter? If the linked database is on another instance / server, even the synonym is going to be using (I assume) a linked server to get to the data. Unless there's a way to get around that limitation that I'm unaware of. If so, please tell me.
I just find it hard to justify the work of building and maintaining synonyms (to my already large list of things to do) to reference things that can be referenced through a linked server and won't generally break if a column is added / dropped / updated.
May 13, 2019 at 4:41 pm
RE: Synonyms and 3 / 4 part naming conventions, is this just a preference or are you noticing performance issues? And in the end, why does it matter? If the linked database is on another instance / server, even the synonym is going to be using (I assume) a linked server to get to the data. Unless there's a way to get around that limitation that I'm unaware of. If so, please tell me. I just find it hard to justify the work of building and maintaining synonyms (to my already large list of things to do) to reference things that can be referenced through a linked server and won't generally break if a column is added / dropped / updated.
One reason we use synonyms is because we often have dev and qa databases (and shockingly even prod in a couple cases) on the same sql server (not my choice). I can't just use a different name for the linked server because I still have to specify the database. This would make code promotion a nightmare if I didn't use synonyms. In our case synonyms makes this process seamless as the query just works in every environment.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2019 at 4:49 pm
OUCH!
I see the need for synonyms in that case. And you have my intense sympathies for your situation.
May 13, 2019 at 4:53 pm
No kidding. It makes for some interesting things from time to time to say the least.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2019 at 5:25 pm
No kidding. It makes for some interesting things from time to time to say the least.
Sorry, Sean, but knowing a bit about the baan-like environment you're working in has used up all the sympathy I have for your pain. There's too much pain inherent there to feel bad about synonyms. I know you know exactly what I mean. 😉
May 13, 2019 at 5:58 pm
Sean Lange wrote:No kidding. It makes for some interesting things from time to time to say the least.
Sorry, Sean, but knowing a bit about the baan-like environment you're working in has used up all the sympathy I have for your pain. There's too much pain inherent there to feel bad about synonyms. I know you know exactly what I mean. 😉
Right?!?!?!? Synonyms are nothing compared to some of the other downright horrific items I have to deal with on a day to day basis.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2019 at 6:39 pm
Ed Wagner wrote:Sean Lange wrote:No kidding. It makes for some interesting things from time to time to say the least.
Sorry, Sean, but knowing a bit about the baan-like environment you're working in has used up all the sympathy I have for your pain. There's too much pain inherent there to feel bad about synonyms. I know you know exactly what I mean. 😉
Right?!?!?!? Synonyms are nothing compared to some of the other downright horrific items I have to deal with on a day to day basis.
Ditto that!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 63,541 through 63,555 (of 66,815 total)
You must be logged in to reply to this topic. Login to reply