Fixed database paths in stored procedures/views - is there a smoother way of doing this?

Question

Our developers love to create lots of different stored procedures/views, which extracts data from lots of different databases on different servers.

Every time we migrate to a new database server, something breaks because some obscure procedure or view didn't get fixed at the same time. Documenting them all is already underway.. I still have to ask - isn't there a simpler way of doing this?

Would it be possible for a stored procedure to extract the connection string from a table - let's say "dbo.linked-databases", and have a unique row per database being references, where one of the columns specify the connecting string? Can stored procedures then SELECT this, and expand it as a variable?

Sorry if the question doesn't make any sense, as I'm quite new with SQL.

Answer 1

A linked server definition includes the name of the server that you are connecting to and includes the security settings for the linked server. If you (assuming Microsoft SQL Server) "SELECT * FROM master.sys.databases" you will see all of your linked servers.

Therefore, if you script out all of the linked servers and recreate them on the new server, then you should be able to set up the new server just the same as the old server. (Exception is that any SQL Server logins used in linked servers will not include the passwords, so you will need to update the scripts.)

Of course, the other issue is that one of the servers that you link to will change and you need to update your linked server settings in the primary server.
From that you can determine which linked server definitions need to be changed.

I suggest that you name your linked servers according to their purpose, rather than with a server name. (E.g. FinancialUpdates rather than AccountingSQL005.) That way any code hidden away in stored procedures will not need to be changed when some server name changes or a database is moved to a new server. This also suggests using separate linked servers, even if pointing to the same physical server, for different sets of data.

Just update the linked server by scripting out the definition and change the server name. Then use sp_dropserver and sp_addlinkedserver to drop the old definition and add the new one.

Answer 2

I wouldn't recomend it because of the security implications required to implement such a process.

Perhaps you should have a specific documented process to do it, say etl or a staging area for that data to be made available to different systems. This would make moving the source or target a much easier process.