Tag Archives: database

Installing SharePoint 2013 Foundation

Come along with me on a small adventure into the world of free SharePoint. Yes, free! SharePoint Foundation 2013 is technically free (well, included in your existing Windows licenses) and can do a whole lot for you without needing to spend significant amounts of money on Server editions. I am going to walk through a mini-series around SharePoint Foundation: installing (this post), what features it contains (and does not), how it can fulfill some use cases and finally a wrap up discussing if Foundation is a viable solution for companies both large and small.

Downloading SharePoint Foundation

Finding SharePoint Foundation was not as easy as one would expect. Going to www.sharepoint.com, and clicking Try or Buy didn’t render a link. I clicked Try Now under SharePoint Server 2013. Then off to the right under Related Downloads is a link for SharePoint Foundation 2013. Wait, that doesn’t have SP1 which is required for Windows Server 2012 (but SP1 has been retracted, hasn’t it?), so I searched some more and found it. Lucky for you, you can download it from: http://www.microsoft.com/en-us/download/details.aspx?id=42039. If you’re running Windows Server 2008, you can download it without SP1: http://www.microsoft.com/en-us/download/details.aspx?id=35488. Looks like Microsoft doesn’t necessarily want to show off this free version is readily available.

Requirements

Requirements for Foundation are similar to the Server versions. Check out the hardware and software requirements here.

My Setup

I am installing it in VMWare, with 8GB RAM and 2 cores. It’ll be slow but that’s okay for now. I already have Windows 2012 R2 Standard installed, and I have SQL Server 2012 Standard with SP1 installed on another VM. SQL has been configured with a named instance SPF2013A for this specific instance of SharePoint.

I need SQL too?

SharePoint runs on Microsoft SQL, period. Sorry, you can’t run on MySql or any other variation out there. If you don’t have access to SQL server, you can either install SharePoint in a single server instance which includes SQL for you, or better yet, download and install SQL Express (free). Note there are limitations, but if you’re doing a quick POC or trying out SharePoint, SQL Express will probably be fine. I recommend installing SQL separately as it’ll allow you to expand on your new SharePoint farm in the future. Even if you don’t have plans to expand this farm, install SQL separately, as your production farm will be using this model. I do not cover installing SQL, as there are many flavors and options.

Service Accounts

SharePoint utilizes Active Directory for permissions and such, starting at installation. You cannot install it on a server without AD. You shouldn’t install it on a server that is the AD domain server, though it will probably work, it’s not supported by Microsoft.

Create the following AD accounts for your SharePoint install. You can go crazy and use a service account for every service in SharePoint, allowing for an incredible amount of security separation (and maintenance) but being realistic, I prefer to use the following. Each account has to be a domain account, not a domain admin, just a domain user.

  • Farm admin account, i.e. spfarm.
    • This account will be the installation account, and SharePoint will setup the permissions for the rest of the accounts.
    • Needs local administrative rights to your SharePoint server, excluding SQL.
    • Account must be setup with dbcreator and securityadmin roles in SQL.
  • Application service account, i.e. appsvc. This account will be used to run your service applications in SharePoint.
  • Web service account, i.e. websvc. This account will be used to run your web sites services and application pools.

Prerequisites

Prior to installing SharePoint on your server, you’ll need to setup your server to handle a web site. Add the Application Server and Web Server (IIS) roles. Also include Application Server Role Services Web Server (IIS) Support.

Check your SQL Server’s properties, specifically the Max Degree of Parallelism of a value of 1. Check out http://www.sharepointpitstop.com/2013/04/max-degree-of-parallelism-error.html for more details, I ran into this issue myself ;).

Installing SharePoint Foundation

Now that you’ve found and downloaded SharePoint Foundation, got SQL setup, let’s install SharePoint! First, log in with your  spfarm account, and then run the install file you downloaded, and you’ll get the lovely blue splash screen.

SharePoint Foundation 2013 Splash Screen

Click Install software prerequisites, under the Install section. This installs all the stuff SharePoint needs to run.

SharePoint Prerequisite Installer

Just accept defaults and walk through this wizard. This wizard will probably require your server to restart. It may need to reboot a couple of times depending on how many updates it has to do.

