Evaluating MS SQL Server Express as interim upgrade to Access Database.
A recent SQL Server Express evaluation came down to a number of key considerations. Bear in mind that the network platform involved was Windows XP workstations running on Windows Server 2003.
1. Can a SQL Server Express back-end serve more users than an equivalent MS Access platform?
2. Should we use SQL Server Express 2005 or 2008?
3. What are the installation requirements for SQL Server Express 2005.
4. How does the feature set of SQL Server Express compare to SQL Server.
5. What would be involved in converting the current Access database to SQL Server Express 2005
Obviously, if you are evaluating SQL Server Express for longer term projects other issues should be considered, for example:
- Security
- Scalability
- Robustness
But this is a short-term, interim upgrade until a full SQL Server upgrade option is available. So, here I outline the answers we formulated to each question.
Q1. Can a SQL Server Express back-end serve more users than an equivalent MS Access platform?
Older 'free' SQL Server versions (MSDE) had a governer built in to limit user connections. Neither SQL Express 2005 & 2008 have a 'governer' or limiter built into the software.
They are limited in terms of system RAM (1GB ) and Enterprise Features, however they should be capable of serving more users reliably. So, how many users are we talking about? According to Rajesh & Delano (2006) these limitations typically allow 25 concurrent users - though obviously this number is dependent on the complexity of the application and how well queries are optimised.
"There is no limit on the number of user connections to the database, but performance is limited by the use of a single CPU and 1GB RAM. Typically applications using SSE can scale to 25 concurrent users."
Rajesh & Delano (2006)
Q2. Should we use SQL Server Express 2005 or 2008?
The installation requirements for 2005 are less - and the though the manangement console is inferior - you can actually use the 2008 management tools on a 2005 database engine.
In terms of backward compatibility, 2005 would seem to be the better choice. .
Q3. Installation requirements SQL Server Express 2005
|
SQL Server 2005 Express Edition |
Pentium III-compatible processor or higher |
Minimum: 500 MHz |
Recommended: 512 MB or more |
|
SQL Server 2005 Express Edition with Advanced Services |
Pentium III-compatible processor or higher |
Minimum: 600 MHz |
Minimum: 512 MB |
4. Features of SQL Server Express compared to SQL Server
Automated Backup is possible - but not standard for Express version. Some of the features include:
|
SQL Server 2005 Express All programmability features such as T-SQL, ADO.NET, SQL Native Client, and .NET support. SQL Server Management Studio Express Edition Replication Subscription SQL Service Broker Client Data Encryption and Key Management Basic Import and Export Basic Reporting 1 CPU and 1 GB Ram supported 4 GB Limit on database size 64-bit WOW support User Instance (XCopy Deployment)[*] |
||
|
[*]All the features except for User Instance (XCopy Deployment) are present in higher level editions. |
||
This link shows the differences between SSEE and other SQL Server versions:
http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx
5. Conversion of Access DB to SQL Server Express 2005
Two approaches are available if the existing Access frontend will be maintained:
1. Using an ODBC connection - no changes required to frontend.
2. Using a native ADP connection - While there will be no interface changes for end users, significant time will be required to marry the current frontend with the ADP version.
Questions to consider.
What version of SQL Server will the system eventually run on?
So overall, the consensus in this case is that SQL Server Express 2005 is the favoured option. As an interim solution it offers the best blend of functionality, manageability (using the 2008 console) and backward compatibility.
References
, and . "Chapter 1 - Getting Started with SQL Server 2005 Express Edition". Wrox's SQL Server 2005 Express Edition Starter Kit. Wrox Press. © 2006. Books24x7. <http://common.books24x7.com.dml.regis.edu/book/id_11795/book.asp> (accessed October 20, 2009)
SQL Server 2008 Express with Tools(64-bit) x64
The following table shows the system requirements for SQL Server 2008 Express with Tools (64-bit) x64.
| Component | Requirement |
|---|---|
|
Processor |
Processor type:
Processor speed:
|
|
Operating System |
Windows Server 2003 x64 Windows Server 2003 SP2 64-bit x64 Standard1 Windows Server 2003 SP2 64-bit x64 Datacenter1 Windows Server 2003 SP2 64-bit x64 Enterprise1 Windows Vista Ultimate x64 Windows Vista Home Premium x64 Windows Vista Home Basic x64 Windows Vista Enterprise x64 Windows Vista Business x64 Windows Server 2008 64-bit x64 Web1 Windows Server 2008 64-bit x64 Standard Windows Server 2008 64-bit x64 Standard without Hyper-V1 Windows Server 2008 64-bit x64 Datacenter Windows Server 2008 64-bit x64 Datacenter without Hyper-V1 Windows Server 2008 64-bit x64 Enterprise Windows Server 2008 64-bit x64 Enterprise without Hyper-V1 Windows 7 64-bit x64 Ultimate2 Windows 7 64-bit x64 Home Basic2 Windows 7 64-bit x64 Home Premium2 Windows 7 64-bit x64 Enterprise2 Windows 7 64-bit x64 Professional2 Windows 2008 R2 64-bit x64 Web1,2 Windows 2008 R2 64-bit x64 Foundation Server1,2 Windows 2008 R2 64-bit x64 Standard1,2 Windows 2008 R2 64-bit x64 Enterprise1,2 Windows 2008 R2 64-bit x64 Datacenter1,2 |
|
Memory |
RAM:
|
1 Management Tools are supported in WOW64, a feature of 64-bit editions of Windows that enables 32-bit applications to execute natively in 32-bit mode. Applications function in 32-bit mode even though the underlying operating system is running on the 64-bit operating system.
2Supported only on SQL Server SP1 or higher.
- admin's blog
- Login or register to post comments
