Duplicate table on 2 different servers (MS SQL 2000/2008)

Ok here is the thing:

I have an old MS SQL 2000 server, and this one will keep running. however for a new website i h开发者_如何学运维ave a SQL 2008 server.

I need 3 tables from the old server (lets call it www.oldserver.com) to be on the new server too. the data on the old server still changes daily.

I would like to update the tables immediately when something changes on the old server. how do you do this. i looked at mirroring but that doesnt seem to be the way to go, now i've checked Import function in SQL Server management studio, but i dont want to import the data all the time. 1 import, then updated like daily are ok. so i guess i need to 'write a query to specify the data to transfer' but i have no idea how that query should look.

the import will go to a SSIS package so it can be scheduled.

what is the best practice here? how should i do it?

You could set up the old server as a Linked Server in the new server.

Then, you could create the tables on the new server not as tables, but as views, directly selecting from the tables on the old server.
Like this (on the new server):

create view OldTableOnNewServer as
select * from OldServer.OldDatabase.dbo.OldTable

Advantages:

• no replication/updating necessary - the data comes directly from the tables on the old server

Disadvantages:

• Network traffic: each time someone selects from the view, the new server will access the oldserver over the network
• Availability: if the old server is not available, the views on the new server won't work at all