How do I add my Azure Sql Server Admin User to 'Sysadmin' Server Role?

Question

I'm attempting to migrate a Sql Db from a local server to Azure using the new Data Migration Assistant v2.0 (announced here).

It seems to have connected to my source (a local SQL 2014 server, Windows Auth) and destination (Azure V12 SQL, server admin username and password) servers correctly, but gives the following error when attempting to get to the 'Add Database' step.

Provided credentials for target server 'fooserver.database.windows.net' have insufficient server-level permissions to perform migration. The credentials used to connect to the target server must be a member of the sysadmin server role.

How do I add my Azure Sql Server admin user to the 'sysadmin' server role? I couldn't see anything related to this in the Azure Portal.

Answer 1

I do not believe that Data Migration Assistant is the right tool for the job. If you original database is Microsoft SQL one you could either use The Microsoft SQL Server 2016 Upgrade Advisor Preview or The Export Data Tier application wizard in SQL Server Management Studio. I have used both approaches many times and they work very well.

The following article is probably the best one our there that lists and explains all available options - SQL Server database migration to SQL Database in the cloud.

Let me know if you have any questions.

Update 1 - @Ted has contacted Microsoft and they have said that Data Migration Assistant does not support migrating to Azure at the moment. This scenario will be supported in a future version.

Answer 2

Nov 2017 Update

The MS Data Migration Assistant now absolutely does support migrating on premises SQL databases to Azure SQL, as well assessing your DB for compatibility, and providing / applying suggestions for fixes if required.