In this post, we discuss how you can streamline the database development process, reduce development costs, and enforce change management best 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.
Saving a snapshot of a golden copy is an essential step when upgrading a version. dbMaestro TeamWork enables you to use this archived version when deploying database version.
There are several events that we recommend saving a snapshot version. Whenever a schema is being deployed, you should save the source and target schema(s), for example:
From development to integration
From integration to test or additional development
From test to production or integration
From integration to development (rebase)
The “save version” procedure should be invoked in the source environment before running the Deployment Manager and in the target environment both before and after executing deployment scripts.
It is important to save the source version of the schema in order to have a baseline against which the next deployment from this schema can be compared. For example:
When deploying the nth version from the development to the test environment, saving the nth version in the development environment enables the (n+1)th deployment from development to test to be compared against the nth version as a baseline.
Saving the current version in the target environment before executing deployment scripts enables the organization to prepare a rollback script and implement a rollback, if necessary.
Saving the nth version in the target environment after executing deployment scripts enables the organization to use the saved nth version as a baseline when implementing the (n+1)th deployment to that environment. Comparing the two versions reveals important changes implemented in the target environment due to conflicts. These changes can be implemented as a quick fix to the (n+1)th version, redeployed to the integration environment of other teams, etc.
TeamWork’s 3-way analysis compares version A and version B against a baseline version. When different changes are introduced in version A and version B (a typical situation when more than one team works on the same database), TeamWork’s automatic conflict resolution engine recommends an intelligent solution whose combined code merges the changes from both teams into a single piece of code.
TeamWork’s 3-way comparison analyzes changes between a source environment (e.g., development) and a destination environment (e.g., integration) and compares each to a baseline environment (e.g., QA or a previous version).
TeamWork’s Deployment Manager (DM) highlights the origin of each change. If a change came from the development environment, the DM recommends deploying it. If the change came from the integration environment, the DM recommends ignoring it because it was already deployed by another team. If both the source and destination environments differ from the baseline, the DM recommends merging the changes by using TeamWork’s automatic merge engine that creates merged code with a mouse click.
When database development teams must work with more than one database schema to achieve the desired results, creating one project that includes all relevant schemas is the best approach to take. TeamWork provides a variety of features that make it simple and easy to effectively manage a project with multiple schemas.
TeamWork also provides advanced permission capabilities that give DBAs more control over their environments. Unauthorized, accidental, and incorrect changes to schemas and related objects can be proactively avoided by allocating granular access permissions that give members of a development team rights to only those parts of each schema that they are responsible for changing.
This document describes how to specify source, baseline, and target (destination) schemas for a 3-way comparison. It also describes how to use the 3-way compare and merge module to automatically resolve schema and data merge conflicts. Finally it describes how to automatically generate an upgrade script.
This document describes how to save database content, how to compare versions, how to review specific changes, and how to automatically generate a script.
In this post, we’ll explain the various steps required to save a database version (or label), and use it in our innovative database version control solution, dbMaestro TeamWorkTM. Saving a version will allow you to create a “snapshot” that can be referred to at any time, for any purpose and it uses mostly as a baseline when deploying changes. For example, it can be compared to other versions, referred to as a baseline, used to generate scripts, etc, you can roll back to the version whenever needed and even used as a backup for your development database.
Saving Database Content
The first step is to take a snapshot (Save Version) of your database schema. During this action, dbMaestro TeamWorkTM reads all the objects’ metadata and for save them in the database version control repository, it also saves the content of the configuration table in the repository, as they are part of the version.
Since this can take time, the best practice is to change the schema state to Freeze, which prevent from anyone to check-out the objects and modify them during the Save Version operation.
Freezing the schema requires all objects to be in check-in state, thus making sure that the snapshot will not include partial definition of the objects.
Once you freeze the schema, you can save the version by left-clicking on the appropriate database in the center of your console, and selecting “Save Version”.
A pop up window will appear, where you can enter the name, description, and other version details, and link the version to your CM tool. If you wish to archive table content, be sure to check the “Select data tables to save” option at the bottom of the window. Click “OK” when you’re done.
Another pop up screen will display. Check the boxes next to those tables whose content you wish to archive. Click “OK” once you’ve made your selections.
Comparing Versions
In order to compare database versions to one another, left-click the database in the center of your console, and select “View History”. In the pop-up window, highlight the versions you’d like to compare, then click the “Compare Versions” button on the right side of your screen.
The next pop-up screen will allow you to compare schemas and their associated data, indicating where objects and sub objects have been previously modified.
Reviewing Specific Changes
To drill down to more detail about individual changes, click on the button with the three dots next to the object in question. A pop-up window will display the specific changes made in both the baseline and compared versions of the database.
If all changes are correct, simply close all pop-up boxes, and return to the “History” window. Click on “Generate Script” on the right side of the screen to create your schema script.
Getting started with dbMaestro TeamWork™, our robust and unique database version control, is fast and easy. In this post, we will guide you some of the basic procedures, and demonstrate how to use a few of the core features.
Creating a New Project
First you should create a project in the project tree and sub project for the development, QA and other environments you may have.
To create a new project, left-click TeamWork Projects at the top left corner of your screen, and select “Add New Project”. When the pop up box appears, enter your project’s name, and click “OK”.
Assigning Schemas
Once a project has been created, you’ll need to add your database schemas. Choose the environment within the project from the tree on the left side of your screen, and select “Assign Schema to Project”. When the pop up window appears, add the schema connection details, click “Test”, and then click “Next”. Note that you will be required to enter your SYS credentials for the first schema you add. When the Summary box appears, click “Finish”.
At this point, dbMaestro TeamWork™ will analyze the schema, but will not yet be managing it. This means that any changes or modifications can still be made by anyone and are not documented. At this time, dbMaestro TeamWork™ will also identify the objects in the schema (it may take a few minutes until you see the objects in the GUI). In order for dbMaestro TeamWork™ to manage the schema, you’ll need to add the objects to source control.
Adding Objects to Source Control
To add individual objects to source control, highlight the appropriate database in the tree on the left side of your screen. All objects associated with that database will then be displayed in the middle of your console. Any of these objects can be added to source control by simply left-clicking it, then selecting “Add Object to Source Control”. Include any important notes in the “Comments” window of the pop-up box that appears and then select “OK”.
To add all objects to source control simultaneously, left-click on the database, then select “Add All Objects to Source Control”. When the pop-up box appears, enter any notes in the Comments window and then click “OK”.
Managing Schemas and Their Content
Changes to database objects must be made from your Oracle development environment. dbMaestro TeamWork™ change policy is embedded within Oracle engine and will prevent any unauthorized or unapproved modifications from being made.
For example, a table cannot be altered if it is in “Check-In” status. Your console will display a red lock icon next to any objects that are unable to be modified. Left-click the object you want to change, and select “Check Out Items”. When the pop-up window appears, highlight the object and click “OK”.
If you try to make the change again, you will see that now it will be completed successfully.
Enforcing Change Management on Lookup Data
To enable change management rules on lookup data, left click the relevant table, and select “Enable Table Content Management”. When the Confirmation box appears, click “OK”. Another window will then display, where you can select the appropriate objects to have their content under change management. When you are finished, click “OK”.
Note that in order to invoke Table Content Management, the table must have a primary key or unique index(es).
Once you’ve implemented your change management guidelines, go back to the database version control software, dbMaestro TeamWork™, console, left-click on the table the rules were applied to, and select “Check-Out Data”. When the pop up window appears, click “OK”.
Next, go to your Oracle development environment, and choose the appropriate object from the tree on the left. From here, you can define data rules, set constraints and dependencies, etc.
For example, selecting a specific data element, such as Postal Code within the Location table, then clicking on the red X at the top of the screen, will lock it so it cannot be altered.
After performing a commit in the database you can Check-In your changes in dbMaestro TeamWork™, console, left-click on the table the rules were applied to, and select “Check-In Data”. When the pop up window appears, enter a comment describes the change and click “OK”.
The TeamWork security mechanism enables changes to Oracle databases objects to be controlled at the Windows account and group levels. TeamWork enables you to define your organization’s database change policy in order to prevent undocumented database changes and to control who can do what, to control when they can do it, and to see why they did it.
When people talk about database change control, they usually mean having an accurate audit trail of database changes. However, another factor that is often even more important is the ability to prevent anyone from making unauthorized changes. TeamWork provides both change control and prevents unauthorized changes by the DBA and IT teams.
We have found that in many of the organizations that have implemented TeamWork, developers are allowed to change database objects including packages, procedures, functions, and the content of lookup tables, but DBAs are permitted to change all objects including table structures. Likewise, QA users are only permitted to make changes in the QA environment and not in the development environment.
But, as a practical matter, all developers and all DBAs usually work with a single Oracle user account – the owner of the database objects. As a result, without TeamWork, every developer, every QA user and every DBA actually can change any object in the database at any time. TeamWork provides the ability to prevent unauthorized changes to the database structure and the data it contains, including unauthorized changes by users who do have authorization to access the database.
It is also not rare to find organizations that have the same password for the Oracle user account in all database environments: Development, Integration, QA, Pre-production and sometimes even in the Production environment. When using TeamWork, the administrator can give developers access to change packages, procedures, and functions in the Development and QA environments while restricting their access to the Production environment by only giving them access to review history.
Of course, most of our customers implement TeamWork in their Development environment before implementing it in any other environment. But we recently had one customer that had such a serious database security problem in their Production environment that they installed TeamWork in their Production environment before beginning to use it in other environments.
TeamWork provides advanced permission management capabilities that give you full control over who makes changes to your databases. This enables you to proactively prevent unauthorized modifications to database schemas and their related objects. TeamWork enables you to implement very granular access settings, giving users the right to modify only certain portions of each schema, based on their role and their responsibilities.
TeamWork not only controls the permission to make changes, it produces an audit trail of changes to the database, correlating each change (What) with the person who made it (Who), the date and time of the change (When), and the business reason for the change (Why).
For more information on these topics, see these useful links:
Clalbit Systems is the software development arm of Clal Insurance, one of the largest insurance companies in Israel.
Clalbit Systems has been using SCM methodologies to manage application code. All Clalbit Systems’ projects utilize ALM methodologies, including source control and clear segregation between development, integration, internal QA, external QA, and production environments. All systems are deployed over identically configured infrastructure to ensure testing efficiency.
The Problem
One major Clalbit project entailed a complete rewrite of the application that manages pension accounts, to support recent business guidelines and regulations.
The project required data conversion in support of the new application, and suddenly, their lack of a Database Change Management solution was looming large.
While their source development practices were as advanced as possible, Clalbit Systems’ management felt that they needed to achieve similar capabilities managing database artifacts while correlating those changes to code changes. The reasons are simple.
Database structure and lookup table data are an integral part of the system.
During code modification, developers work on a local copy, publishing the change only when the work is complete and the code is returned to the system via the SCM system’s check-in operations.
Conversely, the database is a central resource. Changes to database artifacts become active as soon as a DBA or developer commits the change or runs the associated DDL command.
To protect from the unintended consequences of such action, a policy enforcement that prevents code being overwritten was deemed necessary.
The Solution –dbMaestro TeamWork™
Clalbit Systems decided to use dbMaestro TeamWork™ for database version control, increasing the ability of their development teams to coordinate activities through the check-out / check-in mechanism as well as adding the ability to capture a version at any time during the development cycle and set it as base line for deployment.
dbMaestro TeamWork™ provided the following advantages:
Reduced merge times for each cycle – from a minimum of four hours to minutes
Freed-up human resources – before implementing dbMaestro TeamWork™, each merge cycle tied up the project leader for half a day, and stopped the relevant team from working
Increased ability to accommodate requirements changes during development
Improved conflict resolution of the database development deployment scripting
Automatic labeling of sources as associated with specific version
Automatic script generation from command line
Increased management control – team supervisors have an accurate status of who is working on what and when
Extended security control at the object level, enabling effective permission management
Clalbit’s Evaluation ofdbMaestro TeamWork™
Jonathan Bar-Sela, Development Manager, Long Term Savings, Clalbit Systems summarized his experience:
“By implementing dbMaestro TeamWork, we finally have full control of our databases. Having our schemas, data, triggers and other database artifacts managed makes a huge difference. By integrating the application with our source control and continuous integration paradigm, we are able to deploy our application in minutes and with fewer errors.
It’s been really great working with the dbMaestro team.
They assisted to build effective processes and provided quick responses to our questions and needs.”
Developers need an environment that applies proven software change management (SCM) methodologies to the database world and seamlessly synchronizes database development activities with SCM processes. The best way to accomplish these two goals is to deploy a next-generation database change management (DCM) solution and link it closely with an SCM system.
Seamless unification of DCM processes with an SCM solution can enable development organizations to ensure that changes are not made in a vacuum. By facilitating improved communication and coordination among software and database developers, this type of integration helps avoid conflicts and provides end-to-end products development life cycle.
Integrating software change management and database change management activities makes database development a truly integral part of the SCM process. Because both database development and source code development are treated equally from a change management perspective, manual and semi-automated processes are eliminated while efficiency and accountability are increased.
dbMaestro TeamWork facilitates integration with these widely used SCM products:
Microsoft’s TFS (Team Foundation Server)
IBM’s RTC (Rational Team Concert)
IBM’s RCQ (Rational ClearQuest)
Microsoft’s TFS (Team Foundation Server)
Integration with TFS enables you to perform the following actions:
Assign a check-in to work items (changesets)
Update TFS history with information from TeamWork
Correlate a “Saved Version” to a TFS Label by matching a native code “snapshot” with the corresponding database code
Review history by changesets
Deploy changes by changesets
Deploy changes by TFS Label
IBM’s RTC (Rational Team Concert)
Integration with RTC enables you to perform the following actions:
Assign a check-in to work items (changesets)
Review history by changesets
Deploy changes by changesets
For more information about TeamWork integration with IBM’s RTC, follow these links:
Integrating TeamWork with RTC, CQ or TFS provides numerous benefits. For example, companies can better align development projects with business needs and ensure that the end result meets any and all strategic requirements. This integration also eliminates the information “silos” that make it difficult to comprehensively track change histories – giving all stakeholders complete visibility into all the modifications made to both the software code and the database schemas and objects. Perhaps most importantly, SCM-DCM integration enables organizations to implement and enforce comprehensive change policies that effectively meet both internal and external compliance guidelines.
Linking software development projects with database development activities is fast and simple with dbMaestro TeamWork. The TeamWork Management Studio Console provides a simple and intuitive way to configure TeamWork to work directly with RTC, CQ, TFS, and other software change management environments. This allows developers to link any database schema to any project within the software change management system.
dbMaestro TeamWork™ is a state-of-the-art Database Change Management solution that brings the benefits of Source Change Management (SCM) methodologies to database development.
Because the implementation of TeamWork involves changes in methodology and organizational behavior, a quick and successful implementation of the solution is very important. Failure to make the required modifications in work methods will reduce or delay the benefits that TeamWork can provide and may have a negative impact on user satisfaction.
In particular, employees who had become accustomed to performing the manual activities that are automated by TeamWork may feel threatened by the loss or reduction of these activities and try to prevent the successful implementation of the new methods. It is important to demonstrate that the time saved by automating these tasks will enable them to spend more time on other tasks that are more interesting and less repetitive.
Topics covered in this post
This post describes the best practices that have helped our clients make a successful transition to the type of work environment that maximizes the benefits that result when using TeamWork to manage database changes.
This post will talk about the preparation the organization needs to do before using TeamWork. It will not cover the mechanics of the TeamWork installation procedure. For information about system pre-requisites and installation instructions, see http://www.dbmaestro.com/dbmaestro/594.aspx
The actions you need to perform to properly use TeamWork can be described under the following categories:
Selecting the right team
Selecting the implementation leader
Compare & Synchronization operations
Mapping database objects
Defining Modules and assigning objects to them
Adding objects to source control
Selecting the right team
It is important to select a development team that has adequate time to properly implement the new solution. A team that has a release date in the near future may not have time to invest in planning and implementing the transition to a new solution.
Selection of the team actually influences the schema to be managed in TeamWork. The team should have development and test environments.
Selecting the implementation leader
This person should have in-depth knowledge of the customer’s application and the database schema.
Compare & Synchronization operations
We have found that the initial synchronization of the development environment with the production environment can reduce the amount of work that is later required to prepare deployment scripts. The development environment often contains objects that were created but were not deleted even though they are no longer in use.
Mapping database objects
The implementation leader must determine which database objects need to be under version control and which tables are lookup tables whose data also needs to be under version control.
dbMaestro TeamWork for Oracle can manage the content of lookup tables. Lookup tables changes are part of release deployment.
Defining Modules and assigning objects to them
In this stage, the implementation leader creates modules in TeamWork and assigns the database objects that need to be under version control to those modules. This enables the development team to easily locate a working object within TeamWork. See our previous post on viewing the database schema from a module point of view.
Adding objects to source control
This action enables the TeamWork change policy to be enforced on the database objects that need to be protected at the database level. If anyone attempts to update a protected database object (by using a development environment such as Visual Studio, for example) before checking it out, a message is displayed indicating that the object is protected and must first be checked out.
Conclusion
Following these simple steps makes dbMaestro TeamWork of Oracle implementation quickly and smoothly.
This new feature enables organization to add another layer of security on top Oracle. Usually when developers, DBA connect to oracle database if it the development, test or even production environments, they connect with the owner of the objects. During this connection any object can be changed since the owner has full permissions on its objects.
The organization needs another layer of security and this can be done using TeamWork for Oracle Access Roles. With this the organization can give specific permission to Active Directory account or group.
We recommend using Active Directory groups, assign them the right privileges in TeamWork and add the right members to this group.
Here is an example from real customer implementation:
This customer manages three environments: development, test and pre-production. They created the following Active Directory groups:
DBA – Will be able check-out/check-in any object
Developers – Can check-out/check-in only PL/SQL code (packages, functions, procedures)
Read-Only – Can view object history but not change
Release Managers – Can save version, freeze schema, remove and add objects from source control
People were assigned in Active Directory to the relevant group and each group was assigned with the right privileges based on the environment.
For example: Developers can check-out/check-in only PL/SQL code (packages, functions, procedures) in development and test environment, but in production they have Read-Only permissions.
You are welcome to contact us to hear how you can add another layer of security in your organization, just send us an email to sales@dbmaestro.com.
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.
Loading...