April 26, 2012 at 3:41 am
hi .
i have 3 servers ,and each of them have sql server 2008 and same table but without same data .
so i want to send my query (ex search query ) to all 3 servers .
how can i do that ?
tnx.
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 4:02 am
Pls provide some more details. From where you want to fire that query?
Whar do u expect in the returned resultset?
Larry Page(Ehsan.Akbar) (4/26/2012)
hi .i have 3 servers ,and each of them have sql server 2008 and same table but without same data .
so i want to send my query (ex search query ) to all 3 servers .
how can i do that ?
tnx.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 26, 2012 at 4:11 am
S_Kumar_S (4/26/2012)
Pls provide some more details. From where you want to fire that query?Whar do u expect in the returned resultset?
Larry Page(Ehsan.Akbar) (4/26/2012)
hi .i have 3 servers ,and each of them have sql server 2008 and same table but without same data .
so i want to send my query (ex search query ) to all 3 servers .
how can i do that ?
tnx.
it is like a google .all page is saved in different location and when you search some thing your query is sent to different server and you get your result ,as you see in google
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 4:41 am
in essence replication is used to transfer data from one server to another
so do you only want to execute the search query once, on one server? if so you need the complete set of data on that server so yes you will need to look at something like replication.
or are you happy with each server having a different subset of data which forms one big data set? if so you will need to execute the query 3 times in order to see the full picture so something like using linked servers might be the way to go to query the different parts and return them as one big data set.
April 26, 2012 at 4:52 am
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2012 at 6:16 am
anthony.green (4/26/2012)
in essence replication is used to transfer data from one server to anotherso do you only want to execute the search query once, on one server? if so you need the complete set of data on that server so yes you will need to look at something like replication.
or are you happy with each server having a different subset of data which forms one big data set? if so you will need to execute the query 3 times in order to see the full picture so something like using linked servers might be the way to go to query the different parts and return them as one big data set.
in fact my servers have different subset of data ,so you think i should use linked servers?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 6:20 am
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.
my data in each servers are different ,so i just want to increase my performance !
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 6:24 am
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.
I am not sure but I think it is better done from the front end coding (i.e. C# etc.). What I can think of is initializing 3 different connections with the Command to execute as the search query. Later on you can analyze the data set that holds the returned result set.
April 26, 2012 at 6:39 am
Larry Page(Ehsan.Akbar) (4/26/2012)
in fact my servers have different subset of data ,so you think i should use linked servers?
Yes, I think you should use Linked Servers. Replication is when you want to replicate the data to various servers. But, in this scenario I think you just want a result set from tables which are on different servers based on a search condition right??....
You can use Linked Servers as follows:
Select * From Test Where ID In(2,4,38,39)
Union
Select * From Vimal.Sourabh.dbo.Test Where ID In(2,4,38,39)
In the above piece of code the table "Test" is on my local server which contains Ids from 1 to 40 and "Vimal.Sourabh.dbo.Test" is on a linked Server and contains Ids from 1 to 20. The Where clause holds my Search predicates.
The result set fetched here is a combination of data from both tables. I think you could extend this to use three tables.
Hop this helped you.
April 26, 2012 at 6:39 am
Divine Flame (4/26/2012)
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.I am not sure but I think it is better done from the front end coding (i.e. C# etc.). What I can think of is initializing 3 different connections with the Command to execute as the search query. Later on you can analyze the data set that holds the returned result set.
this way could increase your network traffic
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 6:43 am
whatever way you tackle the approch you will increase network traffic
linked servers will establish the connection to server1, then it will talk to server2 and server3 so in essence you have 3 connections open at one time
if you code it in C# you will need 3 connections each to a server which then comes back and interprets the dataset in the app, either way its additional traffic
April 26, 2012 at 6:44 am
Larry Page(Ehsan.Akbar) (4/26/2012)
Divine Flame (4/26/2012)
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.I am not sure but I think it is better done from the front end coding (i.e. C# etc.). What I can think of is initializing 3 different connections with the Command to execute as the search query. Later on you can analyze the data set that holds the returned result set.
this way could increase your network traffic
That you are going to face any way.
April 26, 2012 at 6:44 am
vinu512 (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
in fact my servers have different subset of data ,so you think i should use linked servers?
Yes, I think you should use Linked Servers. Replication is when you want to replicate the data to various servers. But, in this scenario I think you just want a result set from tables which are on different servers based on a search condition right??....
You can use Linked Servers as follows:
Select * From Test Where ID In(2,4,38,39)
Union
Select * From Vimal.Sourabh.dbo.Test Where ID In(2,4,38,39)
In the above piece of code the table "Test" is on my local server which contains Ids from 1 to 40 and "Vimal.Sourabh.dbo.Test" is on a linked Server and contains Ids from 1 to 20. The Where clause holds my Search predicates.
The result set fetched here is a combination of data from both tables. I think you could extend this to use three tables.
Hop this helped you.
thank you .do you think link server could increase search performance ,or is better to have all data in one server and execute search query on it?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 6:50 am
Nothing could be better than having all the data in one server. Both Linked Servers and coding from the front end would be more or less the same in terms of performance.
Here the performance in your case takes a hit due to both - the no. of connections and may be the amount of data that is being transported.
So if you can keep all the data in one server it would definitely be better.
April 26, 2012 at 6:56 am
Larry Page(Ehsan.Akbar) (4/26/2012)
Divine Flame (4/26/2012)
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.I am not sure but I think it is better done from the front end coding (i.e. C# etc.). What I can think of is initializing 3 different connections with the Command to execute as the search query. Later on you can analyze the data set that holds the returned result set.
this way could increase your network traffic
Choices:
1) Create linked servers and union the results sets
+ data kept separate and updated separately so no issues with table structures
- increases network traffic when the query is run
2) Use transactional replication so that all 3 servers have all 3 databases (separately) ie Server1 DB replicates to Server2 and Server3, Server2 DB replicates to Server1 and Server 3 etc in a spiders web network
+ data kept separate and updated separately so no issues with table structures
+ queries will use the local version of all 3 DB at all sites
- increased network traffic during replication
3) Use merge replication (with changed schema to include UNIQUEIDENTIFIER column if you do not have it already).
+ local DB used for queries
- Schema change
- possibility of merge conflicts
- increased network traffic during replication
.... (other choices are available on request)
No magic wand here.
Fitz
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply