none
External database versus Intrinsic database RRS feed

  • General discussion

  • When developing a new LightSwitch application (ie: no existing database), what are the advantages and disadvantages of using an External database developed using Microsoft EDM versus a  Internal database?

    Would this be accurate:?

    Intrinsic Database:

    1. Great for applications with simple a database (# tables <50, simple relationships)
    2. Number of users < 20
    3. Duplicate work for RIA

    External Database:

    1. More complex databases (# tables > 50, complex relationships, many to many relationships)
    2. Greater flexibility in data types
    3. RIA is more of an enhancement than duplication

    Please let me know your thoughts.


    Thank you, Bill



    • Changed type jessiedog Thursday, June 13, 2013 6:25 PM Should have posted as discussion instead of question
    • Edited by jessiedog Monday, June 17, 2013 6:36 PM Internal Database changed to Intrinsic Database
    Thursday, June 13, 2013 4:51 PM

All replies

  • why "number of users <20 for intrinsic db ?

    Using an intrinsic db is just a matter of comfort: you use the data designer in LS, and you enjoy the comfort. When it comes to performance or whatsoever, when the db is deployed there is no difference at all.

    Also the usage of RIA services on top of either intrinsic or external db is exactly the same.


    paul van bladel

    Thursday, June 13, 2013 5:16 PM
  • External Databases are necessary if you don't want to run the DB in the cloud (private or public) but need to run it on an in-house dedicated DB server instead.

    There are many reasons for this scenario, like security or how technically advanced (or not) your IT group is. I work for companies that due to security and regulatory concerns either cannot or will not operate a cloud (private or public). I also work for companies that don't have the funds to build a private cloud and are not comfortable with putting private confidential info out in the public cloud no matter how safe we show that it is.

    You will also need an "External Database" solution if you are using LighSwitch to provide a UX for existing internal data.

    But other than the scenarios I outlined above, it has been my experience that if you are developing a new application from the ground up and you have access to either a private or public cloud, then use the Internal Database that comes with Lightswitch.


    -Christopher DeMars

    Thursday, June 13, 2013 6:08 PM
  • Oh yeah, if you want to run Stored Procs, then you need an External DB, as LightSwitch does not support those.

    -Christopher DeMars

    Thursday, June 13, 2013 6:11 PM
  • Hi Christopher,

    I'm confused what you mean by external database, and especially about the relationship with the cloud?

    For me the only difference between an external db and let's call it an internal (or intrinsic, or LS managed), is the way how the scheme is managed.

    1. external db: the schema is managed externally but not by LS.

    2. internal db: LS manages the scheme, it's a "mirror" of what happens in the data designer in LS.

    But the eventual db (either internal or external) is a database, which needs to be deployed on a sql server instance. Where that instance is located is completely irrelevant: on premise or in the cloud.

    What is potentially different is that for using an external db in LightSwitch you only need DB_Writer rights on that db (since you don't need to deploy it). But for deploying the security tables, you'll need anyhow more rights.


    paul van bladel

    Thursday, June 13, 2013 6:17 PM
  • Having said all that, I ran some tests and successfully managed to convert LS apps using intrinsic database to switch to external. That gives you the option of prototyping and early db development in LS before switch and continuing externally. Also without ruining the internals of your LS project.

    I plan on running more tests very soon and completing a draft blog article on the subject.

    I expect it to be a one-way trip but never tried the reverse (yet).

    I am fully on board with the need for security, SP's, views, good indexing, LS incompatible database items, etc.. We almost entirely use external databases in production systems as LS is only a part, but significant part of a solution.

    Cheers

    Dave


    Dave Baker | Xpert360 blog | twitter : @xpert360 | Xpert360 website Opinions are my own. For better forums, please mark as helpful/answer, if it helps/solves your problem.

    Thursday, June 13, 2013 6:21 PM
  • Oh yeah, if you want to run Stored Procs, then you need an External DB, as LightSwitch does not support those.

    -Christopher DeMars

    You can deploy stored procs to any database: either internal or external. I think that you can even use stored procs in your local db during dev time, but I have never tried this.

    It's a piece of cake to deploy the additional scripts (for the stored procedures), together with your db.

    See: DEPLOY ADDITIONAL SQL SCRIPTS DURING AUTOMATED DEPLOYMENT WITH WEB DEPLOY FOR A LIGHTSWITCH PROJECT


    paul van bladel


    Thursday, June 13, 2013 6:32 PM

  • I plan on running more tests very soon and completing a draft blog article on the subject.

    Wow Dave, looking forward to that article !

    thanks


    paul van bladel

    Thursday, June 13, 2013 6:34 PM
  • Like Paul is saying is correct, it mostly boils down to who is "master of the schema": and that decision is not always under your control or made on technical ground :O

    Paul: I have some software for you to try too ofline, its Xmas!

    Cheers

    Dave


    Dave Baker | Xpert360 blog | twitter : @xpert360 | Xpert360 website Opinions are my own. For better forums, please mark as helpful/answer, if it helps/solves your problem.

    Thursday, June 13, 2013 6:37 PM

  • Paul: I have some software for you to try too ofline, its Xmas!


    Ok :) You know where to find me :)

    paul van bladel

    Thursday, June 13, 2013 6:41 PM
  • Paul, Christopher and Dave,

    Thank you for your great insight!

    I am the person who has to support the Lightswitch application and related database moving forward. I've worked with MS-SQL since 1996 and am comfortable with database design, optimization, etc.

    To provide greater detail, our LightSwitch application will make extensive use of RIA services to do data intensive calculations. Our "system" has a Live database, Gamma database and a Beta database.

    The calculations require "perfect" data. The data in the live system changes significantly every day. We have a process that then copies the live data to the beta site on a nightly basis. The data is fairly small at about 8gb.

    The challenge is in order to develop, one needs the beta data rather than the intrinsic database. I realize one could use SSMS to export the data from beta to the intrinsic data every morning but that takes too much time.

    Question #1: It seems the only way for a developer to "point" the connection string of visual studio / lightswitch to a non-intrinsic database is to utilize an external database from the onset. Is this correct?

    Question #2: Unfortunately, there are cases where something is not working in the live database that works in the gamma database. In scenario, we need to point the connection string of visual studio / lightswitch to the live database. This does not seem possible unless an "external database" is used from the onset.


    Thank you, Bill


    • Edited by jessiedog Friday, July 19, 2013 5:40 PM added ms-sql experience
    Friday, July 19, 2013 5:36 PM
  • Hi Bill,

    I just took a copy of a course manager LightSwitch project here (50+ entities, 50+ screens), pointed the intrinsic 'ApplicationData' container at a database full of data in a SQL2012 instance, and it runs ok in debug mode for the first few screens at least.

    At this point there are no guarantees such that 'Update Datasource' will work (without more work), but for your purposes you may not want to do such crazy things!

    I will test some more and write it up this weekend.

    Cheers

    Dave


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.


    • Edited by Xpert360 Friday, July 19, 2013 7:12 PM extra info
    Friday, July 19, 2013 7:08 PM
  • Dave,

    Thank you for the note. I enjoy reading http://xpert360.wordpress.com/.

    I read a few of your other posts. I noticed you stated you almost exclusively utilize external databases.

    From reading many posts, it apears the only real loss of using an external database is the automatic publication of database changes.

    Does this still hold true?


    Thank you, Bill

    Friday, July 19, 2013 7:18 PM
  • Hi Bill,

    Thanks for the kind words. I had a busy week product testing but will address the intrinsic-external switching this weekend. I was attempting to get the update external datasource working afterwards, which is more than you need for debugging against a copy of a production database.

    Cheers

    Dave


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Friday, July 26, 2013 7:38 PM
  • I'm curious: I've always attached to a SQL Server DB simply because I am also the SA on our Dev and Production boxes so I can do as I will.

    That said, I was under the impression that the Application data simply published to a SQL database upon publish of the application.  Is this not the case?
    Monday, July 29, 2013 1:51 PM
  • That is more or less the case, the schema changes are pushed to the SQL Server DB when you publish. You can override and do schema changes manually if LightSwitch cannot handle the changes internally.

    If you start with an Intrinsic database and then later decide that you want to manage the database externally then you face the prospect of manually applying changes to the LightSwitch Intrinsic database to match external schema changes. Then no longer using 'update data source wizard' and not allowing LightSwitch to publish schema changes on deployment.

    Chris, for you, like me, this does not seem too bad as we both have good SQL skills and admin access to databases and servers, so we can do as we will. For some devs it is a big hassle or even a showstopper. If you start off with an external database, you manage the schema in SSMS / VS database project from day one.

    Cheers

    Dave


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Monday, July 29, 2013 2:01 PM
  • Thanks for the clarification.  This is a good point to make as I begin to evangelize the merits of Lightswitch internally as I'm not willing to give SA rights to anyone but executives.
    Tuesday, July 30, 2013 4:01 PM