database with membership/roles

I have a database.mdf and associated web pages that worked fine on both my local server and the DASP server. I found that when I added the roles/membership on my local server, i could do this easily using the webConfiguration tools that came with VisualStudio 2008.When I ran these tools, I found that the tools automatically added a new local database called ASPNETDB.MDF. This new database had all the roles and membership stuff that you would expectand they were set up in normal SQL tables that I could see locally. If you just try to load this new database to DASPwith two .mdf files (changing the connections appropriately in web.config) then nothing works right.

Next i tried running through the procedures in kb.discountasp.net/article.aspx?id=10413. However, these didnt seem to work either. Finally I restarted the development from scratch. It seems that when I ran the webDevelopment tool with the connections pointingto my local server that this was a mistake. I couldnt seem to overcome this. If I used ONLY the aspnet_regsql.exe as explained in kb.discountasp.net/article.aspx?id=10413, then i could get it all to work. The missing clue in the knowledgebase articleis: Dont use the local webConfiguration on your localsystem before going through the detailed steps in kb.discountasp.net/article.aspx?id=10413!

I also found out a couple of things that happens when you run aspnet_regsql.exe. The two databases (tables/data/schema) database.mdf and ASPNETDB.MDF are merged into a single database at the DASP site. You cant actually see this (or at least i couldnt) but indeed this is what happens. I know this because when youbuild a new page locallyand access the original database.mdf(using the connections pointing to DASP) -- you can see the table structure that contains the membership/roles tables i had earlier seen on my local system. So on DASP there is actually a new table structure.I am not an expert on servers so this is all a bit empirical!!

But now there seems to be a problem. I got all working and was very happy with the excellent perfornamce. I found that I had to develop my database with all the connections pointing to DASP -- this slows things down but Iwasnt able to simply change my connections to point to my local SQLSERVER and develop on my local database. So it seems once you upload to the DASP, you seem to be stuck with thoseconnections and the slower access. I'm sure there is a better way but i couldnt find it nor a discussion onthe web about this point.

Now for the problem. Imade a change tomy table structureon my local database.mdf. (I dont know how to work with the table structure at the DASP site.) Then i did a new full upload of the database.mdf to DASP site. Now nothing worked!! It seems I hadto redo the aspnet_regsql.exe step to get the roles/membership back. After seeing this it seems logical since aspnet_reqsql.exe merged the tables in the first place. I had destroyed this merger when i re-uploaded the database.mdf again. So rerunning aspnet_regsql.exe then fixes everything again -- right? Well almost. Now, of course, I have lost all my membership/roles i had originally set up using the webConfiguration tool. Uggh -- so I had to reload all this stuff. Finally, I was able to go the complete cycle of creating a local database, uploading to DASP, adding membership/roles. Plus I was able to modify the database locally and reload it -- but alas iclobbered all those tables that were originall in ASPNETDB.MDF.

I am very certain i have done this the hard way. However, none of the several books i have on asp.net 2.0, the various web videos, and the DASP/asp.net forum discussions have really tied up this process nicely. Hopefully some smart folk out there will have a better discussion of all this and can point me to a less tangled process for using a spiral development process for web development/deployment.

---Jim

 

I'm working on a set of videos to help DASP customers.
If you can ZIP or RAR those mdf files I'd appreciate it and I'm sure others will too.
I'll use them to show how a local copy of SQL Server can be used to merge the databases.
If you find this to be a workable solution please e-mail the file to me at [email protected]
Salute,
Mark

 

A videowould be really helpful. i see a lot of people doing just what i'm doing and trying to replace management spreadsheets placed on sharepoint with a real web-enabled datadriven site. However, we all want to secure our database with the roles/membership functionality so easily accessed by asp. so the idea of developing a local functionality and then porting to the likes of DASP will be a major direction for business apps. But i wont use DASP unless i can secure my data. MS is heavily marketing this approach with VS2008 and asp.net 3.0 --- but if we all have the wicked learning path i have travled over the last month -- then the MS hype wont sell.


The files you suggested i send seem to be HUGE. 10MB in my case -- and its just stuff MS dumped in my local folder when i added the roles/membership. Basically i opened a new web site using VS2008 (the new free-for-90-days hyped MS asp.net web development stuff). one click creates a dummy site with default.aspx page. I went to web configuration and opened the security link and added users/roles, etc. Then when I look atmy localwebsite using the Server view, i see a database called "ASPNETDB.MDF" -- this has a SQL 2005 set of tables and relationships that MS has set up for me -- i didnt do anything except do the security stuff in web condigurationwizard. So you can do this and see thetable structure MS set up for us!! You can also poke through the tables using the VS2008 tools and see the users/roles that you set up. These tables seem to be just standard SQL2005 tables that i could modify if i wanted (havent tried this).


When you use aspnet_regsql.exe -- then this seems to merge another independent database with the ASPNETDB.MDF --- i believe what really happens here takes more SQL Server insight that i have. this is what is discussed in the wiseguys article posted on this furum somewhere.


But the overall bottom line is that this is really confusing trying to keep track of what's happening locally and what happens on the DASP site and how to maintain all this stuff in a spiral development environment. I want to mod/update the database but when things are happening behind the scenes with the obsure well-hiddenaspnet_regsql.exe app -- i feel i have totally lost control.


