Database Upgrade
jochem4207
Posts: 47
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?
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
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.
Redgate Software
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
Thanks,
David
Product Manager
Redgate Software
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
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)
Product Manager
Redgate Software
@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!