Distributed App SQL Help

Question

Distributed App SQL Help

Becker-213572

Old Hand

Points: 394
More actions
June 12, 2006 at 8:17 am

#172902

I am working on developing an application. I know the input database structure, and I know my database structure I am creating, however for my clients, I don't know where they will put my application.

They may put it on the same SQL Server as the input database, or they may put it in a different SQL server instance. I also don't know what the name / owner of my input DB will be.

So my question, is if all I know is the input table / column names, how should I build my stored procedures to access this so that when I go to install this at a client site, there is minimal or no code change required based on where they install my application?

Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Make your code refer to a "linked server" and use a 4 part naming convention everywhere in your code. Then, you can just repoint the linked server at whatever box you want including the box your code is on.

See Books Online for how to create and grant permissions on a linked server. Start with lookup on [sp_AddLinkedServer].

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Becker-213572 Old Hand Points: 394 More actions · Answer 2

Thanks Jeff, but what if the database name may be something different and the owner may be different? I was wondering if I wrote views local in my app DB that did select * from each of the soruce tables, that way all I'd have to do is change the 3 parts of the 4 part convention? I wasn't sure what performance would do though with select * views being merged together and crossing linked servers though.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Sorry about the confusion on the owners... I'm used to folks putting everything into production as "dbo". Yes, the views you speak of would certainly and easily solve that problem without adding much overhead so long as they are truly "pass through views" (ie, SELECT * FROM 3parttablename) with no calculations, joins, or criteria.

Not sure what you mean by "views being merged together", though... probably not enough coffee, yet...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Becker-213572 Old Hand Points: 394 More actions · Answer 4

The views I'm thinking of would be SELECT * from 4parttablename and what I was concerned about was when I join a couple of views together in a query like view1.colx = view2.colx, I wasn't sure what happens in a distributed environment when my views are doing joins to tables across a linked server, and then what happens if I try to join it to a "local" table?

I'm worried about the optimizer and where it will try to do the join and how it will do the join.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

I have an SQL Server based IVR at work (actually, 400 miles away from the main DB)... it uses a linked server connection with multiple pass through views as you have suggested. What takes 10 minutes to do on the local box takes 30 minutes to do from the IVR. There's a 3 to 1 hit on performance probably due to the VPN speeds. One of the Insert/Selects uses joins between 5 tables... doesn't seem to be a problem. I suspect 3 to 1 is the absolute worst you would see.

We also have linked servers between local servers with a pretty good LAN. There is some degradation in performance but it's not as bad as you would think. Updates seem to take the biggest hit at something less than 2 to 1. Selects seem to run at something less than 1.25 to 1.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Becker-213572 Old Hand Points: 394 More actions · Answer 6

Very good, we will rely on SQL Views to write our code against then in conjunction with linked servers. Thanks for the insight.