Flexibility without the complications Richard Banville Fellow Progress Software June 7 2011 Agenda Administration architecture Multitenant Administration How to Utility usage examples ID: 636869
Download Presentation The PPT/PDF document "Multi-tenant Table Administration" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Multi-tenant TableAdministration
Flexibility without the complications.
Richard Banville
Fellow, Progress Software
June 7, 2011Slide2
Agenda
Administration architecture
Multi-tenant Administration: “How to”Utility usage examplesMulti-tenant Configuration Tool demoSlide3
Multi-tenant Identity DefinitionsTenant
“Named” group of users Share same application and dataMulti-tenancy
Deployment supporting multiple tenantsDefault tenantUsers who do not assert tenant identitySuper tenantTenant with ability to access/manage data of any tenantMulti-tenant groupMultiple tenants sharing data for a specific multi-tenant table Slide4
Name index
Record 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 indexSlide5
Multi-tenant Administration
Tenant AdministrationTenantsGroups
DomainsUsersSecurity/Data AccessPerformanceMonitoringConfigurationData AdministrationTable definitionAllocationLocationMaintenance
What needs administration?Slide6
Multi-tenant Model
Users
Suzi@HD_Domain
Allen@HD_Domain
Cat@HD_Domain
Rich@HD_Domain
Rich@Lowes1
John@Lowes1
Claudio@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
…
1
2
3
0 thru 4Slide7
Physical Model
ExamplesSlide8
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
Area10Slide9
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 7
HD
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 IndexesSlide10
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 AreaSlide11
Example: Adding A New Tenant “AceHardware”
Data
Tenancy Layer
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 nextSlide12
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
ItemIdxs
Customers
Orders
Items
…
Example
: Adding an Inventory tableSlide13
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
ItemIdxs
Customers
Orders
Items
…
Inventory
Inventory
A19
A20
HD Inventory Data
HD
InventoryIdxs
A21
A22
Lowes Inventory Data
Lowes
InvIdxs
Tenant
specific space allocation immediate or delayedSlide14
Groups
Data
Tenancy Layer
AceHardware
TaxCode
State
Customers
…
HomeDepot
TaxCode
State
Customers
…
Tenants
have
their own table instance
if:
Not
part of a
groupSlide15
Tax Code Data
State Data
Groups
Data
Tenancy Layer
AceHardware
TaxCode
State
Customers
…
A30
Tax Code Indexes
State Indexes
TaxCodeGroup
TaxCodes
HomeDepot
TaxCode
State
Customers
…
StateGroup
States
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.Slide16
Tax Code Data
State Data
Groups
Data
Tenancy Layer
AceHardware
TaxCodeGroup
StateGroup
Customers
…
A30
Tax Code Indexes
State Indexes
TaxCodeGroup
TaxCodes
HomeDepot
TaxCodeGroup
StateGroup
Customers
…
StateGroup
States
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.Slide17
I think I get it,
but what do I do to do it?Slide18
Multi-tenant Administration
Administration mechanisms
Database enablement: dbutil describe: 14 Multi-tenancy Yes Create storage areasNo 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>.stSlide19
Identify Multi-tenant Tables
Users
Suzi@HD_Domain
Allen@HD_Domain
Cat@HD_Domain
Rich@HD_Domain
Rich@Lowes1
John@Lowes1
Claudio@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
…
1
2
3
0 thru 4Slide20
Object Creation Considerations w/out Tenants
Convert existing table to multi-tenant tableAll objects of table instance MUST reside in TII storage area
One way conversionDefault partition contains existing dataCreate new multi-tenant tablesNo need for default partition (optional)New multi-tenant “default index” goes in same area as tableAdding new indexes with new tablesActivation state at index level, NOT tenant levelCan be changed after the fact
Schema manipulation tools such as data dictionary, SQL DDL, etcSlide21
Sequence Creation Considerations
SequencesShared or tenant specific
Definition database wide (max/min/cycle values)Current value tenant specificNot group specificAvoid multi-tenant sequences for group dataGroup data is shared amongst specific tenantsSlide22
Tenant Creation
Users
Suzi@HD_Domain
Allen@HD_Domain
Cat@HD_Domain
Rich@HD_Domain
Rich@Lowes1
John@Lowes1
Claudio@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
…
1
2
3
0 thru 4Slide23
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?Slide24
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 Area
Defaults
Reference
Interfaces,
ServicesSlide25
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 NoneSlide26
Partition Location Re-assignment
Reassign partition location after allocation
Table/index move OR Dump, de-allocate, reassign via APIs/tools, reloadUsing table/index move utilitiesNew de-allocate utility (bye-bye data)Binary Dump: data is tenant indifferentDump 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>Slide27
Using Domains
Users
Suzi@HD_Domain
Allen@HD_Domain
Cat@HD_Domain
Rich@HD_Domain
Rich@Lowes1
John@Lowes1
Claudio@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
…
1
2
3
0 thru 4Slide28
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). Slide29
Users
Suzi@HD_DomainAllen@HD_Domain
Cat@HD_Domain
Rich@HD_Domain
Rich@Lowes1
John@Lowes1
Claudio@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
…
1
2
3
0 thru 4Slide30
User Creation
Users D
omain 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:
fblake
HD_CEO
Userid
/Password is incorrect.
Nothing
new hereSlide31
User Creation
Users D
omain 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_Domain
HD_CEO
Nothing
new hereSlide32
Group Creation
Data
Tenancy Layer
HomeDepot_NH1
Customers
…
CreditCheckGroup
Credit
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 Slide33
Group Creation
Data
Tenancy Layer
HomeDepot_NH1
CreditCheckGroup
Customers
…
CreditCheckGroup
Credit
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 Slide34
Group Creation
Data
Tenancy Layer
HomeDepot_NH1
CreditCheckGroup
HD_ItemsGroup
Customers
…
CreditCheckGroup
Credit
HomeDepot_NH2
CreditCheckGroup
HD_ItemsGroup
Customers
…
HD_ItemsGroup
Items
Credit Data
HD Item Data
A300
Credit Indexes
HD Item 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
Multiple
Home Depot tenants sharing
same
item list, same credit check data.Slide35
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 Area
DefaultsSlide36
Adding Tenants To Groups
Table allocation state: NoneSimply 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).Slide37
Adding Tenants To GroupsT
enant’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, etcDe-allocate tenant’s table instance (bye-bye data)Add tenant as member of the group for that tableSee previous slide
proutil
<db>
-C
deallocate
<table>
[
tenant
<name> |
group <name> ]Slide38
Adding A New Multi-tenant Object
Use favorite toolDB admin tool, navigator, load .df
Default allocation state (per tenant/group): ImmediateCreates new storage partition for each object of the table instanceAllocation performed for each tenant/groupDelayed or NoneWill not allocate any storageAllows non-default area assignment
Once tenants exist…Slide39
Adding indexes to existing tablesActivate at creation
Index wide, not tenant specificCan be slow to activate immediately (off-line)
Index activate onlineConcurrent for different tables of same tenantConcurrent for same or different table between tenantsR-code dependenciesExisting static queries maintain but don’t use newly activated indexTenant “mixed” activation with updated r-codeIndex name is inactive and cannot be referenced. (995
)
proutil
<db>
-C
idxactivate
<index-name>
[
tenant
<name> | group <name> ]Slide40
Maintenance & Monitoring
ExamplesSlide41
Various Data Definition Files
Data dump files (.d)Output directory change only: <tenant
>/<table>.dBinary 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 changeData definition files (.df)
ADD TABLE “Customer”
MULTITENANT yes AREA "Customer/Order Area”
Flexibility with compatibilitySlide42
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>
]Slide43
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 Mean
PUB.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 Mean
PUB.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 70
PUB.order
9 312.0B 34 35 34
PUB.order-line 31 1.0K 33 34 33 ---------------------------------Subtotals: 44 593.0K 33 73 46Slide44
Promon: Tenant Identity, Sorting And Filtering
M. Modify DefaultsS. Sort user lists : by user
Id OR tenant IdT. 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/ABLSlide45
And now for the
Multi-tenant Configuration Tool
demoSlide46
SummaryAdministration Architecture
Create tenant related stuffSelf provisioning APIVarious utility usage
Multi-tenant Configuration Tool demoSlide47
Questions
?Slide48Slide49
Sept. 19 – 22, 2011
Boston Westin Waterfront Hotel and Boston Convention & Exhibition CenterSlide50
Multi-tenant Tables: Data Migration
1 1 Lance Armstrong 1 2 John Cleese
1
3 Tipper Gore
2 4 Shaquille O’Neil
2 7 Ben Stein
2 8 William
Shatner
3 2 Dennis Rodman
3 7 Nick Nolte
3 9 Lindsay
Lohan
3 10 Al Gore
Default
Partition
Customer
Schema
Enable multi-tenancy on existing db
Mark existing table as multi-tenant table
Set super-tenant identity
Move data
Truncate empty
partition
Tenant1
Tenant2
Tenant3
Infrastructure
App
Shared
Tenancy
DB
Tenant1
Tenant2
Tenant3
App
DB
Infrastructure
OE11 Shared
Tenancy
DEFINE BUFFER
bCust
FOR
cust
.
FOR EACH
Cust
WHERE
Cust.tenant
-id = 1
TENANT-WHERE BUFFER-TENANT-ID(
Cust
)=0
:
CREATE
bCust USE-TENANT 1.
BUFFER-COPY Cust
TO bCust
. DELETE
Cust.
END.Slide51
1
1 Lance Armstrong 1 2 John Cleese 1
3 Tipper Gore
2 4 Shaquille O’Neil
2 7 Ben Stein
2 8 William
Shatner
3 2 Dennis Rodman
3 7 Nick Nolte
3 9 Lindsay
Lohan
3 10 Al Gore
Default
Partition
Customer
Schema
1 1
Lance
Armstrong
1 2
John
Cleese
1 3 Tipper Gore
2 4 Shaquille O’Neal
2 7 Ben Stein
2 8 William
Shatner 3 2 Dennis Rodman
3 7 Nick Nolte
3 9 Lindsay Lohan
3 10 Al Gore
Customer
Schema
Tenant 3
(Rent-a-wreck)
Tenant 1
(Hertz)
Tenant 2(Hertz)
Multi-tenant Tables: Data Migration
Tenant1
Tenant2
Tenant3
Infrastructure
App
Shared
Tenancy
DB
SETUSERID
“userA@tenant1”.
FOR EACH
customer
:
DISPLAY
customer
.
NOTE: Existing tenant column remains but is no longer needed for new multi-tenant queries.
Tenant1
Tenant2
Tenant3
App
DB
Infrastructure
OE11 Shared
Tenancy Slide52
Multi-tenant Tables: Data Migration
DB #
1(Hertz)
1 Lance Armstrong
2 John
Cleese
3 Tipper Gore
Customer
DB #
2
(Hertz)
4
Shaquille O’Neal
7 Ben Stein
8 William
Shatner
Customer
DB #
3
(R.W.)
2 Dennis Rodman
7 Nick Nolte
9 Lindsay Lohan 10 Al Gore
Customer
Tenant1
Tenant2
Tenant3
App
App
App
DB
DB
DB
Infrastructure
Infrastructure or Application
Tenancy
Tenant1
Tenant2
Tenant3
App
DB
Infrastructure
OE11 Shared
Tenancy Slide53
Multi-tenant Tables: Data Migration
Create
new multi-tenant db
Can convert existing one
Add tenants
Load multi-tenant schema
Dump from current
Load
to new
Tenant1
Tenant2
Tenant3
App
DB
Infrastructure
OE11 Shared
Tenancy
Tenant1
Tenant2
Tenant3
App
App
App
DB
DB
DB
Infrastructure
Infrastructure or Application
Tenancy
DB #
1
(Hertz)
1 Lance Armstrong
2 John
Cleese
3 Tipper Gore
Customer
DB
#2
(Hertz)
4
Shaquille O’Neal
7 Ben Stein
8 William
Shatner
Customer
DB #
3
(R.W.)
2 Dennis Rodman
7 Nick Nolte
9 Lindsay
Lohan
10 Al Gore
Customer
proutil
DB1 –C dump customer
proutil
MTdb –C load customer tenant hertz2Slide54
Multi-tenant Tables: Data Migration
Tenant1
Tenant2
Tenant3
App
DB
Infrastructure
OE11 Shared
Tenancy
Tenant1
Tenant2
Tenant3
App
App
App
DB
DB
DB
Infrastructure
Infrastructure or Application
Tenancy
1 Lance
Armstrong
2 John
Cleese
3 Tipper Gore
4 Shaquille
O’Neal
7 Ben
Stein
8 William
Shatner
2
Dennis
Rodman
7
Nick
Nolte
9 Lindsay
Lohan
10
Al Gore
Customer
Schema
Tenant 3
(Rent-a-wreck)
Tenant 1
(Hertz
)
Tenant 2
(Hertz)
DB
#1
(Hertz)
1 Lance Armstrong
2 John
Cleese
3 Tipper Gore
Customer
DB #
2(Hertz)
4 Shaquille O’Neal
7 Ben Stein
8 William Shatner
Customer
DB #
3(R.W.)
2 Dennis Rodman
7 Nick Nolte 9 Lindsay Lohan
10 Al Gore
Customer