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

How to deploy CLR Stored Procedure with external access

CLR Stored Procedures can be deployed within the Visual Studio environment. However CLR-SP has no access to external sources by default. You must give exclusive permission to your CLR-SP in your database. I did not manage to deploy SP in the Visual Studio.Therefore i choose to deploy my sp within the SQL Server after building it with Visual Studio.The following steps explain how to configure SQL Server for external access:

1)Enable CLR:

SP CONFIGURE 'clr_enabled', 1

GO

RECONFIGURE

GO

OR

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

2)Set database trushworthy:

ALTER DATABASE master SET trustworthy ON

3)Authorization:

If database is authorized by another user you should run this statement. Dont write your db name or user name with ' '

ALTER AUTHORIZATION ON DATABASE: 'db name' TO 'user name'

OR

ALTER AUTHORIZATION ON DATABASE: 'db name' TO 'DOMAIN/user name'

In some cases you may wonder who is the owner of a database. You can use the following function to learn SID and name:

SELECT SD.[SID]

,SL.Name as [LoginName]

FROM master..sysdatabases SD inner join master..syslogins SL

on SD.SID = SL.SID

Where SD.Name = 'db name'

4)Create Assembly from dll file produced in .NET:

CREATE ASSEMBLY 'file name'

from 'dll path'

WITH PERMISSION_SET = EXTERNAL_ACCESS

5)Create Stored Procedure using the assembly file above.

CREATE PROCEDURE 'procedure name'

@var1 NVARCHAR(128)

AS

EXTERNAL NAME 'assembly name'.'project namespace'.'function name'

Web services may require more than one assembly reference. For instance XML.Serialization is one of the reference that is automatically added to the project when a web service is used. Therefore you may need to consider additional assemblies. Since i do not want to deal with such references, i used httprequest object to consume web service.

Sources:

http://kevine323.blogspot.com/2011/03/database-owner-sid-recorded-in-master.html

http://www.simple-talk.com/sql/learn-sql-server/building-my-first-sql-server-2005-clr/