[SQL SERVER][HA]利用SQL Express打造Log Shipping
前言TechNet論壇網友詢問如何利用SQL Express自行建立Log Shipping，當然我個人是不建議自己打造輪子，因為SQL Express沒有SQL Agent也沒有壓縮功能，而且後續執行時，一定也要管理維護次要資料庫還原交易記錄檔是否成功或失敗，那這樣你又得自己在打造另一顆輪子(Error Handling和效能都是讓人很感冒的問題)..anyway了解記錄傳送主要的三項作業，我們依然可以使用SQL Express自行打造Log Shipping，下面自行記錄整各過程(For SQL2005)，不過我事先聲明，利用該方法建立Log Shipping後續有任何問題時請自行負責....
三項主要作業1.主要執行個體備份交易記錄檔2.複製交易記錄檔到次要執行個體上3.次要執行個體上還原交易記錄檔
監視伺服器可有可無。
必要條件:1.主要資料庫需要完整復原模式2.兩個資料庫定序請一致3.SQL Server請使用Standard以上版本(這就先忽略了...)
需求:Log Shipping是SQL Server高可用性解決方案之一(硬體成本較低)，可以為主要資料庫提供災難復原(但DBA需手動執行Failover)或分擔資料庫查詢工作(次要資料庫可唯獨存取)。早期因為SQL2012以前的版本 DB Mirroring 次要資料庫無法存取，所以那時我為了模擬正式資料庫環境，我還會在另一台伺服器上建置Log Shipping來讓開發人員使用(相對成本也較高)，但現在SQL2012 AlwaysOn Availability Groups 解決了以往的問題(進階版的DB Mirroring)，最大化你的伺服器硬體利用率(閒置少)。
單獨使用Log Shipping將面臨問題:1.當主要伺服器故障時，DBA只能手動執行容錯移轉(Failover)。2.可能遭遇資料遺失問題。這取決於你設定傳送交易記錄檔並還原到次要資料庫的時間週期，3.執行容錯移轉到次要伺服器後，為了讓應用程式執行正常，你可能需要修改Server IP、DB Name...等。
Log Shipping建立步驟:1.主要資料庫執行完整備份。--1.full backup db
BACKUP database db1 to disk='d:\db1.bak' WITH init
2.還原到次要資料庫
--2.restore db with standby
--請自行修改DBName和standby file
RESTORE DATABASE [db2] FROM DISK = N'D:\db1.bak' WITH FILE = 1,
MOVE N'db1' TO N'd:\sqldata\db2.mdf',
MOVE N'db1_log' TO N'd:\sqldata\db2_1.ldf',
STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ROLLBACK_UNDO_db2.BAK',
NOUNLOAD, STATS = 10
3.restore master to standby instance這是為了解決執行容錯移轉後，登入帳號SID不一致問題，不過這裡我都再同一執行個體示範，所以就沒執行該步驟。RESTORE database master from disk='...' WITH REPLACE
成功如下圖
db2為待命/唯讀狀態。
查詢db2.dbo.tbl資料
目前該資料表只有4筆。
底下可以利用cmd呼叫sqlcmd執行相關語法並搭配windows scheduled來處理交易記錄檔
4.主要執行個體建立BackupLog 預存程序(store procedure)
USE [master]
GO
CREATE Proc dbo.usp_BackupLog
@LogPath nvarchar(1000),
@DBName nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
--Default:NOINIT, NOSKIP, NOFORMAT
BACKUP LOG @DBName to disk=@LogPath with NOINIT, NOSKIP, NOFORMAT
END
GO 5.次要執行個體建立RestoreLog 預存程序(store procedure)
先建立取得還原的交易記錄檔數量
CREATE PROCEDURE dbo.usp_GetRestoreCount
@FileName AS varchar(1000)
AS
RESTORE HEADERONLY FROM DISK=@FileName
建立執行還原交易記錄檔
--自行修改 standbyfile path
CREATE PROCEDURE dbo.usp_RestoreLog
@LogPath nvarchar(1000),
@DBName nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StandbyFile nvarchar(1000);
SET @StandbyFile = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ROLLBACK_UNDO_' + @DBName + '.BAK';
DECLARE @spid as varchar(10)
DECLARE @mycmd as varchar(1000)
DECLARE cur CURSOR FOR SELECT spid FROM master.dbo.sysprocesses WHERE dbid =
(SELECT dbid FROM sysdatabases WHERE name = @DBName)
open cur
FETCH NEXT FROM cur INTO @spid
--kill process
WHILE @@FETCH_STATUS = 0
BEGIN
SET @mycmd = 'kill ' + @spid
EXEC (@mycmd)
FETCH NEXT FROM cur INTO @spid
END
CLOSE cur
DEALLOCATE cur
DECLARE @i int
DECLARE @j int
SET @j = 1
SET @i = 0
--For SQL2005
CREATE TABLE #HeaderBackupTable
(BackupName varchar(255),
BackupDescription varchar(255),
BackupType int,
ExpirationDate datetime,
Compressed int,
Position int,
DeviceType int,
UserName VarChar(255),
ServerName varchar(255),
DatabaseName varchar(255),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize numeric(24,0),
FirstLsn numeric(24,0),
LastLsn numeric(24,0),
CheckpointLsn numeric(24,0),
DatabaseBackupLsn numeric(24,0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder int,
CodePage int,
UnicodeLocaleID int,
UnicodeComparisonStyle int,
CompatibilityLevel int,
SoftwareVendorID int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName varchar(255),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation varchar(255),
FamilyGUID uniqueidentifier,
HasBulkLoggedData bit,
IsSnapshot bit,
IsReadOnly bit,
IsSingleUser bit,
HasBackupChecksums bit,
IsDamaged bit,
BeginsLogChain bit,
HasIncompleteMetaData bit,
IsForceOffline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(24, 0),
RecoveryModel varchar(256),
DifferentialBaseLSN numeric(24, 0),
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription varchar(256),
BackupSetGUID uniqueidentifier
--CompressedBackupSize int
)
INSERT INTO #HeaderBackupTable
EXEC master.dbo.usp_GetRestoreCount @LogPath
SET @i = (SELECT COUNT(*) FROM #HeaderBackupTable )
DROP TABLE #HeaderBackupTable
WHILE @i+1 > (@j)
BEGIN
RESTORE LOG @DBName FROM DISK=@LogPath
WITH FILE = @j,
STANDBY = @StandbyFile
SET @j = @j + 1
END
END
測試:
DML相關測試
主要資料庫新增資料後執行備份交易記錄檔
INSERT into tb1 VALUES('rico4',getdate())
INSERT into tb1 VALUES('rico5',getdate())
exec dbo.usp_BackupLog 'D:\sqllogshipping\db1.trn','db1'
傳送交易記錄檔並還原到次要資料庫
exec dbo.usp_RestoreLog 'D:\sqllogshipping\db1.trn','db2'
確認次要資料庫上的資料
可以看到主要資料庫剛剛新增的2筆資料。
再次新增資料並執行備份交易記錄檔
INSERT into tb1 VALUES('rico6',getdate())
INSERT into tb1 VALUES('rico7',getdate())
INSERT into tb1 VALUES('rico8',getdate())
INSERT into tb1 VALUES('rico9',getdate())
exec dbo.usp_BackupLog 'D:\sqllogshipping\db1.trn','db1'
還原交易記錄檔並確認資料
exec dbo.usp_RestoreLog 'D:\sqllogshipping\db1.trn','db2'
確認db2資料
刪除資料測試
DELETE tb1 WHERE c1 >=5 AND c1 <=10
更新資料測試
UPDATE tb1 SET c2='ricoisme' WHERE c2='rico'
DDL相關測試
ALTER TABLE dbo.tb1 ADD c4 bit null;
CREATE table tb2
(
c1 int
)
DROP TABLE tb2
建立索引測試
CREATE index idx_1 on tb1(c1)
確認DB2索引
SELECT * FROM sys.indexes
where object_id=object_id('tb1')
參考