We were trying to figure out a way of having a dynamically encoded database name in our sql queries for a series of reports working off two databases. We needed to have the second database on a different server and we wanted to replicate this on our development environment as well as making deployment easier.
After spending a while looking into dynamic sql queries (not a great idea) and report parameters I decided to have another look at linked servers.
Linked servers are a means in SQL Server (probably exist in other db worlds- haven’t researched) of connecting two servers so that you can access database objects from one server within queries hosted in another.
To set them up:
- Go to “Server Objects” of the database server where the linked server will be added and right click on “Linked Servers”. Select “Add New Linked Server”.
- In the “General” tab, add a name for the new linked server in the “Linked Server” field.
- Select “Other data source”and for “Provider” select “Microsoft OLE DB for SQL Server”
- For “Product Name” type in “SQLOLEDB”
- In the “Data Source” field, enter the IP address of the server to be linked (this can be local)
- “Catalog” is the name of the database on the linked server and is optional (db2 in my case)
- Go to the “Security” tab and select “Be made using this security context”. Type in the remote login and credentials. Naturally set this to be a user with only the necessary permissions and not all.
Credit to http://www.jensbits.com/2010/11/10/create-linked-server-sql-server-2008/ on the creation of linked servers
Where this becomes powerful is that you can set the datasource to be the local machine name or ip so in effect you can link a server to itself. This works brilliantly for development as we can set our sql query using the linked server name and have it configured to multiple environments without touching the code.
To use a linked server in code you prefix the tablename with the name of the linked server so
select * from database.dbo.table becomes
select * from linkedservername.database.dbo.table
This works out to be quite a tidy implementation. Hope it helps someone else out there.