五餅二魚工作室

前一陣子整理了一下有關於 SQL Server AlwaysON 的部分資料，陸續接到一些回饋的問題，其中有個部分是關於當採用 AlwaysON 之後，那該在哪一台環境上執行交易紀錄備份呢 ? 而當可用性群組進行移轉，或者是指定的副本沒有上線的話，那該怎麼切換呢 ?

 

這些問題蠻有趣的，因此我用之前的測試環境做個測試，目前我們的可用性群組中的 Primary 是在 ALWAYSON-SQL1 ，副本是在 ALWAYSON-SQL2，在可用性群組中的一個資料庫 DEMO2 下建立一些測試資料。

CREATE TABLE T1( F1 int , F2 nchar(10), F3 nchar(100) )
GO

INSERT INTO T1( F1, F2, F3 ) VALUES 
	( 1 , '0000000001', NEWID() ),( 2 , '0000000002', NEWID() ),( 3 , '0000000003', NEWID() ),
	( 4 , '0000000004', NEWID() ),( 5 , '0000000005', NEWID() ),( 6 , '0000000006', NEWID() ),
	( 7 , '0000000007', NEWID() ),( 8 , '0000000008', NEWID() ),( 9 , '0000000009', NEWID() );
GO

 

而在判斷要用哪台 SQL Server 來做備份，這個部份我搭配 sys.fn_hadr_backup_is_preferred_replica 這一個新的 DMV ，如果判斷某資料庫是適合在該主機備份，則此 DMV 會回傳回 1。因此這個時候我們在兩台環境上執行，SQL Server 會在  ALWAYSON-SQL2 回傳回 1，因此這個時候我們就到  ALWAYSON-SQL2 上來進行備份，由於 ALWAYSON-SQL2 是副本，因此這個時候我們在完整備份的時候要再多加上 COPY_ONLY 的參數。

BACKUP DATABASE DEMO2 TO DISK='D:\BACKUP\DEMO2_20160223_FULL.BAK' WITH COPY_ONLY

接下來我們在資料表內刪除一筆資料，再透過指令進行備份

BACKUP LOG DEMO2 TO DISK='D:\BACKUP\DEMO2_20160223_1012_LOG.BAK'

再刪除一筆資料，同樣也是做交易紀錄備份

BACKUP LOG DEMO2 TO DISK='D:\BACKUP\DEMO2_20160223_1015_LOG.BAK'

 

此時我們刻意把 ALWAYSON-SQL2 這台副本主機的服務給關閉，而在這個時候我們在 Primary 的 ALWAYSON-SQL1 上使用 sys.fn_hadr_backup_is_preferred_replica 來判斷是回傳 1，因此這個時候我們再刪除一筆資料後，在  ALWAYSON-SQL1 進行交易紀錄備份

BACKUP LOG DEMO2 TO DISK='D:\BACKUP\DEMO2_20160223_1021_LOG.BAK'

 

完畢之後，接下來我們再重新將 ALWAYSON-SQL2 這台副本主機的服務給啟動，這個時候我們在 Primary 的 ALWAYSON-SQL1 上使用 sys.fn_hadr_backup_is_preferred_replica 來判斷是回傳 ０，在ALWAYSON-SQL２ 上會回傳 1，因此再做一次資料刪除後，接著在 ALWAYSON-SQL2 這台副本進行交易紀錄備份

BACKUP LOG DEMO2 TO DISK='D:\BACKUP\DEMO2_20160223_1024_LOG.BAK'

---

透過上述的相關步驟，這個時候我們已經有一個完整備份和四個交易紀錄備份，那首先我們先透過 RESTORE HEADERONLY 的方式來查看一下備份檔案的檔頭，看看相關的 LSN 是否正確。

DECLARE @headers TABLE 
( 
    BackupName varchar(256),
    BackupDescription varchar(256),
    BackupType int,        
    ExpirationDate varchar(256),
    Compressed int,
    Position int,
    DeviceType int,        
    UserName varchar(256),
    ServerName varchar(256),
    DatabaseName varchar(256),
    DatabaseVersion varchar(256),        
    DatabaseCreationDate datetime,
    BackupSize int,
    FirstLSN varchar(256),
    LastLSN varchar(256),        
    CheckpointLSN varchar(256),
    DatabaseBackupLSN varchar(256),
    BackupStartDate datetime,
    BackupFinishDate datetime,        
    SortOrder varchar(256),
    CodePage varchar(256),
    UnicodeLocaleId varchar(256),
    UnicodeComparisonStyle varchar(256),        
    CompatibilityLevel varchar(256),
    SoftwareVendorId int,
    SoftwareVersionMajor int,        
    SoftwareVersionMinor int,
    SoftwareVersionBuild int,
    MachineName varchar(256),
    Flags varchar(256),        
    BindingID varchar(256),
    RecoveryForkID varchar(256),
    Collation varchar(256),
    FamilyGUID varchar(256),        
    HasBulkLoggedData varchar(256),
    IsSnapshot bit,
    IsReadOnly bit,
    IsSingleUser bit,        
    HasBackupChecksums bit,
    IsDamaged bit,
    BeginsLogChain bit,
    HasIncompleteMetaData bit,        
    IsForceOffline bit,
    IsCopyOnly bit,
    FirstRecoveryForkID varchar(256),
    ForkPointLSN varchar(256),        
    RecoveryModel varchar(256),
    DifferentialBaseLSN varchar(256),
    DifferentialBaseGUID varchar(256),        
    BackupTypeDescription varchar(256),
    BackupSetGUID varchar(256),
    CompressedBackupSize varchar(256),        
    Containment varchar(256)
)

