PCFormat Forum Archive

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?

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

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.

psychotic_savage wrote:Ah thanks.

You are welcome

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)

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:And there is no other way to reference a db located on a different server?

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.

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

@ 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

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.

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.

Who said anything about copying the whole DB?
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.

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.

IT Intellect in Musgrave have a pretty clued up SQL trainer - Alfred is his name.
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!

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

psychotic_savage wrote:How long have you been at the whole SQL thing Ruad?

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 you

All to true

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.

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?

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.

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

Ah lifes great pretending to be a DB admin

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.
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

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.

Anthro wrote:

Ah lifes great pretending to be a DB admin

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.
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

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

On that note, everyone should try consider two income streams - we gonna need it...

Does being a Ninja count

****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)

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.