SOUG_Deployment__Automation_DB

Database Deployment
Automation
EFSTATHIOS EFSTATHIOU
CEO, DATABASE-SERVERS.COM

Agenda
Introduction
A Friday afternoon story
The term automation reviewed
Automation Visions
Potential Tools and Frameworks
Building Basic Deployment Workflows
Conclusion
Q&A

Introduction
About Myself
◦ Married with children (one daughter, who I gave the advise to never work in IT)
◦ Linux since 1998
◦ Oracle since 2000
◦ OCM & OCP
◦ Chairman of NGENSTOR since 2014
◦ CEO of DATABASE-SERVERS since 2015
DATABASE-SERVERS.COM
The fastest way to run databases

Introduction
About DATABASE-SERVERS
◦ Founded in 2015
◦ Custom license-optimized whitebox servers
◦ Standard Operating Environment Toolkit for Oracle/Redhat/Suse Linux
◦ CLX Cloud Systems based on OVM/LXC Stack
◦ Performance kits for all brands (HP/Oracle/Dell/Lenovo)
◦ Water Cooling
◦ Overclocking
◦ HBA/SSD Upgrades
◦ Tuning/Redesign of Oracle Engineered Systems (ODA, Exadata)
◦ Storage Extension with NGENSTOR Arrays
◦ Performance Kits
◦ Merger with NGENSTOR
◦ More Appliances and more competition for Oracle Engineered Systems giving you more ROI

A Friday afternoon
story
Imagine
… it’s Friday 16:55 the developer finished the last lines of code and uploaded it to the versioning
system.
It’s just 5 minutes separating him from the weekend.
He sends out a mail to operations, who could send him to hell.
«Him again!!!!», yells Pete the DBA.
Before he can even think about looking at the code, the big boss calls him asking for a status.
Pete deploys the script, which throws an error.
Rollback is tricky and Pete’s weekend fucked up…

The term automation
reviewed
In our the database world we can separate into infrastructure and application tasks:
Infrastructure
◦ Server / VM deployment
◦ Database Creation
◦ Backup/Restore/Replication
◦ …
Application
◦ New Application Version
◦ New Interfaces
◦ New Attributes
◦ …

The term automation
reviewed
The two main areas we work on are:
Housekeeping / Recurring Tasks
◦ Gather Statics of your Databases (Schema)
◦ Cleanup of ADR
Change Management & Change Deployment
◦ Data Correction Patches
◦ New Application / Database / Host Setup
◦ OS / Database / Application Patching

The term automation
reviewed
Since the days of mainframe…
◦ You write a deployment or housekeeping script
◦ You eventually add this code to a versioning system and check it out
◦ You add it to a scheduler with conditions (e.g. JOB Control or cron)
◦ It works for your most important application, but only for that one
◦ You do not have a lifecycle
◦ You do not have configuration managment
◦ Your goal is to automate to deploy/run

The term automation
reviewed
In most companies Automation atm realized is:
Either
◦ Something they code theirselves
◦ To deploy stuff
◦ Using a scheduler
Or
◦ Something they bought a huge software suite for to kill the task and are still programming
◦ One department is leading
◦ Another Agent on the Machine besides the other 10 to 20

Automation
Visions
What everyone think it is
CIO (10’000m)
«Press button to deploy»
Architect (1000m)
«One solution fits all»
Developer (100m)
«Tool to deploy my scripts»
DBA (1m)
«Alot of work to clean up the mess and sleepless weekends"

Automation
Visions
What they think they actually need
CIO
Automation capability
Architect
Documented and standardized workflows
Developer
«A workflow to deploy my script»
DBA
Workflows, that can be executed by a Job Scheduler to:
Check scripts
Do Backup
Run Scripts
Post Check
Post Backup

Automation Visions
What Organization needs
Company
«Capability to map business processes into deployable workflows for the company’s IS applications»
IT Department
«Rebuild capability for their core applications such as their ERP System»

Potential Tools and
Frameworks
Tools
Job Schedulers (e.g. Control M)
Software Packaging (RPM, Redhat Satellite)
Provisioning (Puppet, HP Server Automation)
Versioning Systems (SourceSafe, SVN)
DevOps Tools (Delphix)
OS-Tools (System Center)
Host Tools

Potential Tools and
Frameworks
Key decisions for selecting tools
◦ Audience / Scope (entire IT, Development only)
◦ Reuse existing work and tool suites y/n?
◦ Do I want to have interfaces y/n?
◦ Do I want to map it to current organisation or do I want to make a transition?
◦ What are my KPI (no of deployments, failure rate)?
◦ Cost
◦ Time to market
◦ Additional added value
◦ Enforce Change Management
◦ Configuration Management
◦ Trackability / Reporting

Potential Tools and
Frameworks
Potential Pitfalls
◦ Automation/Provisioning Suites are often tuned for specific areas
◦ Provisioning, Development Tasks
◦ Often gaps, eg. bad Database Plugins => Coding
◦ Automation/Provisioning Suites have alot of features, but are stubborn
◦ You can spend a lot of time defining workflows
◦ Pure Schedulers have only few pre-defined Workflows
◦ Again: alot of coding 
◦ Your solution does only work for one team (e.g. Middleware) 
◦ Acceptance problem with other teams
◦ Requirement gap
◦ Code around the problems (customizing)

