Tutoriel Windows Azure - Disposer d'une base de données live de secours

Import-module azure
Get-command -module azure

Add-AzureAccount

Get-AzureSubscription

Select-AzureSubscription -Default "Azdem169A44055X"

netsh advfirewall firewall add rule `
    name='SQL Server (TCP-In)' `
    program='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe' `
    dir=in `
    action=allow `
    protocol=TCP

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$wmi = new-object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $env:COMPUTERNAME
$wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp'].IsEnabled = "True"
$wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp'].Alter()
$svc = Get-Service -Name 'MSSQLSERVER'
$timeout = New-Object System.TimeSpan -ArgumentList 0, 0, 30
$svc.Stop(); 
$svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)
$svc.Start(); 
$svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)

$wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp'].IsEnabled

$workingDir = "C:\workingDirectory\"
$location = "North Europe"
$affinityGroupName = "ContosoAG"
$affinityGroupDescription = "Contoso SQL HADR Affinity Group"
$affinityGroupLabel = "IaaS SQL Affinity Group"
$virtualNetworkName = "ContosoNET" 
$localNetworkName = "ContosoNETLocal"
$networkConfigPath = $workingDir + "NetworkConfig.xml"
$storageAccountName = "sqlnuage"
$storageAccountLabel = "Contoso SQL HADR Storage Account"
$storageAccountContainer = "https://$storageAccountName.blob.core.windows.net/vhds/"
$sqlImageName = (Get-AzureVMImage | where {$_.ImageFamily -EQ "SQL Server 2014 on WS 2012 R2"} | sort PublishedDate -Descending)[0].ImageName
$serviceName = "sqlnuage"
$sqlServerName = "sqlnuage"
$vmAdminUser = "AzureAdmin" 
$vmAdminPassword = "BXkikovn14_____"
$subnetName = "Back"

Get-AzureVMImage | where {$_.ImageFamily -EQ "SQL Server 2014 on WS 2012 R2"

Get-AzureVMImage | select -unique ImageFamily | sort -Property ImageFamily

New-AzureAffinityGroup `
    -Name $affinityGroupName `
    -Location $location `
    -Description $affinityGroupDescription `
    -Label $affinityGroupLabel

<NetworkConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/ServiceHosting/2011/07/NetworkConfiguration">
  <VirtualNetworkConfiguration>
    <Dns />
    <VirtualNetworkSites>
      <VirtualNetworkSite name="ContosoNET" AffinityGroup="ContosoAG">
        <AddressSpace>
          <AddressPrefix>10.10.0.0/16</AddressPrefix>
        </AddressSpace>
        <Subnets>
          <Subnet name="Back">
            <AddressPrefix>10.10.1.0/24</AddressPrefix>
          </Subnet>
        </Subnets>
      </VirtualNetworkSite>
    </VirtualNetworkSites>
  </VirtualNetworkConfiguration>
</NetworkConfiguration>

Set-AzureVNetConfig `
    -ConfigurationPath $networkConfigPath

New-AzureStorageAccount `
    -StorageAccountName $storageAccountName `
    -Label $storageAccountLabel `
    -AffinityGroup $affinityGroupName

Set-AzureSubscription -SubscriptionName (Get-AzureSubscription -Default).SubscriptionName `
    -CurrentStorageAccountName $storageAccountName

New-AzureVMConfig `
    -Name $sqlServerName `
    -InstanceSize Large `
    -ImageName $sqlImageName `
    -MediaLocation "$storageAccountContainer$sqlServerName.vhd" `
    -DiskLabel "OS" | 
    Add-AzureProvisioningConfig `
        -Windows `
        -Password $vmAdminPassword `
        -AdminUserName $vmAdminUser `
        -DisableAutomaticUpdates ` |
        Set-AzureSubnet `
            -SubnetNames $subnetName |
            Add-AzureEndpoint `
                -Name "SQL" `
                -Protocol "tcp" `
                -PublicPort 1433 `
                -LocalPort 1433 | 
                Add-AzureEndpoint `
                    -Name "mirroring" `
                    -Protocol "tcp" `
                    -PublicPort 5022 `
                    -LocalPort 5022 | 
                    New-AzureVM `
                        -ServiceName $serviceName `
                        -AffinityGroup $affinityGroupName `
                        -VNetName $virtualNetworkName

