SQL Server Performance Forum – Threads Archive
Linking SQL Server Databases
Good Afternoon everyone. I am kind of new to database design using SQL Server.How could I go about linking multiple SQL Server Databases? And what are all design factors to consider? And most importantly, why one would want to have multiple SQL Server Databases Linked for his or her application?
I will truly appreciate any help
Thank you in advance
Bensta Tarik Bensehil
See Distributed Queries and Distributed Transactions in Books online to have all the help on linked servers and distributed queries. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Is there a web site you could direct me to please?
And when you say linked server and distributed queries, is it the same thing as with microsoft access? That is, is it similar to linked tables?
And lastly, from a perforamce point of view, can ditributed queries be a bad thing to have? If so, why would you want to have linked servers and ditributed queries? What would be a typical situation?
Thank you very much for your help in advance.
Bensta Tarik Bensehil
> How could I go about linking multiple SQL Server Databases?
When you say "multiple SQL Server Databases", do you mean multiple SQL Server or multiple databases on the same server? Bambola.
I have no idea what these things mean in Microsoft Access. Distributed queries are required when the SQL Server data has to be queried from another instance of SQL server on same / different machines or for querying data from some other data source than SQL Server. This may be the situation for partitioned data across servers or for data migration etc. Yes distributed queries are not good from performance point of view and should not be used until absolutly required. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
I am under the impression that with "linking multiple SQL Server Databases" Bensta was refering to different database on the same server rather than different servers. Bensta – if this is the case than the solution is quite simple. If you want to refer to a object (table sproc function etc) on a different database on the same server, refer to it with
database_name.owner.object_name. calling a stored procedure my_stored_procedure from db_1 owned by dbo from db_2 will look like this exec db_1.dbo.my_stored_procedure Bambola.
That is exactly what I was refering to. Different databases on the same server rather than different server. In access, however, you have the ability to link to other tables in others databases. The reason you would do this is when you want to you connect to as sql server through an ODBC or you want to use a table in another database as a lookup or if you want to create a client/server application in Access ( you will have 2 database files, a backend and a front-end) In the front-end you link tables to the backup-end.
Thank you
Bensta Tarik Bensehil
Is there a web site you could direct me to please?
Books online are installed by default with the SQL installation and for the latest release of BOL get from this link http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp]. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Bensta, Specifically, you may want to read these topics on SQL Server Books Online:
1. linked servers, access an Access Database
2. linked servers, configuring Jon M
]]>