INSERT INTO @headers 
	EXEC ( 'RESTORE HEADERONLY FROM DISK=''D:\BACKUP\DEMO2_20160223_FULL.BAK'' ')
INSERT INTO @headers 
	EXEC ( 'RESTORE HEADERONLY FROM DISK=''D:\BACKUP\DEMO2_20160223_1012_LOG.BAK'' ')
INSERT INTO @headers 
	EXEC ( 'RESTORE HEADERONLY FROM DISK=''D:\BACKUP\DEMO2_20160223_1015_LOG.BAK'' ')
INSERT INTO @headers 
	EXEC ( 'RESTORE HEADERONLY FROM DISK=''D:\BACKUP\DEMO2_20160223_1021_LOG.BAK'' ')
INSERT INTO @headers 
	EXEC ( 'RESTORE HEADERONLY FROM DISK=''D:\BACKUP\DEMO2_20160223_1024_LOG.BAK'' ')

SELECT DatabaseName,ServerName, FirstLSN,LastLSN,DatabaseBackupLSN FROM @headers

從上面的結果可以看出來，雖然交易紀錄檔案是分別在不同的環境上來做備份，但第四筆的 FirstLSN 卻是有接續在第三筆 LastLSN，而第五筆的 FirstLSN 也有接續在第四筆 LastLSN，看起來並沒有因為在不同主機上備份，造成 LSN 沒有接續的問題。

 

而接下來我們實際還原資料庫看看，透過下面的指令執行完畢之後，資料庫也可以順利還原成功，並且跟既有的資料庫比對一下，資料都是一樣的。

USE [master]
RESTORE DATABASE [DEMOX] 
	FROM DISK = N'D:\BACKUP\DEMO2_20160223_FULL.BAK' 
	WITH MOVE N'DEMO2' TO N'F:\DATA\DEMOX.mdf',  MOVE N'DEMO2_log' TO N'G:\LOG\DEMOX_log.ldf',
	NORECOVERY
GO

RESTORE DATABASE [DEMOX] 
	FROM DISK = N'D:\BACKUP\DEMO2_20160223_1012_LOG.BAK' 
	WITH MOVE N'DEMO2' TO N'F:\DATA\DEMOX.mdf',  MOVE N'DEMO2_log' TO N'G:\LOG\DEMOX_log.ldf',
	NORECOVERY
GO

RESTORE DATABASE [DEMOX] 
	FROM DISK = N'D:\BACKUP\DEMO2_20160223_1015_LOG.BAK' 
	WITH MOVE N'DEMO2' TO N'F:\DATA\DEMOX.mdf',  MOVE N'DEMO2_log' TO N'G:\LOG\DEMOX_log.ldf',
	NORECOVERY
GO

RESTORE DATABASE [DEMOX] 
	FROM DISK = N'D:\BACKUP\DEMO2_20160223_1021_LOG.BAK' 
	WITH MOVE N'DEMO2' TO N'F:\DATA\DEMOX.mdf',  MOVE N'DEMO2_log' TO N'G:\LOG\DEMOX_log.ldf',
	NORECOVERY
GO

RESTORE DATABASE [DEMOX] 
	FROM DISK = N'D:\BACKUP\DEMO2_20160223_1024_LOG.BAK' 
	WITH MOVE N'DEMO2' TO N'F:\DATA\DEMOX.mdf',  MOVE N'DEMO2_log' TO N'G:\LOG\DEMOX_log.ldf',
	RECOVERY
GO

 

因此如果您的 AlwaysON 環境上需要進行備份的話，那麼可不要忘記使用  sys.fn_hadr_backup_is_preferred_replica 來做判斷，這樣會簡化您不少的判斷和處理，可以讓備份的選擇更加容易了。

---

PS1 如果要判斷資料庫在本機是否為 Primary，可用以下的判斷處理

SELECT hars.role_desc
FROM sys.databases d
INNER JOIN sys.dm_hadr_availability_replica_states hars ON d.replica_id = hars.replica_id
WHERE database_id = DB_ID(@DBName);