Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are you using linked servers? Are all of the databases on SQL Servers? Or do you also include Access and other desktop software as databases? –  RLF Sep 16 '13 at 12:31
    
@RLF These are linked SQL servers, no access or anything similar to that. –  pauska Sep 16 '13 at 13:11

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Aaaaah! That last bit was the key information here, I did not know you could just drop it and recreate with a different server name, since the GUI didn't let us select a different name. Thank you, this solves it for us. –  pauska Sep 16 '13 at 13:38

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.