SQL Server 鏡像

標籤：des   style   blog   http   io   ar   os   使用   sp   

sql server 2005鏡像製作

 

以下是操作步驟：
-- =========================================== 
-- 無論是主體伺服器、鏡像伺服器, 還是見證伺服器 
-- 除特別說明外，均需要保證下面的操作在master庫中執行 
USE master 
GO

-- =========================================== 
--（1）  建立鏡像主體資料庫 
-- 此操作主體伺服器上執行 
-- a. 建立測試資料庫 
CREATE DATABASE DB_Mirror 
ON( 
NAME = DB_Mirror_DATA, 
FILENAME = N‘C:\DB_Mirror.mdf‘ 
) 
LOG ON( 
NAME = DB_Mirror_LOG, 
FILENAME = N‘C:\DB_Mirror.ldf‘ 
) 
ALTER DATABASE DB_Mirror SET 
RECOVERY FULL 
GO

-- b. 完全備份 
BACKUP DATABASE DB_Mirror 
TO DISK = N‘C:\DB_Mirror.bak‘ 
WITH FORMAT 
GO

-- =========================================== 
--（2）  初始化鏡像主體資料庫 
-- 此操作鏡像伺服器上執行 
-- 假設主體資料庫的完全備份已經複製到 c:\DB_Mirror.bak 
RESTORE DATABASE DB_Mirror 
FROM DISK = N‘C:\DB_Mirror.bak‘ 
WITH REPLACE 
, NORECOVERY 
-- 如果鏡像資料庫檔案要放在指定位置, 則啟用下面的 Move 選項 
-- , MOVE ‘DB_Mirror_DATA‘ TO N‘C:\DB_Mirror.mdf‘ 
-- , MOVE ‘DB_Mirror_LOG‘ TO N‘C:\DB_Mirror.ldf‘ 
GO

