SharePoint Performance Optimization with SQL Server

How to design your disk system for maximum performance at the database level.

Disk input/output (I/O) performance is the single biggest constraint on database performance in the SharePoint world. Running SharePoint on an underpowered or poorly designed SQL Server environment will slow performance throughout the farm. 

Because it's equally important to make sure SQL Server databases are kept highly available, some level of disk redundancy is usually factored into physical designs for database servers. Disk redundancy is often classified by RAID levels. After I explain how the various RAID levels can help performance and availability, I'll share some tips on how to design your disk system for maximum performance at the database level.

RAID Fundamentals

RAID technologies predate PCs and modern x86 server designs. Three of the most common levels are RAID 1, RAID 5, and RAID 10, so let's take a closer look at them. 

RAID 1. In a RAID 1 design, you have mirrored pairs of disks. If one disk fails, the other has a full copy of all the data and goes on operating. RAID 1 is a high-performance technology but requires purchasing 2GB of raw disk space for every 1GB of data because you need two sets of identical data. So, although RAID 1 might initially seem like an inexpensive solution because there are only two disks involved, it can be an expensive one if you have a lot of data to store. 

RAID 5. In this RAID level, you use groups of disks to create a large synthetic unit. For example, five 150GB disks could be combined to create one hybrid 600GB storage pool. 

At this point, you might thinking, "Wait a minute, that's not correct!" Although I'm not great at math, I know that 150GB ´ 5 should equal 750GB, not 600GB. However, in RAID 5, one disk is dedicated to calculating a checksum, or parity bit, every time data is written to the group. That way, if any of the single disks fail, the data can be redistributed to other disks by reversing the parity calculations. As a result, RAID 5 is highly efficient at optimizing the volume of data stored. It's also great at handling high-volume read operations -- more disks working together means more systems to read bits from a disk platter and fewer chances for bottlenecks. 

Write operations are a different story. Because every write operation requires a calculation, high-volume write operations are slower than they would be in a traditional disk system. However, for SharePoint, that's not always the worst thing, because SharePoint is a read-heavy database consumer. 

RAID 10. Some people make a distinction between RAID 1+0 (stripes across mirrors) and RAID 0+1 (mirrors of stripes). This debate doesn't really matter in SharePoint designs. What does matter is that out of all the RAID levels, RAID 10 offers the highest availability and highest performance possible. However, RAID 10 requires purchasing extra disks for parity and mirroring -- more than 2GB of raw disk space for every 1GB of data, effectively more than doubling storage costs.

Design Tips

SQL Server databases are the largest consumers of disk space in SharePoint. Thus, designing your disk system for maximum performance at the database level is more crucial than for any other level. Here are some general design tips. 

Don't virtualize SQL Server. If possible, don't virtualize SQL Server because it's already an integration platform. If you have to virtualize SQL Server, try to limit the virtualization to test or development systems. Avoid using it for production systems because it raises the bar in disk engineering to get good performance. SQL Server databases stored in virtualized disk files are inherently slow compared with dedicated physical disks. 

Use multiple logical drive letters. It's usually a good idea to break up SQL Server databases into multiple logical drive letters because database files, transaction logs, backup files, and temporary databases (tempdb) benefit from having multiple independent sets of disk spindles. Adding more spindles spreads the load across multiple parallel operations when data is being written to the database. 

If you're going to use a SAN or virtualized environment, make sure you understand where those logical drive letters will be mapped. For example, if the D, E, and F drives will all point back to separate LUNs on your SAN, but those LUNs will be part of the same storage group and same set of physical disks, splitting those files into multiple drive letters will add complexity without significant performance gains. 

Use RAID 10 judiciously. RAID 10 is great, but you might not be able to justify it for all applications. For example, it might be overkill for backup files. Balancing disk performance and cost is a reasonable trade-off. One possible design is to use:

• RAID 1 on boot disks
• RAID 5 on data disks
• RAID 10 on log disks
• No RAID or RAID 5 on backup disks

Break large content databases into multiple database files. If you have large content databases, you can engineer better performance by breaking each large database into multiple database files. Each database file should be on a separate disk. 

Presize SQL Server databases. SQL Server databases can be set to automatically grow as needed, but this can lead to massive file fragmentation. Presizing the databases to a sufficient size at the outset helps ensure contiguous file allocations. Note that SQL Server's tempdb database is heavily used by SharePoint, so you should presize it to about 20 percent of the size of the single largest content database. 

If you want to use automatic database growth settings instead of presizing your databases, you should set the databases to grow in 50MB to 100MB clumps and not by percentage. Setting a 100GB database to grow in 10 percent increments means the database essentially stops to add 10GB or more on each increment. Using a small clump size will lead to more frequent, but smoother, steady state (i.e., continuous) growth.

Design a High-Performance Database Environment

If you use RAID and follow the general design tips, you can design a high-performance database environment that's also highly available. This is essential to a smoothly running SharePoint system.

With permissions from SharePoint Pro. 

Installing SharePoint 2013: Video

SharePoint 2013 is finally here! It packs a real punch with all the new features that Microsoft is delivering with it. For example, have you played with the new document upload functionality or how about the new SharePoint Apps marketplace? 

