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.
1) Auto-Create Feature in SQL 2008:
2) Set the maximum degree of parallelism in SQL 2008:
3) Harden & Secure SQL 2008 server DBs:
5) SQL 2008 Best practices in SharePoint 2010 Farm: