Application Design and the DBA

Application design is more than just writing efficient database requests in application programs. Of course, application design includes database concerns such as interfacing SQL with traditional programming languages and the type of SQL to use. But every aspect of the way the program is coded will affect the usability and effectiveness of the application. Furthermore, each application program must be designed to ensure the integrity of the data it modifies. And, of course, performance must be treated as a design issue.

Many organizations erroneously assume that the DBA should not be involved in application design. The common line of thought goes something like this: “DBAs manage the database software and programmers manage applications so I don’t really want DBAs mucking around with programs.” Maybe this would work in some perfect world where all programmers understood the DBMS well enough to program efficient database access – but not in this world.

The DBA must promote the concept of designing applications with an understanding of the database at the forefront. It is unacceptable to allow programmers to design and code applications without considering how the programs will perform as they interact with databases. The uninformed will approach database application development with no proactive performance engineering – the assumption being that any performance problems can be resolved after development by the DBA. But it may be impossible to tune an improperly designed application program without rewriting it. So why not write it correctly the first time? 

Designing a proper database application system is a complex and time-consuming task. The choices made during application design will impact the usefulness of the final, delivered application. Indeed, an improperly designed and coded application may need to be redesigned and re-coded from scratch if it is inefficient, ineffective, or not easy to use. In order to properly design an application that relies on databases for persistent data storage the system designer at a minimum will need to understand the following issues:

• How data is stored in a relational database
• How to code SQL statements to access and modify data in the database
• How SQL differs from traditional programming languages
• How to embed SQL statements into a host programming language
• How to optimize database access by changing SQL and indexes
• Programming methods to avoid potential database processing problems

In general, the developer must match the application development languages and tools to the physical database design and the functionality of the DBMS being used. The first thing to be mastered, though, must be a sound understanding of SQL, the lingua franca of the relational database world. SQL is different than most high-level programming languages.

SQL is designed such that programmers specify what data is needed – not how to retrieve it. That is, SQL is coded without embedded data-navigational instructions. The DBMS analyzes each SQL statement and formulates data-navigational instructions “behind the scenes.” The DBMS understands the state of the data it stores, and so it can produce efficient and dynamic access paths to the data. The result is that SQL, used properly, provides a quicker application development and prototyping environment than is available with corresponding high-level languages. Furthermore, the DBMS can change access paths for SQL queries as the data characteristics and access patterns change, all without requiring the actual SQL to be changed in any way.

Most programmers are accustomed to hard-wiring data navigation instructions into their programs. Making the switch to SQL can be troublesome without some training. At any rate, programmers will need to be trained in these high-level differences between non-database and database programming techniques. This job falls to the DBA. Of course, there are many more details at a lower level that the database programmer needs to know, such as SQL syntax, debugging and testing methods, optimization techniques, and program preparation procedures (e.g. compilation, bind, etc.).

Additionally, application programs require an interface for issuing SQL to access or modify data. The interface is used to embed SQL statements in a host programming language (such as COBOL, Java, C, or Visual Basic). Standard interfaces enable application programs to access databases using SQL. There are several popular standard interfaces or APIs (Application Programming Interfaces), for database programming including ODBC, JDBC, and SQLJ.

Of course, the DBA will need to clearly describe the database interfaces to that are available for the programming languages in use in their organization. And the DBA must provide proper guidance and training such that the correct interface is used. Of course, that means that the DBA must have a solid background in — and knowledge of — the various programming languages being used within their shop and the ways in which they interface with the DBMS.

Performance can vary greatly for different interfaces and versions due to issues such as dynamic versus static SQL support, optimization of the interface, and the use of proper compilation and binding parameters and techniques.

Summary

Of course, these are just a few of the issues where the DBA can help to improve application design. Others include publishing up-to-date SQL coding techniques and tips, ensuring proper transaction coding including database commit frequency, interaction with TP systems and middleware, and coding to minimize locking problems. Application design and development is the core responsibility of systems analysts and application programmers, but the DBA must be involved in the process when programs are being written to access databases.

To Achieve Success DBAs Must Diversify

 