-- =========================================== 
--（3）  主體伺服器上的資料庫鏡像端點及身分識別驗證用的認證 
-- 此操作主體伺服器上執行 
-- a. 用於資料庫鏡像端點身分識別驗證的認證 
IF NOT EXISTS(  -- 使用資料庫主要金鑰加密認證 
SELECT * FROM sys.symmetric_keys 
WHERE name = N‘##MS_DatabaseMasterKey##‘) 
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = N‘abc.123‘

CREATE CERTIFICATE CT_Mirror_SrvA 
WITH 
SUBJECT = N‘certificate for database mirror‘, 
START_DATE = ‘19990101‘, 
EXPIRY_DATE = ‘99991231‘ 
GO

-- b. 備份認證, 以便在與此端點通訊的另一端建立此認證 
BACKUP CERTIFICATE CT_Mirror_SrvA 
TO FILE = ‘C:\CT_Mirror_SrvA.cer‘ 
GO

-- c. 資料庫鏡像端點 
CREATE ENDPOINT EDP_Mirror 
STATE = STARTED 
AS TCP( 
LISTENER_PORT = 5022,  -- 鏡像端點使用的通訊連接埠 
LISTENER_IP = ALL)    -- 偵聽的IP地址 
    FOR DATABASE_MIRRORING( 
AUTHENTICATION = CERTIFICATE CT_Mirror_SrvA, -- 憑證驗證 
ENCRYPTION = DISABLED,                      -- 不對傳輸的資料加密,如果需要加密,可以配置為 SUPPORTED 或 REQUIRED, 並可選擇密碼編譯演算法 
ROLE = ALL)                                  -- 端點支援所有的資料庫鏡像角色, 也可以設定為 WITNESS(僅見證伺服器),或 PARTNER(僅鏡像夥伴) 
GO

-- =========================================== 
--（4）  鏡像伺服器上的資料庫鏡像端點及身分識別驗證用的認證 
-- 此操作鏡像伺服器上執行 
-- a. 用於資料庫鏡像端點身分識別驗證的認證 
IF NOT EXISTS(  -- 使用資料庫主要金鑰加密認證 
SELECT * FROM sys.symmetric_keys 
WHERE name = N‘##MS_DatabaseMasterKey##‘) 
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = N‘abc.123‘

CREATE CERTIFICATE CT_Mirror_SrvB 
WITH 
SUBJECT = N‘certificate for database mirror‘, 
START_DATE = ‘19990101‘, 
EXPIRY_DATE = ‘99991231‘ 
GO

-- b. 備份認證, 以便在與此端點通訊的另一端建立此認證 
BACKUP CERTIFICATE CT_Mirror_SrvB 
TO FILE = ‘C:\CT_Mirror_SrvB.cer‘ 
GO

-- c. 資料庫鏡像端點 
CREATE ENDPOINT EDP_Mirror 
STATE = STARTED 
AS TCP( 
LISTENER_PORT = 5022,  -- 鏡像端點使用的通訊連接埠 
LISTENER_IP = ALL)    -- 偵聽的IP地址 
    FOR DATABASE_MIRRORING( 
AUTHENTICATION = CERTIFICATE CT_Mirror_SrvB, -- 憑證驗證 
ENCRYPTION = DISABLED,                      -- 不對傳輸的資料加密,如果需要加密,可以配置為 SUPPORTED 或 REQUIRED, 並可選擇密碼編譯演算法 
ROLE = ALL)                                  -- 端點支援所有的資料庫鏡像角色, 也可以設定為 WITNESS(僅見證伺服器),或 PARTNER(僅鏡像夥伴) 
GO

-- =========================================== 
--（5）  在鏡像伺服器上完成主體伺服器資料庫鏡像端點的傳輸安全模式配置 
-- 此操作鏡像伺服器上執行 
-- a. 建立主體伺服器上的認證(假設主體伺服器上備份的認證已經複製到 C:\CT_Mirror_SrvA.cer) 
CREATE CERTIFICATE CT_Mirror_SrvA 
FROM FILE = ‘C:\CT_Mirror_SrvA.cer‘

-- b. 建立登入 
CREATE LOGIN LOGIN_Mirror_SrvA 
FROM CERTIFICATE CT_Mirror_SrvA

-- c. 授予對資料庫鏡像端點的 connect 許可權 
GRANT CONNECT ON ENDPOINT::EDP_Mirror 
TO LOGIN_Mirror_SrvA 
GO

-- =========================================== 
--（6）  在主體伺服器上完成鏡像伺服器資料庫鏡像端點的傳輸安全模式配置 
-- 此操作主體伺服器上執行 
-- a. 建立主體伺服器上的認證(假設鏡像伺服器上備份的認證已經複製到 C:\CT_Mirror_SrvB.cer) 
CREATE CERTIFICATE CT_Mirror_SrvB 
FROM FILE = ‘C:\CT_Mirror_SrvB.cer‘

-- b. 建立登入 
CREATE LOGIN LOGIN_Mirror_SrvB 
FROM CERTIFICATE CT_Mirror_SrvB

-- c. 授予對資料庫鏡像端點的 connect 許可權 
GRANT CONNECT ON ENDPOINT::EDP_Mirror 
TO LOGIN_Mirror_SrvB 
GO

-- =========================================== 
--（7）  在鏡像伺服器上啟用資料庫鏡像 
-- 此操作鏡像伺服器上執行 
ALTER DATABASE DB_Mirror SET 
PARTNER = ‘TCP://SrvA:5022‘ 
GO

-- =========================================== 
--（8）  在主體伺服器上啟用資料庫鏡像(預設為高安全性模式,所以不用進行模式設定) 
-- 此操作主體伺服器上執行 
ALTER DATABASE DB_Mirror SET 
PARTNER = ‘TCP://SrvB:5022‘ 
GO

-- =========================================== 
--（9）  配置見證伺服器 
-- 此操作在見證伺服器上執行 
-- a. 完成見證伺服器上資料庫鏡像端點的傳輸安全模式配置 
-- (a). 用於資料庫鏡像端點身分識別驗證的認證 
IF NOT EXISTS(  -- 使用資料庫主要金鑰加密認證 
SELECT * FROM sys.symmetric_keys 
WHERE name = N‘##MS_DatabaseMasterKey##‘) 
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = N‘abc.123‘

CREATE CERTIFICATE CT_Mirror_SrvWitness 
WITH 
SUBJECT = N‘certificate for database mirror‘, 
START_DATE = ‘19990101‘, 
EXPIRY_DATE = ‘99991231‘ 
GO

-- (b). 備份認證, 以便在與此端點通訊的另一端建立此認證 
BACKUP CERTIFICATE CT_Mirror_SrvWitness 
TO FILE = ‘C:\CT_Mirror_SrvWitness.cer‘ 
GO

-- (c). 資料庫鏡像端點 
CREATE ENDPOINT EDP_Mirror 
STATE = STARTED 
AS TCP( 
LISTENER_PORT = 5022,  -- 鏡像端點使用的通訊連接埠 
LISTENER_IP = ALL)    -- 偵聽的IP地址 
    FOR DATABASE_MIRRORING( 
AUTHENTICATION = CERTIFICATE CT_Mirror_SrvWitness, -- 憑證驗證 
ENCRYPTION = DISABLED,                            -- 不對傳輸的資料加密,如果需要加密,可以配置為 SUPPORTED 或 REQUIRED, 並可選擇密碼編譯演算法 
ROLE = ALL)                                        -- 端點支援所有的資料庫鏡像角色, 也可以設定為 WITNESS(僅見證伺服器),或 PARTNER(僅鏡像夥伴) 
GO

-- b. 完成主體伺服器上資料庫鏡像端點的傳輸安全模式配置 
-- (a). 建立主體伺服器上的認證(假設主體伺服器上備份的認證已經複製到 C:\CT_Mirror_SrvA.cer) 
CREATE CERTIFICATE CT_Mirror_SrvA 
FROM FILE = ‘C:\CT_Mirror_SrvA.cer‘

-- (b). 建立登入 
CREATE LOGIN LOGIN_Mirror_SrvA 
FROM CERTIFICATE CT_Mirror_SrvA

-- (c). 授予對資料庫鏡像端點的 connect 許可權 
GRANT CONNECT ON ENDPOINT::EDP_Mirror 
TO LOGIN_Mirror_SrvA 
GO

-- c. 完成鏡像伺服器上資料庫鏡像端點的傳輸安全模式配置 
-- (a). 建立鏡像伺服器上的認證(假設鏡像伺服器上備份的認證已經複製到 C:\CT_Mirror_SrvB.cer) 
CREATE CERTIFICATE CT_Mirror_SrvB 
FROM FILE = ‘C:\CT_Mirror_SrvB.cer‘

-- (b). 建立登入 
CREATE LOGIN LOGIN_Mirror_SrvB 
FROM CERTIFICATE CT_Mirror_SrvB

-- (c). 授予對資料庫鏡像端點的 connect 許可權 
GRANT CONNECT ON ENDPOINT::EDP_Mirror 
TO LOGIN_Mirror_SrvB 
GO

-- =========================================== 
--（10）  在鏡像伺服器上完成見證伺服器資料庫鏡像端點的傳輸安全模式配置 
-- 此操作鏡像伺服器上執行 
-- a. 建立見證伺服器上的認證(假設見證伺服器上備份的認證已經複製到 C:\CT_Mirror_SrvWitness.cer) 
CREATE CERTIFICATE CT_Mirror_SrvWitness 
FROM FILE = ‘C:\CT_Mirror_SrvWitness.cer‘

-- b. 建立登入 
CREATE LOGIN LOGIN_Mirror_SrvWitness 
FROM CERTIFICATE CT_Mirror_SrvWitness

-- c. 授予對資料庫鏡像端點的 connect 許可權 
GRANT CONNECT ON ENDPOINT::EDP_Mirror 
TO LOGIN_Mirror_SrvWitness 
GO

-- =========================================== 
--（11）  在主體伺服器上完成見證伺服器資料庫鏡像端點的傳輸安全模式配置 
-- 此操作主體伺服器上執行 
-- a. 建立見證伺服器上的認證(假設見證伺服器上備份的認證已經複製到 C:\CT_Mirror_SrvWitness.cer) 
CREATE CERTIFICATE CT_Mirror_SrvWitness 
FROM FILE = ‘C:\CT_Mirror_SrvWitness.cer‘

-- b. 建立登入 
CREATE LOGIN LOGIN_Mirror_SrvWitness 
FROM CERTIFICATE CT_Mirror_SrvWitness

-- c. 授予對資料庫鏡像端點的 connect 許可權 
GRANT CONNECT ON ENDPOINT::EDP_Mirror 
TO LOGIN_Mirror_SrvWitness 
GO

-- =========================================== 
--（12）  在主體伺服器上為資料庫鏡像啟用見證伺服器 
-- 此操作主體伺服器上執行 
ALTER DATABASE DB_Mirror SET 
WITNESS = ‘TCP://SrvWitness:5022‘ 
GO

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～ 
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

-- =========================================== 
-- 下面的操作可用於確定同步 
-- 1. 查詢資料庫狀態 
-- 下面的指令碼可以在主體伺服器和鏡像伺服器上執行,執行結果為鏡像的狀態 
SELECT 
mirroring_role_desc,          -- 資料庫在鏡像會話中當前的角色 
mirroring_state_desc,          -- 鏡像目前狀態 
mirroring_safety_level_desc,  -- 鏡像運行模式 
mirroring_witness_state_desc  -- 與見證伺服器的串連情況 
FROM sys.database_mirroring 
WHERE database_id = DB_ID(N‘DB_Mirror‘) 
GO

-- 2. 資料測試 
-- b. 主體伺服器上執行下面的語句以建立測試表 
CREATE TABLE DB_Mirror.dbo.tb( 
id int) 
WAITFOR DELAY ‘00:00:01‘ 
GO

-- b. 鏡像伺服器上, 建立鏡像資料庫的快昭資料庫,以便可以查詢當前的資料 
CREATE DATABASE SNP_DB_Mirror 
ON( 
NAME = DB_Mirror_DATA, 
FILENAME = N‘C:\SNP_DB_Mirror.mdf‘) 
AS SNAPSHOT OF DB_Mirror 
GO

-- c. 從快照資料庫中查詢測試表是否已經同步 
SELECT * FROM SNP_DB_Mirror.dbo.tb 
GO

-- d. 刪除測試建立的快照資料庫 
DROP DATABASE SNP_DB_Mirror 
GO

-- =========================================== 
-- 下面的操作用於刪除此樣本配置的鏡像對象 
--  認證和資料庫的備份需要在作業系統的資源管理員中刪除 
-- 1. 主體伺服器上執行的操作 
-- a. 停止鏡像和刪除主體資料庫 
USE master 
GO

ALTER DATABASE DB_Mirror SET 
PARTNER OFF 
DROP DATABASE DB_Mirror 
GO

-- b. 刪除鏡像端點 
DROP ENDPOINT EDP_Mirror 
GO

-- c. 刪除登入及認證 
DROP LOGIN LOGIN_Mirror_SrvB 
DROP LOGIN LOGIN_Mirror_SrvWitness 
DROP CERTIFICATE CT_Mirror_SrvA 
DROP CERTIFICATE CT_Mirror_SrvB 
DROP CERTIFICATE CT_Mirror_SrvWitness 
GO

-- 2. 鏡像伺服器上執行的操作 
-- a. 刪除鏡像資料庫 
USE master 
GO

DROP DATABASE DB_Mirror 
GO

-- b. 刪除鏡像端點 
DROP ENDPOINT EDP_Mirror 
GO

-- c. 刪除登入及認證 
DROP LOGIN LOGIN_Mirror_SrvA 
DROP LOGIN LOGIN_Mirror_SrvWitness 
DROP CERTIFICATE CT_Mirror_SrvA 
DROP CERTIFICATE CT_Mirror_SrvB 
DROP CERTIFICATE CT_Mirror_SrvWitness 
GO

-- 3. 見證伺服器上執行的操作 
-- a. 刪除端點 
DROP ENDPOINT EDP_Mirror 
GO

-- b. 刪除登入及認證 
DROP LOGIN LOGIN_Mirror_SrvA 
DROP LOGIN LOGIN_Mirror_SrvB 
DROP CERTIFICATE CT_Mirror_SrvA 
DROP CERTIFICATE CT_Mirror_SrvB 
DROP CERTIFICATE CT_Mirror_SrvWitness 
GO

-------------------------------------------------------------------------------------------------------------------------------------------

轉載自：http://www.cnblogs.com/tianyue3107/archive/2010/01/06/1640554.html

SQL Server 鏡像