SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 View / Query based on 2 Servers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rischfre
Starting Member

Spain
15 Posts

Posted - 12/17/2013 :  17:09:09  Show Profile  Reply with Quote
Hi

I plan to migrate a Database from a server, to another server. The issue I see, is that there are several views which connect (INNER JOIN) a table from the database to be migrated and a database which will remain on the old server.
I never seen any view like that, is it possible? In this case how...

Thanks

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 12/17/2013 :  17:17:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
It's possible but you'll need to setup a linked server, which is going to slow down the query, possibly significantly. Both databases should be migrated if at all possible.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/18/2013 :  06:36:40  Show Profile  Reply with Quote
your best bet is to migrate the table also along with data to the new server inside same db if possible so as to avoid linked server/distributed queries.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
794 Posts

Posted - 12/24/2013 :  13:40:16  Show Profile  Reply with Quote
As everyone has stated - ideally you don't want to use a linked server for this. However, if you have no choice then you would need to created the linked server (with appropriate permissions) and modify all queries that need the remote data to either use synonyms (recommended) or 4-part naming.

With synonyms, you can modify the code to use the synonym and when you migrate the other database it is simply a matter of updating the synonyms (no code changes).
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000