使用SQL Express打造Log Shipping

发布日期:2012-08-16 17:08:28
如何利用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)

先建立取得还原的交易记录档数量

 www.it165.net

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 #HeaderTable) 
    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

 


 

Tag标签: SQL   Express   Log   Shipping  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规