[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

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.
0
r_i_x
Asked:
r_i_x
  • 5
  • 2
2 Solutions
 
jogosCommented:
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)
 
0
 
jogosCommented:
0
 
r_i_xAuthor Commented:
What about recommended hardware? Infrastructure?

All on one server? Multiple servers? SAN? Cluster?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
jogosCommented:
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.

0
 
jogosCommented:
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.

http://www.sql-server-performance.com/2004/hardware-planning/
0
 
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
0
 
jogosCommented:
"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
 
0
 
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now