app_data: a strange folder

hi,

I noticed that inVWD i can create my sql database and place it in a special folder called app_data, what is this new?
i think it is imposible because sql server is a serverdb and not a file based db like access!It's for Sql Express 2005, which is more or less a file DB. Also, the default membership provider model takes advantage of SQL Express 2005 (ASPNETDB.MDF file) automatically created under this directory.|||what are the main differences between "normal" and express edition, which is more performant?
does sql eXpress allow external connections (webs in other servers or clients)?|||It's still a server-based db, that's just it's data file. The server process will just spawn when you first try to access it.

App_Data vs SQLEXPRESS performance?

Hi all,

Just wondering if there are any performance differences with having your database file in the App_Data folder vs having it directly on SQLEXPRESS.

Cheers,

Simon

Yes, but not much of one in most cases. If it's in App_Data with an autoattach, the database will be attached when it first accessed, which will delay the first request slightly. It will then stay attached for subsequent accesses. Eventually if there are no more accesses, the system will unattach it to conserve resources, I think it's between 5 and 30 minutes, but could be wrong.

In any case, the performance difference is slight, and the auto attaching and unattaching will help free memory and resources for the other applications that may be running on that server when the database isn't being used. Extremely nice for both infrequent database applications (Low resources), and high usage database applications (Since the database will always stay attached).

|||Great thanks for the detailed reply Motley! I think I'll migrate my db into the App_Data folder now =)

Cheers

App_Data new DB fails

Hello,

I'm trying to create a new SQL Express database in VWD, and I get the error: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

What is this error all about? I don't understand it.

Thanks.

The db when added in VS has no owner and is somehow bound to the profile. So many things can go wrong with that, so instead of guessing, wipe it out and let the IDE recreate it.

This blog saved me days of heartache. Unfortunately, I still wasted time till I came across it.

http://www.sqljunkies.com/WebLog/ktegels/archive/2005/11/15/17401.aspx

1. Disconnect all dbs, close down the IDE, close down SSMS(if any) on your machine.
2. Stop all services and processes related to sql server.
3. Rename(safer than deleting) the whole folder:
c:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

4.restart the IDE/SSE.
5. this song-n-dance may not work the first time. Try more than once.

I am really disappointed at the flaky architecture of SSE and have stopped using it since.

HTH!

APP_DATA directory

If I already have SQL2005 installed, can I create SQL express databases for distribution in my app, or do I need to install SQLExpress to run side-by-side?

I actually had SQLExpress originally but upsized it to the full version. Now I want to be able to create portable DBs with my application.

And if this is possible, how do I go about creating the DB?

Thanks!

You can refer to the upgraded SQL2005 instance just as you did to SQL Express. One thing to note is that if you have set "User Instance" attribute to true in your connection string, the connection to SQL2005 may fail with error message saying "User Instance can only be used with SQL Express..." (not exact, but something like this)|||

Thanks for the answer.. that helps on that error that I have received...

I think I might have been a bit unclear though...

On my local dev machine, I have the full SQL2005 version, but I want to create an application that can be distributed with a SQLExpress database in its app_data directory. This application will not require that the user attaches the .mdf through sql2005, but only that they have SQLexpress running as the DB will live locally in the APP_DATA directory.

Question is: Can I create the DB through my version and then just drop the .mdf in the APP_DATA directory and will it be compatible with SQLExpress?

Also, can I use a SQLExpress connection string / DB on my system during development without having to attach the DB to my sqlserver instance?

Sorry if my questions seem ignorant, but I am just trying to wrap my hands around the whole thing.

Thanks!

|||

NevermindIf you do not want to attache the database file at run time, you have to use a database in your SQL Server. That's because what your application needs is not only a database file, but also needs a SQL Server instance. So if you want to switch between SQL Express and SQL 2005 (means different SQL instances) without attaching the database file at run time, you have to change your connection to SQL, and move database as well. There are some options you can choose to move database:

How to move database using detach/attach:

http://msdn2.microsoft.com/en-us/library/ms187858(d=ide).aspx

And copy database with backup/restore:

http://msdn2.microsoft.com/en-us/library/ms190436(d=ide).aspx

This article shows a good torturial for changing SQL connections in web application:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

Anyways, I recommend attaching the database file at run time--then you just need to change connection to SQL without moving database

App_Data database not updating.

This is a weird problem. I added a database to the app_data folder, but when I make updates and then run some reports the updates aren't shown. The database is updated, cause queries show the updates and I inspected the database. The updates however, aren't being reflected when I run the reports.Hmmm. Are you sure the reports are running off of the same database as the queries?|||At first they weren't, but then I realized that mistake and added the correct database to the App_data. When I do this, does it copy the database to the App_data folder or does the App_Data folder point to the database. If it is the former is it capable of just pointing to the location or do I have to change all the connections?|||

helsaint:

At first they weren't, but then I realized that mistake and added the correct database to the App_data. When I do this, does it copy the database to the App_data folder or does the App_Data folder point to the database. If it is the former is it capable of just pointing to the location or do I have to change all the connections?

I'd say you need to change the connections to point to the desired database.

|||

Hi,

When you copy the correct database to App_Data, ASP.NET will attach the database automatically when your ASP.NET application is running.

So, you have to check your connection string to see if it is pointing to the correct database file name.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Hi I am so stupid. The web_config file's entry for the database for the reports was different from the connection strings. It was pointing to an older version. Had to change that link.

Thanks guys