A good DBA is a Jack-of-All-Trades. You can’t just master one thing and be successful in this day-and-age. A day in the life of a DBA is usually quite hectic. The DBA maintains production and test environments, monitors active application development projects, attends strategy and design meetings, selects and evaluates new products, and connects legacy systems to the Web. And, of course: Joe in Accounting, he just resubmitted that query from hell that’s bringing the system to a halt. Can you do something about that? All of this can occur within a single workday.

To add to the chaos, DBAs are expected to know everything about everything. From technical and business jargon to the latest management and technology fads, the DBA is expected to be “in the know.” And do not expect any private time: A DBA must be prepared for interruptions at any time to answer any type of question — and not necessarily just about databases, either.

When application problems occur, the database environment is frequently the first thing blamed. The database is “guilty until proven innocent.” A DBA is rarely approached with a question like “I’ve got some really bad SQL here. Can you help me fix it?” Instead, the DBA is forced to investigate problems where the underlying assumption is that the DBMS or perhaps the DBA is at fault, when the most common cause of relational performance problems is inefficiently coded applications.

Oftentimes the DBA is forced to prove that the database is not the source of the problem. The DBA must know enough about all aspects of IT to track down errors and exonerate the DBMS and database structures he has designed. So he must be an expert in database technology, but also have semi-expert knowledge of the IT components with which the DBMS interacts: application programming languages, operating systems, network protocols and products, transaction processors, every type of computer hardware imaginable, and more. The need to understand such diverse elements makes the DBA a very valuable resource. It also makes the job interesting and challenging.

Jack-of-All-Trades

Getting to the heart of the matter, a DBA has to diversify. Why?

Databases are at the center of modern applications. If the DBMS fails, applications fail, and if applications fail, business can come to a halt. And if business comes to a halt often enough, the entire business can fail. Database administration is therefore critical to the ongoing success of modern business.

Databases interact with almost every component of the IT infrastructure. The IT infrastructure of today comprises many tools:

• Programming languages and environments such as COBOL, C#/C/C++, Java, etc.
• Database and process design tools such as ERwin, Rational, and ER Studio
• Transaction processing systems such as CICS and Tuxedo
• Message queueing software such as MQSeries and MSMQ
• Networking software and protocols such as SNA, VTAM, TCP/IP, and Novell
• Networking hardware such as bridges, routers, hubs, and cabling
• Multiple operating systems such as Windows, z/OS, UNIX, Linux, and perhaps others
• Data storage hardware and software such as enterprise storage servers, Microsoft SMS, IBM DFHSM, storage area networks (SANs), and NAS
• Operating system security packages such as RACF, ACF2, and Kerberos
• Other types of storage hardware such as tape machines, silos, and solid state storage
• Non-DBMS data set and file storage techniques such as VSAM and B-tree
• Database administration tools
• Systems management tools and frameworks
• Operational control software such as batch scheduling software and job-entry subsystems
• Software distribution solutions for implementing new versions of system software across the network
• Internet and Web-enabled databases and applications
• Client/server development techniques such as multitier, fat server/thin client, thin server/fat client
• Object-oriented and component-based development technologies and techniques
• Big Data technologies such as Hadoop, HBase, Hive, and so on
• Analytics, BI and querying solutions
• Portable devices such as smart phones and tablets

Although it is impossible to become an expert in all of these technologies, the DBA should have some knowledge of each of these areas and how they interrelate. Even more importantly, the DBA should have the phone numbers of experts to contact in case any of the associated software and hardware causes database access or performance problems.

So, being a DBA is sorta like structuring a well-invested portfolio: to be successful at either, you will need to diversify!

Data Professionals: Invest in Yourself!

So, are you worth investing in or not?

Most every IT professional continually looks for their company to invest money in on-going education. Who among us does not want to learn something new – on company time – and with the company’s money? Unless you are self-employed, that is!

Yes, your company should invest some funds to train you on new technology and new capabilities – especially if it is asking you to do new things. And since technology changes so fast, most everyone has to learn something new at some point every year. But the entire burden of learning should not be placed on your company!

Budget some of your own money to invest in your education, which is also an investment in your career. After all, you probably won’t be working for the same company your entire career. Why should your company be forced to bankroll your entire ongoing education? Now, I know, a lot depends on your particular circumstances. Sometimes we accept a lower salary than we think we are worth because of the “perks” that are offered. And one of those perks can be training. If that was the promise upon being hired it makes absolute sense to hold the company to its end of the bargain… but times change and companies change, and that means that sometimes your perks can disappear.

