What are Best Practices for Deploying Enhancements When Using Team Coding and Version Control?

We’re new to Version Control (if it hasn’t been obvious from my other threads!). We’ve been working on figuring out setup and what buttons to push to get TOAD to track objects and get source checked out and checked back in as we make changes.

And now we’ve come to the end-game and we’re beginning to see what is obvious to you that have already done this. With C# or Java or such, its fairly simple. You progress through development and testing and finish with your source changes committed to the production master. Then the master source is compiled and the binaries are distributed to the machines where they will be run. But the source stays locked away.

However, working with Oracle, you don’t distribute compiled objects. Rather, the source is distributed, which is then stored along with the compiled objects on the production server, and which could possibly be modified without the change being registered in the VCS? In fact, the person responsible for deployment could bypass version control entirely and simply extract the source directly from the test server? Further if a table needs a column added, for example, then you can’t simply use the new source because you would lose the data stored in the table. The update process has to know to use an alters, or to offload and reload the table. So somehow the change process has to know this.

How do your teams account for this? What are best practices for organizing schema’s and repositories and progressing through coding and testing to deployment?

– Doug

(Note: For what its worth, we’re a smallish IT team supporting our own companies internal software portfolio of vendor and custom code. Most of our work is small projects which are applied as they are completed, rather than waiting for a major release cycle. We’re working on pilots of TOAD with both Git and Team Foundation Version Control to decide which would work the best for us. )

Please check out our new product https://www.quest.com/products/toad-devops-toolkit/

Toad for Oracle and specifically its feature Team Coding will assist you in taking control of your rdbms changes.

Toad DevOps Toolkit integrates with orchestration tools like Jenkins. It will help you to automate CI (code quality and unit testing) and push changes forward in your deployment pipeline.

Hi Doug,

Different development shops usually have different ways they manage their change management process, but generally speaking there will be different database environments which help manage the changes from Development into Test and/or QA, then Staging for final testing before releasing into Production. Sounds like since you are a fairly small shop, your build and release sprints are fairly short. but having a functional testing stage for any PL/SQL code changes as well as a performance testing stage prior to release would be important.

The version control system (VCS) will be the “system-of-record” which documents any changes made to schema objects (inc PL/SQL code), represented as source code, along the way. Toad’s Team Coding is a great way to simplify the process for a developer (or anyone else) who needs to access source code in the VCS and ensure code integrity in a collaborative environment. Sounds like you’ve figured that out? If not, read this great article by one of our Toad developers Getting Started with Team Coding.pdf (1.49 MB).

The Team Coding repository is restricted to users on a particular database instance (like Development). So if you need someone on a Test database to use Toad’s Team Coding to access objects in the VCS, they will need to create a Team Coding repository on their database, otherwise they can access the source code files directly in the VCS.

In terms of making table column changes like you describe, you should know that in our upcoming release of Toad, we have made some changes to Team Coding to where you can specify that a table is rebuilt (using a copy table and moving the data) rather than simply dropping and re-creating the table on the target database.

For functional (or unit) testing of PL/SQL code, then Toad’s Code Tester (part of the Toad Developer Edition) can automatically generate all your test cases and store them in a repository. Again, repositories are limited to a database instance, so if you need to provide access to someone using the Test database, they will have their own repository. Both PL/SQL code and unit tests are stored in the VCS, so someone in Test will be able to access these for further testing.

Once these basic foundations are in place, you could automate the whole process as part of DevOps by leveraging a build automation server like Jenkins, using Toad DevOps Toolkit which would give you access to all the development artifacts and promote them throughout the other environments. For more information, take a look at the new Toad DevOps Toolkit community which contains videos showing how it works: https://community.toadworld.com/products/toad-devops-toolkit .

If you need to performance test your schema changes (perhaps you’ve made some SQL changes), Benchmark Factory (part of the Toad Developer Edition) can perform a scalability test on your schema changes using a previously captured production database workload and replaying it in a test database, incorporating the changes, to assess whether they will be any performance degradation.

