Updating external data sources in SQL Server
I recently had to create a copy of multiple Azure SQL databases. They were all interconnected, i.e. each database had some other databases registered as external data sources. After the import, these data sources had to be updated with new database locations and credentials.
Since I didn't know which external data sources were registered in each database, I started by listing them:
SELECT * FROM sys.external_data_sources;
And also the credentials they used:
SELECT * FROM sys.database_scoped_credentials;
From here on, I only had to update them all with new information:
For credentials, I set the new login and password:
ALTER DATABASE SCOPED CREDENTIAL RemoteCredential WITH IDENTITY = 'remoteUser', SECRET = 'remoteUserPassword';For external data sources, I set the new server and database names:
ALTER EXTERNAL DATA SOURCE [RemoteDataSource] SET LOCATION = N'new-server.database.windows.net', DATABASE_NAME = N'NewDatabase';
To make sure the configuration was correct, I tried querying an external table from each external data source.
After creating a copy of a database with external data sources, these need to be updated to point to corresponding copies. Otherwise, they will still point at the original database instead of the copy.
