does linked server solve performance issue??

Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server'
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved'
Thanks in Advance,
Peter Van Wilrijk.Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid => tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid => td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server'
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved'
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is run
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
>> Why don't you replicate the data back to the server that you're query is
>> run on, and select from there?
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>>ChrisR wrote:
>>Why don't you replicate the data back to the server that you're query is
>>run on, and select from there?
>>
>>Thanks, Good question?
>>I surely must start checking out how to implement replication ... but I
>>guess it will not be an option for our website, because our users, while
>>surfing ... read, insert and update data in all three databases. I guess
>>this means we should replicate continuously in two directions, so updates,
>>deletes and inserts on server 2 must be immediately available on server 1
>>and vice versa. Can replication do that?
>>Kind regards,
>>Peter Roothans.
>
>