locked
Use Cases for multiple instances of 2-tier app sharing the same database? RRS feed

  • Question

  • I need some group-think help here. I have a project that needs a 2-tier app published to four different Windows 7 machines and we need to share the same database. I'm using SQLExpress 2012 (because I don't know how to use LocalDB yet.) Surely others have this type of deployment concern I can learn from?
    Wednesday, March 13, 2013 2:01 AM

Answers

  • Local DB is really designed for single instance applications and not multi user systems.

    SQL Express can do the job.. but it is limited in it's resource use and maintenance. Limited to 1 CPU and 1 GB RAm usage, no matter what is installed on the server it is installed on. Also the database can only be 10GB in size. On a side note if you do use express expect to be upgrading to a larger database engine in the future.

    You need to make sure that what ever solution you do use that you maintain it, the databases can not be installed and then forgotten about. As the data gets older the performance will degrade, you need to maintain the storage and indexes of the database as well as the consistancy of the data within. Express does not have the built in methods to do this automaticly, you will need to eith script or use third party tools. Also the most important thing... Backups, you will need to script solutions to backup these databases, simply making a copy of the raw files will not do you have to use the native backup tools, or third party tools.

    My suggestion would be to move to standard edition and instal it on a server, this will allow you to use the maintenace functions of the standard edition as well as use the full resources o the server.

    For the lightswitch application, I would be setting up your conection as a external datasource, then to upgrade the application you would apply the databases through script at deployment.

    Wednesday, March 13, 2013 9:25 PM
  • This is exactly what LightSwitch was designed to do.

    You can publish to any flavour of SQL Server (from 2005 on). LocalDB should only be used for developing your application anyway.

    However, you must have a machine somewhere on the network that has the SQL Server instance installed on it, & all workstations where you deploy the application must be able to "see" it. You point to its location in the Publish Wizard (plus supply the appropriate connection string details) & then deploy the application to any number of workstations by running the generated setup program.

    If you also put the setup files on a network share (rather than using something like a CD), you only need to copy the republished files for any updates to one place, for all of the workstations to be using the latest version (assuming of course, that they all installed the application from that same network share).


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Wednesday, March 13, 2013 4:02 AM
    Moderator

All replies

  • This is exactly what LightSwitch was designed to do.

    You can publish to any flavour of SQL Server (from 2005 on). LocalDB should only be used for developing your application anyway.

    However, you must have a machine somewhere on the network that has the SQL Server instance installed on it, & all workstations where you deploy the application must be able to "see" it. You point to its location in the Publish Wizard (plus supply the appropriate connection string details) & then deploy the application to any number of workstations by running the generated setup program.

    If you also put the setup files on a network share (rather than using something like a CD), you only need to copy the republished files for any updates to one place, for all of the workstations to be using the latest version (assuming of course, that they all installed the application from that same network share).


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Wednesday, March 13, 2013 4:02 AM
    Moderator
  • Thanks for stopping by Yann.

    I've got the connectivity worked out; the .\SQLExpress instance gets installed on a preferred "server" instance of a client (Windows 7) and all other instances of the app installed on other machines share the same database because each instance of the app uses the same connection string.

    Your following comments were particularly helpful:

    If you also put the setup files on a network share (rather than using something like a CD), you only need to copy the republished files for any updates to one place, for all of the workstations to be using the latest version (assuming of course, that they all installed the application from that same network share).


    • What do we do, copy the contents of our newly updated Publish folder over the existing assets in the Publish folder we stored on the network share and then run setup again?

      When a latest version of the app gets located on the network share are you saying the app on each other client machine can automatically know and will update itself?

    • Where's the app anyway? All I can find is the ClickOnce icon in the AppData > Roaming path for the user logged into the machine. Is that "the app?"

    • Do we have any control of where on the machine the ClickOnce app is actually installed then? I'm not fond of the AppData > Roaming path as it can get omitted by those doing backups.

    • Given the compelled use of the AppData > Roaming path on each machine are there any best practices for using a shared connection string?

    Finally (for now I think) is there a known way to automate all of this? Apparently I have to go onto the premises to do a lot of onsite work to install and configure LightSwitch 2-tier apps. I'm not lazy but having to have hands-on kills most chances to sell to those from out of town that will not have the employee(s) that can do it themselves.



    Wednesday, March 13, 2013 7:06 PM
  • Local DB is really designed for single instance applications and not multi user systems.

    SQL Express can do the job.. but it is limited in it's resource use and maintenance. Limited to 1 CPU and 1 GB RAm usage, no matter what is installed on the server it is installed on. Also the database can only be 10GB in size. On a side note if you do use express expect to be upgrading to a larger database engine in the future.

    You need to make sure that what ever solution you do use that you maintain it, the databases can not be installed and then forgotten about. As the data gets older the performance will degrade, you need to maintain the storage and indexes of the database as well as the consistancy of the data within. Express does not have the built in methods to do this automaticly, you will need to eith script or use third party tools. Also the most important thing... Backups, you will need to script solutions to backup these databases, simply making a copy of the raw files will not do you have to use the native backup tools, or third party tools.

    My suggestion would be to move to standard edition and instal it on a server, this will allow you to use the maintenace functions of the standard edition as well as use the full resources o the server.

    For the lightswitch application, I would be setting up your conection as a external datasource, then to upgrade the application you would apply the databases through script at deployment.

    Wednesday, March 13, 2013 9:25 PM
    • What do we do, copy the contents of our newly updated Publish folder over the existing assets in the Publish folder we stored on the network share and then run setup again?

    Yes for the first bit, but no you don't run the setup again. The next time the user starts the application, they'll simply get the updated version.

    • When a latest version of the app gets located on the network share are you saying the app on each other client machine can automatically know and will update itself?

    Yes, but only the next time that the application is started.

    • Where's the app anyway? All I can find is the ClickOnce icon in the AppData > Roaming path for the user logged into the machine. Is that "the app?"

    You can't change the path, so I wouldn't worry about it. The application is stored in a location that allows a user without admin permissions to able to "install" it.

    • Do we have any control of where on the machine the ClickOnce app is actually installed then? I'm not fond of the AppData > Roaming path as it can get omitted by those doing backups.

    No, you have no control over the installed path.

    • Given the compelled use of the AppData > Roaming path on each machine are there any best practices for using a shared connection string?

    That's all taken care of by the Publish Wizard.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Thursday, March 21, 2013 5:34 PM
    Moderator