DBA secrets to stress reduction

The current DBA trend is increased database management requirements and less DBAs to manage. 

 Many factors are responsible , such as :

1)      Global trends and predictions for increased data requirements

2)       Automation

3)       Economic pressure to decrease size of IT departments

You can see why for many DBAs intense stress and a complete lack of time management is a way of life.   

These are top 9 practises I follow in managing  large database environments.

 

1)       Inventory management system. The first DBA task is to create an  database inventory management system. The objective is to list all database server instances in the environment , under your responsibility.

It could be a simple Excel spreadsheet listing out all database servers under your responsibility. The sort of columns I include are: Server name, instance, directory structure, version, collation, application, application owner etc.  A combination of server and business information

A list of database servers under your management is a clear way of communicating your responsibilities and introducing some control into your workload.

 

2)       Disaster Recovery Plan.  It doesn’t matter how performant your databases are, if you can’t recover from a disaster. Organise Service Level Agreements (SLA) , Recovery Time Objectives (RTO) and Recovery Point objectives (RPO).  These agreements assist in designing an architecture to support the  organisational requirements.

Can you deal with database corruption?

What is your contingency plan for a power outage?

How does the DBA fit into the business continuity plan?

Underpinning a solid Disaster recover plan strategy is a backup strategy. Are you backing up databases according to recovery requirements ? Are you monitoring backup failures and proactively fixing problems? Are you regularly testing Restores?

 

3)       Standardization. Maintain fix packs, security , maintenance, data-log-temp file layout ,installation files\methods and start up accounts  as standard. Stick to the system as closely as possible. Different instances have different values such as memory , collation,OLE automation etc , script as much as possible and retain with the install files

 

4)       Source of information. Being a database expert of every aspect of the database server ecosystem is a big challenge. It’s an on-going process . To be an effective DBA , you need to know where to get information  , sometimes in a hurry.

I’ve found cultivating some habits , such as a) documenting problems and solutions b) reading the database server manuals  c) following blogs d) involved in forums   as useful. 

 

5)       IO performance. Three key pieces of information to document  a) The IO profile of the database server instance b)  Characteristics of the substorage , such as RAID levels, mirroring, snapshot capabilities  c) A checklist of configurables such as disk alignment – have they been implemented ? have they been checked?

 6)       Server Maintenance Plan. Regular maintenance can save you much stress on a Monday morning. A well thought through and carefully implemented maintenance plan  is key to  well performing database servers.  

Reindexing , statistics management, integrity checks are some key tasks

 

7)       Database server Security.  Create a Security Policy and distribute to the organisation.  Encourage application users to sign – off the document . It becomes the benchmark for any security requests

      Create scripts which lockdown the database server at installation. Apply regular     checks to ensure the security policy is maintained.  Typical security issue include developers with elevated rights and application users with system administrator rights!

 

8)       Develop relationships with other IT team . For a DBA this includes developers, Operations and Storage engineers. Each group has it’s own perspective. Educating yourself about these other disciplines and working proactively assists greatly. It will help in troubleshooting and offering useful feedback to these groups , rather than accusatory threats.

 

9)       Monitor Server Performance and Tuning.  I consider performance tuning one of the primary functions of a DBA. Maintaining good database server performance is largely about a) a good maintenance plan b) monitoring and fixing bottlenecks quickly. It’s important to baseline the server and create a IO profile.  

When a performance problem arises use the baseline to compare and gauge the performance issue severity.

 

Author: Jack Vamvas(http://www.dba-db2.com)

User Devices and Database Servers

“In 2013 PC install base will be 1.78 billion”

“Smartphone install base to be 1.32 billion in 2013”

“By 2013, 505 million enhanced phones with advanced browsers in use”

“Mobile phones will drive more web accesses than PCs from 2013”

“By 2013, mobile phones will overtake PCs as the most commonweb access device worldwide”

These quotes are part of a Gartner Top Predictions webinar.

 The consequences for Database servers are significant .

 What does this mean for DBAs ?

1)  More electronic transactions through mobile phones than the PC.  Applications will be redesigned for a thinner\faster client.

