SQL Server Configuration

SQL Server environment is 4 years old, looking to update/upgrade for performance, space, etc.

Hosts internal ERP solution. DB is about 50G in size and growing at about 15G/year. Between 150-200 users access site concurrently.

Current install is SQL Server Standard 2005, we do have license for SQL 2008 Enterprise.

Use Sage ACCPAC and DBs are also hosted there on same instance. (3 separate DBs) About 15 users making use of these DBs.

SQL Server Reporting Services is used and also on the same server instance.

Operation is 24/7 as it supports manufacturing environment. Some downtime is allowed for PM in slow season or some off weekends.

Looking for recommendations for configuration for ultimate performance. Budget is not determined at this point as the goal is performance. Let me know your thoughts/recommendations.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

With that growth I think you will benifit of partitioning.

A move to 2008 will give other oportunities (to reduce the size)
- compression http://www.mssqltips.com/sqlservertip/1582/implementing-data-compression-in-sql-server-2008/
-  filtered indexes : http://www.mssqltips.com/sqlservertip/1785/sql-server-filtered-indexes-what-they-are-how-to-use-and-performance-advantages/

And from that 15G a yeart i think there will be data that does not need to live forever
-> look for data that can be deleted after for example 3 years without compromising usage of data that remains (with or without a change in your db or app)
r_i_xAuthor Commented:
What about recommended hardware? Infrastructure?

All on one server? Multiple servers? SAN? Cluster?
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

One server should not be a problem, but monitoring your server should learn you that: cpu, disk, memory.
Also for the sql itself you should monitor to see what's the bottleneck at the moment, how it slows down .  Maintenance (fragmentation), indexes, tuning querries, smaler lock-window .... there is a lot to gain but you have to measure first to see where you can improve.

Disk, general rule as much things you use together on different spindle. That goes for data-file, log-file, temp-db, backup-files (if you backup on disk first).
More than one temp-db (till one per CPU).

A cluster is always nice especialy when you want to keep the uptime high. If the small databases are on another sql instance you can put them default on the other node of the cluster. If a server upgrade has to be done you put them both instances on same node and you have your hands free on the other.

You have to measure now how is your actual system performing, where the bottlenecks are. Only then you can start thinking what will be top priority  in your new system.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
r_i_xAuthor Commented:
Not sure that hardware is the issue but here is what we're planning on.

What we're going to do:
- move reporting data (updated every night) to a SQL Express server.
- move another application's db to a different server
- start to archive  transactional data with a 13-month archive process
"Not sure that hardware is the issue " -> measure, measure, measure... even if you don't have a problem yet it's good to know the weakest part and a reference for when it's getting a problem or what the gain was of the moves of db's
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.