MDF file vs. Local Sql Server...what's the connection?

[Darrel]

I'm still having a hell of a time figuring out this whole SQL Express set
up.

I finally discovered why I couldn't run the aspnet_regsql...my local sql
server wasn't running.

I turned that on, and now I can see a list of DBs running locally.

However, the .mdf file I have in my app_data folder does NOT appear on this
list.

I'm confused. Is my local SQL server (installed with VS.net) NOT the same as
my SQL Express DB in my app_data folder? If not, how do I set up my MDF file
to accept the membership provider tables?

Any help appreciated. This has been a huge road block for me.

-Darrel

 

[Mark Fitzpatrick]

The main difference is: the file that's in the app_data folder is a SQL
Server database, but it's not technically attached to the database as the
ones in your view are. You are connecting to the database as an independent
file, which is typical of a sql server express database in an app_data
folder. So, technically SQL Server Express is handling requests to it, but
is not attaching it as a constantly running database.

Make sure that your hosts supports SQL Server Express as a lot of them
don't. If they don't you'll have to publish the database to a full SQL
Server and it won't be running from the app_data folder.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - Expression

 

[Darrel]

The main difference is: the file that's in the app_data folder is a SQL

Server database, but it's not technically attached to the database as the
ones in your view are. You are connecting to the database as an
independent file, which is typical of a sql server express database in an
app_data folder. So, technically SQL Server Express is handling requests
to it, but is not attaching it as a constantly running database.

So...what do I do? How do I use my MDF file as my main DB and get membership
provider tables set up in it? Or should I not be using that? In which case,
how do I set up a DB in my local SQL server?

I'm trying to accomplish what all of the video tutorials make appear easy.
Just make a few clicks in VS.net and you have your MDF file with all your
memebership provider data set up. Alas, I'm guessing those are all highly
edited tutorials and are missing 12 complex steps or so. ;o)

Make sure that your hosts supports SQL Server Express as a lot of them
don't. If they don't you'll have to publish the database to a full SQL
Server and it won't be running from the app_data folder.

I'm positive the host will not support Express, however, I was under the
impression that it's not too difficult to develop locally with SQL express
and then just 'copy' the DB into a real SQL DB on the server. Fair
impression? That said, I'm finding it hard just to set up the Express DB in
the first place, so maybe I'm way underestimating the complexity of this
process.

-Darrel

 

[Norman Yuan]

So...what do I do? How do I use my MDF file as my main DB and get
membership provider tables set up in it? Or should I not be using that? In
which case, how do I set up a DB in my local SQL server?

You do not use *.mdf file directly, SQL Server/Expresss does. You see
database list when connecting to SQL Server/Express, you do not see *.mdf
file.
When you use ASP.NET management wizard, it create a database on available
SQL Server or SQL Server Express. The *.mdf file is just database's file in
app_data folder. You do not deal with the file itself, SQL Server/Express
does. Once the database is created, you cannot touch the *.mdf file at all:
you canot copy/move/delete it.

However, there is a catch that make many new-bes confused when using SQL
Server Epxress, that is, SQL Server Express's USER INSTANCE. In this case,
the database based on the *.mdf file can be attach/detach to the SQL Server
Express when the application starts and end. Look at your connectionstring,
if there is something "AttachDBFile=..." and/or "User Instance=True", then
you are using SQL Server Express User Instance, which nost hosting services
would not support and you MUST make sure you DO understance what USER
INSTANCE is before using it.

Anyway, there is two ways for you to go ahead:

1. Just let the wizard do its job without knowing much what is going on
under the hood. You would for sure run into issues if you need to deploy
your website/project to other computer.

2. Thoroughly study SQL Server and Express before use it. As ASP.NET
developer working on MS platform, you need to this sooner or later anyway. I
see sound SQL Server knowledge as basic requirement for using it in
development. Once you know SQL Server well, then you can concentrate on how
to group uses, controls accessibilities. The easyiness of SQL Server based
membership provider is based on hard study of SQL Server.

