本文接上面两篇搭建镜像的文章：
第三篇——第二部分——第三文 配置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 (黄钊吉的博客)第一部分：常见的配置问题及解决方式：
在配置过程中，可能会有一系列的问题，特别是初次搭建。另外，非常多正式环境并不能理想化配置，所以这一部分会列出一些常见的、本人搭建过程中也会遇到的一些问题，希望对读者处理镜像有所帮助。
常见的问题主要集中在三类：数据库还原问题、事务日志问题和通信问题。
数据库还原问题：
前面几篇文章中重复提醒，当还原数据库到镜像server时，须要使用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=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Database "TestDB" is not configured for database mirroring. (Microsoft SQL Server, Error: 1416)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=1416&LinkId=20476------------------------------
BUTTONS:
OK
------------------------------
截图例如以下：
当出现这样的情况时，对于大库就比較痛苦了，你必须使用NORECOVERY选项又一次还原。 说白了，你刚才的还原已经没用了。而且为了追上主体server的进度，还须要还原一系列的日志备份，注意日志备份相同须要使用NORECOVERY选项。仅仅要当中一个没用到NORECOVERY，必须从头開始。
事务日志问题：
事务日志的问题，是前面说过的在搭建镜像前，要停用全部相关库的日志备份作业的原因之中的一个。假设在開始镜像前没有还原事务日志到镜像server上，或者在镜像server上已经还原了完整备份之后，又在主体server上进行了日志备份，却没有把备份文件同步到镜像server时，会出现以下两类错误：
- 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.这样的情况是发生在做了一次完整备份，并把备份文件还原到镜像库中，然后还没開始镜像配置时又在主体server做了一次日志备份导致的，解决这样的问题，仅仅须要把日志文件又一次还原到镜像数据库就可以。
-
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.
这样的情况较为少见，通常出如今配置镜像过程中，常规的备份作业还在执行，打断了原有的日志链。不管是哪种报错，保证在主体server的备份种类及次数都应该和镜像库还原过程中一样，而且禁用主体库的常规备份以免影响日志链。
通信问题：
相对于前面两种问题，通信问题是最难解决的，由于问题可能会有非常多种，也可能是非常多因素导致同一个报错。另外，由于涉及2台甚至3台server，所以问题的侦測难度更大。常见的通信报错有以下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这个错误：是针对见证server和当中一个伙伴server的通信错误。另外Error 1418是针对镜像环境中的伙伴server之间的通信问题。对于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常规升级
- 故障转移后恢复原有主体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 这里提到的补丁已经打上。日志过大，会导致初始化过程变得非常慢甚至超时，针对日志过大，首先要检查是否有常规的日志备份，其次就是检查是否有未关闭的事务而且执行了非常久。
在确定日志文件已经有常规备份而且实在太大是，收缩日志文件可能是必须做的事情，可是通常收缩应该是最后一步。这里包括两个部分：
- 识别须要收缩的日志
- 收缩日志文件
识别须要收缩的日志：
这里使用脚本查询主体server上已经配置为镜像的库机器日志文件大小：
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常规升级 ：
在正式环境中，server的操作系统、SQL Server及其它应用程序可能会定期打补丁，这时候就涉及重新启动机器或者SQL Server的工作，假设搭建了镜像环境，这个操作就会略微变得复杂了。由于为了高可用的稳定执行，不仅主体server要打补丁，镜像server甚至见证server也要打补丁。
本部分包括两个情况：
- 安装常规补丁
- 升级SQL Server或为SQL Server打补丁
安装常规补丁：
这里指的补丁不是SQL Server的补丁，在镜像环境中，假设打了补丁而且须要重新启动，尽可能避免镜像环境的全部伙伴server都同一时候重新启动，毕竟搭建高可用的原因是为了加大业务连续性。
常规做法是：先对一台server打补丁，重新启动这台server（假设须要重新启动），确保这台机已经正常执行之后，以相同步骤处理下一台。镜像环境中重新启动顺序没有强制规定，依照实践，一般是：见证→镜像→主体server。
升级SQL Server或为SQL Server打补丁：
针对SQL Server打补丁，必须更加慎重，特别是镜像环境出现了见证server。假设带有见证server，首先要移除见证server，并在打完补丁之后把见证server加回去。见证server能够在不论什么时候进行升级。能够在主体server上执行：
ALTER DATABASE [DatabaseName] SET WITNESS OFF;--移除见证server
ALTER DATABASE [DatabaseName] SET WITNESS = 'TCP://WitnessServer.FQDN.com:Port#';.--加入见证server
对于执行模式，建议先设为异步模式，然后再打补丁，在准备Failover时，改为同步模式，使其同步信息之后再Failover。
在正式打补丁时，先对镜像server打补丁，最基本的原因是能够验证补丁是否有效，而且打完之后能否够安全Failover。当镜像server已经打完补丁而且Failover成主体server时，由于补丁的级别不一致，SQL Server会挂起镜像会话，这时候应该尽快对原主体server打补丁，否则挂起太久会引起非常多后患。
故障转移后恢复原有主体server ：
在镜像环境中，人为或者自己主动故障转移都会使镜像server变成主体server，假设这不是你希望的，那么你就须要想办法把如今的主体server变回镜像server，当中一个原因是假设不这样做，你必须准备一个新的镜像server以便下一次故障转移。这里分两步介绍：
- 恢复原有主体server的原因
- 实现原有主体server的恢复
恢复原有主体server的原因：
恢复原有主体server有非常多原因，可是比較重要的原因是：镜像不同步作业、维护计划、SSIS等。假设是非人为Failover，会导致这些组件失败、重试，而有些系统中，这些组件是至关重要的，尽快联机并切换回原有状态是首要任务，另外，镜像server往往性能没有主体server强，长时间接管业务可能导致镜像server面临压力。
实现原有主体server的恢复：
能够使用这个脚本进行恢复：
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一般有两种：自己主动转移和手动转移，手动转移能够非常easy地使用以下语句实现：
ALTER DATABASE [DatabaseName] SET PARTNER FAILOVER --在主体server上执行
在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、伙伴server上的作业、维护计划等或者其它外部资源。2、应用程序依赖的库是否也同一时候转移，不管是否须要。3、账号
针对这些问题，能够使用Windows Management Instrumentation(WMI)命名空间来发送警告。能够通过SSMS打开，如图：
然后配置WMI事件：
然后配置响应页：
最后填写警告信息：
通过这个警告，能够创建一些维护作业响应自己主动故障转移中的潜在问题。除此之外，还能够用Service broker、Powershell等工具来监控。针对具体的监控，能够看下一文：http://blog.csdn.net/dba_huangzj/article/details/26846203