Now some folks simply abhor spending any of their hard-earned dollars to help advance their careers. Shelling out a couple of bucks to buy some new books, subscribe to a publication, or join a professional organization shouldn’t be out of the reach of most folks in IT, though.

A willingness to spend some money to stay abreast of technology is a trait that should apply to DBAs. Most DBAs I’ve known are insatiably curious and many are willing to invest some of their money to learn something new. Maybe they bought that book on XML or NoSQL before anyone at their company started using it. Perhaps it is just that enviable bookshelf full of useful SQL and database books in their cubicle. Or maybe they paid that nominal fee to subscribe to the members-only content of that SQL portal. They could even have forked over the $10 fee to attend the local user group.

Don’t get me wrong. I’m not saying that companies should not reimburse for such expenses. They should – it provides for better-rounded, more educated, and more useful employees. But if your employer won’t pay for something that you think will help your career, then why not just buy it yourself?

And be sure to keep a record of such purchases because unreimbursed business expenses can be tax-deductible.

The Business-Savvy DBA?

Heads-down DBAs who know technology, but not their company’s business, soon will be on the endangered species list. Be Business Savvy

Although DBAs are technologists first and foremost, we need to be ever cognizant of the business reasons that our beloved technologies support. Yes, DBAs like to immerse themselves in the bits and bytes of technical solutions and learn all there is to know about the software we use. And this is okay–up to a point. As long as we take care not to blind ourselves to the business reasons for the software and hardware they love so much.

To keep the business “top of mind,” the DBA’s tools and utilities need to be tied to business strategies and initiatives. In this way, the DBA’s work becomes integrated with the goals and operations of the organization.

The first step in achieving this needed synergy is the integration of DBA services with the other core components of the IT infrastructure. Of course, the DBA should be able to monitor and control the databases under his purview, but he should also be able to monitor them within the context of the broader spectrum of the IT infrastructure–including systems, applications, storage and networks. Only then can companies begin to tie service-level agreements to business needs, rather than technology metrics.

To fulfill the promise of business/IT integration, it will be necessary to link business services to the underlying technology. For example, a technician should be able to immediately comprehend that a service outage to transaction X7R2 in the PRD2 CICS region means that regional demand deposit customers cannot access their accounts. See the difference?

Focusing on transactions, TP monitors and databases is the core of the DBA’s job. But servicing customers is the reason the DBA builds those databases and manages those transactions. Technicians with an understanding of the business impact of technology decisions will do a better job of servicing the business strategy. This is even more true for the DBA’s manager. Technology managers who speak in business terms are more valuable to their company.

Of course, the devil is in the details. A key component to realizing effective business/IT integration for DBAs is the ability to link specific pieces of technology to specific business services. This requires a service impact management capability–that is, analyzing the technology required to power each critical business service and documenting the link. Technologies exist to automate some of this through event automation and service modeling. Such capabilities help to transform availability and performance data into detailed knowledge about the status of business services and service level agreements.

Today’s modern corporation needs technicians who are cognizant of the business impact of their management decisions. As such, DBAs need to get busy transforming themselves to become more business-savvy–that is, to keep an eye on the business impact of the technology under their span of control.

The Proper Approach: Developing Systems Based on Models

A big challenge facing organizations today is ensuring the appropriate information reaches the right people so that data is used appropriately and accurately. Information is the key to business success and its proper care and treatment should be of the utmost importance. But relatively few organizations truly treat data as the corporate asset that it truly is.

Data architects can design the best possible data model, but that does not mean that developers will use the information in the way that it was intended, or that business analysts will immediately understand the relationships between different data elements.

An integrated solution for modeling can help to reduce this problem by streamlining information exchange and contextualizing the data model to minimize errors. By using models as the common language for all data stakeholders – whether they are application developers, BI professionals, DBAs, or business users – organizations can effectively translate otherwise disparate requirements in context of one another.

For example, a data architect passing a logical data model to a development DBA needs to make sure that the design is properly implemented in the database to assure the business’s rules are enforced. With an integrated approach, models – and the underlying metadata – can be accurately passed across functional boundaries.

