migrating database to sql azure...

Question

I want to migrate an on premisis database to SQL Azure. I've used the Migration wizard several times successfully, but then for some reason or other it doesn't work.

Here's one of my posts

http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/a44f7aa2-9c67-464d-b652-94c496971411

I got it working after this post but now it's not working again.

Hats off to the folks that wrote the Migration Wizard, but It's not quite consistant enough for me to rely on.

I have a SQL 2005 instance that doesn't have SSIS other wise I'd make a package to transfer data.

I found this:

http://msdn.microsoft.com/en-us/library/hh335292.aspx

Question: How do I create a .BACPAC file?

Question: What is the best way to move data from an on premisis SQL Server Instance to SQL Azure?

Question: When will SQL Azure Aspirin be available ;>

Thanks,

Robert

Answer 1

Hi Robert, if you haven't seen this article yet, take a look:

http://msdn.microsoft.com/en-us/library/jj650016.aspx

There are a few ways to perform the on-premise to cloud migration and I'm actually in the process of creating a blog entry to highlight the different options and which to use in different circumstances.  The simplest is probably using SSMS (perhaps 2008/R2 and above) to create a .bacpac.  You can right-click on the database name and "export" to DAC, which creates a .bacpac file containing schema and data.  You can then move that to cloud storage and import into Azure / SQL Database.   

Another option if you don't have the updated client tools is to download sqlpackage.exe / DacFramework and use the command line to perform the export to .bacpac.  The command line route is pretty easy as well.  Let me know if you'd like a sample for that and I'll round one up.

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

Question: What is the best way to move data from an on premisis SQL Server Instance to SQL Azure?

Try DBConvert software Just setup MS SQL as source and SQL Azure as destination. 

Answer 3

Samuel,

Thank you for responding. As I mentioned, I have a SQL 2005 instance. I didn't see any option to export to DAC. Only the Migration Wizard from server to server.

Also, the link you provided details SQL Azure Databases. Ex: "This topic introduces the concepts and the functionality available currently to back up and restore the data within Windows Azure SQL Database."

I need to export, rather migrate and entire Database (Schema and Data) to SQL Azure. (Which I have done before with the Migration wizard, but now does not work)

Robert

Answer 4

Looks cool! Nice work. I'm just a little leery about typing in my all database credentials into your app and then running my data through it ;>

Answer 5

Sorry I wasn't clearer on the steps.  The option through SSMS is to connect to your 2005 instance with a newer version of SSMS where the DAC functionality has been introduced and then run the export.  I just did a quick check on my machine with SQL Server 2012 SSMS installed, connecting to a 2005 instance, and running the export.  If you don't have a machine with newer client tools, you can install the standalone DacFramework.msi and run sqlpackage.exe (part of DacFramework) to perform the export to .bacpac from the command line.

DacFramework MSI download:
http://www.microsoft.com/en-us/download/details.aspx?id=35756

SqlPackage.exe info:
http://msdn.microsoft.com/en-us/library/hh550080(v=VS.103).aspx

I'm rusty with my SQL Server 2005 memory, but another option may be to run Generate Script Wizard (right-click on DB, "Generate Scripts").  In the advanced options, you can select "schema and data" as the scripting option.  You could then run these scripts in SQL Database to recreate your DB.  

One advantage of using .bacpacs is that there is a validation step in the export that occurs to ensure your DB will be fully supported in SQL Database.  This doesn't exist using the scripting methods, but the scripts allow you full control to make any modifications needed on your own.  

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 6

1: I'm in SSMS 2012 on my laptop.
2: I've connected to the SQL 2005 Server.
3: If I Right Click on the Database, I don't see "Export to DAC"
4: If I Right Click on the Database I do see "Tasks"
5: If I select "Tasks" I still don't see "Export to DAC", only "Export" which starts the Wizard.
6: I need to "Copy" the entire Database (Schema and Data) and move it to a new instance in SQL Azure.

Answer 7

Your steps are correct, but do you have SP4 applied to your SQL Server 2005 instance?  According to the documentation, you can extract/export a DAC from databases residing on instances of SQL Azure, or SQL Server 2005 Service Pack 4 (SP4) or later.  On the instance where I exported, I was running SP4.

http://msdn.microsoft.com/en-us/library/ee210526(v=SQL.105).aspx

Once you have SP4 installed, you can right-click -> Tasks -> Export to Data-Tier Application to start the export to .bacpac process.  Let me know if this isn't working for you and I'll check with a few folks when I get in the office later today.

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 8

I didn't have SP4. I installed it and it worked fine. Thank you!