August 31, 2009 at 9:13 am
Hello all,
I have a database that is currently being used by one customer but have several others coming on board in the next few months. The database is definitely going to grow quick. There is a small window where new data for each customer will be processed and updated but, for the most part, data will be queried by each company.
Right now all tables are company independent and records are identified by a CompanyID column. We have identified several tables that could be separated by company if need be and several tables that we want to stay company independent (like company settings, etc.). We want to go ahead a make a change before we get too many companies in the database.
I have looked at schema separation and partitioning both as options. I have looked at partitioning but it looks like I am going to have to add the CompanyID column to several tables and modify a lot of primary and foreign keys as well as change plenty of stored procedures. As far as schema partitioning goes, it looks like I will have some foreign key problems with the generic tables pointing to two different tables along with the increase in the number of tables.
What are the pros and cons of each? Any help would be greatly appreciated.
August 31, 2009 at 12:06 pm
Anyone......at all?
August 31, 2009 at 1:10 pm
You haven't really given us enough information to make much of a distinction for you. I blogged about Shared Schema, Multi-tenancy DB and application desings and have a couple of articles at my blog about multi-tenant applications and DB design here and here.
Hopefully these will be useful to you, the one has a lot of good links also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2009 at 2:03 pm
Thanks fore the links Barry. I believe I am leaning towards the shared database shared schema approach and partitioning based on CompanyID. When you use partitioning, do you recommend using foreign keys? I found several sites that recommended against using them. I find myself having to add the CompanyID to tables that don't need to be partitioned just to enforce the primary key. I can provide the schema if it will help you any.
August 31, 2009 at 2:39 pm
Actually, I am pretty unclear on why you are partitioning at all for this.
Multi-tenancy is an application security-mapping architectural issue, whereas Partitioning is primarily a performance tool. I'm not getting the connection between these two issues?
As for what tables to put the CustomerID on: the rule for shared-schema design is, if it isn't global (universal) information, then it must have a TenantID (i.e., your CustomerID). You don't *ever* deviate from that in shared-schemas as it leads to many nightmares.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2009 at 2:45 pm
I am looking at multi-tenant because I am going to have multiple companies accessing the database and only their information along with the global tables. I am interested in partitioning because several of the company tables are growing at a large rate and when we start adding additional customers it will grow very large very quickly.
September 8, 2009 at 4:41 pm
tgarland (8/31/2009)
I am looking at multi-tenant because I am going to have multiple companies accessing the database and only their information along with the global tables.
Just out of curiosity, what's your backup/recovery strategy? How do you handle the scenario of one company having to restore lets say... point in time to this morning at 4:22AM?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 8, 2009 at 5:46 pm
RBarryYoung (8/31/2009)
...whereas Partitioning is primarily a performance tool.
Just as a quick aside...partitioning really isn't a performance thing. In the great majority of real-world cases, a partitioned table is less performant (more true in 2005 than 2008, but still true). It's more of an admin/management thing. And a great way to lock a database to Enterprise edition servers. 🙂
September 9, 2009 at 7:07 am
PaulB,
At the moment, we only have one customer so we don't have to worry about restoring for an individual company but as we start adding companies in the next few months that will be an issue. If I go with partitioning, I can separate each company on it's own filegroup and perform filegroup backups. Luckily, the way our database it setup and the application front end works, we can easily delete that company's data and reprocess it up to a point in time if need be.
September 9, 2009 at 7:24 am
If you do go the file group backup route, be sure you fully understand the restore options and the need to start and restore with the PRIMARY file group. It is worth considering storing no user objects on PRIMARY - create a different file group and set that as DEFAULT.
Are you expecting to be able to restore per-company to a point in time in place from the file group backups?
And are you expecting that to work with partitioned tables where different partitions are on different file groups?
Just wondering.
Paul
September 9, 2009 at 7:24 am
tgarland (9/9/2009)
If I go with partitioning, I can separate each company on it's own filegroup
IMHO paritioning is not the right tool to solve a multi tenant environment.
Two issues with the separate partitions/filegroups per company.
1) Administrative nightmare, especially in regars to space allocation per company.
2) It does not scale well... if my memory still works the way it used to work you cannot go over 256 partitions on a table while you can have as many as 32K databases in an instance.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 9, 2009 at 7:28 am
You can can have up to 1000 partitions. I do not know what the limit is on filegroups per table. I did not start off planning on creating a separate filegroup per company. I was just thinking that as part of the backup and recovery strategy. Backup and recovery in our case is second to manageability and performance since we can easily recreate a customer's data fairly quickly. Not to say that we don't need good backups.
September 9, 2009 at 7:44 am
tgarland (9/9/2009)
Backup and recovery in our case is second to manageability and performance since we can easily recreate a customer's data fairly quickly.
Well... if recovery would be based in re-creating customer's data I see no reason not to go with a private schema per customer, just put customer specific tables there.
Partitioning is designed to help either Archive & Purge or Quering processes on a large table. This does not appears to be the case since I understand the issue to solve is how to store customer specific data that would always be used in a per customer basis, never as a whole.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 9, 2009 at 7:50 am
PaulB,
Unless I have a misunderstanding about partitioning, I thought it fit very well into what we are trying to accomplish. The main thing we are trying to do is improve query processing on very large tables that will only be accessed on a per company basis even though multiple companies are stored in a single table.
September 9, 2009 at 8:05 am
tgarland (9/9/2009)
The main thing we are trying to do is improve query processing on very large tables that will only be accessed on a per company basis even though multiple companies are stored in a single table.
It all depends on the type of access.
If access is based on indexes allowing for single read or index range scan inside the customer's specific domain you will find there is no performance difference in between a partitioned and an un-partitioned table.
If access is based on full table scan on specific "customer" you will find partitioning is your way to go.
If access is based in anything forcing to scan multiple partitions you may find partitioned table perform worst than a non-partitioned one.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply