flyfox

On SQL Server 2008 R2。於 Main 移除鏡像並將資料庫刪除

於 Mirror 將資料庫刪除
ALTER DATABASE MyDB SET PARTNER OFF
drop DATABASE MyDB

查詢鏡像端點名稱、認證 ID
select * from sys.database_mirroring_endpoints;
以認證 ID 查詢認證過期日
select expiry_date，thumbprint，* from master.sys.certificates where certificate_id = 261;

查詢認證名稱、認證 ID、認證過期日
select principal_id，* from master.sys.certificates

查詢鏡像端點名稱
SELECT name，role_desc，state_desc FROM sys.database_mirroring_endpoints

刪除鏡像端點
drop ENDPOINT Mirror

刪除認證
DROP CERTIFICATE MirrorCertD
DROP CERTIFICATE MirrorCertDM
DROP CERTIFICATE MirrorCertDW

刪除資料庫保護金鑰
drop master key
drop database encryption key

建立資料庫保護金鑰
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@sswOrd'
GO

CREATE CERTIFICATE MirrorCertD
WITH SUBJECT = 'DB MirrorCert D'
GO

CREATE ENDPOINT Main
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE MirrorCertD,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE=ALL);
GO

BACKUP CERTIFICATE MirrorCertD
TO FILE = 'D:\Mirror2012\MirrorCertD.cer'
GO

匯入 DM 認證
USE master

CREATE CERTIFICATE MirrorCertDM
AUTHORIZATION dbo
FROM FILE = 'D:\Mirror2012\MirrorCertDM.cer'
GO

匯入 DW 認證
USE master

CREATE CERTIFICATE MirrorCertDW
AUTHORIZATION dbo
FROM FILE = 'D:\Mirror2012\MirrorCertDW.cer'
GO