SQL Express or SQL Compact

Posted on 2011-05-05
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
    LVL 25

    Expert Comment

    by:Lee Savidge
    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.

    LVL 1

    Author Comment

    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

    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.
    LVL 1

    Author Comment

    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
    It is good to go with SQL Express.
    LVL 1

    Author Comment

    Great! I was thinking about this, and had at least concluded that if nothing else, I could get the 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??
    LVL 1

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now