YourSQLDba的翻译(十二)

YourSQLDba包括存储过程去创建SQL镜像。这个镜像会接受还原的数据库从源数据库

SQL镜像实例的用处

SQL镜像实例能成为一个SQL 故障转移的服务器

还原数据库备份检查备份文件的完整性

一个SQLSERVER2008镜像实例能用于迁移数据库从SQLSERVER2005到SQLSERVER2008

使用SQL镜像服务器的准备条件

两个SQL实例必须有同样的YourSQLDba 版本号，要查看YourSQLDba 的版本号，你可以执行Install.PrintVersionInfo 存储过程来显示你当前服务器的YourSQLDba 版本号

备份文件夹必须能够两个实例都可以访问。备份文件夹要能两台机器都可以共享的。备份文件夹在网络上能够访问

备份文件夹在UNC路径下可以访问，UNC格式像 '\\<server name>\<backup folder>'.

源SQL实例启动帐号必须在镜像服务器上有sysadmin权限

对于第一次导出备份，数据库可以不必在镜像实例上存在

在镜像实例上的数据库将被还原，并且当数据库在镜像实例上正在还原的时候，状态会显示 RESTORING

 下面有几个步骤去配置SQL镜像实例

1、选择一个SQL实例作为SQL镜像，里面放要还原的数据库。

所选择的镜像实例和源实例必须遵循下面条件

源实例的数据库版本必须是 SQL2005 、SQL2008、SQL2008R2

镜像实例数据库版本必须是 SQL2005 、SQL2008、SQL2008R2

镜像实例可以是一个生产服务器。没有必要把镜像实例定义为镜像服务器

镜像实例的数据库版本一定要比源实例的数据库版本要新或者要等于源实例的数据库版本

 

 为什麽要这样呢？因为低版本数据库备份文件可以还原到高版本数据库，但是高版本数据库备份文件不能还原到低版本数据库上

2、安装同样版本的YourSQLDba 在SQL源实例和镜像实例上

3、SQL 端口在防火墙上必须打开，无论是源服务器还是镜像服务器

源服务器的输入端口

UDP1433

TCP1433 默认实例

TCP2433 到2449 根据每个命名实例的端口分配

源服务器的输出端口

TCP1433 默认实例

TCP2433 到2449 根据每个命名实例的端口分配

镜像服务器的输入端口

UDP1433

TCP1433 默认实例

TCP2433 到2449 根据每个命名实例的端口分配

镜像服务器的输出端口

TCP1433 默认实例

TCP2433 到2449 根据每个命名实例的端口分配

 

4、在镜像服务器上创建源SQL实例启动帐号，并分配sysadmin权限

5、源服务器完整备份文件目录必须共享

源SQL实例启动帐号必须对共享目录有完全控制权

6、源服务器日志备份文件目录必须共享

源SQL实例启动帐号必须对共享目录有完全控制权

7、在源SQL实例执行Mirroring.AddServer存储过程

8、编辑全局维护作业 YourSQLDba_FullBackups_And_Maintenance 脚本

添加@MirrorServer 参数

 @FullBackupPath 参数和 @LogBackupPath parameters参数 必须使用一个 UNC 格式路径，例如'\\<server name>\<backup folder>'.

9、编辑log backup维护作业 YourSQLDba_LogBackups  脚本

添加@MirrorServer 参数

 @FullBackupPath 参数和 @LogBackupPath parameters参数 必须使用一个 UNC 格式路径，例如'\\<server name>\<backup folder>'.

10、在SQLSERVER代理里启动全局维护作业在源SQL实例上

 

数据如何传送到镜像实例

当Maint.YourSQLDba_DoMaint存储过程或者Maint.SaveDbOnNewFileSet 存储过程附带了 @MirrorServer 参数的被执行

他会执行两个额外步骤

1、在镜像实例上还原数据库（如果还原失败，一个错误的邮件会由SQL 管理员发送）

2、复制所有定义从源服务器到镜像服务器（这会允许镜像实例最终成为一个故障转移SQLSERVER）

 

如何切换应用程序到镜像实例，当源实例发生故障的时候

1、在镜像实例上执行Mirroring.DoRecovery 存储过程去迁移在线数据库

2、改变应用程序的ODBC去使用镜像实例数据库

 

如何切换应用程序的连接从镜像服务器切换回源服务器，当源服务器变为可用的时候

1、在源服务器上执行 Upgrade2008.FailoverForUpgrade存储过程去切换在线数据库到源服务器

2、改变应用程序的ODBC去使用镜像实例数据库

Configure a SQL Mirror Server

  

Updated: 2011-09-21

YourSQLDba includes stocked procedures for create a link to a SQL mirror. This mirror will receive the restored databases from the source Server.

 

SQL mirror instance usefulness

The SQL mirror instance can become a SQL failover server.

Restoring database backups checks the backup files integrity.

A SQL Server 2008 mirror instance can be used to migrate databases from SQL Server 2005 to SQL Server 2008.

 

