Migrating Database Objects

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jyao/migratingdatabaseobjects.asp

  • You make your life so hard!

    To me everything starts and ends with source control. The developers develop it, I audit it and deliver it. Continuous code builds ensure that the developers don't check in code that breaks existing code. Code deployments are tested on copies of the target ensuring the script is faultless and all of this is automated as I use DB Ghost. Fair enough (to any sceptics) that I built the thing - but I know it works and so does my growing customer base like Dell, AGFirst, Lloyds etc.

    And it complies with rule number one and two.

    regards,

    Mark Baekdal

    http://www.dbghost.com

    http://www.innovartis.co.uk

    +44 (0)208 241 1762

    Build, Comparison and Synchronization from Source Control = Database change management for SQL Server

     

     

     

  • I agree - with DB Ghost handling the building of our databases and also the compare and upgrade as well Rules 1 and 2 are completely adhered to - Rule 2 because we are fully utilizing the power of our source control system to realize the benefits of our developers teamwork.

    They don't waste any time using this method and neither do I thus leaving more time to get an even greater understanding of SQL Server - a real win-win!

    Malcolm
    DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
    www.dbghost.com

  • We use Harvest as our source control.  Our approach is as follows:

    1. Developers develop code and place it in Harvest (Dev env.)
    2. Code is migrated to Test and tested by QA team.  They also promote the code to Test env. in Harvest.
    3. Developers build install BAT files that install into Dev|Test|Staging|Production env.  These are placed in Harvest and migrated the same as code.
    4. After the install scripts are tested by Dev|Team members the code and install BATs are promoted to Staging (BTW install BATs use osql and produce error logs).
    5. DBA examines the scripts/bat files for anything incorrect.  They then run the BAT file and report errors.
    6. QA\UAT\performance testing is done in Staging with DBA assistance, Profiler, etc..
    7. Code is promoted to Prod and DBA examines the BAT file and runs it, reports errors.

    The process for DBA to install code (minus backups CYA) is about 15 - 30 minutes.  All code migrations are done this way and it proves to be quite handy.



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • Hi everyone,

    All excellent comments. I once used both Harvest and VSS, and I agree with you all the source control is absolutely the right way to solve most of the problems. But on the other hand, I also worked for some companies / government agencies, who have "strange rules" that only load the source codes / products into VSS / Harvest once those codes have passed the UAT and move into production. I complained but to no avail.

    On the other hand, to persuade some CTOs to budget for third party tools is morre difficult than you can imagine. Personally, I always use what are already inside SQL Server to better sell my 'cheaper' solutions.

    But seriously, I find my approach has a much better advantage in terms of doucmenting purpose. In real world, each sheet file is dedicated for one product/database migration objects list. It is really simple and straight-forward compared with finding out database objects through source codes with thousands of lines.

    I welcome more comments.

  • there's a saying that goes "why code when you can download"

    Re-inventing the wheel is more costly than buying a well tested product that solves the problem.

    But seriously, I find my approach has a much better advantage in terms of doucmenting purpose. In real world, each sheet file is dedicated for one product/database migration objects list. It is really simple and straight-forward compared with finding out database objects through source codes with thousands of lines.

    DB Ghost does this - you don't have to, that's the point.

     

     

    regards,

    Mark Baekdal

    http://www.dbghost.com

    http://www.innovartis.co.uk

    +44 (0)208 241 1762

    Build, Comparison and Synchronization from Source Control = Database change management for SQL Server

     

     

     

  • Hmmm, it seems I need to try the dbghost next time since so many people recommend it...  but isn't it too "eye-stressing" if you have to pickup various objects through GUI interface from thousands of different objects? and is there any team collaboration work with dbghost? (Excuse me if my questions are naive as I never use dbghost before) 

    As said before, my current philosophy is to use what is available and do what I can do to benefit both my employer and my own career. To me, my approach is simple and useful, once my "migration engine" is done, I never have to change it unless I need to add new protocols.

    But thanks Mark for ur comment, I will play with dbghost in the weekend and see what advantages I can get. But I have a feeling if I add a simple GUI interface to my "migration engine", I may make this a simple and flexible tool.

  • DB Ghost is best used to allow easy collaboration between developers during the development phase and easy reporting and propagation of changes directly from any source control system.

    For the developers it is easy to keep their dev databases in sync with source control and for DBAs it is easy to accumulate all of those changes (without having to keep any separate documentation up to date) by simply building and deploying what is in source control.  If any reporting of what schema or static data objects have changed is required you simply use the reporting and diff functions built in to every source control system.  For example you can run reports that give you 'all of the objects that changed between build X and build Y' or 'what objects have been changed as part of new feature X and by whom'.

    It reduces the mundane workload for developers AND DBAs, simple as that.

    Malcolm
    DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
    www.dbghost.com

  • Based on your saying, it seems that we can take Better advantage of dbghost only if source control system exists. But in real world, this may not be true sometimes.

     

    I’d like to raise the following questions / scenarios for your considerations:

    1. Can dbghost migrate some tables with some of the records, say for sales table, I need to migrate the sales table together with top 10 largest transactions in each year? For other tables, I may / may not need to populate them with specific business rules?
    2. How can I change the job schedule from original monthly running to weekly running during the migration?
    3. Can you show to your clients what you will do upfront with a simple and clear document so everyone can understand and make a comment? (I know dbghost can generate a report later, but to me I need the report first to get the review and approval and my client can even add their own business rule in the document, which I will use to generate my migration scripts directly, and I will not change a letter.)

     Yes, for 1 & 2, we can have additional scripts to achieve the purpose but that's not what I want.

    Saying all these, I think maybe dbghost does not have a similar target function space as my approach tries to achieve. I’d say my approach has the limit as far as VBScript can reach (to interpret the self-defined protocols)

  • Whether my view is economical or not is not important, the important thing is my approach is practical / flexible and helps me a lot in my daily work. I do not know much about your DBGhost but I may take a look sometime when I have time and discuss with you later. But if you really think my view is not important, let me tell you I am not the original creator but my clients are. To me, there is nothing uneconomical in the requirements from customers.

    On the other hand, after reading your PDF document, I would say probably you do not understand my article fully. If you know how important it is to be able to customize a migration protocol to fit various scenarios / requirements, you may consider to make some improvements to your DBGhost in future.

    BTW, I welcome constructive suggestions and discussions but do not expect to hear the words in the tone of teaching a pupil, such as

    "Learn & try, a closed mind is a useful as a dead one in a ever expanding stage..."

  •  

    maybe I can learn something and my apologies if I sounded condescending

    regards,

    Mark Baekdal

    http://www.dbghost.com

    http://www.innovartis.co.uk

    +44 (0)208 241 1762

    Build, Comparison and Synchronization from Source Control = Database change management for SQL Server

     

     

  • Never mind, Mark, I love SSC because I can meet many talented peers here and share with my ideas and learn from their experiences.

Viewing 12 posts - 1 through 11 (of 11 total)

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