打通自動化雲端部署到地端-自動化建立Database版本差異化Script

基於雲到地部署,除了我們在Application能做到CI&DI的流程,現在連Database也是可以做到這個流程,不過,在實務上不太可能讓開發人員自動化去更新資料庫,尤其在正式環境下更是不可能,畢竟異動資料庫的風險是很大的,所以,一般保險作法可以產生要部署的SQL Script提供給DBA確認後,再做部署的動作

要產生部署的SQL Script不是很容易嗎?是的,不過當你從開發到真正要上線的時間可能並非很短暫,且每次更新原本的Script又怎能保證在部署階段時候,不會遺漏掉有哪些Store Procedure或是View之類的忘記更新呢!所以,透過自動化方式與正式環境DB比較這是最保險的,可避免遺漏部分

準備事項


如果要做到這一步份,並非隨便就可以把產生差異化的DB Script給自動化,必須要有幾個前提須先完成

  1. DB設計與開發都必須做版控並納入VSTS內
  2. 使用SQL Project進行開發
  3. 能產生.dacpac檔案
  4. 地端的Agent那台Server必須安裝好SQL Server Data Tools (SSDT),這樣才有sqlpackage.exe程式
    這是前期必須要準備好的,如果已經準備好這樣的環境,後面的CI就可以開始做了

    Build SQL Project


首先要必須要SQL Project編譯成.dacpac檔案,所以,必須先把專案給編譯過一次才行,跟編譯.NET程式的流程是類似的。

建置SQL Solution其實只有三個步驟就可以,而這邊只需要使用MS Buid,而裡面設定基本上只要用到基礎值就可以

  • 第一步驟使用Ms Build,把專案編譯成.dacpac

Source地方請務必選擇專案的.sln檔案的位置,如果有用到Nuget記得要Restore一下

  • 第二步驟也只是把編譯好的.dacpac搬到要發佈的目錄

  • 第三步驟發佈.dacpac

基本上這三步驟就完成建置,不過,今天如果你一個.sln檔案裡面不只一個SQL Project,且可能會使用到Nuget Package,就必須多一個步驟Nuget Restore,不能在Visual Studio Build內去還原Nuget Package,因為在Visual Studio Build內只能選擇某一個SQL Project的.sqlproj檔,在.sqlproj檔案並沒有讓你取得Nuget Package資訊,所以,執行nuget restore就會發生錯誤。

且如果要使用地端Agent部署,請改用地端的Agent

產生差異化SQL


建置部分是比較簡單的,再來就是在Release地方去執行產生差異化指令的程序。在這邊,必須有幾樣事情需要先定義

  1. 定義Source資料夾,這是要存放Build出來的.dacpac
  2. 定義Target資料夾,這是存放與正式資料庫的.dacpac
  3. 定義Diff資料夾,這是存放差異化Script的.sql
    然後,定義Release流程
  4. Copy build .dacpac到Source資料夾
  5. 與Target比較,如果第一次做比較則需要先把一份正式資料庫的.dacpac放在此資料夾,再做比較
  6. 產生差異化的script放到Diff資料夾
  7. 如果執行差異化的script在正式資料庫後,DBA審核後無誤再去同意把Source內的.dacpac檔案覆蓋到Target資料夾
  • 第一步驟就是從Source Folder把$(System.DefaultWorkingDirectory)內的.dacpac檔案複製一份到Source資料夾,就是設定中的Target Folder

  • 執行地端的sqlpackage進行差異化比較,選用Command Line Task

這邊最麻煩就是如何去執行SQLpackage指令,要參考其指令可以看下面網址
https://msdn.microsoft.com/zh-tw/library/hh550080(v=vs.103).aspx.aspx)

Tool部分是去找到sqlpackage.exe.目錄位置,位置可以參考

1
"C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\sqlpackage.exe"

Arguments則是擺放sqlpackage指令,目前我採用指令如下

1
/action:Script /q:true /sourcefile:"D:\DataBase_dacpac\xxx\Source\Source.dacpac" /targetfile:"D:\DataBase_dacpac\xxx\Target\Target.dacpac" /targetdatabasename:XXX /outputpath:"D:\DataBase_dacpac\xxx\Diff\xxxDiff.sql" /p:ScriptDatabaseCompatibility=false /p:ExcludeObjectTypes=DatabaseRoles,RoleMembership,Rules,Audits,Credentials,DatabaseAuditSpecifications,ServerRoleMembership

裡面有用到比較重要的指令分別是

  • /p:ScriptDatabaseCompatibility : 指定當您發行至資料庫時,應該忽略或更新資料庫相容性的差異
  • /Quiet:{True | False} : 指定是否隱藏詳細的意見反應。預設為 False。
  • /p:ExcludeObjectTypes : 為了避免產生過多不必要的Script,這邊可以設定要忽略的DB物件,讓他們不要去跟正式資料庫比較,因為有些物件可能是屬於DBA專用,開發人員不太會去碰觸到

另外,在environment設定部份,兩個Task有一點不同,不同點在於事件的Trigger部分

  • Different SQL Task

  • TargetDB Task

因為產生的差異檔是本身是可以透過SQLcmd去執行,所以,如果這個Script檔案在SSMS內執行,必須啟動SQLcmd模式才可以


透過這種方式就可以讓SQL Project也可以自動產生差異化檔案給DBA做部署