The models for the data architect and the DBA should remain similar in look and feel, and utilize the same types of notation. Deploying modeling solutions with tight integration across functional areas can reduce the amount of time, effort, and error involved in sharing information. And sharing at the model level simplifies potentially complex processes because models can more readily be consumed and understood by all required parties.

Of course, we should clarify what we mean by models. A model is not just a diagram with boxes and arrows. Yes, a data model must include the visual representation of the data (such as an entity/relationship diagram), but it must also be backed up by metadata. A model without metadata might as well just be a quick picture scrawled on a napkin somewhere. It is the powerful combination of a clear diagram and the metadata definitions for the elements in the diagram that make a model useful.

Facilitating Data Integration

Among other objectives, a good data modeling solution can help you to reverse engineer existing schemas into models and forward engineer models into new target schemas. And if you think about it, this is basically what you do during an ETL process: you reverse engineer an existing data source (that is, you extract the data along with the accompanying metadata, and it is the latter that is described in the schema), transform that data, and then forward engineer the target data.

For this reason, it can make sense to utilize models as a mechanism to facilitate data movement and data warehouse population. A model-driven approach to data integration can enable data architects and BI professionals to import data models and the associated metadata for use during ETL.

This visual approach to data integration helps to simplify the process while ensuring the complete and accurate migration and integration of data between systems.

Simplifying Application Development

And models are not just useful for database development, but also to guide application development. The comprehensive data model can be integrated into a process model to streamline the development process. With knowledge of, and access to the metadata, developers are better prepared to build the correct processes the first time around.

Furthermore, application modeling solutions used by development teams enable clear application models to be created that can then be translated into code. Model-based application development enables the deployment of higher quality applications in less time. And with the appropriate tools, you can then generate code directly from models into popular application development technologies (e.g. Java, C, VB, etc.).

Enhancing Data Integrity Across the Enterprise

Creating data models and collecting metadata that spans the breadth of an organization can deliver significant benefits such as better decision support and business information, or richer customer information and support. However, data management professionals know that these benefits are only realized when the information is accessible and accurate. Using integrated modeling solutions to implement model-driven data management and development can help organizations to share knowledge effectively and with minimum effort. And it will certainly produce higher quality applications and data, too.

The Bottom Line

A logical data model should be used as the blueprint for designing and creating physical databases. Modeling is a powerful that can be integrated into your IT infrastructure thereby helping. to exploit the synergy that exists within your databases, IT activities and application systems.

Inside the Data Reading Room

With today’s post I am beginning a recurring series on the books that I am reading and using as a data professional. The goal of these posts is to introduce you to new and classic books on data technology and database systems. Along the way, I will also touch upon other books that fall outside this realm, but might interest you anyway.

That said, the first book off the shelf in the Data Reading Room today is the third, and latest edition of the classic book Database Design for Mere Mortals by Michael J. Hernandez (ISBN 978-0-321-88449-7). Although there are quite a number of database design books on the market, this book by Hernandez is my current favorite. It does a very nice job of tackling the concepts and requirements of database design for the novice — and offering expert advice and organization for the more advanced reader.

The book consists of four broad parts, each one covering an essential component of the overall discipline of database design. This first part, Relational Database Design, kicks off with historical and introductory concepts and introduces the importance of a well thought out mehodology for designing databases.

The second part, The Design Process, digs into the actual process of  database design. The reader is introduced to key topics and terminology including concepts such as table structures, primary keys, relationships, views, and data integrity mechanisms.

The third part, Other Database Design Issues, is actually more interesting than its pedestrian title suggests. It clarifies and describes what bad database design looks like, how to avoid it, and when you might want to consider bending the rules (after all, every rule was designed to be broken, right?)

The fourth and final part and consists of Appendixes, including a summary of design guidelines, example forms, diagram symbols, checklists, and more.

If you are looking for a book to teach database design — or to be used as a research and reference guide — look no further…  Database Design for Mere Mortals by Michael J. Hernandez (published in 2013) is the book you should choose.

Another new book in the Data Reading Room this month is Ralph Hughes’ Agile Data Warehouse Project Management (ISBN: 978-0-12-396463-2). This book addresses the challenge of managing a data warehouse project and asserts that agile techniques can be used to successfully drive warehousing projects.