2)  More emphasis on database servers and universal caching , for data reads and commits.

3)  SQL Performance Tuning requirements will increase.  Why?

Regular change requests for :

a)  Security management

b)  Data volume and integration changes due to  new products\markets and compliance regulations

c)  Transaction volume changes – multiple sales channels

d)  Availability  (Disaster Recovery and High Availability) – 24 x7

e)  Server consolidation model – OLTP and real time analysis.

 

DB2 HADR and HADR_PEER_WINDOW settings

Setting HADR_PEER_WINDOW at zero ,  only blocks primary transactions for the time specified in HADR_TIMEOUT.   

If the HADR_PEER_WINDOW is set to greater than zero, then the Primary database waits until the connection is restored  or the HADR_PEER_WINDOW  period finishes.

 Connections between the Primary and Standby databases are temporarily broken . Reasons can be: a) network  b) OS issues c) DB2 problems.

Leaving the HADR_PEER_WINDOW at zero , is beneficial to maintain availability.   At zero, soon as the connection between Primary and Standby  closes , the Primary is no longer in Peer state. 

 Therefore ,

a)  For data consistency , go for a non-zero value

b)  For data availability , consider a zero value

Author: Jack Vamvas(http://www.dba-db2.com)

How to use large BLOB/CLOB in HADR

Currently working on a Webspere Business Monitor application integrating with DB2 9.5 on Linux. I've made a few observations.One that is interesting , particuarly as Webspere Business Monitor uses BLOBs\CLOBs, relates to HADR and BLOB\CLOB replication.

Database logs are used in HADR , for replicating the Primary database to the Standby database.The Primary database
will support non-logged operations , such as changes to the configuration, changes to the recovery history file and BLOBs\CLOBs .But the problem - is that large BLOBs\CLOBs , which cannot be logged - cannot be replicated.

Insert, update, delete operations performed on a table with CLOBs\BLOBs that are larger that 1 GB  cannot be logged therefore  not replicated to the standby server. The space is defined on the standby server , but CLOB\BLOB is not replicated.

If a CLOB\BLOB is smaller than 1 GB and is logged , than this will be replicated

This raises questions about : 1)if large BLOBs\CLOBs need to be maintained on the Standby Server - what procedure need to be created to maintain the Standby 2)In a failover situation , would the BLOBs\CLOBS be necessary to maintain the service. Consideration needs to be given to your backup procedure, servcie levels etc  3)Is HADR the best way to maintain High Availability - there are alternatives such as Shared Disk Clustering - with Tivoli Storage Automation(TSA)

 

 Source:Jack Vamvas(http://www.dba-db2.com)

Tivoli Storage Automation for Multiplatforms(SA MP) and the shared disk approach

SA MP is defined as a highly available cluster – that uses various  monitoring methods to detect availability and rules to define what actions to complete in order to correct a failure.

 SA MP can be implemented in two ways:

1)Shared disk approach

2)SA MP with Highly available disaster recovery – (HADR) (shared nothing approach)

 In the shared disk approach ,the databases will be located on a shared disk. The disk is shared by two servers. If failure occurs the other node will assign the virtual IP to the network adapter , mount the shared disk and start the DB2 instance and database

 The SA MP with HADR approach – requires two servers : Primary and Standby. The replication occurs between the Primary and Standby servers and maintains synchronisation.

 Below is a diagram of the Shared Disk approach . The main features include: Shared disk , no replication, Virtual host name \Virtual IP address.

 

For this solution the requirements are:

Two separate but identical servers for the database(s). To support database manager configuration and database registry transfer

One shared disk which either database server can mount. No synchronisation is required since the disk is mounted one server at a time

Virtual hostname and Virtual IP address

 

The clustering management software will monitor the heartbeat signal and implement any corrections.The DB2 shared disk approach maintains the database redundancy.The IP address takeover will be transparent to the users , who will continue to use the same address .

 In the next blog I’ll go into the TSA with HADR approach , and the advantages\disadvantages of either solution

 Source:Jack Vamvas(http://www.dba-db2.com)