Database Projects Topologies

I have been working with Visual Studio Database projects starting with Database Professional to its successor SQL Server Data Tools. Within this post I wish to share some of the project topologies that I have used to manage my project databases requirements

Before starting I would like to cover the 2 ways that database projects can be referenced.  As these methods play an important part of the projects topologies without having to develop code.

• External Referencing
  When adding a database reference and setting the options of database name or, optional, server name with a literal / variable value will configure the reference as external.  This will mean any SQL objects within the reference will need to be referred by using 3/4 part naming.  Also the referenced DACPAC / project will need be to deployed first, separately, before deploying the current project.

• Composite Referencing
  Creating a database reference but not setting the database name / server name will configure as internal, composite, to the database.  Allowing the use of two part naming.  Another benefit is that the project containing the reference can deployed the reference objects within the same deployment.

The reason behind how I came to create theses topologies were due to the following reasons:

• Deployments problems.
• Reduce repeating code
• Ensuring consistent model
• Couldn’t spend time creating, managing and installing extensions for the tools.

Tables and Code splitting topology

I was working on a database warehouse project, which was using DB Pro, was having deployment problems.  The deployments were failing due to circular references as database required objects be created in each of the other databases.  To get a deployment to work I had re-create the deployment script a few times.

To deploy all the projects compiled models in a single execution I restructured the projects in the following manner:

• storage project: which contain all the, table, indexes and files DDL
• code project: this project had all the views, functions and store procedures DDL

The code project would use composite referencing to the storage project for the same database. Then external referencing both the code and storage project for other external databases.

There was an added advantage of splitting the projects in this way. It allowed the DBA to check the deployment for table rebuilds.  As he only had to check the output of the storage project.  However there was some extra configuration management caused by this method, this was mitigated within our deployment framework I was using at the time.

Showing the Table and Code Referencing Topology

Publication, Subscriber and Publisher topology

Another project I worked on made use of SQL Server replication to replicate a subset of objects. To ensure that the objects being replicated where consistent between the databases I use the following project topology:

• A publication project: Would contain all the objects which would need to exist on both publisher and subscribers.
• A publisher project: Which contain objects that would only exists on the publisher database.
• A subscriber project: This project contain objects only for the subscribing database.

The publication and subscriber project used the composite project reference to this publication project.
This helped ensure that the publisher and subscriber database had a consistent model for the replicated objects.  Also any changes only needed to be made to one project.

Showing the Publication, Subscriber and Publisher Topology

Packaging Standard Objects

Another topology I've used within my database projects is to separate any standard/self-contained code into a separate project. Then apply composite reference if required.

Here are some examples:

• An example of self-contained code would be the tsqlt framework. To find out more please read the following link: http://zogamorph.blogspot.com/2014/07/integrating-tsqlt-with-sql-server-data.html
• One of my previous workplaces had a standard set of objects and code which were apply to any replicated databases.
• My current workplace has a standard security schema which is applied to all its databases.

Here are the following reasons why I placed the standard code into a separate project:

• The standard code is maintained in one place.
• I have a deploying mechanism for the standards
• Made it easier database projects to get the standards applied by referencing the projects.