More Violated Now Than Ever Before

Last week while I was trying to have a vacation in Las Vegas, something came up at work that seemed rather strange. It seems that our monitoring scripts for a particular server stopped working. Worse, our backups also started failing. We can live without monitoring for a period of time, but we really need to make certain that the backups are running, right? Well, they weren’t, and the reason why was clear, the ‘select @@servername’ statement was returning a NULL.

Yeah, that’s right, a NULL, as in “I don’t know who I am anymore”.

How? Good question. We noticed a linked server had been created on the instance with the same name as the instance itself, and the entry in the sysservers table for srvid = 0 was gone. Have you ever tried to create a linked server that has the same name as the local instance using SSMS? No? Well, if you did, you would see an error message:

 

 

 

 

 

Know what that means? It means “DON’T DO THIS”. It does not mean “FIND SOME OTHER WAY AROUND THE STUPID THING YOU ARE TRYING TO DO”. By this time we suspected that our old friend, Egghead, was up to his old tricks. He had taken a job recently at a certain software company whose name I shall not reveal (hint: it rhymes with ‘Breast’), and we found one of their products as the likely culprit. So I started asking a lot of hard questions and got very little in terms of response other than “that’s the way it works”, and “they say it is necessary”.

Around the same time, I noticed that Brent Ozar posted an entry to his blog on the dangers of building linked servers on the fly. Seemed like too much of a coincidence, right? So I grilled Brent on the subject, for two reasons. First, he and I worked on a similar product last year that built linked servers on the fly through the use of OPENROWSET statements. Second, because has done extensive research on Egghead, he knows how he talks, knows where he sleeps, how he thinks, knows what he likes for breakfast.

Brent did a fine job answering my questions, he really did. However the net result of the install makes me feel as if I went to the dentist, got gassed, knocked out, and woke up with my pants at my ankles.

I use @@servername extensively. It helps me build dynamic scripts and allows for my team to effectively monitor our instances. The idea that a vendor would go out of their way to manipulate my sysservers table for their own end result is nothing short of amazing. It is one thing to build a linked server, it is another thing altogether to manipulate my server to the point that it doesn’t even know who it is anymore.

Look, I understand the idea that linked servers and OPENROWSET statements give you the illusion of scalability. As Brent points out, often times this gets you into trouble. Believe it or not, I used to build software. No, really. I swear to the Holy Church of Bacon. And often times we would think about how our product would scale. It is not an easy thing to simply build into your product, because you never quite know the environment you will be deployed into. So, you want to offer some functionality while at the same time not impede performance. You need to consider a lot of variables, and will try to pick the one that suits you best. But the idea that you would create a linked server that redefines the internal server name of the instance itself is crossing a line.

It is fairly clear to me that only a mastermind like Egghead himself would be behind something like this. Who else would be so insidious as to present a seemingly benign install process for a instantly scalable product only to know that underneath the covers of the database instance lays a Radar Egg, where the slightest movement will cause the product and/or the instance to implode?

Listen to me, Eggheads. If you feel the need to create a permanent linked server, and that link needs to point to the local instance, then why not create a data source and simply name it something OTHER than the actual instance name? That would make it easy to flag the links later on when, say, you decide to migrate some databases or systems. Or, did you think that the servers and names would never get changed? So, name it ‘instance_asshat’, that way we could easily search for links with ‘*asshat’ in the name in order to understand what links are being used by this product and where. Or use OPENDATASOURCE instead in your code?

I will give you a choice. Either stay out of my sysservers table, or keep your product on the shelf. Which would you prefer?