As a DBA we always get the requests to upgrade or migrate the databases, either from older version to newer version of SQL Server and now when organizations has started considering Azure, the migration could also be from On-Prem SQL Server to Azure SQL Database.
Migration/up-gradation has always been much easier than finding out what all can break by moving to newer versions. There are always some datatypes or functions or system tables which Microsoft has decided to remove from the newer version of SQL Server. And with Azure in the picture there are many a small things we need to check, as PaaS databases are newer version databases but have some limitations tagged along with them.
Previously I used to start a server side trace checking for all the deprecated features being used, along with SQL Server upgrade adviser. Now Microsoft has decided to bring a whole new tool to help in identifying the compatibility issues and feature parity and its called Data Migration Assistant (DMA), download from here.
It can be used for assessing the database and its features for compatibility with the destined version of SQL Server/Azure Database and also to migrate the databases (generally smaller size databases).
In this blog we are going to see how to use DMA to find the compatibility issues which are required to fixed before making the move.
In our example we will see what all compatibility issues DMA identifies when migrating AdventureWorks2012 database to Azure SQL Database.
1. Click on + and select Assessment. Give your project a meaningful name, choose the Source and Target type.
2. DMA can check for both the database compatibility issues and also the feature parity, we will be choosing both in our case as we want to know the features that aren’t available or not supported in Azure SQL Database.
3. In the next screen we need to provide the SQL Server name and authentication mode, we can access multiple databases from multiple sources at one time helping us out in documenting everything in one go.
If you are using Encrypted Connection in your environment than check it other wise keep it unchecked.
4. Select all the databases to assess, AdventureWorks2012 in our case.
DMA also has the option to provide the load to be used along with the database. When provide the load it basically have all the queries which your application is using, and so you will be able to find out the different queries that the application team also needs to change. Use the below Extended Event session to capture the load, this is taken from MS docs.
DROP EVENT SESSION [DatalayerSession] ON SERVER go CREATE EVENT SESSION [DatalayerSession] ON SERVER ADD EVENT sqlserver.sql_statement_completed( ACTION (sqlserver.sql_text,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id)) ADD TARGET package0.asynchronous_file_target(SET filename=N'C:\temp\Demos\DataLayerAppassess\DatalayerSession.xel') WITH (MAX_MEMORY=2048 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=3 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) go ---Start the session ALTER EVENT SESSION [DatalayerSession] ON SERVER STATE = START; ---Wait for few minutes ---Query events SELECT object_name, CAST(event_data as xml) as event_data, file_name, file_offset FROM sys.fn_xe_file_target_read_file('C:\temp\Demos\DataLayerAppassess\DatalayerSession*xel', 'C:\\temp\\Demos\\DataLayerAppassess\\DatalayerSession*xem', null, null) ---Stop the session after capturing the peak load. ALTER EVENT SESSION [DatalayerSession] ON SERVER STATE = STOP; go
5. When you have made sure that you have chosen the right source destination and the databases you can Begin Assessment. Assessment can be time consuming based upon the database size and workload used. In my case it took under 30 seconds and provided the result as below.
For compatibility issues, we dont have much but DMA also provides suggestion based on what it has found.
The assessment is saved in DMA and can also be exported or saved for further analysis and sharing with other team members.