Transport Systems

I’ve worked on a couple of SAP systems, both the “R” flavors and the BW product. Although I had my learning challenges with them, there was one construct there I really liked. It was the transport mechanism.


Basically what this involved was setting up three instances of the system – one called development, one called testing (or staging), and another called production. That really isn’t unusual, since most of us do that with our database systems. The difference is that if you set it up properly, you can’t write or install code directly on test or production. All development is done in the development system, and then a special transport mechanism is the only thing that is allowed to transfer code to the test system. This is done under the control of an administrator. From there, the testing system’s administrator (could be the same person) is the only one allowed to “certify” the code tested and then it is moved to production.


In small shops, this is a real pain. But in larger, more complicated environs, this is such a lifesaver. Oh, and another interesting feature I had built into the systems I used was a “production snap”, where the production system could automatically refresh the development server. It would track the changes (because of the transport system) and could send back the changes from production to development. Any data marked “sensitive” would be scrambled, so you could still have practice data from the production server without worrying too much about security.


How do you deal with keeping your development, testing and production servers synced?

Comments (5)

  1. I’ve worked on a couple of SAP systems, both the “R” flavors and the BW product. Although I had my learning

  2. Louisducnguyen says:

    At my company it’s backup and restore, fix SIDS, attempt to blackout hard-coded names & passwords in stored procs, … cross-fingers.  I have a stored proc that searches sys.sql_modules definition column.  It BCP’s out a "before" definition.  It does patindex and replace to create an "after" definition. If there’s a better way, I’d really like to know.

  3. BuckWoody says:

    I used a "script and compare" method myself. You do know that the Visual Studio Edition for DBA’s has this feature built-in, yes?

  4. Louisducnguyen says:

    I’ve used VSTS for DB professionals to compare database objects between 2 SQL instances.  I found the button to "synch" objects between two instances a bit dangerous.  Always paranoid I might touch production accidentally.  (I’m sysadmin across dev, test, & prod environments).

    The stored proc I was referring to is a bit different.  It cursors over all the definitions in sys.sql_modules, finds any bad words from a blacklist, replaces the bad words, and scripts out the modified stored proc and the unadulterated proc.  The bad words are servernames, usernames, passwords, etc…  (I didn’t write this code).  I cleanse the DB by applying the modified procs, before turning it over to the developers.

  5. BuckWoody says:

    Nice. I’ve seen those kinds of procs, but as I mentioned I use to use the "script oout and compare" myself. The reason I do that is that I then have a lot of control on the order and I can also include it in other command-line things.

    Actually, I’ve become a huge fan of PowerShell this way.