Have you ever gone through the process of importing your newly created dacpac into Azure SQL Database and after many minutes or hours of waiting, you get a failure of type
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.Data.Tools.Schema.Sql)
Well, I did, and it was a very large pain to debug. Here is where I ended up.
To start with, the dacpac was originally created on a SQL Server 2012 RTM (No SP’s or CU’s applied). The dacpac itself seemed fine, because I was able to successfully load it to my own local SQL Server 2014 instance without any problem, but for whatever reason the same would not work when pointing to our Azure SQLDB instance. It is notable that the process was done by loading the dacpac from local storage, not from Azure Storage.
My first thought was to just see if I created thet dacpac from the database on my local instance (that was created from the original dacpac anyway) how things would work when coming from a dacpac created on SQL 2014. So I did, and it worked. That seemed very nice, but I have absolutely no love for the idea of having to
- Create dacpac on SQL 2012 instance
- Install dacpac on SQL 2014 instance
- Create new dacpac with the same data from SQL 2014 instance
- Load dacpac to Azure SQL DB
This just did not seem like fun to me, but given the scenario it could be a reasonable work around (we do not have to do this often). However, when doing this same approach with a different and larger database (around 8 GB which is small to me in the real world but seemingly large for Azure SQL DB) the same process failed anyway. So this was not fool proof.
Since the old standard dacpac did not work in all cases, I figured I would try one of the many other solutions that came up when searching for issues of this nature. One of the first that came up was the Azure Migration Wizard from Codeplex, so I thought I would give it a shot. The process itself is pretty self-explanatory once you had downloaded the application and I selected the following for my parameters:
- On the opening screen I selected “Analyze/Migrate” and chose the “Database” button. For the Target Server I selected “Azure SQL Database”
- Clicking Next brought me to a standard server connection item where I entered the name of my local server and database that I had previously loaded that dacpac into.
- On the choose objects screen, I chose “Script All Database Objects”
- I had it then generate the script and when it asked for the credentials to my Azure SQL Database I added them and connected.
- From here the application did its thing. Several hours later, everything completed without problem (no out of memory exception with this application, at least not in my experience so far).
This seemed to work great. The application appears to do similar things to a bacpac, in as much as it appears to call bcp at some level underlying, appears to process the commands in a similar fashion and appears to try and do basically the same kind of stuff. But it works and so far it has worked for me in each of my scenarios.
So if you come across this issue, this would be my recommendation in terms of trying something new. It is easy, free and seems to work.