As a DBA, have you ever faced a situation where you have to move only some of the databases from one server to another, whilst leaving the other databases where they are? Here are some example scenarios:
- On your SQL Server that hosts several databases, you find that one particular database is resource hungry, and uses a lot of CPU and IO. This is affecting the performance of other databases on the server. You decide to move this one database to another server with higher specification.
- Your development team wants to make use of newer T-SQL features for improving the performance of a database. They want you to upgrade the SQL Server to the latest version – but the other databases on the older server are not ready to be upgraded to newer version. You now need to move this database to a new SQL Server running the latest version.
In these example scenarios, before moving that one database to a newer server, you need to be sure that there are no cross-database dependencies between the database being moved and the rest of the databases on the server. It is quite common to have stored procedures or views that reference tables or views from other databases, or execute stored procedures from other databases. If you ignore this, and move the database to another server, your queries/stored procedures/views etc. will fail with errors like below:
Msg 208, Level 16, State 1, Line 2
Invalid object name ‘pnl.dbo.QuarterlySnapshot’.
How to identify cross database dependencies?
As part of impact analysis, there are a few steps you can take to identify most of the cross-database dependencies:
Step 1: sys.sql_expression_dependencies
sys.sql_expression_dependencies is a SQL Server system catalog view that tracks the relationships between database objects (like tables, views, and stored procedures). It returns a row for every by-name dependency, showing which objects depend on others and exactly which columns are referenced.
The below query uses the information from sys.sql_expression_dependencies catalog view and joins it with other metadata catalog views to present meaningful dependency information. The WHERE clause of the query filters out in-database dependencies, as we are only interested in cross-database dependencies at this moment:
/*
Written by: Vyas Kondreddi
Website: https://dutyDBA.com/
*/
WITH RefCTE
AS
(
SELECT
DB_NAME() AS CurrentDBName,
d.referencing_class_desc AS ReferencingObjType,
CASE d.referencing_class
WHEN 1 THEN OBJECT_SCHEMA_NAME(referencing_id) + '.' + OBJECT_NAME(referencing_id)
WHEN 12 THEN trg.name
WHEN 13 THEN strg.name COLLATE database_default
END AS ReferencingObjName,
CASE WHEN d.referencing_minor_id <> 0
THEN c.name
ELSE ''
END AS ReferencingColName,
COALESCE(d.referenced_server_name, 'LOCAL_SERVER') AS ReferencedSrvName,
COALESCE(d.referenced_database_name, 'LOCAL_DATABASE') AS ReferencedDBName,
CASE WHEN DB_ID(d.referenced_database_name) IS NULL THEN 0 ELSE 1 END AS ReferencedDBExists,
d.referenced_class_desc AS ReferencedObjType,
COALESCE(d.referenced_schema_name + '.', '') + d.referenced_entity_name AS ReferencedObjName
FROM
sys.sql_expression_dependencies AS d
LEFT JOIN
sys.objects AS o
ON o.object_id = d.referencing_id
LEFT JOIN
sys.server_triggers AS strg
ON strg.object_id = d.referencing_id
LEFT JOIN
sys.triggers AS trg
ON trg.object_id = d.referencing_id
LEFT JOIN
sys.columns AS c
ON c.object_id = d.referencing_id
AND c.column_id = d.referencing_minor_id
)
SELECT *
FROM RefCTE
WHERE
(ReferencedDBName NOT IN(DB_NAME(), 'LOCAL_DATABASE'))
ORDER BY
ReferencingObjNameJust run the query in the database that you are planning to move. It will then show the other database objects this database depends on. Similarly, if you run this query on the other databases, it will show if any of them depend on the database being moved. This query returns the current database name, referencing object type and name and any column names. It also returns the referenced database name and object type and name of the referenced object.
The ‘referenced database name’ column in the output also shows the XML document names from XPath queries, even though they are not true database names. You can identify these false database names using the corresponding ReferencedDBExists column, which will show up as 0.
Step 2: sys.sql_modules
The metadata catalog view sys.sql_modules stores the definition (code) of database objects like stored procedures, functions, views etc. The idea here is to query the code stored in sys.sql_modules to search for the database name we are interested in. For example, if you want to see your database (Database1) has any references to another database named Database2, then run the below query in Database1. Remember to update the database name to a name of your choice on line number 9.
/*
Written by: Vyas Kondreddi
Website: https://dutyDBA.com/
*/
DECLARE @DBName sysname
--Change the database name in the below line as needed
SET @DBName = 'Database2'
SELECT
OBJECT_SCHEMA_NAME(m.object_id) + '.' + OBJECT_NAME(m.object_id) AS ReferencingObjectName,
o.type_desc AS ReferencingObjectType,
m.definition AS ReferencingObjectDefinition
FROM
sys.sql_modules AS m
LEFT JOIN
sys.objects AS o
ON o.object_id = m.object_id
WHERE
m.definition LIKE '%' + @DBName + '%'
ORDER BY
ReferencingObjectType,
ReferencingObjectNameNote: Not all returned rows indicate a true cross-database dependency, as the query is just doing a string match. For example, the query could pick up on a variable that has the same name as a database. You have to go through the code of the identified object to verify if there is a genuine cross-database depe.ndency
Step 3: sys.synonyms
A Synonyms is a database object that acts as an alias or alternate name for another database object (known as the base object). It acts as a pointer, allowing you to reference the target object without using its full name. You could have a Synonym in your database that could be referring to an object in another database, there by creating a cross database dependency. If you have code (stored procedures or views) that uses this synonym to access objects in other databases, then moving the database to a different server would break the objects that use this synonym.
The below query on sys.synonyms would highlight any synonyms that reference objects in a another database, specified by the variable @DBName. Simply update the variable value for @DBName and run the query in the database being moved.
/*
Written by: Vyas Kondreddi
Website: https://dutyDBA.com/
*/
DECLARE @DBName sysname = 'MyDatabase2';
WITH SynCTE
AS
(
SELECT
SCHEMA_NAME(schema_id) + '.' + name AS SynonymName,
base_object_name AS FullReferencedObjectName,
COALESCE(PARSENAME(base_object_name, 4), 'LOCAL SERVER') AS ReferencedServerName,
PARSENAME(base_object_name, 3) AS ReferencedDatabaseName,
PARSENAME(base_object_name, 2) AS ReferencedSchemaName,
PARSENAME(base_object_name, 1) AS ReferencedObjectName
FROM
sys.synonyms
)
SELECT
*
FROM
SynCTE
WHERE
ReferencedDatabaseName = @DBNameStep 4: Other places to check for cross-database dependencies
Apart from the above three steps, there could be other ways in whcih your database could end up with cross-database dependencies. These include:
- Linked Servers: Code within your database that is being moved, could be referencing linked servers to access objects from other dtabases or even from other servers. You could search your code (example from Step 2) to see if any of your objects are using those linked servers
- SQL Agent Jobs: You could have some code in SQL Agent job steps or jobs in other scheduling tools like Auttosys or Windows Task Scheduler. Check the scripts in your jobs to see if there are any cross-database references that need to be taken care of prior to the database move.
- SSIS Packages: Similar to the above, check your SSIS/DTS packages to make sure any cross-database dependencies are addressed.
- Other code repositories: Not all code is stored within the SQL Server database. If you have applications that run ad hoc SQL scripts and queries against your database, you have to go through that code to identify any cross-database dependencies.
Hope find the above pointers and code examples helpful in dealing with cross-database dependencies and relationships when moving databases. Please share your thoughts by leaving a comment below.