Log back in and rerun the installer. Back at the blue screen, press Install SharePoint Foundation. Most of the installation will be pretty basic: accept the terms, press continue. For Server Type, select Complete and press Install Now.

server type

When done, you can go ahead and press Close.

install done

So far so good? The SharePoint bits, the binaries and files, are installed! Now onto the fun part and configuring SharePoint!

The SharePoint Products Configuration Wizard will appear next. Click Next then Yes to the warning. On the next page, select Create a new server farm.

create

 

On the next screen, enter your details for the SQL server, and use your spfarm account for its credentials. I have the \SPF2013A because I installed SQL with another instance, you can probably leave yours as the server name.

sql

Specify a passphrase. Important! This passphrase will be needed if you want to add another server to your farm in the future. Keep it safe!

passphrase

 

On the next screen, you can leave it as default, or specify a port number. If you decide to specify your own port, do not specify any of the standard web ports like 80, 443, 8080, etc. This should be a unique port number as central administration is the core of SharePoint, all configuration, permissions and such occurs here.

 

central administration port

Click Next a couple more times and the configuration will run. It may take a while depending on hardware and what not. Let it run. It will error if there’s a problem, otherwise, no news is good news.

successful

Success! Click Finish and Central Administration will open.

Configuring SharePoint

After a successful installation, Central Administration should open. If for some reason it doesn’t, you can open it from the Programs menu.

When Central Administration opens first, it’ll ask if you want to help make SharePoint better. Do as you wish, it’s your conscience.

So you have an option to here to use a wizard to configure SharePoint.

wizard

I prefer not to use the wizard, I’m a hands on kind of guy. The wizard is ok, and if you’re going to do a quick and dirty proof of concept, I guess you could do that. I will, however, carry us through the entire process, I’m going to press Cancel. That will bring us out to Central Administration:

central administration

Before we make any new sites, we have to continue to configuring SharePoint to get ready. Click Security in the left quick launch, and then click Configure managed accounts.

register account

 

Add your service accounts. In my case, I added the 2. What’s nice is if you fat finger the password, it will prompt you. This is nice since that will not cause issues down the road.

 

added svc accountsOnce added, click Application Management, then click Manage Service Applications.

service application

If you click new in the top ribbon, you’ll see a few options. We’re going to add each of these. Start with the App Management Service. Fill out the following in the dialog:

  • Name. I go with something clear, like “App Management Service”
  • Database. This should default to your SQL server, and you can leave it as is.
  • Failover Server. If you have one, you can specify it, otherwise continue on.
  • Application Pool. We do want to create a new application pool.
    • Application pool name, specific Application Services.
    • For the security account, select Configurable and select the appsvc account from the picker.
  • Create App Management Service Proxy. Keep the Create option checked.
  • Click OK.

Next up, create a new Secure Store Service.

Why did I skip the Business Data Connectivity Service? Because we don’t need it. This service allows SharePoint to connect to external data systems, like another SQL database. If you want to use it, go for it, but for most POCs, we don’t need it.

Ok, back to the Secure Store Service.

  • Name: Secure Store Service.
  • Database. Again, this should default, let’s move along.
  • Failover Server. Ya, again, move along.
  • Application Pool. This time, let’s select an existing application pool, specifically the one we made before, Application Services.
  • Enable Audit. Keep that checked.
  • Click OK.

Outgoing Email

Couple more small things. Click on System Settings, then Configure outgoing e-mail settings. Specify your outgoing email server. You can simply put your Exchange server here. You’ll have to allow relaying from the SharePoint server IP address. SharePoint does not authenticate with the outgoing email server.

If you want, you can validate the outgoing email by setting up a relay on the SharePoint server. This works well with Exchange or any cloud based email service. Check out my other post on Sending SharePoint emails through the cloud.

Services

Click System Settings then Manage Services on Server.

services on server

Now we have to turn a bunch of stuff on. Turn on the following:

  • App Management Service.
  • Microsoft SharePoint Foundation Subscription Settings Service.
  • Secure Store Service.

Create your first site

Now that SharePoint is configured and ready to go, let’s create a site. The site itself will be what your users access. Click Application Management then Manage web applications.

web app

