Linked tables in SQL Server 2008 (R2)

Usually Google is my friend, but looking for SQL Linked tables gave me some frustration. Admittedly, I searched for the wrong thing...

My problem

Consider 2 identical databases on 1 (SQL 2008) server. The data is -off course- different, however some tables need to be 'in sync'. My initial thought was to make database one the lead database, storing the data. In database two I delete that particular table and create a linked table to database one. Just like one would do in MS Access...

Problem solved?

Hold that thought: "Just like in access"... Short version of the story: there are no linked tables in SQL Server!

Problem solved!

It turns out in SQL Server it's not "linked tables", it's Synonyms!!! So when I did a search on Synonyms Google showed a wealth of information.


Some things I stumbled into when replacing the tables with synonyms:

• First remove all foreign keys related to tables which are being replaced by synonyms
• Then remove the primary keys of tables to be replaced
• Synonyms can not have FK constraints!

So  linking tables in SQL turns out to be as easy as:

 DROP TABLE [dbo].[myTable]  
 CREATE SYNONYM [dbo].[myTable] FOR [TheOtherDatabase].[dbo].[myTable]