SQL Express or SQL Compact

Posted on 2011-05-05
Medium Priority
Last Modified: 2012-05-11
I am use to at work using SQL Enterprise.. I have been asked to create a small project for a volunteer program for a yearly course they hold that only has maybe 40 participants & about 12 staff members..

So being of little cash, SQL Enterprise is out of the question.. But they would like it to be on the internet, so the staff can work on it from their homes. But can be pulled off & stand-alone when need be.. (the course itself is run in the woodlands where there is no hookup & you have to climb to a mountain peak to pick up cell phone signals..).. Then of course when done with the course, the data would need to be uploaded to the internet again

From reading Compact is for mobile devices and to sync up, We are designing for an internet and laptops (which will need to work off-line on occasion).. So maybe Express is what I want??

But I do want to sort of sync up.. I mean when the user goes off line they need at least some of the database on the internet to download to their database on a stand-alone version on their laptop. Then when they get back on-line, they need to upload modified records back to the internet database..

I do hope I can go with SQL Express, or one of you can point me on the right track on how to use Compact 3.5.. Because SQL Express has queries and is very similar to me to working with SQL Enterprise, especially when using in Visual studio (although I know deployment is a little more quirky, I am fine with how to create a msi package..) I can find little to direct me on how to code using the Compact 3.5.. How to modify records with the Database programically, let alone how to transfer data from one Database to another.. I got stuck just trying to move a talbe with two image fields from SQL Enterprise to the Compact.. I read that I could just put them in the VS Server Explorer and copy and paste between the two, this worked but for the image fields, and is of no use when building a contained package for users even if I was successful..
Question by:moosetracker
  • 4
  • 2
LVL 25

Expert Comment

by:Lee Savidge
ID: 35698560
You sound like you need SQL Express. SQL Express is the basic database engine. You can download and install the Management Studio to do your development. Compact is aimed at the mobile market.



Author Comment

ID: 35698703
So keeping the Internet DB & off-line personal DB's in sync, is not really what they mean by sync.?..  I can push & pull data with SQL Express?
LVL 25

Accepted Solution

Lee Savidge earned 750 total points
ID: 35698884
If you are using a database backend for a web based application, there is no syncing needed unless you would like client machines to be able to work offline and then sync later. This is not likely to be the case. If you're just wanting to use a database to store data and push things back and forth between the database and the user over the web then SQL Express is all you need.
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


Author Comment

ID: 35699130
No we do need to work off-line.. The course is held in a remote area, so over the year they want to work over the internet so people from their homes can access & modify a common database. But during the course, they need to download the data, run it in a self-contained process.. I would like to say that it is then read only, but unfortunately not.. The participants will be grouped and their groups recorded, and they may buy merchandise, and that needs to be recorded.. So when you return from the course the changes need to be uploaded back to the internet database..

That's my dilema.. We are not mobil devices, but can SQL express communicate with another SQL express to up-load & down-load information?? Or is this the process of syncing that is only accomplished with the Compact Edition..

I would prefer to stay with SE as it is more familure. Although I hear that Compact in VS 2010 now allows query capabilities (which will help)

If I need the compact Edition, can anyone direct me to good tutorials on how it up-loads / down-loads and can be programmed in C# code to read and modify records.. Books, on-line tutorials.. Because except for promoting it, I find absolutely nothing on how to utilize in with Visual Studio..
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 750 total points
ID: 35704414
It is good to go with SQL Express.

Author Comment

ID: 35704946
Great! I was thinking about this, and had at least concluded that if nothing else, I could get the Asp.net program on the web site to create Excel tables that it would let the user save on their desktops, then the user could use the windows form application to suck the data into the database there..(Which would then work in the reverse direction to upload)

I doubt there is a way for the internet process to link up with the database on the users pc, because of all the limits placed on coding to try to keep people from writing virus's (of course those seem to be the only people figuring out the workaround)...

Is there any special way to point the stand-alone Windows form program to be able to read the SQL express on the internet directly?  Or is my idea of temporarily offloading the data to Excel the best way to push the data??

Author Closing Comment

ID: 35715191

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question