Best Practices for SQL 2008 in SharePoint 2010 Farm

Hi All,


In this post i’d like to share with you best practices for SQL 2008 R2 in SharePoint 2010 farm and the operations and configuration that need to be taken in place when installing, configuring and administrating SharePoint 2010.


1) Designing SQL 2008 servers in your SharePoint 2010 farm: When you decide which topology you will have in your SharePoint 2010 farm, the SQL Server 2008 should be in a dedicated server and shouldn’t be having any other role in your SP 2010 farm.


2) After installing SQL 2008 R2, you shouldn’t go and start SP 2010 installation with SQL 2008 R2 Default configuration, here is what you need to revise:
    a) Don’t enable auto-create statistics on SQL Server that’s supporting SharePoint farm. You can enable/disable this feature on the DB level, here is the command you need to execute in your DB:



ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS OFF

     b) Set Maximum degree of parallelism to 1 for all SQL server instances that host SP 2010 DBs. The value behind this is that each request is served by a single SQL Server Process.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

3) Secure and harden your SQL 2008 server DB by blocking the default ports and block default SQL server default ports. Check Out ref #3.

4) Use SQL Server 2008 backup compression feature to speed up the SharePoint backup. This feature is available in SQL 2008 Ent. edition and SQL 2008 R2 standard edition too. This feature significantly reduces the backup file size and shipped logs. The only consideration you should know that this feature consumes more processing time that might affect the performance of your DB server if the server doesn't properly planned to have enough processing power. This feature you enable it on the server level under Database settings, you will find a check box to use DB compression.



Hope this helps and let me know if you have any additional notes or concerns.


Refereces:
1) Auto-Create Feature in SQL 2008:
http://blogs.technet.com/b/rob/archive/2008/05/16/sql-server-statistics.aspx
2) Set the maximum degree of parallelism in SQL 2008:
http://msdn.microsoft.com/en-us/library/ms181007.aspx
3) Harden & Secure SQL 2008 server DBs:
http://technet.microsoft.com/en-us/library/ff607733.aspx
http://technet.microsoft.com/en-us/library/cc262849.aspx
5) SQL 2008 Best practices in SharePoint 2010 Farm:
http://technet.microsoft.com/en-us/library/hh292622.aspx


Regards,
Mostafa Arafa
http://twitter.com/mostafaelzoghbi

Fix it: IIS Manager doesn’t update web.config when you add HTTP Response headers in Azure

Hi All,

I was trying to disable Silverlight caching in IIS 7, and when i deployed my solution to Windows 2008 Ent. in Windows Azure. Every time you click on the xap file that i need to disable caching on it by checking expire web content immediately after i clicked on Set Common Headers link on the right pane, the web.config is not being updated at all. but this works on my local IIS.

I ended up with adding this attribute that has been added my local IIS to web.config and deploy it so i don’t need to do this every time i perform a deployment.

<system.webServer> 
     <staticContent> 
      <clientCache cacheControlMode="DisableCache" /> 
    <staticContent>       
<system.webServer>


To disable caching in Silverlight “xap” file from IIS, Here is the article that i’m referring to:



Hope this tip helps.

Regards,Mostafa Arafatwitter.com/mostafaelzoghbi