Which version of SQL Server is for me?

[SigAdams]

Currently I have MS Access front ends connecting to an MS Access backend.

We've outgrown this solution and looking to step up to an SQL Server backend.

Which of the current Microsoft licensing options would suit my needs of 75 - 100 users connected at a time.

Thanks

 

[gmmastros]

Can you explain WHY you have outgrown MS Access?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SigAdams]

Any useful advice?

 

[gmmastros]

I was hoping you would explain the problem you are currently having with your MS Access database. Are you having problems with space, performance, or both.

Which of the current Microsoft licensing options would suit my needs of 75 - 100 users connected at a time.

All versions of Microsoft SQL Server can handle 75 - 100 users (except for MSDE2000). That assumes that your queries are efficient. A poorly designed and implemented database can perform horribly with just one or 2 users, and a properly designed database can support many times more users. How many? It depends on various factors.

I assume you are currently handling things with MS Access. As such, I would recommend you download SQL Server 2008 R2 Express. This is a free product from Microsoft. With it, your are limited to the size of the database (10 gigs if I remember correctly). This shouldn't be a problem though because your MS Access database has a similar limit.

After porting your database to SQL Express, you will likely need to modify some of your queries, particularly the ones involving dates and any queries that have IIF and the NZ function. SQL Server has similar functionality but different syntax.

Once you get your queries to work properly, then you can begin performance tuning those that are slow. This will likely involve adding additional indexes and/or modifying the queries to be more efficient.

If you later decide that the free version of SQL Server isn't good enough, you can purchase a "better" version like standard or enterprise. If you decide to do this, all you will need to do is detach the database, uninstall the express version, install the newer version, and reattach the database.

My suggestion is to try the free version first. If you need to purchase a better version, the time spent getting the free version to work will not have been wasted because you would still have needed to do all of the conversion work anyway.

You can download the free version here:

http://www.microsoft.com/express/database/

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should also mention that it is common for people that are upgrading from Access to SQL Server to have questions regarding syntax and/or performance. If you find yourself in a similar situation, you can post your question at this site and someone is likely to help. Each question you have should be posted to a new thread.

I think you'll find that the SQL Server community is very helpful, and that there is a lot of information available to help you during this transition.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SigAdams]

Basically I have 3 sites located hundreds of miles a part. Each is writing data to their own Access backend. I want to consolidate by writing data to a single SQL backend. I already have hosting.

I just don't understand the SQL license structure.