locked
SQL Local db and Azure RRS feed

  • Question

  • User-1314346660 posted

    Hello experts,

    Up until now I have used SQL Server in Azure to store data for my web applications. However, I now have a requirement to use a local db. I am struggling with the concept and was hoping someone could help me out.

    Is it possible for a web app to use a local db? And if so, does the client machine need sql express installed? I want the local db to be updated by a webform. 

    Can someone point me to a tutorial to set this up please. I have a database schema ready to go that i can copy across.

    Thanks in advance.

    Billson3000

    Friday, February 1, 2019 1:39 PM

All replies

  • User1120430333 posted

    Is it possible for a web app to use a local db? And if so, does the client machine need sql express installed? I want the local db to be updated by a webform.

    Yeah you can do it. But is it going to work? No, it is not going to work. A local instance of MS SQL Server is a detached MDF file that is accessible by a cut-down version of MS SQL Server Express database engine,  a single user instance database that is not a multiple usage database.. The program is going to blow up if using a single user instance of the database when deployed to IIS.

    What is Localdb?

     https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/

    If using a detached MDF file, then its going to be used on a local machine for a Windows form desktop solution, as an example. It can be used in Web development using Visual Stuido and IIS Express. But when the Web application is deployed to IIS, then the local MDF file must be attached  to the MS SQL Server database engine for Server or Express versions. 

    Friday, February 1, 2019 5:34 PM
  • User283571144 posted

    Hi Billson3000,

    According to your description, I couldn't understand your requirement clearly.A

    Could you please tell me why you want your application to use a local db instead of azure sql db?

    In my opinion, the db is used to store the data which you have used in your web application. 

    You application's user will have its own data stored in your application db.

    If you want to use local db, you should firstly make sure your application could access the localdb.

    Then you should make sure the localdb has the enough resource to serve the db service(Make sure the local db performance is well enough to serve your application).

    Best Regards,

    Brando

    Monday, February 4, 2019 3:55 AM
  • User-1314346660 posted

    Hi Brando,

    Yes I wasn't very clear about the requirement. My requirement is to enable some very limited offline functionality when my webapp isn't connected to the internet.

    I have this working in development. Using a localdb and then transferring the data to azure when a connection exists. My question really is..."what is the best way to manage this".

    I know the users and can access the C: of their machines but would rather not. So the questions for me now are "does the end user need SQL express for Localdb to work? and "is this an approporiate way of managing this workflow"?

    The alternative that I can think of is to put an MS Access mdf file on the C: of the local users and write to that instead.

    Billson3000

    Monday, February 4, 2019 8:42 AM
  • User1120430333 posted

    I know the users and can access the C: of their machines but would rather not. So the questions for me now are "does the end user need SQL express for Localdb to work? and "is this an approporiate way of managing this workflow"?

    You would have to install MS SQL Express Localdb on all client machines, and you would have to make the detached MDF  database file and place it on every client machine.

    The alternative that I can think of is to put an MS Access mdf file on the C: of the local users and write to that instead.

    https://www.infoworld.com/article/3060115/application-development/how-to-work-with-msmq-in-c.html

    https://msdn.microsoft.com/en-us/library/ms711472(v=vs.85).aspx

    <copied>

    Message Queuing (MSMQ) technology enables applications running at different times to communicate across heterogeneous networks and systems that may be temporarily offline. Applications send messages to queues and read messages from queues. The following illustration shows how a queue can hold messages that are generated by multiple sending applications and read by multiple receiving

    <end>

    https://visualstudiomagazine.com/articles/2014/02/01/offloading-work-from-your-application-with-a-queue.aspx

    You XML or Json serialize the data and send it to the MSMQ. The database goes back online, then you go to the queue get the data,  deserialize the data and write it to the database tables. You can have a queue per table or have an indicator on he XML or Json record as to the table the record belongs to.

    https://www.codeguru.com/csharp/.net/net_general/internet/article.php/c19563/Leveraging-MSMQ-in-ASPNET-Applications.htm

    Monday, February 4, 2019 10:20 AM
  • User541108374 posted

    Hi,

    Billson3000

    Is it possible for a web app to use a local db? And if so, does the client machine need sql express installed? I want the local db to be updated by a webform. 

    Yes it could but then the SQL instance would have to run on the same machine as your web application. You can also have a SQL Server installed on a server close to the server on which you run your web application. You don't have to install a local SQL on the client machine (unless that's the machine on which your web application runs locally as well and you want to have it portable on a laptop without internet connection.

    Update: I once had to make a web application for a company that does dredging with large ships (with flakey internet connection over satellite). What I ended up doing was making use of PWA (https://developers.google.com/web/fundamentals/instant-and-offline/web-storage/offline-for-pwa). 

    Microsoft had something in the works in the past by Steve Sanderson but it never came into production unfortunately. I'm sure there are packages available for your preferred JavaScript framework for this.

    Kris.

    Wednesday, February 6, 2019 7:58 AM
  • User-1314346660 posted

    Thanks Kris and DA924.

    Looks like I have stumbled across a common and not wholly resolved problem with webapps!

    I think for my purposes then the most simple way of managing this is to place a database file onto the client machine. MS Access would be good but I can already see that there will be problems with 64b vs 32b conversion. Azure uses only 64b and client machines are most likely 32b so that's probably a no-go!

    I have looked into SQLite. It isn't something I have ever used before but it seems like it could be a good solution. Have you any experience of SQLite?

    Billson3000

    Thursday, February 7, 2019 10:17 AM
  • User1120430333 posted

    What kind of application is this that you would need this kind of functionality? 

    Thursday, February 7, 2019 12:56 PM
  • User753101303 posted

    Hi,

    Seems a confusion with SQLLocalDB which is a "no admin" version of SQL Server. "Local" means on the same machine than the application that uses it ie for a web app, this file would be on the web server. Your server side code doesn't have access to client side files (and moreover if you are currrently disconnected). You have also https://www.w3schools.com/html/html5_webstorage.asp which allows an HTML/JavaScript to still store or interact with data while offline.

    It seems you still need to get some clarification about what they asked you exactly.

    Thursday, February 7, 2019 1:14 PM
  • User-1314346660 posted

    Its a web application. Stores data in SQL Azure. But...some limited offline functionality is required. The db needs to 'pull down' data from the server, let the user update and add to it and then synch back to the server later.

    I thought this would be straightforward! Ha. How wrong I was. I am not sure why Microsoft haven't nailed this yet! I guess it wont be too long until 100% connectivity is the norm so its a waste of dev time.

    I am going to look into SQLite on the user machine with a synch to azure when connected to the internet.

    Billson3000

    Thursday, February 7, 2019 8:07 PM
  • User1120430333 posted

    Its a web application. Stores data in SQL Azure. But...some limited offline functionality is required. The db needs to 'pull down' data from the server, let the user update and add to it and then synch back to the server later.

    So this is some kind of Intranet application in reality that is being hosted on the Azure platform? 

    You may want to look at linked tables.

    https://www.mssqltips.com/sqlservertip/3087/creating-a-sql-server-linked-server-to-sqlite-to-import-data/

    Thursday, February 7, 2019 10:44 PM
  • User-1314346660 posted

    Thanks DA924.

    No this is a web application but there is a very limited need for some offline working. Thanks for the SQLite link. I am hoping that will be my solution. I will post back here with some progress notes .

    Friday, February 8, 2019 9:56 AM
  • User753101303 posted

    Really unclear. Your C# code runs on the web server. HTML, CSS, JavaScript runs on the client side  but doesn't have access to local files. You do have HTML 5 client storage available that JavaScript can interact with.

    Or you want something in case the web server doesn't have access to its usual  database ? (rather than if the browser can't access the web site ?)

    Friday, February 8, 2019 11:52 AM