Red Gate forums :: View topic - Database Upgrade
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
Deployment Manager
Deployment Manager forum

Database Upgrade

Search in Deployment Manager forum
Post new topic   Reply to topic
Jump to:  
Author Message
jochem4207



Joined: 06 Dec 2013
Posts: 34
Location: Netherlands

PostPosted: Tue Dec 24, 2013 11:36 am    Post subject: Database Upgrade Reply with quote

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?
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1116
Location: My desk.

PostPosted: Fri Dec 27, 2013 11:17 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
jochem4207



Joined: 06 Dec 2013
Posts: 34
Location: Netherlands

PostPosted: Tue Jan 21, 2014 10:28 am    Post subject: Reply with quote

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 Very Happy
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1114
Location: Twitter: @dtabase

PostPosted: Tue Jan 21, 2014 12:22 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
AlexYates



Joined: 03 Dec 2012
Posts: 1
Location: Cambridge

PostPosted: Tue Jan 21, 2014 1:16 pm    Post subject: Reply with quote

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. Smile

Alex
_________________
Alex Yates
@_AlexYates_
alex.yates@red-gate.com
Back to top
View user's profile Send private message Send e-mail
jochem4207



Joined: 06 Dec 2013
Posts: 34
Location: Netherlands

PostPosted: Tue Jan 21, 2014 1:22 pm    Post subject: Re: Reply with quote

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)
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1114
Location: Twitter: @dtabase

PostPosted: Tue Jan 21, 2014 1:26 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Send e-mail
jochem4207



Joined: 06 Dec 2013
Posts: 34
Location: Netherlands

PostPosted: Tue Jan 21, 2014 1:33 pm    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group