SQLServer數(shù)據(jù)庫鏡像搭建(無見證無域控)

1. 環(huán)境配置
服務(wù)器1：sqlmirror01
IP：192.168.10.176
服務(wù)器2：sqlmirror02
IP：192.168.10.177
操作系統(tǒng)：Windows 2012 R2
安裝 .Net Framework 3.5/4.0
關(guān)閉服務(wù)器防火墻或設(shè)置白名單
數(shù)據(jù)庫版本：SQL Server 2014
2. 創(chuàng)建主密鑰(非必需)
use master
go
create master key encryption by password='zaq12WSX'
go
3. 創(chuàng)建證書
sqlmirror01執(zhí)行
use master
go
create certificate sqlmirror01_cert with subject='sqlmirror01 certificate',expiry_date='2099-1-1'
go
sqlmirror02執(zhí)行
use master
go
create certificate sqlmirror02_cert with subject='sqlmirror02 certificate',expiry_date='2099-1-1'
go
4. 創(chuàng)建鏡像端點(diǎn)
sqlmirror01執(zhí)行
use master
go
create endpoint Endpoint_Mirroring
state=started
as tcp ( listener_port = 5022,listener_ip = all )
for database_mirroring ( authentication = certificate sqlmirror01_cert, encryption = required algorithm aes, role = all )
go
sqlmirror02執(zhí)行
use master
go
create endpoint Endpoint_Mirroring
state=started
as tcp ( listener_port = 5022,listener_ip = all )
for database_mirroring ( authentication = certificate sqlmirror02_cert, encryption = required algorithm aes, role = all )
go
5. 備份證書
sqlmirror01執(zhí)行
use master
go
backup certificate sqlmirror01_cert to file = 'C:\Certificate_files\sqlmirror01_cert.cer'
go
sqlmirror02執(zhí)行
use master
go
backup certificate sqlmirror02_cert to file = 'C:\Certificate_files\sqlmirror02_cert.cer'
go
6. 拷貝證書
將sqlmirror01_cert.cer拷貝至sqlmirror02的C:\Certificate_files\
將sqlmirror02_cert.cer拷貝至sqlmirror01的C:\Certificate_files\
7. 創(chuàng)建登錄名
sqlmirror01執(zhí)行
use master
go
create login sqlmirror02_login with password='abc@123456'
go
sqlmirror02執(zhí)行
use master
go
create login sqlmirror01_login with password='abc@123456'
go
8. 創(chuàng)建使用該登錄名的用戶
sqlmirror01執(zhí)行
use master
go
create user sqlmirror02_user for login sqlmirror02_login
go
sqlmirror02執(zhí)行
use master
go
create user sqlmirror01_user for login sqlmirror01_login
go
9. 證書與用戶關(guān)聯(lián)
sqlmirror01執(zhí)行
use master
go
create certificate sqlmirror02_cert
authorization sqlmirror02_user
from file='c:\Certificate_files\sqlmirror02_cert.cer'
go
sqlmirror02執(zhí)行
use master
go
create certificate sqlmirror01_cert
authorization sqlmirror01_user
from file='c:\Certificate_files\sqlmirror01_cert.cer'
go
10. 授予對遠(yuǎn)程鏡像端點(diǎn)的登錄名的 CONNECT 權(quán)限
sqlmirror01執(zhí)行
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlmirror02_login];
go
sqlmirror02執(zhí)行
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlmirror01_login];
go
11. 配置鏡像伙伴
sqlmirror01執(zhí)行
創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE [Mirror_test_1]
ON PRIMARY
( NAME = N'Mirror_test_1', FILENAME = N'C:\dbdata\Mirror_test_1.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Mirror_test_1_log', FILENAME = N'C:\dbdata\Mirror_test_1_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
備份數(shù)據(jù)庫
backup database Mirror_test_1 to disk='C:\backup\Mirror_test_1.bak' with compression,checksum
將備份拷貝至sqlmirror02
sqlmirror02執(zhí)行
restore database Mirror_test_1 from disk='C:\backup\Mirror_test_1.bak' with norecovery
連接鏡像
sqlmirror02執(zhí)行
use master
go
ALTER DATABASE Mirror_test_1 SET PARTNER = 'TCP://sqlmirror01:5022';
go
sqlmirror01執(zhí)行
use master
go
ALTER DATABASE Mirror_test_1 SET PARTNER = 'TCP://sqlmirror02:5022';
go


更改為高性能模式
鏡像主體sqlmirror01執(zhí)行
use master
go
ALTER DATABASE Mirror_test_1 SET PARTNER SAFETY OFF
go

成都創(chuàng)新互聯(lián)長期為數(shù)千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊從業(yè)經(jīng)驗10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為吉陽企業(yè)提供專業(yè)的做網(wǎng)站、成都網(wǎng)站建設(shè)，吉陽網(wǎng)站改版等技術(shù)服務(wù)。擁有10多年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。

文章標(biāo)題：SQLServer數(shù)據(jù)庫鏡像搭建(無見證無域控)
文章鏈接：http://muchs.cn/article4/piscoe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、全網(wǎng)營銷推廣、虛擬主機(jī)、手機(jī)網(wǎng)站建設(shè)、網(wǎng)站建設(shè)、標(biāo)簽優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)