Data warehousing projects are different than other software development projects and, as such, should be approached using different development and management techniques and processes. The book is not another “what is data warehousing” book nor is it another “agile development” book. Instead, it marries the two together and shows the reader how data warehousing can succeed using agile project management techniques (such as Scrum).

The author explains how a project team using Scrum can help to achieve a quicker time to delivery and reduced costs — the holy grail of project management. Anyone who has worked on a data warehousing project knows that it can be a monumental undertaking. Agile Data Warehouse Project Management by Ralph Hughes (published in 2013) offers up an approach that can minimize challenges and improve the chance of successful delivery. And that is worth reading if you are currently, or about to start working on a warehouse project don’t you think?

The next book off the shelves in the Data Reading Room is Turning Numbers Into Knowledge 2nd ed by Jonathan G. Koomey Ph.D. Though not technically a book about data, this charming and easy-to-read book offers up problem-solving techniques that will benefit every data professional.

Such a book is desperately needed because it clarifies what it means to be literate regarding data and information. The author does a fine job of describing why the book is important in the “Why You Should Read This Book” blurb near the front of the book. In it, he states “Mastering the art of problem solving takes more than proficiency with basic calculations: it requires (among other things) understanding how people use information,recognizing the importance of ideology, learning the art of story telling and acknowledging the important distinction of facts and values.”

The book contains many tips and tricks for solving problems in the real world. It will improve your ability to judge the assertions of others and to develop reasoned assessments based upon your own analysis of information.

Indeed, Koomey’s Turning Numbers Into Knowledge (published in 2008) is well worth the time to seek out and read.

Finally, if you are a fan of Star Trek (The Original Series) you will be hard-pressed to find a book more fun than Star Trek: The Original Series 365 by Paula M. Block with Terry J. Erdmann.

The odd-sized book offers up comprehensive coverage of the entire series as it dissects each episode. The photos are stunning, the commentary is interesting and the presentation is beautiful. Even long-time fans of the show may learn new things from the behind-the-scenes stories and writer interviews that are collected in this tome.

And that concludes our first trip to the Data Reading Room. Hopefully you found the trip interesting and perhaps you will check out one or more of the books we’ve examined… Until next time, happy reading and high quality data to all!

How Secure Are Your Databases?

Increasingly, security issues are on everyone’s mind these days. We are constantly bombarded with images on the daily news that underscore just how important security is: homeland security, personal security, security in general. Every time you open your e-mail in box vigilance is required to avoid stumbling into some virus trap in one of those hundreds of spam messages. And governmental regulations on corporate governance require more diligent security mechanisms.

With all of the above in mind then, how would you answer the question posed by this column’s title: how secure are your databases? Really take some time to think about it before answering though. I’m not just talking about the basic DBMS-based granting and revoking of privileges.

First of all, do you even know what is being done to your data and who is doing it? Being able to answer this question requires the implementation of auditing and database usage reporting. Typically, audit reports show things like a history of changes made to database privileges, changes to database structures, changes to data, and access to data. To be useful, the report should minimally show the database, table, user, type of change/access, and timestamp. Even more useful would be a before and after image of any change. Some DBMS products provide native capabilities for auditing data access and modification, but turning on these features can increase overhead thereby degrading database performance. Even so, in this day and age you may need to bite the bullet and enable auditing. Of course, some third party products are available that mine this information from the database log files – an attractive option for avoiding the performance hit of audit traces.

Another consideration for database security is the ability to monitor and report on user behavior. Many studies have shown that internal users are a bigger threat to security than external hackers. So you better do your best to protect your databases from damage done by disgruntled employees or fired employees whose access has not been terminated. This is a challenge. One approach is to maintain a baseline of standard user behavior and report on anomalies. For example, consider a user whose regular pattern is to access tables A, G, T, and Z weekdays from 9 to 11:30, is logged off until 1:15, and then is back on the system again until 5:30 accessing the same tables, plus another table from 3:00 to 3:30. Wouldn’t it be interesting to be able to report on deviations from that norm? A report that showed the user was in on Saturday accessing table T would be useful if data corruption appears in table T on Monday. Similarly, it would be useful to note if the user did not access the 3:00 table as he normally does.

