to the database
DBaaS At Thomson Reuters
Boris Belous 7/24/2014
1
Wouldn’t it be nice if someone actually had a clear idea of what “DBaaS” really is?
2
Cloud and “As‐A‐Service” •
Cloud computing relies on sharing of resources to achieve coherence and economies of scale, similar to a utility (like the electricity grid) over a network. At the foundation of cloud computing is the broader concept of converged infrastructure and shared services.
•
A cloud database is a database that typically runs on a cloud computing platform.
•
Two ways to run a Cloud Database: – Database As An Image – Database As A Service
3
“As‐An‐Image” and “As‐A‐Service” • Database‐As‐An‐Image – A user provisions a VM and taps into its resources – The image installed on the VM includes the database s/w and a running instance
• Database‐As‐A‐Service – A user taps into an existing VM or physical resource running a database. – A logical and physical partition of that resource is allocated to the user based on requested usage. – A user is presented with a multitude of options to manage (“to service”) the resource. 4
What’s a “database service”, anyway? What it is • Name / create / destroy container • Size instance • Configure instance
Instance
Database
• Name / create / destroy database • Startup / shutdown database • Backup / restore database • Replicate database • Migrate database • Clone database • Upgrade database
What it (probably) isn’t
H/W / Network / Storage / OS Provisioning
Schema
• Name / create / destroy service • Startup / shutdown service • Configure service
DB Service*
Resource Sharing & Scalability
Object
• Analyze cloud resources holistically • Balance databases across the resource pools based on criteria • Scale infinitely horizontally by adding more resources into the pool and vertically by increasing the capacity of each resource
• Provision / destroy a compute host, VM or physical (Why? – It’s an image) • Provision / destroy the OS on the host (Why? – It’s an image) • Provision / destroy disk storage (Why? – It’s an image) • Provision / destroy networking between compute and storage (Why? – It’s an image) • Provision / destroy clustering between hosts (Why? – It’s an image)
• Name /create / delete schema (Why? – OEM) • Export / import schema (Why? – Intricate app dependencies, dev know‐how) • Reverse‐engineer schema (Why? – OEM) • Grant access to schema (Why? – OEM)
• Name / create / delete object (Why? – OEM) • Modify object (Why? – OEM) • Export / import object (Why? – Intricate app dependencies, dev know‐how) • Reverse‐engineer object (Why? – OEM) • Grant access to object (Why? – OEM)
• Name / create / delete user and roles (Why? – OEM) • Grant / revoke privileges from user and roles (Why? – OEM) • Configure user quota, resources, etc… (Why? – OEM)
DB User
* An “Oracle DB service”, part of Oracle’s connectivity model; not to be confused with a “database service” in the context of DBaaS.
5
TR’s Three Initiatives Provide a physical resource with a DB (container) instance on it that one can manage and provide database services in those containers that one can manage
DBaaS ‐ Multitenant
DBaaS
Provide a physical resource with a DB instance on it
Provide a physical resource with a DB (container) instance on it that one can manage
Image
Cloud Database
6
Thomson Reuters DB‐As‐An‐Image • Based on Apache CloudStack ‐ Citrix CloudPlatform 4.2.1‐3 • Based on a VMWare farm – CIS Private Cloud – 3 regions 4x6 host ESX cluster w/ HP BL460 G8
• Releases: – Oracle 11.2.0.3 – April 2013 – MySQL 5.5 – December 2013
• Has had some pretty good usage (20‐30 Oracle DBs/month) • Limited use case – – – – –
Quick and often spin‐up / destroy Users have admin rights No Backup / HA Local disk No Support from DCO
7
DBaaI Technical Limitations • Local Disk – Constrainted to ~65GB of user data
• Limited to the VM farm – No RAC – 24GB VMs
• Cookie‐cutter databases on cookie‐cutter machines • CloudStack has limited customization capabilities ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ Bottom Line: it doesn’t scale to the Org’s needs very well
8
Requirements For The DBaaS • • • •
Open, modular, flexible, modern framework Framework supported by the IT community (OpenStack) Ability to partition and reuse resources Ability to provision database services, not database hardware or software • Ability to shift database services intelligently across a vast array of resources • Ability to scale the Cloud of resources to any desired size • Ability to build a robust security and support model around the provisioned services.
9
DB‐As‐A‐Service: Phase 1 Focus: Build automation •
Step 1: Implement OpenStack Havana w/ Oracle 11.2.0.3, stand‐alone – It’s modular – It’s open and easy to customize – It has a Database‐As‐A‐Service component (Trove)
•
Step 2: Integrate Thomson Reuters database build automation into Trove – – – –
Trove does not offer Oracle capabilities… yet Re‐use the build automation IP Consistently maintain build automation IP Get something out the door relatively quickly
This is where we are •
Step 3: Integrate DCO Infrastructure – – – –
•
Monitoring and alerting Database tracking system Support and incident tracking system OEM
Step 4: Introduce other advanced features – – – –
External NAS storage Backups Replication Mobility
10
DB‐As‐A‐Service: Phase 1 Focus: Build automation • Challenges – No native Trove support for Oracle. Oracle is not yet actively contributing to Trove. On their radar. • fudging the MySQL code for Oracle script call‐outs • Re‐using MySQL database object for Oracle schema – Some confusion and inconsistencies
– Git merging issues of OpenStack code with the Thomson Reuters derivative – OpenStack newness and immaturity, still evolving standards – Learning as we go – implementation speed – Questions around the Keystone security model robustness – Questions around the Cinder NetApp storage provisioning capabilities – Scalability beyond VMs – Resource partitioning, reuse and sharability – Service mobility (movement and cloning) 11
DB‐As‐A‐Service: Phase 1 Focus: Build automation
12
DB‐As‐A‐Service: Phase 1 • The Demo
Focus: Build automation
Get a list of flavors: trove flavor-list Create a VM of flavor 4 called “vm300” and seed its database instance with a schema called “schema301”: trove create vm300 4 --databases schema301 Associate a floating IP via Dashboard (http://10.220.60.110) Check the instance status: trove show List available schemas: trove database-list Add another schema “schema302” to the database: trove database-create schema302
13
DB‐As‐A‐Service: Phase 1 • The Demo
Focus: Build automation
Continued… Change the schema password to “pass123”: trove user-update-attributes schema302 -new_password pass123 List available schemas: trove database‐list Add regular user “user01” with password “pass123” to the database: trove user-create user1 pass123 List available users: trove user-list Test SQL*Net connectivity: sqlplus user01/pass123@:1521/vm300.novalocal
14
DB‐As‐A‐Service: Phase 2 Focus: Resource sharing and scalability
• The Oracle Multitenant Model – Partitioning a single resource logically and physically – Sharing resources – Requesting and determining resource consumption – Shifting of database services (moving and cloning) – Abstracting backups and replication – Similarities to MySQL’s data model 15
DB‐As‐A‐Service: Phase 2 Focus: Resource sharing and scalability • Challenges – Multitenant Licensing – Copy on write limitations • Shared NAS • Multitenant Self‐Service Provisioning – cannot scale
– Copy over db link limitations • Black box – No tuning – No parallelization / optimization – No read‐write database mode
– Upgradability / shifting between the containers of different 12c version and beyond – Model needs to be simplified / “dummified” for MySQL – Integration with OpenStack, security, metering – Which stacks are going to play a role in the pool? Exadata?
16
DB‐As‐A‐Service: Phase 2 Focus: Resource sharing and scalability
This is the “image” side
This is the “service” side
17
DB‐As‐A‐Service: Phase 2 Focus: Resource sharing and scalability
18
DB‐As‐A‐Service: Phase 2 Focus: Resource sharing and scalability
• The Demo List the cloud: list_cloud List databases: list_databases
Add a host to the cloud: add_host -n ai-oracle12c-4.int.thomsonreuters.com -k 'ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA3wC56fbaf54k5NtfbIROb/V4PGw1NeYN/tFrzo +5h9cNZJVP3tWsr+S1ThDObWMkRtPLMz9tLqvsS8OYHBDuCDYHUWTMgwlwntgUfbEM kmABo8bgfMOa6klK9ujop+N1nOa9eH1emqT5lJ4OEY7d8yz3u1yN2c0u+17RryHOej vi6Q6qM2qGSCzqbVuFwV9joOuLwGHfzk3kn7t8S8kjFYGRGu2qukUDxtLzK2V6OLsq G7M5h2bG1c/My8uJv2uuOOvU/1F94iPLPn+0SAFmPquXg5Jr0NSqb9HLY6CPhWGqP7 6RGbQ/ko6Pzq8gRHXONIxfeW/syJjW0v9ALw1poQ== oracle@ai-oracle12c-4' -t oracle -u boris Add resource: add_resource -n playground3 -c none -p 1521 -z 13 -e 1 -s 4 -u boris 19
DB‐As‐A‐Service: Phase 2 Focus: Resource sharing and scalability
• The Demo Continued…
Fulfill resource: fulfill_resource -i 11 -h 17 -u boris Add database: add_database -n 500 -d oracle -c region:3 -p storage,cpu,io -s 1000 -t 4 -e 1 -a pass123 -u boris Move database: move_database -i -c region:4 -p storage,cpu,io -o migrate -u boris Clone database: move_database -i -c zone:1 -p storage,cpu,io -o clone:501 -u boris Remove database: remove_database –i -u boris 20
TR vs Industry Trends Feature
Engine Type (Oracle, MySQL, etc…)
Amazon RDS
TR Non‐MT Oracle Only
TR MT Oracle Only
DB Engine Version Replication (A‐Z deployment) Allocated Storage Size Compute Size / Flavor / Class Arbitrary Instance ID / App Name Master User Management DB Name Port Number Availability Zone Character Set Option Group / Installed DB Features Parameter Group / Configuration Settings 21
TR vs Industry Trends (Continued) Feature
Security Groups
Amazon RDS
TR Non‐MT
TR MT
n/a
n/a
Fine‐Grained DBaaS Privileges Resource Analysis Across the Cloud Compute CPU / Memory (Flavor/Class) Resize Rebalancing / Placement Optimization Database Mobility – Migration Database Mobility – Cloning Database Backup Services Database Recovery Services Service Management Schema Management Object Management
22
23
Boris Belous 7/24/2014
1
Wouldn’t it be nice if someone actually had a clear idea of what “DBaaS” really is?
2
Cloud and “As‐A‐Service” •
Cloud computing relies on sharing of resources to achieve coherence and economies of scale, similar to a utility (like the electricity grid) over a network. At the foundation of cloud computing is the broader concept of converged infrastructure and shared services.
•
A cloud database is a database that typically runs on a cloud computing platform.
•
Two ways to run a Cloud Database: – Database As An Image – Database As A Service
3
“As‐An‐Image” and “As‐A‐Service” • Database‐As‐An‐Image – A user provisions a VM and taps into its resources – The image installed on the VM includes the database s/w and a running instance
• Database‐As‐A‐Service – A user taps into an existing VM or physical resource running a database. – A logical and physical partition of that resource is allocated to the user based on requested usage. – A user is presented with a multitude of options to manage (“to service”) the resource. 4
What’s a “database service”, anyway? What it is • Name / create / destroy container • Size instance • Configure instance
Instance
Database
• Name / create / destroy database • Startup / shutdown database • Backup / restore database • Replicate database • Migrate database • Clone database • Upgrade database
What it (probably) isn’t
H/W / Network / Storage / OS Provisioning
Schema
• Name / create / destroy service • Startup / shutdown service • Configure service
DB Service*
Resource Sharing & Scalability
Object
• Analyze cloud resources holistically • Balance databases across the resource pools based on criteria • Scale infinitely horizontally by adding more resources into the pool and vertically by increasing the capacity of each resource
• Provision / destroy a compute host, VM or physical (Why? – It’s an image) • Provision / destroy the OS on the host (Why? – It’s an image) • Provision / destroy disk storage (Why? – It’s an image) • Provision / destroy networking between compute and storage (Why? – It’s an image) • Provision / destroy clustering between hosts (Why? – It’s an image)
• Name /create / delete schema (Why? – OEM) • Export / import schema (Why? – Intricate app dependencies, dev know‐how) • Reverse‐engineer schema (Why? – OEM) • Grant access to schema (Why? – OEM)
• Name / create / delete object (Why? – OEM) • Modify object (Why? – OEM) • Export / import object (Why? – Intricate app dependencies, dev know‐how) • Reverse‐engineer object (Why? – OEM) • Grant access to object (Why? – OEM)
• Name / create / delete user and roles (Why? – OEM) • Grant / revoke privileges from user and roles (Why? – OEM) • Configure user quota, resources, etc… (Why? – OEM)
DB User
* An “Oracle DB service”, part of Oracle’s connectivity model; not to be confused with a “database service” in the context of DBaaS.
5
TR’s Three Initiatives Provide a physical resource with a DB (container) instance on it that one can manage and provide database services in those containers that one can manage
DBaaS ‐ Multitenant
DBaaS
Provide a physical resource with a DB instance on it
Provide a physical resource with a DB (container) instance on it that one can manage
Image
Cloud Database
6
Thomson Reuters DB‐As‐An‐Image • Based on Apache CloudStack ‐ Citrix CloudPlatform 4.2.1‐3 • Based on a VMWare farm – CIS Private Cloud – 3 regions 4x6 host ESX cluster w/ HP BL460 G8
• Releases: – Oracle 11.2.0.3 – April 2013 – MySQL 5.5 – December 2013
• Has had some pretty good usage (20‐30 Oracle DBs/month) • Limited use case – – – – –
Quick and often spin‐up / destroy Users have admin rights No Backup / HA Local disk No Support from DCO
7
DBaaI Technical Limitations • Local Disk – Constrainted to ~65GB of user data
• Limited to the VM farm – No RAC – 24GB VMs
• Cookie‐cutter databases on cookie‐cutter machines • CloudStack has limited customization capabilities ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ Bottom Line: it doesn’t scale to the Org’s needs very well
8
Requirements For The DBaaS • • • •
Open, modular, flexible, modern framework Framework supported by the IT community (OpenStack) Ability to partition and reuse resources Ability to provision database services, not database hardware or software • Ability to shift database services intelligently across a vast array of resources • Ability to scale the Cloud of resources to any desired size • Ability to build a robust security and support model around the provisioned services.
9
DB‐As‐A‐Service: Phase 1 Focus: Build automation •
Step 1: Implement OpenStack Havana w/ Oracle 11.2.0.3, stand‐alone – It’s modular – It’s open and easy to customize – It has a Database‐As‐A‐Service component (Trove)
•
Step 2: Integrate Thomson Reuters database build automation into Trove – – – –
Trove does not offer Oracle capabilities… yet Re‐use the build automation IP Consistently maintain build automation IP Get something out the door relatively quickly
This is where we are •
Step 3: Integrate DCO Infrastructure – – – –
•
Monitoring and alerting Database tracking system Support and incident tracking system OEM
Step 4: Introduce other advanced features – – – –
External NAS storage Backups Replication Mobility
10
DB‐As‐A‐Service: Phase 1 Focus: Build automation • Challenges – No native Trove support for Oracle. Oracle is not yet actively contributing to Trove. On their radar. • fudging the MySQL code for Oracle script call‐outs • Re‐using MySQL database object for Oracle schema – Some confusion and inconsistencies
– Git merging issues of OpenStack code with the Thomson Reuters derivative – OpenStack newness and immaturity, still evolving standards – Learning as we go – implementation speed – Questions around the Keystone security model robustness – Questions around the Cinder NetApp storage provisioning capabilities – Scalability beyond VMs – Resource partitioning, reuse and sharability – Service mobility (movement and cloning) 11
DB‐As‐A‐Service: Phase 1 Focus: Build automation
12
DB‐As‐A‐Service: Phase 1 • The Demo
Focus: Build automation
Get a list of flavors: trove flavor-list Create a VM of flavor 4 called “vm300” and seed its database instance with a schema called “schema301”: trove create vm300 4 --databases schema301 Associate a floating IP via Dashboard (http://10.220.60.110) Check the instance status: trove show List available schemas: trove database-list Add another schema “schema302” to the database: trove database-create schema302
13
DB‐As‐A‐Service: Phase 1 • The Demo
Focus: Build automation
Continued… Change the schema password to “pass123”: trove user-update-attributes schema302 -new_password pass123 List available schemas: trove database‐list Add regular user “user01” with password “pass123” to the database: trove user-create user1 pass123 List available users: trove user-list Test SQL*Net connectivity: sqlplus user01/pass123@:1521/vm300.novalocal
14
DB‐As‐A‐Service: Phase 2 Focus: Resource sharing and scalability
• The Oracle Multitenant Model – Partitioning a single resource logically and physically – Sharing resources – Requesting and determining resource consumption – Shifting of database services (moving and cloning) – Abstracting backups and replication – Similarities to MySQL’s data model 15
DB‐As‐A‐Service: Phase 2 Focus: Resource sharing and scalability • Challenges – Multitenant Licensing – Copy on write limitations • Shared NAS • Multitenant Self‐Service Provisioning – cannot scale
– Copy over db link limitations • Black box – No tuning – No parallelization / optimization – No read‐write database mode
– Upgradability / shifting between the containers of different 12c version and beyond – Model needs to be simplified / “dummified” for MySQL – Integration with OpenStack, security, metering – Which stacks are going to play a role in the pool? Exadata?
16
DB‐As‐A‐Service: Phase 2 Focus: Resource sharing and scalability
This is the “image” side
This is the “service” side
17
DB‐As‐A‐Service: Phase 2 Focus: Resource sharing and scalability
18
DB‐As‐A‐Service: Phase 2 Focus: Resource sharing and scalability
• The Demo List the cloud: list_cloud List databases: list_databases
Add a host to the cloud: add_host -n ai-oracle12c-4.int.thomsonreuters.com -k 'ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA3wC56fbaf54k5NtfbIROb/V4PGw1NeYN/tFrzo +5h9cNZJVP3tWsr+S1ThDObWMkRtPLMz9tLqvsS8OYHBDuCDYHUWTMgwlwntgUfbEM kmABo8bgfMOa6klK9ujop+N1nOa9eH1emqT5lJ4OEY7d8yz3u1yN2c0u+17RryHOej vi6Q6qM2qGSCzqbVuFwV9joOuLwGHfzk3kn7t8S8kjFYGRGu2qukUDxtLzK2V6OLsq G7M5h2bG1c/My8uJv2uuOOvU/1F94iPLPn+0SAFmPquXg5Jr0NSqb9HLY6CPhWGqP7 6RGbQ/ko6Pzq8gRHXONIxfeW/syJjW0v9ALw1poQ== oracle@ai-oracle12c-4' -t oracle -u boris Add resource: add_resource -n playground3 -c none -p 1521 -z 13 -e 1 -s 4 -u boris 19
DB‐As‐A‐Service: Phase 2 Focus: Resource sharing and scalability
• The Demo Continued…
Fulfill resource: fulfill_resource -i 11 -h 17 -u boris Add database: add_database -n 500 -d oracle -c region:3 -p storage,cpu,io -s 1000 -t 4 -e 1 -a pass123 -u boris Move database: move_database -i -c region:4 -p storage,cpu,io -o migrate -u boris Clone database: move_database -i -c zone:1 -p storage,cpu,io -o clone:501 -u boris Remove database: remove_database –i -u boris 20
TR vs Industry Trends Feature
Engine Type (Oracle, MySQL, etc…)
Amazon RDS
TR Non‐MT Oracle Only
TR MT Oracle Only
DB Engine Version Replication (A‐Z deployment) Allocated Storage Size Compute Size / Flavor / Class Arbitrary Instance ID / App Name Master User Management DB Name Port Number Availability Zone Character Set Option Group / Installed DB Features Parameter Group / Configuration Settings 21
TR vs Industry Trends (Continued) Feature
Security Groups
Amazon RDS
TR Non‐MT
TR MT
n/a
n/a
Fine‐Grained DBaaS Privileges Resource Analysis Across the Cloud Compute CPU / Memory (Flavor/Class) Resize Rebalancing / Placement Optimization Database Mobility – Migration Database Mobility – Cloning Database Backup Services Database Recovery Services Service Management Schema Management Object Management
22
23