You must have at least jumped into the awesome Community site social features right? No? Well, I guess you need an environment first to get started on SharePoint 2013.…This is what we're going to talk about here.
To get a prototype environment going, you need to install SharePoint 2013 either directly on your computer or install it within a virtual machine (either Hyper-V or VMware). There are plenty of steps to installing the software; however, the first things you need to make sure of are the following:

1. You’ll need a 64-bit environment. Starting with SharePoint 2010, the software no longer supports anything but a 64-bit environment whether native or virtual in nature.
2. Windows Server 2008 R2 is needed as the base OS to install SharePoint 2013.
3. SQL Server 2008 R2 (or SQL Server 2012) is needed to host your SharePoint 2013 databases. SQL Server Express can also do the trick; however, it has limitations

To watch complete video: http://www.sharepointpromag.com/article/sharepoint-2013/installing-sharepoint-2013-144618

Understanding My Sharepoint Sites- SharePoint 2007

I researched on the net and did not find a perfect Answer explaining about “My SharePoint sites” . So Thought of sharing this

I got to know that this is a Automated process But how does it work ?  Why does it not work for some users And they keep Opening Tickets J

Being a Farm admin why don't we see all the sites in the My SharePoint sites

This was very confusing so I wanted to do some more research on this . After Researching what I got to know is the Membership information comes from the profile import/crawl and the "My SharePoint Sites" gets updated on the client PC on a daily basis.

It happens that's some users don't see all the sites listed under My SharePoint sites even if they are a part of the site.

Add to My links is not connected to My SharePoint sites it will add a link in the Drop down below My SharePoint Sites . This is the First thing all the users often try .

So after Going through some documentation this is what I got to know

For the users that are not experiencing the membership features of MOSS 2007 (for e.g. My SharePoint Sites, etc), they were listed in the Owners group on the SharePoint site.

The membership functionality strictly works for the users listed explicitly in the Members group of the SharePoint site.

Now It Does make sense and is much Transparent. The only thing we have to do now is insure the owner of the site is also a member of each site collection in order for them to take advantage of the membership features .

As long as your site collections all reside under the same SSP your My Site belongs to, the membership functionality will work across the board.

Note : Your memberships will be updated on the next index crawl cycle

***** How to Show sub-sites Under My SharePoint Sites ?******

Answer = You will have to explicitly add the user to the members group of all the subsites as well.

When we Create a sub-site it automatically creates 3 new groups for that subsite. By default it puts the site creator in the Owners and the Members group.

 So it Doesn't Mean That if you are a owner you need not be a member of that site .If you remove yourself from the members group will not allow the site to be captured in the membership system

Documents in this folder are not available. This folder may have been moved or deleted, or network problems may be preventing a connection to the server.: Explorer View.

Issue:

While trying to open the document library in explorer view or using “Open with Windows Explorer” option and was getting following error

Documents in this folder are not available. This folder may have been moved or deleted, or network problems may be preventing a connection to the server.

Troubleshooting:

1.       Confirmed that it’s a machine specific issue by login user to other workstation.

2.       On user workstation, checked and found that the webClient service was not running.

3.       From services.msc, changed service startup type to Automatic and Started the service.

4.       Performed IE Reset.

5.       Run Office diagnostics.

6.       Rebooted the workstation after every steps to verify.

Resolution:

1.       Backed up the registry folder "Software".

2.       Deleted a folder “Software” under “HKEY_CLASSES_ROOT -> Software -> Microsoft -> MasterAggregatorForIPP -> OleDbHandlers”

3.       Run Office Repair

4.       Restarted workstation.

Configure People Picker in SharePoint 2013

You configure the People Picker web control at the zone level for a farm by using the Stsadm setproperty operation. By configuring the settings for the control, you can filter and restrict the results that are displayed when a user searches for a user, group, or claim. Those settings will apply to every site within the site collection.

The information in this article applies only to web applications that use Windows authentication in either claims mode (the default for SharePoint 2013) or classic mode.

You use the People Picker control to find and select users, groups, and claims when a site, list, or library owner assigns permissions in SharePoint 2013. People Picker is configured at the zone level for a farm by using the Stsadm setproperty operation. By configuring the settings for the control, you can filter and restrict the results that are displayed when a user searches for a user, group or claim. Those settings will apply to every site within the site collection. For more information about the People Picker properties, see Peoplepicker: Stsadm properties

This article contains information about People Picker Stsadm properties and how to configure People Picker for specific scenarios. For more information about the People Picker control and how it works, its relationship to authentication and claims providers, and how to plan for People Picker, see People Picker and claims providers overview (SharePoint 2013).
Before you perform the procedures in this article, you must do the following:

• Verify that the account that you use to run Stsadm is a member of the Administrators group on the server on which SharePoint 2013 is installed.
• Open the Command Prompt window as an administrator to perform the procedures in this article.
• In the command prompt on the drive where SharePoint 2013 is installed, change to the following directory: %COMMONPROGRAMFILES%\Microsoft Shared\Web Server Extensions\15\Bin. %COMMONPROGRAMFILES% is typically set to :\Program Files\Common Files.

Detailed Article: http://technet.microsoft.com/en-us/library/gg602075%28v=office.15%29.aspx