Retaining database name on upgrade (also data migration)

MattMacdonald

Sorry if this question has already been answered but I couldn't find an answer. This is also a multitiered question. I downloaded the free version of Deployment Manager to test some situations that reflect our current workflow at my company.

I have multiple servers set up, each having a differently named database on it. I basically need to sync these databases with a tested database iteration. The simplest way seemed to be to name the server connections with the database names. Then try and set a variable in the database name field in the step to use the name of the connection when deploying. So hopefully on deployment to those servers I don't need them to all conform to one name and can allow them to have independent names.

The only other way I could see to do this is each server have their own step and explicitly name the database in each step. The part I don't like about that solution and would try to avoid is that in my mind I envision needing to upgrade multiple server databases with a couple steps which may include one or more migration scripts to be run. This seems like it would expand into a lot of steps. Perhaps that is the correct way to use the manager? Or am I misunderstanding how I should be structuring projects?

MattMacdonald

So I think I actually need each server to be on a separate project, but now I am confused about the steps. Say I've created one release with a package. Then I create a second release with an upgraded package as one step and added a migration as a second step (I'm also not sure if I can automate migration scripts?) , then I create a third release that has yet another upgraded package but no migration script this time. Do I need to be adding and removing steps for each release? Also is there no instance saved? i.e. say I need to revert back to the first release and go back up to the third release. It seems like the state of the packages doesn't get preserved between releases.

Sorry if this is confusing, I can clarify if needed. In my head I'm trying to equate release versions in the Deployment Manager to versions of our products database schema. I may need some clarification on better use cases for the DM.

EDIT: ah I may have figured it out. Perhaps I just needed to explore some more. The tour was also helpful. It looks like I should just keep all my steps and select only the ones I want to use when creating a release. I'm still not sure about migration scripts. Again sorry for all the questions

robin

Hi Matt,

For your infrastructure we would recommend you set up Deployment Manager as follows:

You will need to add each of your SQL Servers in the environments tab.
You should have only one project with a single database step.

To set up so that the database has a different name on each server there are two possibilities:

1) As you suggested, name the server with the database name and the on the database package step settings you can set the database name to be $(RedGateDatabaseServer) to reference the server name

2) You can override the database name by setting up a project variable. You will need to go the the project variables page and for each machine add a variable with the same name e.g. DeployDatabaseName and then in the drop-downs select the database server, and then enter the ppropriate value. Then on the package step page change the database name field to reference that variable (For our example change it to $(DeployDatabaseName).)

With regard to migration scripts, we are currently working on support for SQL Source control's migration scripts natively in Deployment Manager, and we expect that to only require a single package for both schema and migrations. If you have custom migration scripts you wish to run, you will need to write a powershell script to run your .sql scripts and then you can either package them in with your existing database package, or create a different package and add a second step to your project. An example of how to do this can be found here.

I don't quite understand what you mean by 'the state of the packages'. The way Deployment Manager is intended to work is that each package represents the state of a component, and deploying that package means "Make it look like this." For databases, we use SQL Compare technology to update the database schema.

Hope this is helpful, let us know if you have any more questions

Robin[/url]

MattMacdonald

Thank you robin! That was very helpful. You also were accurate in your interpretation of what I meant by package state.