Brief overview of SharePoint’s architecture

The SharePoint farm is what we have now. It’s SharePoint, installed and configured. It can be installed across multiple servers. Note we didn’t have to install SharePoint on SQL. SQL simply stores the databases, however SQL is still considered part of the farm.

Web applications are the top level of data collections. As you’ll see, Central Administration has a web app. A web app is a collection of Site Collections.

Site collections are a collection of sites, and can contain one to many sites.

Sites are the interfaces your users go to to access SharePoint. Sites contain lists, libraries and all the user data.

Create your web application

Click New in the ribbon. Fill out the page as follows:

  • IIS Web Site. SharePoint will create the following IIS web site on your farm. Keep port 80, specify a host header. For quick and dirty, you can specify your server’s name, in my case spf2013a. If you want something more meaningful, specify a valid name which has been setup in your DNS.
  • Security Configuration. You can leave this as is.
  • Claims Authentication Types. Leave it.
  • Sign In Page URL. Move on.
  • Public URL. Ditto.
  • Application Pool. We’re going to create a new one, keeping the default name is fine. Under the security account, select your websvc.
  • Database Name and Authentication. You can leave the database name as is, however I generally append the site name to the database name so I know what database goes to which site.
  • Failover Server. Ya, you know.
  • Service Application Connections. Move along.
  • Customer Experience Improvement Program. Again, up to you.
  • Click OK.

Create your site collection

When the web application finishes, the confirmation window will have a link to create a site collection, click that.

If you were so excited to have setup your web app that you closed that confirmation window. Click Application Management, then Create site collections.

In the Create Site Collection dialog, specify the following:

  • Title and Description. The title is the name of the site, what your users will see. This can be changed at any point later. Not as stressful as naming your kid, but close. You can leave description blank.
  • Web Site Address. Keep the / selection.
  • Template Selection. Select a Team Site. This is a basic site, a great starting point.
  • Primary Site Collection Administrator. Select the smartest person you know, yourself! Specify your user name in here so you can easily get into the new site.
  • Secondary Site Collection Administration. Select the other administrator of the site. You can specify more later on.
  • Quota Template. Leave with No Quota.
  • Click OK.

You’re ready to go!

new site

That’s it! You should be all set to go! We didn’t have to create a site, as a site collection always has a default root site within it.

If you try to hit the site from the server console, you may have an issue, check out this post for an easy fix.

Quick tips:

  • Click the cog, or the gear, or the little circle looking thing in the top right, then go to Site Settings. This is all the behind the scenese including permissions, Look and Feel, search settings and a whole lot more. Familiarize yourself with what’s here.
  • Click the cog, then Add an app. This is how you add new lists and libraries.
  • Click the cog, then Site Contents. This shows all content on the current site. This is also where you can create new sub sites, scroll down and you’ll see a link for new subsite.

Til next time, Happy SharePointing!

 

About these ads

My Users Don’t Like SharePoint because it is too slow!

This is Part 7 of my series on ‘My Users Don’t Like SharePoint…

As your SharePoint matures and grows, it can get considerably larger and complex. Content databases can grow to hundreds of gigs, search indexes grow larger, users rely more on Excel services, additional external business data is pulled in, some custom functionality is added, etc. All of this can impact performance when not implemented correctly.

I look at it like it’s a good sign: your users are using SharePoint! However, as your farm grows, you should monitor the farm and possibly reconfigure and add additional servers to the mix. The first step is to figure out why it’s slow.

It’s so slow, what can I do?!

There’s a lot of reasons SharePoint’s performance can dwindle, here’s a few. This is everything I could dream up, dealt with or heard about. Did I miss something? Leave a comment!

  • Is it designed properly? Check out Microsoft’s recommendations for hardware, software and farm architecture to cover the basics. If you’re running your entire farm on a single server, I’d start by adding some more servers. Check out the SharePoint 2010 Technical Diagrams for a great starting point: http://technet.microsoft.com/en-us/library/cc263199(v=office.14).aspx 
  • A very common slow issue is when SharePoint first wakes up. Since SharePoint is a .Net application running on IIS, the application pools need to spin up, compile all of the assemblies and serve up pages. This can take a few minutes when SharePoint initially starts up. In most cases, once you’re past this slow start up, SharePoint will continue to run smoothly throughout the day. One trick to avoid this slow start up is to keep SharePoint awake. There is a simple PowerShell script which you can schedule in Windows Tasks to run, and it’ll keep hitting your SharePoint sites, thereby keeping them awake. Check it out at http://spwakeuppowershell.codeplex.com.
  • Check the Task Manager on your servers. Simple enough, but it tells us a lot. Looking at the stats in Task Manager you can determine which services are taking up loads of RAM or are pinning the processor. If you’re seeing a lot of w3wp.exe processes, take a look at my PowerShell script which marries the w3wp with the process in SharePoint. This might help clarify things a little.

