Create Database Production;

xrT

My friend ask me a question which got me asking myself too, "How do you migrate a database from the dev environment to the actual production?".

I was just exposed to "inheriting" existing systems so I never actually got to see how DBs are moved to production.

Off the top of my head, I could think several ways like restoring DBs (structures and required data only) to production, creating lots of scripts, DB projects in VS (though I'm not sure how), DTS package, make the app "build" the DB structure if it does not exist, or just copy the DB and rename it to <db name>_production (eek! :P).

Also I wondered, will this be included and executed in the installer package? Or a support from the team would do this migration, as well as the installation of the app?

ammoQ

(thread moved to "coding related help & questions)

In Oracle, probably the simplest way to do it is to use the export and import facilites. "exp" dumps the whole database (or one schema) to a flat file; "imp" imports it again. In case of a schema export (IMO more suitable for app deployment) this must be accompanied with a script that creates the schema and grants the necessary privileges; a script is also necessary to create public synonyms, if they are necessary for the application.

Jim_Suruda

xrT-

If you are running SQL Server, the first migration is pretty easy, you can create a script to replicate all the tables and sprocs. But after the DB is running you'll of course have data in production that you don't want erased. Red Gate makes a product called SQL Compare that makes it pretty easy to figure out what tables and data are different between two SQL Server instances and what to migrate.

SQL Server doubtless comes with a tool to do the same thing, but SQL Compare is pretty painless.

Word
J

apapadimoulis

There is really only one way to do this and it's through DDL scripts (see http://fawcette.com/special/sqlserver/apapadimoulis-ddl/ if you've only used GUI tools).

Each release of your product (defied as anytime something moves to production) should have a corresponding set of DDL and DML scripts. As part of the deployment/release procedure, these scripts need to be executed. This is true for situation and every type of software.

However, the deployment/release procedure changes drasticially from project to project and it's imposibile to advise you with the information you've provided. The question you're asking is like "how do you fix a leaky faucet".

Tell us some more about the scenario.

VectorVision

I have to agree with Alex. It may not be as convenient or quick as
using Enterprise Manager, but for reliability and repeatability of
release cycles there isn't currently a better way to do this. You can
also add your DDL scripts to source control right along with your
source code.

I'm sure utilities like SQL compare work well, but I just don't trust
an automated utility to make decisions about changes to a production
database. When you're working with a production database it only takes
one "oops" to change the course of history.

What I would like to see is more integration in Visual Studio for
recording DDL scripts. The database projects are okay, but I don't
understand why the change scripts that are generated by the database
designer in VS can't be automatically saved to the database project.
The change scripts are already auto-generated in the background, but
you have to remember to manually view the script and copy it to the
database project or they are lost. Does anyone know if there is a way
to automate this process? This seems like a no-brainer for solutions
using a database project, so I'm wondering if I've overlooked something
somewhere.

jsmith

What about development "oopses"? Would you want that accidental DROP COLUMN to be propogated to production? It's better to deal with them manually or generate the scripts with a compare tool after QA. BTW, the test environment and a good unit test suite will prevent that oops from making it to production.

xrT

@Alex Papadimoulis said:

There is really only one way to do this and it's through DDL scripts (see http://fawcette.com/special/sqlserver/apapadimoulis-ddl/ if you've only used GUI tools).

Each release of your product (defied as anytime something moves to production) should have a corresponding set of DDL and DML scripts. As part of the deployment/release procedure, these scripts need to be executed. This is true for situation and every type of software.

However, the deployment/release procedure changes drasticially from project to project and it's imposibile to advise you with the information you've provided. The question you're asking is like "how do you fix a leaky faucet".

Tell us some more about the scenario.

Actually it's really just a question from a friend which got me thinking... And since I haven't had any experiences on actually moving the app to the production for the first time, I figured to just post it here, and see how this was actually done by others. :)

tufty

@jsmith said:

What about development "oopses"? Would you want that accidental DROP COLUMN to be propogated to production? It's better to deal with them manually or generate the scripts with a compare tool after QA. BTW, the test environment and a good unit test suite will prevent that oops from making it to production.

I personally disagree with generating the scripts after QA. DDL should absolutely be a part of the development and testing process. Yes, I've seen untested database migrations destroy data.

