Which version of SQL Server Express 2014? 3

[CP60]

We want to allow some of our low budgeted small curstomers to use SQL Server Express or similar with our Visual Studio 32 bit applications.

These customers will only have about 1-2 Gb of data, and around 2-5 regular concurent users each with several hundred record writes per day, and maybe 25 other users with one to five record writes per day each.

They have Windows 2008 or 2012 R2 Servers where SQL Server Express will be installed and the client systems are any where from W7 to W10 where the application is installed. Some will also run the application over Terminal Server.

They will need normal recordset reads and writes and need to run bulk updates.

I would also like to be able to pass data between the database on the server and a local desktop SQL Server database.

(No reporting services are needed)

Will SQL Server Express 2014 serve this purpose fine, or should we use a different database like MySql or Firebird?

If SQL Server Express 2014 is a good choice, then which download to we need (the Management Studio is also requested)

MS SQL Server Express 2104 Download Link

I am thinking we will need the "ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe" download and either the "Express 32BIT\SQLEXPR_x86_ENU.exe" or the "Express 32BIT WoW64\SQLEXPR32_x86_ENU.exe" download

Thnak you!

 

[fredericofonseca]

Express will do fine for that volume.

for the server you should install the 64 bit version if your os is also 64 bit. e.g. file
"ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe"

for workstations that require SSMS to be installed the same file may eventually be used - try do run the setup and see if you can install only the tools out of it - as far as I know SSMS will still be 32 bit but easy to try it on your own. if not then use the 32 bit version of the same file.

For access to the database on the server should only require the SQL client which should be part of the OS or should be installed by whatever application you have.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[CP60]

Thanks fredericofonseca!

So, they could install the 64bit on the server, and the 32bit or 64 bit on the clients systems in order to also store data locally?

Can the native client also be used in code to transfer data between the server db and the local db?

 

[Olaf Doschke]

For what purpose do you need a local express installation? Do they work offline and sync data with the cetnral db afterwards?
Otherwise they only need client tools/drivers and can connect to the central DB.

I do data transfer via exporting data to dbf and merging with a local SQL Express, but that's a legacy app.

While it would be ideal to transfer data directly from server to client db, and solution to data syncing could be replication, this is not really covering usual needs of eg salesmen to have partial data (eg for clients they visit). I'd let your app work with two connections and pull data from server to a dataset, pull data from client (empty to start with) to a separate dataset, then copy over data in the two datasets (depending on what data should travel where) and finally commit datasets, so the data flow is central SQL<->dataset1<->dataset2<->local SQL.

The advantage over any more direct data transfers from server to server is this goes through your application business logic and you can process or limit data the way it's needed, set some check-out/check-in flags or whatever you need.

And about the bitness of the server: Even 32bit clients can connect to a 64bit installation, but of course you can't install 64bit SQL Server local on 32bit clients. The bitness just has to match when connecting via Shared Memory or pipes instead of TCP/IP, which only is possible at the client side anyway, so only 64bit clients having your 32bit software and a 64bit server are a problem, if not going the network tcp/ip stack.

Bye, Olaf.

 

[gmmastros]

I have approximately 200 customers. 90% of them are using express. We have more data and more volume than what you are describing. Express should work just fine for you.

You should know that there are limitations with the express version. Specifically, Express is limited to 10 GB per database and is also limited to a single CPU.

If you already have an application that works on SQL Server Standard, then it should be super easy to install and express instance to verify that it works. In my opinion, this is exactly why SQL Server is such a great choice in databases. It's free for small implementations and can scale up to accommodate really large implementations.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CP60]

Thanks Olaf.

1. Yes, I just tested that 32 bit SQL/64 Bit SQL and 32 bit App and all works fine.

2. Local DB: Yes, I also need to do this transfer of data to local mdf, (or even to a MDB) because of a legacy program which needs to get temporary data into a local database to be further processed by other means.

The sql commands for the data transfer needs to be handled off the connection object with in the application code.

Using two recordsets off of two connections and looping through records, as I thing you were suggesting, is not efficient enough (could be several hundred thousand records).


I will ask this in a separate thread.

 

[Olaf Doschke]

>Using two recordsets off of two connections ... is not efficient enough (could be several hundred thousand records).

I can imagine that this is not fast enough for such an amount of data, I'm doing this for tenthousands of records though, and it's pretty fast. Moving the data in memory almost takes no time.

Also the application logic does more than just moving data. The data moved to a local Express instance also is marked as checkedout, which by appliication business logic locks that data for other users, until the (most often) modified/extended data is checked in again.

I've seen your other thread, though, and wasn't aware an OLEDB provider could do such queries having a connection in the query itself. It still reads and writes the data, so the operations will take as long as going through two datsets, you go from one db to another via memory with such a query, too.

As you want local data in SQL Express, you don't want to pull data into an Access MDB file, though.

Bye, Olaf.