Manage Cross-Database Development in Visual Studio

When streamlining our database development process, we first must get our databases under source control. Often, the code accesses other databases on the server. Whether it is another in-house database or a vendor database, dependencies make version control harder and deploying to multiple QA and UAT environments time-consuming.

Take a little time upfront and you will have the security of source control and the ability to deploy to multiple environments instantly. Here we will set up database projects and parameterize the references so when you get a new vendor release you can deploy to UAT and point to the vendor’s new version as easily as publishing a schema change to production.

We will walk through these 6 steps to manage cross-database development.

1. Create a database a project in Visual Studio
2. Add projects for referenced databases
3. Add references to database project
4. Replace the database name with a variable
5. Deploy to Production
6. Deploy to QA

Step 1: Create a database project in Visual Studio

Create a database project for your database(s). If you are new to Visual Studio Database Projects, see Create Your First Visual Studio Database Project before continuing.

Step 2: Add projects for referenced databases

For our example, I created CustomApp, VendorProd, and VendorVnext databases.

It is common for companies to reference Vendor databases directly in SQL objects such as views and synonyms.

In our CustomApp database, we have a view that references the Vendor database to get the version.

Our view:

CREATE VIEW dbo.VendorVersion
AS
SELECT VersionNumber
FROM VendorProd.dbo.AppVersion;

Our Project looks like this:

When we try to Build it, we get the following errors.

Create database projects in the same solution for the referenced databases as in Step 1. If you miss one, Visual Studio will let you know.

For our example, I added VendorProd and VendorVnext databases.

Step 3: Add references to the database project

Now we add the reference database to our CustomApp project. Right-click on References and select Add Database Reference.

Point to the Database Project. Since we will only be pointing to one Vendor Database at a time, we will use the same Database Variable. If you use multiple reference databases at the same time, create a variable for each.

Check the Suppress errors caused by unresolved references in the referenced project. We probably do not have their referenced database. I have heard of some issues with this. If you get errors in a third-party database, you will have to comment out that code. Since you do not have access to the database, that code will not run in your system anyway.

Take note of the Example usage box. Copy it for reference in the next step.

SELECT * FROM [$(Vendor)].[Schema1].[Table1]

Step 4: Replace the database name with a variable

Now that Visual Studio has a link to the reference database, we must point our source code to the referenced database. To do that, we will change all the database names in the object name to use a variable. You will enter the variable when you publish (deploy) your project to SQL Server.

To perform the global change, under Edit, Find and Replace, select Replace in Files.

Uncheck Keep modified files open after Replace All. You do not want to open a window for every change. You can spot-check objects after the update.

Now our View source looks like this:

CREATE VIEW dbo.VendorVersion
AS
SELECT VersionNumber
FROM [$(Vendor)].dbo.AppVersion;

Now when we Build, we are successful.

Step 5: Deploy to Production

When you publish, you will have to enter the Target Database Connection and database name for the Vendor variable.

On your project, right-click and select Publish.

Enter the connection information and the database to be referenced when publishing.

After the information is entered, click Publish.

Publish is successful.

If you scripted the view, it would be the same.

CREATE VIEW [dbo].[VendorVersion]
AS
SELECT VersionNumber
FROM VendorProd.dbo.AppVersion;

Step 6: Deploy to QA

We want to deploy to our UAT database and point to the VendorVNext database. Use the same process of pointing to our target database and change the value in the reference variable.

Publish was successful.

Check the view in UAT.

Conclusion

Now you have control of your database code and can easily deploy it to multiple environments. You can place the project in source control and manage the development process in sync with other applications.