Linked Tables - SQL Server 2008 to SQL Server 2005

Question

Hey all,

I have a database server that currently has two databases, call them [A] and [B].

[A] is the standard database used for my application [B] is used by another application (that puts considerable load on the server), however, we use a few tables sparingly (that are shared between my application and the primary application that utilizes [B]). Recently the use of [B] has been increasing and it's causing long wait periods and timeouts in my application.

I'm open to alternative ideas, but at the moment the quickest potential solution I've come up with is to get a second database server and move [A] to that. However, I do need access to some of the tables in [B] - ideally with as little code changes as possible.

We were thinking a setup something like:

Current: DB Server 1 {[A],[B]} (SQL Server 2005)

New Setup DB Server 1 {[B]} (SQL Server 2005) DB Server 2 {[A], [B]} (SQL Server 2008)

Where in the new setup, DBServer2.[B] is a linked table (kind of) to DBServer1.[B]

I looked into linked databases, from my understanding (limited) those work as an alias to a database on another server, which is almost what we want, except it adds the extra server qualifier, and it works on the db level. I'd like to do something like this, but ideally without the server qualifier and on a table level.

So for example, [B] has tables Users and Events. The Users table is updated weekly by batch, and we use it often, so we'd like to have a local copy on the new DBServer2. However, Events we use far less often and needs to be real-time between the two servers. In this case, it would be great to have Events as a linked table.

Further, it would be fantastic if we could use the same markup for queries. Today to query one db from the other we'd do something like

select * from b.events join a.dates

We'd like to continue that, except have the database server know that when we touch events it's really located at dbserver1.b.events.

Does that make sense? I confuse myself sometimes.

Thanks for any help ~P

Answer 1

"Alternative Idea" from me since you said you are open...

How about looking into the cause of the slowness? What is the "Load" you are measuring? How are your disks laid out on the server?

Maybe you could use more memory, another CPU or Some SQL tuning?

Fixing your issues with a software or hardware fix MAY be faster than getting a server and doing all the installs and then working through the integration problems you may run into.

Answer 2

You can use synonyms for linked objects - http://msdn.microsoft.com/en-us/library/ms177544%28SQL.90%29.aspx

This unfortunately only works for single objects, you CANNOT make a synonym for linkedserver.databasename and then reference synDBName.table.