SharePoint's w3wp list

  • Sometimes a specific page or two may be loading slowly. Look into all of the web parts on the page. The more you load on a page, the more it has to do. Consider taking some off, or creating another page that can house some of the web parts.
  • Community Feedback: Thanks Marc! A large number of closed Web Parts, often on the home pages, will slow down a page. Every closed Web Part causes a small amount of processing overhead, and it can add up. To check this, add ?contents=1 to the page’s URL and remove any Web Parts which aren’t displayed. It can make a huge difference.
  • If all pages are doggy, and you have a custom design, it may be a good idea to look into the assets of the design: images, CSS and JavaScript files. If these aren’t sized correctly you might experience slow performance. There are applications available that can assess a page and tell you what’s slow and how large the assets are. I like to use YSlow, and add on for Chrome, gives some pretty neat stats:

SharePoint page stats using YSlow

    • One note about these types of page stats in SharePoint. There are going to be some elements you’re stuck with, namely this first result page. SharePoint has a long list of JavaScript files which are necessary and can’t be removed. These will always skew these stats. Fortunately, the files are minified (art of shrinking a file by removing wasted space) to minimize download time.
  • You should configure your content databases in SQL to handle the anticipated usage and traffic. See my older post which also talked about performance, and has more details on databases: Improving SharePoint’s Performance.
  • Your content databases can grow up to SQL’s limit, which is in the terabytes  It’s not a recommended practice as it does impede the performance of SharePoint. As your site collections grow, consider splitting off site collections to their own content databases. This allows you to manage each database individually, which will help improve performance. Check out this MSDN article on Moving sites between content databases.
  • Caching. Storing data closer to where you need it to help improve performance. Instead of sending SharePoint all the way back to the database to get some data, it can read a local cache instead. Check out MSDN article Plan for caching and performance.
  • Community Feedback: Thanks paslatek! There is a big performance issue when your server does not have internet access and it tries to validate certificates with clr.micosoft.com. I found this TechNet article which may help.
  • Community Feedback: Thanks mansi! Performance issues can be caused by BLOB Storage: since SharePoint saves all the files (documents, images, videos, etc.) in SQL Server in the form of unstructured objects known as Binary Large Objects (BLOBs). Having too many BLOBs can slow down SharePoint’s performance as they take lot of space and require SQL to process more. A solution to work around this issue is to store your BLOBs in a location other than your content database, which SQL refers to as RBS: Remote BLOB Storage. You can read more on this: Storing your SharePoint files outside of the database (RBS).
  • Update: Lists and libraries containing several thousand items can slow things down a lot too. Check out TechNet article Designing large lists and maximizing list performance.

Gah, I think that’s it for now. I’m sure there’s more, but these have been the primary reasons I’ve come across, and have seen some great improvements after applying. If I’m missing any, please leave a comment below.

Til next week, Happy SharePointing!

Questions on SharePoint 2010 Databases

What’s the maximum size for a SharePoint database?

This is a very common question across the board. Microsoft’s documentation recommends a maximum of 200GB per content database. This isn’t a limitation, just a recommendation. They are recommending this level due to performance and maintenance considerations. When your database gets much larger than that, there are some additional steps you need to take to optimize SQL’s performance. Check out MS’s white paper on Managing Multi-Terabyte Content Databases with Microsoft SharePoint 2010. This paper explains what’s needed to optimize databases between 200GB and 4TB, and then databases even larger than that!

What about Remote Blob Storage (RBS)?

