Upgrade

Question

Upgrade

sashafroyland

Grasshopper

Points: 14
More actions
August 2, 2009 at 6:07 pm

#224327

--deleted--

Viewing 5 posts - 1 through 5 (of 5 total)

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

Alex Popescu SSC Eights! Points: 979 More actions · Answer 1

sashafroyland (8/2/2009)
Hello,
Looking for some stats to compare MS Access with SQL Server Express. Stats to include would be INSERTs per second and complex query retrieval of record sets under 500 records but involving more then 5 table joins.
Also, can someone please let me know if Express utilized multiple cores on a single CPU?
I'm trying to justify an upgrade path from MS Access to SQL Server and the above stats would be much appreciated.
Cheers,
Sasha Froyland

Does this link helps with the comparation between Access and SQL Server Express ?

http://articles.techrepublic.com.com/5100-10878_11-6140859.html

The limitations of SQL Server Express Edition

1. Number of CPUs supported

SQL Server Express uses only one CPU at a time. It can be installed on a server with multiple CPUs, but it will use only one CPU at a time.

2. Maximum memory used

SQL Server Express uses a maximum of 1 GB memory for it's data buffer. So, if your server has several GB memory, SQL Server Express cannot use it.

4. Database size limit

Maximum database size is limited to 4 GB (log file size is not counted)

5. Profiler in SQL Server Express

Profiler tool is not included with SQL Server Express editions.

6. Job Scheduler

Job Scheduling service is not available with SQL Server Express (you can use .BAT files that can use SQLCMD command line utility)

7. Import/Export

Data import and export feature is not available with SQL Server Express.

More on in depth comparation between the features of different SQL server version, here is the Microsoft page :

http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

sashafroyland (8/2/2009)
Hello,
Looking for some stats to compare MS Access with SQL Server Express. Stats to include would be INSERTs per second and complex query retrieval of record sets under 500 records but involving more then 5 table joins.
Also, can someone please let me know if Express utilized multiple cores on a single CPU?
I'm trying to justify an upgrade path from MS Access to SQL Server and the above stats would be much appreciated.
Cheers,
Sasha Froyland

I can't think of a single case where Access could outperform SQL Server. Also you have a great deal more control over SQL than you do in Access.

Inserts/second are largely dependent on your hardware and especially your disk sub-system. This will be true for Access as well.

What is the problem you are trying to solve.

CEWII

sashafroyland Grasshopper Points: 14 More actions · Answer 3

Hello CEWII,

Looking to justify the cost and added complexity of moving to SQL Server Express from MS Access by quoting concrete facts/stats - apples to apples comparison. Thus far I have not found conclusive data.

One experienced person I spoke with stated that SQL Server Express and MS Access perform about the same until the tables approach 1 million rows in size and only then does SQL Server Express start to outperform MS Access. This is the guiding principal I'll be using going forward to help decide when an MS Access database should be migrated/upsized to SQL Server Express.

Good article:

http://articles.techrepublic.com.com/5100-10878_11-6140859.html

Some Stats - (couldn't really understand the overall results)

http://blog.nkadesign.com/2009/access-vs-sql-server-some-stats-part-1/

Open Question:

Does SQL Server Express utilize a single CPU's multiple cores?

Thanks,

Sasha Froyland

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

I think the Tech Republic article was pretty good..

I have some additional questions.

1. What will the front-end be?

2. How big do you expect this app to grow to?

3. What is your end goal for the app?

4. At some point in the future would you likely move from Access to SQL?

5. How many concurrent users?

6. Any complex processes or queries you want to implement?

7. Planning to sell app?

I have some additional comments.

Express will only use 1 processor, period.

Cost is Free, access has to be licensed in some way, may not be an issue in your organization.

I reviewed that blog entry and can see how you can come to the 1M assumption, one of the most important sentences is:

As a side note, in this particular test, Access offers much better raw performance than SQL Server. In more complex scenarios it’s very likely that Access’ performance would degrade more than SQL Server, but it’s nice to see that Access isn’t a sloth.

Single table writes is one thing but this case rarely represents a real-world scenario.

For anything shy of very simple app that can reside fully in Access I would strongly recommend against Access. From your post I think that the biggest objection would be complexity of SQL Express.

For me, any commercial app using Access as a database engine is a deal killer for me. With the advent of SQL Express (or MSDE) and SQL Server Compact there is no reason to use Access for a commercial app.

CEWII