第三篇——第二部分——第五文配置SQL Server镜像——域环境SQL Server镜像日常维护...

亦凉 2022-03-31 13:58 237阅读 0赞

原文: [  第三篇——第二部分——第五文 配置SQL Server镜像——域环境SQL Server镜像日常维护 ][_ _SQL Server_SQL Server_]

本文接上面两篇搭建镜像的文章：

第三篇——第二部分——第三文 配置SQL Server镜像——域环境：[http://blog.csdn.net/dba\_huangzj/article/details/28904503  
][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_27652857]

在搭建的过程中，可能你会遇到比较多的问题，下面介绍一些常见的问题及解决方案，另外把主要精力放到对镜像搭建后的日常维护中。而对镜像的监控及优化，会在下一篇文章中介绍：[http://blog.csdn.net/dba\_huangzj/article/details/26846203][http_blog.csdn.net_dba_huangzj_article_details_26846203] 。

原文出处：[http://blog.csdn.net/dba\_huangzj/article/details/30484925][_ _SQL Server_SQL 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    
    ------------------------------

截图如下：

[![image][]][image 1]

当出现这种情况时，对于大库就比较痛苦了，你必须使用NORECOVERY选项重新还原。 说白了，你刚才的还原已经没用了。并且为了追上主体服务器的进度，还需要还原一系列的日志备份，注意日志备份同样需要使用NORECOVERY选项。只要其中一个没用到NORECOVERY，必须从头开始。

## 事务日志问题： ##

事务日志的问题，是前面说过的在搭建镜像前，要停用所有相关库的日志备份作业的原因之一。如果在开始镜像前没有还原事务日志到镜像服务器上，或者在镜像服务器上已经还原了完整备份之后，又在主体服务器上进行了日志备份，却没有把备份文件同步到镜像服务器时，会出现下面两类错误：

1.  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.这种情况是发生在做了一次完整备份，并把备份文件还原到镜像库中，然后还没开始镜像配置时又在主体服务器做了一次日志备份导致的，解决这种问题，只需要把日志文件重新还原到镜像数据库即可。
2.  [![image][image 2]][image_image 2]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中：

1.  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.
2.  Error 1486 - Database Mirroring Transport is disabled in the endpoint configuration.
3.  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][http_msdn.microsoft.com_zh-cn_library_aa337361.aspx]

针对上面三种错误，常规步骤如下：

1.  确保SQL Server不是使用Local System 账号运行。必须使用Network Service或者域账号运行，除非使用证书验证。
2.  尝试Telnet每个需要用到的端口，如果不行，可能需要配置防火墙。
3.  查询sys.database\_mirroring\_endpoints目录视图，确保镜像环境中的每个端点的加密和授权方式一致。
4.  查询sys.tcp\_endpoints目录视图，确保数据库镜像端点已经启动，如果没启动，可以使用下面语句实现：

ALTER ENDPOINT 端点名 STATE=STARTED

## 小结： ##

根据本人经历，配置过程的问题很多时候是没有根据“规定”和“规范”来做，缺少了或者做错了一些操作导致的，所以建议读者仔细研究搭建部分，跟着做基本上就不会有太多问题，等对搭建和使用都有了一定心得之后，再进行扩展使用。

# 第二部分：SQL Server镜像日常维护： #

如果你搭建好高可用方案就觉得已经完事的话，后果将非常严重。所以本文重点介绍常规的镜像维护，记住维护工作极其重要。

维护工作主要包含下面几个部分：

1.  事务日志备份
2.  监控日志文件大小
3.  SQL Server常规升级
4.  故障转移后恢复原有主体服务器
5.  数据库配置
6.  控制故障转移
7.  自动故障转移

##     ##

## 事务日志备份： ##

镜像的核心思想就是对事务日志的操作，这也是镜像必须使用完整恢复模式的原因之一。抛开高可用，任何完整模式下的数据库如果缺乏维护，日志文件都会出现非预期增长，最终导致数据库变成只读。根据拇指定律，核心系统的日志备份监控不宜大于半个小时。如果事务量巨大，甚至需要把备份间隔缩短到15分钟。

对于事务日志的管理，很重要的一个工作是做常规的日志备份，日志备份可以用维护计划实现，也可以写脚本实现，更多信息可以看《SQL Server扫盲》系列的日志备份一文：[http://blog.csdn.net/dba\_huangzj/article/details/26844859][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][http_support.microsoft.com_kb_947462] 这里提到的补丁已经打上。日志过大，会导致初始化过程变得很慢甚至超时，针对日志过大，首先要检查是否有常规的日志备份，其次就是检查是否有未关闭的事务并且运行了很久。

在确定日志文件已经有常规备份并且实在太大是，收缩日志文件可能是必须做的事情，但是通常收缩应该是最后一步。这里包含两个部分：

1.  识别需要收缩的日志
2.  收缩日志文件

###     ###

### 识别需要收缩的日志： ###

这里使用脚本查询主体服务器上已经配置为镜像的库机器日志文件大小：

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的工作，如果搭建了镜像环境，这个操作就会稍微变得复杂了。因为为了高可用的稳定运行，不仅主体服务器要打补丁，镜像服务器甚至见证服务器也要打补丁。

本部分包含两个情况：

1.  安装常规补丁
2.  升级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会挂起镜像会话，这时候应该尽快对原主体服务器打补丁，否则挂起太久会引起很多后患。

## 故障转移后恢复原有主体服务器 ： ##

在镜像环境中，人为或者自动故障转移都会使镜像服务器变成主体服务器，如果这不是你希望的，那么你就需要想办法把现在的主体服务器变回镜像服务器，其中一个原因是如果不这样做，你必须准备一个新的镜像服务器以便下一次故障转移。这里分两步介绍：

1.  恢复原有主体服务器的原因
2.  实现原有主体服务器的恢复

###     ###

### 恢复原有主体服务器的原因： ###

恢复原有主体服务器有很多原因，但是比较重要的原因是：镜像不同步作业、维护计划、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打开，如图：

[![image][image 3]][image_image 3]

然后配置WMI事件：

[![image][image 4]][image_image 4]

然后配置响应页：

[![image][image 5]][image_image 5]

最后填写警告信息：

[![image][image 6]][image_image 6]

通过这个警告，可以创建一些维护作业响应自动故障转移中的潜在问题。除此之外，还可以用Service broker、Powershell等工具来监控。针对详细的监控，可以看下一文：[http://blog.csdn.net/dba\_huangzj/article/details/26846203][http_blog.csdn.net_dba_huangzj_article_details_26846203]

[_ _SQL Server_SQL Server_]: http://blog.csdn.net/dba_huangzj/article/details/30484925
[http_blog.csdn.net_dba_huangzj_article_details_28904503]: http://blog.csdn.net/dba_huangzj/article/details/28904503
[http_blog.csdn.net_dba_huangzj_article_details_27652857]: 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/26846203
[image]: /images/20220331/b8c02c635a8d4efeae2290f90067bd9f.png
[image 1]: https://img-blog.csdn.net/20140613143242937
[image 2]: https://img-blog.csdn.net/20140613143244500
[image_image 2]: /images/20220331/085fc20dbb5d47a983393c191c0b31bf.png
[http_msdn.microsoft.com_zh-cn_library_aa337361.aspx]: http://msdn.microsoft.com/zh-cn/library/aa337361.aspx
[http_blog.csdn.net_dba_huangzj_article_details_26844859]: http://blog.csdn.net/dba_huangzj/article/details/26844859
[http_support.microsoft.com_kb_947462]: http://support.microsoft.com/kb/947462
[image 3]: https://img-blog.csdn.net/20140613143245625
[image_image 3]: /images/20220331/3f0ae9e24789450f86e64eab463e6605.png
[image 4]: https://img-blog.csdn.net/20140613143246921
[image_image 4]: /images/20220331/8146a9967c704207bbc5cc26e44bf6de.png
[image 5]: https://img-blog.csdn.net/20140613143248078
[image_image 5]: /images/20220331/8a3bf5a49bd646baa71f88cc24999189.png
[image 6]: https://img-blog.csdn.net/20140613143249390
[image_image 6]: /images/20220331/5a08c130d7724d62be9aaaff949603e5.png