Which version of SQL Server?

Question

Which version of SQL Server?

pstephen

SSC Rookie

Points: 26
More actions
May 3, 2010 at 3:47 am

#224090

Hi,
I work for a company of 20 people and have developed an Access 2003 db over the last 3 years which has now expanded to an extent where it's in use virtually all day by all employees. Needless to say it's rather stretched and we're encountering frequent lockouts and sporadic record corruption.
We plan to move to SQL server but there seem to be various different versions of both 2005 and 2008
Standard, Enterprise, Developer, Workgroup, ?
Being a small company we need to minimise the costs of purchasing the software. (the hardware is not an issue)
A few of us will be getting some training on SQL server and the cost for this is already in the budget.
My question is really which version of SQL server would suit us best if we want to migrate approx 80k records from Access 2003 and also allow for future expansion of the db by implimenting inventory management also (approx 150k line items)
I know it's probably a difficult question to answer, not knowing our business nor the current extent of our db but any pointers would help. 🙂
Ideally we're looking at spending as little as possible but allowing for future expansion 😛
Thanks
Paul

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I would use SQL Server 2008 R2 Express Edition. It's free and should do whatever you need. SQL Server Express supports 1 physical processor, 1 GB memory, and 10 GB storage. Should be enough for you. If you go beyond that limit, you can always buy Standard Edition.

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

Total agreement. You'll get enough of what you need in order to build out your initial system with Express. If you hit limits, mostly likely in connections, you take the exact same database, data, etc., and move it right over to Standard edition.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

I don't know if SQL 2008 R2 has changed something in connection limits for Express Edition.

The only thing I'm sure of is database size, that changed from 4 to 10 Gb.

-- Gianluca Sartori

Steve Jones - SSC Editor SSC Guru Points: 738632 More actions · Answer 4

Third vote for Express. If you go with SQL 2008 R2, you have an increased 10GB size limit.

You also have workgroup to go to if you are watching costs.

Editions of SQL Server

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

Gianluca Sartori (5/3/2010)
I don't know if SQL 2008 R2 has changed something in connection limits for Express Edition.
The only thing I'm sure of is database size, that changed from 4 to 10 Gb.

I thought there were connection limits on Express. I guess I'm wrong. Sorry about that.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Grant Fritchey (5/3/2010)
Gianluca Sartori (5/3/2010)
I don't know if SQL 2008 R2 has changed something in connection limits for Express Edition.
The only thing I'm sure of is database size, that changed from 4 to 10 Gb.
I thought there were connection limits on Express. I guess I'm wrong. Sorry about that.

I was confused too, Grant. I was sure MSDE 2000 had a 5 concurrent connections limit, but, after searching a bit, I found out that Express Editions (starting from SQL 2005) have no connections limit. This is what I found:

MSDN - SQL Server 2005 Express Edition Overview
The SQL engine supports 1 CPU, 1 GB RAM, and a 4 GB database size. [...] There is no hard-coded limit to the number of users that can attach to SQL Server Express but their CPU and memory limits impose practical limits on the number of users that can achieve acceptable response times from a SQL Server Express database.
SQL Server Express can install and run on multiprocessor machines, but only a single CPU is used at any time. [...] Features such as parallel query execution are not supported because of the single CPU limit.
The 1 GB RAM limit is the memory limit available for the buffer pool. [...]
The 4 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server.

-- Gianluca Sartori

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Although I agree the Express may be the way to go initially, it does have some other limitations. SQL Server Agent is one of them, it doesn't have it, so you will have to use Windows Task Manger and SQLCMD to run your backups of the database. I would suggest using Workgroup Edition on a dedicated server.

pstephen SSC Rookie Points: 26 More actions · Answer 8

Thanks for all the advice folks.

Looks like maybe Express is the best way to start off and we could upgrade later if required.

Excuse my ignorance though. Do I also need a separate front end application or does Express include a useable front end client? (I use Access2003 BE and Access runtime FE for the clients at the moment)

Thanks

Paul

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

SQL Server doesn't need a front-end, it just needs a client connectivity library to be installed.

If you are using a front end, probably you have Access forms, so you might have to code them from scratch in .NET.

-- Gianluca Sartori

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Gianluca Sartori (5/3/2010)
SQL Server doesn't need a front-end, it just needs a client connectivity library to be installed.
If you are using a front end, probably you have Access forms, so you might have to code them from scratch in .NET.

Nope, just need to move the current access forms to use ADP. That means that the data will be on the sql server and the forms still in access. There's an upgrade wizard. It's not perfect but it's a start.

Check out accessmonster.com You'll find all the help you need there for the venture.

pstephen SSC Rookie Points: 26 More actions · Answer 11

Thanks Everyone for your inputs.

Quick query though. If the Express version only supports single CPU will it actually still run on a dual or quad CPU server?

Thanks

Paul

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

Yes, it will run fine, but will never use parallel plans, because only one CPU will be used.

-- Gianluca Sartori

pstephen SSC Rookie Points: 26 More actions · Answer 13

pstephen

SSC Rookie

Points: 26

May 6, 2010 at 5:27 am

#1163079

Thanks 🙂

paul.knibbs SSCoach Points: 15320 More actions · Answer 14

Gianluca Sartori (5/3/2010)
I was confused too, Grant. I was sure MSDE 2000 had a 5 concurrent connections limit, but, after searching a bit, I found out that Express Editions (starting from SQL 2005) have no connections limit.

Presumably if you were running them on a non-server OS (XP, say) they'd be subject to the global 10 connection limit, though?