Database Upgrade

jochem4207jochem4207 Posts: 47
edited January 21, 2014 7:33AM in Deployment Manager
Hello RG,

We're currently working on a database upgrade script.
Our upgrades only need to execute some SQL scripts. This is a bit different then the default way that redgate uses for database upgrades.

So we created a database step to have direct access to the database variables.

There we have a deploy.ps1 which executes all the scripts via sqlcmd.
If it fails it writes that it fails via Write-Host/Write-Warning. This still shows as a "succesfull deploy". If we set Exit code to 1 it 'crashes' and stops the full deployment.

Is there a way to let the database step fail and still continue the other deployment steps?

Are we doing this the right way? Creating a database step to run sql scripts, is there maybe a other way to do this via the normal redgate database steps?

Comments

  • Our own database method works quite differently to that- in that the package will usually contain a full representation of the database as a set of scripts (most commonly from a source control system, but you could generate these manually via SQL Compare and package them up with RGPublish) then the deployment will execute SQL Compare to establish the differences between the package and the target and generate the upgrade script on the fly.

    You can run manual scripts yourself of course (which it sounds like you are doing) but a non-zero exit code from powershell will abort the deployment and mark it as failed.

    There's not really any kind of "continue anyway" type setting in DM for this (although feel free to suggest it here)

    If you want the deployment to succeed / continue even if the SQL exited with an error, you might be able to implement try/catch to throw a zero exit code still- this article looks like it may be helpful.
    Systems Software Engineer

    Redgate Software

  • Hey,

    In the mean time I've almost finished the normal steps so now I'm back at the database steps.

    We have our software with associated table structure. When we've a new version of our software we create via Redgate SQL compare the structure and generate scripts based on that.

    When we roll out the update to customers they have a existing database that needs a upgrade on structure. But without the losing the data. This is now done via Redgate Data compare.

    I want to automate it and I kinda have a solution at this moment. That is that we run the scripts generated earlier with redgate data/sql compare. But this is not really a solid solution.

    is this possible doing via redgate DM database steps?

    Basicly what I need it:
    * Backup database
    * Update the database structure
    * Keep the data the same
    Extra
    * Update data in specific tables (e.g. the license code)

    For the most I've already written scripts, it's not that I ask for scripts but just to know if it's possible via DM and maybe a little guide.

    Yep we do like redgate :D
  • Just curious about the "Keep the data the same" requirement. In what way would the data not be kept the same using Deployment Manager as is? Would you be able to provide an example?

    Thanks,

    David
    David Atkinson
    Product Manager
    Redgate Software
  • Hi Jochem,

    Most of what you asked for is either covered out of the box or you can achieve it by using some PowerShell.

    For more details on how to use PowerShell you should consult our documentation page here, but briefly, if you include a PowerShell script with the name PreDeploy.ps1, Deploy.ps1 or PostDeploy.ps1 we will run the PowerShell script at the appropriate point during the deployment. We also have a forum here where real users have been sharing and discussing PowerShell scripts that they have written.

    Given that you can use PowerShell to perform certain actions you could use it to run a SQL script (or set of SQL Scripts) based on the environment that you are deploying to or any other variable. Naturally we would recommend using the native database upgrade logic but if you want to work in this way it is possible. Also, some people use this approach to append a regular DB deployment with some form of pre or post deploy script.

    With regards your other suggestions, there is already a PoswerShell example on the forum to take a DB backup as a pre-deploy step here and an alternative approach using Red Gate's SQL Backup tool here.

    Updating the schema could either be done using the default database deployment mechanisms or by running your own SQL scripts via sqlcmd in a PowerShell script.

    We don't touch your data by default, although you should be vigilant about the upgrade method that you choose. If you use the default method you will be relying on SQL Compare logic to generate the script so you want to be careful with table re-names etc. There is nothing stopping you from automating a pre-configured SQL Data Compare project from a PowerShell script to compare the pre-deploy backup with the post deploy edition of the DB.

    If you want to include specific reference tables in your deployment you can do this in two ways. Either use the static data feature of SQL Source Control and create your packages based on your source control version of your DB. (This is the best practice). Or use the SSMS add-in (available from the tools tab in DM) to create your package and select the static data from there.

    Let me know if this covers all your requirements. :)

    Alex
  • Just curious about the "Keep the data the same" requirement. In what way would the data not be kept the same using Deployment Manager as is? Would you be able to provide an example?

    Thanks,

    David

    Hey David,

    I thought this because of you have a database for a customer. Then you upload a database package. Which I guess will contain the new database structure/data from e.g the development database and will overwrite that.

    As you can see I didn't got a really clear point of how the Database part works. (Yes I did read the documentation)
  • SQL Compare's scripts make structural changes while preserving the data. Some changes require a table rebuild. In these cases SQL Compare will create a temporary table for the data while the operation is in progress.
    David Atkinson
    Product Manager
    Redgate Software
  • Thanks David and Alex for the quick replies.

    @Alex I want to use as much as possible from Deployment Manager. But because of me and a other collegeau didn't assumed it was possible to do what we wanted to archieve we started the powershell way.

    I'm going back to the drawing table and let you know the results!
Sign In or Register to comment.