Schema Binding Multi-Tenant SQL Environment For Azure

Initializing Tenant and db:

One of the option that can be used in multi-tenancy system is to use schema binding to each tenant. 

 First we need insert login credentials into master db:

CREATE LOGIN [tenant1Login] WITH PASSWORD=N'Pass2006'

go

After that, we need to create our main db:

CREATE DATABASE mydbname COLLATE Latin1_GENERAL_CI_AS(   MAXSIZE = 10 GB , EDITION='business')

Now we can start to create schema and bind them to  login users:

CREATE SCHEMA [tenant1] AUTHORIZATION [dbo]
CREATE USER tenant1User FROM LOGIN [tenant1Login] WITH DEFAULT_SCHEMA=[tenant1]
GRANT SELECT ON SCHEMA :: tenant1 TO tenant1User;
GRANT INSERT ON SCHEMA :: tenant1 TO tenant1User;
GRANT UPDATE ON SCHEMA :: tenant1 TO tenant1User;
GRANT DELETE ON SCHEMA :: tenant1 TO tenant1User;
GRANT EXECUTE ON SCHEMA :: tenant1 TO tenant1User;

At this stage, you can try to connect with different users and you will only see tables,views etc. that is belond to login user's schema.


Note for accessing db with tenant users: In options of connection, instead of default db, you must choose a db different than the master db because only admin user has access to master db.

Primary Key Rule:

Every table must have a primary key. Once the table is created with a primary key, you cannot drop primary key. You must recreate the table.[1]

For foreign keys, you can drop and recreate it with the following scripts:

ALTER TABLE [tenant1].[Dim_Cost_Center] DROP CONSTRAINT FK_Dim_Cost_Center_Dim_Cost_Center

ALTER TABLE [tenant1].[Dim_Cost_Center] WITH CHECK ADD CONSTRAINT [FK_Dim_Cost_Center_Dim_Cost_Center] FOREIGN KEY([id_parent_cost_center])

REFERENCES [tenant1].[Dim_Cost_Center] ([id_cost_center])

Generating Scripts:

Some Table properties cannot be used with Azure environment. The example script below is a valid create table script:

CREATE TABLE [tenant1].[k_tree](

[id_tree] [int] NOT NULL,

[name_tree] [nvarchar](255) NULL,

[id_owner] [int] NULL,

CONSTRAINT [PK_k_tree] PRIMARY KEY CLUSTERED

(

[id_tree] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)

)

The following properties are not supported in Azure:

• ALLOW_ROW_LOCKS,
• ALLOW_PAGE_LOCKS
• PAD_INDEX
• FILLFACTOR

You can generate script for Azure environment by selecting Azure as shown in the picture below:

Resources:

[1]http://stackoverflow.com/questions/5645145/how-can-i-change-primary-key-on-sql-azure