Two servers and one webpage - need data from both

[kev510]

Hello everyone.
Recently, our company added on a new server so that we can split the data created & used by our web pages between the two servers.

Lets call them server1 and server2. Server1 must have table1 and server2 must have table2. I'm looking for the best way to retrieve and write data to both servers, using our web pages that are running from server1.

One way I thought of is creating a query pointing to the server2 table, but someone said that it's an inefficient way to go about the situation (the data in table1 and table2 are very large).

They instead suggested that I should set a variable with server information in global.asa, then just use strConnection to open that server each time I use the table.

What do you think will be the best way for this to be done?

Thank you for your time!!

 

[MissyEd]

If you don't need to join both tables in a query, then that's how we do it - one connection string per server. Don't know if we use global.asa, that might be a question more suited to a .NET or ASP forum I think.

If you do need to join both tables, I would look at Linked Servers in BOL. They are relatively easy to use - you would be using a 4 part name instead of a 3 part name i.e. servername.databasename.owner.tablename. There are a few things you can't do, so best have a good read and decide if it applies to your situation.

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter