Two sql server connection problem?

Question

How to connect two SQL Servers on private LAN (i.e., i have a private networked LAN with server configured as Domain Server), one (SQL Server EE) on Windows 2003 EE on server and another (SQL Server Personal Edition) on WinXP Pro on client?

Answer 1

You've got a lot of options. In order to answer specifically the problem you're trying to solve needs to be better defined. I'll assume that you want to run a query on server "A" against data on server "B". One option here would be to set up a linked server (see the link to SQL Server BOL below). The easy way is to run sp_addlinkedserver, but most people like GUI's. In Enterprise Manager on server "A" go into the Security folder. There you can define the connection to server "B". Once you've established the connection, in a query window you can call server "B" data from server by using fully qualified queries: SELECT ... FROM servername.db.owner.table. Again, it's all laid out in the link below.



That's one option. Others include using SSIS to move data between servers, providing two connections through your application layer, replication, database mirroring, log shipping... It really depeneds on what you're goal is.

Answer 2

Establish an ODBC connection to the servers from the client that wants access to the SQL server. Connection details are LAN specific (Username/Password/hostname or IP/etc)



In general though the previous poster is correct. You don't connect 2 server together unless you are using clustering. In which case you need to read the Microsoft documentation on configuring a Microsoft SQL cluster.

Answer 3

servers don't connect to each other.

are you trying to sync data between the two? i'd read the documentation on sql server for that.

Answer 4

you'll favor to create a database link between both databases. I don;t understand if referential integrity assessments will span those links that you'll try this, you need to favor to create triggers to finish the assessments your self. i does not propose that though because it places added overhead on processing.