Can Azure DMS use to move logins from SQL Server to Azure SQL Database Managed Instance?
What is Azure Database Migration Service (Azure DMS)?
Azure Database Migration Service is a fully managed service designed to enable seamless migrations from multiple database sources to Azure Data platforms with minimal downtime (online migrations). Azure Database Migration Service integrates some of the functionality of our existing tools and services. The service is currently in General Availability, with ongoing development efforts focused on:
- Reliability and performance.
- Iterative addition of source-target pairs.
- Continued investment in friction-free migrations.
It provides customers with a comprehensive, highly available solution. The service uses the Data Migration Assistant to generate assessment reports that provide recommendations to guide you through the changes required prior to performing a migration. It's up to you to perform any remediation required. When you're ready to begin the migration process, Azure Database Migration Service performs all of the required steps. You can fire and forget your migration projects with peace of mind, knowing that the process takes advantage of best practices as determined by Microsoft.
Azure DMS offline and an online migration experience
You can use Azure Database Migration Service to perform offline and online migrations. With an offline migration, application downtime starts when the migration starts. With an online migration, downtime is limited to the time to cut over at the end of the migration. We suggest that you test an offline migration to determine whether the downtime is acceptable; if not, do an online migration.
Important: Using Azure Database Migration Service to perform an online migration requires creating an instance based on the Premium pricing tier
Steps required to use Azure DMS to perform a simple database migration:
1) Create a target database(s).
2) Assess your source database(s).
a) For homogenous migrations, assess your existing database(s) by using DMA.
b) For heterogeneous migrations (from competing sources), assess your existing database(s) with SSMA. You also use SSMA to convert database objects and migrate the schema to your target platform.
3) Create an instance of the Azure Database Migration Service.
4) Create a migration project specifying the source database(s), target database(s), and the tables to migrate.
5) Start the full load.
6) Pick the subsequent validation.
7) Perform a manual switchover of your production environment to the new cloud-based database.
How to move Logins and Groups to Azure SQL Database Managed Instance?
As we all know, by using Azure DMS we can move the Shema and Data from SQL Server to Azure SQL Database Managed Instance very efficiently. But many customers facing challenges when they want to move the logins and groups from SQL Server to Azure SQL Database Managed Instance.
Let's assume that, by using online migration experience you have already moved your selected database/s schema and data. Now you have to move your logins from the source SQL Server to the Azure SQL Database Managed Instance. You can do this in 2 ways.
Option 1: Move Logins using Azure DMS
Now you can move logins using Azure DMS. You can get this experience inside the Azure DMS Service. This is a hidden feature that you can leverage by using offline migration experience. Please follow the below steps to move the logins to Azure SQL Database Managed Instance using Azure DMS.
a) Select the Source SQL Server from where we need to move the logins:
b) Select the Target Azure SQL Database Managed Instance where we want to move the logins
c) In the "Select Databases" step, make sure that, you are not selecting any databases. Unchecked select databases checkbox. (The assumption here is, you have already moved the schema and data using online migration experience and now you have to move only logins)
d) Select logins that you want to move to the Azure SQL Database Managed Instance from the list.
Note: Please note that, Windows users and groups are disabled in the above screenshot. Request you to follow the below steps to move the Windows users and groups to SQL Database Managed Instance
Migration of Windows users and groups to Azure SQL Database Managed Instance
If there is a need to migrate/move windows users and groups to Azure SQL Database Managed Instance that is also possible by using Azure DMS. To to do this you have to enable the options from the Configuration Blade of Azure DMS Service.
Important: Select whether to enable or disable Windows user/group logins migration from the source SQL Server to the target Azure SQL Database Managed Instance. If the service is online, it will need to be restarted for the updated setting to take effect.
Option 2: PowerShell script to move SQL Server Logins, Users and Roles in Azure
A PowerShell script creates a T-SQL command script to re-create logins and select database uses from an on-premise SQL Server to an Azure SQL PaaS Service. The tool allows the automatic mapping of Windows AD accounts to Azure AD accounts or it can do UPN lookups for each login against the on-premise Windows Active Directory. The tool optionally moves SQL Server native logins as well. Custom servers and database roles are scripted, as well as role membership and database role and user permissions. Contained databases are yet not supported and only a subset of possible SQL Server permissions are scripted; i.e. permissions grant with the grant are not supported (Complex permission trees). More details are available here and the script has comments for ease of understanding.
Please use the below link to get detailed information about the usage of the MoveLogin Powershell script.
I hope the above 2 methods will help you to move the logins and groups from SQL Server to Azure SQL Database Managed Instance.
