Deploying SQL CE 4.0 in a Hosted Environment

by Doug 11. July 2011 19:32



Ok, not any "Hosted Environment" but specifically the hosting environment over at www.discountasp.net. If your app is hosted elsewhere, you still might be interested in this. The steps may be similar with your hosting company as well.

Note: As of this blog posting SQL CE 4.0 has been released to the public for 6 months - since January 2011.

Truthfully, I'm not much of an expert on SQL CE. (Compact Edition). The little I know is just the fact that it's embedded inside devices other than traditional computers to handle data duties using a schema and query language based on it's biggest brother: SQL Server. Because it's free from needing to run inside its own service or on its own machine, means that you can run it right along side all your code inside a phone, printer, mp3 player or other device.

However, with the release of the latest version (4.0), Microsoft added the ability of embedding it inside traditional web applications as well. CE has traditionally been a free Microsoft product and suddenly I was very interested in having a free & simple SQL database run in my web apps.

But I don't run my own web server, nor have a "Dedicated" hosting account. I have a shared hosting account that caters to ASP.NET web applications at DiscountASP.Net. If you're interested in a place to deploy your web apps, please visit them. ( www.discountasp.net) They are very good for many reasons. Including their support of CE.

( They don't have to support a free version SQL. They'd rather you pay for their offerings of the full version of SQL. But in reality, sometimes SQL Server is overkill. Sometimes you just need a bit of data, or a proof of concept, etc. So, kudos to them for understanding this from a developers point of view. )

The technical blog posts I was reading all had examples of CE deployment based on access to your own web server. Something got under my skin and had to chase down a process to see if I could make this work in my DiscountAsp.net hosted environment. I spent an afternoon fiddling with MSDeploy, Visual Studio 2010 Publish Settings and "My Account" settings at DiscountAsp.Net.

In the end: Success! After getting it running, I've been continually developing my app on top of it. And something else: Once deployed, you don't have to go through the entire process to upload small changes to your server. Simply use your favorite FTP program to punt new changes. That includes the SQL CE file (.sdf) too!

Remember that CE has serious limitations. No support for Functions. No support for Stored Procedures. It seems like it runs a little slow if you've got complicated queries. OK, well this all makes sense. Not much different than an XML blob wearing a SQL costume.

NOTE: Technical Evangelist Mark Wisecarver is working on a webcast based on these steps and will go into much more depth on the differences between SQL CE and SQL Server. Come 'on back for the link, it'll be [ here ] when the webcast is complete.

UPDATE: Mark has posted his Webcast HERE: http://www.dasptv.com/avc-view.aspx?videoid=39

For those of you who are pressed to carry on, here are the steps:


VIA DiscountASP.Net Control Panel

  • Login to your DiscountASP.net account
  • Go to IIS Tools
  • Go to MS IIS Manager Tab
  • Enable Primary Account User Access - (and click Update)
  • Click "Web Deployment Tool" (and click Update)
  • Go to Account Info/Edit
  • Note Server name (example web101)


VIA Visual Studio 2010

Download and install Microsoft SQL CE 4.0 here: http://www.microsoft.com/download/en/details.aspx?id=17876

  • File > New > Project > ASP.Net Web Application (Note: This is the template web app that comes with the ASPNETDB.MDF database already included - used for user login. Using the sequence listed here will NOT enable this .MDF database file to run on DiscountASP.Net servers - and the login feature of this template will not work using this database - it must use a regular sql server db)
  • In App_Data file, (this is where the .mdf db resides) Add > New Item > Sql Server Compact 4.0 Local Database

  • Add records to this database

  • Add connection string to web.config - Mine looks like :
    <add name="DataConnection" connectionString="Data Source=|DataDirectory|\Data.sdf" providerName="System.Data.SqlServerCe.4.0"/> Where |DataDirectory| will automatically be changed to "App_Data" upon using MSDeploy (later in this sequence)

  • Add some code to interact with the data on your default.aspx page

  • Run app locally to make sure it's doing what it's supposed to

  • Project > Add Deployable Dependencies (let VS go and add a bunch of folders with .dll files)

  • Project > Package/Publish Settings - your app properties pages opens defaulted to the Package/Publish Web tab

  • In the "Items to Deploy(Applies to All Deployment Methods)" section - set to: All files in this project

  • Check = Exclude generated debug symbols

  • unCheck = Exclude files from the App_Data Folder

  • In the "Items to Deploy(Web only) section - leave all boxes UnChecked

  • In the "Web Deployment Package Settings" section:

  • Check = "create deployment package as .zip file"

  • In the "IIS Web site/application name" section set to: Default Web Site/AppName (replace AppName with the name of your web application - no not change "Default Web Site")

  • Go to the Package/Publish SQL TAB

  • Click the "Import from Web.Config" button

  • Check the connection string that appears in the list - that goes with your SQL CE database

  • Use the Remove button to remove all other connection strings that you won't use on your deployed app (remember if the connection string to the ASPNETDB.MDF file is on the list, it must be removed. The login feature will not work using this mdf file on DASP servers...)

  • SAVE YOUR SETTINGS

  • Build > Publish (app Name)

  • The "Publish dialog shows up

  • Publish Method = Web Deploy

  • Service URL = https://[yourWebServerHere].discountasp.net:8172/MsDeploy.axd

  • Site/Application = yourdomain.com/yourAppName

  • Check = Mark as IIS Application on destination

  • Check = Leave extra files on server (Not sure if this makes any difference...)

  • Check = Allow untrusted certificate
  • Enter DASP UserName - the one you use to login to DiscountAsp.Net

  • Enter DASP Password - the one you use to login to DiscountASP.Net

  • Click Publish

  • Check the live version of your site.


