multi-tenant table administration flexibility without the complications. richard banville fellow,...
TRANSCRIPT
Multi-tenant TableAdministration
Flexibility without the complications.
Richard BanvilleFellow, Progress SoftwareJune 7, 2011
© 2011 Progress Software Corporation. All rights reserved.2
Agenda
Administration architecture
Multi-tenant Administration: “How to”
Utility usage examples
Multi-tenant Configuration Tool demo
© 2011 Progress Software Corporation. All rights reserved.3
Multi-tenant Identity Definitions
Tenant• “Named” group of users
• Share same application and data
Multi-tenancy• Deployment supporting multiple tenants
Default tenant• Users who do not assert tenant identity
Super tenant• Tenant with ability to access/manage data of any tenant
Multi-tenant group• Multiple tenants sharing data for a specific multi-tenant table
© 2011 Progress Software Corporation. All rights reserved.4
Name indexRecord Data
Table instance• Defined by a single table definition (schema)
• Each table instance contains
– A data segment for the record data
– A separate data segment for each index/lob of the table
• Each tenant/group has a different table instance
• A tenant need not instantiate every multi-tenant table
Data segment• Tenant/group specific chain of data for each table/index/lob
• Stored/encapsulated in its own physical data partition
Physical data partition• Managed storage for each object of a multi-tenant table instance
• Do not span storage areas
Multi-tenant Allocation Definitions
Customer
Record Data
Blob Data
Name Index
Cust-num Index
Record Data Name index
© 2011 Progress Software Corporation. All rights reserved.5
Multi-tenant Administration
Tenant Administration• Tenants
• Groups
• Domains
• Users
• Security/Data Access
Performance• Monitoring
• Configuration
Data Administration• Table definition
• Allocation
• Location
• Maintenance
What needs administration?
© 2011 Progress Software Corporation. All rights reserved.6
Multi-tenant Model
Users
Suzi@HD_DomainAllen@HD_Domain Cat@HD_DomainRich@HD_Domain
Rich@Lowes1 John@Lowes1Claudio@Lowes2 Louie@Lowes2
Edward
Domains/Tenants
Name Tenant
HD_Domain HomeDepot
Name Tenant
Lowes1 Lowes
Lowes2 Lowes
Name Tenant
blank Default
Data
Tenancy Layer
HomeDepot
Lowes
Default de-allocated or migrated data
Shared
Customers
Orders
Items …
_file _field _tenant
state ……
Customers
Orders
Items …
123 0 thru 4
© 2011 Progress Software Corporation. All rights reserved.7
Physical Model
Examples
© 2011 Progress Software Corporation. All rights reserved.8
Physical Model By Data and Datatype
Data
Tenancy Layer
HomeDepot
Lowes
Default
de-allocated or migrated data
Shared
Customers
Orders
Items …
_file _field _tenant
state …
Customers
Orders
Items …
…
*Type II Storage Areas
Area7 Area8
CustOrder Data CustOrderIdxs
ItemData ItemIdxs
A11 A12
Shared Data
Shared Indexes
Area9 Area10
© 2011 Progress Software Corporation. All rights reserved.9
Physical Model By Tenant
Data
Tenancy Layer
HomeDepot
Lowes
Default
de-allocated or migrated data
Shared
Customers
Orders
Items …
_file _field _tenant
state …
Customers
Orders
Items …
…
Type II Storage Areas
Area 7HD CustOrder Data
HD CustOrderIdxs
HD ItemData
HD ItemIdxs
Area 8 Lowes CustOrder Data
Lowes CustOrderIdxs
Lowes ItemData
Lowes ItemIdxs
Area 9 Default Data Area
Default Index
A10 A11
Shared Data
Shared Indexes
© 2011 Progress Software Corporation. All rights reserved.10
Physical Model By Tenant By Data And Datatype
Data
Tenancy Layer
HomeDepot
Lowes
Default
de-allocated or migrated data
Shared
Customers
Orders
Items …
_file _field _tenant
state …
Customers
Orders
Items …
…
Type II Storage Areas
A7 A8
A9 A10
HD CustOrder Data
HD CustOrderIdxs
HD ItemData
HD ItemIdxs
A11 A12
A13 A14
Lowes CustOrder Data
Lowes CustOrderIdxs
Lowes ItemData
Lowes ItemIdxs
A15 A16 Default Data Area
A17 A18
Shared Data
Shared Indexes
Default Index Area
© 2011 Progress Software Corporation. All rights reserved.11
Example: Adding A New Tenant “AceHardware”
Data
Tena
ncy
Laye
r
AceHardware
Shared
Customers
Orders
Items …
A23 A24
A25 A26
Ace CustOrder Data
Ace CustOrderIdxs
Ace ItemData
Ace ItemIdxs
• Table instance allocation: delayed, immediate, none
Type II Storage Areas
• Tenant Access: Allowed, disallowed
• Determine physical layout 1st
• Create tenant & assign partition locations next
© 2011 Progress Software Corporation. All rights reserved.12
New Inventory Table With Existing Tenants
Data
Tenancy Layer
HomeDepot
Lowes
Customers
Orders
Items …
Type II Storage Areas
A7 A8
A9 A10
HD CustOrder Data
HD CustOrderIdxs
HD ItemData
HD ItemIdxs
A11 A12
A13 A14
Lowes CustOrder Data
Lowes CustOrderIdxs
Lowes ItemData
Lowes ItemIdxsCustomers
Orders
Items …
• Example: Adding an Inventory table
© 2011 Progress Software Corporation. All rights reserved.13
New Inventory Table With Existing Tenants
Data
Tenancy Layer
HomeDepot
Lowes
Customers
Orders
Items …
Type II Storage Areas
A7 A8
A9 A10
HD CustOrder Data
HD CustOrderIdxs
HD ItemData
HD ItemIdxs
A11 A12
A13 A14
Lowes CustOrder Data
Lowes CustOrderIdxs
Lowes ItemData
Lowes ItemIdxsCustomers
Orders
Items …
Inventory
Inventory
A19 A20HD Inventory Data HD InventoryIdxs
A21 A22Lowes Inventory Data Lowes InvIdxs
• Tenant specific space allocation immediate or delayed
© 2011 Progress Software Corporation. All rights reserved.14
Groups
Data
Tena
ncy
Laye
r
AceHardware
TaxCode State
Customers …
HomeDepot
TaxCode State
Customers …
Tenants have their own table instance if:• Not part of a group
© 2011 Progress Software Corporation. All rights reserved.15
Tax Code Data
State Data
Groups
Data
Tena
ncy
Laye
r
AceHardware
TaxCode State
Customers …
A30Tax Code Indexes
State Indexes
TaxCodeGroupTaxCodes
HomeDepot
TaxCode State
Customers …
StateGroupStates
A31
Tenants have their own table instance if:• Not part of a group
Group data• Shared amongst tenants in the group
• Can exists with no tenants assigned
• Follow allocation rules already defined
Each group has only one table instance• Cannot delete table if it has groups
Can be spread across storage areas.
© 2011 Progress Software Corporation. All rights reserved.16
Tax Code Data
State Data
Groups
Data
Tena
ncy
Laye
r
AceHardware
TaxCodeGroup StateGroup
Customers …
A30Tax Code Indexes
State Indexes
TaxCodeGroupTaxCodes
HomeDepot
TaxCodeGroup StateGroup
Customers …
StateGroupStates
A31
Tenants have their own table instance if:• Not part of a group
Group data• Shared amongst tenants in the group
• Can exists with no tenants assigned
• Follow allocation rules already defined
Each group has only one table instance• Cannot delete table if it has groups
Can be spread across storage areas.
© 2011 Progress Software Corporation. All rights reserved.17
I think I get it,
but what do I do to do it?
© 2011 Progress Software Corporation. All rights reserved.18
Multi-tenant Administration
Administration mechanisms
Database enablement:
dbutil describe: 14 Multi-tenancy Yes
Create storage areas
• No new syntax to database structure files (.st)
• Coordinate with database designers/developers
• Have a plan based on physical model
• Naming conventions can ease your pain
Appearance of database isolation where possible
proutil <db> -C enableMultitenancy
• Data Dictionary• SQL DDL• Browser based config tool
• Command line tools• ABL administrative APIs
dbutil prostrct add[online] <db> <addmt>.st
© 2011 Progress Software Corporation. All rights reserved.19
Identify Multi-tenant Tables
Users
Suzi@HD_DomainAllen@HD_Domain Cat@HD_Domain Rich@HD_Domain
Rich@Lowes1 John@Lowes1Claudio@Lowes2 Louie@Lowes2
Edward
Domains/Tenants
Name Tenant
HD_Domain HomeDepot
Name Tenant
Lowes1 Lowes
Lowes2 Lowes
Name Tenant
blank Default
Data
Tenancy Layer
HomeDepot
Lowes
Default de-allocated or migrated data
Shared
Customers
Orders
Items …
_file _field _tenant
state ……
Customers
Orders
Items …
123 0 thru 4
© 2011 Progress Software Corporation. All rights reserved.20
Object Creation Considerations w/out Tenants
Convert existing table to multi-tenant table• All objects of table instance MUST reside in TII storage area
• One way conversion
• Default partition contains existing data
Create new multi-tenant tables• No need for default partition (optional)
• New multi-tenant “default index” goes in same area as table
Adding new indexes with new tables• Activation state at index level, NOT tenant level
– Can be changed after the fact
Schema manipulation tools such as data dictionary, SQL DDL, etc
© 2011 Progress Software Corporation. All rights reserved.21
Sequence Creation Considerations
Sequences• Shared or tenant specific
• Definition database wide (max/min/cycle values)
• Current value tenant specific
• Not group specific
– Avoid multi-tenant sequences for group data
– Group data is shared amongst specific tenants
© 2011 Progress Software Corporation. All rights reserved.22
Tenant Creation
Users
Suzi@HD_DomainAllen@HD_Domain Cat@HD_Domain Rich@HD_Domain
Rich@Lowes1 John@Lowes1Claudio@Lowes2 Louie@Lowes2
Edward
Domains/Tenants
Name Tenant
HD_Domain HomeDepot
Name Tenant
Lowes1 Lowes
Lowes2 Lowes
Name Tenant
blank Default
Data
Tenancy Layer
HomeDepot
Lowes
Default de-allocated or migrated data
Shared
Customers
Orders
Items …
_file _field _tenant
state ……
Customers
Orders
Items …
123 0 thru 4
© 2011 Progress Software Corporation. All rights reserved.23
Tenant Creation Using ABL Multi-tenant APIs
using OpenEdge.DataAdmin.*. define variable service as DataAdminService no-undo. service = new DataAdminService("demo").
define variable tenant as ITenant no-undo.define variable partition as IPartition no-undo.
tenant = new Tenant("HomeDepot").
assign tenant:Type = "Regular" /* or super */ tenant:IsOnline = yes tenant:Description = "Describe HomeDepot" tenant:DefaultDataArea = service:GetArea("Data Area") tenant:DefaultIndexArea = service:GetArea("Index Area") tenant:DefaultLobArea = service:GetArea("Lob Area") tenant:DefaultAllocation = "Delayed". /* Immediate or None */
/* Create the tenant */ service:CreateTenant(tenant).
… /* continued on next page */
Code?
Why are you showing
me code?
© 2011 Progress Software Corporation. All rights reserved.24
Tenant Creation Using ABL Multi-tenant APIs
using OpenEdge.DataAdmin.*. define variable service as DataAdminService no-undo. service = new DataAdminService("demo").
define variable tenant as ITenant no-undo.define variable partition as IPartition no-undo.
tenant = new Tenant("HomeDepot").
assign tenant:Type = "Regular" /* or super */ tenant:IsOnline = yes tenant:Description = "Describe HomeDepot" tenant:DefaultDataArea = service:GetArea("Data Area") tenant:DefaultIndexArea = service:GetArea("Index Area") tenant:DefaultLobArea = service:GetArea("Lob Area") tenant:DefaultAllocation = "Delayed". /* Immediate or None */
/* Create the tenant */ service:CreateTenant(tenant).
… /* continued on next page */
Set AreaDefaults
ReferenceInterfaces,Services
© 2011 Progress Software Corporation. All rights reserved.25
Partition Assignment
… /* continued from previous page */
assign partition = tenant:Partitions:Get(service:GetTable("Customer")) partition:Area = service:GetArea("HD CustomerArea“)
partition = tenant:Partitions:Get(service:GetTable("Order")) partition:Area = service:GetArea("HD OrderArea")
partition = tenant:Partitions:Get(service:GetTable("Item")) partition:Area = service:GetArea("HD ItemArea") partition:AllocationState = “None”.
/* Allocate Space */tenant:Allocate(). /* indicate allocation of everything in delayed state */
Service:UpdateTenant(tenant).
delete object service.
Allocate
Reassign partition location information prior to allocation.Allocation State: Delayed or None
© 2011 Progress Software Corporation. All rights reserved.26
Partition Location Re-assignment
Reassign partition location after allocation • Table/index move OR Dump, de-allocate, reassign via APIs/tools, reload
• Using table/index move utilities
• New de-allocate utility (bye-bye data)
• Binary Dump: data is tenant indifferent
– Dump file: <table>_<ID>{T|G}_<tenant | group>.bd[n]
– Example: customer_2T_HomeDepot.bd1
• Utilities also support “group” based operations
proutil <db> -C deallocate <table> tenant <tenant>
proutil <db> -C tablemove <table> <area> tenant <tenant>
proutil <db> -C dump <table> tenant <tenant>
© 2011 Progress Software Corporation. All rights reserved.27
Using Domains
Users
Suzi@HD_DomainAllen@HD_Domain Cat@HD_Domain Rich@HD_Domain
Rich@Lowes1 John@Lowes1Claudio@Lowes2 Louie@Lowes2
Edward
Domains/Tenants
Name Tenant
HD_Domain HomeDepot
Name Tenant
Lowes1 Lowes
Lowes2 Lowes
Name Tenant
blank Default
Data
Tenancy Layer
HomeDepot
Lowes
Default de-allocated or migrated data
Shared
Customers
Orders
Items …
_file _field _tenant
state ……
Customers
Orders
Items …
123 0 thru 4
© 2011 Progress Software Corporation. All rights reserved.28
Domain Creation
Domain Tenant: Domains ALWAYS associated w/tenants• (default, regular named tenant, super tenant)
Renaming a tenant “cascades” the rename to the domains
Renaming a domain “cascades” the rename to the users
NOTE: Multiple domains can map to the same tenant
define variable domain as IDomain no-undo.define variable cAccessCode as character init “secret-code" no-undo.
domain = new Domain("HD_Domain")
assign domain:DomainType = service:GetDomainType("_oeusertable") domain:AccessCode = cAccessCode domain:Tenant = service:GetTenant("HomeDepot") domain:IsEnabled = yes domain:Description = "HomeDepot security domain".
service:CreateDomain(domain).
© 2011 Progress Software Corporation. All rights reserved.29
Users
Suzi@HD_DomainAllen@HD_Domain Cat@HD_Domain Rich@HD_Domain
Rich@Lowes1 John@Lowes1Claudio@Lowes2 Louie@Lowes2
Edward
Adding Users
Domains/Tenants
Name Tenant
HD_Domain HomeDepot
Name Tenant
Lowes1 Lowes
Lowes2 Lowes
Name Tenant
blank Default
Data
Tenancy Layer
HomeDepot
Lowes
Default de-allocated or migrated data
Shared
Customers
Orders
Items …
_file _field _tenant
state ……
Customers
Orders
Items …
123 0 thru 4
© 2011 Progress Software Corporation. All rights reserved.30
User Creation
Users Domain Tenant (Adding users is optional)• Setting user establishes tenant identity determining data access
define variable domain as IDomain no-undo.define variable auser as IUser no-undo.
auser = new User(“fblake").
assign auser:GivenName = “Frank“ auser:SurName = “Blake" auser:Password = “HD_CEO".
domain = service:GetDomain("HD_Domain").domain:Users:Add(auser).
service:UpdateDomain(domain).
User Id:Password:
fblakeHD_CEO
Userid/Password is incorrect.
Nothingnew here
© 2011 Progress Software Corporation. All rights reserved.31
User Creation
Users Domain Tenant (Adding users is optional)• Setting user establishes tenant identity determining data access
NOTE: “Can” permissions extended to tenant qualified userid
define variable domain as IDomain no-undo.define variable auser as IUser no-undo.
auser = new User(“fblake").
assign auser:GivenName = “Frank“ auser:SurName = “Blake" auser:Password = “HD_CEO".
domain = service:GetDomain("HD_Domain").domain:Users:Add(auser).
service:UpdateDomain(domain).
User Id:Password:
fblake@HD_DomainHD_CEO
Nothingnew here
© 2011 Progress Software Corporation. All rights reserved.32
Group Creation
Data
Tena
ncy
Laye
r
HomeDepot_NH1
Customers …
CreditCheckGroupCredit
HomeDepot_NH2
Customers …
Credit Data
A300
Credit Indexes
A301
HD #2 Data Area
A200
HD #2 index Area
A201
HD #1 Data Area
A100
HD #1 index Area
A101
Type II Storage Areas
© 2011 Progress Software Corporation. All rights reserved.33
Group Creation
Data
Tena
ncy
Laye
r
HomeDepot_NH1
CreditCheckGroup
Customers …
CreditCheckGroupCredit
HomeDepot_NH2
CreditCheckGroup
Customers …
Credit Data
A300
Credit Indexes
A301
HD #2 Data Area
A200
HD #2 index Area
A201
HD #1 Data Area
A100
HD #1 index Area
A101
Type II Storage Areas
© 2011 Progress Software Corporation. All rights reserved.34
Group Creation
Data
Tena
ncy
Laye
r
HomeDepot_NH1
CreditCheckGroup HD_ItemsGroup
Customers …
CreditCheckGroupCredit
HomeDepot_NH2
CreditCheckGroup HD_ItemsGroup
Customers …
HD_ItemsGroupItems
Credit Data
HD Item DataA300
Credit Indexes
HD Item IndexesA301
HD #2 Data Area
A200
HD #2 index Area
A201
HD #1 Data Area
A100
HD #1 index Area
A101
Type II Storage Areas
• Multiple Home Depot tenants sharing same item list, same credit check data.
© 2011 Progress Software Corporation. All rights reserved.35
Group Creation
Create the group
define variable partitiongroup as IPartitionGroup no-undo.
partitiongroup = new PartitionGroup("HD_ItemsGroup“).
assign partitiongroup:Table = service:GetTable("Item") partitiongroup:DefaultDataArea = service:GetArea(“A300") partitiongroup:DefaultIndexArea = service:GetArea(“A301") partitiongroup:DefaultLobArea = service:GetArea(“A300") partitiongroup:DefaultAllocation = "Immediate“ /* or delayed */ partitiongroup:Description = "Home Depot Items".
service:CreatePartitionGroup(partitiongroup).
Set AreaDefaults
© 2011 Progress Software Corporation. All rights reserved.36
Adding Tenants To Groups
Table allocation state: None• Simply add tenant as member of the group for that table
NOTE: Tenant allocation state must be “None” to join a group
define variable partitiongroup as IPartitionGroup no-undo.
assign partitiongroup = service:GetPartitionGroup("HD_ItemGroup").
partitiongroup:Tenants:Add(service:GetTenant("HomeDepot_NH1")).
partitiongroup:Tenants:Add(service:GetTenant("HomeDepot_NH2")).
service:UpdatePartitionGroup(partitiongroup).
© 2011 Progress Software Corporation. All rights reserved.37
Adding Tenants To Groups
Tenant’s table instance allocated?• It’s a bit more difficult.
• Move or remove data from tenant’s table instance
– Dump/load, buffer copy/delete via super tenant, etc
• De-allocate tenant’s table instance (bye-bye data)
• Add tenant as member of the group for that table
– See previous slide
proutil <db> -C deallocate <table> [ tenant <name> | group <name> ]
© 2011 Progress Software Corporation. All rights reserved.38
Adding A New Multi-tenant Object
Use favorite tool• DB admin tool, navigator, load .df
Default allocation state (per tenant/group):• Immediate
– Creates new storage partition for each object of the table instance
– Allocation performed for each tenant/group
• Delayed or None
– Will not allocate any storage
– Allows non-default area assignment
Once tenants exist…
© 2011 Progress Software Corporation. All rights reserved.39
Adding indexes to existing tables
Activate at creation• Index wide, not tenant specific
• Can be slow to activate immediately (off-line)
Index activate online
• Concurrent for different tables of same tenant
• Concurrent for same or different table between tenants
R-code dependencies• Existing static queries maintain but don’t use newly activated index
• Tenant “mixed” activation with updated r-code
– Index name is inactive and cannot be referenced. (995)
proutil <db> -C idxactivate <index-name> [ tenant <name> | group <name> ]
© 2011 Progress Software Corporation. All rights reserved.40
Maintenance & Monitoring
Examples
© 2011 Progress Software Corporation. All rights reserved.41
Various Data Definition Files
Data dump files (.d)• Output directory change only: <tenant>/<table>.d
Binary dump files (.bd)• File name format change only - <table>.bd[n] becomes:
• <table>_<#>T_<tenant>.bd[n] OR <table>_<#>G_<group>.bd[n]
• customer_5T_HomeDepot.bd1 OR state_2G_itemGroup.bd1
Bulkload file definition (.fd)• No change
Data definition files (.df)ADD TABLE “Customer” MULTITENANT yes AREA "Customer/Order Area”
Flexibility with compatibility
© 2011 Progress Software Corporation. All rights reserved.42
Maintenance Utilities
Area and object utilities now support tenant/group keyword
Database wide utilities remain unchanged• Roll forward
• Truncate bi
• Backup/restore
• Auditing
• Increase startup parameters to
• Move schema
• Set/display create/toss limits
• Index move/table move
• Binary dump/load
• Index check
• Index rebuild
• Index fix
• Index compact
• Index activate
proutil <db> -C dbanalys [ area <area> ] [ shared | tenant <name> | group <name> ]
© 2011 Progress Software Corporation. All rights reserved.43
Analysis Tools Report By Tenant/Group
dbutil <db> -C dbanalys area <area> [ tenant <name> | group <name> ]
RECORD BLOCK SUMMARY FOR SHARED OBJECTS: -Record Size (B)- Table Records Size Min Max MeanPUB.state 51 1.8K 31 42 36 ----------------------------------Subtotals: 51 1.8K 31 42 36
RECORD BLOCK SUMMARY FOR GROUP HD_ItemGroup: -14 -Record Size (B)- Table Records Size Min Max MeanPUB.item 51 2.2K 37 49 44 -----------------------------------Subtotals: 51 2.2K 37 49 44
RECORD BLOCK SUMMARY FOR TENANT HomeDepot_NH1: 1 -Record Size (B)-Table Records Size Min Max Mean PUB.customer 4 280.0B 64 73 70PUB.order 9 312.0B 34 35 34PUB.order-line 31 1.0K 33 34 33 ---------------------------------Subtotals: 44 593.0K 33 73 46
© 2011 Progress Software Corporation. All rights reserved.44
Promon: Tenant Identity, Sorting And Filtering
M. Modify Defaults• S. Sort user lists : by user Id OR tenant Id
• T. Tenant filter for user lists : one OR range
User Control: by user number
Usr:Ten Name Type 0:0 richb BROK 5:2 u2@t2 SELF/ABL 6:1 u1@t1 SELF/ABL 7:0 richb SELF/ABL
Currently Connected Tenants
Tenant Id Name User Count
0 Default 3 1 t1 1 2 t2 1
User Control: by tenant Id
Usr:Ten Name Type 0:0 richb BROK 7:0 richb SELF/ABL 6:1 u1@t1 SELF/ABL 5:2 u2@t2 SELF/ABL
© 2011 Progress Software Corporation. All rights reserved.45
And now for the
Multi-tenant Configuration Tool
demo
© 2011 Progress Software Corporation. All rights reserved.46
Summary
Administration Architecture
Create tenant related stuff• Self provisioning API
Various utility usage
Multi-tenant Configuration Tool demo
© 2011 Progress Software Corporation. All rights reserved.47
Questions?
© 2011 Progress Software Corporation. All rights reserved.48
Sept. 19 – 22, 2011
Boston Westin Waterfront Hotel and Boston Convention & Exhibition Center