Using MS SQL without installing it.

[stoymigo]

I'll be attempting this myself but asking here incase someone has gone through this, I'm creating a .net app
and connecting it to ms sql.
Am I right in saying that one can use SQL Compact edition, and avoiding installing MS SQL on a client's machine?
I'm looking to avoid installing the whole of ms sql, whether it is express or not.

Thx

[Hamster]

Yes. All that will change is your connection string when you go live (and that you set in the app.config).

EDIT: Wait, do you want to use CE once live?

[stoymigo]

Not sure what you mean but the idea is to use it on one application only, if there are more than one pcs we'll be using it on a network then I'd be willing to install ms sql on the machine that would be the server.

[PsyWulf]

You want to host an MS SQL database without the MS SQL server software? What

[Hamster]

Then yes. The only difference will be the connection string.

Note though, CE has some limitations like the total file size etc. but it doesn't sound like you are writing the kind of app that will reach these limits or make use of the advanced features of MSSQL any way.

You are probably going to need to distribute the CE libraries with your app.

[xumwun]

Maybe what you need is SQLLite.
http://en.wikipedia.org/wiki/SQLLite

[garp]

Been down this route a few times. Somehow you always end up supporting the MSSQL deployment as much as the application. Eventually we settled on SQLLite as a really stable, easy to deploy and functional alternative. You can just change your connection string and *most* of the sql is the same - the best part is it requires no installation and exists solely in a single file on the client system so it's very portable and easy to manage and backup. Also v. fast.

[Hamster]

Maybe what you need is SQLLite.
http://en.wikipedia.org/wiki/SQLLite

And if he wants to start using one database over the network?

[garp]

Maybe what you need is SQLLite.
http://en.wikipedia.org/wiki/SQLLite

+1. Beat me to it by a few mins

[Nerfherder]

Not sure what you mean but the idea is to use it on one application only, if there are more than one pcs we'll be using it on a network then I'd be willing to install ms sql on the machine that would be the server.

The application ?

Do you mean the management studio ?

[garp]

And if he wants to start using one database over the network?

Create a multi-tiered app where the core functions are hosted in the "server" version via sockets or wcf. This is good app design anyway. Otherwise, for relatively low volumes you can open the sqlite db in a windows share but concurrency may be an issue.

[shogun]

If you want to use an in-process version of sql server... have a look at Sql Server LocalDB (new with 2012). It's the upgrade of Sql CE, and is designed for deployment on client machines without installation (like Sql CE or SQ Lite).

10GB database limit though. If you need more, then look at SQLite.

The bonus with using localDB vs SQLite is that your SQL will basically be the same as if you were connecting to the service hosted version, and working with things like Entity Framework don't require added plugins etc as far as I know.

[garp]

If you want to use an in-process version of sql server... have a look at Sql Server LocalDB (new with 2012).

AFAIK, LocalDb still runs in a separate SQLServer process requiring installation with admin rights??

[shogun]

AFAIK, LocalDb still runs in a separate SQLServer process requiring installation with admin rights??

Nicely spotted, you are correct (it does require admin to install, and runs as a separate process), but... the process is triggered when the application needs it and runs as an attached process to the main executable. There is a separate "child" process per application instance of your program.

I think this was their way to include the full Sql Server functionality (or more of it than Sql CE at least), without having to install the full version of sql server, or doing a complete re-write for an in-process (I was incorrect earlier... it's not in-process) db. You can include it as a pre-requisite to your application install from what I understand, so installation shouldn't be a headache.

If you require installation without admin rights... that might be a problem (well spotted).

"I'm looking to avoid installing the whole of ms sql, whether it is express or not." LocalDB has a 140MB footprint where Sql Server Compact has a 4MB footprint. A little larger, but if you include it as a pre-requisite, this surely shouldn't be an issue, considering the full-featured nature of the product.