Database Upgrade

Automated deployment for web applications and databases

Moderators: Mike Upton, justin.caldicott, Sean.newham, csmith, chirayu, DavidSimner, david.conlin

Database Upgrade

Postby jochem4207 » Tue Dec 24, 2013 11:36 am

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?
jochem4207
 
Posts: 34
Joined: Fri Dec 06, 2013 2:27 pm
Location: Netherlands

Postby james.billings » Fri Dec 27, 2013 11:17 am

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby jochem4207 » Tue Jan 21, 2014 10:28 am

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
jochem4207
 
Posts: 34
Joined: Fri Dec 06, 2013 2:27 pm
Location: Netherlands

Postby David Atkinson » Tue Jan 21, 2014 12:22 pm

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
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby AlexYates » Tue Jan 21, 2014 1:16 pm

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 [url=http://documentation.red-gate.com/display/DM2/Using+PowerShell+scripts+in+deployment#]here[/url], 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 [url=http://www.red-gate.com/messageboard/viewforum.php?f=166]here[/url] 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 [url=http://www.red-gate.com/messageboard/viewtopic.php?t=19545=166]here[/url] and an alternative approach using Red Gate's SQL Backup tool [url=http://www.red-gate.com/messageboard/viewtopic.php?t=18365]here[/url].

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 [url=http://documentation.red-gate.com/display/SOC3/Source-controlling+static+data]the static data feature of SQL Source Control[/url] 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
Alex Yates
@_AlexYates_
alex.yates@red-gate.com
AlexYates
 
Posts: 1
Joined: Mon Dec 03, 2012 2:29 pm
Location: Cambridge

Re:

Postby jochem4207 » Tue Jan 21, 2014 1:22 pm

David Atkinson wrote: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)
jochem4207
 
Posts: 34
Joined: Fri Dec 06, 2013 2:27 pm
Location: Netherlands

Postby David Atkinson » Tue Jan 21, 2014 1:26 pm

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
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby jochem4207 » Tue Jan 21, 2014 1:33 pm

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!
jochem4207
 
Posts: 34
Joined: Fri Dec 06, 2013 2:27 pm
Location: Netherlands


Return to Deployment Manager

Who is online

Users browsing this forum: No registered users and 0 guests