3 Replies Latest reply on Jun 2, 2011 3:16 AM by Daniel Tharby

    Database deployments using BladeLogic

      I am working on automating an application part of which involves automating the database deployments for that application. I am trying to determine the best approach for the following use case:

       

      USE CASE: The database deployments could be either a single SQL statement or a SQL script or multiple SQL scripts. Currently, this is done manually i.e. the deploy engineer opens a SQLPLUS connection to respective database and runs the statement or the scripts as required.

       

      Here is the approach I thought about:

      MY APPROACH:

      ASSUMPTION: The target server has oracle client installed in it.

       

      1. Create a BL Package with empty external command

      2. Save either the SQL statement or the script to a SQL file on the physical disk.

      3. Get the following as input variables into Property Dictionary -

      ORACLE SERVER NAME

      ORACLE DATABASE INSTANCE

      ORACLE USERNAME

      ORACLE PASSWORD

      4. Export appropriate environment variables within the external command

      5. Invoke SQLPLUS in the external command and call the SQL file from step#2.

       

      I am planning to automate the creation of the above package. So, the only manual step is #2 where the user has to save the SQL statement or script into the physical SQL file on the disk.

       

      Any other approaches or suggestions on how to optimize or better automate this part?

      I looked at using the 'Long Text' type property but it is only limited to 2000 characters. It won't be useful if the statement or script is longer than 2000 characters.

       

      Thanks

        • 1. Re: Database deployments using BladeLogic
          Daniel Tharby

          Hi Naveen,

           

          An approach might be to have the end user put the sql script on a drop directory and then use some NSH scripting to pick up the file and add it or create a new BLPackage.

           

          I have done something similar using scripting to create a component, add to a BLPackage and then destroy the component. The script I created for this is on the wiki.


          When you create the BLP, you can either script the additional of the external commands or possibly have a generic BLP and add the asset to the package and move to the start.

          That way the script would be deposited to the target server first and then you could invoke SQL Plus.

           

          HTH

          Dan

          • 2. Re: Database deployments using BladeLogic

            I am doing something similar. The user is going to drop the sql script into a staging directory on a server which has bladelogic agent and sqlplus installed. I create one static package with dynamic parameters for database username, password, server and sid. I deploy the package to that server which in turn invokes sqlplus, connects to the right db and executes the sql script from the staging directory.

            This seemed to be the best option as the customer was willing to install oracle client / sqlplus on all the servers.

            • 3. Re: Database deployments using BladeLogic
              Daniel Tharby

              what you could do is instead of the file being dropped on the target server, get them to drop it to a shared area on the App Server and use the BLP to pickup and drop to the target.

               

              This will then eliminate the possibility that the file is dropped in the wrong location and the script fails because it can't find the file.

               

              another idea to toy with ?