Azure – Migration Main steps

Posted on by

/*

Please perform the following tasks:

Asessment

  1. Restore database(s) to the staging server from backup file
  2. Delete the backup file(s) from the staging server
  3. Perform compatibility assessment with the DMA Tool (Target=”Azure SQL Database Managed Instance“)
  4. Store output of assessment (csv) in the respective folder(s) for each source database server
  5. Export list of users from the database(s).

Restore to Azure

  1. Create encrypted backup(s) of database(s) on staging server with SQL Server certificate
  2. Upload encrypted backup(s) to Azure Storage Account
  3. Restore database(s) to target Azure SQL Managed Instance successfully
  4. Drop database(s) from staging server
  5. Delete database backup file from Storage Account
  6. Confirm everything is ok

*/

— To get the filenames for the restore below
RESTORE FILELISTONLY from disk=’D:\Dumps\DB1.bak’
go
RESTORE HEADERONLY from disk=’D:\Dumps\DB1.bak’
go

restore database [DB1] from disk=’D:\Dumps\DB1.bak’
with
move ‘DB1’ to ‘d:\MSSQL\DATA\DB1.mdf’,
move ‘DB1_Log’ to ‘e:\MSSQL\DATA\DB1.ldf’,
stats=1
go

— Encrypt the database
USE [DB1]
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO
ALTER DATABASE [DB1] SET ENCRYPTION ON;
GO

— Now get the list of users and roles in the database
— select name, islogin, issqlrole, isapprole, isntname, isntgroup, isntuser from sysusers
— Also run the DMA assessment and save the csv output file

–Verify encryption
USE master
go
SELECT
database_name = d.name,
dek.encryptor_type,
cert_name = c.name,
dek.percent_complete,
dek.encryption_state — 1 means no encryption, 2 means it is in the process of encrypting and 3 means fully encrypted
from sys.dm_database_encryption_keys dek left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id
go

–Once the database is fully encrypted Backup the database ready for upload
backup database [DB1] to disk=’D:\Dumps\DB1_mig_enc_full.bak’
with compression,
stats=1

— Upload the backup file using Azure Storage Explorer – [https://Storage1.blob.core.windows.net/migration-backups]

–In the Azure Managed instance Restore the database from Blob storage
restore database [DB1] from url= ‘https://Storage1.blob.core.windows.net/migration-backups/DB1_mig_enc_full.bak’
go

— Final step is to restore any users and jobs etc.