HI There,
i have to make my application SAAS compliant .For achieving multi tenancy , i was thing of partitioning the data , and each partition will be for a tenant. and this partitioning will be done dynamically .
has anybody done something like this ? what do you think the better approach be ?
i am using SQL 2005
Regards DEE
There is a limit of 1000 partitions per partition scheme and you can only partition on a single field, so if you intend to multi-tenant beyond 1000 instances you are going to have to jump through a lot more hoops. You can extend the limit by using a partitioned view on top of multiple partitioned tables, but this increases the management overhead. You can use the DMVs and create your own automated system that generates new partitions per client / tenant and manages the problem but it will be specific to your application and not generic.
At present there is no automatic dynamic partitioning in SQL Server, it was mentioned at the PDC09 in relation to the SQL Azure future roadmap, but I did not hear of it for SQL Server.
Your alternative choices are a database or SQL Instance per client, there are benefits to this approach in that you give yourself far more opportunity to scale out if the needed arises, and if you start looking at a larger data centre, you can start balancing the SQL Instances across a farm of servers etc. If you automatically have all the data in a single database.
Other things to take into consideration:
Security: Whilst you have the data in a single database with partitioning, you have no data protection. You risk exposing one client s data to another very trivially with any single bug in the code.
Upgrading: If all the clients access the same database, then the upgrades will be an all or nothing approach - you will not be able to easily migrate some users to a new version whilst leaving the others as they were.
Backups: You can make each partition occupy a separate file group and try manage the situation. Out of the box so to speak, every client s backups are mingled together. If a single client asks for a rollback to a given data you have to plan carefully in advance how that could be executed without affecting the other users of the system.
I m trying to export some tables from SQL Server 2005 and then create those tables and populate them in Oracle. I have about 10 tables, varying from 4 columns up to 25. I m not using any constraints/...
In our database we have an SQL server account that has the correct roles to access some of the databases. We are now switching to windows authentication and I was wondering if we can create a NT user ...
I have been trying to execute a MS SQL Server stored procedure via JDBC today and have been unsuccessful thus far. The stored procedure has 1 input and 1 output parameter. With every combination I ...
Is it possible to use the sqlcmd command to dump table structure the way it can be done with mysqldump?
Hi I have the following SP, however when I use LINQ to SQL it generates 2 multiple recordsets. For my sanity I am trying to fathom out what it is in the stored procedure that is doing this and would ...
I want to create a table in MS SQL Server 2005 to record details of certain system operations. As you can see from the table design below, every column apart from Details is is non nullable. CREATE ...
I love the new DATE datatype in SQL Server 2008, but when I compare a DATE field to a DATETIME field on a linked server (SQL 2005, in this case), like this: DECLARE @MyDate DATE SET @MyDate = CONVERT(...
How to determine if a Transaction is active i.e. before issuing Begin Transaction I want to ensure that no previous transaction are open.. the platform is VB6, MS-SQL Server 2000 and ADO 2.8
