微軟給我們提供了一種非常好用的資料庫遷移方案,但是我發現周圍的同學用的并不多,是以我還是想把這個方案整理一下。.NET選手看過來,特别是還在通過手工執行腳本來遷移資料庫的同學們,當然你也可以選擇EF的Migration方案和FluentMigrator,但是下面我介紹的這種方案符合我對團隊協作的所有要求,對開發者而言使用起來非常友善,不容易犯錯。
一、方案目标
一個好的資料庫遷移方案在我看來需要滿足以下條件:
1、适用于每個開發者擁有自己獨立的資料庫開發環境,用于不同feature的并行開發
2、能夠配合版本控制工具,不同的版本能夠友善合并和易于解決沖突
3、資料庫開發環境要易于在不同的版本之間切換
4、易于跟CI工具內建,不同的開發環境(Dev,QA,Staging,Product)能夠部署不同的資料庫開發環境
5、DBA能夠友善稽核開發人員送出的資料庫腳本
6、整個資料庫的遷移過程由腳本自動化完成,不應該有人工幹涉
二、準備
假設我們有一個資料庫blog,該資料庫中包含一個表Users,資料庫初始腳本:
Create Database Blog
GO
USE [Blog]
GO
/****** Object: Table [dbo].[Users] Script Date: 2016/7/31 17:18:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](200) NULL,
[Email] [nvarchar](100) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
如圖所示,我們得到了一個初始的資料庫版本:
三、建立資料庫遷移解決方案
1、打開vs, 我用的是vs2015
2、如圖所示,建立工程
3,在Blog.Database工程,右鍵,選擇Schema Compare…
4、點選中間的“交換位置”圖示,左邊代表源(Source),右邊代表目标(Destination)。我們現在要本地資料庫把schema更新在我們建立的資料庫工程中。
5、在“源”中選擇Select source
6、按照下圖所示添加資料庫連接配接
7、Compare 然後Update,資料庫中的schema将會同步在我們的vs解決方案中
四、添加存儲過程
至此為止我們已經添加了對Blog資料庫的遷移方案,所有開發人員對資料庫的更改都要通過該解決方案來完成。
比如開發者A這時候需要添加第一個存儲過程:
1、在dbo目錄下建立Stored Procedures檔案夾
2、建立存儲過程腳本GetUser.sql
編寫以下存儲過程:
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE GetUser
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT TOP 100* FROM Users
END
腳本已經編寫完畢,這時候開發者A需要把這個更改更新到本地的資料庫中:
這時候“源”是我們的資料庫遷移方案,目标是本機的資料庫,compare然後update
以git為例,開發人員此時會把Blog.Database解決方案更改合并到develop分支,其他開發人員通過compare-update操作将别人對資料庫的更改update到本地。
五、更改表結構
開發人員B在另一個分支需要對表User添加兩列:Gender和Description,直接在解決方案中打開User表做更改
當然最後要通過Compare-Update操作将更改應用到本地資料庫,其他開發人員也會通過相同的方式将此更改應用在本地。
六、添加Reference Data
開發人員添加了一個表Gender,并且需要添加三條固定資料:
在Tables檔案夾下右鍵-Tabel-Gender
這時候需要添加三條固定資料:Male,Female,Unknown,這時候要用到PostDeploymentSql:
1、建立PostDeploymentSql
2、建立Gender.sql
3、(重要)此時要在Gender.sql右鍵,Builder Action-None,否則無法編譯
4、在Gender.sql添加下面的Sql,這個sql在每次部署的時候都要執行,是以一定是“幂等”的:
PRINT 'Beginning Deployment Gender table...'
IF EXISTS (select top 1 1 from dbo.Gender where Value='01')
update dbo.Gender set Name='Male' where Value='01'
else
insert dbo.Gender(value,Name) values('01','Male')
IF EXISTS (select top 1 1 from dbo.Gender where Value='02')
update dbo.Gender set Name='Female' where Value='02'
else
insert dbo.Gender(value,Name) values('02','Female')
IF EXISTS (select top 1 1 from dbo.Gender where Value='03')
update dbo.Gender set Name='Unknown' where Value='03'
else
insert dbo.Gender(value,Name) values('03','Unknown')
PRINT 'Finishing Deployment Gender table...'
5、在Script.PostDeployment.sql中編寫下面的腳本:
PRINT 'Running Post-Deploy Scripts'
:r .\Gender.sql
--append other sql scripts
PRINT 'End Post-Deploy Scripts'
6、Compare-Update,将此更改更新到本地資料庫
此時你會發現本地資料庫添加了Gender表,但是我們添加的三條資料并沒有進來,這是因為Script.PostDeployment.sql并沒有執行,這個腳本隻有在釋出的時候才能執行。
七、添加publish檔案
通過上面的步驟我們可以看出來,我們每次都是先更改資料庫遷移解決方案,然後通過Compare和Update操作将更新同步到本地,但是這樣操作存在兩個缺點:
1、Script.PostDeployment.sql并沒有執行,無法将ReferenceData同步在資料庫
2、隻适用于同步本地資料庫,其他環境需要采用一些自動化的方式來完成,而不是手工compare,update,避免人工操作失誤
通過下面的步驟來添加publish檔案
1、在Blog.Database工程上右鍵-publish
接下來要添加資料庫連接配接,然後添加Create Profile,最後點選publish。
通過Create Profile添加了一個xml的publish檔案,重命名為:Local.publish.xml。
我們可以通過輕按兩下此xml檔案完成對本地資料庫的publish操作
八、自動化publish資料庫遷移方案到其他資料庫環境
我們通過手工publish将更改應用到本地,但是其他環境(Dev,QA,Staging,Prod)則要通過腳本來完成。
1、在本地建立一個空資料庫Blog_QA用來模拟QA的資料庫環境
2、采用之前的步驟建立一個publish檔案,該publish檔案的資料庫為Blog_QA,将該xml檔案重命名為:Blog_QA.publish.xml
在Blog_QA.publish.xml右鍵,屬性,Copy To Output Directory:Copy Always
3、通過sqlpackage程式要遷移資料庫
運作指令行:cd 到C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin目錄
執行指令:SqlPackage.exe /Action:Publish /SourceFile:G:\SourceCode\Blog.Database\bin\Debug\Blog.Database.dacpac
/Profile:G:\SourceCode\Blog.Database\bin\Debug\Publish\Blog_QA.publish.xml
通過編寫腳本來完成不同環境的資料庫遷移。
該方案的核心在于:所有開發人員通過維護vs資料庫工程來完成對資料庫的更改,最後通過publish工具來完成資料庫遷移,同時我們可以通過sqlpackage工具來完成自動化遷移。
整個demo提供下載下傳:https://git.oschina.net/richieyangs/Blog.Database.git
由于資料庫連接配接字元串的不同,是以不能直接使用demo中的publish檔案來完成資料庫遷移。大家根據自己的情況做出修改。
作者:Richie Zhang
來源:http://www.cnblogs.com/richieyang/
聲明:本文版權歸作者和部落格園共有,歡迎轉載,但未經作者同意必須保留此段聲明,且在文章頁面明顯位置給出原文連接配接,否則保留追究法律責任的權利。