Linked servers?

What are the problems associated with linked servers?
Here is what management wants me to do:
Since we cannot convert our existing sql2000 databases (oltp - in use 24/7 - 20 databases ) to sql2005 anytime soon, they want me to install sql2005 on a separate server that contains just one database with just the stored procedures and views required for an application that uses tsql 2005 syntax and features.
Then they want me to create a linked_server that links to the sql2000 databases, and change all the stored procedures and views to select,update,insert,delete into the sql2000 databases via the linked server.
Will this work in the real world?
I know from past experiences that link servers have problems.
I also heard that the optimizer does not work well with linked servers.
Any thoughts on this will be greatly appreciated...thanks
NOTICE: The information contained in this transmission is confidential,proprietary or legally
privileged and may be subject to protection under the law, including the Health Insurance
Portability and Accountability Act (HIPAA). The message is intended for the sole use of the
individual or entity to whom it is addressed. If you are not the intended recipient, you are
notified that any reading, dissemination, distribution, copying or any other use of this message
is strictly prohibited and may subject you to criminal or civil penalties. Interception of e-mail
is a crime under the Electronic Communications Privacy Act, 18 USC sections 2510-2521 and 2107-2709.
If you received this transmission in error, please contact the sender immediately by replying to this
email and delete the message information, and all copies and backups, from any and all computers.

My experience has been that it works well, but be sure to use synonyms in the 2005 environment so that when you migrate the other db's you won't have to go back and change all those stored procedures.
Our dbas may have other stories but from a developer's point of view it works fine.
--------
Jeff N. Cantwell
Contract Programmer
Downtown Little Rock, AR
ICQ #19444448

Ouch, that doesn't sound like a good plan at all. The biggest thing you're going to see is a performance problem (granted, links between two SQLServers is better than a non-SQL box). That and trying to get the DTC to work properly across linked servers.

If you really have to go this route, I would maybe suggest using OPENQUERY and doing everything through a pass-thru instead of calling the procs through the linked server itself.

That's why you have architects to assess the application performance and impact on other systems. A decent solution architect should be able to identify these issues and with DBA and developer input provide a workaround or alternative solution.

Then take one of the senior developers or DBAs and make them the lead or the architect for the project. You should always have someone in the driver's seat with the technical knowledge to drive the solution, even in small organizations with a handful of developers.
If you don't then you asking for trouble down the road when you go to implement or in the maintenance phase.

While there should alwys be someone in the driver's seat, as you put it,
in many organizations that person is a team or department manager. As
such, the "driver" is far more interested in whether the car gets to the
next check point on time than in what kind of gas mileage it gets. In
fact, it has been my experience that the "driver", whether a manager of
some sort or not, frequently will be pressured by the users or "the
business side" to get the car to the next check point on time even if
means that the car has to tow a large trailer full of gas in order to be
able to go 3 blocks.

>If you don't then you asking for trouble down the road when you go to
implement or in the maintenance phase.
While true, the often cited point is that "we'll cross that bridge when we
come to it." because it _is_ "down the road. (Although, I frequently use
a phrase that I learned from a physics lab instructor from India, "We'll
burn that bridge when we come to it." . . . _that_ twist on the phrase is
often just _too_ appropriate. ;-)

"I have also found that developers sometimes don't realize the difference between "the queries in my app work in test" and "the queries in my app work well in production." "

They do :) I'm amazed at the current site I'm working at with that exact mentality. Some how they move schemas into production without indexes on foreign keys and then wonder why it slows down when the data builds. "Worked fine in development".....I'm sure it did :)

"If you don't then you asking for trouble down the road when you go to implement or in the maintenance phase."

You're preaching to the choir here--my point was simply most places don't have an architect on staff (or a qualified one that is).

Then there is a communication problem between IT and the business. The business needs to understand the impact of their decisions, and if they still want to have it their way and still blame IT when things go poorly; I would be dusting off my resume and going somewhere else.

I have to agree with Ralph. The last place I worked the pressure was to deliver (VP level) and don't worry, we will fix it as things progress. Every app was in constant maintenance mode.
Roy Forkner, MCTS Database Administrator
214.745.5778 direct

I agree with Ralph as well... as it happens enough in the real world.
The last place I worked at was exactly like this and went bankrupt a year after I left... something about scope creep, not following a SDLC, and ensuring adequate resources (human and otherwise) are provided makes me shake my head. As well as poor decisions made by management and not listening to IT caused massive overruns downstream.

Vinnie,
While a great idea for Plan D, regretfully it will not work in real world.
Linked server connection is slow due to all the comm layers participating in passing data back and forth. There are other issues too, like distributed transactions.
Imagine instead of passing a basket ball directly to an sql developer at your department, you would have to pass it indirectly through every member of the department.
On the positive side, upgrading to SQL Server 2005 is quite easy and fast. It can be done in a few hours of low usage time.

As a result, I just submitted my resignation !
Since I would be the one they called at 1:00 am , when something went wrong , I could not support this. I could not in good faith , do something that I know is wrong !

