Americas Headquarters EMEA Headquarters Asia-Pacific Headquarters
The High Performance DBA Series
Best Practices That Every Multi-Tasking
DBA Must Know
Embarcadero Technologies
July 2010
100 California Street, 12th Floor San Francisco, California 94111
York House 18 York Road Maidenhead, Berkshire SL6 1SF, United Kingdom L7. 313 La Trobe Street Melbourne VIC 3000 Australia
I
NTRODUCTION
Today’s database administrator has a broad set of responsibilities. Not only is he
responsible for the design, implementation, maintenance, and repair of his organization’s databases, often he is tasked with programming, analyzing and modeling data, and systems management. His role requires him to develop and design database strategies, monitor and improve database performance and capacity, and plan for future expansion requirementsi. To be qualified to perform these duties, many DBAs are now even required
to maintain certifications.
The demand for new data-intensive applications is driving data to be generated at
increasingly mind-bending speeds for most organizations. The current annual average rate of data growth is nearly 30%, and large, critical applications are growing at an annual rate of 52% (Figure 1). DBAs are often struggling to manage a terabyte of data or more, and to do so they must find ways to be more efficient and productive. Automation, for example, helps DBAs accomplish more in less time, and tools that can automate many of the manual tasks that a DBA must perform daily are essential in today’s data-intensive world.
Additionally, having a single view into multiple platforms helps to cut the time needed to toggle between consoles.
Figure 1: Current average data growth rate
This paper takes a look at a day in the life of a typical multi-tasking DBA working for an organization, and examines some of the tools and best practices he can use to be more productive and efficient in completing essential tasks.
A
DBA’
S
C
HALLENGES AND
R
ESPONSIBILITIES
According to Forrester Research, DBAs are challenged with delivering improved performance and availability while handling high data volume growth with increasingly fewer resources. Moreover, they must grapple constantly with shrinking budgets (Figure 2).
Figure 2. Top Data Management Challenges
To address these challenges, DBAs have numerous tasks and responsibilities, many of which must be tackled on a daily basis:
• Multiplatform database management. Today’s IT environments are complex and diverse. According to Forrester Research, 90% of organizations have more than one database type; and 79% of all DBAs manage more than one database type. Most companies use a combination of custom and COTS applications, meaning DBAs often manage both as well. Successful DBAs are efficient at supporting
heterogeneous environments. This includes different platforms—Oracle®, SQL
Server®, Sybase® and more—as well as multiple versions of platforms. A single
application may support different iterations on different versions of Oracle, for example, and the DBA must have the right tools to manage data assets from different platforms across different versions.
• Performance tuning. DBAs seldom write the code for which they are responsible. They must run code written by developers and often are responsible for fine-tuning it for optimum performance. To that end, they need visibility into the potential issues of production systems as well as access to the tools capable of finding and fixing them.
• Capacity management. DBAs need to plan for future potential growth that occurs with the introduction of corporate initiatives such as data center consolidation or
wide-scale virtualization. Databases need to be configured to handle new datasets seamlessly, without interrupting workflows.
• Security and compliance. DBAs are held responsible for the security of data assets and compliance to regulations imposed by governmental, organizational and industry mandates. This takes up a growing portion of a DBA’s time and requires consistent, traceable processes be in place at all times. For example, DBAs must document segregation of duties and track changes to code or database
configurations.
• Backup and disaster recovery (DR). Data is an organization’s most valuable asset. Having systems and processes in place to continuously backup data and be ready to initiate recovery processes in case of disaster is critical.
• Reverse engineering inherited data models. DBAs often inherit applications and need to understand and visualize the environment they are tasked to manage. Having effective data modeling and reverse engineering tools can speed up the process and significantly reduce risk when the architecture team wants to make changes to a production database.
As a result of his long list of responsibilities, a DBA begins his day early and hits the ground running. A typical morning involves scrolling through his smart phone over breakfast to see if any failovers occurred overnight. Then, during his commute, he might work to remediate any outstanding tickets or sort his priorities. He might also take a quick look at the database environment for potential issues. Most DBAs manage 100 to 200 Database servers, so a lot can go wrong in a single night. As soon as he arrives at work, he needs to make sure that all of the servers are performing at acceptable levels, resolve any issues, and then respond to any requests coming in from developers.
Once he handles any major issues, the DBA might do research on error code that continues to manifest in an environment and could pose a problem down the line. He probably performs a series of migrations between databases or to new database
environments, and may work to start up test environments. He may also need to work on longer term initiatives, such as Oracle migration, Windows® 7 testing, OS patch-level
testing, DR plans, data warehouse projects, or third-party application testing. Meanwhile, he is putting out performance fires throughout the day.
Without the right tools, a DBA’s job is exceedingly challenging. To juggle his multiple responsibilities and work efficiently, he needs to have tools on-hand that simplify or
automate routine tasks, provide visibility across heterogeneous environments—preferably from a single console—and enable him to move in and out of tasks quickly and smoothly. Such tools would also enable him to add value to his organization with data analysis and reporting features that have an impact on corporate-wide IT decisions such as security, change management, and capacity planning to help key stakeholders in the organization make informed business decisions.
Let’s examine some of the features and capabilities of the toolsets that should be in a DBA’s arsenal.
D
ATABASE
A
DMINISTRATION
Database administration is made up of numerous routine tasks that occur daily, such as administering objects, rolling out new code, migrating code, downloading data to or from a database, or monitoring an environment. An efficient database administration tool makes small work out of these routine tasks, thereby boosting the DBA’s productivity and enabling him to maximize availability, performance, and security across all major database environments. With the exception of a few mouse clicks, the tool should be able to
perform those tasks seamlessly with little intervention from the DBA. For example, a DBA should be able to consult a single screen, highlight a data object in question, click on a menu item, and view whatever it is he needs to act on that data. Comprehensive graphical editors and wizards along with the ability to administer all databases using a single,
adaptable solution saves time and eliminates, so DBAs can manage larger, more complex databases.
What to look for:
• Provides a single-view from single window
• Graphical editors and wizards that work across all the types of
databases managed
• SQL debugger and profilers that manage performance equally well across the database types
Tool takes care of knowing all the data types for this specific data source
H
ETEROGENEOUS
P
LATFORM
M
ANAGEMENT
Increasingly, organizations use multiple platforms to manage data assets and run business applications. It’s not uncommon to find Oracle, SQL and Sybase in a single datacenter. Most DBAs manage multiple versions of each of these platforms. Getting and staying up to speed on how to configure and manage these diverse environments is challenging, and moving between systems can make up much of a DBA’s day. Although most database software comes with its own management tools, a unified solution that automates the application of configuration standards and templates in the various platforms and can perform compliance checks will boost productivity. For example, using a template and a
heterogeneous, single-view tool, a busy DBA can check 50 to100 servers – of any mix of platforms - at a time to verify compliance. An alert is sent back automatically if something is out of compliance.
What to look for:
• Single view of all databases management
• One tool, one interface
Single view and managed of all database types from one product, one interface
P
ERFORMANCE
O
PTIMIZATION
DBAs are under increasing pressure to maintain the highest levels of database
performance, which is being made more complex to maintain due to more corporate mandates to virtualize and consolidate servers. Optimization tools must ensure that poor-performing code never reaches the production environment – where bad code is
significantly more costly to correct. DBAs should look for tools that automate data masking, or allow the creation of a subset of test data from the production environment, which enables him to take a new piece of code, fine-tune its performance in the database environment, and test it before it goes back into production. This process helps minimize downtime, while enabling the DBA to resolve performance issues. Other key features to look for are the ability to perform wait-time analysis, create performance profiles, drill down into the data by a specific point in time, and enter into a tuning session with a mouse click.
What to look for:
• Tune SQL across all types of databases
Embarcadero Technologies - 5 -
• Load test optimized SQL code to simulate production, before deploying
• Profile all database types from single interface
D
ATABASE
In complex datacenters, 24x7 performance and security monitoring is essential. Even when there are multiple platforms in use, performance across the entire database environment must remain consistent and traceable. For this reason, centralized monitoring that gives the DBA visibility into all platforms at once is desirable. Some organizations have network monitoring systems, but these fail to address more than high level system issues. The ability to access the root cause of a database problem and to monitor for specific database threshold events are key capabilities for DBAs, beyond the system level.
Ideally, the DBA is able to sort platforms by a performance health index and get at-a-glance information about the database environment as a whole. A central console can be used to identify where problems reside, while providing information about which platform is performing the best, and which ones are lagging. The ability to sort by platform, current state, alarms and thresholds, performance, and other parameters helps to organize the data in a way that makes it fast and easy for the DBA to scan and identify problems. He can then take a look at many servers at a time, even if they’re running different database platforms, and quickly understand and assess the health of the total environment.
Additionally, centralized monitoring provides a consistent view across the environment, minimizing training needs. The DBA should be able to drill down into specific items to investigate issues further with a few clicks of his mouse. Then he can work with developers to refine the offensive script or dataset and fine-tune performance.
Typically, the best monitoring solutions are not only centralized, they’re agentless. This lets the DBA eliminate system agent management and administration and save even more time.
What to look for:
• Set custom health index performance indicators across all database types
• Agentless solution
• Single, unified repository of all database historical
performance data
Single enterprise dashboard view, with ability to drill in to each data source details
C
HANGE
M
ANAGEMENT
During migrations, updates and tuning, changes are often made to the database
environment. Tracking those changes is essential for compliance. Additionally, changes can introduce performance problems or more serious issues, and being able to compare configuration states can help identify what changes caused what problems. A quick glance at configuration settings of a database install or structure changes to objects like indices or procedures can reveal where things went wrong, and pinpoint what needs to be changed to restore the environment to its optimal state. A solution that provides this type of
snapshot visibility can save the DBA hours of tedious work and speed time to repair.
What to look for:
• Cross database schema compare and sync
• Compare live database to a schema or configuration snapshot
• Monitor configuration settings across database types
Manage change across multiple database types from a single interface
S
TREAMLINED
C
OMPLIANCE
A DBA must ensure that database environments comply with security standards, as well as governmental, industry, and organizational regulations and requirements. The DBA should be able to take a snapshot of existing configurations to create a template with the
necessary parameters to meet these requirements, and alert on performance degradation or potential security breaches. An efficient DBA tool will perform these functions as well as generate reports both automatically and on-demand to help with compliance audits and troubleshooting. He can also easily track changes for better change management and answer difficult questions in an automated fashion, rather than sorting through data manually—a slow, painstaking task that may provide less-than-accurate results.
What to look for:
Embarcadero Technologies - 8 -
• Define cross database configuration values and ranges to monitor for compliance
D
ATABASE
M
ODELING AND
R
EVERSE
E
NGINEERING
DBAs need automated tools for creating physical models that enable deep visualization of the database environment and ensure that scripts or changes introduced by the architect will run smoothly. While data architects use data modeling tools to maintain organizational data standards, such tools are key for DBAs to visualize application data models and to diagnose and understand the impact of changes more effectively. A good data-modeling tool will document source and target systems to ensure effective version control, and promote data reuse and collaboration with real-time metadata reporting and publishing features. If a new database is being created, DBAs can leverage modeling tools to ensure developers are adhering to the appropriate parameters and requirements of the given environment. Some tools enable DBAs to generate a new database model and perform a compare/merge against a live database to sync up any changes. Ultimately, they provide the DBA with confidence that new scripts or changes introduced in the database
environment for which he’s responsible will work the way they’re supposed to work.
What to look for:
Easily reverse engineer data models of new applications
Compare and consolidate data structures across database types
Run a comparison job to check for compliance in a multi platform environment
Wizard objects available specific to database platform
Top Considerations When Purchasing DBA Solutions
1. Does the toolset enable effective collaboration with developers and team members?
2. Does the toolset increase visibility into the data?
3. Does the solution support a heterogeneous environment including multiple platforms and versions of platforms?
4. Are there any hidden costs of software management and deployment?
5. Does the solution improve the performance and availability of your applications and databases?
6. Does the toolset improve personal performance and enable DBAs to add value to organization?
E
MBARCADERO
XE
D
ATABASE
T
OOLS
The Embarcadero XE family of Database tools provides DBAs with a range of solutions throughout their day, giving them a choice of tools in their arsenal to tackle any problem that comes their way. All major database platforms are supported in each tool from a single interface, including Oracle, IBM® DB2®, Sybase®, Microsoft® SQL Server®, InterBase®
SMP, and MySQL®.
Embarcadero XE Database Tools include:
• Rapid SQL® XE – Write quality SQL faster with a full-featured SQL IDE.
• DBArtisan® XE – The industry’s premier database administration tool.
• DB Change Manager XE – Simplify, automate and report on database changes.
• DB Optimizer XE – Prevent, find, monitor, and fix performance issues via visual SQL
profiling and tuning.
• ER/Studio® – A powerful modeling and architecture solution that combines process,
data, UML modeling, and reporting in a multi-level design environment.
All of Embarcadero’s XE products can be purchased as individual tools, or as part of Embarcadero® All-Access® XE. Both individual tools and All-Access XE include
Embarcadero® ToolCloud™, which enables centralized license management and tool
deployment while InstantOn™. InstantOn is a lightweight, zero-install client that lets DBAs run multiple versions of tools without conflicts and without the need for installation on local machines. DBAs and their teams can centrally provision tools, automatically receive updates, and access tools on demand inside the corporate firewall.
All-Access XE is a single, cost-effective license that provides access to all database tools from a single ToolBox, maximizing the DBA’s productivity while reducing administrative and licensing costs.
Embarcadero provides high-performance tools for DBAs to offer the following benefits: • Heterogeneous tools to manage all database platforms from single window. • Cross-organization scalability and collaboration.
• The ability to standardize on one tool for multiple platforms. • The ability to improve productivity and reduce tooling costs.
• The ability to control tool access and reduce tooling costs through a flexible, unified licensing model.
S
UMMARY
Although the demands on today’s database administrators are high, the right tools and database management solutions can help alleviate some of the pain points DBAs experience on a daily basis, giving them time to truly add value. Embarcadero provides the right solutions in flexible licensing and delivery options with instant access to the essential tools DBAs use every day. With the right tools in their toolbox, DBAs can significantly reduce the time and money spent on maintaining high-performing, reliable and secure database platforms for complex organizations, while becoming even more valuable assets to their organizations.
Embarcadero Technologies - 11 -
Embarcadero Technologies, Inc. is the leading provider of software tools that empower application developers and data management professionals to design, build, and run applications and databases more efficiently in heterogeneous IT environments. Over 90 of the Fortune 100 and an active community of more than three million users worldwide rely on Embarcadero’s award-winning products to optimize costs, streamline compliance, and accelerate development and innovation. Founded in 1993, Embarcadero is headquartered in San Francisco with offices located around the world. Embarcadero is online at
www.embarcadero.com.