RBS externalizes the documents SharePoint stores in SQL. Read more about RBS. This does allow for smaller content databases, however you’re still using up a large amount of space, somewhere. Microsoft’s recommendations as mentioned above should be considered including the space used in RBS.

Can I use the free SQL Express?

SQL Express is a great solution for development environments, and even staging if budgets are tight and the full SharePoint Server experience isn’t required. I wouldn’t recommend it for a production environment at all. Maintenance is a lot more difficult and performance will be significantly limited with SQL Express. You cannot schedule maintenance tasks like backups. You’ll have to manage all of that outside of SQL using scripts or third-party tools. Also, the Express edition has a database size limit of 10GB, and will only use 1GB of RAM. SQL wants as much RAM as you can give it, 1GB for production will slow things down considerably.

Should I run multiple site collections in a single database?

This can go two ways, though you’ll quickly see which is preferred. As noted above, the size of the database doesn’t doesn’t matter, too much, since you can work around maintenance tasks and performance issues. I prefer and recommend to load a site collection into its own content database, instead of managing multiple sites in one database. Not only does this keep the database sizes small, it also helps with recovery.

For instance, lets say you have a site collection for each of your departments, and the HR department does something that warrants a restore. If all of your sites were in one databases, you’d be forced to restore all of the site collections within that same database (or restore it to another farm, then backup that one site collection and move it over). Instead, with each site collection in its own databases, you can restore that one database and be back to work.

It’s not too late if you already have several site collections in your database. Check out the following article on Moving site collections between databases.

Additional resources on the SharePoint databases and capacity planning

Improving SharePoint’s Performance

As SharePoint grows and morphs and becomes your information beast, it sometimes feels like it slows down. This is usually typical in implementations where the proper optimizations were not applied early on. It’s okay, it’s still possible to recover and improve some of your SharePoint’s loss of performance.

Disclaimer

Performance can be significantly degraded by using less than recommended specifications as Microsoft published. 4GB of RAM for a SQL server will slow it down no matter what we do. Make sure your hardware meets, or better yet, exceeds Microsoft’s recommendations! See Microsoft’s recommendations here: http://technet.microsoft.com/en-us/library/cc262485.aspx.

Is your farm big enough?

First thing to consider is the size of your farm. The rest of this post includes other pointers to improve performance, and should be considered regardless of the size of your farm. Your farm should never be a single server farm, unless you’re using it for development or testing. Even the testing environment should closer mirror your production environment. At minimal, your farm should consist of two servers, one for SharePoint and one for SQL. This is a minimal configuration and can survive with a dozen or two users at once. If you’re working with highly used sites, you should be considering at least a three+ server farm, and possibly some load balancing. Sizing a farm should be taken seriously, check out http://technet.microsoft.com/en-us/library/cc263199.aspx for some great references.

A quick note about disk drives.

I mention RAID and drive partitions and such. RAID stands for redundant array of independent disks. This technology allows your server to combine several drives into one large drive, and in turn, it can lose one drive (except for a RAID0) and keep all your data and server running. It magically copies data across multiple drives so if one is lost, there’s no loss of data. There are different configurations available and pros and cons for each: RAID0, RAID1, RAID5, RAID10 are the more popular configurations. Whatever your configuration, the operating system will see your multiple drives as one large drive. For example RAID1 mirrors data, across two identical drives, so if you have two 250GB drives in a RAID1, Windows will only see 250GB. RAID5 requires at least 3 drives and writes the data across all three, so if you have three 250GB drives, Windows will only see 500GB. RAID5 tends to be faster in writing as it can write more data on 2 different drives at once, verse a RAID1 where the same data is being written twice. I hope that clears some stuff up.

If you haven’t formatted your disks yet, or installed anything of consequence, consider a reformat.

[When formatting your disk] On the “File System Settings” page, select NTFS for the File system, select 64K for the Allocation unit size and type “SharePoint Databases” for the Volume label.  Click the Next button. Changing the allocation unit (i.e. cluster) size from the default 4K to 64K is absolutely necessary at this juncture. This setting cannot be changed later without reformatting the disk! Why is 64K considered a best practice? Because SQL Server reads and writes data 64 KB at a time. Increasing the cluster size reduces fragmentation and the number of times disk space needs to be allocated, which can improve reading and writing speed. Since these disks will be used solely for storing SQL Server files, I’m not concerned about the slack (i.e. wasted) space that can occur when the cluster size is too large.

