AI 助理
备案控制台
开发者社区 数据库 文章 正文

自动创建数据库镜像，证书交换

2017-03-16 906
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
简介： 脚本有2个部分，1.证书交换，2.配置镜像只实现了简答的功能，比如镜像证书配置的检查，容错目前都还没有做1.证书交换$SourceServer='192.168.5.7'$SourcePath='d:\SQL Backups'$SourceUser='adminator'$Source...

脚本有2个部分，1.证书交换，2.配置镜像

只实现了简答的功能，比如镜像证书配置的检查，容错目前都还没有做

1.证书交换

$SourceServer='192.168.5.7'
$SourcePath='d:\SQL Backups'
$SourceUser='adminator'
$SourcePassword='Fam901'
$SourceDBUser='sa'
$SourceDBPassword='Fam901'
$SourceCertName='SQL17'
$DestServer='192.168.0.16'
$DestPath='d:\SQL Backups'
$DestUser='adminitor'
$DestPassword='Fam901'
$DestDBUser='sa'
$DestDBPassword='Fam901'
$DestCertName='SQL16'

Function SetupCertificate {
    Param([string]$server,[string]$database,[string]$uid,[string]$password,[string]$CertName)
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
    $CnnString ="Server = $server; Database = $database;User Id = $uid; Password = $password" 
    $SqlConnection.ConnectionString = $CnnString 
    $CC = $SqlConnection.CreateCommand(); 
    if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() } 
    $str=" 
        
    
    USE master
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
    
     
    USE master;
    CREATE CERTIFICATE $CertName
       WITH SUBJECT = '$CertName certificate for database mirroring',
    EXPIRY_DATE= '08/27/2099';
     
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=5022
          , LISTENER_IP = ALL
       )
       FOR DATABASE_MIRRORING (
          AUTHENTICATION = CERTIFICATE $CertName
          , ENCRYPTION = REQUIRED ALGORITHM AES
          , ROLE = ALL
       );
    BACKUP CERTIFICATE $CertName TO FILE = '$SourcePath\$CertName.cer';


" 
    $str
    $cc.CommandText=$str
    $cc.ExecuteNonQuery()|out-null 
    $SqlConnection.Close();
}

Function LoadCertificate {
    Param([string]$server,[string]$database,[string]$uid,[string]$password,[string]$CertName,[string]$CertPath)
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
    $CnnString ="Server = $server; Database = $database;User Id = $uid; Password = $password" 
    $SqlConnection.ConnectionString = $CnnString 
    $CC = $SqlConnection.CreateCommand(); 
    if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() } 
    $str=" 
        
    
    USE master;
    CREATE LOGIN "+$CertName+"_login
       WITH PASSWORD = '1Sample_Strong_Password!@#';
     
    USE master;
    CREATE USER "+$CertName+"_user FOR LOGIN "+$CertName+"_login;
     
    USE master;
    CREATE CERTIFICATE $CertName
       AUTHORIZATION "+$CertName+"_user
       FROM FILE = '$CertPath\$CertName.cer'
     
    USE master;
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ["+$CertName+"_login];



" 
    $str
    $cc.CommandText=$str
    $cc.ExecuteNonQuery()|out-null 
    $SqlConnection.Close();
}

Function CheckRmoteDir{
    #######################################
    #Check and create dir remote 
    ######################################
    
    #
    
$password = $SourcePassword | ConvertTo-SecureString -asPlainText -Force
$succredential = New-Object System.Management.Automation.PSCredential $SourceUser,$password

$password = $DestPassword | ConvertTo-SecureString -asPlainText -Force
$destcredential = New-Object System.Management.Automation.PSCredential $DestUser,$password
    
    $sourcesession=New-PSSession -ComputerName $SourceServer -Credential $succredential
    $destsession=New-PSSession -ComputerName $DestServer -Credential $destcredential
    #
    invoke-command -session $sourcesession -scriptblock {
        if (-not(Test-Path $args[0])){ mkdir $args[0] }
    } -ArgumentList $SourcePath 

    invoke-command -session $destsession -scriptblock {
        if (-not(Test-Path $args[0])){ mkdir $args[0] }
    } -ArgumentList $DestPath 
}