I seriously hope that you are not serious about having submitted your
resignation. While I agree with the principle and standing up for your
beliefs, in this case, I believe that your best option would be to submit
a memo to your _entire_ chain of command regarding your concerns, the
reasons for your concerns, the implications of the proposed approach, and
the Best/Common Practices that support your position. (Of course, I would
also recommend your immediately preparing your resume for a "worst case
scenario" response to your memo. ;-) If the memo is not effective in
changing the plan (or, possibly, even if it is effective), were I you, I
would active my resume on as many job sites and with as many recruiters as
reasonably possible.

It is far easier to find a job if you have a job. (Or, as one of my early
mentors put it, "It is better to help bail out the leaking boat while you
look for another boat than to spend the time waiting for another boat
while swimming in the ocean.")

Yes I am serious ! I have quit my job over this !
I cannot knowingly do something wrong, just for the money. (I might as well sell drugs) I agree that it would have been better to have a job before I quit, but when its all said and done, I have to answer to the man upstairs !

This is not the first time that I had to fight with management over best pratices and standards.

I am tired of getting called at 1:00 am to fix something that I told them would not work anyway ! Time to move on !

Well, I admire your courage (and can remember when I was in the same
position and did the same thing). Don't know where you are or what your
search parameters are with regard to geographic locations but if San
Antonio is on your list of possibles, you might want to check with
RackSpace because I know they have been (and usually are) looking.

I don't know about the others on this forum but you have now caused me to
pause and reflect upon a question that I have not considered for a while
now.

At what point on the slippery slope of compromise to retain employment do
you draw the line and declare, "This much and no more!" and then, more
importantly, _act_ upon that declaration and commit to upholding your
principles?

As I said, this has given me pause . . . as it should, IMHO, each of the
other members of this forum and, for that matter, ITToolbox.

Vinnie,
You and I seem to have the same attitude. I'm not sure if it is good or bad but that is the way it is.
You will find something soon, do doubt.
Roy
Roy Forkner, MCTS Database Administrator
214.745.5778 direct

Ralph,
I don't think there is a pat answer as to when to draw the line in the sand. If one is the sole provider for the family and the job market is iffy, I would think one would not be so quick to stick to principles. It is a real dilemma when making a living takes precedence over principles.
Roy
Roy Forkner, MCTS Database Administrator
214.745.5778 direct

I have to admit, it's usually "my way or the highway" at most places I've worked. I think it's a tad easier for independents than employees though. Most people figure they're paying big $$$ for your expertise and they actually listen to you. On the other hand, employees sometimes get forgotten and absorbed "into the daily grind".

I've threatened to quit engagements many times over politics and, fortunately, I've won each time. On the other hand, I have been second-guessed many times with "second opinions" from Microsoft Consultants and "the like" and usually they just respond with "why did you bring me here, everything is on course".

It is tough, but I also have the opinion I'm not going to be part of a company's ongoing problem. If you're not part of the solution.......

That was, actually, my point. IMHO, there _is_ no pat answer that answers
the question for everyone nor is there one for any one of us that answers
it in every case. However, it is something (again, IMHO) that each of us
needs to periodically reconsider if for no other reason than to reconsider
and remember exactly what our principles _are_. What is it that you
simply will not do even if it means quitting your job.

I have resigned (or explained that I _would_ resign) over a few issues in
the course of my career. The biggest one that will have my desk cleared
off and out before COB is software piracy (which I have actually been
asked to do by no less than the president of a company). However, as
Vinnie has demonstrated, there are also times when it is not so much one
single thing but the accumulation and habitual continuation of things. If
one doesn't consider what one's principles are and what one is willing to
do to maintain those principles, one can easily wind up like sheep who do
not so much choose to wander off and get lost as they just slowly nibble
their way into a state of being lost.

However, I am now letting this thread go . . . although, as I said, I will
be reflecting on it for a while.

Well "my way or highway" doesn't seem to be practical aspect of a DBA.
DBA's are bound to make mistakes and so are the app people or developers.
And sometimes you have to leave them to go their own way but have to ensure that they and accept the responsibility.
So ther's nothing a rigid line in a DBA carrier.
Thanks And Regards,
Pardeep Dhull
DBA-IIS
TSR-Somajiguda
Satyam Computer Services Ltd.

'Well "my way or highway" doesn't seem to be practical aspect of a DBA.'

I don't consider not following best practices or naming conventions 'practical' either. That's what I meant by my way or the highway. You've been very luck in you (short?) career if you have 'ensured that they and accept the responsibility.'

If you are the sole DBA and you are required to allow various people in the organization to have sysadmin rights the "my way or the highway" makes perfect sense. Or, as Ralph stated software piracy would be another reason for "my way or the highway".
There are instances where a stand has to be taken and a line not crossed.
Roy

>Well "my way or highway" doesn't seem to be practical aspect of a DBA.

And this translates to upper management (or even middle management) how?
Nobody says that "my way or the highway" is _ever_ logical (with the
possible exception of military service). That, of course, doesn't mean
that the attitude doesn't persist and, especially, that it doesn't persist
in management circles.

The thing that I find truly amazing is that a goodly number of developers
who _know_ that there are many ways to code a solution to virtually every
problem will adopt the MWOTH attitude when the get promoted to first line
management. Is it because they suddenly think that _their_ coding
practices have been validated by the promotion as being the _only_ way to
code?

I did not quit because of MWOTH attitude. I don't subscribe to that usually. If someone has a legitimate need for something, or they have a solution that meets accepted standards and best pratices, then I am fine with that. The problem comes when they ask me for my opinion on something, and based on past experiences or knowledge, they do not follow my advice and want me to do something that I know is 100% is wrong !
I did not quit because of the MYOTH attitude! I quit because I was tired of being forced to do things that I know are wrong, and then being called on later to fix them ! One can only take so much abuse!

That's the way it is....
Management will cry loud in front of others without even guessing their feasibility and then force you to get that result, doesn't matter whether itsa the best way or the worst way....
Thanks And Regards,
Pardeep Dhull
DBA-IIS
TSR-Somajiguda
Satyam Computer Services Ltd.