If your database contains stored procedures, views, or functions which reference objects via a linked server, you may see an error such as the following appear in SQL Change Automation when verifying projects or running a build as part of continuous integration:

Msg 7202, Level 11, State 
Could not find server 'N' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

In some cases, a similar error might appear, which is:

OLE DB provider "SQLNCLI11" for linked server "N" returned message "Login timeout expired".

How SQL Change Automation verifies your project

SQL Change Automation verification and build processes run the migrations and programmable objects in your project against a real SQL Server database to identify problem before deployment occurs. When verifying your project, the code in your project is run against your shadow database.  When running a build, SQL Change Automation calls the Invoke-DatabaseBuild PowerShell cmdlet, which will either utilize a database you specify by name or create a temporary database if you do not specify a database name.

SQL Server requirements to compile views, functions, and stored procedures which reference objects via linked servers

SQL Server enforces strict requirements when you create a programmable object such as a view, function, or stored procedure which references objects via a linked server.

In order to create the object, SQL Server will validate that the linked server exists on the SQL Server. If the linked server does not exist, SQL Server will report error 7202 cited at the top of this article and it will not create the object.

Further, SQL Server will attempt to access the remote object referenced across the linked server in order to validate that the object exists on the remote server. This validation occurs even within stored procedures: deferred name resolution cannot be used when linked servers are referenced.

These requirements mean that special configuration is often needed for verification and build processes in SQL Change Automation to succeed in organizations where development environments and production environments are implemented in separate domains with limited access between the domains. 

Options to handle references to objects across linked servers in SQL Change Automation projects

There are several techniques which can help verify and build SQL Change Automation projects containing references to objects across linked servers. Here are four possible approaches:

1. Provide access to the objects by connecting to the same linked server from the development and build environments

In some cases, it is possible to create the same linked server referenced in the code on the SQL Server instance which hosts the shadow database, as well as the SQL Server instance used to build the project. 

Because SQL Server accesses the linked server to validate remote objects when you create the views, functions, or stored procedures that reference the linked server, security on the linked server must be configured appropriately and network and domain configuration must allow connections to occur to the linked server. 

Considerations:

• While this approach does not require changing any code in your project, this configuration will not be allowed in some environments due to domain segmentation and security requirements
• This approach will generally not work when using cloud-hosted agents to run builds, as those agents typically will not be able to connect to your organization's other environments due to network, domain, and security configuration
• This approach relies on hard-coding server names directly in the migrations and/or programmable objects in your project, which many consider to be an anti-pattern

2. Implement synonyms with database clones or restored databases

Synonyms provide a useful layer of abstraction and enable extra flexibility in SQL Server. Synonyms are lightweight and exist in every edition of SQL Server. With this approach, you will:

1. Implement a process which restores or clones the databases containing the external object to the environments used for building
2. Add a new pre-deployment script to handle dynamic creation of synonyms. Name this file according to your preferences, one example would be to name it 02-Create_Synonyms. The .sql filetype will be applied automatically.
3. Add idempotent code to the pre-deployment script to create the appropriate synonym definition based on the environment. See 'Create a synonym based on dynamic criteria' for an example pre-deployment script.
4. Modify the stored procedures, views, and functions and replace all four-part object names with the local synonym 

Considerations:

• This approach requires modifying code within your project and will require a production change to deploy the synonyms and to change the views, functions, and stored procedures to reference synonyms instead of the four-part object names containing the linked server name.
• This approach gives you flexibility in configuring different environments: you can utilize a different linked server in your development environment or restore or clone all the databases to a single SQL Server instance if that is your preference.
• Custom variables can be implemented to avoid hardcoding server names into the pre-deployment script itself
• Each developer can run code against their own database which configures the synonym definition to properly reflect the setup in their environment. The default comparison options in SQL Change Automation specify that when a project already contains a synonym with a given name, SQL Change Automation will not generate a migration if the development database has the same synonym pointing to a different item.

3. Utilize a combination of synonyms and stubs within your project

If you wish to enable verification and builds to run in an isolated environment without needing to restore or clone copies of the databases which are referenced by the code, you can utilize a combination of synonyms and local 'stub' objects in your SQL Change Automation Project.

This approach is similar to the implementation described above, but instead of implementing a process to restore or clone the databases containing the external object you would create pre-deployment scripts with "stubs" of code that create local, empty representations of those objects.

For an example of stubs, see the Product Learning article, "Database Build Blockers: Cross-Server Database Dependencies." Note: this article describes a solution for deploying a SQL Source Control Project, but the concept of "stubs" remains the same.

Additional considerations, compared to the synonyms and restored databases approach:

• This approach requires modifying code within your project
• There is a chance that the definition of stubs within your project may drift for the definition of the production objects. You must manually keep them in sync
• This approach may be required if you are building in an isolated environment where you cannot configure linked servers or restore or clone databases to the SQL Server utilized for the build

4. Use a limited build process which does not run your migrations against an empty database

The approaches listed above may be difficult to achieve in some build environments, particularly if you utilize cloud-hosted agents to run builds and lack the ability to restore or clone databases to the SQL Server instance used for the build. 

If this is the case, you may elect to not run a full build in SQL Change Automation, and instead rely on test deployments to other environments such as QA and Staging to validate your changes. 

If you would like to skip the validation portion of the build and simply create an artifact representing the project, you can do this buy executing the New-DatabaseProjectObject PowerShell cmdlet instead of running Invoke-DatabaseBuild or utilizing a graphic extension to invoke the build. 

Considerations:

• This requires utilizing PowerShell for the build portion of your pipeline, instead of using a graphic extension
• This approach does not validate the code in your project against an empty database

Related articles

• If you are deploying a SQL Source Control project containing linked server references with SQL Change Automation, a similar approach to the "synonyms and stubs within your project" is described in the Product Learning article, "Database Build Blockers: Cross-Server Database Dependencies"