Ruby on rails has quite a nice mechanism for this, basically you define your "migration from one version to another" ddl with "up" and "down" scripts; there's an extra table added to the database indicating what version of the schema it has and you can migrate backwards and forwards to your heart's content. If you want to make a particular version irreversible, you can do that too. I'm not too keen on the ruby schema definition stuff that's been made the default now - it's useful for development on one platform and deployment to another, but it sacrifices too much ddl functionality in the interests of supporting what I, at least, consider a somewhat hazardous practice, which is to say not testing against a similar setup to your production environment.

Further documentation on the rails stuff here: Rails documentation and Rails Wiki

It's not rocket science, but it is very useful

Simon

Oscar_L

@xrT said:

@Alex Papadimoulis said:
There is really only one way to do this and it's through DDL scripts (see http://fawcette.com/special/sqlserver/apapadimoulis-ddl/ if you've only used GUI tools).

Each release of your product (defied as anytime something moves to production) should have a corresponding set of DDL and DML scripts. As part of the deployment/release procedure, these scripts need to be executed. This is true for situation and every type of software.

However, the deployment/release procedure changes drasticially from project to project and it's imposibile to advise you with the information you've provided. The question you're asking is like "how do you fix a leaky faucet".

Tell us some more about the scenario.

Actually it's really just a question from a friend which got me thinking... And since I haven't had any experiences on actually moving the app to the production for the first time, I figured to just post it here, and see how this was actually done by others. :)

I strongly prefer Alex's method. Schema changes are part of the deployment, and should be scripted just like any other db change wherever possible. Of course, when your carefully crafted and tested script ends up downing the whole server, you may find yourself reverting to more direct methods. At least, that's what a friend of mine told me.

