I have two instances of SQL Server 2005 Standard Edition running on two separate dedicated servers (let's call them DB1 and DB2). At the moment DB1 is very busy and DB2 is mostly idle. Is it possible to configure the instance running on DB1 to utilize the processor on DB2?
For example, I connect to DB1 and execute "sp-heavy-going" and I would like DB1 to process this request using DB2's processor. Whats the best way to go about achieving this?
The short answer is no. The long answer is, yes, sort of. If you spend a ton of time making sure that your database and application are setup to handle this.
If you were to host the same database on both servers, and use SQL Replication to ensure that the databases are synced between them, then you could put a load balancer in front of the SQL Servers and have clients connect to the load balancer instead of a single SQL Server. However this creates it's own set of issues that have to be addressed first.
A better option might be to look at federating your database so that half the data is stored on each server and data is queried from the server which holds that data. However this is also an extremely tough thing to get setup correctly.
Has the database been properly tuned so that the indexes are all created in the best possible setup?