Prerequisite to use a SQL mirror server

The two SQL instances must have the same YourSQLDba version number. To see the version number of YourSQLDba present on a server ; you can execute the Install.PrintVersionInfo stocked procedure on that server.

The backup folders must be accessible by both instances. The backup folders can be on either machine with a share for the other machine. The backup folders can be on the network. The backup folders must be shared in the UNC format like '\\<server name>\<backup folder>'.

The @FullBackupPath and @LogBackupPath parameters of the Maint.YourSQLDba_DoMaint procedure in the SQL maintenance job executed by the SQL Server Agent in the SQL origin server, must use a UNC format like '\\<server name>\<backup folder>'.

The source SQL instance startup account must be defined on the mirror server with sysadmin rights.

On the first export, the databases must not exist on the mirror instance.

The databases to be restored on the mirror instance must stay in RESTAURING state on the mirror instance while this SQL instance is a mirror for the database.

 

Steps to configure an SQL mirror instance

Choose an SQL instance which will be used as a SQL mirror for the databases to be restored. The selected SQL mirror instance and the SQL instance of origin must respect the following conditions:

The SQL instance of origin must be of one of following editions: SQL Server 2005 or SQL Server 2008 or SQL Server 2008 R2.

The SQL mirror instance must be of one of following editions: SQL Server 2005 or SQL Server 2008 or SQL Server 2008 R2.

The SQL mirror instance can be a production server. It is not necessary to dedicate the mirror instance as a mirror server.

The SQL mirror instance must be of an edition more recent or equal to the SQL instance of origin.

Valid SQL instance editions combinations 

SQL instance of origin  SQL mirror instance 

SQL Server 2005  SQL Server 2005 

SQL Server 2005  SQL Server 2008 

SQL Server 2005  SQL Server 2008 R2 

SQL Server 2008  SQL Server 2008 

SQL Server 2008  SQL Server 2008 R2 

SQL Server 2008 R2  SQL Server 2008 R2 

 

Install the same version of YourSQLDba in the SQL instance of origin and in the SQL mirror instance.

 

SQL ports must be open in the firewall of the server of origin and in the firewall of the destination server.

Input ports for the server of origin:

UDP 1433

TCP 1433 for a default SQL instance

TCP 2433 to 2449 depending on the assigned port for each named SQL instance

Output ports for the server of origin:

TCP 1433 for a default SQL instance

TCP 2433 to 2449 depending on the assigned port for each named SQL instance

Input ports for the mirror server:

UDP 1433

TCP 1433 for a default SQL instance

TCP 2433 to 2449 depending on the assigned port for each named SQL instance

Output ports for the mirror server:

TCP 1433 for a default SQL instance

TCP 2433 to 2449 depending on the assigned port for each named SQL instance

 

Create the source SQL instance startup account on the mirror server with sysadmin rights.

 

The source server full backup directory must be shared.

The source SQL instance startup account (<Domaine>\<AdminAccount>)  must have total control on the directory.

 

The source server log backup directory must be shared.

The source SQL instance startup account (<Domaine>\<AdminAccount>)  must have total control on the directory.

 

Execute the Mirroring.AddServer procedure in the SQL instance of origin.

 

Edit the global maintenance job YourSQLDba_FullBackups_And_Maintenance script in SQL Server Agent in the SQL instance of origin which executes the Maint.YourSQLDba_DoMaint procedure.

Add the @MirrorServer parameter.

The @FullBackupPath and @LogBackupPath parameters must use a UNC format like '\\<server name>\<backup folder>'.

 

Edit the log backup maintenance job YourSQLDba_LogBackups script in SQL Server Agent in the SQL instance of origin which executes the Maint.YourSQLDba_DoMaint procedure.

Add the @MirrorServer parameter.

The @FullBackupPath and @LogBackupPath parameters must use a UNC format like '\\<server name>\<backup folder>'.

 

Start the global maintenance job in SQL Server Agent in the SQL instance of origin.

 

How the data is sent to the SQL mirror instance

When the Maint.YourSQLDba_DoMaint procedure or the Maint.SaveDbOnNewFileSet procedure are executed with the @MirrorServer parameter these procedures executes two supplementary steps:

Restoration of the databases to the SQL mirror instance. (If the restoration fails ; an error email is sent to the SQL administrator.)

Copy all SQL connexions definition from the SQL instance of origin to the SQL mirror instance. (This will allow the mirror instance to eventually become a failover SQL server.)

 

How to switch applications links to the failover SQL mirror instance when the source server is out of service

Execute the Mirroring.DoRecovery procedure on the SQL mirror instance to bring online the databases.

Change the ODBC's for the applications to use the failover SQL mirror instance.

How to switch applications links to the failover SQL mirror instance when the source server is available

Execute the Upgrade2008.FailoverForUpgrade procedure on the SQL source instance to bring online the databases.

Change the ODBC's for the applications to use the failover SQL mirror instance.