Posted by: Sahil Malik [MVP], on 24 Apr 2006 | View original

Lately Ive been flying a lot. Last to last week, I flew from DC to Denver to Colorado Springs to Fort Collins to Denver to Chicago to Grand Rapids to Chicago to Cedar Rapids to Chicago to back to DC. And this weekend, Im on my way again via Los Angeles to San Jose to represent telerik at the experts panel at Syscon.AjaxSeminar (I will be broadcast live on the internet on April 24 (today) from 6:30 to 7:30 PM PST at www.ajaxseminar.com).

As an upshot, I am so sick and fedup of flying in these super cramped seats that I am going to consciously resist flying until TechED in June, and maybe even a bit after that.
 
But there is a good side to flying. I get pockets of time between landing and takeoff, and between takeoff and landing, with no internet access, and at times no cellphone even. Great !! That means I have time to talk about what Ive been meaning to for a while.

I am also in the process of writing my website at www.winsmarts.com , which I am using as the guinea pig for a series of articles in www.code-magazine.com to demonstrate the ASP.NET 2.0 WebParts framework, and Sharepoint Portal Server 2007. I am extremely excited about how that website is turning out, and here is a early preview screenshot

That website produces a framework to setup your site in a quick point and click fashion. As community contribution, you are welcome to download the code, modify it, whatever you wish (disclaimer: I am not responsible for what you do with the code).

So, I wrote that using the WebParts framework, and I leveraged the inbuilt membership API. Bingo stuck with SQLExpress, and now I gotta move it to a hosted environment which www.bitshop.com was kind enough to provide to me.

So here goes, coming to the point. How to move ASPNETDB.MDF to a hosted environment.
The answer is, It really depends on what your hosted environment looks like. I am going to assume the minimum common denominator that you have a blank database, that you can perform TSQL actions against. So you can execute DDL (Create Table etc.) , DML (Insert into etc.) etc.

So Question #1 - Does your hosted environment support SQLExpress?

Yes Then your setup is easy (well maybe). The problem is, moving a SQLExpress DB from the cutsie tiny system tray webserver, to IIS is always a pain in the donkey. And for various other reasons I mentioned, I personally dont recommend using SQLExpress in production.
No Then you will have to migrate the SQLExpress DB from SQLExpress to a full fledged SQL Server. So the question now boils down to

How do I move my ASPNETDB to a real SQL Server?

So your hosting provider may allow you to run a Create Database statement and create a brand new database for ASPNETDB purposes. That is not necessary frankly speaking, but if you wanna do that, go crazy. This in fact may even be necessary if you want to keep your aspnet tables away form business tables. IMO that is again unnecessary, but either way, it really doesnt matter. I passionately hate the idea of splitting up my database into multiple databases Just to keep things clean. BULLSHIT!! It provides you with no extra benefit, but a big downside of distributed transactions down the road.

For now, let us just say you are moving your local ASPNETDB to a remote DB called remotedb.
You cant unfortunately do backup/restore or attach/detach in most scenarios, because SQL Server Management Studio, while connected to a remote database, will read that machines filesystem something you may not have access to. Frankly, you may not be able to connect to the remote database anyway. So that option wont work.

So the first thing you need to do is, setup the schema of ASPNETDB in remotedb. For that you need a TSQL script. You could use aspnet_regsql sqlexportonly to create the TSQL for you, but I bet ya, 99% of the times that script wont work in hosted environments, because it needs access to the master database, or the Create database statement, or something crazy like that.
What I generally do is, I install both the full fledged version of SQL Server on my development desktop, and SQLExpress. But I install SQLExpress, as a named instance, so I can start SQL Server Management Studio, and connect to SQLExpress using (local)\SQLExpress. Once Im in, I would see the aspnetdb on the list of databases. If you cant see aspnetdb in the list of your databases, you will need to attach it. That is rather easy, Right click on databases, select attach, Then add both the MDF and LDFs (you may need to copy them to an alternate location from your app_data directory if they are already attached to your user instance). And bingo youre all set. (BTW, dont be shocked if the database isnt called aspnetdb.mdf, it may be identified by its fullpath, or some machine generated name).

Then what you need to do is, right click on aspnetdb, go to Tasks -> Generate Scripts. That pops up a wizard. Under screen #2 of the wizard, select Script All Objects in the Database. Then in the next screen, click on Finish. This generates a script. You can download the script from here.

Then run this script in remotedb. This can be done through SMO, regular ADO.NET, or if your hosting server allows SSMS. Great, this would setup the schema of your aspnetdb, but what about the data?

Well, some of the data can be setup using code, but some of it, you have to setup yourself. The easiest way to do that would be to setup a linked server but chances are your hosting service uses the locked down, default SQL Server settings, where remote connections are not allowed. In that scenario, you need to move the data from the aspnet_SchemaVersions table yourself. This can be done using Copy Paste, or Insert Statements, or any other means.

Once this data is setup, there are then two ways to setup the rest of the data.

a) Copy it just like above.
b) Use the WAT (Website Administration Tool) , or equivalent ASP.NET code.

a is simple, So let us discuss option b.

The first thing you need to do is, tell your website that dont bother connecting to your local SQL Express database anymore instead connect to the remote database. This can be done by modifying the connectionStrings section of your web.config.

First add a <clear/> element, and then add a new connection string with name = LocalSqlServer to your remote database. (This assuming that you can connect to the remote database from your desktop). If you cannot connect to the remote db directly, then it may be a good idea to setup the aspnetdb schema locally first, and first setup the data on your desktop, and then copy it over manually.

With that much setup, run the website locally, and then run the WAT (Website administration tool) go ahead setup the users, and any other configuration that you may need to do.
If you are doing this connected to a local database, you will next need to copy over the data from your local tables, to the remote database. You must copy the data in the following sequence

1. Aspnet_WebEvent_Events
2. Aspnet_Applications
3. Aspnet_Roles
4. Aspnet_Users
5. Aspnet_UsersInRoles
6. Aspnet_Profile
7. Aspnet_Membership
8. Aspnet_Paths
9. Aspnet_personalizationAllusers
10. Aspnet_PersonalizationPerUser

Awesome !! Youre almost done.

Now in the remote/hosted servers web.config, add the <clear/> element to the connectionStrings section, and add a new Connection String with name = LocalSqlServer that points to remotedb.

Thats it !! Youre done!! Hooray !!

PS: A simple tool could be written to do the above. Unfortunately, that would require you to setup an asmx or something like that at the remotedb. While that can definitely be done, and if that is the route you choose to take, make sure you remove the asmx once youre done moving the database, otherwise itd be a security risk.

Category: #SQL | Other Posts: View all posts by this blogger | Login to add this post to your profile favorites Report as irrelevant |