I'm trying to accomplish what all of the video tutorials make appear easy.
Just make a few clicks in VS.net and you have your MDF file with all your
memebership provider data set up. Alas, I'm guessing those are all highly
edited tutorials and are missing 12 complex steps or so. ;o)

That easiness comes with price you should have paid previously: study SQL
Server.

If you know SQL Server well, then you can develop with SQL Server Express
and move to full SQL Server without single change. But remeber, USER
INSTANCE is SQL Server Express only feature, many ASP.NET book use it in
their sample code without much emphcise, assuming user has the SQL Server
knowledge, which confuses so many newbies, who post related question daily
here.

I'm positive the host will not support Express, however, I was under the
impression that it's not too difficult to develop locally with SQL express
and then just 'copy' the DB into a real SQL DB on the server. Fair

Once you know how SQL Server/Express works, you would know it is just a bit
more than copy/paste: you attach/detach, or back up/restore database from
one SQL Server to the other.

impression? That said, I'm finding it hard just to set up the Express DB
in the first place, so maybe I'm way underestimating the complexity of
this process.

Yes, study on SQL Server is due.

 

[Barrie Wilson]

When you use ASP.NET management wizard, it create a database on available
SQL Server or SQL Server Express. The *.mdf file is just database's file
in app_data folder. You do not deal with the file itself, SQL
Server/Express does. Once the database is created, you cannot touch the
*.mdf file at all: you canot copy/move/delete it.

this all gets more confusing than it ought to, but: if your MDF file is
detached from the database, you can move it anyplace you want to and then
re-attach it to any edition of SQL Server. Alternatively, you can do a
backup and the restore that to any SQL instance in any version.

The placement of the MDF file in the App_Data folder was arbitrary; it
could be anywhere. You also do not have to have an AttachDBFile clause in
your connection string; your MDF file(s) can just be permanently attached
on the DB, which is the usual practice on production databases.

All of these wizards and the VS IDE itself are all very nice (sort of) but
I'm afraid that more often than not they severely impair an understanding of
how things are working. People all over the place are dropping data sets,
fooling with the designers and not having a clue that behind the scenes the
only thing happening of any consequence is that Visual Studio is writing a
data access class for you. This is not a good thing. Ultimately you will
be WAY ahead of the game if you learn how to write SQL DDL to create
databases, tables, views, etc ... and how to write your own data access
classes. At that point, if you want to use the tools to reduce the tedious
work at least you will have some understanding of what they are doing. Of
course that's a fair amount of work and you should probably only do it if
you're at least half serious about writing code for a living or otherwise.
On the other hand, learning to use Visual Studio's wizards, designers, etc.
is ALSO a lot of work .... and might drive you crazy when things go wrong
(they will) and you have no idea how to fix them.

The tools CAN be useful, to be sure ... but remember one thing: there is
nothing you have ever seen in any .NET application that could not have been
written 100% in Notepad. I would not suggest doing this, but understanding
that it's true and *why* it's true will take you a long way towards a solid
understanding of the technology.

Norman is right: learn the basics of SQL Server architecture and management
and all of this stuff shoud begin to fall into place.

 

[Darrel]

You do not use *.mdf file directly, SQL Server/Expresss does. You see

database list when connecting to SQL Server/Express, you do not see *.mdf
file.

So...should I Not be creating MDF files in VS.net under APP_data?

When you use ASP.NET management wizard, it create a database on available
SQL Server or SQL Server Express. The *.mdf file is just database's file
in app_data folder. You do not deal with the file itself, SQL
Server/Express does. Once the database is created, you cannot touch the
*.mdf file at all: you canot copy/move/delete it\

If management creation wizard makes it, should I then see the MDF file under
my app_data folder? I've done that on one machine, but never saw the MDF
file. So I assume it was using some other DB on my machine.

