Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Unanswered: Maintaining a test/production enviroment

    We are a small company, just converting everything over to SQL Server 2005 in the past couple years. I'd like to maintain a test SQL Server and a production one. What tools can I use to make this easier than brute force way I'm doing it now (writing down the changes and then implementing them on the production side).

    Additional info:
    crew of 2 programmers, 20+ yrs experience between us (Paradox, Access, Informix, ASP, VB, HTML, etc.)
    SQL Server is our first real enterprise size database we are administrating
    Servers are on 2 seperate but comparable boxes
    Writing SQL from scratch would not be a problem.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    if you can afford it, Redgate SQL Compare is just wonderful.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Thrasymachus
    if you can afford it, Redgate SQL Compare is just wonderful.
    +1

    Also, I've been working recently with Subversion. It's an open source version control package. You can download a version of it for Windows from www.visualsvn.com. No, I don't work for them (or for Redgate either).

    But I like what I see.

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    VSTS Database Edition is worth a look, especially if you use Visual Studio or TFS for development:
    http://msdn.microsoft.com/en-us/vsts.../bb933747.aspx

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dportas
    VSTS Database Edition is worth a look
    This'll be interesting....

    hmscott - I briefly looked at subversion but wasn't mad keen. You've been working with it long enough to recommend it?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I've used VSTS Database Edition and its previous version (aka "Data Dude") on various projects over the last couple of years. Its strength is its tight integration with Team Foundation Server and Visual Studio. If you use those tools and you want to manage database releases as one part of a bigger development and release process then Database Edition is an obvious choice.

    The database compare feature of Database Edition is very similar to Red Gate SQL Compare (Microsoft licenses the technology from Red Gate). If you want to do stand-alone database config management and ad-hoc comparisons then Red Gate is a cheaper and simpler option.

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    This'll be interesting....

    hmscott - I briefly looked at subversion but wasn't mad keen. You've been working with it long enough to recommend it?
    I like it well enough now. I had a bear of a time getting it to work with SSPI (Windows integrated authentication).

    I am using:
    Visual SVN 1.6.1 (core is SVN 1.5.3, I think)
    TortoiseSVN 1.5.4
    VisualSVN 1.5.3 (trial version, I haven't committed to purchasing yet)

    Server is Windows 2003, SP2

    The basic VisualSVN install is very easy and very straightforward. Like I said, getting it to integrate with SSPI was a bear, though I got it to work after some trial and error. We're not doing anything fancy, we just needed a place to store scripts and keep track of changes. For that I think it works well enough.

    I wonder how well it would work for large environments with many developers. We have only two people in it right now with two or three more to add in the next year.

    Regards,

    hmscott
    Have you hugged your backup today?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Writing down the changes and then implementing them on production???

    You should have THREE environments, minimum:
    1) Development
    2) Test
    3) Production

    You should also consider having a QA environment and a Staging environment.

    All changes should be scripted as releases. The scripts should pass successfully through Test, QA, and Staging before being applied to production.
    If it turns out a script needs to be modified, it goes back to Test and starts again.
    Staging is always refreshed from Production before any script is applied to it.
    QA should be refreshed from production frequently.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    blindman,
    You should have THREE environments, minimum:
    1) Development
    2) Test
    3) Production

    You should also consider having a QA environment and a Staging environment.
    Can you describe in what way the Test, QA and Staging environments differ?

    I totally agree about your description about script-only changes. Manually changing databases is too error-prone, not-reproducible, ...

    Doing frequent restores of the production database to the Development and Test database ensure you won't get any nasty surprises - and no time to deal with them - the moment you have to apply the change scripts on Production.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Test is for developers to test their changes. It is appropriate when developers work primarily on local copies of the database.
    QA is "Quality Assurance", where a separate team (NOT the developers) test the application for defects. Often, client representatives are part of this team.
    Staging is a very recent copy of the production database where scripts are applied as a final check before being executed on production.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    If you practice continuous integration then add a Build environment to that list (for automated build and test).

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think that would be the same as the "Test" environment I suggested. That is pretty much the way we use it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Mar 2006
    Posts
    2
    Thanks for the suggestions guys, I'm definitely looking at red-gate and VisualSVN.

    I understand the importance and reasoning behind the test/dev/prod system, I'm the one pushing it here, but it's a really small business and spending the cash without a quick return can be hard to get past the people with the money sometimes.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Test, dev, QA etc can be on the same box so it is more a question of procedures than cash.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •