Error deploying database to SQL Azure

Question

I'm using forms authentication to handle users and attempting to deploy my database to SQL Azure, but getting this error message:

The only table of the four listed that I utilize is aspnet_Membership, and the only other table I use is aspnet_Users from implementing forms authentication. What is TextInRowSize and why does SQL Azure care about it? Do I have any option to modify aspnet_Membership to make it compatible?

If it would be easier to remove the current system altogether and replace it with my own, I'm fine with that too.

Answer 1

This was solved by generating a SQL Azure script for the DB, and running it on Azure. Here's how I solved it:

First, open SQL Server, right click the database you want to transfer and click "Tasks > Generate Scripts..."

Next, click "Advanced" on the scripting options panel and find the row "Script for the database engine type." Select "Windows Azure SQL Database" and click OK (Note: if you have data that you want to transfer as well, choose "Schema and Data" from the "Types of data to script" option).

Proceed thru the rest of the script generation dialog, remembering where you saved the script file. Connect to your database server using SQL Server or windowsazure.com. Generate a new query for your new database, enter the script that was generated by SQL Server and execute.

Answer 2

That looks like an error in the data-tier application framework. I can suggest a workaround to get your database to Azure: Use SqlPackage.exe (https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx) from the command line to extract a dacpac file with all table data. Then use SqlPackage.exe to deploy that dacpac file to your database in Azure. The extract command would look something like:

C:\>"c:\Program Files\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /a:extract /scs:"Data Source=yourSqlServer;Integrated Security=true;Initial Catalog=yourDatabase" /tf:C:\temp\mydatabase.dacpac /p:ExtractAllTableData=true

And the import command would look something like:

C:\>"c:\Program Files\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /a:publish /tcs:"Data Source=yourAzureSQLServer.database.windows.net;User Id=yourUserId;Password=yourPassword;Initial Catalog=yourDatabase" /sf:C:\temp\mydatabase.dacpac

Answer 3

Seems like TextInRowSize stores large data for older SQL Server types such as text and ntext. You would need to change it's type to nvarchar(max).

Here's a link to a more detailed explanation.

http://www.dnnsoftware.com/wiki/unsupported-property-textinrowsize-set-and-is-not-supported-when-used-as-part-of-a-data-package

Answer 4

So quick suggestions since I see you are using V12 which should support those properties now.

First make sure you are using SSMS 2014 SP1 at least, this has a number of fixes for using V12. Secondly make sure you install the May 2015 update to DacFX (which is the program that creates bacpac files) you can install it here: http://www.microsoft.com/en-us/download/details.aspx?id=46898

This should get you to the best possible chance of your import/export working.