Lenka and John,

Thank you for your responses, certainly more to think about. I wonder, where is the option for table copy and rebuild? I’ve been working with the Oracle 13 Beta and I haven’t found it yet?

Those options are available from a table right-click in Schema Browser. For a Copy, right-click, select Create, then Like, to clone the selected table.

Remember that if you want to rebuild the table and you are using Team Coding with version control, you will have to check-out the table first.

Thanks John. Found it. Is this integrated into VCS processing in some way? It seems like once you had made table changes in Dev, you would want to store the final full table DDL in the VCS. But when you go to deployed to Test or Prod, the rebuild script is what you would want to have run?

Hey Doug,

As John mentioned, every shop is going to have a different method of deploying changes from Dev through QA to Production. How you do this will be up to your company’s preferences and workflow. John and Lenka have already mentioned Toad DevOps Toolkit, Code Tester, and Benchmark Factory. Each of those products are excellent tools for helping solve development and deployment options in different environments. I’ll let them talk more about those applications and how they can help in your environment. If you’re looking to implement some CI workflows, I would definitely look into Toad DevOps Toolkit as both John and Lenka mentioned.

To answer your first question: rebuild actions are partially integrated into VCS processing. In a rebuild action, the table you end up with will have the same name as the table you started with. The table will requires a check-out before you can rebuild it. You can access those either through the Schema Browser, as John mentioned, or the Team Coding Manager. Once the rebuild is done and the changes are made, all you need to do is check the table back in and its DDL will be saved to the VCS. The rebuild process itself, however, also allows you to keep the old table as a backup if you wish. This backup table will be added to Team Coding’s list of objects it manages, but it won’t automatically add it to the VCS. The reason is because you may only wish to keep the backup around for verification and drop it immediately afterwards. Adding backup tables to the VCS would only clutter the VCS – especially if they are temporary. If you wish to add the backup table to the VCS, all you need to do is check that table out and Team Coding will automatically add it to the VCS.

For your immediate need, I know you’re in a small development shop and are probably looking for some solutions that you can get started with immediately. Toad, itself, is filled with a lot of different tools to help you perform just about any task within Oracle. While Team Coding is primarily designed to manage source and change control within your database environment; you can use other tools within Toad to help you accomplish some of the tasks you’re looking to do. One of the ways people accomplish deployment scripts within Toad is by using the Compare Database, Compare Schemas, Generate Database Script, and Generate Schema Script windows within Toad. The first two windows are designed to compare two data sources and automatically create synchronization scripts between those data sources. You can even automatically save those synchronization scripts off to your VCS provider if you so choose. The latter two, as the name suggests, are designed to create scripts for exact copies of a source database or schema.

So, in your situation, you could use Team Coding connected to your Dev environment to do your source and change control. When you’re ready to move things to testing, you could then use the Schema or Database Compare features within Toad to easily create the synchronization scripts and save those off into a deployment folder within your VCS for documentation purposes. Using the VCS tab of Toad, the person responsible for deployment can then check out and review the synchronization script from the VCS before executing it to your QA environment. If you’re using Oracle 12, you could even choose to have your QA environment sitting within a pluggable database and use Toad to spin a new instance of that database onto which you can execute the script. That way, if you find the changes you made cause the QA environment to fail, you still have the original QA environment available without having to go through a process to restore the previous QA environment.

You can get a quick tutorial on using these options for deployment in a video provided by John P here:

community.toadworld.com/…/1493

Of course, this process is still somewhat manual and isn’t really ideal for implementing continuous integration workflows. For that, you’d want to look into Toad DevOps Toolkit above; however, hopefully it will give you a starting point from which you can decide what process would work best for your environment and your company’s workflow.

-John

John, this might be the answer for us. TOAD is developing lots of good things but we might need to learn to walk before we can run. I’ll definitely be exploring this when I get back in next week.

Besides, you can try this tool. It allows setting up DevOps processes in Jenkins with the help of the open-source dbForge DevOps Automation for SQL Server Plugin