Speed, safety and skill level(or the "Three Ss, if you will - you read it here first) are big barriers here, of course. It's way easier to tweak something small with Enterprise Manager(or whatever it's called now), and it's usually faster, too.

masklinn

@tufty said:

@jsmith said:
What about development "oopses"? Would you want that accidental DROP COLUMN to be propogated to production? It's better to deal with them manually or generate the scripts with a compare tool after QA. BTW, the test environment and a good unit test suite will prevent that oops from making it to production.

I personally disagree with generating the scripts after QA. DDL should absolutely be a part of the development and testing process. Yes, I've seen untested database migrations destroy data.

Ruby on rails has quite a nice mechanism for this, basically you define your "migration from one version to another" ddl with "up" and "down" scripts; there's an extra table added to the database indicating what version of the schema it has and you can migrate backwards and forwards to your heart's content. If you want to make a particular version irreversible, you can do that too. I'm not too keen on the ruby schema definition stuff that's been made the default now - it's useful for development on one platform and deployment to another, but it sacrifices too much ddl functionality in the interests of supporting what I, at least, consider a somewhat hazardous practice, which is to say not testing against a similar setup to your production environment.

Further documentation on the rails stuff here: Rails documentation and Rails Wiki

It's not rocket science, but it is very useful

Simon

Yeah the Rails Migrations are nifty suff, I strongly suggest watching the migration screencast it's mighty cool.

The only "downside" of migrations is that you usualy won't be using SQL in them

tufty

@masklinn said:

Yeah the Rails Migrations are nifty suff, I strongly suggest watching the migration screencast it's mighty cool.

The only "downside" of migrations is that you usualy won't be using SQL in them

There's nothing to stop you doing so, just ignore the "ruby schema" crap.

def self.up
 ActiveRecord::Base.transaction do
 execute <<-EOSQL
 -- SQL here
 EOSQL
 end
end

etc etc (use of here-documents to give you one bloody great chunk of sql, or individual 'execute' statements per line of sql, to taste)

What's really nice is that you can use any ActiveRecord objects in your migration as well, making it relatively easy to alter existing data through the "business" code rather than hand-crafted SQL.

Simon

KeeperOfTheSoul

Personaly I just write the DDL sql to migrate the database to the next version by hand. This I do with the development version as I go through it so it's not too much trouble. All this is checked into an working update folder with in the source control and when a new version is ready the working folder is renamed to reflect which version it upgrades from and to.

As I write the DDL by hand there is no chance to forget to copy it from enterprise manager and when I'm done all the change scripts are sat there.

As a side note, I looked at that Ruby thing and I couldn't see how that migrate thing was any different to having two scripts,

Up:
alter table posts add author_name varchar(50) null;
--now at this point we could leave null to mean anonymous (ie the value is unknown) but in keeping with the demo
update posts set author_name = 'Anonymous';

Down:
alter table posts drop column author_name

So how is this vastly different or any more complex than the Ruby system?

masklinn

@KeeperOfTheSoul said:

Personaly I just write the DDL sql to migrate the database to the next version by hand. This I do with the development version as I go through it so it's not too much trouble. All this is checked into an working update folder with in the source control and when a new version is ready the working folder is renamed to reflect which version it upgrades from and to.

As I write the DDL by hand there is no chance to forget to copy it from enterprise manager and when I'm done all the change scripts are sat there.

As a side note, I looked at that Ruby thing and I couldn't see how that migrate thing was any different to having two scripts,

Up:
alter table posts add author_name varchar(50) null;
--now at this point we could leave null to mean anonymous (ie the value is unknown) but in keeping with the demo
update posts set author_name = 'Anonymous';

Down:
alter table posts drop column author_name

So how is this vastly different or any more complex than the Ruby system?

The upsides of the Rails Migrations are:

Migrations are versioned, which means that it's easier to synchronise DBs between devs for example, or to port dev migrations to production as you only have to run "rake migrate" after having updated your repository
A single command (more or less) does everything
And this single command allows you to run 10 or 20 migrations, in the good order, one after the other, and without forgetting any.

Other than these automations -- which you could build by yourself with shell scripts and stuff -- it's no different than writing up/down scripts by hand indeed.

masklinn

@KeeperOfTheSoul said:

Does Ruby Rails let you see the migration sql its about to execute before it does, as I wouldn't like running scripts without knowing what they are doing to the server. Although I don't like running the standard upgrade scripts to a production server (just call me paranoid), but one has to.

Well, rake allows you to dry-run (without impacting the DB or anything), and I seem to recall that verbose migrations give you -- among other things -- the queries log.

I don't know if there is any easier way to get the queries (maybe), but I'm pretty sure you can see what ActiveRecord generates before you let it hit the DB server.

RyuO

@xrT said:

My friend ask me a question which got me asking myself too, "How do you migrate a database from the dev environment to the actual production?".

I was just exposed to "inheriting" existing systems so I never actually got to see how DBs are moved to production.

Off the top of my head, I could think several ways like restoring DBs (structures and required data only) to production, creating lots of scripts, DB projects in VS (though I'm not sure how), DTS package, make the app "build" the DB structure if it does not exist, or just copy the DB and rename it to <db name>_production (eek! :P).

Also I wondered, will this be included and executed in the installer package? Or a support from the team would do this migration, as well as the installation of the app?

The mechanics will vary from DBMS to DBMS (especially Rails, apparently) but the most important thing is to have a backout plan. If something goes wrong you want to keep the downtime to a minimum. Ideally, you'll test the backout plan.

John_Smallberries

I agree with Alex, too.
My preferred way to manage the process is to have 4 environments:

Integration. This is where all the developers put their code to see if it plays well with others and for early QA
Test. Only controlled releases are performed to promote code here. This is the QA playground.
Staging. This is an exact replica of Production. If the release succeeds here, it should work in Production. This validates the release package
Production

If the code is promoted through these environments in a controlled fashion (we use a homegrown, automated release app), all WTFs should be worked out.

xrT

@John Smallberries said:

I agree with Alex, too.
My preferred way to manage the process is to have 4 environments:

Integration. This is where all the developers put their code to see if it plays well with others and for early QA
Test. Only controlled releases are performed to promote code here. This is the QA playground.
Staging. This is an exact replica of Production. If the release succeeds here, it should work in Production. This validates the release package
Production

If the code is promoted through these environments in a controlled fashion (we use a homegrown, automated release app), all WTFs should be worked out.

This is similar to what our environment is setup here where I work. And I think this setup minimizes a lot of conflicts between builds / fixes. We usually just run scripts (from source control) on servers to modify DB structures or to deploy fixes.