Once the database is created, you cannot touch the *.mdf file at all: you
canot copy/move/delete it\

But I could get the data into a real SQL server, right? Or is that not
doable?

However, there is a catch that make many new-bes confused when using SQL
Server Epxress, that is, SQL Server Express's USER INSTANCE. In this case,
the database based on the *.mdf file can be attach/detach to the SQL
Server Express when the application starts and end. Look at your
connectionstring

That's another issue...what connection string? Am I suppose to be putting
that in the config file myself, or is that something ASP.net COnfiguration
Wizard is supposed to be doing?

1. Just let the wizard do its job without knowing much what is going on
under the hood. You would for sure run into issues if you need to deploy
your website/project to other computer.

Well, good to know. That's contrary to other info I've been told, but will
definitely look into this some more.

It seems that SQL Express's sole purpose is to make slick demos for MS
rather than anything useful for use developers, eh?

2. Thoroughly study SQL Server and Express before use it. As ASP.NET
developer working on MS platform, you need to this sooner or later anyway.
I see sound SQL Server knowledge as basic requirement for using it in
development. Once you know SQL Server well, then you can concentrate on
how to group uses, controls accessibilities. The easyiness of SQL Server
based membership provider is based on hard study of SQL Server.

I have a decent understanding of SQL Server (though there's plenty more).
It's what I use at my day job. What I don't get is SQL Express on my home
machine and what/how to use it. ;o)

If you know SQL Server well, then you can develop with SQL Server Express
and move to full SQL Server without single change. But remeber, USER
INSTANCE is SQL Server Express only feature, many ASP.NET book use it in
their sample code without much emphcise, assuming user has the SQL Server
knowledge, which confuses so many newbies, who post related question daily
here.

Ah. Do you have any specific resources to point me at? Specific book or web
site?

-Darrel

 

[Darrel]

this all gets more confusing than it ought to, but: if your MDF file is

detached from the database, you can move it anyplace you want to and then
re-attach it to any edition of SQL Server. Alternatively, you can do a
backup and the restore that to any SQL instance in any version.

Oh! Good. That's what I've heard. Good to know!

I understand the concept of not letting the IDE write the code for you. I
rarely let VS2003 write any code for me. I never liked the output that
ASP.net controls produced, so I didn't even use the web controls that often.

However, all the hype of 2.0 seems to suggest that it truly is a Rapid
Development Environment with much better drag and drop abilities.

Alas, it appears to be more of a smoke and mirrors thing. ;o)

I do have an OK understanding of SQL server, but not from the admin side.
I've mainly build tables, stored procedures, connection strings and the
like. It sounds like you are both saying I really need to get into admin
issues. Can either of you recommend some online reading to get a handle on
what VS.net is doing in relation to SQL and SQL express? I definitely want
to know what is going on behind the scenes.

-Darrel

 

[Barrie Wilson]

But I could get the data into a real SQL server, right? Or is that not
doable?

SQL Express is plenty "real" ... doesn't have all the features and power of
the $$$ editions, but it's real .. and capable

That's another issue...what connection string? Am I suppose to be putting
that in the config file myself, or is that something ASP.net COnfiguration
Wizard is supposed to be doing?

examine your config files after you use your wizards

It seems that SQL Express's sole purpose is to make slick demos for MS
rather than anything useful for use developers, eh?

again, it's plenty useful for developers ... at a great price; btw, I think
MS still sells the Developer Edition of SQL Server for $50 .. which is hard
to beat

Ah. Do you have any specific resources to point me at? Specific book or
web site?

for one thing, don't forget about the Books Online (the help system) .. if
there's a decent SQL Step By Step book or similar, perhaps you could look at
that ... and surely there are numerous good web sites with tutorials (?)

 

[Darrel]

That's another issue...what connection string? Am I suppose to be putting

examine your config files after you use your wizards

I do.

And nothing is changed. No connection string at all.

