How to handle very large database in dedicated server

Handling very large database is crucial part when it comes to high end solution.Effective handling of VLDBs will increase the performance of portal and E-Commerce websites.When application goes big the entries will increase in the database and it can contain millions or rows.Here the point is no need to worry about total number of rows stored in your tables but the quality of query,indexing strategy has to be considered to increase the performance of your database function.

The tows can be grouped by some value either by month or year. Each group could be accessible as separate table with own index.Partitioned Tables complicate the database, but in case of extremely long tables it could lead to significantly better performance. It also supports “filegroups” to store values in different disks.Good search function has to be created to search the records. Check for duplicate, redundant or unused indexes.Make sure your tempdb is properly configured.This one should also apply to any production system but again, it has a really big impact on a VLDB. The main reasons are that ETL processes will usually do operations on temp tables, execution plans that work with a lot of data might use tempdb a lot more aggressively and also row-versioning concurrency is common on these systems to avoid shared locking. So always make sure that:

a) You have enough tempdb files depending on your cores (I usually say start with 50% the amount of cores to a max of 8 and increase if necessary).

b) Your storage tier holding tempdb is fast enough.

c) Your execution plans are optimal and not doing a lot of spills or spooling on tempdb.

To handle efficiently the application can have 3 different connection strings:

1.Writes and real-time reads
2.Reads that can tolerate data older than 15 seconds (no writes allowed with this connection string)
3.Reads that can tolerate data several hours old (like reports, and no writes allowed with this string)

Apart from database optimization the other factor that has to be considered is server hardware. You should have high end CPU and RAM in order to handle VLDBs effectively. Because when user request for value in VLDBs, you should have enough memory to retrieve the value. So starting from 8GB RAM Dedicated Server to 256GB Dedicated Server can be considered for application hosting. If number of users increases then CPU plays a role. General single quad core or dual quad core can handle VLDBs efficiently. SSD drives will still boost your performance. By combining good hardware design and optimized database design model will give best performance to handle VLDBs.