CheckRmoteDir

$srcUNC = Join-Path "\\$($SourceServer.Split('\\')[0])" $($SourcePath.Replace(':','$'))
if (-not(Test-Path $srcUNC)) { New-PSDrive -Name s -psprovider FileSystem -root $srcUNC -Credential $succredential }
$destUNC = Join-Path "\\$($DestServer.Split('\\')[0])" $($DestPath.Replace(':','$'))
if (-not(Test-Path $destUNC)) { New-PSDrive -Name d -psprovider FileSystem -root $destUNC -Credential $destcredential }

Test-Path $srcUNC
Test-Path $destUNC


SetupCertificate -server $SourceServer -database "master" -uid $SourceDBUser -password $SourceDBPassword -CertName $SourceCertName
SetupCertificate -server $DestServer -database "master" -uid $DestDBUser -password $DestDBPassword -CertName $DestCertName

$bkpfile = $SourceCertName+".cer"
Copy-Item $(Join-Path $srcUNC $bkpfile) -Destination $destUNC -Verbose
$bkpfile = $destCertName+".cer"
Copy-Item $(Join-Path $destUNC $bkpfile) -Destination $srcUNC -Verbose


LoadCertificate -server $SourceServer -database "master" -uid $SourceDBUser -password $SourceDBPassword -CertName $DestCertName -Certpath $SourcePath
LoadCertificate -server $DestServer -database "master" -uid $DestDBUser -password $DestDBPassword -CertName $SourceCertName -Certpath $SourcePath

2.配置镜像

