Wednesday, March 30, 2011

Source Code Control - SQL Server/Business Intelligence Stack



    One of the items weighing on my mind is that I have not introduced a true source code control process for my team working primarily with the SQL Server business intelligence stack. A source code control process is not need on this team to 'control' or 'audit' the work among the team, but provide some basic tools for working together:





  1. The ability to work on source code, being confident you are working against the latest and greatest version your team has been able to put together.


  2. The ability to develop source code, and review your work for quality before contributing to the team, and be confident your work will not accidentally override another, or be overridden


  3. The ability to review changes history on code regardless of author, to investigate behaviour at past point and/or rule out "code changes" as the cause of a problem


  4. Current methods



  5. All dev staff RDP into the development server to work on core, server-side projects - especially SSAS and core SSRS


  6. A few of us tend to work locally on SSIS, SSRS, and SQLscripts, then use sync toy to move files up to a file server… use synctoy on the development server to sync files from the share drive down


  7. All staff work on SQL scripts from their own machines, and kind of hope we don't collide with each other




  8. Based on inquiring in and around my organization, and also bringing it up during 'group therapy' session at the Sacramento SQL Server User Group, options included Visual Source Safe, CVS (which based on rudimentary research, actually SVN would be preferred), and Git, which I've been reading about mostly from the open source world. Upon researching Visual Source Safe, learned this old file-based standby is being deprecated by Microsoft in favor of their true server product - Team Foundation Server)



    Alternatives



    Visual Source Safe





























    AdvantagesDisadvantages


    Integration with Visual Studio (for SSIS, SSRS and SSAS)



    License cost/ Procurement process required



    Uses existing file server



    Requires a checkout-edit-checkin workflow





    Is actually in de-support by Microsoft in favor of Team Foundation Server (TFS)





    SVN (client-side tool - TortoiseSVN)

































    Advantages



    Disadvantages



    Free/Open Source



    Lack of integration with Visual Studio (for SSIS, SSRS and SSAS) - 3rd party tool available for $49/seat



    Supports an Edit-Merge-Commit workflow





    Uses existing file server





    http://www.codeproject.com/KB/dotnet/SourceControl_VSNET.aspx





    Git (client-side tool - mySysGit)







































    Advantages



    Disadvantages



    Free/Open Source



    Lack of integration with Visual Studio (for SSIS, SSRS and SSAS)



    Supports an Edit-Merge-Commit workflow





    Uses existing file server?



    Appears to be more geared to storing a project on the open internet (i.e. http:)





    Focuses a lot more on branching and merging - good for bazaar-type development, but not as much value for a small focused (relatively) team





    Team Foundation Server

































    Advantages



    Disadvantages



    Integration with Visual Studio



    Not currently available in our organization. Another shop is evaluating it - need to assess and leverage their efforts, as well as determine licensing



    Other dev lifecycle tools





    Integration with SharePoint