Skip to content

Latest commit

 

History

History
107 lines (74 loc) · 4.6 KB

azuresqlmi-login-migration-using-cli.md

File metadata and controls

107 lines (74 loc) · 4.6 KB

Login migration for Azure SQL Managed Instance using CLI

Perform a login migration of your SQL Server databases running on-premises, SQL Server on Azure Virtual Machines, or any virtual machine running in the cloud (private, public) to Azure SQL Managed Instance using the Azure SQL Migration extension.

Migration using Azure storage

Prerequisites

  • SQL Server with Windows authentication or SQL authentication access
  • .Net Core 3.1 (Already installed)
  • Azure CLI (Already installed)
  • Az datamigration extension
  • Azure storage account (Already provisioned)
  • Azure Data Studio (Already installed)
  • Azure SQL Migration extension for Azure Data Studio

Currently, only Azure SQL Managed Instance and SQL Server on Azure Virtual Machines targets are supported.

Completing the database migrations of your on-premises databases to Azure SQL before starting the login migration is recommended. It will ensure that the database-level users have already been migrated to the target; therefore the login migration process will perform the user-login mappings synchronization.

Getting Started

Caution

  • Connect to the Jump Box VM
  • VM name: jb-migration
  • Use the credentials provided on the deploy page.

Open a Terminal. It is already installed in the VM and by default it uses PowerShell.

  1. Install az datamigration extension if it isn't installed.

    az extension add --name datamigration

  2. Run the following to log in from your client using your default web browser

    az login

    If you have more than one subscription, you can select a particular subscription.

    az account set --subscription <subscription-id>

    The Azure SQL migration extension for Azure Data Studio enables you to assess, get Azure recommendations and migrate your SQL Server databases to Azure.

    In addition, the Azure CLI command az datamigration can be used to manage data migration at scale.

  3. Verify SQL Logins to be migrated

    Open the Azure Data Studio, connect to the Azure SQL VM (10.1.0.4) using the "sqladmin" user and password and run the following T-SQL statement to see all SQL Logins.

    SELECT 
    sp.name AS server_login_name, 
    dp.name AS database_user_name, 
    sp.type_desc, 
    sp.is_disabled
FROM sys.server_principals AS sp
INNER JOIN WideWorldImportersDW.sys.database_principals AS dp ON SP.sid = DP.sid

Start login migration

Caution

  • Connect to the Jump Box VM
  • VM name: jb-migration
  • Use the credentials provided on the deploy page.

At this point you will need to get the Azure SQL MI name. Get server connection information

sqlmi-connectionstring

  1. Use the az datamigration login-migration command to create and start a database migration.

        az datamigration login-migration  `
    --src-sql-connection-str  "data source=10.1.0.4,1433;user id=sqladmin;password=My`$upp3r`$ecret;initial catalog=master;TrustServerCertificate=True" `
    --tgt-sql-connection-str  "data source=<managed instance name>.database.windows.net;user id=sqladmin;password=My`$upp3r`$ecret;initial catalog=master;TrustServerCertificate=True" `
    --list-of-login "sqlpoc" "sqlpocapp" "sqlpocreport" `
    --output-folder "C:\temp\output"

    [!WARNING]

    Change the managed instance name

    [!TIP]

    Windows accounts are out of scope but if you want to learn how to migrate them, check out this [prerequisites](https://learn.microsoft.com/en-us/azure/dms/tutorial-login-migration-ads#prerequisites

    sqlmi-login-migration

  2. Login migration at scale using config file

    Learn more about Migrate SQL Server logins

Page Navigator