Developing Similar Structured Multi-Customer Databases with SQL Server Data Tools (SSDT)

By: Haroon Ashraf   |   Comments (3)   |   Related: More > Database Administration

Problem

You just started as a SQL Server database developer for a company which provides digital services to their business customers who in turn serve their clients using this system.

You have been asked to develop customer SQL Server databases that have the same structure for each customer due to the following reasons:

1. The database requirements for all customers are the same
2. The data is regularly required by the business customers to feed into their systems
3. Later on if required, the databases can be divided between different servers
4. Database maintenance should be carried out by taking one customer offline at a time
5. Each customer may come up with different reporting needs (provided the main transactional database remains the same)

Solution

The solution is to develop multi-customer databases using SQL Server Data Tools (SSDT) that can help your team manage all the database(s) through a single database project which supports both connected and disconnected development and publishing.

We will go through some benefits of SQL Server Data Tools (SSDT) followed by development steps in order to understand how to implement this solution in the best possible way using declarative database development.

Why SQL Server Data Tools (SSDT)

One of the most important things you may be asked by your development team or managers is why have you chosen SQL Server Data Tools (SSDT) rather than SQL Server Management Studio (SSMS).

Declarative Database Development

SSDT fully supports declarative database development where you specify what you want rather than how you want, so you define database object(s) such as a table, stored procedure, view, etc. and deploy them.

When the time comes to alter objects you do not need to write alter scripts rather update the objects straight away and deployment (to debug database in development) takes care of altering the objects according to the most recent definition.

Static Code Analysis

This means your database development work is fully backed by an internal code analysis, so you can see the errors in your script even without the need of deploying the changes or even executing the database objects such as stored procedures.

Intellisense Support

It is quicker to write database objects in SSDT because of Intellisense support which helps you complete statements and as a result objects are created and managed efficiently.

SSOX (SQL Server Object Explorer)

The fact that you stay within Visual Studio while working on an SSDT Database Project is further supported by SQL Server Object Explorer (SSOX) which is an alternative version of SQL Server Management Studio (SSMS) with development focused features.

SQL Server Object Explorer provides you all the necessary support to see your deployment databases in action along with connected database development features that let you create, modify, view and drop objects on the fly.

Refactoring Support

Database refactoring through SSDT Database Project is simple, easy and straight forward. You can rename objects quickly in an SSDT Project.

Multiple Deployment Flavors (Options)

SSDT Projects support a number of deployment options ranging from single-click publishing to DACPAC delivery to a DBA.

Data and Schema Comparisons

SSDT provides an easy to use data and schema comparison feature which is very handy to do the following things:

1. Synching Dev, Test and Production (environments) to follow development best practices
2. Detecting database drifts (mostly in the form of hot-fixes) on Production before deploying new changes to the Production database
3. Comparing data between two databases to see if all reference tables are in sync between Test and Production

Database Version Control

Databases can be version controlled which not only makes development, testing and deployment comfortable, but also helps to revert changes or versions quickly in case the need to go back to the previous version comes into action.

Similar Structured Multi-Customer Databases Development

Now that we are familiar with the benefits of using SQL Server Data Tools (SSDT) for similar structured database development let us begin our journey to meet the requirements mentioned in the problem with the simplest approach.

Database Development Strategy (simplified version)

Please note that in order to focus purely on development strategy we are not mentioning the unit-testing strategy of this solution. To get a good understanding of the unit-testing strategy please refer to this previous tip.

The database development strategy is as follows:

1. Use a single database project to develop database structure
2. Use a single debug database to see changes getting deployed to a sandbox environment (local dev machine)
3. Use the connected publishing mode to publish the database project to multiple databases having a similar structure

This can be illustrated as follows:

Keeping in mind the main requirements mentioned in the problem statement, we are looking to group all the databases into a single database project since the database objects and structures across all databases are the same with the exception of the data.

