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

Hi,

.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 ;)

Answer 2

The MDF isn't actually a flat file database, it uses the SQL Express engine with user instances so it can attach databases on-the-fly. This engine is a limited version of SQL Server, although the limits are quite high so unless you have a busy site it won't be an issue. From here, the limits are...

• Lack of enterprise features support
• Limited to one CPU One GB memory
• limit for the buffer pool Databases
• have a 4 GB maximum size

The connection string should be in your web.config, search for "AttachDbFileName" (without quotes). For more info see here.

If you have full SQL in your hosting package usually the hosting company gives you a web tool to attach the MDF file (by specifying it's path) to an full SQL server instance. You can just leave the MDF file in the app_data folder (since the folder has permissions set preventing the db file being downloaded by anonymous users).

Once attached to use the SQL Server Standard Instance (or other version) instead of SQL Express user instance you just need to change connection string in the web.config file to point to the database instance that you configured using the web tool rather than using the Auto Attach feature of SQL Express.

See here for the godaddy instructions.

Answer 3

Some details will be different depending on your hosting provider, but:

1. If you have not done so already, download and install Microsoft SQL Server Management Studio Express

2. Your hosting provider will have to provide you the admin connection information to connect to your sql server instance. With that information verify that you can connect using MSSMSE. If you can't then the rest of this won't matter.

3. Go to this weblog and follow the steps to create the required database scripts to move your database to production. These scripts can be run in the query window of MSSMSE.

4. Normally your connection string will be stored in the Web.Config file. The connection strings. You will not want to use the "AttachDbFilename=" style if, as you say in your question, you have moved to full SQL Server. Your connection string will look more like this:

   <add name="myConnectionName" connectionString="Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;">

Answer 4

This is very easy to do if you are starting with new data

I ran "C:\Windows\Microsoft.NET\Framework\v2.0.50727\a spnet_regsql -C *Data Source=localhost;Initial Catalog=MYDB;Persist Security Info=True;User ID=;Password=**;" * -A all" to include the ASP.NET membership tables and sprocs into the db of my choice. If you do you this..ensure you override the default LocalSQLServer connectionstring or it won't find the db like this in web.config of your asp.net website

<connectionStrings> <clear/> <add name="LocalSQLServer" connectionString="Data Source=localhost;Initial Catalog=MYDB;Persist Security Info=True;User ID=;Password=**;" providerName="System.Data.SqlClient" />

Answer 5

That MDF file is a SQL Server database. All SQL Server versions (except CE) use the same file format, so the MDF can be opened by any other SQL Server using sp_attach_db or SSMS, see How to: Attach a Database (SQL Server Management Studio).. Once attached to your SQL Server, SSMS can script out the entire database, see How to: Generate a Script (SQL Server Management Studio). Also this can be done programatically, using the SMO Scripter object. The SSMS script will contain all the table, index, procedure and every other objects in the database. There is also an Import and Export Wizard in SSMS and you can use this to export your tables content (any data) into a CSV file and then import this CSV file into your hosted database, but that import/export wizard is fairly complex (it uses SSIS).

Another alternative for the data export is to use bcp utility, see Bulk Import and Bulk Export Operations.

Tehcnically you can doo all these operations using the SQL Server Express instance in your VS environment, using command line tools like sqlcmd and bcp, and perhaps writing your own SMO Scripter application to export the database schema. However, it requires a certain level of experience. It will be tonnes easier to have access to a fully fledged Managent Studio to preare all the data to be imported at your hosting site.

Don't forget to check with your provider if they allow to restore or attach your own database, then you may get away easy with just a file copy to the provider.

Note that your MDF file will only be possible to attach it to a SQL Server of at least the same version as the one you have created with. You're VS environment contains a SQL Server, probably named SQLEXPRESS, and you must check exactly what version it is. Servers of an earlier build than your VS SQL instance version will refuse to attach your MDF file, since its a format they do no recognize. Follow the steps in KB 321185 to determine what SQL Server version your VS environment has.

Update

I wasn't aware VS has an export wizard. Just follow the steps Gary mentions in the link he provides.