Search code examples
sqldatabasemove

SQL Server database move to another server

I have two SQL Server databases that are being used for my application.

The issue is that now we have decided to move one of the databases to another new server due to performance issues.

What's the best method to ensure all the joins in the SQL queries will work correctly? Do I need to update each join between the databases to so it can reference the database on the new server?

I have already updated web.config with the change but this wont affect the joins in the queries

Any advice would be great

Thanks

Solution

  • For this you will need to configure the second server as a linked server: http://msdn.microsoft.com/en-GB/library/ms188279.aspx

    Queries on the first server can then be updated to access tables on the second.

    Be aware though, any queries that join tables across the two servers will usually be very slow - more so if the tables have large numbers of rows.