question

AndrewHarris-7658 avatar image
0 Votes"
AndrewHarris-7658 asked AnuragSharma-MSFT commented

Best way to move tables/views from SQL Server 2016 to Azure SQL Database

I made an Azure SQL Database and I am trying to use an SSIS package in Visual Studio to move tables and views to it from my desktop SQL Server 2016 database. I get an error message about the "USE" statement. I don't know why my script would even have a "USE" statement since I am not switching between databases. I am just moving tables and views from a single database locally to a single database in Azure. How can I fix this? Am I just going about this the wrong way?

azure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AndrewHarris-7658, great to hear things worked for you. You can mark the answer as 'Accepted Answer' that helped you, so to help others having similar queries.

0 Votes 0 ·
AndrewHarris-7658 avatar image
0 Votes"
AndrewHarris-7658 answered AlbertoMorillo commented

Alberto,
I was able to successfully migrate a database using the DMA. I had been selecting "assessment" instead of "migration". Once I switched to migration I was able to use SQL Server authentication and everything worked great. Thank you for all of your help.
-Andrew

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Wonderful. I am really glad it worked for you!

0 Votes 0 ·
AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AndrewHarris-7658 commented

Andrew,

Allow me to recommend you the easiest way to move SQL Server tables/views to Azure SQL. Please try Azure Data Migration Assistant. It can perform an assessment and also an offline migration of the data with just a few clicks.


Take a look at this step-by-step tutorial.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Now i'm having trouble uploading my migration assessment to Azure because I think I have two microsoft accounts using the same email. Here is the error message:
Failed to fetch subscription list from Azure.
AADSTS16000: Either multiple user identities are available for the current request or selected account is not supported for the scenario.

How do I fix this problem?

0 Votes 0 ·

Create a SQL login on the Azure SQL database, and use that SQL login (SQL Server authentication) to connect to the Azure SQL from inside Data Migration Assistant

0 Votes 0 ·
AndrewHarris-7658 avatar image
0 Votes"
AndrewHarris-7658 answered AlbertoMorillo commented

It forces me to log in to an Azure account first. There is no opportunity to specify the SQL database.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Does it matter that I'm on a free account? Part of the error message says " or selected account is not supported for the scenario."

0 Votes 0 ·

Oh yes! If you applied for the $200 credit you have option for Azure SQL DTU S0 only. Please refer to this page to know what options are available for you and their limits.



0 Votes 0 ·
AndrewHarris-7658 avatar image
0 Votes"
AndrewHarris-7658 answered AlbertoMorillo commented

I upgraded to "Pay as you Go" and I still get the same error message.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have many objects/work created with this subscription? It seems you were testing Azure. Can you create a pay as you go account associated with a email account and try with the newly created subscription?

0 Votes 0 ·
AndrewHarris-7658 avatar image
0 Votes"
AndrewHarris-7658 answered

I upgraded to "Pay as you Go" and still get the same error message:
Failed to fetch subscription list from Azure.
AADSTS16000: Either multiple user identities are available for the current request or selected account is not supported for the scenario.

Are you sure I can't use SSIS to do this? I'm not trying to do a one-time migration. I need a way to regularly copy one or more tables/views from my local SQL Server 2016 to Azure SQL Database. This was very easy to do with SSIS when my cloud database was a SQL Server database. Now it seems to be very difficult. Am I missing something?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo edited

DMA takes care of many things in the background. It disables foreign keys and non-clustered indexes ahead of migration and re-enable them after data migration is done. It reads the data in parallel from the tables and perform BULK INSERTs into the target tables. It also takes care of identity columns and this can be a real problem, as some migration methods can left the seed value set to 1. This tool takes care of many challenges you many not see at this time.

In addition, read here all the capabilities this tool has that probably you wont' have with SSIS or will require hours of your time to develop them in SSIS.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.