Post reply

Deploying DB changes

  • September 2, 2008 at 3:21 pm

    #197257

    I'm pretty new to deploying indexes in 2005. As a general rule, when you deploy db changes to production (in our case now, of adding indexes to some tables...) how do you deploy them?

    When we added the indexes in DEV, the indexes were applied manually by the developer. A DBA with access to production will actually make the deployment.

    If you were the deploying DBA, would you do this manually (as we did in DEV) or write a script to do this? Or will 2005 automatically generate a script for this like it will for generating a CREATE script?

    I'm also open to being pointed to good references on the internet on this topic.

    Thanks in advance,

    Bob

  • September 2, 2008 at 3:26 pm

    #866248

    Yes, you can use SSMS in 2005 to generate scripts for indexes. I always recommend having these things scripted out and testing those scripts prior to putting them into production. Its a great practice to make sure that things are going to work right when it gets to where it counts. Additionally, as a DBA who is sometimes releasing things that I did not write, it is nice to have something prepared AND TESTED given to me so that there can be some confidence in what I am about to run.

    One final thought to this, scripting the index, and other changes for that matter, allow for saving scripts in source safe or some similar product as a method of version control.

    I realize there are balances to process and practice but repeatability can't be overemphasized.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • September 3, 2008 at 3:50 am

    #866397

    If you have Visual Studio DB Pro edition, you can download the free powertools add on which gives you the facility to compare two databases and generate scripts to deploy differences/changes from one to the other. Redgate SQL Compare also does the job (better from what I've seen of the eval)

  • September 3, 2008 at 6:49 am

    #866450

    I'll pile on and suggest you have a Staging server that mirrors your Production environment (not necessarily in terms of memory & processing power, but certainly disk space) where you keep a copy of the Production database. Test your scripts there. Make any and all adjustments to the scripts and retest them, again in the Staging area. Then, when they run flawlessly in Staging, without editing a thing, run them in Production.

    I've been doing deployments this way for about 10 years and it's saved my behind more times than I care to think about.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 4, 2008 at 12:14 am

    #866958

    Scripting is the best way to keep the updates perfect. while deploying updates on production database this helps to to deploy the exact changes. what we prefer is alway deploy the script on the production database but before that must verify the script to make it error-less. test script before you deploy it to production database. this will also help you to keep track of hot-fixes to have deployed on production database.

    Abhijit - http://abhijitmore.wordpress.com

  • September 4, 2008 at 12:29 am

    #866965

    Have to agree with David and Grant. Scripting is the way to go. This is the way i do it

    DevDb -> TestDb -> StagingDB -> Production db.

    I have been surprised by the (few) number of errors got at "staging db" level. Most important benefit - no sleepless nights. 😀

    "Keep Trying"

  • September 4, 2008 at 8:12 am

    #867164

    I'm convinced that scripting is the way to go with this. Now my question is how to do this. (Not how to do it from scratch, but a specific question re: the scripting wizard.)

    I'm using the SQL 2005 wizard to do the scripting. I have the option to script all db objects or narrow it down to scripting only the indexes. (The indexes have been applied in dev environment.) Is it sufficient to script only the indexes and then run that against the db in our UA environment or do I have to script the entire db?

    Thanks for the assistance!

    Bob

  • September 4, 2008 at 1:30 pm

    #867395

    Actually, I have figured out the scripting of the indexes but I have one last question (procedure-wise). We use Source Gear for version control of tables, stored procs, etc... does your shop keep track of indexes in a version control system?

    Thanks again,

    Bob

  • September 8, 2008 at 5:52 am

    #868350

    We keep track of every object in the database within source control.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 9, 2008 at 9:00 am

    #869109

    We use Surround to keep all the Objects from the DB in source control. We also make sure that we add scripts to any manual Data Change for tracking.

    -Roy

Viewing 10 posts - 1 through 9 (of 9 total)

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