How to successfully deploy databases with external references

Most database developers are dealing with databases that contain external references. Even if the database code is in source control, these external references can make it very difficult to deploy to new environments. In these multi-database environments, tools like SQLCompare and SQL Change Automation do not automatically resolve object-order across databases, resulting in errors during deployment.

One way to tackle this, which works especially well for CI pipelines, is to create facades for all externally referenced databases. A facade in this context is a database with the expected name, with the expected objects, but those objects are hollowed out and do not contain any dependencies. You can compare this concept to an interface in an object-oriented language. Once you have these facades, they can be used in a pre-deployment step, simplifying the rest of the deployment by effectively removing object-order dependencies with these external databases.

This article shows you how to find all references to external objects in your database and build the necessary facade scripts.

There are three steps:

1. Find all dependencies on external databases, meaning all objects which live in a different database which are referenced by your database. This includes tables, views, stored procedures, and functions.
2. Create shell objects for each module identified.
3. Add the creation of these facades to source control and make them part of your build process.

We’ll also touch on how to keep these scripts in sync without creating a maintenance nightmare.

Find all dependencies

The first step is to find all objects in other databases that your databases reference. To do so, use the following script. You will need to replace the list of databases (found in lines 7 through 9 below) with a list of all of your project’s databases.

While the example below lists multiple databases, in most cases, this list will contain only one (your) database. In other words, you don’t need to have a list of referenced databases; you need to provide only the name of the databases you are searching.

The result will be a list of all external database objects requiring a shell object to be created as part of the facade script.

Listing 1. FindDependencies.SQL

IF OBJECT_ID('tempdb..#dbs') IS NOT NULL DROP TABLE #dbs;
CREATE TABLE [#dbs]
(
  [database_name] NVARCHAR(MAX)
);
INSERT INTO #dbs
VALUES('SomeRandomDB1'),
      ('ServerB.SomeRandomDB2'),
      ('SomeRandomDB3');
GO
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE [#t]
(
[referencing_object] NVARCHAR(MAX),
[referenced_object] NVARCHAR(MAX),
[referencing_class_desc] NVARCHAR(60),
[referenced_class_desc] NVARCHAR(60),
[is_schema_bound_reference] BIT NOT NULL
);
GO
DECLARE @cmd1 NVARCHAR(MAX) = 
'
INSERT INTO #t
SELECT 
    QUOTENAME(CAST(SERVERPROPERTY(''MachineName'') AS VARCHAR(MAX))
       +''\''+CAST(SERVERPROPERTY(''InstanceName'') 
       AS VARCHAR(MAX)))+''.''+
      QUOTENAME(DB_NAME())+''.''+
      QUOTENAME(OBJECT_SCHEMA_NAME(SED.referencing_id))+''.''+
      QUOTENAME(OBJECT_NAME(SED.referencing_id)) referencing_object,
    QUOTENAME(ISNULL(SED.referenced_server_name,
      CAST(SERVERPROPERTY(''MachineName'') AS VARCHAR(MAX))+''\''
      +CAST(SERVERPROPERTY(''InstanceName'') AS VARCHAR(MAX))))+''.''+
      QUOTENAME(ISNULL(SED.referenced_database_name,DB_NAME()))+''.''+
      QUOTENAME(SED.referenced_schema_name)+''.''+
      QUOTENAME(SED.referenced_entity_name) AS referenced_object,
    SED.referencing_class_desc,
    SED.referenced_class_desc,
    SED.is_schema_bound_reference
  FROM sys.sql_expression_dependencies AS SED
 WHERE SED.referenced_database_name IS NOT NULL;
';
DECLARE @cmd2 NVARCHAR(MAX) = 
(SELECT 
   STRING_AGG('EXEC '+DBs.database_name+
      '.sys.sp_executesql @cmd,N'''';','')
     WITHIN GROUP(ORDER BY DBs.database_name) AS cmd
  FROM #dbs DBs
);
EXEC sp_executesql @cmd2,N'@cmd NVARCHAR(MAX)',@cmd1;
--SELECT * FROM #t AS T
SELECT DISTINCT T.referenced_object FROM #t AS T;

Create shell objects

For each external database, create a single facade script that contains all shell objects. Start by scripting out the objects as-is and place them into the file, separated by GOs. You will encounter four different object types: tables, stored procedures, views, and functions.

Tables

Tables can be left as is, but it is advisable to remove foreign key constraints:

Example Table with Foreign Key and its facade object

Original Table

Facade Table

Stored procedures

Stored procedures require the body to be replaced with a single return statement, as in the following example:

Example Stored Procedure and its facade object
Original procedure	Shell procedure for facade

Views

For views, the facade needs to match the original in the following:

• the same name
• the same return column names
• the same return data types

One way to take care of the return columns is to generate a SELECT statement that returns NULLs converted into the correct data types with the correct column names.

If you use Redgate SQLPrompt, this can be achieved easily by following these steps.

1. Open a new query window connected to the database with the original object and write the following two lines, but don’t execute them. Be sure to replace dbo.View1 with the name of the original object.

2. Hover over the second #t and click on the yellow box that appears.

3. In the box that opens, make sure that the Script tab is selected and click on the Copy button.

4. Open a new query window and paste the resulting script.
5. Highlight the list of columns
6. Run the following search and replace. Make sure that regular expressions are enabled (the .* button is highlighted) and Selection is selected in the scope dropdown. Please note that this search and replace RegEx will handle the majority of cases, but not all, so make sure you review the results. This is particularly important if you have non-word characters, like spaces, in your column names.

Search	^\s*(\S+)\s+(\w+\s*([(][^)]*[)])?)\s*(\s(NOT\s+)?NULL)?(,?)\s*$
Replace	$1 = CAST(NULL AS $2)$6

7. After executing replace-all, you should see something similar to the following. Make sure that you don’t change the selection.

8. Click OK, and again without changing the selection, copy the result of the replacement.

9. Now paste the list into the facade object’s create statement as shown in the following screenshot:

Functions

Similar to views, table-valued functions must match the original in the following:

• the same name
• the same parameters
• the same return column names
• the same return data types

The same process shown above for views can be used for table-valued functions. However, when dealing with a function, SQL Server requires that all parameters are specified. For these purposes, though, it is enough to use DEFAULT for each parameter. In the example below dbo.Function2 has one parameter specified:

Note: Both multi-statement and inline table-valued functions can be replaced with an inline shell function like this:

Should one of the objects be a scalar-valued function, you can just replace the body with a RETURN NULL as shown below.

Facade of a Scalar-Valued Function
Original function	Shell function for facade

Facade scripts in source control

In the end, your facade script for each external database should look something like this:

Once the facade scripts are complete, check them into source control alongside your database code to be kept in sync and always available.

Now when creating an environment from scratch, you will first create all the databases involved (without any objects). Then you will need to run the appropriate facade script in each external database to create all shell objects. Once you have done that, you will be able to create all actual objects in your database.

One way to achieve this is to execute the following steps in your pre-deployment:

1. Drop all existing databases, or better yet, get a new SQL Server instance altogether – for example, by using Docker containers or Spawn.
2. Create empty databases, both your database and all databases to which there is an external reference.
3. Execute the facade scripts in their respective databases to create the shell objects.

After these pre-deployment steps are complete, you will be able to create all objects in your database using your tool of choice.

But what about maintenance?

These facade scripts are created manually and, as such, have to be maintained manually. This might feel like extra work. However, in our experience, this overhead tends to be minimal because the referenced databases do not dramatically change on short notice.

If you are using TDD (test-driven design), your facade will be naturally updated as part of your development activities. If you are not using TDD, your CI environment should still alert you to all necessary changes.

Deploy databases with external references

If you’ve followed the steps above, you’ll be able to deploy your database into a new environment or a CI environment with ease.

If you are writing tests (please write tests), you need to consider that the external objects are just a shell. Your tests cannot rely on the original implementation of these shell objects. However, this is a best practice to follow anyway, because writing tests that depend on code outside of your control can make your tests fragile and significantly increase the cost to maintain them.

 

The post How to successfully deploy databases with external references appeared first on Simple Talk.

from Simple Talk https://ift.tt/3Fdco8T
via