$VMStatus = Get-AzureVM -ServiceName $serviceName -Name $sqlServerName

While ($VMStatus.InstanceStatus -ne "ReadyRole")
{
    write-host "Waiting...Current Status = " $VMStatus.Status
    Start-Sleep -Seconds 15
    $VMStatus = Get-AzureVM -ServiceName $serviceName -Name $sqlServerName
} 

Get-AzureRemoteDesktopFile `
    -ServiceName $serviceName `
    -Name $sqlServerName `
    -LocalPath "$workingDir$sqlServerName.rdp"

$vmAdminUser = "AzureAdmin" 
$vmAdminPassword = "BXkikovn14_____"

netsh advfirewall firewall add rule name='SQL Server (TCP-In)' program='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCP

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'USclsmdlymlanokgxw96';
GO

CREATE CERTIFICATE SQLOnPrem_cert WITH SUBJECT = 'SQLOnPrem certificate';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL) 
   FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQLOnPrem_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
GO

BACKUP CERTIFICATE SQLOnPrem_cert TO FILE = 'SQLOnPrem_cert.cer';
GO

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'RNaqkzndqdastyzial12';
GO

CREATE CERTIFICATE SQLInCloud_cert WITH SUBJECT = 'SQLInCloud certificate for database mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL) 
   FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQLInCloud_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
GO

BACKUP CERTIFICATE SQLInCloud_cert TO FILE = 'SQLInCloud_cert.cer';
GO

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA

CREATE LOGIN DBMirroringLogin WITH PASSWORD = 'SGyqzzsjrbvih83';
GO
CREATE USER DBMirroringLogin FOR LOGIN DBMirroringLogin;
GO
CREATE CERTIFICATE SQLInCloud_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQLInCloud_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DBMirroringLogin];
GO

CREATE LOGIN DBMirroringLogin WITH PASSWORD = 'QNcgppjwrziim85';
GO
CREATE USER DBMirroringLogin FOR LOGIN DBMirroringLogin;
GO
CREATE CERTIFICATE SQLOnPrem_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQLOnPrem_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DBMirroringLogin];
GO

ALTER DATABASE AdventureWorksLT set recovery  FULL
GO
BACKUP DATABASE AdventureWorksLT TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\AdventureWorksLT.bak';
GO
BACKUP LOG AdventureWorksLT TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\AdventureWorksLT.log';
GO

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup

RESTORE DATABASE AdventureWorksLT FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\AdventureWorksLT.bak' WITH NORECOVERY
, MOVE 'AdventureWorksLT2008_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Data.mdf'
, MOVE 'AdventureWorksLT2008_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Log.ldf'
GO

RESTORE LOG AdventureWorksLT FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\AdventureWorksLT.log' WITH NORECOVERY
, MOVE 'AdventureWorksLT2008_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Log.ldf'
, MOVE 'AdventureWorksLT2008_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Log.ldf'
GO

ALTER DATABASE AdventureWorksLT SET PARTNER = 'TCP://dans-mes-murs.compiouting.fr:5022';
GO

ALTER DATABASE AdventureWorksLT SET PARTNER = 'TCP://sqlnuage.cloudapp.net:5022';
GO

create database AdventureWorksLT_2 ON
(NAME = 'AdventureWorksLT2008_Data',
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Data_2.mdf'
 )
as snapshot of AdventureWorksLT
GO

use AdventureWorksLT_2
go
select FirstName, LastName from SalesLT.Customer where CustomerID = 1
go

use AdventureWorksLT
go
select FirstName, LastName from SalesLT.Customer where CustomerID = 1
go

update SalesLT.Customer set LastName = 'Smith' where CustomerID = 1
go 
select FirstName, LastName from SalesLT.Customer where CustomerID = 1
go

use master
go
drop database AdventureWorksLT_2
GO
create database AdventureWorksLT_2 ON
(NAME = 'AdventureWorksLT2008_Data',
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Data_2.mdf'
 )
as snapshot of AdventureWorksLT
GO
use AdventureWorksLT_2
go
select FirstName, LastName from SalesLT.Customer where CustomerID = 1
go