locked
Using SQL Server Express with Azure RRS feed

  • Question

  • Hi I am trying to develop an MVC 2 application on Azure which uses a local SQL Server Express Database rather than SQL Azure at the moment. The application is just running locally on the development fabric, it is not hosted on Azure just yet.  I am using the Entity Framework to access the database which is stored under App_Data, a simple .mdf database containing 1 simple table at the moment. If I run the application as an ASP.Net MVC 2 app all works well but if I run it as a cloud app (web role) I can retieve and update the database temporaily and view the updates on different views but once the application quits and restarts all changes made to the data are lost. Please help! Thanks.. I am using Visual Studio 2010 Pro on Windows 7.
    Tuesday, November 2, 2010 6:41 PM

Answers

  • You cannot use a SQL Express user instance stored under App_Data for this. When you launch the project in the development fabric, the app is really running from a copy in the bin\Debug\[YourProjectName].csx folder of the cloud project, and the database that the application modifies will also be a copy in that folder. Every time you restart the application in the development fabric, the changes to the database are overwritten by the copy in your project's folder.

    If you are going to test the cloud project locally using SQL Express, you are better off using a database that is not running in user instance mode.

    • Marked as answer by G Clynch Tuesday, November 9, 2010 2:00 PM
    Wednesday, November 3, 2010 4:37 PM
  • So if I understand you there is no way to use SQL Express in the way I want for this type of application? I need to use full SQL Server or some another DBMS?

    The solution is to not include SQL Server Express in your solution. You should be able test locally by connecting to either SQL Server or SQL Server Express by just providing a connection string - and then repoint that connection string to the appropriate SQL Azure database.

    • Marked as answer by G Clynch Tuesday, November 9, 2010 2:00 PM
    Monday, November 8, 2010 4:47 PM
    Answerer
  • Just to add to what Neil is saying, it’s not that SQL Express doesn’t work at all but rather that it can’t be used in user instance mode with an attached database in the App_Data folder because the database is always copied to the bin folder of the cloud project.

    Well, you may be able to use a workaround if you change your connection string slightly. Presumably currently somewhere within the connection string you have a key-value pair that reads AttachDBFilename=|DataDirectory|<yourdatabase>.mdf, where <yourdatabase> is the name of your database file. |DataDirectory| is a special notation that indicates the App_Data directory of the web application. If you replace |DataDirectory| with the absolute path to your .mdf file in the App_Data directory of the web application project, then, when you launch the application in the development fabric, it will use the database copy in the web application folder instead of its copy in the bin folder of the cloud project. Of course, if you move the location of the project, the connection string becomes invalid.

    I assume that you are aware that this is only good for testing your app locally in the development fabric and that you currently cannot use SQL Express (or SQL Server) in Azure and that even when admin mode becomes available and you manage to install SQL Express in an Azure VM, it doesn’t really make a lot of sense to store you database in the VM’s local disk.

    • Marked as answer by G Clynch Tuesday, November 9, 2010 2:00 PM
    Tuesday, November 9, 2010 2:54 AM

All replies

  • If the updates to the SQL Express DB are visible outside your application (say via the management studio), then they are being written to the DB. I would look to see if there's something in your role's startup that's clearing the database. You might be able to see this if there's anything in the DB after the role quits but before it restarts.
    Tuesday, November 2, 2010 7:44 PM
  • Hi,

    I'm interested to see if your updates are really persisted to the database. You can test this by looking in the database while your application is running.


    With regards,

    Patriek
    If this reply is of help to you, please don't forget to mark it as an answer.
    Tuesday, November 2, 2010 9:22 PM
  • You cannot use a SQL Express user instance stored under App_Data for this. When you launch the project in the development fabric, the app is really running from a copy in the bin\Debug\[YourProjectName].csx folder of the cloud project, and the database that the application modifies will also be a copy in that folder. Every time you restart the application in the development fabric, the changes to the database are overwritten by the copy in your project's folder.

    If you are going to test the cloud project locally using SQL Express, you are better off using a database that is not running in user instance mode.

    • Marked as answer by G Clynch Tuesday, November 9, 2010 2:00 PM
    Wednesday, November 3, 2010 4:37 PM
  • Thanks a lot for your reply. Yes it does as you say, it is running on a copy of the database which is being overwritten when the application in the development fabric restarts. So if I understand you there is no way to use SQL Express in the way I want for this type of application? I need to use full SQL Server or some another DBMS?

     

    Monday, November 8, 2010 4:11 PM
  • So if I understand you there is no way to use SQL Express in the way I want for this type of application? I need to use full SQL Server or some another DBMS?

    The solution is to not include SQL Server Express in your solution. You should be able test locally by connecting to either SQL Server or SQL Server Express by just providing a connection string - and then repoint that connection string to the appropriate SQL Azure database.

    • Marked as answer by G Clynch Tuesday, November 9, 2010 2:00 PM
    Monday, November 8, 2010 4:47 PM
    Answerer
  • Just to add to what Neil is saying, it’s not that SQL Express doesn’t work at all but rather that it can’t be used in user instance mode with an attached database in the App_Data folder because the database is always copied to the bin folder of the cloud project.

    Well, you may be able to use a workaround if you change your connection string slightly. Presumably currently somewhere within the connection string you have a key-value pair that reads AttachDBFilename=|DataDirectory|<yourdatabase>.mdf, where <yourdatabase> is the name of your database file. |DataDirectory| is a special notation that indicates the App_Data directory of the web application. If you replace |DataDirectory| with the absolute path to your .mdf file in the App_Data directory of the web application project, then, when you launch the application in the development fabric, it will use the database copy in the web application folder instead of its copy in the bin folder of the cloud project. Of course, if you move the location of the project, the connection string becomes invalid.

    I assume that you are aware that this is only good for testing your app locally in the development fabric and that you currently cannot use SQL Express (or SQL Server) in Azure and that even when admin mode becomes available and you manage to install SQL Express in an Azure VM, it doesn’t really make a lot of sense to store you database in the VM’s local disk.

    • Marked as answer by G Clynch Tuesday, November 9, 2010 2:00 PM
    Tuesday, November 9, 2010 2:54 AM