From  http://chrisbarbin.wordpress.com/2014/01/19/sql-server-2012-vm-notes-part-3/. Thanks to Chris for the tip!

The database

The database is a common area where performance hits are most prevalent. SharePoint is database intensive, about 95% of SharePoint is stored in a SQL database: files, images, videos, pages, content, user profiles, etc. It’s important to have a happy and health SQL database running.

Things to do or check

  • Make sure databases are running on RAID5 or RAID10 partitions. These two RAID configurations provide the fasted throughput for constant read/write activities. If you’re on a RAID1, consider moving your databases.
  • Check your drive’s free space. Make sure you have several gig available. If it gets too small SQL still start acting funny, and slower. If it get to a gig or under it’ll stop working all together.
  • Check your content database size and growth properties in SQL Server Management Studio. If these are set too low, then SQL has to resize itself a lot and that can slow it down.

To check your database properties

  • Connect to the database server using Microsoft SQL Server Management Studio.
  • Right click your database and select Properties.
  • Go to Files on the left.
  • You should now see 2 rows for your database.
  • Select the one with a File Type of Rows Data.
    • This represents your database file, where all the stuff is stored.
    • Note the initial size. If this site is new and young and doesn’t have much on it, set that initial size to an estimated file size for the future. If it’s an existing content database with a load of data, don’t worry about the initial size. By setting this above and beyond, it’ll resize the database now, instead of later.
    • Note the Autogrowth setting. Modify this to be 25%-50%, higher if the site will be loaded up with a lot of data coming soon. Again, this will resize the database once and cover a lot of data. The default property values requires SQL to resize frequently, and therefore imped performance of the server while it’s resizing.
  • Now select the File Type Log.
    • This represents your transaction logs. As items are being written to and from the database file, the transaction log keeps a log of it, in the event of a database failure, you can technically restore back to the last transaction. This log can grow exceptionally large depending on traffic on your sites.
    • Following the same rules as above, the initial size should be around 25% of the data file initial size.
    • Set autogrowth to 25%-50%, again depending on amount data that will be loaded.
  • If possible, these two files should be on different physical disks, not just different logical disk partitions.
    • This might be difficult as most servers have a single RAID container. In larger implementations, if you have additional drives available, separate the data and log files. Moving them onto their own physical disks or disk arrays, the drives can spin independent of each other, therefore improving performance.
  • Next, go to Options on the left
  • The Collation option should be Latin1_General_CI_AS_KS_WS (SharePoint’s preferred setting).
    • This setting handles how the database treats some finer settings like case sensitivity. If the database was created by SharePoint, you’re fine. If a DBA or someone else made the database first, this might not be set correctly.
  • Click OK.
  • Expand System Databases on the left, and do the same as the above to the tempdb.
    • The tempdb is a temporary location data is handled while it waits for other processes to finish. The tempdb prefers RAID10 over all.

Another consideration for improving database performance is RBS, I go into greater detail on RBS in this post.

SharePoint Logs

SharePoints logs are usually on the drive it was installed on, and in most cases that’s the C drive of the server. It’s recommended to moved these logs to another partition, and preferably different drives altogether.

  • Go to Central Administration > Monitoring > Configure Diagnostic Logging.
    • Note the top part, Event Throttling. This specifies what SharePoint will log. If any of these have been changed from default, they’ll be in bold. Your ideal settings for all of them are
    • Least critical event to report to the event log: Information
    • Least critical event to report to the trace log: Medium
    • In the next section, Event Log Flood Protection, make sure that’s checked.
    • And in Trace Log section, feel free to move the logs to a different drive to either free up space on the current drive or to a unique drive. You may limit the amount of days or space the logs use per your preference.
    • Click OK.

Large lists are slow

If you have lists that have several thousand records in them, those lists may run slow as you load views. Other lists which use your large lists in a lookup field may also slow down as it has to process all of the records.

