資料層應用程式指的是Data-tier Application(簡稱DAC), 這是與微軟SQL Server有關的一項封裝功能, 可詳見官方資料層應用程式一文介紹, DAC內可包含與SQL Server資料庫有關的物件, 如資料表、檢視表、預存等等。
在我的理解, 一般你可以透過SQL Server Management Studio(SSMS)匯出指令碼, 把所有物件匯出成一個大檔, 或是一個物件一個檔, 這個檔案慣例是用副檔名.sql來表示, 這就像是用文字檔般的來使用它; 但是DAC, 副檔名為.dacpac, 相當於是把前者打包封裝, 或說是壓縮成單一檔案, 主要可做為部署或是開發使用。
我常處理資料庫, 將資料庫schema整理進版控系統中, 配合Visual Studio工具以及SSDT, 以 SQL Server資料庫專案 來建立管理方案。不過, 一直以來我都有個困擾, 因為系統上的資料庫不只單一個, 有些系統因存在多年, 有無數的需求建構在其上, 又因多年來有不同人接手規劃, 還有一些莫名的堅持, 所以就產生了多個資料庫, 而資料庫彼此之間也產生了相互連結, 不管是在檢視表、預存, 甚至是跨SQL Server Instance之間的連結, 而更進一步地, 這些連結甚至會發生在不同資料庫之間循環參照的情況。
講這麼多, 這有什麼關係嗎? 假設我將多個資料庫都整理到 SQL Server資料庫專案 的方案中, 以一個專案對應一個資料庫來存放。其實這種專案也是可以做編譯的, 編譯時就是在檢查每個物件的語法正確性, 另外也會檢查每個物件的連結正確性, 不過這裡當然不是真的連到SQL Server上去檢查, 而是就語法面來判別。當有跨資料庫的連結要檢查時, 就是以 加入資料庫參考 的方式來處理, 而這裡一個好用的方式就是使用已存好的.dacpac來代表外部連結的資料庫。
如果你可以編譯成功, 那專案的\bin目錄下就會產生出這個資料庫的.dacpac檔, 這時就可以拿到需要參考這個資料庫的其他資料庫專案中使用。有這麼簡單嗎? 就像前面說的, 我遇到的資料庫不少, 又都有跨資料庫連結, 甚至有循環參照連結, 這時, 你會發現你的專案永遠編譯不成功, 不管從哪一個資料庫著手, 總是會碰到在某一個就卡關了。
於是我想從SSMS上的 擷取資料層應用程式 直接產生, 不過在此介面上能設定的資訊實在少的可憐, 猜想是微軟在這裡根本不想提供進一步的設定功能, 以至於到了SSMS 18.4版仍是如此。最重要的是, 在此仍是無法成功產生我要的檔案。到此為止, 就是一直困擾著我的問題, 每每都是要調整好久, 而且都要做些schema上臨時性的變動, 才能暫時讓我產出一版.dacpac出來。
終於, 今天讓我找到一個方法, 順利解決我的困擾, 這應該也已存在許久, 只是我一直都不曾發現而已。只要改用SqlPackage命令列公用程式就可以順利的產生我要的檔案, 可在Download and install sqlpackage下載程式, 說明則可參考這裡。先看一下實際指令:
C:\Program Files\Microsoft SQL Server\150\DAC\bin>sqlpackage /a:Extract /sdn:Northwind /ssn:(local) /tf:"C:\temp\Northwind.dacpac" /p:ExtractApplicationScopedObjectsOnly=True /p:ExtractReferencedServerScopedElements=False
產生的結果如:
正在連接到伺服器 '(local)' 上的資料庫 'Northwind'。
正在擷取結構描述
正在從資料庫擷取結構描述
正在解析結構描述模型中的參考
已成功擷取資料庫並儲存至檔案 'C:\temp\Northwind.dacpac'。
經過時間 00:00:12.03
這裡以Northwind資料庫為例, 雖然它是單一資料庫, 沒有跨資料庫連結的內容, 但這個範例我測試過確定足以應付這個需求。指令中最重要的是其中/p:的屬性設定, 可以參考以上的說明連結文章
- /p:ExtractApplicationScopedObjectsOnly=True, 是指只擷取指定之來源的應用程式範圍物件。我的認知是這表示只擷取指定資料庫本身範圍內的物件。
- /p:ExtractReferencedServerScopedElements=False, 是指不要擷取來源資料庫物件所參考的登入、伺服器稽核及認證物件。這裡我的認知是指不要擷取外部連結的資料庫資訊。因為外部連結還有外部的外部連結, 若都去擷取, 大概太複雜又緩慢, 所以就不要去擷取。
大功告成!
2020/10/20 更新:
sqlpackage的執行預設使用命令列提示字元工具, 我使用PowerShell時是有錯誤的。這工具支援參數也不少, 可以用 /? 自行查詢, 例如:
/SourceUser:<userid> , 這可用於存取遠端DB, 縮寫 /su:/SourcePassword:<pwd>, 這和上面的搭配使用, 縮寫 /sp:
Jason's buzz 