none
Azure Update publish fails RRS feed

  • Question

  • I have had a lightswitch ver1.0 app running for awhile on Azure without issue.  Have published a couple of mostly bug fix aupdates without issue.

    Today attempted update which include a new table.

    First attempt ran until tcp login eror That I have seen elsewhere.  restarted app and tried again this time fails with the following

    An exception occurred when deploying the database for the application.
    The source database schema provider Sql100DatabaseSchemaProvider could not be translated to provider SqlAzureDatabaseSchemaProvider. Deployment cannot continue. C:\Program Files\MSBuild\Microsoft\VisualStudio\LightSwitch\v1.0\Microsoft.LightSwitch.targets 1114 10 BizManager

    Have restarted app. from azure management

    and also tested login to database.

    It appears the Sreens etc have updated for the app , none of the new tables appear in database.

    Suggestions

    Thanks,

    Bill

    Thursday, March 22, 2012 7:51 PM

Answers

  • Bill:

    I can give you a couple of ideas from my experience.  I have published my app to Azure over 50 times and have had very few problems.  In my experience, when you add new fields or tables to your app, you should have no problems.  If you delete tables or fields, or change the length of a field and this would cause data loss in your Azure SQL database, the publish will not update the schema. 

    This may not be required, but if I change a field length, delete a field, or delete a table, I first do it through the Azure db manager.  Then I make the same change in my LightSwitch app.

    One tool that has helped me a lot is the Azure SQL Migration Wizard (http://sqlazuremw.codeplex.com/).

    This is a great tool to copy data to or from your SQL Azure database to your LightSwitch database.  You can also use it to create a full copy of your azure database either to another Azure database or to a database on your local machine.  One of the most important things that the migration wizard does is that it does Identity inserts correctly, which is critical with a LightSwitch database because all the "Id" fields are identity fields.  I had tried to get the SQL Server Import/Export utility to transfer data, but it will not do the identity inserts.

    So, in your case, after you had the problem, you could have let the LightSwitch publish process create a new database.  Then you could use the SQL Azure migration tool to copy all the data from the old database to the new one.

    One more tip on using the migration wizard to copy data:  If you are copying data to an existing database, you need to select the "Data Only" option in the "Advanced" settings.

    Hope this helps.

    Mark

    • Marked as answer by wtwashburn Sunday, March 25, 2012 3:47 PM
    Friday, March 23, 2012 10:12 PM

All replies

  • After more research It appears this is a issue caused by adding tables to the App , Found recommendation to create new sql database and republish to that instance. (losing the data already in the app which since this was a demo is not the end of the world. What concerns me is if this is typical, how to deal with it in a production mode/ Suggestions on how best to recover. Bill
    • Marked as answer by wtwashburn Sunday, March 25, 2012 3:47 PM
    • Unmarked as answer by wtwashburn Sunday, March 25, 2012 3:47 PM
    Thursday, March 22, 2012 9:51 PM
  • Bill:

    I can give you a couple of ideas from my experience.  I have published my app to Azure over 50 times and have had very few problems.  In my experience, when you add new fields or tables to your app, you should have no problems.  If you delete tables or fields, or change the length of a field and this would cause data loss in your Azure SQL database, the publish will not update the schema. 

    This may not be required, but if I change a field length, delete a field, or delete a table, I first do it through the Azure db manager.  Then I make the same change in my LightSwitch app.

    One tool that has helped me a lot is the Azure SQL Migration Wizard (http://sqlazuremw.codeplex.com/).

    This is a great tool to copy data to or from your SQL Azure database to your LightSwitch database.  You can also use it to create a full copy of your azure database either to another Azure database or to a database on your local machine.  One of the most important things that the migration wizard does is that it does Identity inserts correctly, which is critical with a LightSwitch database because all the "Id" fields are identity fields.  I had tried to get the SQL Server Import/Export utility to transfer data, but it will not do the identity inserts.

    So, in your case, after you had the problem, you could have let the LightSwitch publish process create a new database.  Then you could use the SQL Azure migration tool to copy all the data from the old database to the new one.

    One more tip on using the migration wizard to copy data:  If you are copying data to an existing database, you need to select the "Data Only" option in the "Advanced" settings.

    Hope this helps.

    Mark

    • Marked as answer by wtwashburn Sunday, March 25, 2012 3:47 PM
    Friday, March 23, 2012 10:12 PM
  • Mark,

    Thank You for the advice.  I have already done the first step. I have created a new datbase  instance and the app and db published no problem.

    I have seen posts regarfing the migration wizard but have not used it. Guess now is the time.  I was focused on added tables and did not think about deleted tables, fields or columns.  I was hoping for magic. I will take your kind and helpful advice and try migrating data from old db to new.

    Is there a particular sequnce to the procdure you use ?

    stop app, modify db , then publish update ? or somthing like that?

    Thanks,

    Bill

    I will give it a shot and then mark as answered.


    W. T. Washburn

    Friday, March 23, 2012 10:34 PM
  • Bill:

    Just make the change to the Azure db and your LightSwitch db, then publish.   When you publish, LightSwitch compares the schemas, and if they are the same, it does not have to do anything to the database.

    One more note on the migration wizard.  The order of the fields in a table definition needs to be the same, or you will get an error when the wizard runs BCP to copy the data.  I had a couple of tables where the fields were in a different order.  If you have that issue, I don't think that you can fix it in the LightSwitch designer.  I just used the Server Explorer in Visual Studio to change the design of the table to match the my Azure db.

    Backup your LightSwitch project often!

    Mark

    Saturday, March 24, 2012 2:12 PM
  • Mark,

    Thanks to your helpful suggestion I have recovered Data From other database instance.

    Still uncertain as to cuase of publish failure but I am sure it was one of the things you suspect.  I will take deliberate steps to avoid issues or atleast be able to recover quickly in the futue.

    Thanks again

    Bill Washburn


    W. T. Washburn

    Sunday, March 25, 2012 3:51 PM
  • You're welcome.  I'm glad it helped.

    Mark

    Monday, March 26, 2012 7:31 PM