You can enable indexing on your SharePoint fields within your lists to improve lookup speeds and page loads. Go to your list settings, then under the column list is a link Indexed Columns. Select your columns that you will be searching against, creating filtered views with or using in lookups.

Keeping it alive

This issue is less of an issue as it’s just how technology works. If SharePoint (rather the services running SharePoint: IIS) runs idle for a set amount of time, usually 20 minutes, the services will go to sleep and cache is cleared. As a result, when a user goes to access SharePoint, the services have to start up, and build the cache again. This can set an initial load to take several seconds, which to an end user can be forever. There’s a neat little application available at http://spwakeup.codeplex.com/ which tickles your sites to keep them awake.

Funky Code

How many custom features are you using? Does it seem to slow down when they’re in use? This might be hard to determine as a highly used site might be hitting custom code left and right. If you’re a developer, or want to tell your developer something, check these out

  • Make sure all SPSite and SPWeb objects are disposed of cleanly. There is a free tool which will check your compiled solution for you, download SPDisposeCheck here http://archive.msdn.microsoft.com/SPDisposeCheck.
  • If you’re adding new items to a list that has thousands of records, don’t simply perform SPList.Items.Add(). This will actually load all your items into memory, then create a new record. Instead, do something like SPQuery qryEmpty = new SPQuery() { Query = “0″ }; SPList list = web.lists["Name"]; SPListItem newItem = list.GetItems(qryEmpty).Add(); This will load an empty results set first, then give you a new item. Also, check out the note above on Large lists are slow.
  • Check the status of workflows, ensure they aren’t looping. This is a common issue with developing custom workflows. If the workflow updates the item the workflow is running on, it’ll loop because it was updated again. And around and around you go. Make sure to include some validators in your workflow to ensure you want to perform the update, not just update it.
  • Check out http://bestofcyber.wordpress.com/2008/10/16/best-coding-techniques-to-improve-performance-for-sharepoint-applications/ for some more information on improving your code.

Am I missing some? Please, leave a comment and let me know!!

Storing your SharePoint files outside of the database (RBS)

A new feature provided with SQL Server R2 is the ability to save your BLOBs (binary large objects)(the files you upload into SharePoint) to be stored outside of the content database and in the server’s file system. This functionality is called Remote BLOB Storage (RBS). I’ve had a few of customers ask for this on previous versions. Now that it’s possible, I went ahead and did it on my development box and it worked really well.

With RBS enabled, SharePoint stores all of the files in the specified file system, but serves everything up through SharePoint. The user experience and customizations will not change, users won’t know the difference.

So now that I can do it, I question why. Why store files outside of the safety and comfort of the SharePoint Content Database and in the Windows File System which is wrought with peril?

There are some pros and cons. Keep in mind, this is a per content database setting, not per library or site. If you see a need for enabling RBS for a specific need, consider creating a new site collection in a new content database.

Real Quick Technical Overview

Remote BLOB Storage utilizes SQL server’s FILESTREAM feature. This feature is independent of SharePoint and RBS, and is required to be installed and configured before installing RBS. RBS is a provider which allows applications, like SharePoint, to effectively and efficiently use FILESTREAM. Once everything is enabled, when a document is uploaded to SharePoint, the document is saved to the file system as a unique number and the SQL database is updated to reference that file when we come in looking for it later.

The Benefit

RBS was made to offload your BLOB data to a storage device instead of taking up the expensive storage on your SQL box. You can host your BLOB store on a storage area network (SAN) and alleviate performance and storage from your SQL server. If your SQL server is slower/older, switching to RBS might improve performance significantly.

Of course, there are costs and significant considerations to weigh before jumping on the band wagon.

Operational Costs

RBS increases operation costs as your IT dept has more to do and monitor with RBS. RBS will slow down tasks like backup and restore, upgrading to a new version of SharePoint, migrating sites to another environment, etc. There is in affect a second repository for your SharePoint database which IT has to monitor and manage.

Database Size

This was an obvious one I thought, but it really isn’t. Of course, when RBS is enabled, the database size won’t grow with each file that’s added. However, Microsoft’s recommendations for database sizes in SharePoint will include the size of the database and the BLOB store, so you’re not escaping their size recommendation. Using RBS will however allow you to use SQL Express past its limitations of 4GB per database, as the database won’t get that large as fast.

