一、配置主備機
1、 服務(wù)器基本信息
主機名稱為:HOST_A,IP地址為:192.168.1.155
備機名稱為:HOST_B,IP地址為:192.168.1.156
二、主備實例互通
實現(xiàn)互通可以使用域或證書來實現(xiàn),考慮實現(xiàn)的簡單,以下選取證書的方式實現(xiàn)。注意:實現(xiàn)“主備數(shù)據(jù)庫實例互通”的操作只需要做一次,例如為了將兩個SQL Server 2008的實例中的5個數(shù)據(jù)庫建成鏡像關(guān)系,則只需要做一次以下操作就可以了;或者這樣理解:每一對主備實例(不是數(shù)據(jù)庫)做一次互通。
1、創(chuàng)建證書(主備可并行執(zhí)行)
--主機執(zhí)行:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,
START_DATE = '2012-08-02',
EXPIRY_DATE = '2099-08-02';
--備機執(zhí)行:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',
START_DATE = '2012-08-02',
EXPIRY_DATE = '2099-08-02';
2、創(chuàng)建連接的端點(主備可并行執(zhí)行)
--主機執(zhí)行:
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
--備機執(zhí)行:
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
3、備份證書以備建立互聯(lián)(主備可并行執(zhí)行)
--主機執(zhí)行:
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\SQLBackup\HOST_A_cert.cer';
--備機執(zhí)行:
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\SQLBackup\HOST_B_cert.cer';
4、互換證書
將備份到C:\SQLBackup\的證書進行互換,即HOST_A_cert.cer復(fù)制到備機的C:\SQLBackup\。HOST_B_cert.cer復(fù)制到主機的C:\SQLBackup\。
5、添加登陸名、用戶(主備可并行執(zhí)行)
以下操作只能通過命令行運行,通過圖形界面無法完成。(截至SQL Server2005的補丁號為SP2)
--主機執(zhí)行:
CREATE LOGIN HOST_B_login WITH PASSWORD = '123456';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'C:\SQLBackup\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
--備機執(zhí)行:
CREATE LOGIN HOST_A_login WITH PASSWORD = '123456';
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'C:\SQLBackup\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
三、建立鏡像關(guān)系
以下步驟是針對每個數(shù)據(jù)庫進行的,例如:現(xiàn)有主機中有5個數(shù)據(jù)庫以下過程就要執(zhí)行5次。
1、 手工同步登錄名和密碼
在上文中提到數(shù)據(jù)庫鏡像的缺點之一是無法維護登錄名,所以需要我們手工維護登錄。
通常來說數(shù)據(jù)庫都將會有若干個用戶作為訪問數(shù)據(jù)庫的用戶,并且數(shù)據(jù)庫會有相應(yīng)的登錄名,但是在備機中缺少與之相對應(yīng)的登錄名,例如某業(yè)務(wù)系統(tǒng)使用'myuser'作為登錄名訪問數(shù)據(jù)庫,但是在備機中沒有'myuser'這個登錄名,因此一旦主備切換,業(yè)務(wù)系統(tǒng)就無法登錄數(shù)據(jù)庫了,這種情況稱為"孤立用戶"。在主機和備機數(shù)據(jù)庫上建立相同用戶名及密碼即可。
2、 準(zhǔn)備備機數(shù)據(jù)庫(主機備份及鏡像還原)
在主機上備份數(shù)據(jù)庫,先做完整備份,再做日志事務(wù)備份。
1、主數(shù)據(jù)必須設(shè)置成完整模式進行備份,如下圖:
上圖中將“恢復(fù)模式”選成“完整模式”。
2、備份數(shù)據(jù)庫,如下圖:
備份時將“備份類型”選成“完整”。
3、備份事務(wù)日志,如下圖:
將“備份類型”選成“事務(wù)日志”且備份目錄與備份數(shù)據(jù)庫的目錄一致。
將備份文件在備機上使用主機的全備文件進行還原,在還原數(shù)據(jù)的時候需要使用選上“with non recover”。如圖所示:
如果執(zhí)行成功數(shù)據(jù)庫將會變成這個樣子:
3、 建立鏡像
--在備機中執(zhí)行如下語句:
ALTER DATABASE shishan SET PARTNER = 'TCP://192.168.1.155:5022';
說明:shishan為數(shù)據(jù)庫名,需要根據(jù)實際進行修改。192.168.1.155為主機IP地址,需根據(jù)實際進行修改。
--主機執(zhí)行:
ALTER DATABASE shishan SET PARTNER = 'TCP://192.168.1.156:5022';
說明:shishan為數(shù)據(jù)庫名,需要根據(jù)實際進行修改。192.168.1.156為備機IP地址,需根據(jù)實際進行修改。
執(zhí)行成功后:
到此,SQL鏡像熱備配置完成。
四、常見命令
--切換主備
use master;
alter database testdb set partner failover;
-- 備機強制切換
use master;
alter database testdb set partner force_service_allow_data_loss;
--恢復(fù)鏡像
use master;
alter database testdb set partner resume;
--取消見證服務(wù)器
ALTER DATABASE testdb SET WITNESS OFF ;
--取消鏡像
ALTER DATABASE testdb SET PARTNER OFF;
--設(shè)置鏡像數(shù)據(jù)庫還原為正常
RESTORE DATABASE testdb WITH RECOVERY;
備份主數(shù)據(jù)庫出現(xiàn):Backup a database on a HDD with a different sector size,可以執(zhí)行以下語句備份:
BACKUP DATABASE MyDB TO DISK = N'D:\MyDB.bak' WITH INIT , NOUNLOAD , NAME = N'MyDB backup', STATS = 10, FORMAT
總結(jié)
要進行以上sql server的鏡像設(shè)置一定要使用sql server 的配置管理器開啟TCP/IP協(xié)議,如下圖
如果沒有啟用TCP/IP協(xié)議則只能在同一個網(wǎng)段內(nèi)的機器配置鏡像,前面的配置步驟里面所用到的IP地址要換成對應(yīng)的實例名。同一個網(wǎng)段配置并使用鏡像的時實性、傳輸速率更高,適用于大數(shù)據(jù)量的同步,跨網(wǎng)段或者跨公網(wǎng)的sql server 鏡像一般適用于數(shù)據(jù)量小,時實性要求不高的數(shù)據(jù)同步,而且數(shù)據(jù)庫在公網(wǎng)上同步也不安全。
您可能感興趣的文章:- sql server連接不上怎么辦 SQL Server2008R無法登錄的解決方案(1814\18456)
- Java連接sqlserver2008數(shù)據(jù)庫代碼
- PHP 5.6.11 訪問SQL Server2008R2的幾種情況詳解
- win2008 r2 服務(wù)器php+mysql+sqlserver2008運行環(huán)境配置(從安裝、優(yōu)化、安全等)
- SqlServer2008誤操作數(shù)據(jù)(delete或者update)后恢復(fù)數(shù)據(jù)的方法
- sqlserver2008首次登錄失敗問題及解決方法