首页 > 代码库 > 第三篇——第二部分——第五文 配置SQL Server镜像——域环境SQL Server镜像日常维护
第三篇——第二部分——第五文 配置SQL Server镜像——域环境SQL Server镜像日常维护
本文接上面两篇搭建镜像的文章：
第三篇——第二部分——第三文 配置SQL Server镜像——域环境：http://blog.csdn.net/dba_huangzj/article/details/28904503第三篇——第二部分——第四文 配置SQL Server镜像——非域环境：http://blog.csdn.net/dba_huangzj/article/details/27652857
在搭建的过程中，可能你会遇到比较多的问题，下面介绍一些常见的问题及解决方案，另外把主要精力放到对镜像搭建后的日常维护中。而对镜像的监控及优化，会在下一篇文章中介绍：http://blog.csdn.net/dba_huangzj/article/details/26846203 。
原文出处：http://blog.csdn.net/dba_huangzj/article/details/30484925 (黄钊吉的博客)第一部分：常见的配置问题及解决方案：
在配置过程中，可能会有一系列的问题，特别是初次搭建。另外，很多正式环境并不能理想化配置，所以这一部分会列出一些常见的、本人搭建过程中也会遇到的一些问题，希望对读者处理镜像有所帮助。
常见的问题主要集中在三类：数据库还原问题、事务日志问题和通信问题。
数据库还原问题：
前面几篇文章中反复提醒，当还原数据库到镜像服务器时，需要使用NORECOVERY选项，如果没有使用这个选项，会返回Error 1416错误：
TITLE: Database Properties ------------------------------ An error occurred while starting mirroring. ------------------------------ ADDITIONAL INFORMATION: Alter failed for Database ‘TestDB‘. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=http://www.mamicode.com/Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476>
截图如下：
当出现这种情况时，对于大库就比较痛苦了，你必须使用NORECOVERY选项重新还原。 说白了，你刚才的还原已经没用了。并且为了追上主体服务器的进度，还需要还原一系列的日志备份，注意日志备份同样需要使用NORECOVERY选项。只要其中一个没用到NORECOVERY，必须从头开始。
事务日志问题：
事务日志的问题，是前面说过的在搭建镜像前，要停用所有相关库的日志备份作业的原因之一。如果在开始镜像前没有还原事务日志到镜像服务器上，或者在镜像服务器上已经还原了完整备份之后，又在主体服务器上进行了日志备份，却没有把备份文件同步到镜像服务器时，会出现下面两类错误：
- Error 1412 - The remote copy of database <DatabaseName> has not been rolled forward to a point in time that is encompassed in the local copy of the database log.这种情况是发生在做了一次完整备份，并把备份文件还原到镜像库中，然后还没开始镜像配置时又在主体服务器做了一次日志备份导致的，解决这种问题，只需要把日志文件重新还原到镜像数据库即可。
Error 1478 - The mirror database, <DatabaseName>, has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database.
这种情况较为少见，通常出现在配置镜像过程中，常规的备份作业还在运行，打断了原有的日志链。无论是哪种报错，保证在主体服务器的备份种类及次数都应该和镜像库还原过程中一样，并且禁用主体库的常规备份以免影响日志链。
通信问题：
相对于前面两种问题，通信问题是最难解决的，因为问题可能会有很多种，也可能是很多因素导致同一个报错。另外，由于涉及2台甚至3台服务器，所以问题的侦测难度更大。常见的通信报错有下面3中：
- Error 1418 - The server network address <NetworkAddress> can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
- Error 1486 - Database Mirroring Transport is disabled in the endpoint configuration.
- Error 1456 - The ALTER DATABASE command could not be sent to the remote server instance <NetworkAddress>. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
对于Error 1456这个错误：是针对见证服务器和其中一个伙伴服务器的通信错误。另外Error 1418是针对镜像环境中的伙伴服务器之间的通信问题。对于Error 1486，可以看专门的文档：MSSQLSERVER_1418：http://msdn.microsoft.com/zh-cn/library/aa337361.aspx
针对上面三种错误，常规步骤如下：
- 确保SQL Server不是使用Local System 账号运行。必须使用Network Service或者域账号运行，除非使用证书验证。
- 尝试Telnet每个需要用到的端口，如果不行，可能需要配置防火墙。
- 查询sys.database_mirroring_endpoints目录视图，确保镜像环境中的每个端点的加密和授权方式一致。
- 查询sys.tcp_endpoints目录视图，确保数据库镜像端点已经启动，如果没启动，可以使用下面语句实现：
ALTER ENDPOINT 端点名 STATE=STARTED
小结：
根据本人经历，配置过程的问题很多时候是没有根据“规定”和“规范”来做，缺少了或者做错了一些操作导致的，所以建议读者仔细研究搭建部分，跟着做基本上就不会有太多问题，等对搭建和使用都有了一定心得之后，再进行扩展使用。
第二部分：SQL Server镜像日常维护：
如果你搭建好高可用方案就觉得已经完事的话，后果将非常严重。所以本文重点介绍常规的镜像维护，记住维护工作极其重要。
维护工作主要包含下面几个部分：
- 事务日志备份
- 监控日志文件大小
- SQL Server常规升级
- 故障转移后恢复原有主体服务器
- 数据库配置
- 控制故障转移
- 自动故障转移
事务日志备份：
镜像的核心思想就是对事务日志的操作，这也是镜像必须使用完整恢复模式的原因之一。抛开高可用，任何完整模式下的数据库如果缺乏维护，日志文件都会出现非预期增长，最终导致数据库变成只读。根据拇指定律，核心系统的日志备份监控不宜大于半个小时。如果事务量巨大，甚至需要把备份间隔缩短到15分钟。
对于事务日志的管理，很重要的一个工作是做常规的日志备份，日志备份可以用维护计划实现，也可以写脚本实现，更多信息可以看《SQL Server扫盲》系列的日志备份一文：http://blog.csdn.net/dba_huangzj/article/details/26844859 。这里简要演示一下脚本备份的操作。
脚本化日志备份：
脚本的核心主要有下面几个：
- 检查数据库状态：对于镜像环境中的镜像角色，是不能联机的，也就是不能进行备份（包括日志备份）。所以如果要做备份脚本，需要检查数据库状态，可以查看sys.database_mirroring和sys.databases中的state/state_desc。
- 检查恢复模式：简单模式下不允许进行事务日志备份，所以需要检查sys.databases中的recovery_model/recovery_model_desc的信息。
- 检查数据库快照：对于数据库快照，同样不能使用备份操作，所以备份时需要检查sys.databases中的source_database_id，如果不为null，则证明为快照库，跳过备份操作。
- 检查日志传送：对部署了日志传送的数据库进行日志备份会破坏这个功能，所以需要检查msdb中的dbo.log_shipping_primary_databases表是否存在要备份的数据库。
- 检查系统库：tempdb和master库不能进行日志备份，而model库也通常没有必要进行日志备份，对于msdb，视乎使用情况决定，如果搭建了复制，会出现distribution库，这个库有时候会产生很多的日志，必要时候也应该纳入备份策略中。
下面是从网上专家脚本中收集的脚本，读者如有必要，可以使用，并且进行适当修改，但是作为尊重，请勿以原创发布，除非你做了大面积的修改：
CREATE PROCEDURE dbo.dba_BackupDBLogs
-- Database name or null for all databases
@DBName SYSNAME = NULL ,
-- Location where you want the backups
@LogBackupLocation NVARCHAR(255) = NULL ,
-- log backup extension
@FileExtension NVARCHAR(3) = ‘trn‘ ,
-- 0 = do not send alerts, 1 = send alerts
@SendAlerts BIT = 0 ,
@AlertRecipients VARCHAR(500) = NULL ,
-- 0 = execute log backup, 1 = output the code without executing
@Debug BIT = 0
AS
DECLARE @BakDir NVARCHAR(255) ,
@Exists INT ,
@CurrID INT ,
@MaxID INT ,
@SQL NVARCHAR(1000) ,
@LogBackup NVARCHAR(500) ,
@DateSerial NVARCHAR(35) ,
@ErrNumber INT ,
@ErrSeverity INT ,
@ErrState INT ,
@ErrProcedure SYSNAME ,
@ErrLine INT ,
@ErrMsg NVARCHAR(2048) ,
@FailedDBs NVARCHAR(4000) ,
@Subject VARCHAR(255) ,
@Body VARCHAR(8000) ,
@ProfileName SYSNAME
DECLARE @DBs TABLE
(
DBID INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY ,
DBName SYSNAME NOT NULL
)
DECLARE @FileExists TABLE
(
FileExists INT NOT NULL ,
FileIsDirectory INT NOT NULL ,
ParentDirectoryExists INT NOT NULL
)
DECLARE @Failures TABLE
(
FailId INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY ,
DBName SYSNAME NOT NULL ,
ErrNumber INT NULL ,
ErrSeverity INT NULL ,
ErrState INT NULL ,
ErrProcedure SYSNAME NULL ,
ErrLine INT NULL ,
ErrMsg NVARCHAR(2048) NULL
)
SET NOCOUNT ON
SET @DateSerial = CONVERT(NVARCHAR, GETDATE(), 112)
+ REPLACE(CONVERT(NVARCHAR, GETDATE(), 108), ‘:‘, ‘‘)
IF @DBName IS NOT NULL
BEGIN
IF NOT EXISTS ( SELECT 1
FROM sys.databases
WHERE name = @DBName )
BEGIN
RAISERROR (‘The specified database [%s] does not exist.
Please check the name entered or do not supply
a database name if you want to back up the log
for all online databases using the full or
bulk-logged recovery model.‘, 16, 1, @DBName);
RETURN;
END
IF EXISTS ( SELECT 1
FROM sys.databases
WHERE name = @DBName
AND state > 0 )
BEGIN
RAISERROR (‘The specified database [%s] is not online.
Please check the name entered or do not supply
a database name if you want to back up the log
for all online databases using the full or
bulk-logged recovery model.‘, 16, 1, @DBName);
RETURN;
END
IF EXISTS ( SELECT 1
FROM sys.databases
WHERE name = @DBName
AND recovery_model = 3 )
BEGIN
RAISERROR (‘The specified database [%s] is using the simple
recovery model. Please check the name entered or
do not supply a database name if you want to back up
the log for all online databases using the full or
bulk-logged recovery model.‘, 16, 1, @DBName);
RETURN;
END
IF EXISTS ( SELECT 1
FROM sys.databases
WHERE name = @DBName
AND source_database_id IS NOT NULL )
BEGIN
RAISERROR (‘The specified database [%s] is a database snapshot.
Please check the name entered or do not supply
a database name if you want to back up the log
for all online databases using the full or
bulk-logged recovery model.‘, 16, 1, @DBName);
RETURN;
END
IF EXISTS ( SELECT 1
FROM msdb.dbo.log_shipping_primary_databases
WHERE primary_database = @DBName )
BEGIN
RAISERROR (‘The specified database [%s] is a log shipping
primary and cannot have its log file backed up.
Please check the name entered or do not supply
a database name if you want to back up the log
for all online databases using the full or
bulk-logged recovery model.‘, 16, 1, @DBName);
RETURN;
END
END
IF @LogBackupLocation IS NULL
BEGIN
EXEC xp_instance_regread N‘HKEY_LOCAL_MACHINE‘,
N‘Software\Microsoft\MSSQLServer\MSSQLServer‘,
N‘BackupDirectory‘, @BakDir OUTPUT, ‘no_output‘;
IF @BakDir IS NOT NULL
BEGIN
INSERT INTO @FileExists
EXEC sys.xp_fileexist @BakDir;
SELECT @Exists = ParentDirectoryExists
FROM @FileExists
IF @Exists = 1
BEGIN
SET @LogBackupLocation = @BakDir;
END
END
END
IF @LogBackupLocation IS NULL
BEGIN
SELECT TOP 1
@BakDir = LEFT(MF.physical_device_name,
LEN(MF.physical_device_name)
- CHARINDEX(‘\‘,
REVERSE(MF.physical_device_name)))
FROM msdb.dbo.backupset BS
INNER JOIN msdb.dbo.backupmediafamily MF ON MF.media_set_id = BS.media_set_id
WHERE NOT EXISTS ( SELECT 1
FROM msdb.dbo.log_shipping_primary_databases
WHERE primary_database = BS.database_name )
-- log backups first, then differentials, then full backups
ORDER BY BS.type DESC ,
BS.backup_finish_date DESC; -- newest first
IF @BakDir IS NOT NULL
BEGIN
DELETE FROM @FileExists
INSERT INTO @FileExists
EXEC sys.xp_fileexist @BakDir;
SELECT @Exists = ParentDirectoryExists
FROM @FileExists
IF @Exists = 1
BEGIN
SET @LogBackupLocation = @BakDir;
END
END
END
IF @LogBackupLocation IS NOT NULL
BEGIN
IF RIGHT(@LogBackupLocation, 1) <> ‘\‘
SET @LogBackupLocation = @LogBackupLocation + ‘\‘;
END
ELSE
BEGIN
RAISERROR (‘Backup location not specified or not found.‘, 16, 1);
RETURN;
END
INSERT INTO @DBs
( DBName
)
SELECT name
FROM sys.databases D
WHERE state = 0
AND --online
-- 1 = Full, 2 = Bulk-logged, 3 = Simple
-- (log backups not needed for simple recovery model)
recovery_model IN ( 1, 2 )
AND
-- No log backups for core system databases
name NOT IN ( ‘master‘, ‘tempdb‘, ‘msdb‘, ‘model‘ )
AND
-- If is not null, database is a database snapshot
-- and can not be backed up
source_database_id IS NULL
AND
-- Backing up the log of a log-shipped database will
-- break the log shipping log chain
NOT EXISTS ( SELECT 1
FROM msdb.dbo.log_shipping_primary_databases
WHERE primary_database = D.name )
AND ( name = @DBName
OR @DBName IS NULL
);
SELECT @MaxID = MAX(DBID) ,
@CurrID = 1
FROM @DBs;
WHILE @CurrID <= @MaxID
BEGIN
SELECT @DBName = DBName
FROM @DBs
WHERE DBID = @CurrID;
SET @LogBackup = @LogBackupLocation + @DBName + ‘\‘;
-- Make sure backup location exists
-- Will not overwrite existing files, if any
IF @Debug = 0
BEGIN
EXEC xp_create_subdir @LogBackup;
END
ELSE
BEGIN
PRINT ‘Exec xp_create_subdir ‘ + @LogBackup + ‘;‘;
END
SET @LogBackup = @LogBackup + @DBName + @DateSerial + ‘.‘
+ @FileExtension
SET @SQL = ‘Backup Log ‘ + QUOTENAME(@DBName) + ‘ To Disk = ‘‘‘
+ @LogBackup + ‘‘‘;‘;
BEGIN TRY
IF @Debug = 0
BEGIN
PRINT ‘Backing up the log for ‘ + @DBName;
EXEC sp_executesql @SQL;
END
ELSE
BEGIN
PRINT ‘Print ‘‘Backing up the log for ‘ + @DBName
+ ‘‘‘;‘;
PRINT @SQL;
END
END TRY
BEGIN CATCH
SET @ErrNumber = ERROR_NUMBER();
SET @ErrSeverity = ERROR_SEVERITY();
SET @ErrState = ERROR_STATE();
SET @ErrProcedure = ERROR_PROCEDURE();
SET @ErrLine = ERROR_LINE();
SET @ErrMsg = ERROR_MESSAGE();
INSERT INTO @Failures
( DBName ,
ErrNumber ,
ErrSeverity ,
ErrState ,
ErrProcedure ,
ErrLine ,
ErrMsg
)
SELECT @DBName ,
@ErrNumber ,
@ErrSeverity ,
@ErrState ,
@ErrProcedure ,
@ErrLine ,
@ErrMsg
END CATCH
SET @CurrID = @CurrID + 1;
END
IF EXISTS ( SELECT 1
FROM @Failures )
BEGIN
SELECT @MaxID = MAX(FailId) ,
@CurrID = 1
FROM @Failures
WHILE @CurrID <= @MaxID
BEGIN
SELECT @DBName = DBName ,
@ErrNumber = ErrNumber ,
@ErrSeverity = ErrSeverity ,
@ErrState = ErrState ,
@ErrProcedure = ErrProcedure ,
@ErrLine = ErrLine ,
@ErrMsg = ErrMsg
FROM @Failures
WHERE FailId = @CurrID
PRINT ‘‘;
PRINT ‘Database Name = ‘ + @DBName;
PRINT ‘Error Number = ‘ + CAST(@ErrNumber AS VARCHAR);
PRINT ‘Error Severity = ‘ + CAST(@ErrSeverity AS VARCHAR);
PRINT ‘Error State = ‘ + CAST(@ErrState AS VARCHAR);
PRINT ‘Error Procedure = ‘ + ISNULL(@ErrProcedure, ‘‘);
PRINT ‘Error Line = ‘ + CAST(@ErrLine AS VARCHAR);
PRINT ‘Error Message= ‘ + @ErrMsg;
PRINT ‘‘;
SET @CurrID = @CurrID + 1
END
SELECT @FailedDBs = ISNULL(@FailedDBs + ‘, ‘, ‘‘)
+ QUOTENAME(DBName)
FROM @Failures
IF @SendAlerts = 1
AND @AlertRecipients IS NOT NULL
BEGIN
IF EXISTS ( SELECT 1
FROM sys.configurations
WHERE name = ‘Database Mail XPs‘ )
BEGIN
SELECT TOP ( 1 )
@ProfileName = name
FROM msdb.dbo.sysmail_profile P WITH ( NOLOCK )
LEFT JOIN msdb.dbo.sysmail_principalprofile PP ON PP.profile_id = P.profile_id
ORDER BY PP.is_default DESC
SET @Subject = ‘Backup failures on ‘
+ CAST(@@SERVERNAME AS VARCHAR(255))
SET @Body = ‘Unable to back up the following databases: ‘
+ @FailedDBs
EXEC msdb..sp_send_dbmail @profile_name = @ProfileName,
@recipients = @AlertRecipients,
@Subject = @Subject, @body = @Body
END
END
RAISERROR (‘Unable to back up the following databases: %s‘,
1, 1, @FailedDBs);
END日志备份文件管理：
由于日志备份的频率较高，所以可能一段时间之后，日志文件会有很多个，即使个数不多（以追加的方式每次写入同一个文件，本人不建议这样做），日志备份的体积也可能会很大。因此，对过久的日志文件，应该进行归档或者删除。对文件的归档，可以使用sys.xp_delete_file存储过程实现。下面提供一个删除日志备份文件的脚本，同样是收集来的，使用者不要在没有大面积修改前直接以原创方式发布：
CREATE PROCEDURE dbo.dba_DeleteLogBackups
-- Name of database, all databases if null
@DBName SYSNAME = NULL ,
-- Location of log backups
@LogBackupLocation NVARCHAR(255) = NULL ,
-- log backup extension
@FileExtension NVARCHAR(3) = ‘trn‘ ,
@Retention INT = 4 , -- days
-- 0 = execute deletion of log backup,
-- 1 = output the code without executing
@Debug BIT = 0
AS
DECLARE @DeleteDate NVARCHAR(19) ,
@BakDir NVARCHAR(255) ,
@Exists INT
DECLARE @FileExists TABLE
(
FileExists INT NOT NULL ,
FileIsDirectory INT NOT NULL ,
ParentDirectoryExists INT NOT NULL
)
SET NOCOUNT ON
SET @DeleteDate = CONVERT(NVARCHAR(19), DATEADD(DAY, -@Retention,
GETDATE()), 126)
IF @DBName IS NOT NULL
BEGIN
IF NOT EXISTS ( SELECT 1
FROM sys.databases
WHERE name = @DBName )
BEGIN
RAISERROR (‘The specified database [%s] does not exist.
Please check the name entered or do not supply
a database name if you want to delete the
log backups for all databases.‘, 16, 1, @DBName);
RETURN;
END
END
IF @LogBackupLocation IS NULL
BEGIN
EXEC xp_instance_regread N‘HKEY_LOCAL_MACHINE‘,
N‘Software\Microsoft\MSSQLServer\MSSQLServer‘,
N‘BackupDirectory‘, @BakDir OUTPUT, ‘no_output‘;
IF @BakDir IS NOT NULL
BEGIN
INSERT INTO @FileExists
EXEC sys.xp_fileexist @BakDir;
SELECT @Exists = ParentDirectoryExists
FROM @FileExists;
IF @Exists = 1
BEGIN
SET @LogBackupLocation = @BakDir + ISNULL(‘\‘
+ @DBName, ‘‘);
END
END
END
IF @LogBackupLocation IS NULL
BEGIN
SELECT TOP 1
@BakDir = LEFT(MF.physical_device_name,
LEN(MF.physical_device_name)
- CHARINDEX(‘\‘,
REVERSE(MF.physical_device_name)))
FROM msdb.dbo.backupset BS
INNER JOIN msdb.dbo.backupmediafamily MF ON MF.media_set_id = BS.media_set_id
-- log backups first, then differentials, then full backups
ORDER BY BS.type DESC ,
BS.backup_finish_date DESC; -- newest first
IF @BakDir IS NOT NULL
BEGIN
DELETE FROM @FileExists
INSERT INTO @FileExists
EXEC sys.xp_fileexist @BakDir;
SELECT @Exists = ParentDirectoryExists
FROM @FileExists
IF @Exists = 1
BEGIN
SET @LogBackupLocation = @BakDir;
END
END
END
IF @LogBackupLocation IS NOT NULL
BEGIN
IF RIGHT(@LogBackupLocation, 1) <> ‘\‘
SET @LogBackupLocation = @LogBackupLocation + ‘\‘;
END
ELSE
BEGIN
RAISERROR (‘Backup location not specified or not found.‘, 16, 1);
RETURN;
END
IF @Debug = 0
BEGIN
EXEC sys.xp_delete_file 0, @LogBackupLocation, @FileExtension,
@DeleteDate, 1;
END
ELSE
BEGIN
PRINT ‘Exec sys.xp_delete_file 0, ‘‘‘ + @LogBackupLocation
+ ‘‘‘, ‘‘‘ + @FileExtension + ‘‘‘, ‘‘‘ + @DeleteDate
+ ‘‘‘, 1;‘;
END
监控日志文件大小：
常规的日志备份并不能完全避免日志文件的异常增长，所以对日志文件大小的监控也是非常重要的，另外如果使用2005，确保 http://support.microsoft.com/kb/947462 这里提到的补丁已经打上。日志过大，会导致初始化过程变得很慢甚至超时，针对日志过大，首先要检查是否有常规的日志备份，其次就是检查是否有未关闭的事务并且运行了很久。
在确定日志文件已经有常规备份并且实在太大是，收缩日志文件可能是必须做的事情，但是通常收缩应该是最后一步。这里包含两个部分：
- 识别需要收缩的日志
- 收缩日志文件
识别需要收缩的日志：
这里使用脚本查询主体服务器上已经配置为镜像的库机器日志文件大小：
SELECT DBName = DB_NAME(MF.database_id) ,
LogFileName = MF.[name] ,
LogFileSize = CEILING(MF.[size] * 8 / 1024.0)
FROM sys.master_files MF
INNER JOIN sys.database_mirroring DM ON DM.database_id = MF.database_id
WHERE MF.[type] = 1 -- 日志文件
AND DM.Mirroring_Role = 1--主体数据库需要提醒一下，对主体库的日志收缩并不同步到镜像库，所以在使用DBCC SHRINKFILE之后，使用ALTER DATABASE XX MODIFY FILE强制主体库更改日志文件的大小。以便同步到镜像库。
收缩日志文件：
下面是某位MCM提供的进行收缩的脚本，这个脚本主要针对镜像库的日志收缩：
CREATE PROCEDURE dbo.dba_ShrinkMirroredDatabases
-- database to shrink; all mirrored databases if null
@DBName SYSNAME = NULL ,
-- target size for shrink operation. Defaults to 5 GB (5120 MB)
@TargetSize INT = 5120 ,
-- 0 = Execute it, 1 = Output SQL that would be executed
@Debug BIT = 0
AS
DECLARE @CurrID INT ,
@MaxID INT ,
@DefaultTargetSize INT ,
@FileName SYSNAME ,
@FileSize INT ,
@NewFileSize INT ,
@SQL NVARCHAR(MAX) ,
@ErrMsg NVARCHAR(500)
DECLARE @MirroredDBs TABLE
(
MirroredDBID INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY ,
DBName SYSNAME NOT NULL ,
LogFileName SYSNAME NOT NULL ,
FileSize INT NOT NULL
)
SET NOCOUNT ON
-- Assume entered as GB and convert to MB
IF @TargetSize < 20
BEGIN
SET @TargetSize = @TargetSize * 1024
END
-- Assume entered as MB and use 512
ELSE
IF @TargetSize <= 512
BEGIN
SET @TargetSize = 512
END
-- Assume entered as KB and return warning
ELSE
IF @TargetSize > 19922944
BEGIN
SET @ErrMsg = ‘Please enter a valid target size less than 20 GB. ‘
+ ‘Amount entered can be in GB (max size = 19), ‘
+ ‘MB (max size = 19456), or ‘
+ ‘KB (max size = 19922944).‘;
GOTO ErrComplete;
END
-- Assume entered as KB and convert to MB
ELSE
IF @TargetSize > 525311
BEGIN
SET @TargetSize = 525311 / 1024
END
-- Assume entered as KB and use 512 as converted MB
ELSE
IF @TargetSize > 19456
BEGIN
SET @TargetSize = 512
END
-- Else assume entered as MB and use as entered
INSERT INTO @MirroredDBs
( DBName ,
LogFileName ,
FileSize
)
SELECT DB_NAME(MF.database_id) ,
MF.[name] ,
-- Size = number of 8K pages
CEILING(MF.[size] * 8 / 1024.0)
FROM sys.master_files MF
INNER JOIN sys.database_mirroring DM ON DM.database_id = MF.database_id
WHERE MF.[type] = 1
AND -- log file
DM.Mirroring_Role = 1
AND -- Principal partner
-- Specified database or all databases if null
( MF.database_id = @DBName
OR @DBName IS NULL
)
IF NOT EXISTS ( SELECT 1
FROM @MirroredDBs )
BEGIN
SET @ErrMsg = CASE WHEN @DBName IS NOT NULL
THEN ‘Database ‘ + QUOTENAME(@DBName)
+ ‘ was either not found or is not‘
+ ‘ a mirroring principal.‘
ELSE ‘No databases were found in the ‘
+ ‘mirroring principal role.‘
END;
GOTO ErrComplete;
END
ELSE
BEGIN
SELECT @MaxID = MAX(MirroredDBID) ,
@CurrID = 1
FROM @MirroredDBs
WHILE @CurrID <= @MaxID
BEGIN
SELECT @DBName = DBName ,
@FileName = LogFileName ,
@FileSize = FileSize
FROM @MirroredDBs
WHERE MirroredDBID = @CurrID
IF @FileSize > @TargetSize
BEGIN
SET @SQL = ‘Use ‘ + QUOTENAME(@DBName) + ‘;‘
+ ‘DBCC ShrinkFile(‘‘‘ + @FileName + ‘‘‘, ‘
+ CAST(@TargetSize AS NVARCHAR) + ‘);‘
IF @Debug = 0
BEGIN
EXEC sp_executesql @SQL
END
ELSE
BEGIN
PRINT @SQL
END
SELECT -- Size = number of 8K pages
@NewFileSize = CEILING(( [size] + 1 ) * 8)
FROM sys.master_files
WHERE [type] = 1
AND -- log file
[name] = @FileName
AND database_id = DB_ID(@DBName)
IF @NewFileSize < @FileSize
BEGIN
SET @SQL = ‘Alter Database ‘
+ QUOTENAME(@DBName)
+ ‘ Modify File (name = ‘ + @FileName
+ ‘, size = ‘
+ CAST(@NewFileSize AS NVARCHAR)
+ ‘KB);‘
IF @Debug = 0
BEGIN
EXEC sp_executesql @SQL
END
ELSE
BEGIN
PRINT @SQL
END
END
END
SET @CurrID = @CurrID + 1
END
END
Success:
GOTO Complete;
ErrComplete:
RAISERROR (@ErrMsg, 1, 1)
RETURN
Complete:SQL Server常规升级 ：
在正式环境中，服务器的操作系统、SQL Server及其他应用程序可能会定期打补丁，这时候就涉及重启机器或者SQL Server的工作，如果搭建了镜像环境，这个操作就会稍微变得复杂了。因为为了高可用的稳定运行，不仅主体服务器要打补丁，镜像服务器甚至见证服务器也要打补丁。
本部分包含两个情况：
- 安装常规补丁
- 升级SQL Server或为SQL Server打补丁
安装常规补丁：
这里指的补丁不是SQL Server的补丁，在镜像环境中，如果打了补丁并且需要重启，尽可能避免镜像环境的所有伙伴服务器都同时重启，毕竟搭建高可用的原因是为了加大业务连续性。
常规做法是：先对一台服务器打补丁，重启这台服务器（如果需要重启），确保这台机已经正常运行之后，以相同步骤处理下一台。镜像环境中重启顺序没有强制规定，按照实践，通常是：见证→镜像→主体服务器。
升级SQL Server或为SQL Server打补丁：
针对SQL Server打补丁，必须更加谨慎，特别是镜像环境出现了见证服务器。如果带有见证服务器，首先要移除见证服务器，并在打完补丁之后把见证服务器加回去。见证服务器可以在任何时候进行升级。可以在主体服务器上运行：
ALTER DATABASE [DatabaseName] SET WITNESS OFF;--移除见证服务器 ALTER DATABASE [DatabaseName] SET WITNESS = ‘TCP://WitnessServer.FQDN.com:Port#‘;.--添加见证服务器
对于运行模式，建议先设为异步模式，然后再打补丁，在准备Failover时，改为同步模式，使其同步信息之后再Failover。
在正式打补丁时，先对镜像服务器打补丁，最主要的原因是可以验证补丁是否有效，并且打完之后是否可以安全Failover。当镜像服务器已经打完补丁并且Failover成主体服务器时，由于补丁的级别不一致，SQL Server会挂起镜像会话，这时候应该尽快对原主体服务器打补丁，否则挂起太久会引起很多后患。
故障转移后恢复原有主体服务器 ：
在镜像环境中，人为或者自动故障转移都会使镜像服务器变成主体服务器，如果这不是你希望的，那么你就需要想办法把现在的主体服务器变回镜像服务器，其中一个原因是如果不这样做，你必须准备一个新的镜像服务器以便下一次故障转移。这里分两步介绍：
- 恢复原有主体服务器的原因
- 实现原有主体服务器的恢复
恢复原有主体服务器的原因：
恢复原有主体服务器有很多原因，但是比较重要的原因是：镜像不同步作业、维护计划、SSIS等。如果是非人为Failover，会导致这些组件失败、重试，而有些系统中，这些组件是至关重要的，尽快联机并切换回原有状态是首要任务，另外，镜像服务器往往性能没有主体服务器强，长时间接管业务可能导致镜像服务器面临压力。
实现原有主体服务器的恢复：
可以使用这个脚本进行恢复：
CREATE PROCEDURE dbo.dba_FailoverMirrorToOriginalPrincipal
-- database to fail back; all applicable databases if null
@DBName SYSNAME = NULL ,
-- 0 = Execute it, 1 = Output SQL that would be executed
@Debug BIT = 0
AS
DECLARE @SQL NVARCHAR(200) ,
@MaxID INT ,
@CurrID INT
DECLARE @MirrDBs TABLE
(
MirrDBID INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY ,
DBName SYSNAME NOT NULL
)
SET NOCOUNT ON
-- If database is in the principal role
-- and is in a synchronized state,
-- fail database back to original principal
INSERT INTO @MirrDBs
( DBName
)
SELECT DB_NAME(database_id)
FROM sys.database_mirroring
WHERE mirroring_role = 1
AND -- Principal partner
mirroring_state = 4
AND -- Synchronized
( database_id = DB_ID(@DBName)
OR @DBName IS NULL
)
SELECT @MaxID = MAX(MirrDBID)
FROM @MirrDBs
WHILE @CurrID <= @MaxID
BEGIN
SELECT @DBName = DBName
FROM @MirrDBs
WHERE MirrDBID = @CurrID
SET @SQL = ‘Alter Database ‘ + QUOTENAME(@DBName)
+ ‘ Set Partner Failover;‘
IF @Debug = 1
BEGIN
EXEC sp_executesql @SQL;
END
ELSE
BEGIN
PRINT @SQL;
END
SET @CurrID = @CurrID + 1
END数据库配置 ：
数据库配置中有两个选项可以用于镜像，一个是数据库拥有者（database owner）和Trustworthy 数据库属性。当备份一个数据库是，SQL Server会重置Trustworthy ，这个属性搞熟SQL Server可以信任这个数据库中包含的对象。可以使用：ALTER DATABASE [库名] SET TRUSTWORTHY ON; 来设置。
在还原数据库或者创建新库时，SQL Server会把你操作的账号作为数据库的拥有者，这可能因为你这个账号的权限不足以完成你所需的任务而导致数据库在运行过程中报错。可以使用下面语句来查看数据库的拥有者：
SELECT P.name
FROM sys.databases D LEFT JOIN
sys.server_principals P
ON P.sid = D.owner_sid
WHERE D.name = ‘数据库名‘如果数据库onwer为null或者不是期望的，可以用下面语句修改：
EXEC 库名.sys.sp_changedbowner @loginame = ‘sa‘ --2005 ALTER AUTHORIZATION ON DATABASE::库名TO sa; --2008
控制故障转移 ：
Failover一般有两种：自动转移和手动转移，手动转移可以很简单地使用下面语句实现：
ALTER DATABASE [DatabaseName] SET PARTNER FAILOVER --在主体服务器上执行
在Failover过程中，你可以会遇到一些问题，毕竟现实总不是太理想的，如果在异步模式下执行Failover命令，会报错，需要在Failover之前把数据库设为同步，下面提供一个控制Failover的存储过程，常见的问题已经在里面得到处理：
CREATE PROCEDURE dbo.dba_ControlledFailover
-- database to fail back; all applicable databases if null
@DBName SYSNAME = NULL ,
-- @MaxCounter = max # of loops, each loop = 5 seconds
-- 60 loops = 5 minutes
@MaxCounter INT = 60 ,
-- 0 = Execute it, 1 = Output SQL that would be executed
@Debug BIT = 0
AS
DECLARE @SQL NVARCHAR(1000) ,
@MaxID INT ,
@CurrID INT ,
@DMState INT ,
@SafeCounter INT ,
@PartnerServer SYSNAME ,
@SafetyLevel INT ,
@TrustWorthyOn BIT ,
@DBOwner SYSNAME ,
@Results INT ,
@ErrMsg VARCHAR(500) ,
@Print NVARCHAR(1000)
DECLARE @Databases TABLE
(
DatabaseID INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY ,
DatabaseName SYSNAME NOT NULL ,
PartnerServer SYSNAME NOT NULL ,
SafetyLevel INT NOT NULL ,
TrustWorthyOn BIT NOT NULL ,
DBOwner SYSNAME NULL
)
SET NOCOUNT ON
INSERT INTO @Databases
( DatabaseName ,
PartnerServer ,
SafetyLevel ,
TrustWorthyOn ,
DBOwner
)
SELECT D.name ,
DM.mirroring_partner_instance ,
DM.mirroring_safety_level ,
D.is_trustworthy_on ,
SP.name
FROM sys.database_mirroring DM
INNER JOIN sys.databases D ON D.database_id = DM.database_id
LEFT JOIN sys.server_principals SP ON SP.sid = D.owner_sid
WHERE DM.mirroring_role = 1
AND -- Principal role
DM.mirroring_state IN ( 2, 4 )
AND -- Synchronizing, Synchronized
( D.name = @DBName
OR @DBName IS NULL
)
IF NOT EXISTS ( SELECT 1
FROM @Databases )
AND @DBName IS NULL
BEGIN
RAISERROR (‘There were no mirroring principals found on this server.‘,
1, 1);
END
IF NOT EXISTS ( SELECT 1
FROM @Databases )
AND @DBName IS NOT NULL
BEGIN
RAISERROR (‘Database [%s] was not found or is not a mirroring principal
on this server.‘, 1, 1, @DBName);
END
SELECT @MaxID = MAX(DatabaseID) ,
@CurrID = 1
FROM @Databases
-- Set Safety to Full on all databases first, if needed
WHILE @CurrID <= @MaxID
BEGIN
SELECT @DBName = DatabaseName ,
@PartnerServer = PartnerServer ,
@SafetyLevel = SafetyLevel
FROM @Databases
WHERE DatabaseID = @CurrID
-- Make sure linked server to mirror exists
EXEC @Results = dbo.dba_ManageLinkedServer @ServerName = @PartnerServer,
@Action = ‘create‘
IF @Results <> 0
BEGIN
RAISERROR (‘Failed to create linked server to mirror instance
[%s].‘, 1, 1, @PartnerServer);
END
IF @SafetyLevel = 1
BEGIN
SET @SQL = ‘Alter Database ‘ + QUOTENAME(@DBName)
+ ‘ Set Partner Safety Full;‘
SET @Print = ‘Setting Safety on for database ‘
+ QUOTENAME(@DBName) + ‘.‘;
IF @Debug = 0
BEGIN
PRINT @Print
EXEC sp_executesql @SQL
END
ELSE
BEGIN
PRINT ‘-- ‘ + @Print
PRINT @SQL;
END
END
SET @CurrID = @CurrID + 1
END
-- Reset @CurrID to 1
SET @CurrID = 1
-- Pause momentarily
WAITFOR DELAY ‘0:00:03‘;
-- Failover all databases
WHILE @CurrID <= @MaxID
BEGIN
SELECT @DBName = DatabaseName ,
@DMState = DM.mirroring_state ,
@SafeCounter = 0 ,
@SafetyLevel = SafetyLevel
FROM @Databases D
INNER JOIN sys.database_mirroring DM ON DM.database_id = DB_ID(D.DatabaseName)
WHERE DatabaseID = @CurrID
WHILE @DMState = 2
AND -- Synchronizing
@SafeCounter < @MaxCounter
BEGIN
WAITFOR DELAY ‘0:00:05‘;
SELECT @DMState = mirroring_state ,
@SafeCounter = @SafeCounter + 1
FROM sys.database_mirroring
WHERE database_id = DB_ID(@DBName)
END
IF @DMState = 2
AND @SafeCounter = @MaxCounter
BEGIN
RAISERROR(‘Synchronization timed out for database [%s].
Please check and fail over manually.‘, 1, 1, @DBName);
IF @SafetyLevel = 1
BEGIN
SET @SQL = ‘Alter Database ‘ + QUOTENAME(@DBName)
+ ‘ Set Partner Safety Full;‘
SET @Print = ‘Setting Safety Full for database ‘
+ QUOTENAME(@DBName) + ‘.‘;
IF @Debug = 0
BEGIN
PRINT @Print
EXEC sp_executesql @SQL
END
ELSE
BEGIN
PRINT ‘-- ‘ + @Print
PRINT @SQL;
END
END
END
ELSE
BEGIN
SET @SQL = ‘Alter Database ‘ + QUOTENAME(@DBName)
+ ‘ Set Partner Failover;‘
SET @Print = ‘Failing over database ‘ + QUOTENAME(@DBName)
+ ‘.‘;
IF @Debug = 0
BEGIN
PRINT @Print
EXEC sp_executesql @SQL
END
ELSE
BEGIN
PRINT ‘-- ‘ + @Print
PRINT @SQL;
END
END
SET @CurrID = @CurrID + 1
END
-- Reset @CurrID to 1
SET @CurrID = 1
-- Pause momentarily
WAITFOR DELAY ‘0:00:03‘;
-- Set safety level and db owner on failed over databases
WHILE @CurrID <= @MaxID
BEGIN
SELECT @DBName = DatabaseName ,
@PartnerServer = PartnerServer ,
@SafetyLevel = SafetyLevel ,
@TrustWorthyOn = TrustWorthyOn ,
@DBOwner = DBOwner ,
@DMState = DM.mirroring_state ,
@SafeCounter = 0
FROM @Databases D
INNER JOIN sys.database_mirroring DM ON DM.database_id = DB_ID(D.DatabaseName)
WHERE DatabaseID = @CurrID
-- Make sure linked server to mirror exists
EXEC @Results = dbo.dba_ManageLinkedServer @ServerName = @PartnerServer,
@Action = ‘create‘
WHILE @DMState = 2
AND -- Synchronizing
@SafeCounter < @MaxCounter
BEGIN
WAITFOR DELAY ‘0:00:05‘;
SELECT @DMState = mirroring_state ,
@SafeCounter = @SafeCounter + 1
FROM sys.database_mirroring
WHERE database_id = DB_ID(@DBName)
END
IF @DMState = 2
AND @SafeCounter = @MaxCounter
BEGIN
RAISERROR(‘Synchronization timed out for database [%s]
after failover. Please check and set
database options manually.‘, 1, 1, @DBName);
END
ELSE
BEGIN
-- Turn safety off if it was originally off
IF @SafetyLevel = 1
BEGIN
SET @SQL = ‘Alter Database ‘ + QUOTENAME(@DBName)
+ ‘Set Partner Safety Off;‘
SET @SQL = ‘Exec ‘ + QUOTENAME(@PartnerServer)
+ ‘.master.sys.sp_executesql N‘‘‘ + @SQL
+ ‘‘‘;‘;
SET @Print = ‘Setting Safety off for database ‘
+ QUOTENAME(@DBName) + ‘ on server ‘
+ QUOTENAME(@PartnerServer) + ‘.‘;
IF @Debug = 0
BEGIN
PRINT @Print
EXEC sp_executesql @SQL
END
ELSE
BEGIN
PRINT ‘-- ‘ + @Print
PRINT @SQL;
END
END
-- Set TrustWorthy property on if it was originally on
IF @TrustWorthyOn = 1
BEGIN
SET @SQL = ‘Alter Database ‘ + QUOTENAME(@DBName)
+ ‘ Set TrustWorthy On;‘
SET @SQL = ‘EXEC ‘ + QUOTENAME(@PartnerServer)
+ ‘.master.sys.sp_executesql N‘‘‘ + @SQL
+ ‘‘‘;‘;
SET @Print = ‘Setting TrustWorthy On for database ‘
+ QUOTENAME(@DBName) + ‘ on server ‘
+ QUOTENAME(@PartnerServer) + ‘.‘;
IF @Debug = 0
BEGIN
PRINT @Print
EXEC sp_executesql @SQL
END
ELSE
BEGIN
PRINT ‘-- ‘ + @Print
PRINT @SQL;
END
END
-- Change database owner if different than original
SET @SQL = ‘If Exists (Select 1 From sys.databases D‘
+ CHAR(10) + CHAR(9)
+ ‘Left Join sys.server_principals P‘
+ ‘ On P.sid = D.owner_sid‘ + CHAR(10) + CHAR(9)
+ ‘Where P.name Is Null‘ + CHAR(10) + CHAR(9)
+ ‘Or P.name <> ‘‘‘ + @DBOwner + ‘‘‘)‘ + CHAR(10)
+ CHAR(9) + ‘Exec ‘ + QUOTENAME(@DBName)
+ ‘..sp_changedbowner ‘‘‘ + @DBOwner + ‘‘‘;‘
SET @SQL = REPLACE(@SQL, ‘‘‘‘, ‘‘‘‘‘‘)
SET @SQL = ‘Exec ‘ + QUOTENAME(@PartnerServer)
+ ‘.master.sys.sp_executesql N‘‘‘ + @SQL + ‘‘‘;‘;
SET @Print = ‘Changing Database owner to ‘
+ QUOTENAME(@DBOwner) + ‘ for database ‘
+ QUOTENAME(@DBName) + ‘ on server ‘
+ QUOTENAME(@PartnerServer) + ‘.‘;
IF @Debug = 0
BEGIN
PRINT @Print
EXEC sp_executesql @SQL
END
ELSE
BEGIN
PRINT ‘-- ‘ + @Print
PRINT @SQL;
END
END
SET @CurrID = @CurrID + 1
END自动故障转移 ：
自动故障转移要考虑几个问题：1、伙伴服务器上的作业、维护计划等或者其他外部资源。2、应用程序依赖的库是否也同时转移，不管是否需要。3、账号
针对这些问题，可以使用Windows Management Instrumentation(WMI)命名空间来发送警告。可以通过SSMS打开，如图：
然后配置WMI事件：
然后配置响应页：
最后填写警告信息：
通过这个警告，可以创建一些维护作业响应自动故障转移中的潜在问题。除此之外，还可以用Service broker、Powershell等工具来监控。针对详细的监控，可以看下一文：http://blog.csdn.net/dba_huangzj/article/details/26846203