locked
Populating a lightswitch db on publication / deployment-- best practices? RRS feed

  • Question

  • I'm doing some proof-of-concept work on an HTML lightswitch app and use the post-deploy SQL script lightswitch gives us to get the database back to a good, known state whenever I do so.

    This script deletes from the tables and merges data from my script.  That works ok, but now we have testers beginning to add data to the demo system, and it's becoming tough to incorporate their changes into my scripts by hand.

    What I'd like to do is use SQL Server Management Studio's abilities to script out the database with all the schema changes and data, and then after I republish to use that same script to repopulate the database again, with the users' data now included.

    I realize that my schema will change and that on occasion I will have to update the scripts generated by SQL Server to accommodate new columns, etc.  But that should be easier than what I'm doing now.  

    Do you do something similar, or if not, how do you incorporate user-added data into your post-deploy scripts?  I'm fairly familiar with SQL Server and working with it, but am not a SQL MVP.

     


    Sunday, December 20, 2015 11:11 PM

Answers

  • I think it's important to separate schema changes from data changes when you deploy LightSwitch Applications. 

    Assuming you're using intrinsic db, which I believe is required if you're using deploy scripts in the SQL Database project of your LS solution, then I would suggest letting LS do the schema changes and use SQL post-deploy scripts for data only.

    LS doesn't deploy any data from dev\test into production, we use post-deployment scripts for that.  Now typically we don't want to overwrite user data in production, so it's unusual to truncate tables and insert or merge all the data.  More often we want to merge static data such as lookup tables and often a subset of the data in those tables.

    One way to do that is to use the Data Comparison tools in Visual Studio + SSDT like so:

    Compare and Sync Data

    This presents a nice wizard for connecting to Source (local dev db) and Target (production db -  including Azure), picking the tables to compare, showing a summary of identical records, different records, records only in source and records only in target.  Once you're happy with the results, you can create a script with a single click and use it as your Post-deployment script. 

    Again you've only compared and scripted the data.  I would not recommend scripting schema changes with LS intrinsic db - let LS handle that. 

    HTH,

    Josh


    • Edited by joshbooker Monday, December 21, 2015 3:29 PM links
    • Marked as answer by Angie Xu Thursday, January 7, 2016 1:55 AM
    Monday, December 21, 2015 3:24 PM
  • Is it not the case that when LS publishes a schema change, it truncates the tables involved? 

    LS deployment makes zero change to data.  It neither publishes data forward nor truncates data in target db.  The pub wiz compares schema and migrates target db schema only.

    edit:  to be exact, the LS intrinsic db deployment is a bit of a black box.  I'm not even sure the it does a compare.  It may simply assume the state of deployed schema and do a 'up' migration of design changes made.  If so, this is where you can run into problems if you alter schema of intrinsic db in SQL scripts directly.  If you make a schema change that throws LS assumptions then the next deployment may break. (?)

    This is so that future deployments can make schema changes while not affecting production data.  This also imposes a few limitations to prevent loss of data, as Ian mentions - ie cannot remove a field from a table that has data in it.

    HTH,

    Josh 



    • Edited by joshbooker Monday, December 21, 2015 4:21 PM edit
    • Marked as answer by Angie Xu Thursday, January 7, 2016 1:55 AM
    Monday, December 21, 2015 4:09 PM

All replies

  • Hi Jim,

    Why don't you just carry on using your test database for production?

    If you publish an update to your app, it will automatically make (most of) the schema changes for you automatically. I say mostly as certain schema changes (i.e. any that will delete data) will not be made automatically. Lightswitch integrates SSDT (SQL Server Data Tools) to perform the database diff and update when publishing.

    I guess what I am saying is (unless I misunderstand) is that this is one of the benefits that LS has is that it does the database migrations automatically ... with the caveats I described above.

    Hope this helps,

    Cheers


    Monday, December 21, 2015 6:23 AM
  • I think it's important to separate schema changes from data changes when you deploy LightSwitch Applications. 

    Assuming you're using intrinsic db, which I believe is required if you're using deploy scripts in the SQL Database project of your LS solution, then I would suggest letting LS do the schema changes and use SQL post-deploy scripts for data only.

    LS doesn't deploy any data from dev\test into production, we use post-deployment scripts for that.  Now typically we don't want to overwrite user data in production, so it's unusual to truncate tables and insert or merge all the data.  More often we want to merge static data such as lookup tables and often a subset of the data in those tables.

    One way to do that is to use the Data Comparison tools in Visual Studio + SSDT like so:

    Compare and Sync Data

    This presents a nice wizard for connecting to Source (local dev db) and Target (production db -  including Azure), picking the tables to compare, showing a summary of identical records, different records, records only in source and records only in target.  Once you're happy with the results, you can create a script with a single click and use it as your Post-deployment script. 

    Again you've only compared and scripted the data.  I would not recommend scripting schema changes with LS intrinsic db - let LS handle that. 

    HTH,

    Josh


    • Edited by joshbooker Monday, December 21, 2015 3:29 PM links
    • Marked as answer by Angie Xu Thursday, January 7, 2016 1:55 AM
    Monday, December 21, 2015 3:24 PM
  • Thanks Ian / Josh! 

    Yes, I'm using the intrinsic db for this app (that's ok, I assume, no scalability issues down the road?  This app _might_ have 500 simultaneous users as a max, and SQL Azure should handle that.)

    Didn't know about the Data Comparison tools, so thanks there.  And I should have been clear before-- I don't plan on scripting schema changes myself but rather (as you guys say) let LS handle that.

    Is it not the case that when LS publishes a schema change, it truncates the tables involved?  I've been using a SQL post deploy script almost from the start and repopulating the tables with it, so I may have mistakenly made that assumption.

    Monday, December 21, 2015 3:46 PM
  • Is it not the case that when LS publishes a schema change, it truncates the tables involved? 

    LS deployment makes zero change to data.  It neither publishes data forward nor truncates data in target db.  The pub wiz compares schema and migrates target db schema only.

    edit:  to be exact, the LS intrinsic db deployment is a bit of a black box.  I'm not even sure the it does a compare.  It may simply assume the state of deployed schema and do a 'up' migration of design changes made.  If so, this is where you can run into problems if you alter schema of intrinsic db in SQL scripts directly.  If you make a schema change that throws LS assumptions then the next deployment may break. (?)

    This is so that future deployments can make schema changes while not affecting production data.  This also imposes a few limitations to prevent loss of data, as Ian mentions - ie cannot remove a field from a table that has data in it.

    HTH,

    Josh 



    • Edited by joshbooker Monday, December 21, 2015 4:21 PM edit
    • Marked as answer by Angie Xu Thursday, January 7, 2016 1:55 AM
    Monday, December 21, 2015 4:09 PM
  • http://blogs.msdn.com/b/lightswitch/archive/2013/07/03/intrinsic-database-management-with-database-projects-chris-rummel.aspx

    Monday, December 21, 2015 6:24 PM