如何运用SQLserver镜像库获取数据副本

什么是SQL Server镜像库及其作用

SQL Server镜像库是SQL Server高可用性解决方案之一，它允许将一个数据库的镜像副本保持在一个不同的SQL Server实例上。SQL Server镜像库可以帮助管理员提高应用程序的可靠性、可用性和数据保护性。当主要数据库不可用时，镜像副本可以帮助应用程序从镜像副本中快速恢复并继续运行。

SQL Server镜像库是一个异步的数据保护解决方案，其意味着主数据库的更改在镜像副本中可能不会立即反映出来。如果管理员在主数据库上执行一个事务，则在提交该事务并将更改复制到镜像副本之前，镜像副本可能不会反映出该更改。

SQL Server镜像库的配置

配置主数据库

要配置主数据库以与SQL Server镜像副本一起使用，管理员必须在数据库上设置以下属性：安全选项、可靠性属性、镜像选项和主键。管理员可以通过以下步骤将主数据库配置为使用SQL Server镜像库：

-- 开启数据库镜像选项
ALTER DATABASE [dbname] SET PARTNER SAFETY OFF; -- 设置安全检查，提高镜像速度
ALTER DATABASE [dbname] SET RECOVERY FULL; -- 设置可靠性属性
ALTER DATABASE [dbname] SET PARTNER TIMEOUT 10; -- 设置主、副本通信时间超时为10秒
ALTER DATABASE [dbname] SET PARTNER RESUME; -- 恢复与镜像数据库的连接
ALTER DATABASE [dbname] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS OFF; -- 防止强制服务允许丢失数据
ALTER DATABASE [dbname] SET PARTNER FAILOVER -- 选择指向镜像数据库的数据库副本

此外，还需要在数据库上创建一个主键。主键是数据库的主要标识符，可以帮助管理员准确地找到需要进行镜像复制的数据。要创建主键，管理员可以执行以下操作：

ALTER TABLE [tablename] ADD CONSTRAINT [PK_tablename_id] PRIMARY KEY CLUSTERED ([id])

配置镜像数据库

在配置主数据库之后，管理员必须再配置镜像副本。要配置镜像副本，管理员应在镜像数据库上执行以下操作：

-- 获取配置主数据库的URL
SELECT GROUP_ID, PARTNER, PARTNER_URL, SAFETY_LEVEL
FROM sys.database_mirroring 
WHERE mirroring_state_desc = 'PRINCIPAL'; --获取主库url
-- 在副本数据库上启用镜像
ALTER DATABASE [dbname] SET PARTNER SAFETY OFF; -- 设置安全检查，提高镜像速度
ALTER DATABASE [dbname] SET PARTNER TIMEOUT 10; -- 设置主、副本通信时间超时为10秒
ALTER DATABASE [dbname] SET PARTNER RESUME; -- 恢复与主数据库的连接
ALTER DATABASE [dbname] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS OFF; -- 防止强制服务允许丢失数据
ALTER DATABASE [dbname] SET PARTNER FAILOVER -- 选择指向主要数据库的数据库副本

复制主数据库到镜像数据库

在配置完主数据库和镜像数据库之后，数据库管理员应该复制数据库中的数据。要完成此操作，管理员可以使用本地备份并将其还原到镜像副本，或使用SQL Server提供的内置功能将主副本之间的更改自动复制到镜像副本中。

使用本地备份还原到镜像副本

管理员可以使用SQL Server备份和还原功能，通过本地备份将主数据库复制到镜像副本。要完成此操作，管理员可以执行以下步骤：

在主数据库上启用备份：

BACKUP DATABASE [dbname] TO DISK = N'C:\path\backup.bak' WITH NOFORMAT, INIT, NAME=N'dbname-full database backup', SKIP, NOREWIND, NOUNLOAD, STATS=10 -- 备份语句

将备份文件传输到镜像副本上

在镜像副本上还原备份文件：

RESTORE DATABASE [dbname] FROM DISK = N'C:\path\backup.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10 -- 还原语句

使用内置功能复制更改

管理员可以使用SQL Server提供的内置功能将主副本之间的更改自动复制到镜像副本中。要启用此功能，管理员应在主数据库和镜像副本上执行以下操作：

在主数据库上启用数据库镜像功能

在镜像副本上启用数据库镜像功能

在主数据库上为镜像副本配置及状态设置指定的URL（主库url）

在主数据库上将数据库设置为镜像模式并启动镜像

ALTER DATABASE [dbname] SET PARTNER = 'TCP://mirrorserver.example.com:7024'; -- 设置镜像库URL
ALTER DATABASE [dbname] SET PARTNER SAFETY OFF; -- 开启安全性检查
ALTER DATABASE [dbname] SET PARTNER TIMEOUT 10; -- 设置通信超时为10秒
ALTER DATABASE [dbname] SET PARTNER RESUME;
ALTER DATABASE [dbname] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS OFF;
ALTER DATABASE [dbname] SET PARTNER FAILOVER;
ALTER DATABASE [dbname] SET MIRRORING PARTNER SAFETY OFF; -- 开启事务传送
ALTER DATABASE [dbname] SET MIRRORING TIMEOUT 10; -- 设置事务传送的超时为10秒
ALTER DATABASE [dbname] SET MIRRORING REDO_QUEUE_TYPE = DISK; --设置交易记录队列类型
ALTER DATABASE [dbname] SET MIRRORING REDO_QUEUE_SIZE = 5 GB; --设置交易记录队列大小
ALTER DATABASE [dbname] SET WITNESS OFF; --设置监视者
ALTER DATABASE [dbname] SET MIRRORING SET SAFETY FULL; --设置镜像安全性级别
ALTER DATABASE [dbname] SET MIRRORING FAILOVER --指向镜像库

验证主数据库和镜像数据库之间进行了同步

如何使用SQL Server镜像库获取数据副本

使用SQL Server镜像库获取数据副本的过程非常简单，只需要连接到镜像副本即可访问副本数据库。在连接到数据库时，可以使用SQL Server Management Studio（SSMS）或应用程序中的连接字符串来指定连接字符串。连接字符串应指向镜像副本，而不是主数据库。

--连接镜像库
server=mirrorserver.example.com:7024;database=dbname;uid=sa;pwd=mypass

总结

SQL Server镜像库是SQL Server高可用性解决方案之一，它提高了数据库应用程序的可靠性和可用性。配置SQL Server镜像库需要管理员对两个数据库实例进行配置，并复制数据以创建副本。连接到副本时，连接字符串应指向镜像副本而不是主数据库。 通过SQL Server镜像库获取数据副本，可以确保应用程序在主副本不可用时可以使用镜像副本继续运行。