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.
Part 1 of this topic described the general characteristics of a next generation Database Change Management (DCM) system. A successful DCM system must not only maintain database version control, it must also facilitate the comparison and deployment of database schema and data. To see Part I, click here.
The .NET framework provides:
A Common Language Runtime abstraction layer for operating system independence
Base Class Libraries containing pre-built code for low-level programming tasks
Development frameworks with customizable solutions for larger programming tasks
Developers working in a .NET framework typically use Microsoft Team Foundation Server (TFS) to handle Software Change Management (SCM) and work items for change requests. TFS provides extended functionality in the same working environment that the developers use to upgrade and debug their code. TFS is available as stand-alone software or as the server side backend platform for Visual Studio Team System (VSTS).
This is why a dedicated Microsoft Visual Studio add-in was created for dbMaestro TeamWork. The TeamWork Add-in for Microsoft Visual Studio integrates dbMaestro TeamWork functionality into the VSTS environment. This gives VSTS users the ability to track database modifications in tandem with application source code changes and enables developers working with VSTS to use dbMaestro TeamWork functionality from within the Microsoft VSTS environment.
A new panel that has been added to Visual Studio enables VSTS users to perform change management activities such as check-in, check-out, view history, and rollback database objects.
Every check-in performed in TeamWork is also documented in the TFS history.
Additionally, developers working in a .NET environment can leverage TeamWork’s Visual Studio add-in to create a single, centralized workspace for changing .NET code and updating the status of work items, as well as for altering database objects and linking them to those work items. This approach:
Allows users to correlate VSTS and TeamWork projects, including linking database changes and updates with VSTS activities
Enables users to save extended information about database activities in VSTS
Automatically adds TeamWork check-in comments to the linked TFS work item’s history
Provides a single location for impact analysis, task tracking, and managing and auditing changes to the application source code and the database
Enables access to merged information about code and db changes from within VSTS
Provides a single vocabulary to drill down into project tasks and impact analysis
(what was changed when feature X was developed or bug Y was fixed) from both
source code and database perspectives
Creates a unified process for achieving code and database deployment based on Microsoft VSTS definitions of activities and work items
In a typical VSTS environment, each development team uses a separate database that is managed by TeamWork for Oracle and linked to the TFS tree. This enables each team to work on its own version without interfering with another team’s changes. A common database environment is created for integration.
Database changes in the common environment are managed by TeamWork for Oracle and used for deploying each team’s change scripts into a unified environment. However, if each team works on different objects and the risk of conflict is low, a single database environment may be sufficient.
This topic will be divided into two parts. Part 1 describes the attributes of a comprehensive but easy-to-use database change management system. Part 2 will contain information about using dbMaestro TeamWork Add-in for MS Visual Studio Team System (VSTS) in a .NET framework.
For years, application development organizations have relied on software configuration management (SCM) solutions to ensure seamless updates and modifications to software code. Although SCM has provided comprehensive tracking of alterations made to application code, it has done little to help manage related changes made to the underlying application databases.
Traditional SCM systems work well with software code changes but are not designed to handle changes to databases. A major disconnect exists between software and database development activities, with software developers using automated tools while database developers struggle with manual processes and workarounds. As a result, changes made in the development environment are not linked, creating failures in the production environment just after upgrading to a new version.
Additional problems are caused by the fact that SCM solutions save scripts in their repository (the vault) and retrieve these scripts from the repository as opposed to saving definitions in and retrieving these definitions from the database. Therefore, the DBAs must follow a multi-step manual process that requires them to:
Export the appropriate object DDL from the underlying database
Check-out the object DDL from the repository
Update the script
Run the script in the database
Check-in the object DDL to the repository
What DBAs and their teams really need to successfully manage change is proactive database change management that applies proven SCM principles to the database world and enforces those principles in the database engine.
DBAs and their teams need solutions that follow traditional SCM concepts that:
In order to accomplish this, the next generation of Database Version Control solutions needs to bring the complete array of software change management functionality to the database development environment. Unlike the current generation of database tools which are little more than “compare and sync” applications, the next generation of Database Change Management solutions must include much more comprehensive capabilities. In addition to compare and sync, they must deliver object locking, check-in/check-out, baseline setting and comparison, code merging, tagging and labeling, and change rollback capabilities.
Part 2 of this topic will provide information about using the dbMaestro TeamWork Add-in for
MS Visual Studio to provide TeamWork functionality in a .NET environment. Stay tuned for the 2nd installment.
Loading...