And how prepared are you for vulnerability management? In other words, are you sure that your environment is perfectly set up and secured? Periodic scanning for potential vulnerabilities is a prudent approach to take. For example, a regularly scheduled report showing DBMS patches not applied, default passwords that were not changed, and the like can help to avoid potential security violations. To be successful, database vulnerability management should be approached like PC virus scans. A regularly updated database of vulnerabilities for each DBMS should be available and organizations should be able to register software that checks their systems against known problems. What DBA group wouldn’t want to subscribe to that service?

Of course, this post barely scrapes the surface of database security. We haven’t even talked about SQL injection attacks and integrating database security with operating system security. Indeed, there is a lot to think about – and a lot to do. But failing to properly secure your databases is just asking for trouble.

Removing Superfluous Spaces

We all can relate to dealing with systems that have data integrity problems. But some data integrity problems can be cleaned up using a touch of SQL. Consider the common data entry problem of extraneous spaces (or blanks) inserted into a name field. Not only is it annoying, sometimes it can cause the system to ignore relationships between data elements because the names do not match. For example, “CraigMullins” is not equivalent to “Craig Mullins”; the first one has two spaces between the first and last name whereas the second one only has one.

You can write an SQL UPDATE statement to clean up the problem, if you know how to use the REPLACE function. REPLACE does what it sounds like it would do: it reviews a source string and replaces all occurrences of a one string with another. For example, to replace all occurrences of Z with A in the string BZNZNZ you would code:

     REPLACE(‘BZNZNZ’,’Z’,’A’)

And the result would be BANANA. So, let’s create a SQL statement using REPLACE to get rid of any unwanted spaces in the NAME column of our EMPLOYEE table:

     UPDATE EMPLOYEE
            SET NAME = REPLACE(
                         REPLACE(
                           REPLACE(NAME, SPACE(1), '<>')
                           '><', SPACE(0))
                         '<>', SPACE(1));

Wait-a-minute, you might be saying. What are all of those left and right carats (less-than and greater-than signs) and why do I need them? Well, let’s go from the inside out.

The inside REPLACE statement takes the NAME column and converts every occurrence of a single space into a left/right carat. The next REPLACE (working outward), takes the string we just created, and removes every occurrence of a right/left carat combination by replacing it with a zero length string. The final REPLACE function takes that string and replaces any left/right carats with a single space. The reversal of the carats is the key to removing all spaces except one – remember, we want to retain a single space anywhere there was a single space as well as anywhere that had multiple spaces. Try it, it works.

Of course, you can use any two characters you like, but the left and right carat characters work well visually. Be sure that you do not choose to use characters that occur naturally in the string that you are acting upon.

Finally, the SPACE function was used for clarity. You could have used strings encased in single quotes, but the SPACE function is easier to read. It simply returns a string of spaces the length of which is specified as the integer argument. So, SPACE(12) would return a string of twelve spaces.

Super Bowl Teaches Us to Test Our Data Recovery Plans

We can be reminded of the importance of backup and data recovery plans any time… even while watching the Super Bowl. Even though Super Bowl XLVII occurred several weeks ago now I bet that you remember the lights going out…

Important data and systems need to have backup plans. Without such planning you can be assured that, one day, probably at the most inopportune time, your system will crash and you won’t be able to recover it. For this reason, careful users will be sure to create backup copies of all of their important data. Now this sounds like a simple task, but there is really much more to it than first meets the eye. A sound backup and recovery plan must be built on recovery time objectives. In other words, how rapidly must you be able to bring the systems back online in the event of a failure. If you watched Super Bowl XLVII you have to wonder whether anyone had considered their recovery time objectives.

You see, during the Super Bowl at the Super Dome in New Orleans, the lights went out in half the stadium just after the beginning of the second half of the game. At first glance, this would not seem to be a catastrophic failure. But it took more than half an hour for the lights to be turned back on and the game to resume. For an event of the magnitude of the Super Bowl, watched by millions, 30 minutes is a long time.

Companies pay a lot of money to advertise their products and services during the Super Bowl. According to Forbes Magazine the average cost of a 30-second ad in Super Bowl XLVII was $4 million dollars. But did advertisers get their money’s worth when ads were shown during the lighting problem? Well, it is hard to tell. Some of the people I was watching the game with left the room when the problem occurred. Most of these folks were people who normally watch the game more for the commercials than the game.

