How to join tables from different servers
-
- Registered User
- Posts: 439
- Joined: 04 Jun 2004, 02:00
- Contact:
How to join tables from different servers
Hi All,
Fun question for those that are whey more awesome at SQL Server than I am.
How would I go about creating a select statement that would join two tables on two separate servers that join on a unique col.
ie.
Select Server1.DB1.Table1.col1 , Server2.DB2.table2.col1
from Server1.DB1.Table1 , Server2.DB2.table2
where Server1.DB1.Table1.col1 = Server2.DB2.table2.col1
This would return the same results next to each other. My logic is all there but the syntax just isn't.
Maybe I'm just being really wishful in thinking this is possible.
Any thoughts?
Fun question for those that are whey more awesome at SQL Server than I am.
How would I go about creating a select statement that would join two tables on two separate servers that join on a unique col.
ie.
Select Server1.DB1.Table1.col1 , Server2.DB2.table2.col1
from Server1.DB1.Table1 , Server2.DB2.table2
where Server1.DB1.Table1.col1 = Server2.DB2.table2.col1
This would return the same results next to each other. My logic is all there but the syntax just isn't.
Maybe I'm just being really wishful in thinking this is possible.
Any thoughts?
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: How to join tables from different servers
First of all you will need to understand the way naming works within SQL server
That done when linking between two diffirent servers you will need to use the full syntax.
That would do with the code. But before that will work you need to set up linked servers. for that you can have a read over here
Code: Select all
[Server Name].[Namespace].[dbo].[Table].[Coloumn]
Code: Select all
SELECT
[Server1].[DB1].[dbo].[Table1].[Col1],
[Server2].[DB2].[dbo].[Table2].[Col1]
FROM
[Server1].[DB1].[dbo].[Table1] JOIN
[Server2].[DB2].[dbo].[Table2] ON [Server1].[DB1].[dbo].[Table1].[Col2] = [Server2].[DB2].[dbo].[Table2].[Col2]
- Spoiler (show)
-
- Registered User
- Posts: 439
- Joined: 04 Jun 2004, 02:00
- Contact:
Re: How to join tables from different servers
Ah thanks.
Now before I can do anything like that I have to create a white paper on linked servers. (I hate white papers)
And there is no other way to reference a db located on a different server?
Thanks for the insight though.
Now before I can do anything like that I have to create a white paper on linked servers. (I hate white papers)
And there is no other way to reference a db located on a different server?
Thanks for the insight though.
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: How to join tables from different servers
You are welcomepsychotic_savage wrote:Ah thanks.
Don't we all. But hey they are there for a reason. Just google "SQL Linked Server Configuration" and you should have more than enough info on it.psychotic_savage wrote:Now before I can do anything like that I have to create a white paper on linked servers. (I hate white papers)
Nope. Unless your app is written in a way to actually pull data from multiple data sources and plonk it all together before you use it. But that is just stupid as it takes up more processing and clutters your code. If you do use this though in a live environment. I suggest you set up views on the server you will be using the data from. Say you actually have a cluster of about 5 servers with the databases on use one of them as your primary connection and create Views with unions between the diff tables/db's you want to draw data from. Or even stored procs. Then it is easy to change if need be and again it simplifies the code of the app which needs to use it. If not views at least get some stored procedures running to pull the data together for you. But in my experience Views with unions works best. Especially if you have the same db across multiple servers with different data which actually constitutes to a complete dataset. Whereas one of the servers alone might not provide the whole picture when put together in a union you get all the data. Did this on a Database with about 2.6Bil records in a single table to speed things up a tad. Had the data archived from the one to the next depending on how old it was and just pulled queries on the views.psychotic_savage wrote:And there is no other way to reference a db located on a different server?
- Spoiler (show)
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: How to join tables from different servers
You could create an SSIS package to copy the foreign table to a local temp table and then work on it from there.
Not the most elegant solution by any means, just another option
Not the most elegant solution by any means, just another option
-
- Registered User
- Posts: 439
- Joined: 04 Jun 2004, 02:00
- Contact:
Re: How to join tables from different servers
@ jamin_za, not really an option since copying a 70Gig Db has some major drawbacks main one is lack of space on the primary server.
Off topic: would anyone know of more advanced courses for SQL Server 2008 available in the Durban area. I really need to learn how to do these things on my ace. :lol
Off topic: would anyone know of more advanced courses for SQL Server 2008 available in the Durban area. I really need to learn how to do these things on my ace. :lol
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: How to join tables from different servers
Sorry to say but the more advanced stuff in IT is most of the time self study. You want to be a DBA.... Fine go do a course and get the basics then you have to go sit and play around... build up experience. It is not something you can simply go study out of a book.
- Spoiler (show)
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: How to join tables from different servers
Who said anything about copying the whole DB?psychotic_savage wrote:@ jamin_za, not really an option since copying a 70Gig Db has some major drawbacks main one is lack of space on the primary server.
Just copy the TABLE you want to work with from the remote server to a temp table in your local DB... that is pretty quick.
IT Intellect in Musgrave have a pretty clued up SQL trainer - Alfred is his name.Off topic: would anyone know of more advanced courses for SQL Server 2008 available in the Durban area. I really need to learn how to do these things on my ace.
The DBA courses are 70-432 and 70-450. 450 being the advanced one.
But like RRF said, the courses don't really teach you more than the basics. I've been at it for 2 years now and I'm still just a novice!
-
- Registered User
- Posts: 439
- Joined: 04 Jun 2004, 02:00
- Contact:
Re: How to join tables from different servers
That would explain why all the courses I look through are things I have already done at some point in time. I was kinda hoping for something epically advanced. I guess I'll need to use my good old friend google.
Between google and I we know everything.
How long have you been at the whole SQL thing Ruad?
I'm just about to finish my first 6 months and its been one hell of an up hill battle. Especially because there was no DB admin before and everyone pretty much did what ever they wanted. Can anyone say data heap
Between google and I we know everything.
How long have you been at the whole SQL thing Ruad?
I'm just about to finish my first 6 months and its been one hell of an up hill battle. Especially because there was no DB admin before and everyone pretty much did what ever they wanted. Can anyone say data heap
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: How to join tables from different servers
About 5 years but more on the programming side than actualy DBA stuffs. So there is stuff I know and stuff I don't. Each of us specializes in what you need to get your job done not that of the person next to youpsychotic_savage wrote:How long have you been at the whole SQL thing Ruad?
- Spoiler (show)
-
- Registered User
- Posts: 439
- Joined: 04 Jun 2004, 02:00
- Contact:
Re: How to join tables from different servers
All to true
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: How to join tables from different servers
I only picked up the linked server stuff due to working with it at C-Track. Those tracking databases are so big that they had to split it up in terms of the age of the data contained in the databases. They have the same db structure across 6 servers and the data gets moved from the one to the other depending on the age of the data. The one only contains data which has been received in the last hour. Then it gets moved to the next one containing the data for the past 24hours. Then a week. Then a Month. Then 3 Months. Then it goes into an archive. Using the linked servers we could actually pull decent queries on 1bil+ table sizes without feeling that it is actually that big. We managed to actually pull 1Month worth of tracking data (1 entry every 30sec for about 250 vehicles) in just under 10sec. Quite impressive actually.
- Spoiler (show)
-
- Registered User
- Posts: 439
- Joined: 04 Jun 2004, 02:00
- Contact:
Re: How to join tables from different servers
That is very impressive. Our DB's are also quite large and I may have to look into setting up some kind of linked server setup.
We weigh in at around 10mil records per table and a total of 150mil records per DB with 4-8 DB's per server Total of 6 servers.
The main issue is that every record needs to be able to be read at random intervals and every time a new record gets added its verified against the entire Table. As well as with other DB's tables this is really time consuming.
I still think its a bit of a development issue as the queries always seem to reference the entire DB via either a count or a rather large nested select statement.
When your data was being moved from table to table did you find a large amount of resources where used on that or was it optimized somehow?
We weigh in at around 10mil records per table and a total of 150mil records per DB with 4-8 DB's per server Total of 6 servers.
The main issue is that every record needs to be able to be read at random intervals and every time a new record gets added its verified against the entire Table. As well as with other DB's tables this is really time consuming.
I still think its a bit of a development issue as the queries always seem to reference the entire DB via either a count or a rather large nested select statement.
When your data was being moved from table to table did you find a large amount of resources where used on that or was it optimized somehow?
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: How to join tables from different servers
It was optimized. I wasn't really involved in that part of the whole thing. What I do know is that they used a custom app to pull the data and push it to the new server. But whenever anything was selected,inserted or updated from the db's everything worked through either a view or stored proc which took care of where the data is located. In your case when you insert data you could have the stored proc do the verification checs which is much faster than actually going and pulling data and comparing it in a custom app outside of the db.
- Spoiler (show)
-
- Registered User
- Posts: 439
- Joined: 04 Jun 2004, 02:00
- Contact:
Re: How to join tables from different servers
Good Idea I have been looking at was to get the developers to start using stored procedures to speed things up.
We have also created an app to move all info older than 30 days to a new DB but on the larger DB's it gives issues by increasing the transaction log files to really inflated amounts.
Ah lifes great pretending to be a DB admin
We have also created an app to move all info older than 30 days to a new DB but on the larger DB's it gives issues by increasing the transaction log files to really inflated amounts.
Ah lifes great pretending to be a DB admin
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
-
- Moderator Emeritus
- Posts: 5547
- Joined: 21 Dec 2002, 02:00
- Processor: i7 3770k
- Motherboard: ASUS P8P67-Pro
- Graphics card: 2xNvidia GTX670
- Memory: 16 GB Gskill Sniper
- Location: In SQL Space inserting 'null' on purpose
- Contact:
Re: How to join tables from different servers
Please tell me your salary matches this statement, because honestly I get pissed off when I see someone not able to do the job get hired to do the job.Ah lifes great pretending to be a DB admin
I am not attacking you personally - just a generalisation (which may pose to be true to so many 'end user / server supporters') such as myself
Temporary Absence
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: How to join tables from different servers
We didn't have space issues as we had a nice large SAN to work with. Think about in the region of 30TB or something like that when I left. They also upgraded the storage every 3 months.
- Spoiler (show)
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: How to join tables from different servers
Anthro wrote:Please tell me your salary matches this statement, because honestly I get pissed off when I see someone not able to do the job get hired to do the job.Ah lifes great pretending to be a DB admin
I am not attacking you personally - just a generalisation (which may pose to be true to so many 'end user / server supporters') such as myself
-
- Registered User
- Posts: 439
- Joined: 04 Jun 2004, 02:00
- Contact:
Re: How to join tables from different servers
Ooh nice we getting SAN storage in a month or two but its more for a blade server that we going to be clustering our mirror and VM servers to. To bad I wont get to use this for the live environment. Triple redundancy. What a headache.
@Anthro: As for the salary its low. Our company hired me more because of my large over all knowledge of SQL, Hyper-V, Win 2008 and IIS oh and a bit of Dev. Enough to nudge Ron in the correct direction. (Ron: I know you're watching).
I might not know as much as I should to be a DB admin but I make up for it else where
@Anthro: As for the salary its low. Our company hired me more because of my large over all knowledge of SQL, Hyper-V, Win 2008 and IIS oh and a bit of Dev. Enough to nudge Ron in the correct direction. (Ron: I know you're watching).
I might not know as much as I should to be a DB admin but I make up for it else where
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
-
- Moderator Emeritus
- Posts: 5547
- Joined: 21 Dec 2002, 02:00
- Processor: i7 3770k
- Motherboard: ASUS P8P67-Pro
- Graphics card: 2xNvidia GTX670
- Memory: 16 GB Gskill Sniper
- Location: In SQL Space inserting 'null' on purpose
- Contact:
Re: How to join tables from different servers
On that note, everyone should try consider two income streams - we gonna need it...
Temporary Absence
-
- Registered User
- Posts: 439
- Joined: 04 Jun 2004, 02:00
- Contact:
Re: How to join tables from different servers
Does being a Ninja count
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
-
- Moderator Emeritus
- Posts: 5547
- Joined: 21 Dec 2002, 02:00
- Processor: i7 3770k
- Motherboard: ASUS P8P67-Pro
- Graphics card: 2xNvidia GTX670
- Memory: 16 GB Gskill Sniper
- Location: In SQL Space inserting 'null' on purpose
- Contact:
Re: How to join tables from different servers
****IN HELL YES !!, I sometimes find people who only specialise in one field get screwed over when they have to troubleshoot various environments, rather learn it all (your brain can handle it if your pocket wants it)
Temporary Absence
-
- Registered User
- Posts: 439
- Joined: 04 Jun 2004, 02:00
- Contact:
Re: How to join tables from different servers
I agree the only problem with knowing your entire environment is you become a go to guy. That's fine in the beginning but when things go wrong at all hours of the night and you always the one contacted first the brain stops working.
That was me last week. I even forgot my password Overtime was good though.
That's how I afforded my new PC.
That was me last week. I even forgot my password Overtime was good though.
That's how I afforded my new PC.
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD