Run Multi tenants applications on SQL Azure Elastic Scale for non .NET Applications
Microsoft had announced a new model of Microsoft SQL Azure enable developers to build robust enterprise applications in the cloud as well as SaaS applications. You can find more details about elastic scale from this link
Unfortunately, there is currently no out-of-the-box libraries support for non .NET applications, in addition federation application (Web plans) are going to be retired at September 2015, therefore any multi tenant applications written in other languages such as PHP will not be able to use the new elastic scale directly.
we write this blog to introduce solution can overcomes SaaS migration to new SqlAzure elastic scale without using .NET libraries.
first of all you can follow the below video tutorial from Microsoft of how to migrate from federation to elastic scale
Technically, we will discuss how to create Shards and Shard manager deeply for non .NET application, however now we are blogging regarding that you already have an existing shard and shard manager. This blog tell you how to get and operate the data for specified tenant in multi tenant applications.
Row Level Security to filter SELECT, UPDATE, and DELETE queries
Create a row security filter allow to filter queries in application to fit specific tenant, note that the Row Level Security is allowed only on SQL SERVER V12 over the windows azure so be sure your server is updated.
To create the level security you need to create the filter and the schema the below code enable row level security
[box type=”shadow” ]CREATE SCHEMA rls — separate schema to organize RLS objects GO
CREATE FUNCTION rls.fn_tenantAccessPredicate(@TenantId int) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_accessResult
WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID(‘dbo’) — the user in your application’s connection string (dbo is only for demo purposes!)
AND CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = @TenantId — @TenantId (int) is 4 bytes GO [/box]
Now we have enable the security level, we need to filter the tables we would like to apply the row level on it as the following example:
[box type=”shadow” ]
CREATE SECURITY POLICY rls.tenantAccessPolicy
ADD FILTER PREDICATE rls.fn_tenantAccessPredicate(TenantId) ON dbo.Blogs
ADD FILTER PREDICATE rls.fn_tenantAccessPredicate(TenantId) ON dbo.Posts GO [/box]
Further we need to set the Default value for the Tenant ID in order to be prompted in the query executions, the default value will be
[box type=”shadow” ]CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO()))[/box]
Route tenant to the specific shard
after define the row level security in tables in order to filter the data according to a specific tenant, we want to route the tenant to the specific shard if we have multi shards contain tenants.
if your multi tenant application depend on ranges e.g shard1 tenant range from 0-200 and shard2 range from 200-400 and so on you need to define the below function in your shard manager to get the shard for the requested tenant
[box type=”shadow” ]
[highlight]
CREATE FUNCTION [dbo].[getTenantConnectionInfoByTID] (@TenantId int) RETURNS TABLE AS RETURN
( SELECT sg.[DatabaseName] ,smsg.[Name] AS ‘shardMapManagerName’
FROM [__ShardManagement].[ShardMappingsGlobal] smg, [__ShardManagement].[ShardsGlobal] sg, [__ShardManagement].[ShardMapsGlobal] smsg
WHERE smg.[ShardMapId] = smsg.[ShardMapId] AND
smg.[ShardMapId] = sg.[ShardMapId] AND
sg.[ShardId] = smg.[ShardId] AND
@TenantId >= (CONVERT(int, CONVERT(VARBINARY(4), [MinValue])) +2147483648) AND
@TenantId < (CONVERT(int, CONVERT(VARBINARY(4), [MaxValue])) +2147483648)
)
[/highlight]
[/box]
In the other hand if your application register tenants regardless their ranges you can change the function to the following
[box type=”shadow” ]
[highlight]
CREATE FUNCTION [dbo].[getTenantConnectionInfoByTID] (@TenantId int) RETURNS TABLE AS RETURN
( SELECT sg.[DatabaseName] ,smsg.[Name] AS ‘shardMapManagerName’
FROM [__ShardManagement].[ShardMappingsGlobal] smg, [__ShardManagement].[ShardsGlobal] sg, [__ShardManagement].[ShardMapsGlobal] smsg
WHERE smg.[ShardMapId] = smsg.[ShardMapId] AND
smg.[ShardMapId] = sg.[ShardMapId] AND
sg.[ShardId] = smg.[ShardId] AND
@TenantId = (CONVERT(int, CONVERT(VARBINARY(4), [MinValue])) +2147483648) )
[/highlight]
[/box]
Create a stored procedure that the middle-tier application will call after connecting to the database to set CONTEXT_INFO to the tenantID filter.
[box type=”shadow” ]CREATE PROCEDURE sp_setContextInfoAsAppUserId(@TenantId int) AS SET CONTEXT_INFO @TenantId; GO[/box]
[divider]
Now your done, all you have to filter your application data according to a specific tenant.
put it all together first connect to the shard manager database and get the shard database name using the previous function getTenantConnectionInfoByTID(your tenant id here), second connect to the shard database and filter the data according to the tenant by calling executing the procedure
[box type=”shadow” ];’EXEC sp_setContextInfoAsAppUserId ‘your tenant id here[/box]
your app is now filtered for a the requested tenant, therefore you can select, insert , delete , update on the filtered tenant.
Go on !