SQl Express or Everywhere

Gilad Kapelushnik

Hi I have an SQL database under a MS2005 SQL Server. This is a ~200MB databse that grows from time to time, only one person (Administrator) adds\removes data from this database. On the other side I have a client application that uses this database. At the moment the the SQL database is locked and has stored procedures as an "API". Since allot of users accessing the data is CPU consuming and takes allot of bandwidth I would like to have the database localy on the clinet side, *locked*, enabeling only the execution of the stored procedures with an option to sync' with the main database on the SQL server. The end user can't add\remove items from the main\local database nor does he have the login\password for his local database. I started looking into this and found SQL express and SQL mobile (everywhere). My quersion is, which of the above is more suited for my application ? Thanks, Gilad.

Colin Angus Mackay

Gilad Kapelushnik wrote:

My quersion is, which of the above is more suited for my application ?

I would say neither is suited to your application. However, without knowing more about your application I cannot say what is most suited to it. A recent example of a system I worked on benefitted from the SQL Server being upgraded to Enterprise Edition so it could take advantage of more memory on the server box. It was a database that was growing at a rate of ~1Gb per week (significantly larger than your database). You also mention bandwidth problems. Are you returning more data than necessary to your applications? Could you do more processing on the server?

Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog

Gilad Kapelushnik

Thank you for your quick answer. At the moment the database is ~200MB growing at a rate of 80k per day, thats about 30MB a year, not too much. 99.999% of the data never changes so I would like the history data to stay on the client side. The client gets all the data it needs and just the data it needs, nothing extra. This is very optimized. I have many users accessing the database at once and would like only to send them the 80k daily "update data" rather then all the data. Gilad.

Mike Dimmick

Right now, as far as I'm aware, SQL Everywhere is only a CTP, I don't think it's been released yet. SQL Everywhere doesn't have per-user or per-object security. It also doesn't have stored procedures. You'll need to use SQL Express. Given the size of the database I'd look at SQL Express. You'd probably want to use transactional replication so that the size of updates is small. You would still have to connect to the master database to execute any stored procedures that change data. You have to have some kind of credentials to log into any SQL Express database. I presume that you mean that you wouldn't give out the system administrator password. That's sensible. I would grant the \Users Windows login access to the database server and map it to a user in the 'public' role in the database, then use Windows Authentication to connect to the database - no separate password needed. I've not done this but I know that SQL Express can subscribe to replication publications. Stability. What an interesting concept. -- Chris Maunder

Gilad Kapelushnik

Hi Mike, Thanks allot, you really hit the nail on its head. I guess SQL everywhere\mobile isn't the solution for my situation. Is SQL Express "Deployable" ? The client application is a "Click-One" App. Do I need a special liciense in order to use it on the client end ? Thanks Gilad.

Not Active

You can distribute SQL Express to the client. Think of it as the replacement for MSDE, which it really is. You would probably need to have the client install SQL Express before your application, or have some code that runs at startup of your app to verify and install SQL Express prior to start up.