I'm not sure who has the obligation to explain all this to us -- the web hosters (like DASP)or MS -- but if you want all of us sharepoint info managers to switch to asp.net -- then its gotta be a bit simpler!! What i told my staff is to toss your spreadsheets and learn asp.net --- for $200 you can get your own web site and database(e.g., from DASP) that all the world can use and see/manage/interact with our information. So here's your challenge. take a two-table database (e.g., table of products and product type) -- add the table and a few sample records. Add a single page with gridview to display and allow CRUD operations on the database --- just exercise the simple MS-supplied gridview control. This is well demoed by the entry-level MS asp.net videos. Next add the membership/roles as also well demoed by the MS videos. setting this up after viewing a few of the MS videos takes about 1/2 hr.


Next tell us how to port this trivial app to DASP using the MS-supplied copy tool and get this running when i access my site there from the web. Using aspnet_regsql.exe is OK but this really seems more complex that what MS and DASPis trying to sell here!!Next show me how i can modify/develop my databaselocally --keeping the hostedDASP mdf and my local mdf synched. You could show this by just adding a column to the local database.mdf and then rehosting the modified website.


BTW -- i still have no clue how to modify my data.mdf table structure (e.g., add a column) and reload my DASP site w/o losing all the data in my membership/roles tables!! So for now, im stuck with one passworded user so i dont have to reset much when i modify the database. Any assistance you can provide with this sore point would be helpful.


---Jim

 

I can definitely make this easier for a lot of the people you are referring to and your input is very helpful.

Previously for companies like Siemens I used SQL Server "hands on".
My approach for something like this is often very different nowadays.

I'm a big fan of SQL Server, especially because I have used so many other DB engines.
In 1999 I used SQL Server version 7 and then fell in love with SQL Server 2000.

Because there are so many methods that can be used I can only offer one solution, from experience.
Even before I create the videos I know they won't serve everyone.

One of the problems for many of the APS.NET developers I help is they don't have a local copy of SQL Server.
For them the Aspnet_regsql tool is theirdefault choice,however I don't use it.
The ASP.NET team has done agreat job on itbut I still feel more comfortable doing everything from within SQL Server.
btw, here's the info page:
http://msdn2.microsoft.com/en-us/library/x28wfk74.aspx

I suppose it would make the most sense in this Videos series to include methods with Aspnet_regsql.
No sense in my being stubborn.
Salute,
Mark

 

i think the big problem is that a large bunch of us new web-enabled uses want to do things we never did before -- like toss our spreadsheets, file/IO, XMLand go to asp with a full-fledged SQL database. but the fast learning path is the MS videos -- so that's all we know. But when we get into trouble we ask the "experts" and they have this rich history with stuff like asp, ado, php and html and stuff we C++ guys think is for the programmers who took the "business" option. Now we need this stuff to exist in todays world. But when weask the experts we get a lot of jibberish that MS forgot to put in the videos.

--Jim

 

Good point Jim.
Fortunately Microsoft is going through a major, read major, evolution this year.

 

I had a breakthrough today on my ability tocontinue development of my database without haveing to reload the database schema to DASP when I updated it. Originally, I developed my database on my local computer with a database.mdf and then added the membership/roles. I uploaded everything to DASP using the website copy app from VS2008.I pointed my connection strings to DASP and the data manipultation pages worked just fine as I continued my development locally -- with an occasional copy website. However, If I modified my table structure locally, i had to reload the database and thus lost all my membership/roles stuff. Basically I was using the DASP database (per the connection strings) but the visible tables on my website project were actually on my local server. To modify them and have them be used, I had to reload the database.


The resolution is drop dead easy! Just view the website in "server view" using VS2008 and right clickon the database.mdf. Pull down to add new server connectionand aha -- it lets you point your local database to the DASP site. Therefore all the data described in the database is at DASP and the various table schema definitions refer to the DASP site not your local server. If you changethe schemausing VS2008, it actually changes the table structure/definition at DASP. Also, you actually see locally, the various tables associated with the ASPNETDB.MDF functionality I have been looking for! This is where the membership/roles stuff is physically stored.


--jim

 

Thanks for sharing bro.

 

I miss QA too.
One thing I've been doing is in the SQL 2005 Management area just script any object.
i.e. Right-click on a table for example and send it to a new window as Create.

Then just clear the SQL in that window and go to town with a light version of QA.
Not exactly the same but you can still work against objects and parse queries.
Salute,
Mark

 

Jek, I don't know how to put this but ....

If you want to marry my daughter go ahead, take my dog whatever ....

Your 'breakthrough' has just short cut through a massive dev problem I have been armwrestling with. Yes of COURSE! point the dev IDE to the DASP database and forget about synchronising the schema, the data content for users/roles!

Nice one Jek, so obvious

By the way guys and gals - synchronising dbs is not so bad if you use a tool like SQL DELTA 4. My problems where not so much schema and data but in re-configuring the asp app to live off the real server rather than the local dev server.


Cheers

Mike

 

As a long time SQL guy it took a lot of convincing to get me to move my sql dev to visual studio. I don't like it all and I still miss query analyzer but when it works it works well. It really screams when you get the hang of building and deploying an entire project through it.


I have just gone through the same exercise you describe (I actually broke my perfectly fine, working site and had to recover it ).


Since I had to redo it I thought I'd like to use the sql express aspnetdb.mdf in the app_data folder for security and roles. Got it set up and working (found the same solution for seeing the database in VS you describe) and hated the method.


I have moved all my roles etc into (actually back into ... since that's what I broke) my primary DASP database. It seems quicker and when I bring down a backup to use locally I have the advantage of having all the roles and users update automatically.


Note sure you've considered this but I'd recommend it if you haven't.