App_Data/ASPNETDB.MDF to Sql Server 2005 (or 08)

Question

I've been developing an ASP.NET WebForms app that needed account login functionality (e.g. register new users, change passwords, recover passwords, profiles, roles, etc). To do this, I used FormsAuthentication with the default data store, which, to my surprise, is an MDF file in App_Data. When it comes time to actually deploy this app. live on the web, I'm going to use some shared hosting like GoDaddy or another cheap company.

For efficiency, I'd like to switch over from this MDF to actual SQL Server 2005 or 2008 (who in their right mind uses flat files?). With shared hosting, however, I'm not going to be able to run any programs like aspnet_regsql.exe. I'll just have a single login and password to a SQL Server database, and an FTP account into the domain root. No MSTSC remote desktop, no console access, no tinkering in IIS, etc.

I won't need to transfer any user accounts from ASPNETDB.MDF (site will be starting with zero users), but how am I suppose to:

1) Easily create the tables, procedures, etc. that Visual Studio automatically created in ASPNETDB.MDF when I used Web Site Administration Tool to get started with FormsAuthentication?

2) Have the SQL membership provider use a connection string I give it instead of using whatever it is now to connect to this ASPNETDB.MDF. Hell, I don't even see any connection string to this MDF in the web.config; how the hell is my app. even finding it? Machine.config? That would be a poor decision if that's the case. This behind-the-scenes crap drives me nuts.

Any help from someone who has been through this migration would be very, very much appreciated!

Answer 1

.mdf is not a flat file. It is a SQL server express database file.

Sometimes it is best to not drive a Porsche on a dirt road... ;)

I recommend getting a dedicated server if you are going to host a website with a serious backend. Shared hosting has limits from an admin point of view.

For finding the tables in SQL express, there is a tool called 'management studio' that ships with SQL Server EE that should allow you to get to the tables in the .mdf etc

Here is an example of a connection string to an .mdf in your web.config

<connectionStrings>

        <add name="AddressBookConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\AddressBook.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

</connectionStrings>

I empathize with your frustration. Setting up and hosting a complex website is no easy task. Usually running wizards to configure a web application is a bad idea if you plan anything serious for the site, unless you are learning about web applications. You need to get past that level of abstraction and learn more about how to manually configure/override the membership provider etc if you are going to be hosting professionally.

Hope this will point you in the right direction. Good luck ;)