So what is my point, you may be asking? Well, the Super Dome crew and CBS probably had a backup plan for emergencies. But it could not have been very well thought out. When the lights went out the CBS announcer (Phil Simms) was cut off in mid-sentence and there was dead air for at least a minute before they cut to a commercial. Dead air is never a good thing in broadcast television. Which raises the question: if they had a backup plan, did they test it?

This is an important question, and it is one that you should ask yourself about your data recovery plans. Right now. When was the last time you tested your data recovery plans? Because, let’s face it, the backup portion is the easy part of backup and recovery, recovery is the hard part. If you don’t test your ability to recover, then you’ll be “testing” in production; that is, while the failure is in progress and everyone’s hair is on fire. And that is not ideal.

Managing Database Change

Although it is a cliché, it is also true that change is the only constant in today’s complex business environment. An ever-changing market causes businesses to continually adapt. Businesses need to strive to meet customer expectations that are constantly changing, while sustaining revenue growth and profitability at the same time. To keep pace, businesses must constantly change and enhance products and services to meet and exceed the offerings of competitors.

And the individuals that comprise each and every business usually find it difficult to deal with change. This is so because change means we need to take on additional roles and responsibilities which inevitably makes our job more difficult. Our comfortable little status quo no longer exists. So we have to change, too – either change aspects of our environment or our way of doing things.

There are many different aspects of managing change, particularly with respect to Information Technology. Each of the following are different facets of the “change management” experience.

• The physical environment or work place changes to accommodate more employees, fewer employees, or perhaps just different employees with new and different skill sets.
• The organization changes where “things” like processes or methodology have to adapt to facilitate a quicker pace for product and service delivery.
• The network infrastructures changes to provide support for a growing, and perhaps geographically dispersed workforce.
• Applications and systems change to do different things with existing data or to include more or different types of data.
• The type and structure of data changes requiring modifications to the underlying database schemata to accommodate the new data.

Change is inevitable but necessary for business survival and success. So we better have solutions that enable us to better manage these inevitable changes.

Changing the Database  

Most of today’s business information is managed within the context of a complex, computerized business application. Almost every type of information you can think of — from customer information to vendor information to product specifications to payroll information — is managed by computer. And, this information is dynamic, it is always changing. Businesses must prepare to handle the challenge of managing and controlling their constantly changing information. To complicate matters, though, most businesses are further challenged by having a limited number of resources with which to tackle this growing mountain of information.

A DBMS is used to store the majority of today’s computerized data. So when that data changes, the databases used to store the data must also change. If the data is not reliable and available, the system does not serve the business, but instead, threatens the health of the business. So we need infallible techniques to manage database changes. But even more, we need techniques that are not just failproof, but are also automated, efficient, and easy to use.

Change Management Requirements

To successfully implement effective change management it is imperative to understand a set of basic requirements. The following factors needs to be incorporated into your change management discipline in order to ensure success: proactivity, intelligence, analyses (planning and impact), automation, standardization, reliability, predictability, and quick and efficient delivery.

Pro activity. Proactive change, which can eliminate future problems, is an organization’s most valuable type of change. The earlier in the development cycle that required changes can be identified and implemented, the lower the overall cost of the change will be.

Intelligence. When implementing a change, every aspect of the change needs to be examined because it could result in an unanticipated cost to the company. The impact of each change must be examined and incorporated into the change process. Because a simple change in one area may cause a complex change in another area. Intelligence in the change management process often requires a thorough analysis including an efficient and low-risk implementation plan. True intelligence also requires the development of a contingency plan should the change or set of changes not perform as projected.

Planning analysis. Planning maximizes the effectiveness of change. A well-planned change saves time. It is always easier to do it right the first time than to do it again after the first introduced change proved less than effective. An effective organization will have a thorough understanding of the impact of each change before allocating resources to implementing the change.

Impact analysis. Comprehensive impact and risk analysis allows the organization to look at the entire problem and examine the involved risk to determine the best course of action. A single change usually can be accomplished in many different ways. But the impact of each type of change may be considerably different. Some carry more risks: the risk of failure, the risk associated with a more difficult change, the risk of additional change being required, the risk of causing downtime, etc. All considerations are important when determining the best approach to implementing change.

