January 12, 2011 at 12:02 pm
Thank U every Body.
Currently I am developing a certain Web based Application. the following Information.
Database Table more than 100,
DBMS: SQL Server 2008,
Windows Server 2008,
Report Server: SSRS
Web server: IIS7
And the data is expected to be very large with in a short period of time. ….Millions/Billions of records….. And different users are expected to use the system ( access, Add, Delete ,edit ) according to their privilege both inside and outside of the LAN.
Since now I am going to deploy it, I am thinking about the performance of the system. For this, I have to consider many things ( like The machine itself …. and other parameters).
The other important point is, I am not familiar with deploying such large system. That is why I want to ask professional in the field regarding the issue.
Could you advise me what power full server/Machine …, number of machine(Server Computer)./ can be good for such system (for performance issue)… and other things that increase the performance( I already considered the Query and the design issue on performance)
Thank U very much for your reply
January 12, 2011 at 12:24 pm
If I had my choices I'd use at least three machines, IIS, SSRS, SQL.
SQL should be big in terms of memory 32GB+, maybe 64GB+. I'd probably go at least 2 x 8core processors. Drive layouts with data, logs, tempdb, and backups all on different disk spindles if possible, maybe SAN.. I like RAID 10 for data, logs, and tempdb, backups can be local on RAID5 or on a file share on file server.
SSRS doesn't need a whole lot of disk space unless you are caching a lot of reports. Memory can be much smaller 8-16GB is probably more than enough. I'd say shoot for at least 4 cores, perhaps 8, but many more than that probably would be under-utilized. Also, with this configuration you could scale out and add an additional SSRS machine for scalability very easily.
IIS (note: this is my weak point in terms of sizing) is only used for serving pages and the real report work occurs on the SSRS machine. I would say this machine could be small 4GB and 4 cores. If for some reason this machine gets too busy you could add a second to handle the load, perhaps even connect each IIS machine to its own SSRS, or any other rational seperation (internal/external, power users/everybody else, US/International, etc.)..
Absolutely spend time evaluating indexes and query performance. Be sure that the database structures make sense. In honor of Jeff Moden, please keep in mind potential RBAR logic and avoid it. Resist pressures to "just get it done".
As a final thought.. "We aint got time to do it right, but we got time to do it twice.." Don't fall into that trap..
CEWII
January 12, 2011 at 12:24 pm
I strongly suggest that you get a consultant in to do a review and point out any areas of potential problem
Disclaimer: I am a consultant and this is the kind of work I do, though I doubt I'm in the same part of the world as the OP.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2011 at 12:25 pm
Also a very good idea Gila..
CEWII
January 12, 2011 at 12:28 pm
Well....
There are some very important pieces of information that I think anyone is going to need in order to provide any sizing suggestions.
When you say there will be millions/billions of records, which will it be? There is a huge difference between 20 million and 1 billion.
You mention 100 tables, will one table contain most of the records? Will it be several?
How many transactions do you need to do a minute on average? What will your peak transaction rate be? This is very important in order to configure I/O, processor and memory. What sort of response time do you need, sub second, 5 minutes?
What is the breakdown between reads and writes?
Let's start there.
January 12, 2011 at 12:46 pm
Thank You very much for your information.
Let me explain more.
There are 5 main tables (Expected to grow fast). The expected amount of records (approximately) are shown below
Table 1: 600,000 records per year
Table 2: 100,000 records per year
Table 3: 500, 000 records per year
Table 4: 1000, 000 records per Year
Table 5: 200, 000 records per year
The others are below 50, 000 records per year and even there are many tables with nearly fixed records ( and records less than 500)
Average transaction is expected to be around 200
And peak transaction is expected to be maximum of 1000-1500
Thank U very much
January 12, 2011 at 1:38 pm
Thank you very much all of you(Elliott Whitlow, Henry Treftz, GilaMonster) for your suggestion or further elaboration.
I explained more about the expected data size. Could you suggest me based on this ? or any thing that you can advise me?
Thank U in advance!
January 12, 2011 at 2:06 pm
Use 64 Bit Machine as the Server and good amount of Memory like 32 GB + , Seperate the Tlogs, Datafiles, TempDB , Backups onto seperate drives , http://www.sql-server-performance.com/articles/per/Characterizing_IO_Workload_p1.aspx
Split Tempdb into multiple similar files based on the processor count ,Use RAID 10 or RAID 1 (If Budget Constraints) for better performance , optimaly configure the SQL Server settings like Max Memory and Lock Pages in Memory, Seperate the tables and indexes into different filegroups and put them on seperate disks , Enable the option "Optimize for Adhoc Workloads " and Regulary do the maintenance activities like index defrag and update statistics , periodically chek for any missing indexes and unused indexes.
Also SSRS 2008 don't need IIS.
Thank You,
Best Regards,
SQLBuddy.
January 12, 2011 at 2:23 pm
I would say not much changed for me.. 2-3m new records even on a fairly small machine doesn't really scare me..
SQL, probably 2 x 4 cores and 16-32GB of ram, all the rest stays the same. HOWEVER, if you can get it, go with the original sizing.
Also definitely 64-bit and SQL Buddy's recommendation of multiple tempdb files is a rule of thumb, I have seen studies indicating that core to file ration is not always (usually?) 1:1. But definitely more than 1..
However, how heavy is the reporting gonna be? Is this a report heavy system? You might consider rolling the SSRS and the IIS into the same box if it isn't terribly..
I'd rather have additional capacity, you can usually find other tenants..
CEWII
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply