In this post, we discuss how you can streamline the database development process, reduce development costs, and enforce change management best practices, using Database Version Control practices
Using the Check-Out & Check-In of database resources feature of our innovative database version control solution, dbMaestro TeamWorkTM Management Studio, prevents code overrides and out-of-process changes;
Checking Out and Checking In
From your dbMaestro TeamWork Management Studio console, select the database you want to work with from the tree on the left. Enter your schema password if prompted to provide it.
All of the database objects will be displayed in the center of your console. Right-click the object you want to work on and select “Check Out Object Definition”. Make sure the object you want is selected in the pop-up dialog box, then click “OK”.
Now you can update this object using the Oracle Client IDE of your choice.
Next, go back to the dbMaestro TeamWork Management Studio console, right-click the object that was changed, and select “Check In Object Definition”. When the pop-up dialog box appears, type in information about the changes in the “Comments” pane and click “OK”.
Associating Changes to Change Sets
When you Check-In your changes, you can link the changes to tasks in the “Assign to Changeset” area at the bottom of the screen. Choose the appropriate task to link to from the drop-down menu, and click “OK”.
Later, this enables you to deploy the changes made for specific change-sets while ignoring other changes. For example, you can deploy changes that fix one or more bugs while skipping other changes that are not yet ready to deploy.
Analyzing Schema Structure Changes
To review changes to schema structures, right-click the database object in the center of your screen and select “View History”. When the pop-up dialog box appears, highlight the revisions you want to compare and click the “Compare Revisions” button on the right side of the screen.
A pop-up window will display the changes made in both the base schema and the compared schema. This pop-up window allows you to review and compare changes. You can change the versions being compared by selecting different values in the combo boxes at the top of the pop-up window.
Making Table Content Changes
To change table content, right-click the name of the appropriate table in the center of your console and select “Check Out Data”. When the pop-up dialog box appears, make sure you have chosen the correct table and click “OK”.
Next, update the table’s content using the Oracle client IDE of your choice.
To check in the changed table, return to the dbMaestro TeamWork Management Studio console, left-click the name of the changed table in the center of the screen, and select “Check In Data”. When the pop-up window appears, make sure you selected the right table, then click “OK”.
In order to review changes in the table content, right-click the name of that table again and select “View History”. When the pop up window appears, select the revisions you want to compare and click “Compare Revisions” on the right side of the screen. Another window will display the changes in both the baseline and the compared tables.
One of our clients recently implemented dbMaestro TeamWork for Oracle in a rather unusual way. Most clients start their TeamWork implementation in the development environment when developers begin documenting changes using Database Change Management methodologies. But this client started its TeamWork implementation in its production environment to solve a critical database access problem.
The access problem existed because the same Oracle password was being used in every environment. It is not unusual that the password for the Oracle user is the same in the development, Integration, QA, and pre-production environments and sometimes even in the production environment. As a result, without Teamwork, changes to database objects could be made by any developer or DBA at any time without leaving an audit trail that indicated who did what when.
With TeamWork for Oracle the administrator can give developers access to change packages, procedures, and functions only in the development and QA environments while allowing them to review history in the production environment. This client assigned a role (developer, QA tester, release manager, DBA or read-only) to every person who could access the database for any purpose. Only people who were authorized to check-out objects in the production database could do so via TeamWork for Oracle. Everyone else could no longer do this.
Because the TeamWork security mechanism is integrated with the corporate active directory, this client achieved immediate control over access to the database in its production environment. For more information about database security, see Database Security.
Because TeamWork for Oracle’s change policy enforcement is integrated with the database engine, changed DDL files could not be executed in Oracle until they had been checked in via the TeamWork application. Only people who are authorized to check-in database objects can do so via TeamWork, so no one else could change the production database objects. In addition, only files that implemented changes associated with the work-plan for a release could be checked in to the version for that release.
When Teamwork for Oracle is implemented in the development environment, another source of human error is eliminated: manually produced deployment scripts. It is important to have a single deployment script for structure and meta-data. Meta-data is an essential part of the application logic. Deploying code changes without meta-data can cause an application to crash. Deploying structure changes to a meta-data table without the meta-data changes can produce incorrect values in the meta-data tables.
TeamWork for Oracle automatically generates a single deployment script that contains the structure and meta-data changes associated with an upgrade. This automatically generated script eliminates human errors in deployment scripts that are discovered only when the upgraded application crashes in the production environment.
In addition to gaining control over changes to the production database, this client now has an accurate up-to-date audit trail for every change made to its production database objects. This audit trail enables the client to determine what database changes were made after the people who made them have forgotten the details or even left the company. It also enables the client to satisfy the stringent accountability requirements of outside auditors. For more information about how TeamWork helps you comply with audit requirements, see Auditing.
Compliance audit – a veritable nightmare. No one loves an audit; no one wants to be in the hot seat. But compliance is a major issue, and it continues to be one of the topmost priorities for IT organizations. And there is no end in sight for the foreseeable future.
Hopefully, you will not face an audit. But if and when you do, you had better be able to account for your data, including what changes were made to the database, when and by whom they were made, and for what reason.
Given the fact that SCM solutions do not track, or provide audit trails for database changes, this can be a real problem. Just where will the needed details come from? And audit is not the time that you want to rely completely on memory to explain database changes.
The solution? dbMaestro TeamWork for Oracle™.
TeamWork enables IT organizations to meet their compliance requirements for their Oracle databases, including COBIT (Control Objectives for Information and related Technology) and Gramm-Leach-Bliley Act (GLBA) compliance requirements.
Does the auditor want to know what changes were made to the database? TeamWork’s database version control and auditing saves and tracks all database change versions.
Does the auditor want to know the reason for the change and/or who made the changes? TeamWork integrates with major software configuration management products, such as IBM Rational Team Concert® and Microsoft Team Foundation Server®. Through this integration, changes to an application’s database can be linked to a work item or change set, which identify the reason for the change and the person responsible.
TeamWork provides you access to a reliable audit trail for the managed database object definitions as well as the ability to manage content. In fact, using proven change management methodologies at the database level, TeamWork becomes an integral part of the development process.
It enforces compliance with change policy through all stages of the development process, from development through testing to production:
* Changes to database objects must be documented and approved
* The audit trail of object changes must be reviewed
* Database changes made in development must be deployed to testing and production
So before you face a compliance audit, implement dbMaestro TeamWork for Oracle™. And then relax. And breeze through the audit.
When application development and testing are completed, and it is finally time to prepare the release, the biggest challenge might not be preparing the application’s release. It might just be preparing the database deployment.
Preparing the database deployment is time consuming work. You must prepare the database deployment script, which includes sections for schema deployment and data deployment. And this is no small task; rather, it is a tremendous challenge.
Database changes are by far different than application code changes. With application code, the Software Change Management tool forces check-in and check-out modules and tracks changes and versions. If the code changes were made correctly and the testing was precise and thorough, then with the help of the SCM, the process of putting together the release flows.
But database deployment is huge challenge because database changes are generally not managed, nor are they documented, and there is generally no simple way to determine all the objects that were changed.
So in the end, the DBA or Team Leader who works on this task has the unenviable task of trying to secure the list of changed objects. And once that list of changed objects is secured (assuming it is correct and comprehensive), the DBA / Team Leader must now generate a database deployment script for all the changed objects. And each object can have different change command, based on its status.
And as if that wasn’t enough, this task is made even more difficult by the fact that the sequence of the commands is critical, because there are constraints between the objects. And finally, to make this task almost impossible, the DBA / Team Leader must also prepare a data deployment script for the configuration tables.
Of course, it need not be this difficult. Clearly, what is needed is a database change management to complement the SCM. This is all the more important because databases often contain significant amounts of code for data –driven applications. This code can appear as pure code (for example, procedures, functions, packages, and triggers), or as initialization values (sometimes referred to as Lookup / Metadata / Configuration tables).
You would not think of doing application development without an SCM to help manage and secure the process. Shouldn’t a database change management solution be axiomatic? Yet, for some reason, recognition of the need for database change management seems to fall between the cracks.
Well, it need not be this way.
dbMaestro TeamWork for Oracle does it all, automatically. It ensures that no changed database objects are overlooked, eliminates the need to manually put together deployment scripts, and reduces the time spent on integration of the database schema database content during deployment. All of this, in addition to providing enforced Check-out and Check-In procedures, database version control and an audit trail during the development database change phase.
Isn’t it time for you to bring management of your database changes up to the same level as your management of application changes?
Many times developers or DBAs make changes to lookup data tables, and after performing a commit in the database, they want to roll back the database to how it was before the change.
What they need is an “Undo the Change” capability. But without a saved definition of the lookup data prior to the change, the developer / DBA only has personal memory to rely on, trying to remember what was changed and how to undo it. This, of course, can increasingly become a mission impossible as the days since the changes turn to weeks and then to months.
If only you could roll back changes to your database as easily as you can roll back changes to your apps. In fact, you can – if you use dbMaestro TeamWork for Oracle.
Roll back of application code development changes is simple with existing Software Change Management products. First of all, developers work locally on their particular section of code, and perform check-in when the work is complete. Furthermore, with SCM tools, application change versions are saved, and if the need arises, development managers and project managers can roll back to an earlier version.
Database version control is a more complex issue. Databases are centralized, so database developers work directly on the database. A strong database change management system is needed – one that compels and enforces check-out and check-in of database schema and content. And such a system must provide an audit trail that provides for versioning and version control.
Enter dbMaestro TeamWork for Oracle.
TeamWork compels Check-out of database objects before work can be performed on them, and Check-in when the work is completed. It provides an audit trail of changes and responsible parties, and saves different versions with each check-in.
Thus, if the need arises for lookup data rollback, the user can undo changes and roll back to an earlier version with just a few mouse clicks.
So implement dbMaestro TeamWork for Oracle before you have to rack your memory to undo lookup data changes and find yourself wishing you had implemented TeamWork earlier.
You would never perform application development and changes without the benefit of Software Change Management. So why do you perform database change without the benefit of Database Change Management?
There is a reason that you will not find a software project that does not utilize a Software Change Management (SCM) solution.
Application development is a complex process. It involves many people and teams working locally on many code segments that ultimately must be re-integrated into the final code. Ideally, it should all go well. But it doesn’t always turn out that way.
SCM methodologies have many features and controls that facilitate and maximize application development. One of its benefits is versioning that enables you to undo certain changes and roll back to an earlier version. Imagine what it would be like to try to roll back to an earlier version of an application without the benefit of SCM. It is too horrible to contemplate.
But for all its benefits, SCM does not handle database change management, largely because databases are by nature a centralized resource that developers work on directly. Whereas in code, developers work on local copy and apply the changes to the central location in a check-in operation.
So now change your focus and suppose that you had to roll back to an earlier database schema.
Many times developers or the DBA make changes in the packages / procedures / table definitions, and after making the changes, they find that they want or need to return to what existed before the change – to “Undo the change.”
But without a “saved definition” of the object prior to the change, the developer / DBA must rely on memory to determine what was changed, and then try to undo it. This is hard enough (not to mention exceedingly error prone) days or weeks later. If the changes were made several months earlier, it becomes a mission impossible.
To successfully perform schema rollback, you really need Database Versioning and Database Version Control. Without this functionality, schema rollback would be nightmare. But SCM does not provide these capabilities. Only a Database Change Management solution can provide this functionality.
In short, you need Database Change Management just like you need Software Change Management. And the best Database Change Management solution is dbMaestro TeamWork™.
In our previous post on What You Need to Monitor: Database Changes Made by Privileged User, we discussed the need to control how privileged users, like database administrators (DBAs), make changes to the information contained in back-end systems. Next, we’ll talk about the need to prevent those privileged users from accessing the database using inappropriate or unapproved channels.
According to Gartner, the greatest threat here is that “accessing databases outside of normal channels can also be symptomatic of compromised accounts being used by external hackers.”
The analyst firm recommends that DBAs and other super-users use only specific, authorized tools, so as not to bypass monitoring and tracking mechanisms. However, many DBAs connect directly to the database server using a remote console, thus bypassing network security. And after logging locally, they typically initiate a local session to the database. This session does not send network packets, so security and sniffer tools will not capture and audit what has been done. Some DBAs even know how to simulate the connection as if its origin is an approved channel. This, in turn, poses immeasurable risks in terms of database availability and integrity.
Our next-generation Oracle db change management environment, dbMaestro TeamWork™ has a powerful lock mechanism that oversees all relevant update events, regardless the connection type.
TeamWork can proactively minimize the threats caused by privileged users using unauthorized channels to access back-end systems. Because it works from within the database itself, its locking capabilities cannot be bypassed in any way. Additionally, it manages access, changes, and deletions to all database elements – regardless of the connection type, the program, or the client. So modifications are always captured, and can be fully audited at any time, regardless of the channel used to make them.
There are countless benefits that can be achieved by integrating dbMaestro TeamWork™ with RTC. In prior posts, we talked about the ability to more comprehensively track changes across software code and database schemas and objects, as well as the ability to better align development projects with business needs. In our final post in this series, we’ll highlight how the seamless integration of these two important solutions can help companies more effectively enforce their change management policies.
Because of rigid compliance guidelines and strict internal policies, most IT organizations have formal software change management and database version control procedures in place. Yet, if the systems used by software and application developers to manage their activities are not closely linked with those used by database development teams, those policies will be nearly impossible to enforce.
But, when the TeamWork Oracle db change management solution is closely linked with RTC, IT organizations can ensure full adherence to all software and database change management policies, at all times. For example, they can define access rights to certain database objects, so only those developers who have been assigned related tasks can modify them. This increases productivity by preventing developers from wasting time on tasks that are irrelevant, not important, or can be delayed to address other, more critical activities.
Additionally, linking TeamWork with RTC offers many other benefits. It allows IT organizations to address code and database upgrades in a way that is more aligned with business requirements. It allows developers to provide stakeholders – including DBAs, application managers, and business executives – with a single report that outlines all changes made from both a database and a software perspective. And, it enables complete visibility into the entire history of all changes, so all those involved in a project can see “the big picture”.
Database administrators (DBAs) have a lot of responsibility. They oversee all aspects of the database, its maintenance, and its operation. And, in order to ensure that everything goes smoothly, they need complete visibility into all the activities being performed by the developers to the database objects. In other words, they must approve any changes to the schema or objects – before they are made.
But, this can cause problems, particularly in scenarios where a high volume of modifications are planned. Each individual alteration must be authorized by the DBA before it is made and implemented. And most modifications require several iterations, all of which will require DBA approval. But DBAs are quite busy, and typically can’t review proposed changes right away. The result is project delays.
Project Managers need to find a way to keep their projects on schedule, while ensuring that the DBAs they are accountable to are fully informed at all times. That’s where an Oracle database change management solution, like dbMaestro TeamWork™, can help.
Our robust, next-generation database version control solution provides DBAs with comprehensive reports on change activity and history. They can see what changes will be made, by whom, and why.
Developers can move forward with their changes, without disturbing the DBA, while the DBA can stay fully apprised of all that’s going on. Reports can be run at any time, so DBAs can review changes in progress at their convenience. Once all changes are approved and executed, the DBA can then move forward and run the upgrade script.
Furthermore, the DBA can use the internal permission mechanism within TeamWork to more efficiently manage database security. They can accomplish this by granting only partial access to developers. For example, they can allow a developer to change packages and procedures, but not the table structure. This will not only enhance security, it will make it easier for the DBA to check and approve changes, since mistakes are less likely to be made.
Whether you’re building software for internal use, or creating an application to be sold to customers, you’ve got one very important task to complete once you’ve finished making changes to the database – you must prepare the script that will execute the upgrade from the previous version to the new one.
Preparing this script can be quite time-consuming. It involves not only your database administrators, but your application database administrators and team leaders, as well developers and QA staff. These professionals usually participate in the generation of several iterations of the script, which can waste valuable resources – something your project manager wants to avoid.
Additionally, script preparation is often left until the final stages of the project, to ensure that it includes all changes made to the database throughout. However, in some scenarios, particularly those projects that have spanned long timeframes, the stakeholders may have forgotten what changes were made.
dbMaestro TeamWork™, our next-generation Oracle db change management solution, can streamline and accelerate this process by enabling you to closely govern the way modifications are made to your database. For example, you can create formal methodologies and procedures for database version control, and TeamWork will automatically enforce them any time schemas, objects, or other database content is being altered. So, the work performed by your development team will always be in compliance with your internal policies, as well as regulatory guidelines.
TeamWork’s value in this area has been proven time and time again in real-world scenarios. In fact, dbMaestro customers, like the Massachusetts Department of Education, have reported that the use of TeamWork has helped them to significantly speed up deployments by reducing the time needed to prepare upgrade scripts by as much as 95 percent.
Loading...