Potential Tools and
Frameworks
Solution Approaches
◦ Define what’s feasible and what not
◦ Database Versioning?
◦ Micro Changes
◦ Think in Services
◦ What does my team provide to other peers?
◦ Deployment
◦ Configuration Data
◦ How can I realize my services in my «environment»?
◦ How can I deploy the developer’s scripts using Cloud Control?
◦ How do I provide and lifecycle for my application scripts?
◦ Define Interfaces
◦ Development Suite to Oracle Cloud Control Job Submission

Potential Tools and
Frameworks
How we do it
◦ Use what’s available
◦ IBM UrbanCode Deploy for
◦ Script Versioning
◦ Middleware Workflows
◦ Cloud Control for
◦ Provisioning
◦ Store Target Properties (custom ones) for UrbanCode Interface
◦ Database Jobs
◦ SQL Script Execution
◦ Housekeeping
◦ Backup

Building Basic Deployment
Workflows
Non-Database People often understimate Database Deployments
◦ They think in files
◦ packages are most add/delete files/compile
◦ New installation means delete, config and deploy
◦ They have no user Data
◦ They do not see the database engine’s complexity is os-like
◦ An upgrade/change on the engine/appliacation will change the behaviour
◦ New data will be written in a different «format»
◦ Rollback is tricky, as the customer want’s to keep it’s data
◦ Backup is not enough, we need to migrate the data and it’s attributes
◦ Using database links, we can also touch data in a remote schema (for which we hopefully have a backup…)

Workflows
A minimal set of tasks for a database deployment
◦ Check, what databases are touched
◦ Backup
◦ Check Script Synthax
◦ Deploy
◦ Check Application
◦ Go/No Go
As simple as it sounds, mostly people overdo it right from the start 

Workflows
How we do it

Workflows
Set up credentials (required for script) execution
◦ To run host commands via Cloud Control or EMCLI (emcli execute_hostcmd)
◦ emcli set_credential -target_type=host -target_name=$host -credential_set=HostCredsNormal -user=UCD -
column="username:oracle;password:$1"
◦ To run sql commands via Cloud Control or EMCLI (emcli execute_sql)
◦ emcli set_credential -target_type=host -target_name=$host -credential_set=DBHostCreds -user=UCD -
column="username:oracle;password:$1"
◦ emcli set_credential -target_type=oracle_database -target_name=$db -credential_set=DBCredsNormal -user=UCD -
columns="username:sys;password:${ORA_PW_PREFIX}c${SID};role:sysdba"
◦ emcli set_credential -target_type=oracle_database -target_name=$db -credential_set=DBCredsSYSDBA -user=UCD -
columns="sysDBAUserName:sys;sysDBAPassword:${ORA_PW_PREFIX}c${SID};sysDBARole:sysdba"

Workflows
Create Proxy User
◦ Proxy Users and Auditing Proxy Users (Doc ID 782078.1)
CREATE USER UCD IDENTIFIED BY *********;
GRANT CREATE SESSION TO UCD;
ALTER USER DBSNMP GRANT CONNECT through UCD;
AUDIT SESSION;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY UCD ON BEHALF OF DBSNMP;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY DBSNMP BY ACCESS;
CONNECT UCD[DBSNMP]/*********;
CREATE TABLE test (id NUMBER);
INSERT INTO test VALUES(1);
commit;

Workflows
Target Properties
◦ Add
emcli add_target_property -target_type="oracle_database" -property="JDBC_CONNECTION_STRING"
emcli set_target_property_value -property_records="GRMAN01_GRMAN01_1:oracle_database:JDBC_CONNECTION_STRING:$host
GRMAN01_1 1568"
emcli remove_target_property -target_type="oracle_database" -property="JDBC_CONNECTION_STRING«
◦ Use

Workflows
Create a Job Template

Workflows
Use our Multi-Exec Template

Workflows
Fancy: Generate Delphix CLI Template
◦ Create a Multi-Exec Job in the Cloud Control Library
◦ Source0: https://community.delphix.com/delphix/topics/tip_of_the_day_using_the_cli_to_provision_an_oracle_vdb
◦ Integrate VDB Creation into your testing
◦ We use Delphix to create an ad-hoc VDB or a set, if changes span multiple databases (db-links)
◦ Workflow
◦ Checkout Nightly Build from IBM UCD
◦ Create VDB Env
◦ Test Script in VDB
◦ Check Result
◦ Approve/Reject Deployment

Workflows
WIP: Liquidbase
◦ Not all database types are supported
◦ Used for quality assurance
◦ Deploy to Dev
◦ Deploy to Test
◦ Run Liquidbase diff for QAS
◦ Generate Changelog file, if needed to correct the delta ;-)

Conclusion
Database Deployment Automation
◦ Is doable
◦ Still requires efforts from multiple teams
◦ Release management
◦ Development
◦ Dba
◦ Comes with no OOTB-Solution for all you individual Application needs
◦ There will always be a slight amount of code required
◦ Can be simplified using Automation Tools
◦ Oracle Cloud Control provides a very good job schedulder, CMDB facilicy and overall good Warehouse
◦ Wheither you script yourself or buy the management pack, it’s there: USE IT ;-)
◦ A Synthax Checker would be very welcome in future releases or generally more Developer-Friendly features like a Code
Versioning Plugin or SQL Script Repository

Contact
elgreco@linux.com
efstathios.efstathiou@database-servers.com

SOUG_Deployment__Automation_DB

More Related Content

What's hot

Similar to SOUG_Deployment__Automation_DB

More from UniFabric

SOUG_Deployment__Automation_DB