Performance

A small disclaimer: I have not performed any benchmark testing on these concepts. This is all according to Microsoft’s article RBS Planning. I am planning to perform some basic tests on my workstation soon, but the reasoning is sound.

If you’re working with a majority of larger files (over 1 MB) using RBS can improve performance. If you’re working with a majority of smaller files (less than 256kb), performance can be decreased.

If the files are going to be more frequently read but not revised, RBS improves performance. If files are going to be revised frequently, then RBS will decrease performance and the BLOB store will increase significantly in size as it creates and manages versions for each document.

With your files being stored in the file system instead of SQL, you remove some of the SQL overhead and memory usage when requesting the files. This allows SQL to process other important tasks and queries. Also, accessing the files from a file system is faster than pulling it out of a SQL table.

Check out High-performance FILSTREAM tips and tricks by Paul S. Randal, he covers a lot of additional tips for improving performance when enabling FILESTREAM (which is the technology behind RBS).

Maintenance

Maintenance efforts are increased using RBS. SharePoint will automatically flag BLOB data it no longer needs, however it’s not removed until the SQL Server RBS Maintainer tool is run. Fortunately this tool can be set on a schedule (automatically added during install) and run frequently to keep your BLOB store clean.

Maintenance on the SQL databases will perform quicker since there isn’t as much data to process. It’s important to keep the RBS Maintainer running as it keeps the database and the BLOBs sync’d and happy.

Backups via SharePoint or SQL will include the BLOB store data automatically. This is where the performance will be hit, it will take noticeably longer to backup an RBS enabled content database verse a normal database.

Other Considerations

  • Cannot use System Center Data Protection Manager 2010 to backup
  • Does not support database mirroring
  • Encryption occurs on the file system
  • Supports iSCSI drives, Network Attached Storage (NAS)
  • Does not support Direct Attached Storage (DAS)

With all that said, you gotta know what you’re doing with your sites!

My initial reaction, and I know many others think the same, is AWESOME let’s enable it!! STOP! Take the time and figure out if this makes sense for you first, and please check with your IT department, as their tasks will increase.

If you’re using your site collection as an active file share, not just posting a file once but actively editing and managing documents, you’ll need to consider additional storage for the BLOB store and consider any performance decrease as files are actively updated.

If you’re going to have a passive file share, like posting documents that are only going to be consumed and maybe edited annually the BLOB store should be minimal and performance will be improved for your end users.

For example, I uploaded a 2,289KB Word file to my library and it appeared in the BLOB store. When I first uploaded it, a copy of the file is made, then SharePoint immediately prompted me to specify certain field/metadata values, and I didn’t, I just pressed OK, which technically revised the original copy. I then changed my mind, went back in and added metadata. That changed the version of the document, therefore creating another copy in the BLOB store. I then opened the Word doc and added the word test to it. Saved it back, that created another copy and increased the size a hair. See screen shot below to see the same file listed in the BLOB store 4 times within 1 minute of uploading it.


What do the GUIDs mean?

If your site collection is going to target large files which won’t be edited frequently, then RBS is a great solution and performance will be greatly improved. For example, a photo site, videos, document repositories, record centers, ECM, etc. would benefit from RBS.

Excerpt from Microsoft’s article

Most optimal use of RBS

Because RBS is a solution created for a specific set of conditions, there is an optimal use of RBS in which the benefits outweigh the costs. The optimal environment for using RBS is an environment where the following is true:

  • You want to store fewer large BLOBs (256 KB or larger) for read-intensive or read-only access.
  • The resources on the computer that is running SQL Server might become a performance bottleneck.
  • The expense of high-cost drive space is greater than the expense of increased IT operations complexity that might be introduced by using RBS.

Least optimal use of RBS

RBS is not a good solution for all environments. The costs will outweigh the benefits most of the time. The least optimal environment for using RBS would be an environment where the following is true:

  • You want to store many small BLOBs (256 KB or less) for write-intensive access.
  • The resources on the computer that is running SQL Server are not a performance bottleneck.
  • The expense of increased IT operations complexity that might be introduced by using RBS is greater than high-cost drive space.

Other resources