First published on TECHNET on Nov 22, 2012
So continuing down the road of Web Analytics see my Previous post
Thought on Web Analytics in SharePoint 2010 - Part 1
The next question my customer asked was about architecture so I have gathered some information here. We do have some great guidelines around the Web Analytics Databases and SharePoint (See the end for articles), but I felt we lacked around SQL and more specifically the drives that the databases will be stored on.
The first thing to consider is the IOPS you require and figuring out the IOPS can be difficult as it depends on several variables least of which is
- Retention period
- Daily volume of data being tracked
- Number of site collections, sites, and subsites being analyzed
After talking with others the best guidance I can give without actually going thru the testing is that web analytics will require approx. 1 - 2 IOPS per GB, if you review the article http://technet.microsoft.com/en-us/library/gg440601.aspx the article actually recommends a full instance of SQL for the two databases (Not that I am) but just to show the intensity of the service on the SQL server.
So going with the recommendations above (We will go with 1.5 IOPS since that is the median) and a drive that is 3 TB in size the current disk sub-system would have to be able to handle at least 4608 IOPS being added to it.
Some more guidance that I can give is that we are looking for response times from the disk that is better than 20ms and performance that matches Raid 10.
Articles
Storage and SQL Server capacity planning and configuration (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/cc298801.aspx
Performance and capacity test results and recommendations (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/ff608068.aspx
Capacity requirements for the Web Analytics Shared Service in SharePoint Server 2010
http://technet.microsoft.com/en-us/library/gg440601.aspx
