Cluster Log Shipping 1 SQL SERVER 2014 3

镜像+快照 介绍 Cluster + Log Shipping 准备 作 软件准备 1) SQL SERVER 2014 (至少 3台) 2) WINDWS SERVER 2012 (至少 4台) 3)VMWARE-WORKSTATION 10. 0 (1个VM) 网络地址准备 �算机 Mirror 1(�像 A) Mirror 2(�像 B) Node-DC(域控) Witness(�� ) IP 主机 IP: 192. 168. 1. 120 MASK: 255. 0 DNS: 192. 168. 100 IP: 192. 168. 1. 121 MASK: 255. 0 DNS: 192. 168. 100 IP: 192. 168. 1. 100 MASK: 255. 0 Contoso. com IP: 192. 168. 1. 122 MASK: 255. 0

配置镜像 主节点创建数据库 create database test_school on ( name='test_school_data', Filename='D: databasestest_school_data. mdf', size=10, maxsize=UNLIMITED, filegrowth=64 mb) log on (name = 'test_school_log', filename='D: databasestest_school_log. ldf', size=10, maxsize=UNLIMITED, filegrowth=200 mb)

配置镜像 主节点创建表 create table student ( SNO Varchar(10), SName Varchar(20), SAGE tinyint, SEX Char(6), Mobile. No Char(11) CONSTRAINT pk_Stundent. ID PRIMARY KEY (SNO, SName) )

配置镜像 主节点添加字段 insert into student values ('1', 'kobe', '15', 'male', '12345678901') , ('2', 'James', '10', 'male', '12345678901') , ('3', 'Jordan', '20', 'male', '12345678901') , ('4', 'Iverson', '16', 'male', '12345678901‘) , ('5', 'Jabbar', '30', 'male', '12345678901') , (‘ 6’, ’Durant', ’ 8', 'male', '12345678901') , (‘ 7’, ’Curry', '20', 'male', '12345678901')

配置镜像 主节点备份数据库 backup database test_school to disk ='D: backuptest_school. bak’ backup log test_school to disk ='D: backuptest_school_log. bak'

配置镜像 辅助节点创建数据库 create database test_school on ( name='test_school_data', Filename='D: databasestest_school_data. mdf', size=10, maxsize=UNLIMITED, filegrowth=64 mb) log on (name = 'test_school_log', filename='D: databasestest_school_log. ldf', size=10, maxsize=UNLIMITED, filegrowth=200 mb)

配置镜像 辅助节点还原数据库norevoery restore filelistonly from disk ='D: backuptest_school. bak’ restore database test_school from disk ='D: backuptest_school. bak' with move 'test_school_data' to 'D: databasestest_school_data. mdf', move 'test_school_log' to 'D: databasestest_school_log. ldf', replace, file =1, norecovery restore database test_school from disk ='D: backuptest_school_log. bak' with replace, file =1, norecovery

配置镜像 辅助节点还原数据库norevoery restore filelistonly from disk ='D: backuptest_school. bak’ restore database test_school from disk ='D: backuptest_school. bak' with move 'test_school_data' to 'D: databasestest_school_data. mdf', move 'test_school_log' to 'D: databasestest_school_log. ldf', replace, file =1, norecovery restore database test_school from disk ='D: backuptest_school_log. bak' with replace, file =1, norecovery

快照配置 SQL Server 配置快照 1. 创建快照 001 create database test_school_001_snapshot on ( name = ‘test_school_data’, ------数据库逻辑名称 filename = 'D: databasestest_school_001_snapshot. snap’ ) as snapshot of test_school ------数据库名 2. 创建快照 002 create database test_school_002_snapshot on ( name = ‘test_school_data’, ------数据库逻辑名称 filename = 'D: databasestest_school_002_snapshot. snap’ ) as snapshot of test_school ------数据库名

快照配置 SQL Server 配置快照 1. 创建快照 create database test_school_001_snapshot on ( name = ‘test_school_data’, ------数据库逻辑名称 filename = 'D: databasestest_school_001_snapshot. snap’ ) as snapshot of test_school ------数据库名

快照配置 创建自动快照 1. SQL 代理 – 新建作业 – 作业名称: auto_database_snaposhot – 确定 2. 作业属性 – 步骤添加以下数据脚本 declare @snapshot. Name varchar (128) declare @snapdata. Name varchar (128) declare @snapfilename varchar (500) declare @RQ varchar (50) declare @databasename varchar (100) declare @query varchar (5000) set @RQ=convert(varchar(4), datepart(YYYY, getdate())) + convert(varchar(4) , datepart(mm, getdate())) + convert(varchar(4), datepart(dd, getdate())) +convert(varchar(2), datepart(hh, getdate())) +convert(varchar(2), datepart(MI, getdate())) set @databasename = 'test_school' set @snapdata. Name = 'test_school_data' --快照数据库名称 set @snapshot. Name = 'test_school_snapshot'+''+@RQ --快照文件名称 set @snapfilename = '"D: snapshoottest_school_snapshoot'+'_'+@RQ+'. snap"'

快照配置 创建自动快照 --已建立删除 select * from sys. databases where source_database_id=db_id(@databasename) and name=@snapshot. Name if @@rowcount <> 0 begin set @query = 'drop database'+@snapshot. Name print @query exec (@query) end

快照配置 创建自动快照 --创建自动快照 set @query ='Create database ' +@Snapshot. Name+ ' on (Name = '+ @snapdata. Name +', File. Name='+@snapfilename +') AS SNAPSHOT of ' + @databasename print @query exec (@query)

快照配置 创建自动删除前一天快照 declare @query varchar(5000) set @query='' select @query=@query+'Drop database '+name+'; '+char(14) from sys. databases where source_database_id is not null --and create_date<getdate()-1 --print @query exec(@query)

订阅发布介绍 发布服务器 Mirror 1服务器 创建数据库 • • • • create database test on ( name='test_data', Filename='D: databasestest_data. mdf', size=10, maxsize=UNLIMITED, filegrowth=64 mb) log on (name = 'test_log', filename='D: databasestest_log. ldf', size=10, maxsize=UNLIMITED, filegrowth=200 mb) Mirror 1服务器 创建表 create table student ( SNO Varchar(10), SName Varchar(20), SAGE tinyint, SEX Char(6), Mobile. No Char(11) CONSTRAINT pk_Stundent. ID PRIMARY KEY (SNO, SName) )

配置镜像 Mirror 1添加字段 insert into student values ('1', 'kobe', '15', 'male', '12345678901') , ('2', 'James', '10', 'male', '12345678901') , ('3', 'Jordan', '20', 'male', '12345678901') , (‘ 4’, ‘Iverson’, ‘ 16’, ‘male’, ‘ 12345678901’) , ('5', 'Jabbar', '30', 'male', '12345678901’) , ('6', 'Durant', '8', 'male', '12345678901') , (‘ 7’, ’Curry', '20', 'male', '12345678901')