2009 sdd's centralized database administration service (cdas)

SDD’s Centralized Database SDD’s Centralized Database Administration Service (CDAS)Administration Service (CDAS)

Yves Leonard,

Database Administration Services,

Oracle Technology Center,

System Development Division.

April 16th, 2009

04/10/23Statistics Canada • Statistique Canada2

Agenda

What is a DBA? History of the DBA service Current team composition Consolidated advantages Our DBA services Client Analysis SLA Benefits Questions

04/10/23Statistics Canada • Statistique Canada3

What is a DBA?

A Database administrator (DBA) is a person who is responsible for the environmental aspects of a database. In general, these include:

• Recoverability - Creating and testing Backups• Integrity - Verifying or helping to verify data integrity• Security - Defining and/or implementing access controls

to the data• Availability - Ensuring maximum uptime• Performance - Ensuring maximum performance• Development and testing support - Helping

programmers and engineers to efficiently utilize the database.

• And more…

04/10/23Statistics Canada • Statistique Canada4

History of the DBA service

Started in 2005 for Census 2006 2 DBAs Supported the response database

2006 Some database upgrade for Field 4

04/10/23Statistics Canada • Statistique Canada5

History – 2007 / 2008

2 new clients 1) International Trade Division

• 3+ Tb of data, 9 instances, full service 2) Survey Operation Division

• < 1 Tb of data, 4 instances, full service• Network A & B

04/10/23Statistics Canada • Statistique Canada6

History 2008 / 2009 ..1

Field 6 study at end of 07/08 Recommendation that all divisions in Field 6 joins the

DBA service 5 new clients Geography division

• < 3 Tb of data, 11 instances, full service, Net A & B Business Register Division

• Backup support / Partner with BR• < 2 Tb of data, 50+ instances

04/10/23Statistics Canada • Statistique Canada7

History 2008 / 2009 ..2

Tax Data Division• < 3 Tb of data, 2 instances, full support

Health Division• > 1 Tb of data, 3 instances, full support• Oracle and Sql-Server

Census of Agriculture• < 0.5 Tb of data, 4 instances, full support• Will grow in production…

Census of Population• > 2 Tb of data(2006), 28 instances, full support• Sql-server, Oracle and DB2• Encryption, stand-by and RAC

Dissemination Division• Backup / emergency support

04/10/23Statistics Canada • Statistique Canada8

Current Team Composition

Sub-Section of the Oracle Technology Center

• OTC Chief: Cathy Kari

• DBA Manager: Yves Leonard Mandate

• Extend OTC services

• Full range database administration

04/10/23Statistics Canada • Statistique Canada9

Current Team composition

04/10/23Statistics Canada • Statistique Canada10

Consolidated advantages: Client Perspective(1)

Reduced risks:• Experienced CS as DBA to meet short,

medium & long term needs• Backup DBA to your local resources to get

started• Turnover, succession planning• Affordable standards, best practices &

tools i.e. RMAN, Redgate

04/10/23Statistics Canada • Statistique Canada11

Consolidated advantages: Client Perspective(2)

Flexible Service Level Agreement:• Full range of services integrated with ITSD

Services• Availability and standby duties tailored to your

business needs• Estimate at start of fiscal year & refine/adjust

as required

04/10/23Statistics Canada • Statistique Canada12

Consolidated advantages: Client Perspective(3)

Client-oriented services:• Excellent reputation with existing clients• Partnership with local DBA(s)• Complement and collaborate with

developers (different skill sets)

04/10/23Statistics Canada • Statistique Canada13

Consolidated advantages: Agency Perspective

Efficient use of scarce DBA expertise & CS across the bureau Pool of resources summoned & allocated in the event of an emergency Problem solved for one client leveraged to bureau users Research, test, implement & document: Patches, Upgrades & New Versions SRM to report, track & document – historical information for client,

knowledge base for the Oracle community Opportunity to assist with integration to related services

• ITSD Corporate Services

• Oracle Technical Support Services

04/10/23Statistics Canada • Statistique Canada14

Consolidated advantages: Employee Perspective

Reduce time training new DBA’s within centre of expertise • Coaching, mentoring, common methods/practices, knowledge

sharing Team covers for each other

• planned & unplanned absence Increased job satisfaction

• Member of DBA team with common interest/share ideas vs. isolated IT worker

Opportunity to develop technical leaders • Clients & agency benefit from employees keeping abreast,

anticipating needs in a highly specialized domain

04/10/23Statistics Canada • Statistique Canada15

Consolidated advantages: Expected benefits

Opportunity to address scarcity of DBA expertise & agency CS shortage

Conducted studies of 5 divisions in Field 6• BR, TDD, Standards, Geography & SAAD• Recommendations to offer a consolidated Field 6 DBA services

working closely with local DBA’s in place Demonstrated efficiencies by consolidation and risk

mitigations is promising • i.e. one division from 1.5 to .75 FTE’s ($44k annual savings)

Leveraging to support other technologies• Currently support MS SQL Server & IBM DB2 (Census)• Researching Redgate software suite of tools for MS SQL Server

for increased productivity

04/10/23Statistics Canada • Statistique Canada16

Our DBA services

Support 11 divisions 100+ instances ORACLE, SQL-Server and DB2 Sun, HP-UX, Linux, Windows and soon AIX

Census of Agriculture Business Register Census of Population Dissemination Geography Health

International Trade System Development Social Survey Methods Survey Operation Tax

04/10/23Statistics Canada • Statistique Canada17

Our DBA Services

Designed to be Flexible Full DBA service to emergency support only Work with client area DBA’s if required Flexible Service Level Agreements We do a complete analysis of the client environment We provide a cost estimate, typically at the start of the

fiscal year, but adjustments can be made • Resource estimation• Infrastructure cost

04/10/23Statistics Canada • Statistique Canada18

Client Analysis

An in depth study of each client in terms of the environment, the DBA duties being performed and the DBA’s• Servers – HW and SW environment• Resources – DBA’s• Recoverability• Installation of new software• Security • Availability• Performance• Problem Detection, Escalation, Resolution and Completion• System Documentation• Recommendations

04/10/23Statistics Canada • Statistique Canada19

Service Level Agreement

Minimum is 25 days for emergency support (plus $1,400 infrastructure)

Includes full documentation of the environment

Documentation means any DBA can fill in if necessary

Clients choose what they want us to do and what they want to do (if anything) on their own

04/10/23Statistics Canada • Statistique Canada20

Benefits for Agency and Divisions - 1

Efficiency• Full time DBA• Increased availability of database• Increased reliability of database

Cost Savings• Sharing DBAs• Sharing training• Mentoring• Research activities

04/10/23Statistics Canada • Statistique Canada21

Benefits for Agency and Divisions - 2

Retaining the talents• More use of the DBA skills• Less DBA turn-around

Best practices, methods and tools• Research on trends, issues, new technologies• Evaluate, use, recommend tools and methodologies• Operational procedures in database management• Best practices in client-oriented services

04/10/23Statistics Canada • Statistique Canada22

Benefits for DBA

Good shop for full time DBA Working on variety of projects, platform, technologies Training needed for the work Backup of peers Learning of peers Greater potential for career path

04/10/23Statistics Canada • Statistique Canada23

Questions ?

04/10/23Statistics Canada • Statistique Canada24

Contacts

Yves Leonard,Database Administration Services,Oracle Technology Center,System Development Division,R.H. Coats bldg, 13-J,Work: 613-951-3233,Email: yves.leonard@statcan.gc.ca,Website: http://oracle/