{"id":26275,"date":"2018-06-21T19:26:00","date_gmt":"2018-06-21T19:26:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/premier_developer\/?p=26275"},"modified":"2019-02-14T20:17:59","modified_gmt":"2019-02-15T03:17:59","slug":"sql-server-objects-as-software-artifacts","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/premier-developer\/sql-server-objects-as-software-artifacts\/","title":{"rendered":"SQL Server Objects as Software Artifacts"},"content":{"rendered":"

This post is provided by Premier Field Engineer JJ Jacob<\/a> who takes a look at the benefits of SQL Server Objects as software artifacts.<\/p>\n

\n

A recent customer engagement revealed several pain-points.  One of them is the issue of not being able to pinpoint the exact database schema the development team delivered to the production team. <\/em><\/p>\n

The issue is about pointing to the one version of truth with regard to SQL deliveries into production.
What compounds this issue is that software delivery is made through updates to a file share, which is prone to the following risks:<\/p>\n

    \n
  1. Files are overwritten\n
  2. There is not auditing or traceability as to who made the changes.\n
  3. Furthermore, there\u2019s no traceability as to which bug or feature the changes were made.<\/li>\n<\/ol>\n

    We started thinking as to what technology could address this. Two issues we needed to solve were:<\/p>\n

      \n
    1. Making the Database schema an artifact of version control \u2013 this could be easily solved by Team Foundation Server as the repository\n
    2. The bigger problem was to get a representation of the schema into a set of version-controlled artifacts.<\/li>\n<\/ol>\n

      \"image\"<\/a>We find that Visual Studio along with SQL Server Data Tools, works very well with Team foundation Server\u2019s version control system.<\/p>\n

      With this approach we were able to demonstrate:<\/p>\n

        \n
      1. Visual Studio\u2019s capability to create a SQL solution, and import a database representation of it\u2019s objects into *.sql file\n
      2. Checking in that solution file into version control and tracking changes and comparisons between versions.\n
      3. Comparing versions using schema compare between the solution and the live DB, or between schema snapshots\n
      4. Ability to publish or deploy to Test\/Dev environments.<\/li>\n<\/ol>\n

        The other value prop we realized was that DBA\u2019s prefer to wok on SQL Server Management Studio, making changes to a DEV database. At the end  of the day, the Lead DBA had the capability to import the final DEV DB and check the schema changes in.<\/p>\n

        Visual Studio along with SQL Server Data Tools, in tandem with Team Foundation Server enables this scenario of tracking SQL schema as software artifacts \u2013 leveraging capabilities that are in-built in Visual Studio, SSDT and TFS. <\/p>\n

        \n

        Premier Support for Developers<\/strong><\/a> provides strategic technology guidance, critical support coverage, and a range of essential services to help teams optimize development lifecycles and improve software quality.  Contact your Application Development Manager (ADM) or email us<\/a> to learn more about what we can do for you.<\/p>\n","protected":false},"excerpt":{"rendered":"

        This post is provided by Premier Field Engineer JJ Jacob who takes a look at the benefits of SQL Server Objects as software artifacts. A recent customer engagement revealed several pain-points.  One of them is the issue of not being able to pinpoint the exact database schema the development team delivered to the production team. […]<\/p>\n","protected":false},"author":582,"featured_media":37840,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[8],"tags":[10,348,3],"class_list":["post-26275","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data","tag-sql","tag-ssdt","tag-team"],"acf":[],"blog_post_summary":"

        This post is provided by Premier Field Engineer JJ Jacob who takes a look at the benefits of SQL Server Objects as software artifacts. A recent customer engagement revealed several pain-points.  One of them is the issue of not being able to pinpoint the exact database schema the development team delivered to the production team. […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/26275","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/users\/582"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/comments?post=26275"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/26275\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media\/37840"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media?parent=26275"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/categories?post=26275"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/tags?post=26275"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}