How to replace linked server calls in SQL Server?

Question

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?

If there is a consistence in the alter procedure, then you could write a C# application and connect to the new database and replace the alter procedure. Its also a good Idea to post some of your alter procedure here so we could have a look if there is to much different between them. — Alen.Toma
– Alen.Toma, Commented Sep 1, 2021 at 4:25
@Alen.Toma Thank you for your suggestion. Like what you've said, I could make a script or C# program that automates the altering of the procedures. But I was curious if there are any SQL server built-in features for this kind of work. — itsnothingg
– itsnothingg, Commented Sep 1, 2021 at 4:37
Although MS does not officially support creating linked servers that point to the local server, in practice this does work, except for breaking distributed transactions -- but as long as you're not using those it works fine. For this specific scenario you do have to watch out for recursion or duplicated data, in case any procedures could inadvertently end up calling themselves or inserting data twice while trying to do it on the "other" server. Obviously this should only be used as a stepping stone to a more permanent solution, though. — Jeroen Mostert
– Jeroen Mostert, Commented Sep 1, 2021 at 7:20

Dayat San · Accepted Answer · 2021-09-01 08:08:11Z

1

you can configure the link server that is by filling the Data Source with the name of the sql server instance used.

answered Sep 1, 2021 at 8:08

Dayat San

532 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to replace linked server calls in SQL Server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related