Why Not Test-Driven Development (TDD)

To implement such a solution, test-driven database development (TDD), where unit-tests initiate the development to ensure required objects exist to meet requirements, is highly recommended provided things remain within scope.

We assume the readers of the tip have gone through this previous tip about test-driven development (TDD) and are capable of converting the upcoming simple example into TDD since the main focus is on developing multi-customer databases with SQL Server Data Tools (SSDT).

Business Requirements

Let us take an example of simple requirements to understand the process with ease.

We have just received our first business requirements which states the following:

1. All our customers have their own clients
2. All their clients can be added or removed by the customers
3. All our customers offer services to their clients
4. All our customer can add or remove their services
5. All our customers’ services can be purchased by the clients

Create SQL Database Project

Let us begin our work by creating a blank database project called “MultiCustomerDatabases” under a solution.

Create Client Table

Considering all our customers have their own clients we need a client table in each of the databases.

Add a new folder “Tables” to the project and create a new table “Client” with the following code:

CREATE TABLE [dbo].[Client]
(
   [ClientId] INT NOT NULL IDENTITY(1,1) , 
   [Company] VARCHAR(40) NOT NULL,
   [Email] VARCHAR(320) NOT NULL,
   [Phone] VARCHAR(50) NULL,
   [RegistrationDate] DATETIME2,
   [Status] bit, 
   CONSTRAINT [PK_Client] PRIMARY KEY ([ClientId])
) 

Create Add and Remove Client Stored Procedures

As all our customers should be able to add and remove their clients, we require an add and remove client stored procedures.

Add a new folder “Programmability” in the project and create a sub-folder “Stored Procedures” in that folder.

Then add a new stored procedure as follows:

CREATE PROCEDURE [dbo].[AddClient]
   @Company VARCHAR(40) ,
   @Email VARCHAR(320) ,
   @Phone VARCHAR(50) ,
   @RegistrationDate DATETIME2,
   @Status bit -- 1 active, 0 closed
AS

SET NOCOUNT ON

INSERT INTO dbo.Client 
(Company,Email,Phone,RegistrationDate,Status)
VALUES
(@Company,@Email,@Phone,@RegistrationDate,@Status)

RETURN 0 

Next add another stored procedure to remove the client using the following code:

CREATE PROCEDURE [dbo].[RemoveClient]
   @ClientId INT
AS

DELETE FROM dbo.Client WHERE ClientId=@ClientId

RETURN 0

The project now looks like the following:

Create Service Table

Next add a Service table as follows:

CREATE TABLE [dbo].[Service]
(
   [ServiceId] INT NOT NULL,
   [Name] VARCHAR(40),
   [Detail] VARCHAR(1000),
   [Pricing] DECIMAL(10,2), 
   CONSTRAINT [PK_Service] PRIMARY KEY ([ServiceId])
)

Create Add and Remove Services Stored Procedures

We also need to add stored procedures to add and remove a service as follows:

CREATE PROCEDURE [dbo].[AddService]
   @Name VARCHAR(40),
   @Detail VARCHAR(1000),
   @Pricing DECIMAL(10,2)
AS

INSERT INTO Service
(Name,Detail,Pricing)
VALUES
(@Name,@Detail,@Pricing)
 
RETURN 0



CREATE PROCEDURE [dbo].[RemoveService]
   @ServiceId INT
AS

DELETE FROM Service WHERE 
ServiceId=@ServiceId

RETURN 0

Create Service Order Table and Purchase Service Stored Procedure

Next we have to create a ServiceOrder table as follows:

