sql express, workgroup or standard 3

[robsuttonjr]

I am helping a retail store chain with 16 stores in setting up a data warehouse. I am certain we will use the enterprise version at the home office for the central database. My question is on whether to use sql server express, workgroup or standard at the actual stores. Right now the biggest store has about 300 meg of data files in dbf format. I plan to have sql server at the store which will pull data from dbf into sql then transmit to central enterprise sq server. Eventually this retail chain could have over 100 stores. Which sql level should I recommend and why?

Regards,

Rob

 

[ptheriault]

The benefit of using SQL Express is that it is free.
The down side is that the max db size is only 4gb and the lack of features like no SQL Agent to schedule jobs, 1gb of memory, no full text search and no Log shipping or mirroring.
It does support Transactional and merge replication.

With Workgroup you get more features like support for 2 multi core CPU's, 3GB of memory, Log Shipping, full text search, SQL agent.

Standard will support up to 4 multi core CPU's, memory will be limited to the OS and it will support db mirroring and Cluster.

For a complete list of features for each edition you can check out this link.

http://www.microsoft.com/sql/prodinfo/features/default.mspx

You will need to determine your business needs at the store to help you make this choice. Do you need to run nightly jobs, what will be your backup strategy, how do you plan on moving data around.

If price is not an factor he I would always choose the version with the most options because you never know what someone might ask for in the future.

- Paul
- Database performance looks fine, it must be the Network!

 

[onpnt]

It depends on the server hardware as much as anything else. Do you have that information? OS, Proc's, memory etc..

From what you've said though and the replication factors I would go with standard at the stores if the $$ was there because I think the hardware would dictate that. Express will work from the way it sounds sense they will seemingly only be subscribers to the main office though but limited on the hardware. What kind of disaster recovery model are you thinking of? Are you looking at failing over to one of the stores in case of a disaster?

You are correct that you'll need Enterprise at the home office.

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[onpnt]

correction: I meant workgroup over standard at the stores.

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[mrdenny]

The cost difference between workgroup and standard is minimal (when you start talking about 100 stores it's probably not so minimal).

It's basically going to come down to if Workgroup supports the features that you need get Workgroup. I would assume that the server at the stores will just be a beefy workstation and not a true server?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com