Aug 31 2021 07:32 PM - edited Aug 31 2021 09:42 PM
I have two SQL Server database instances - let's call them server A and B.
Sadly, as the user traffic decreases, there is now no need for running two servers anymore. So I'm planning to merge databases on server B to A.
But here's the problem: there are thousands of stored procedures and triggers on server A that connects to server B through linked server and vice versa.
When merged, there is no need to use linked server so I'm going to have to replace all of them.
It might be possible to keep linked server and make it to point itself? But it seems like a bad practice.
How can I effectively replace all of them? Do I have to write thousands of ALTER SQL scripts?
Aug 31 2021 10:18 PM
Solution@intackchoi , use SSDT = SQL Server Database Tools, create a database project, import database design, replace all references to the linked server and deploy the changes to your SQL Server.
Aug 31 2021 10:18 PM
Solution@intackchoi , use SSDT = SQL Server Database Tools, create a database project, import database design, replace all references to the linked server and deploy the changes to your SQL Server.