GERBELOTBARILLON.COM

Parce qu'il faut toujours un commencement...

Azure SQL Managed Instance

Créer une SQL Managed Instance avec Powershell


import-module AZ
Login-AzAccount
Get-AZSubscription
Set-AzContext "EnterYourSubscriptionIDHere"


# Create Azure SQL Managed Instance
# Enter the values for the variables for: 
# SubscriptionID, ResourceGroupName, location, vNetName, vNetAddressPrefix, miSubnetName, miSubnetAddressPrefix, instancename, miAdminSqlLogin, miAdminSqlPassword, edition, vCores, maxStorge, computeGeneration and license
# below based on your environment and requirements
$NSnetworkModels = "Microsoft.Azure.Commands.Network.Models"
$NScollections = "System.Collections.Generic"
# The SubscriptionId in which to create these objects
$SubscriptionId = ""
# Set the resource group name and location for your managed instance
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = ""
# Set the networking values for your managed instance
$vNetName = "myVnet-$(Get-Random)"
$vNetAddressPrefix = "20.0.0.0/16"
$miSubnetName = "myMISubnet-$(Get-Random)"
$miSubnetAddressPrefix = "20.0.0.0/24"
#Set the managed instance name for the new managed instance
$instanceName = "myMIName-$(Get-Random)"
# Set the admin login and password for your managed instance
$miAdminSqlLogin = "SqlAdmin"
$miAdminSqlPassword = "ChangeYourAdminPassword1"
# Set the managed instance service tier, compute level, and license mode
$edition = "General Purpose"
$vCores = 4
$maxStorage = 128
$computeGeneration = "Gen5"
$license = "LicenseIncluded" #"BasePrice" or LicenseIncluded if you have don't have SQL Server license that can be used for AHB discount

# Create a resource group
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location -Tag @{Owner="SQLDB-Samples"}

# Configure virtual network, subnets, network security group, and routing table
$virtualNetwork = New-AzVirtualNetwork `
                      -ResourceGroupName $resourceGroupName `
                      -Location $location `
                      -Name $vNetName `
                      -AddressPrefix $vNetAddressPrefix

Add-AzVirtualNetworkSubnetConfig `
    -Name $miSubnetName `
    -VirtualNetwork $virtualNetwork `
    -AddressPrefix $miSubnetAddressPrefix |
Set-AzVirtualNetwork
                  
$scriptUrlBase = 'https://raw.githubusercontent.com/Microsoft/sql-server-samples/master/samples/manage/azure-sql-db-managed-instance/delegate-subnet'

$parameters = @{
    subscriptionId = $SubscriptionId
    resourceGroupName = $resourceGroupName
    virtualNetworkName = $vNetName
    subnetName = $miSubnetName
    }

Invoke-Command -ScriptBlock ([Scriptblock]::Create((iwr ($scriptUrlBase+'/delegateSubnet.ps1?t='+ [DateTime]::Now.Ticks)).Content)) -ArgumentList $parameters

$virtualNetwork = Get-AzVirtualNetwork -Name $vNetName -ResourceGroupName $resourceGroupName
$miSubnet = Get-AzVirtualNetworkSubnetConfig -Name $miSubnetName -VirtualNetwork $virtualNetwork
$miSubnetConfigId = $miSubnet.Id

##
# Create credentials
$secpassword = ConvertTo-SecureString $miAdminSqlPassword -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($miAdminSqlLogin, $secpassword)

# Create managed instance
New-AzSqlInstance -Name $instanceName `
                      -ResourceGroupName $resourceGroupName -Location $location -SubnetId $miSubnetConfigId `
                      -AdministratorCredential $credential `
                      -StorageSizeInGB $maxStorage -VCore $vCores -Edition $edition `
                      -ComputeGeneration $computeGeneration -LicenseType $license

Restaurer un backup SQL MI sur instance SQL 2022

Il y a 2 étapes impliquées dans la restauration d'une base provenant de SQL Managed Instance sur un serveur SQL On-Premises :

  1. Réalisation d'un backup depuis SQL MI vers un Blob Storage
    2. Transfert du backup depuis le Blob Storage vers SQL Serveur On-Premises

Depuis Azure, se rendre dans storage account > Security + Networking > Shared Access Signature. Si ce n'est pas déjà fait, cocher les cases Service, Container et Object dans la rubrique Allowed Resource Types. Définir ensuite une durée de validité pour le token SAS que nous allons vouloir utiliser pour l'accès au Blob Storage puis presser le bouton Generate SAS and connection string.

Copier le token SAS pour servir de secret key dans le script d'export des backups de SQL MI. Si le token commence par '?', l'enlever du script. La créatin d'un fichier de credential dans l'espace du storage blob permet de préciser automatiquement à SQL MI comment la connexion va s'établir. 


USE master
GO
DROP CREDENTIAL [https://<storage_name>.blob.core.windows.net/dbbackups]
GO
CREATE CREDENTIAL [https://<storage_name>.blob.core.windows.net/dbbackups]
   WITH IDENTITY='SHARED ACCESS SIGNATURE',
   SECRET='<copier le token SAS ici>'
GO

Le bases de données sur SQL MI sont chiffrées par défaut. Pour vérifier le chiffrement des bases de données, exécuter le script ci-après 

SELECT
   CASE e.encryption_state
      WHEN 0 THEN 'No database encryption key present'
      WHEN 1 THEN 'Unencrypted'
      WHEN 2 THEN 'encryption in progress'
      WHEN 3 THEN 'Encrypted'
      WHEN 4 THEN 'Key change in progress'
      WHEN 5 THEN 'Decryption in progress'
   END AS encryption_state_desc, e.percent_complete, DB_NAME(e.database_id) AS DatabaseName, e.encryption_state
   FROM sys.dm_database_encryption_keys AS e

Pour décypter les bases : 

USE <database_name>
GO
ALTER DATABASE <database_name> SET Encryption Off
GO
DROP database encryption key
GO

Maintenant que la base est totalement déchiffrée, nous pouvons procéder à son backup 

BACKUP DATBASE <database_name>
   TO URL = 'https://<blob_storage_name>.blob.core.windows.net/dbbackups/backups/<database_name>.bak'
   WITH COPY_ONLY, STAT=1, COMPRESSION
GO

Pour restaurer la base de données, deupis le Studio SQL en mode On-Premises, on va commencer par créer les credentials requis pour accéder au Blob storage 


USE master
GO
CREATE CREDENTIAL [https://<storage_name>.blob.core.windows.net/dbbackups]
   WITH IDENTITY='SHARED ACCESS SIGNATURE',
   SECRET='<copier le token SAS ici>'
GO

Puis on lance la restauration avec 


USE master
GO
RESTORE DATABASE <database_name> FROM URL'https://<blob_storage_name>.blob.core.windows.net/dbbackups/backups/<database_name>.bak'
   FILE=1,
   MOVE N'data_0' TO N'<folder_name_for_mdf>\<database_name>.mdf',
   MOVE N'log' TO N'<folder_name_for_log>\<database_name>.ldf',
   MOVE N'WTP' TO N'<folder_name_for_xtp>\<database_name>.xtp',
   NOUNLOAD, REPLACE, STATS=5
GO