SQL Server環境準備

3.1 確認當前使用的資料庫版本

同步來源資料源的資料至去向資料源之前，需要確定 SQL Server 版本為資料管道支援的版本。

資料管道支援讀取的 SQL Server 資料庫類型及版本如下表所示：

資料管道支援寫入的 SQL Server 資料庫類型及版本如下表所示：

可以透過如下語句查看資料庫的版本。

select @@version

3.2 讀取資料庫開啟CDC

3.2.1 開啟 Sql Server 資料庫代理服務

需要確定 SQL Server Agent 為運作狀態。

檢查方法：使用 sysadmin 角色的使用者執行 EXEC master.dbo.xp_servicecontrol N'QUERYSTATE', N'SQLSERVERAGENT'; 並確定結果為 Running，則標識SQL Server Agent 運作中。

若結果為 Stopped，此時需要使用 sysadmin 角色的使用者開啟資料庫代理服務。

介面操作方式：

在開始選單中，找到 Microsoft SQL Server >配置工具，然後開啟SQL Server 配置管理器。

在 SQL Server 配置管理器中，展開服務，然後按SQL 代理。在結果窗格中，右鍵按啟動，如下圖所示：

SQL Server Agent 旁的圖示上和工具欄上的綠色箭頭指示 SQL Server Agent 已成功啟動，如下圖所示：

3.2.2 開啟 CDC 變更資料擷取

開啟資料庫 CDC

使用命令開啟：

use 資料庫名稱
go
EXEC sys.sp_cdc_enable_db
go

注：其中資料庫名稱是要啟動增量複製的資料庫。

使用命令檢查是否已經開啟CDC：

SELECT name, database_id, is_cdc_enabled 
FROM sys.databases WHERE name ='資料庫名稱'
go

注：其中資料庫名稱是要啟動增量複製的資料庫。

開啟資料表 CDC

注：對資料庫開啟 CDC 後，需要對資料表也開啟 CDC，否則無法進行資料管道任務配置。

使用命令開啟：

use 資料庫名稱
go
EXEC sys.sp_cdc_enable_table
@source_schema = N'Schema',
@source_name = N'Table',
@role_name = N'Role'
go

注：Table是資料表的名稱(沒有 schema )。Role 是可以存取更改資料的角色。資料管道功能需要使用 SQL Server 資料庫 public 資料庫角色 中的 db_datareader 使用者。

例如，schema 模式為 dbo，則範例如下：

use Roxy
go
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'test',
@role_name = N'public'
go

如下圖所示：

使用命令檢查是否已經開啟CDC：

use 資料庫名稱
go
SELECT name,is_tracked_by_cdc
FROM sys.tables
WHERE name = N'table'
go

3.3 確認資料庫帳號權限

需要規劃一個可存取資料源的帳號，用於後續資料管道程式中存取資料源並進行資料提取、寫入的同步操作。

建立帳號操作詳情請參見： 建立Sql server 帳號

建立使用者範例語句可參考：

use your database;
create login test with password='your password';
go
create user test for login test;
go

注：test 為帳號；your password 改為使用者實際的資料庫密碼。

此時即可使用該使用者配置資料源，如下圖所示：

3.3.1 角色要求

資料管道功能需要使用 SQL Server 資料庫 public 資料庫角色 中的 db_datareader 使用者。

例如可使用以下命令將使用者新增到固定資料庫級角色 db_datareader。

use your database;
go
exec sp_addrolemember 'db_datareader','your user'
go

3.3.2 權限要求

資料管道需要 SQL Server 資料庫使用者擁有資料源表的 SELECT 權限、INFORMATION_SCHEMA 的 SELECT 權限、sys 的 SELECT 權限。

• 對需要進行同步的表賦予SELECT權限

use your database;
grant select on OBJECT::your schema.your table name to test;

// 假設database是master，schema是dbo，表名是users，範例：
use master;
grant select on OBJECT::dbo.users to test;

注：test 為帳號。

• 對 SCHEMA 下面所有表賦予SELECT權限

use your database;
grant select on SCHEMA::your schema to test;

// 假設database是master，schema是dbo，範例：
use master;
grant select on SCHEMA::dbo to test;

注：如果 capture instance 指定了角色（role），該角色需要包含當前使用者。

注：test 為帳號。

• 增量同步，需要對名為cdc的schema，賦予SELECT權限

-- 增量權限
use your database;
grant select on SCHEMA::cdc to test;