So anyway, I've NOT tested this method over and over. But have deployed a simple app, it worked, then made changes to the code, re-deployed and the changes made it through the deployment process. Remember you can also FTP small changes once you've done the first successful deployment.


Let me know if this works for you!

Those Were The Days

by Doug 9. July 2011 17:42

I'm not sure why, but I had these lyrics tucked deep in my C:Drive and stumbled upon them just today. Well, they just made me chuckle.

Written in 1971 as the theme song to "All in the Family", you can instantly recognize its timelessness. After 40 years - and beyond I'm sure - these words will make you ponder your life when times were different. Only those of us of a certain age will understand these lyrics. So if you don't get it that's ok. Go experience life and come back after you've paid taxes, had a few kids, bought a home or have survived any number of adult/grownup stuff in general. That's how you'll get to enjoy such insight.

Can't you just hear the voices of Archie and Edith?



"Those Were The Days" (c)1971 by Lee Adams and Charles Strouse

Boy, the way Glenn Miller played. Songs that made the Hit Parade
Guys like us, we had it made. Those were the days.

Didn't need no welfare state. Everybody pulled his weight.
Gee, our old LaSalle ran great. Those were the days.

And you know who you were then. Girls were girls and men were men.
Mister, we could use a man like Herbert Hoover again.

People seemed to be content. Fifty dollars paid the rent.
Freaks were in a circus tent. Those were the days.

Take a little Sunday spin, go to watch the Dodgers win.
Have yourself a dandy day that cost you under a fin.

Hair was short and skirts were long. Kate Smith really sold a song.
I don't know just what went wrong. Those Were The Days.

Archie and Edith

Office Porch Update

by Doug 1. July 2011 20:02

 

For the most part, this project is complete. All major aspects are done. What's left are things like wiring up the exterior lamp, some sun screens and a more formal temporary set of steps (until we get the stone ones put in). I'm glad the bulk of the work is over. My patience had run shallow on this particular project, which I didn't expect. Could be I'm ready to start handing off future projects to hired help. We'll see.

 

Anyway, below is a series of pictures that document the progress:


The entire garage was painted first. The ledger boards got coated too.


Roughing in the understructure. You can see the shape and size of what's coming.


Floor joists and roof joists in place. Roofing material and all related rigging in place. Two coats of paint. It took a long time to get to this point.


Decking boards in place! The border pieces were very time consuming to put in.


Once the borders are done, the other pieces go much faster. Yet there are dozens of screws to sink for each plank.


We really love this new space. Very comfortable spot to get away from the office, read or take a phone call. Lot's of flowers and other decoration coming from Virginia, she's taking over from here :)


Doug's Latest Tweets

Doug Who?

Hey, Doug here.  Thanks for visiting "A Doug's Life". 

 

I enjoy packaging my thoughts, pictures, video clips and other fragments into what hopefully makes up some interesting stuff to share.  

 

I've checked the list that gives you a better idea about me!

 

Love Web Technology
Sunday is for Pro Football
Hike or bike on trail most mornings
Halloween Rocks!
Chase the latest electronic gadgets
Married
(Happily Married)
Drama
Simplicity

 

My wife and I live in the shadow of Mt Lukens on the edge of the Angeles National Forest:

 

Tujunga, CA

More Stuff