<#
     .SYNOPSIS
          Set up a mirrored database
     .DESCRIPTION
          Backs up a database and tlog, copies it to the destination,
          Restores the database on the mirror server, sets up the partner,
          and starts the mirror.
     .PARAMETER  database
          The name of the database to be mirrored
     .PARAMETER  SourceServer
          The name of the primary server
     .PARAMETER  SourcePath
          Local Path for the backup
     .PARAMETER  DestServer
          The name of mirror server
     .PARAMETER  DestPath
          Local path for restore file
     .EXAMPLE
          PS C:\> Invoke-Mirror -database 'string value' 1
                    -SourceServer 'string\string' -SourcePath 'string' `
                    -DestServer 'string\string' -DestPath 'string'
     .NOTES
          AUTHOR:    John P. Wood
          CREATED:   July, 2010
          VERSION:   1.0.5
          The SQL connections rely on Windows authentication and assumes Endpoints
          already exist. Error checking is minimal (i.e. no check is made to
          verify the recovery model is FULL).
#>
#Param(
#    [Parameter(Mandatory=$true)]
#    [string]$database,
#    [string]$SourceServer='lcfsqlvs3\sqlvs3',
#    [string]$SourcePath='U:\SQL Backups',
#    [string]$DestServer='ldrsqlvs3\sqlvs3',
#    [string]$DestPath='U:\SQL Backups'
#    )

    $database='mirror_test'
    $SourceServer='192.168.5.17'
    $SourcePath='d:\SQL Backups'
    $SourceUser='adminiator'
    $SourcePassword='Fam901'
    $SourceDBUser='sa'
    $SourceDBPassword='Fam901'
    $DestServer='192.168.5.16'
    $DestPath='d:\SQL Backups'
    $DestUser='adminisor'
    $DestPassword='Fam901'
    $DestDBUser='sa'
    $DestDBPassword='Fams901'
    
Set-StrictMode -Version 2
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
Function Get-FileName {
    Param([string]$path)
    $names = $path.Split('\\')
    $names[$names.Count - 1]
}
Function New-SMOconnection {
    Param (
        [string]$server,
        [string]$usr,
        [string]$password
    )
    
    $pwd = $password | ConvertTo-SecureString -asPlainText -Force
    $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server,$usr ,$pwd)
    $conn.applicationName = "PowerShell SMO"
    $conn.StatementTimeout = 0
    $conn.Connect()
    if ($conn.IsOpen -eq $false) {
        Throw "Could not connect to server $($server) for database backup of $($dbname)."
    }
    $smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
    $smo
}
Function Invoke-SqlBackup {
    $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
    $dbbk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
    $dbbk.BackupSetDescription = "Full backup of " + $database
    $dbbk.BackupSetName = $database + " Backup"
    $dbbk.Database = $database
    $dbbk.MediaDescription = "Disk"
    $device = "$SourcePath\$bkpfile"
    $dbbk.Devices.AddDevice($device, 'File')
    $smo = New-SMOconnection -server $SourceServer -usr $SourceDBUser -password $SourceDBPassword
    Try {
        $dbbk.SqlBackup($smo)
        $dbbk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Log
        $dbbk.SqlBackup($smo)
        $smo.ConnectionContext.Disconnect()
    }
    Catch {
        $ex = $_.Exception
        Write-Output $ex.message
        $ex = $ex.InnerException
        while ($ex.InnerException)
        {
            Write-Output $ex.InnerException.message
            $ex = $ex.InnerException
        };
        continue
    }
    Finally {
        if ($smo.ConnectionContext.IsOpen -eq $true) {
            $smo.ConnectionContext.Disconnect()
        }
    }
}
Function Invoke-SqlRestore {
    Param(
        [string]$filename
    )
    # Get a new connection to the server
    $smo = New-SMOconnection -server $DestServer -usr $DestDBUser -password $DestDBPassword
    $backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($filename, "File")
    # Get local paths to the Database and Log file locations
    If ($smo.Settings.DefaultFile.Length -eq 0) {$DBPath = $smo.Information.MasterDBPath }
    Else { $DBPath = $smo.Settings.DefaultFile}
    If ($smo.Settings.DefaultLog.Length -eq 0 ) {$DBLogPath = $smo.Information.MasterDBLogPath }
    Else { $DBLogPath = $smo.Settings.DefaultLog}
 
    # Load up the Restore object settings
    $Restore = new-object Microsoft.SqlServer.Management.Smo.Restore
    $Restore.Action = 'Database'
    $Restore.Database = $database
    $Restore.ReplaceDatabase = $true
    $Restore.NoRecovery = $true
    $Restore.Devices.Add($backupDevice)
 
    # Get information from the backup file
    $RestoreDetails = $Restore.ReadBackupHeader($smo)
    $DataFiles = $Restore.ReadFileList($smo)
 
    # Restore all backup files
    ForEach ($DataRow in $DataFiles) {
        $LogicalName = $DataRow.LogicalName
        $PhysicalName = Get-FileName -path $DataRow.PhysicalName
        $RestoreData = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
        $RestoreData.LogicalFileName = $LogicalName
        if ($DataRow.Type -eq "D") {
            # Restore Data file
            $RestoreData.PhysicalFileName = $DBPath + "\" + $PhysicalName
        }
        Else {
            # Restore Log file
            $RestoreData.PhysicalFileName = $DBLogPath + "\" + $PhysicalName
        }
        [Void]$Restore.RelocateFiles.Add($RestoreData)
 
    }
    Try {
    $Restore.SqlRestore($smo)
    # If there are two files, assume the next is a Log
    if ($RestoreDetails.Rows.Count -gt 1) {
        $Restore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
        $Restore.FileNumber = 2
        $Restore.SqlRestore($smo)
    }
        $smo.ConnectionContext.Disconnect()
    }
    Catch {
        $ex = $_.Exception
        Write-Output $ex.message
        $ex = $ex.InnerException
        while ($ex.InnerException)
        {
            Write-Output $ex.InnerException.message
            $ex = $ex.InnerException
        };
        continue
    }
    Finally {
        if ($smo.ConnectionContext.IsOpen -eq $true) {
            $smo.ConnectionContext.Disconnect()
        }
    }
}
Function Set-Mirror { 
  
    Param([string]$server,[string]$db,[string]$uid,[string]$password,[string]$partner)
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
    $CnnString ="Server = $server; Database = $db;User Id = $uid; Password = $password" 
    $SqlConnection.ConnectionString = $CnnString 
    $CC = $SqlConnection.CreateCommand(); 
    if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() } 
    $str= "
    ALTER DATABASE $database SET PARTNER off    
    ALTER DATABASE $database SET PARTNER = 'TCP://" + $partner + ":5022'"
    $str
    $cc.CommandText =$str
    $cc.ExecuteNonQuery()|out-null 
    $SqlConnection.Close();
}

Function CheckRmoteDir{
    #######################################
    #Check and create dir remote 
    ######################################
    
    #
    $sourcesession=New-PSSession -ComputerName $SourceServer -Credential $succredential
    $destsession=New-PSSession -ComputerName $DestServer -Credential $destcredential
    #
    invoke-command -session $sourcesession -scriptblock {
        if (-not(Test-Path $args[0])){ mkdir $args[0] }
    } -ArgumentList $SourcePath 

    invoke-command -session $destsession -scriptblock {
        if (-not(Test-Path $args[0])){ mkdir $args[0] }
    } -ArgumentList $DestPath 
}


$password = $SourcePassword | ConvertTo-SecureString -asPlainText -Force
$succredential = New-Object System.Management.Automation.PSCredential $SourceUser,$password

$password = $DestPassword | ConvertTo-SecureString -asPlainText -Force
$destcredential = New-Object System.Management.Automation.PSCredential $DestUser,$password

CheckRmoteDir

$srcUNC = Join-Path "\\$($SourceServer.Split('\\')[0])" $($SourcePath.Replace(':','$'))
if (-not(Test-Path $srcUNC)) { New-PSDrive -Name s -psprovider FileSystem -root $srcUNC -Credential $succredential }
$destUNC = Join-Path "\\$($DestServer.Split('\\')[0])" $($DestPath.Replace(':','$'))
if (-not(Test-Path $destUNC)) { New-PSDrive -Name d -psprovider FileSystem -root $destUNC -Credential $destcredential }

Test-Path $srcUNC
Test-Path $destUNC

$bkpfile =  $($SourceServer.Replace("\", "$")) + "_" + $database + "_FULL_" + $(get-date -format yyyyMMdd-HHmmss) + ".bak"

Invoke-SqlBackup
Copy-Item $(Join-Path $srcUNC $bkpfile) -Destination $destUNC -Verbose
$bkpfile = $DestPath +"\" + $bkpfile
$bkpfile
Invoke-SqlRestore -filename $bkpfile
# Establish Mirroring from the mirrored database
Set-Mirror -server $DestServer -db "master" -uid $DestDBUser -password $DestDBPassword  -partner $($SourceServer.Split('\\')[0])
# Start the mirror
Set-Mirror -server $SourceServer -db "master" -uid $SourceDBUser -password $SourceDBPassword   -partner $($DestServer.Split('\\')[0])

 

文章标签：
云数据库 RDS SQL Server 版
数据库
SQL
Windows
网络协议
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中，使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库，该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品：云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能，强力支撑各种企业应用，同时也包含了微软的License费用，减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
fanr_zh
目录
相关文章
好奇的菜鸟
|
5月前
|
存储
Cloudstack启动实例模板从二级存储复制到主存储过程
Cloudstack启动实例模板从二级存储复制到主存储过程
好奇的菜鸟
22 0
疯狂学习GIS
|
6月前
|
SQL 存储 定位技术
数据库基础（一）：数据库创建、分离、附加、删除、备份
数据库基础（一）：数据库创建、分离、附加、删除、备份
疯狂学习GIS
92 2
honeymoose
|
存储 Java 数据库
Confluence 6 数据库和临时目录
数据库 所有的其他数据库，包括有页面，内容都存储在数据库中。如果你安装的 Confluence 是用于评估或者你选择使用的是 Embedded H2 Database 数据库。
honeymoose
1165 0
浩秦
|
数据库
中煜软件，数据库删除凭证
SELECT * FROM [Production].[dbo].[glVoucher] where voucherModule like 'IC' and date >= '2017-10-01' DELETE FROM [Production].[dbo].[glVoucher] where voucherModule like 'IC' and date >= '2017-10-01' 删除凭证  网名：浩秦; 邮箱：root#landv.pw; 只要我能控制一個國家的貨幣發行，我不在乎誰制定法律。
浩秦
938 0
技术小阿哥
|
数据库 运维
Exchange2013&2016删除默认邮箱数据库及移动数据库路径
技术小阿哥
2704 0
技术小阿哥
|
存储
关于存储服务器强制起盘命令
技术小阿哥
921 0
科技小先锋
|
存储 数据库 数据库管理
管理邮箱服务器的存储组和数据库
科技小先锋
1315 0
技术小胖子
|
SQL 数据库 网络协议
自动创建数据库镜像，证书交换
技术小胖子
814 0
技术小胖子
|
网络协议 数据库 数据安全/隐私保护
数据库镜像证书验证配置和维护
技术小胖子
1025 0
科技小先锋
|
数据库
卸载exchange server 2010过程中，无法删除公共文件夹数据库
科技小先锋
1441 0

热门文章

最新文章

  • 1
    Flutter之禅 内存优化篇
  • 2
    《Cadence 16.6电路设计与仿真从入门到精通》——2.4 Design Entry CIS原理图图形界面　
  • 3
    记一次对网络抖动经典案例的分析
  • 4
    国外成人网站xHamster大量用户信息黑市变卖，部分账号涉及英美政府官员
  • 5
    用户密码以BCrypt加密的方式来防范被破解
  • 6
    带你理清CPU，cache和存储器之间的逻辑运作
  • 7
    Git设置代理服务器
  • 8
    【DSW Gallery】PAI-DSW开通及授权
  • 9
    吐血整理：机器学习的30个基本概念，都在这里了（手绘图解）
  • 10
    MOSS站点的迁移(备份还原)
  • 1
    设计模式之 5 大创建型模式，万字长文深剖 ，近 30 张图解！
    49
  • 2
    《C++新特性：为多线程数据竞争检测与预防保驾护航》
    41
  • 3
    Kotlin - 区间与数组
    41
  • 4
    Kotlin - 参数与异常
    39
  • 5
    Kotlin - 运算符与中缀表达式
    34
  • 6
    Kotlin - 函数与Lambda表达式
    23
  • 7
    Kotlin - 分支与循环
    19
  • 8
    天气预报1天-中国气象局-地址查询版免费API接口教程
    20
  • 9
    《C++智能合约与区块链底层交互全解析：构建坚实的去中心化应用桥梁》
    19

    • 相关课程

    更多
  • 数据库常见问题排查
  • 数据库的前世今生
  • 从0到1数据库内核实战教程
  • 数据库仓库升级交付实践指引
  • 大咖说 - 开源数据库
  • 数据库核心概念

    • 相关电子书

    更多
  • 数据库异地备份及不还原快速查询备份集最佳实践
  • 阿里云数据库案例集下载
  • 数据库2025 V3

    • 相关实验场景

    更多
  • 使用DAS实现数据库SQL优化
  • 自建数据库迁移到云数据库
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 云原生HTAP数据库，让你的交易和分析一库搞定
  • 快速体验PolarDB开源数据库
  • 容器的配置和存储
    • 下一篇
    阿里云OSS设置跨域访问