Managing development, test, and production databas

  • I would like to know what some of you have found to be best practices are for managing development, test, and production databases. For example, version 1.0 of an app goes into production. If things are done right, all instances of the application’s database should be in synch (same tables, views, stored procs, etc). When it comes to do version 1.1 of the application, the developer’s/DBA’s make changes to the development instance. When the version is ready to be released to test, the database changes need to be applied to the test instance and likewise to the production database when the new version is released to production.

    We have procedures in place to manage the database changes and to migrate them to each instance, but it is labor intensive and it often times results in things not being applied correctly. If you have found a practical way of managing this type of situation, I would love to hear about it. Maybe SQL Server Central can publish an article about some of the best practices.

    Thanks!

  • You can script the database changes in dev, then release then to test, and run the same scripts on production. That way TEST and PRODUCTION will be inline.

    Also http://www.red-gate.com sell a utility called 'sql compare' which produces differences between two databases in script form, which could be used. Its quite cheap as well.

    Steven

  • Different people do it different ways. Steve Jones has a 4 part series posted about how he does it, a complicated (to me) process involving local folders and source control. I use SQLCompare - I work in an environment where we typically dont have that many changes at a time, easier to do the diff when I need it than try to force developers to maintain change scripts (or to do so myself).

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • quote:


    Different people do it different ways. Steve Jones has a 4 part series posted about how he does it, a complicated (to me) process involving local folders and source control. I use SQLCompare - I work in an environment where we typically dont have that many changes at a time, easier to do the diff when I need it than try to force developers to maintain change scripts (or to do so myself).

    Andy

    http://www.sqlservercentral.com/columnists/awarren/


    Thanks. Do you find value in using a tool like SQLCompare? The problem I'm facing is convincing our DBA that tool like this is required to successfully migrate changes to another instance. My argument is that developers and DBAs are humans and they make mistakes. Using a tool we can at least see ALL the differences between two databases. His argument is that you won’t necessarily roll every change to a development database to another instance. I agree with him, but only 10% of the time. We too, don’t have a considerable amount of changes and using a tool like SQLCompare would prevent the easily made mistakes that bit us almost every release.

    Our current methodology of keeping up with changes to a database is to submit Database Request Forms (basically a Word doc describing the changes) to our DBA. He in turns, writes the scripts to alter the objects and stores the change in a master script. When it comes time to move to another instance, he runs the master script. Although this works, it is still a slow process and has the potential for mistakes.

  • It is often hard to get management to spring for an extra environment. Our error reduced drastically when we convinced them to get a beta environment. We would create the scripts necessary for the update, copy everything from live to beta. Run the scripts on beta and run transactions in that beta environment. When we encountered problems, the scripts would be changed and we would try again. It was a little tedious and there are other ways, but this was a low tech solution that worked well for our needs.

    Guarddata-

  • I find SQL Compare to be an excellent tool. However, it is not foolproof. We are in the midst of a large development cycle with a SQL 2000 database. We are writing .net code and we do a complete build each and every week. Once the build is successful, I use SQLCompare to find all differences in schema, stored procs and functions between development and the qa environment. SQLCompare can produce thousands of lines of code in the time I can write one or two scripts. Each week I spend approximately 15 minutes tweaking the script. It saves me many hours of time and misery. For $195.00, you have returned your investment in about 2 weeks.

  • I'd agree with that. Instead of saying SQL Compare (which I like), just say diff tool. What developer doesnt have at least one around? Hard to argue that its not worthwhile to have a tool to do a quick diff - generating change scripts is gravy!

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • We have the following separate SQL Server environments:

    ·Development

    ·Integration

    ·Staging

    ·Production

    We do not allow anyone other than our DBA’s to modify the structures within the database. We rarely allow developers to write their own stored procedures.

    We try to follow the same procedures we use to for application code with database code and schema changes. This works beautifully for stored procedures (which are code.) The problem comes in with tables and other schema objects.

    We store everything in Visual Source Safe.

    We check out the items being changed and if the are to be included in the release they are checked back in (and added to a list in our bug tracking software.) If a table is to be altered, we write a script that does just that and add it to the list in our bug tracking software.) We extract (from VSS) all of the items for the release based on the bugs included in that release and place them in a "package" that is applied via a rather sophisticated command shell script. The release package is the only way our Integration environment is altered. (No one goes in and modifies tables or procedures.) This way, what the testers test is what is going to go to the customers Staging server (and then on to Production.)

    We use SQL Compare from time to time to audit our process by comparing each environment to its neighbor i.e.; Production to Staging, Staging to Integration, Integration to Development.

    I don’t advise anyone to look for the easier, softer way of managing this critical piece. Compare utilities are handy, but shouldn’t your database code be treated as an asset rather than an afterthought?

    If you are interested in looking at a copy of the .cmd file we use to apply the release let me know.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply