CODEWITHSUNDEEP
sql-serverdatabasestored-proceduresarchitecturesoftware-design
Fakhar Ahmad Rasul
Fakhar Ahmad Rasul

Reputation: 1691

How to execute a stored procedure present in database A/Server A and query database B/Server B as well?

I have a configurations database with a lot of stored procedures and then I have a large number of databases, all are part of one system and on the same database server.

When the stored procedures in the config database are executing, they often query other databases as well and it is possible to do so because the configuration and all the other database are on the same database server.

But with time, as the data is growing, customers are growing, databases are growing, this one database server is slowing down. So now we want to take some of our databases and put them on a different database server but we are unable to do so because these databases and the configuration database are tightly coupled to each other because many of the stored procedures in the config database query other databases as well.

Is there some way I can execute a stored procedure present in config database / Server A, but this stored procedure is also querying database 2 on Server B?

If not then what could be the best approach to decouple all the other databases from the configurations database? I know getting rid of the stored procedures by implementing an ORM or something could be an option but that would be very time taking as we 1000+ stored procedures.

Upvotes: 0

Views: 101

Answers (1)

theYogiDBA
theYogiDBA

Reputation: 17

Let's say your configure database is Server A and all your user databases are on Server A and your SP are using 3 part naming to query multiple databases. Now you want to migration one of the databases (DB1) to Servers B. Now DB1 does not exist on Server A, it is now on Server B. You can then Create a place holder DB on Server A called DB1. Then you will create a linked server to Server B and in the Place holder DB1, you will create alias which uses the linked server object with the same table name. This way, no changes is required on your thousands of SP and its configuration. However, Linked Server may introduce performance problems as joining and indexing is less efficient.

Upvotes: 1

Related Questions