Automation. With limited resources and a growing workload, automating a process serves to reduce the human-error factor and to eliminate more menial tasks from over burdened staff.

Standardization of procedure. Attrition, job promotions and job changes require organizations to standardize processes to meet continued productivity levels. An organized and thoroughly documented approach to completing a task reduces the learning curve, as well as training time.

Reliable and predictable process. When creating any deliverable, a business needs to know that all of the invested effort is not wasted. Because time is valuable, a high level of predictability will help to ensure continued success and profitability. Reliability and predictability are key factors in repeatedly producing a quality product.

Availability. Most changes require down time to implement the change. To change an application the application must come down. The same is true of databases. But high availability is required of most applications these days, especially for e-businesses. Reducing the amount of downtime required to make a change will increase application availability. And this is fast becoming a requirement in the Internet age.

Quick and efficient delivery. With most products and services there is a consumer demand for quick turnaround. Profitability is at its best when a product is first-to-market. Conversely, the cost of slow or inefficient delivery of products can be enormous. In the case of implementing change, faster is better.

The Change Management Perspective of a DBA

The DBA is the custodian of database changes. Usually, the DBA is not the one to request a change; that is typically done by the application owner or business user.  But there are times, too, when the DBA will request changes, for example, to address performance reasons or to utilize new features or technologies. At any rate, regardless of who requests the change, the DBA is charged with carrying out the database changes.

To effectively make those changes, the DBA needs to consider each of the items discussed in the previous section: proactivity, intelligence, analyses (planning and impact), automation, standardization, reliability, predictability, and quick and efficient delivery. Without a robust, time-tested product that is designed to effect database changes, the DBA will encounter a very difficult job. Why?

Well, today’s major RDBMS products do not support fast and efficient database structure changes. Each RDBMS provides differing levels of support for making changes to its databases. But none easily supports every type of change that might be required. One quick example: most RDBMSs today do not enable a column to be added to the middle of an existing row. To do so, the table must be dropped and recreated with the new column in the middle. But what about the data? When the table is dropped the data is deleted, unless the DBA was wise enough to first unload the data. But what about the indexes on the table? Well, they were dropped when the table was dropped, so unless the DBA knows this and recreated the indexes too, performance will suffer. The same is true for database security – when the table was dropped all security for the table was also dropped. And this is but one simple example. Other types of database change that are required from time to time include:

• changing the name of a database, table, view, or column
• modifying a stored procedure, trigger, or user-defined function
• changing or adding relationships using referential integrity features
• changing or adding database partitioning.
• moving a table from one database, dbspace, or tablespace to another.
• rearranging the order of column in a table.
• changing a column’s data type or length.
• adding or removing columns from a table.
• changing the primary key without dropping and adding the primary key.
• adding or removing columns from a view.
• changing the SELECT statement on which a view is based.
• changing the columns used for indexing.
• changing the uniqueness specification of an index.
• clustering the table data by a different index.
• changing the order of an index (ascending or descending).

And this is an incomplete listing. Adding to the dilemma is the fact that most organizations have at least two, and sometime more, copies of each database. At the very least, a test and production version will exist. But there may be multiple testing environments for example, to support simultaneous development, quality assurance, unit testing, and integration testing. And each database change will need to be made to each of these copies, as well as, eventually, the production copy. So, you can see how database change quickly can monopolize a DBA’s time.

The solution is to use an automated product to implement database change. The product will enable the DBA to focus on all of the issues of change management because it is built to understand not just the discipline of change management, but also the RDBMS in which the changes are to be made. This built in intelligence shifts the burden of ensuring that a change to a database object does not cause other implicit changes from the DBA to the tool. And once the change has been identified and implemented for one system, it can easily be deployed on other database copies with minimal, or perhaps no changes.

Another feature of a database change manager product is analysis and planning. The impact of changes can be examined prior to implementing any change. This is an invaluable resource for ensuring safe and efficient database changes. This type of tool also uses automation to minimize the resources required to implement database change. Instead of writing a new, complex change script from scratch for each database change, the DBA can rely on the database change management tool to accomplish this. And application and database availability will be enhanced because the product will implement the change in the least intrusive, quickest manner possible.

All in all, a database change management product will improve availability, minimize errors, and speed up your time to market. And I think we all can relate to that!