RiCo技術農場

每當我向一些朋友或同事分享SQL2008 R2透過mirroring升級到SQL2016，而且不會有任何data lose時，

大家每次都給我不太可能做到的表情，最常反問我就是，SQL2008 R2和SQL2016不同版本你怎麼可能建置Mirroring，

我只會說，你怎麼知道不可能?你實際試過嗎?我自己可是做過很多次了(不然我可能要退還不少小朋友)，

我一直以為這不是什麼大秘密，但真實世界~~~~~你知道的 :)

note:我並不討厭Replication，但僅限單向~~哈

/*
RiCo技術農場
https://dotblogs.com.tw/ricochen/1
How to fast upgrade your datbase from SQL2008 R2 to SQL2016 SP1
1.full and tran backup from primary (RICONB\SQL2K8R2)
2.restore full and tran in no-recovery to Mirror (RICONB\SQL2K16)
3.Failover test
note:
1.server instances run as the same sql service account,
in my case as the riconb\rico
2.check port status,in my case such as 1433,5022 and 5023
*/

backup database RCSITEST to disk='D:\tmpfile\sqlbackup\RCSITEST_full.bak'
with compression,init

backup log RCSITEST to disk='D:\tmpfile\sqlbackup\RCSITEST_log.bak'
with compression,init

--primary 
--CREATE ENDPOINT
CREATE ENDPOINT [Mirroring_endpoint] 
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)
go

--start ENDPOINT
ALTER ENDPOINT [Mirroring_endpoint]
STATE = STARTED 
AS TCP (LISTENER_PORT = 5022)
FOR database_mirroring (ROLE = ALL);
GO

--check enpoint status
SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;


--SET PARTNER(primary)
ALTER DATABASE RCSITEST
SET PARTNER ='TCP://RICONB:5023'

use RCSITEST
go
select * from mytbl
insert into mytbl select 'mirroringwithdifferentversion','recommendyoutotry'
insert into mytbl select 'mirroringwithdifferentversionB','recommendyoutotryB'

--FAILOVER test
use master
go
ALTER DATABASE RCSITEST SET PARTNER FAILOVER

--fix restore status
RESTORE DATABASE RCSITEST WITH RECOVERY
GO

 

restore database RCSITEST from disk='D:\tmpfile\sqlbackup\RCSITEST_full.bak'
with replace,stats=10,norecovery,
move 'RCSITEST' to 'D:\SQL2016db\RCSITEST.mdf',
move 'RCSITEST_Log' to 'D:\SQL2016db\RCSITEST_Log.ldf'

restore log RCSITEST from disk='D:\tmpfile\sqlbackup\RCSITEST_log.bak'
with replace,stats=10,norecovery,
move 'RCSITEST' to 'D:\SQL2016db\RCSITEST.mdf',
move 'RCSITEST_Log' to 'D:\SQL2016db\RCSITEST_Log.ldf'

--standby 
--CREATE ENDPOINT
CREATE ENDPOINT [Mirroring_endpoint] 
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)
go

--start ENDPOINT
ALTER ENDPOINT [Mirroring_endpoint]
STATE = STARTED 
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = ALL);
GO

--check enpoint status
SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

--SET PARTNER(standby)
ALTER DATABASE RCSITEST
SET PARTNER ='TCP://RICONB:5022'

--get records before in last 2 hours
use msdb
go
 
EXEC sp_dbmmonitorresults RCSITEST, 2, 0;

use rcsitest
--check sync data
select * from mytbl

--remove mirror
use master
go
ALTER DATABASE RCSITEST SET PARTNER OFF  

--SET COMPATIBILITY_LEVEL= 130 (SQL2016)
ALTER DATABASE RCSITEST SET COMPATIBILITY_LEVEL= 130

 

 

 

參考

[SQL SERVER][HA]資料庫鏡像#簡介

[SQL SERVER][HA]資料庫鏡像#實作同步資料庫鏡像

ALTER DATABASE (Transact-SQL) Compatibility Level