CREATE TABLE [dbo].[ServiceOrder]
(
   [ServiceOrderId] INT NOT NULL ,
   [ClientId] INT,
   [ServiceId] INT,
   [PurchaseDate] DATETIME2,
   [AmountPaid] DECIMAL (10,2),
   [Validity] DATETIME2, 
   CONSTRAINT [PK_ServiceOrder] PRIMARY KEY ([ServiceOrderId]), 
   CONSTRAINT [FK_ServiceOrder_Client] FOREIGN KEY ([ClientId]) REFERENCES [Client]([ClientId]), 
   CONSTRAINT [FK_ServiceOrder_ToTable] FOREIGN KEY ([ServiceId]) REFERENCES [Service]([ServiceId])
) 

Then create a stored procedure to handle purchasing a service by a client from our customer:

CREATE PROCEDURE [dbo].[PurchaseService]
   @ClientId INT,
   @ServiceId INT,
   @PurchaseDate DATETIME2,
   @AmountPaid DECIMAL (10,2),
   @Validity DATETIME2 
AS

INSERT INTO ServiceOrder
(ClientId,ServiceId,PurchaseDate,AmountPaid,Validity)
VALUES
(@ClientId,@ServiceId,@PurchaseDate,@AmountPaid,@Validity)
 
RETURN 0 

The project now looks as follows:

Debug Database Settings

Let's quickly review our debug database settings.

Target Platform:

Set the Target Platform as desired in our case we have set it to SQL Server 2012.

Debug Database:

Also set the debug database to point to your local SQL instance on dev machine (if you prefer to do so).

Always Recreate Database Deployment Option:

Please set “Always recreate database” deployment option to true under Project Settings > Debug.

Please refer to this previous tip for a detailed walkthrough of how to perform the above steps.

Create Debug Database by Debugging Project

Next Debug the Project by pressing F5 which is going to create the desired database on your local dev SQL instance as configured earlier.

Please note that debug is going to trigger BUILD first before creating debug database.

Now debug the database project to see the results:

Have a look at the debug database:

As a best practice I would strongly recommend to follow test-driven development (TDD) (mentioned in this tip) that means all the unit-tests of the objects have been run successfully before the objects get deployed to the debug database, so our debug database is tested in that sense and ready to be used for further work.

Populate Debug Database (MultiCustomerDatabases)

Let's execute the AddClient stored procedure by supplying values to the parameters.

USE [MultiCustomerDatabases]
GO
 
DECLARE @return_value Int
 
EXEC @return_value = [dbo].[AddClient]
     @Company = N'BestCompany001',
     @Email = N'[email protected]',
     @Phone = N'1234567',
     @RegistrationDate = '20180101',
     @Status = 1
 
SELECT @return_value as 'Return Value'
 
GO

Then check by right clicking the Client table and then clicking on view data:

Using the above method we can populate the rest of the tables and then create data scripts to put it into the database projects to be used as reference data. For details please refer to tip and tip.

Creating Multiple Databases from the Project

Right click on the database project and click on “Publish” to see a publish dialogue as shown below:

Click on Edit to open the database connection settings, then enter your dev machine name and then for the Database Name add your desired database name such as “CustomerDatabase1” to be created from Publishing this project:

Click and save the profile as “MultiCustomerDatabase.dev1.publish.xml” as shown below:

Now copy the dev1 publish profile and paste the file in the same place and rename it as “MultiCustomerDatabases.dev2.publish” and change Database name to CustomerDatabase2 before saving the profile:

Now copy dev1 publish profile and paste the file in the same place and rename it as “MultiCustomerDatabases.dev3.publish” and change Database name to CustomerDatabase3 before saving profile:

Now right click on each Publish Profile to deploy the databases to your dev environment:

Our multi-customer databases are now ready as shown below:

Next Steps

• Please read Tip and Tip to add more features to this project.
• Try creating reference data for the objects with post deployment scripts to populate database(s).
• Try creating the whole project mentioned in the tip using test-driven development (TDD).
• Please learn more about SQL Server Data Tools on MSDN Library.
• Try creating multiple debug databases to see all your work in line with local dev, dev, test and production.
• Try creating multi-customer databases using methods other than Publish Profiles.

About the author

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips