SQL Server 2005 : Access Tables / Entities across Servers.
Step 1: Create a link between two servers
Goto Query Window
EXEC sp_addlinkedserver 'PRODSVR', N'SQL Server' GO
‘PRODSVR’ is the name of the Server.Execute it.
Step 2: Setup Permission between Servers.In Management StudioGotoServer Object >> Linked Servers >>
PRODSVR (See Image)
Right Click on PRODSVR goto Properties and make changes as given below.
Step 3: To access the tables in other server
Use the following hirearchy
To access Person.Address table from AdventureWorks database
Select * From PRODSVR.AdventureWorks.Person.Address
1. Update Production server with data from development server. (SSIS is alternate way)
2. Allow users to access production database without multiple logins. (windows authentication or SQL authentication)