| Author |
Topic |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2008-07-16 : 11:02:21
|
| If I have two completely different instances and sql servers, but I need to query both in one procedure, Do I have any options besides creating a linked server if I want to join a table on it?Also regarding security, what is the best practice (Just create a separate account on the remote server that only allows access to that one specific stored procedure I need? Or is it fairly safe to just use a existing user account on that linked server, even if they have execute permission on that entire remote database?)Please let me know your thoughts.Thanks |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-16 : 11:17:40
|
| your query performance will be really bad if you do this. |
 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-07-16 : 11:30:05
|
| Please clarify. What is your suggestion to do? I am open to a better practice. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-16 : 11:42:54
|
quote:
Originally posted by Vinnie881
If I have two completely different instances and sql servers, but I need to query both in one procedure, Do I have any options besides creating a linked server if I want to join a table on it?Also regarding security, what is the best practice (Just create a separate account on the remote server that only allows access to that one specific stored procedure I need? Or is it fairly safe to just use a existing user account on that linked server, even if they have execute permission on that entire remote database?)Please let me know your thoughts.Thanks
you can either link them via linkedserver or use OPENDATASOURCE but as told earlier the performance will be poor. How frequently you need to join between two servers. is it for one time adhoc access? Can you please give some background info. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-07-16 : 11:47:56
|
| It is for a adhoc Web application that will be used frequently. It's actually connecting our Website Primary DB that is in a internally ran DB to another Sql DB on our internal network. I would like this to be scalable, so what is your suggestion on getting the best performance w/o jeopardizing security? |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-07-16 : 12:51:35
|
| I guess the main question is, if I have two different servers that I need to use to query together, what option will give me the best overall performance? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-07-16 : 13:43:17
|
| I guess I could, but this is my first time venturing into doing so. Do you have any articles that will point me in the right direction. Also can I set it up so the replication is instant (i.e. not daily, or something else)?Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-16 : 13:46:09
|
quote:
Originally posted by Vinnie881
I guess I could, but this is my first time venturing into doing so. Do you have any articles that will point me in the right direction. Also can I set it up so the replication is instant (i.e. not daily, or something else)?Thanks
Yup. you can have transactional replicationhttp://www.databasejournal.com/features/mssql/article.php/1438201 |
|
|
|
|
Best Practice (Multiple Servers)