I think that's the issue. Is the ACW SUPPOSED to be changing my config
files? Is something wrong with my install?

again, it's plenty useful for developers ... at a great price; btw, I
think MS still sells the Developer Edition of SQL Server for $50 .. which
is hard to beat

All in all, it maybe seems that my hangup is with SQL express and not
ASP.net 2.0 in general. I don't have the Dev Edition, but, yea $50? Why not!
In the meantime, I have a remote SQL server I can use...it just slows things
down a bit.

for one thing, don't forget about the Books Online (the help system) .. if
there's a decent SQL Step By Step book or similar, perhaps you could look
at that ... and surely there are numerous good web sites with tutorials
(?)

Well, that's the thing. I'm fnding plenty of ASP.net tutorials that don't
really go into any of the issues I'm having with SQL Express. The tutorials
is basically 'click, drag, click, drag an you're done!'

What I need is a tutorial that:

- tells me what I'm supposed to be manually setting up via web.config vs.
what the wizard is supposed to be doing
- showing me what actually gets created when using the membership provider
wizard
- what DB the config wizard is using by default and how to get to it.

Thanks for all your help!

 

[Barrie Wilson]

I understand the concept of not letting the IDE write the code for you. I
rarely let VS2003 write any code for me. I never liked the output that
ASP.net controls produced, so I didn't even use the web controls that
often.

well, I didn't say not to use ASP.NET controls ... if you don't, there's
really little point in using ASP.NET at all. I don't think the output of
the server controls is especially problematic per se .... for example, the
output from <asp:Label> or <aspropDownList> is quite acceptable (and if
you don't like the output, you can override the rendering methods and
output, literally, whatever you want) ...

think back to the original HTML: an <H1> tag was static; how it behaved
depended completely on how your browser decided to render it ... now, you
can have a (roughly) formally equivalent <custom:H1> tag that outputs the
national budget .. or part of it <custom:H1 department="defense"> .. in
multiple formats, with other options <custom:H1 department="defense"
format="xls" fiscalYear="2007" mailDestination="(e-mail address removed)"
tone="angry"> .... there's a lot of inherent and latent power in the model

all that said, yes, sometimes the output of an entire page, as a whole, can
be disconcerting to look at ....

However, all the hype of 2.0 seems to suggest that it truly is a Rapid
Development Environment with much better drag and drop abilities.

how much easier does it get than dropping a table on to the design surface
and having a complete table maintenance facility in place in 10 minutes or
less? not all that long ago a developer might take a few months for a
similar outcome and make a LOT of money doing it .... OTOH, a sophisticated
ASP.NET app is not necessarily an easy thing to produce; it's more than
dragging and dropping

Alas, it appears to be more of a smoke and mirrors thing. ;o)

it's more a problem of obscurity than deception

 

[Darrel]

it's more a problem of obscurity than deception

A fair statement!

-Darrel

 

[Barrie Wilson]

I do.

And nothing is changed. No connection string at all.

I think that's the issue. Is the ACW SUPPOSED to be changing my config
files? Is something wrong with my install?

I hear 'ya; ASP.NET configuration can be maddening ... why don't you have a
look at your machine.config file ... should be located around here:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG (if you're using
Framework v2.0.50727; otherwise, look at the one you *are* using)

I think MS really dropped the ball badly on all of this; these files are a
complete mess to the untutored ... but, examination of that file, along with
the web.config file in your app directory, should shed some light on how
you're actually configured

you should also check these out in Help or on MSDN:
ASP.NET Configuration File Hierarchy and Inheritance
Creating and Configuring the Application Services Database for SQL Server

generally, look in the hierarchy:
Development Tools and Languages->Visual Studio->Visual Web
Developer->Configuration Managment for ASP.NET


if you haven't given up by midnight and decided to become a plumber you're
probably gonna do ok ..

 

[Barrie Wilson]

A fair statement!

fair, but not comforting