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 topologyI 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
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 topologyAnother 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.
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 ObjectsAnother 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.
- 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.