CloudPrep 2014 Development Update

I wanted to take a few minutes today to talk about what we've been doing since late January in regards to CloupPrep and the PowerShell commands for file migration and management of SharePoint Online.

First thing I can say is that one of our most difficult choices was in choosing an e-commerce platform and licensing API to use for our product. Even though we plan to keep our licensing fairly simply, we wanted to have options for future products and well as many of the items we also sell through our partners.

This turned out to be more challenging than I imagined, but we have settled down on using Fast Spring and LogicNP Crypto License. Perhaps in some future post I will talk about those more from a software developer's perspective. What I can say today is that it will be at least a couple weeks before we can get a working prototype of the licensing server and the store online, and so we have had to push back release closer to the end of March or early April, mostly for that reason.

Meanwhile, we have been developing features for the different editions of CloudPrep 2014. Progress on that front continues at a rapid pace and I am pretty satisfied with the way our tools are maturing.

When we decided to produce this software, we planned to release the lite and standard editions first and follow up with premium and professional features later this spring. I was a bit surprised to see that where we are putting our development efforts, probably all four editions of CloudPrep will be available at one time.

Now for the geeky stuff. Here's some of what's been happening as we've been building.

Features we've essentially completed:

  • Upload an entire folder or specific set files to document library
    We've tested that these commands will work against network drives and UNC paths. Take that, OneDrive!
  • Preserve metadata about the local file system that the document was uploaded from
  • Create and Modified dates on files are preserved, though we did find that with larger files there are limits to what we can accomplish here
  • You can specify the content type for uploaded files, root folders, and sub-folders - including Document Set and its child content types
  • A bunch of other random stuff including commands for manipulating SharePoint lists and reports to make sure that file uploads won't exceed SharePoint limits


We noticed that Office 365 throws us a lot of connectivity errors that we don't normally see in on-premises SharePoint environments. If you've been trying to copy files using their standard UI or using OneDrive, some of these errors might be hidden from you. However, they're readily apparent if you're using Web Folders (WebDAV) or Client Side Object Model to connect. We see unexpected dropped connections quite often, and certain upload methods will time out on files that are too big and required some fun workarounds. There are different methods needed for files under 2MB, under 35MB, and larger.

Our path was also complicated by the fact that on certain Office 365 sites, our rights come from delegated admin privileges. This is the preferred way that consultants get their rights to help clients manage SharePoint Online, so we figure a lot of folks who are interested in CloudPrep are seeing this phenomenon as well. When you log in with delegated admin to a client's Office 365 site using the credentials from your own Office 365 account, you sometimes see the access denied page; login again a few seconds later, everything is fine. Our code had to expect and handle this contingency.

Another thing that we did not expect is that we're seeing some reasonable evidence that Office 365 uploads are being throttled. Most of the time, file transfers seem to be limited to about 300KB/sec; there are days when the transfer speed is even slower than that, sometimes by half. As such, it is difficult for us to estimate file upload times, and we're having to improve our algorithms to take these fluctuations and sea changes into account.

As for the cause, we can't say if this is something Microsoft is doing, or if it comes from the erosion of net neutrality. We do wonder if Comcast or other providers may be limiting traffic to Office 365 in order to give their own offerings a competitive advantage or just to control their own costs. I expect we'll be doing some tests in the near future, and we've been kicking around some ways to circumvent these bandwidth caps - at least partially. One test we did in January showed that if we took half our files to a different physical location, we were able to upload them to SharePoint Online in about half the time it would have taken if we'd uploaded them all from one server.

One thing that became clear during early development was that the disconnected nature of cloud storage was going to introduce multiple random problems along the way. As a result, in any large set of documents to be moved to the cloud, there would be some which for one reason or another may not be successfully copied. We started by trying to get this failure rate as low as possible, down to less that 0.25% of files in most cases. We did a lot of work in early February to improve the code and reach this threshold.

Even so, we needed to be able to easily run multiple passes on any file copy operation and track the results. Our first prototypes had to crawl the Document Library in SharePoint one folder and file at a time. This proved to be incredibly slow, and it quickly became apparent that we needed to be able to gather status information for thousands of files at a time if we wanted to hone in on only those which required an update from the local copy. This is something we added to the code base about a week ago, and we're now in the process of replacing some of our early code to use the new file comparison analytics logic.

As a side note, a bevy of SharePoint management features found their way into our PowerShell library simply because we had customers who needed them in short order. For example, we now have the ability to take a View from any SharePoint List and make a copy of it in the same List or a different one even on another SharePoint Site. Of course, one must be very careful with this kind of power, since creating Views with field references that don't exist in the List will certainly break the View if not the entire List itself. When we've added sufficient safety checks, we'll open the capability up as part of the CloudPrep product.

This week, we introduced the concept of using a hash algorithm to test whether files in SharePoint match those on our local drive. Use of a hash in addition to checking the file size and date stamps of a document ensures that the document has been uploaded into SharePoint and that it has not been corrupted in the process. We developed this ability in order to add credibility to Office 365 migrations where we may be moving hundreds of thousands or even millions of files, and we need to establish that the migration process has been completed satisfactorily. This capability can also be used to perform duplicate file detection, and we may develop a follow on product or feature to do just that later on.

Next week, we're planning to work on some important features that we feel are a must for getting this product to where we want it to be.

The first is the make sure that we can translate between Active Directory permissions on the local file system and users in SharePoint. The primary purpose here is to preserve meaningful data for Created By and Modified By fields in SharePoint; this is something we can't do yet. As part of this process, we'll be introducing PowerShell commands to add new users into SharePoint sites and manage groups. For most customers, this is probably of limited use. However, those with several hundred users or groups to manage will find it much easier to deal with these via PowerShell instead of using the SharePoint admin web pages. For consultants, it will make migrations faster by speeding up the time it takes to implement the security configuration. Our goal here is to lower the cost of our migration services.

The next things we do after that will be:

  • Download documents from SharePoint to the local drive
  • Assign metadata from CSV file as you upload documents
  • Flatten a folder structure as you upload it.


These are harder to do than you might think. I'll post more on this in coming weeks, including our challenges and progress updates.

Anouncing CloudPrep 2014 Migration Toolkit for SharePoint Online

We do a lot of Office 365 migrations. Most of these are for businesses with fewer than 50 employees. This should surprise nobody except maybe Microsoft, who seemed to be slow to realize that their cloud platform would have the most appeal to companies with limited budgets – or that most jobs in the US are provided by small businesses. Go figure.

Over the years, I’ve written several times about the challenges of moving from a conventional file store to Office 365. Fact is, it’s just not simple to do. It really makes sense to have an experienced IT professional help you make the move. I like helping customers make the switch, but doing so has presented interesting challenges for my business that I’m sure other SharePoint consultants share too.

Firstly, there are great third party tools out there for migrating files. We often use ShareGate and Content Matrix from MetaLogix. MetaVis is another great company that has great tools with lots of features. Fact is that even though these tools are great, they are also quite expensive. They’re feature rich, so really knowing the tool is a skillset of its own – and it makes good IT people hard to find when I need them to do a job. We also run up against serious limitations when trying to use these tools; sometimes we cannot find a way to use the tools to migrate the files in exactly the way we want to.

Second, some of my client already have a part-time IT person or managed services company that helps them service their PCs and on premises servers. Traditionally, we’re a SharePoint consultancy and we never set out to try and replace other IT folks; they need work too. They have the relationship with my customer, and the local presence needed for that on-site work. Over the years, I’ve seen that customers prefer to have their own local IT provider for most small requests. We needed to find a way to coexist with these other businesses in a way that would benefit us both.

Back in 2012, at the behest of a marketing consultant (who gave me lots of advice that was either bad or I couldn’t follow it at the time) I created a small tool called CloudPrep. This tool wasn’t much; I never had much confidence in it and so I never really promoted it. But, it did the work of renaming files that SharePoint didn’t like, and combined with WebDAV it was enough to make getting 20 to 50 GB of customer files into the cloud in a few days’ time. I released it into the wild, and CloudPrep has been getting downloaded a few times a week – mostly by other Office 365 consultants to my chagrin. Lesson learned and another checkmark for finding a way to compete with other IT providers; there are more of you than there are of me!

One problem I’ve noticed is that Office 365 migration budgets are small – I mean really tiny! That’s weird when you consider that for a 25 person company the ROI could be hundreds of thousands of bucks. But, we have been in an economic slump for something like 5 years now. I guess that takes its toll; even if you knew it would make you a thousand dollars next month, you can’t spend $100 today unless you have it to spare. Some companies are reluctant to spend even a few thousand to plan and execute.

There are a few tools that are in the “beer money” range. I tried FilesToGo once – and only once. It lacked some features that seems obvious to me, but made my client extremely angry. It didn’t have a lot of options either, one size fits all. I won’t discourage anyone from using it if it meets your needs, but I’m not going to risk my relationship with my clients on it. I am honestly surprised that after all this time, there’s nothing else in its price range.

I guess you could say that I’ve gotten fed up with this situation. Yet another migration we had to do where the current tools on the market couldn’t meet our needs for the client’s budget. That story gets old.

So, the boys in the lab and I finally built our own!

Announcing CloudPrep 2014! Forget everything you ever knew about that crappy tool we made back in 2012, because this is completely something at a whole new level.

CloudPrep 2014 is not one of those big expensive tools with a fancy GUI. It’s a set of PowerShell command-lets that work with SharePoint Online and your local file system. These commands and the sample scripts provided with them are designed to empower IT people and make migrating files to and from SharePoint Online a piece of cake.

These tools don’t replace an IT person or their experience. You’ll still need an experienced consultant to tell you how to organize your files, use metadata, overcome or avoid SharePoint Online limitations, and of course actually use the tools. You needed all that before anyway. The difference is that now much of this can be provided by your own experienced IT staff; or if you’re an IT consultant yourself, you can use our tool and make your small-business and small-budget migrations a breeze instead of a quagmire.

Our commands fall into basic categories: planning, preparation, file migration, and SharePoint management. We’re still putting the finishing touches on the product now. We’re hoping to have the Lite and Standard editions released to market sometime in February, with the Premium and Professional versions available as soon as March or April.

In the meantime, please take a look at our feature matrix and proposed pricing structure. There’s still time to collect some feedback. So, if you have a feature you’d like to see that isn’t here, then leave us a comment and let us know. Even if you don’t add a feature by the launch date, we’re planning to add even more features later. We’ll entertain any reasonable suggestion – except charging more for the product.

Like what you see and can’t wait to try it out? Contact us and I’ll give you a 15% discount if you purchase during the early access period.

Edition->Feature        Lite   standard Premium Professional
Release Date   Feb  Feb  March  April
Proposed Price Free $285 $576

$1,092

+$300 Per Tenant>2

Number of Office 365 Tenants Unlimited Unlimited unlimited Unlimited
Numbre of Site collections Unlimited Unlimited Unlimited Unlimited
Requires powershell 2.0 or higher Yes Yes Yes Yes
Requires Sharepoint client connectivity Yes Yes Yes

Yes

1 year support and Updates

(renewable Annually)

  Yes Yes Yes
Supported OS: Windows server 2008 or 2008 R2 N/A Yes Yes

Yes

Supported OS: Windows XP N/A   ?? ??
Supported OS: Windows Server 2003 N/A   ?? ??
Planning and Reporting        
Sizes and Numbers of items by folder, extention, ect. Yes Yes Yes

Yes

 

Check for Potentially Illegal file types   Yes Yes

 Yes

Folder and File Path Length Checking   Yes Yes

Yes

Permissions Checking for Local Files     Yes

Yes

Target URL Length Check Report     Yes

Yes

Upload Time Estimates      

Yes

File Preparation        
File Renaming for Illegal charaters Yes Yes Yes Yes

File Renaming for Illegal Paths

(_files,_forms)

Yes Yes Yes

Yes

Preserve Author and Editor for uploaded Files

  Yes Yes

Yes

Check for and Automatically ZIP files with illegal extentions (EXEs, Ect.)

    Yes

Yes

Check for and Automatically ZIP "_files" Folders

  Yes Yes

Yes

Migrate and Manage Files

       

Supports Network Mapped Drives

yes yes yes yes

Supports Network UNC Paths

yes yes yes

yes

 

Upload Entire Folder to Document Library

Yes Yes Yes

Yes

Upload Specific File to Document library

  yes Yes

Yes

Download Document Library to Folder

  Yes Yes

Yes

Download Specific File

  Yes Yes

Yes

Warns if Source Exceeds 5,000 items

  yes Yes

Yes

Warns if Target URL length Too Long

  yes Yes

Yes

Specify Content Type for Uploaded Documents

  Yes Yes

Yes

Specify Content Type for Top Level Folder

    Yes

Yes

Specify Content Type for Sub-Folders

    yes

Yes

Support for Documents Sets

     

Yes

Flatten Folder Structure with duplicate filename handing

    Yes

Yes

Flatten Folder Structure at 1 or more levels deep

     

Yes

Convert Folder Names to Metadata Fields

    Yes

Yes

Create Source URL Field for Uploaded Files

    Yes

Yes

Create MD5 Hash Field for Uploaded Files

     

Yes

Export Metadata to CSV File when Downloading Files

     

Yes

Synchronize of Local and Cloud files using File Modified Time

    Yes

Yes

Synchronize of Local and Cloud Files using File Modified Time+ MD5 Hash

     

Yes

Automation Features

       

Powershell command-lets

Yes Yes Yes Yes

Unattended Execution

  Yes Yes Yes

Sharepoint Management &

Development      

Create and Edit SharePoint Users

  Yes Yes Yes

Set Common Properties for Lists and Document Librarys

  Yes Yes Yes

Create and Edit Columns in Lists and Document Libraries

  Yes Yes Yes

Create and Edit Views Lists and Document Libraries

    Yes Yes

Copy a view to same or Different Document Library or list and site

    Yes Yes

Import and Export Site Columns

    Yes Yes

Import and Export Content Types

    Yes Yes

Import and export views

    Yes Yes

Add, Remove users and Groups, Permission Sets

    Yes Yes

 

CloudPrep Lite
This edition is a good fit for small file migration needs and try-before-you-buy. You can use it to do basic reporting on the structure of your files, rename files that are known to cause problems during migration, and upload folder structures to your SharePoint Online document libraries. In most cases it has a 99.7% or better success rate, and it produces a handy report so that your remaining files can be uploaded manually.

CloudPrep Standard
This edition includes a standard set of features designed to help you move files into Office 365 with a minimum amount of difficulty. You can upload and download large file collections without having to stand by the computer, perform multiple upload/download passes, and specify a default content type for files. Run it from anywhere, including various versions of Windows Server. We also include some additional pre-migration reporting tools that help to identify problems before you migrate your files.

CloudPrep Premium
For the seasoned SharePoint admin or IT professional, this edition includes features that will help you get the most out of Office 365 in the cloud. We include even more reports to give you a 360 degree view into any potential file migration issues. The file upload tool includes a variety of features for setting metadata and flattening folder structures.

CloudPrep Professional
This edition enables the true Office 365 IT professional to handle migrations for multiple clients. All the features of the Premium Edition plus advanced content type features including support for Document Sets. It also includes the ability to create MD5 Hash file uploaded files, which helps in detecting duplicate files and in determining that if two files are not the same even when their date stamps match.

Lessons from the Field for Migrating to Office 365

Recently, I’ve talked a bit about how companies can save money in lots of places by moving to the cloud with Office 365, and I’ve also described some of the complexities involved in moving large file shares to SharePoint. Today, I’d like to take a few minutes to talk about some of the lessons learned on some of our Office 365 migration projects over the past several months.

Getting Good Information Up Front is A Challenge
As SharePoint developers, we’re used to working with the IT departments of larger organizations (say 500 to 5000 employees) as we develop solutions. However, with Office 365 customers, many times we’re not working directly with IT folks. The customer may have a managed service provider for desktop support, a part-time IT contractor, and some clients do not even have their own IT staff at all.

Needless to say, planning a move to Office 365 requires us to take stock of a great many technical details. It’s not surprising that folks outside of IT might miss the importance of the myriad trivial details involved.

But getting these facts wrong during the early stages can lead to incorrect estimates and costly mistakes down the road. It’s important to get the discovery right.
Here are some things customers should pay careful attention to when gathering information in the pre-project planning phase.

Basic Planning
Make a User Inventory
Know how many users you plan to have. We’re going to need their contact information, including phone and e-mail, because more than likely this information isn’t up to date in Active Directory. From there we can talk about what plans are best for your users.

Make a Workstation and Mobile Inventory
Know how many desktop PCs, laptops, and mobile devices you’ll be configuring. It’s also important to know what kind of mobile devices will be used and how many of each type.

Make a Server Inventory
Know exactly what servers you have, what operating system and version they run on, and exactly what purposes they serve (file shares, print server, domain controller, e-mail, etc.) If you do not know these things, you should consider paying for a 1 to 3 day evaluation to document all of your systems.

What Will You Turn Off After Migration?
Part of calculating the cost is understanding the benefits you get in return for it. If you’re not sure that a system can be fully disabled after moving to the cloud, that’s something we can help you figure out.

Will You Need Any Servers You Don’t Have?

For example, if you are synching Active Directory users to Office 365, you need a server to run this on - though it needn’t be very powerful. If you have applications running on servers that you otherwise want to decommission, you may need a server in the cloud to replace them. Likewise, if your security needs are high, you’ll want to have a CipherPoint Eclipse or F-5 Big IP running in the cloud in front of Office 365.

Domain Registration
You should verify that all your domain names are still current and that you have access to the DNS registration. We’ve occasionally had customers who have some older DNS names that were being used for e-mail aliases, and they weren’t able to migrate them fully because they’d lost the ability to manage the domain name. Check on these beforehand and avoid unpleasant surprises.

Remote Access
Some companies have VPN; this is ideal. Some do not and have to rely on clunky terminal servers or third-party services such as TeamViewer or LogMeIn. If you’re in the later circumstance or haven’t set anything up at all, we should talk about what is likely to cause issues for the folks doing the migration work, because not all of these services are created equal.

What’s Your Actual Available Bandwidth
Knowing if you have a T-1, cable modem, or DSL is helpful; it’s not the end of the story. We’ll want to perform some bandwidth tests at different times of the day in order to account for the connectivity that your company is already using. In general, migrations that have to be pushed to the evening or weekends will take longer.

Test for Equipment Bottlenecks
It’s also worth pointing out that some older equipment can actually be slower than the Internet connection can handle. Early on, we can do a trial run with a few files or a single mailbox in order to determine if there are going to be unexpected problems due to slow hard drives and outdated or overloaded servers.

E-mail Migration Planning
Know Your E-mail Server
Whether you’re using Exchange, Lotus, or some other server it helps to know what we’re dealing with. We’ll need to know how many users you have, how big are their mailboxes, and what distribution lists you’re using. It’s not unusual to find a few people in a company with mailboxes approaching 20GB (or bigger!). Anything at this size is going to take a lot longer to move than usual and that needs to be taken into account.

Great Firewall of Spam
For the mail server, the above is a good start, but not enough. You need to identify if you have an anti-spam appliance (e.g. Barracuda) or service (e.g. Postini) in front of your mail server. You probably won’t need it after moving to Office 365, but if you want us to make it a part of the move we need to know ahead of time.

E-mail Archives
Most people do not think about this, but Outlook Archives (*.PST) files do not move automatically to the cloud. One of the best approaches we’ve found is to copy their contents up into Exchange Online so that you’ll have access to them everywhere you go. If you’re using archives, it’s important to know this so we can take them into account when looking at mailbox sizes, migration plans, etc.

File Migration Planning
Make a File Inventory
Know where your files are, how big they are, what you will move, and what you might leave behind. Professionals have tools that can help to analyze your files and better determine the cost to migrate. However, these tools are only helpful if we have the opportunity to run them against all the files that will be moved.

Public Folders
If you use Exchange Public Folders, you will need to have those files copied down into a regular file share so they can be moved into SharePoint. Exchange Online does not support public folders, which have been phased out in recent versions of Exchange. When we determine the size of the file stores you’ll be moving, these files need to be included.

How Will the Migration Team Access Files?
Depending on the remote access method and the speed of your Internet connection, in some cases it may actually be faster to copy your files to a portable drive and FedEx them to us rather than have us try to copy them from your office. This also provides the fringe benefit of being able to split the migration up across multiple sites, which can make everything go faster.

Dealing with the Unexpected
Obvious, there’s no such thing as a crystal ball, and that’s even more true for IT. Aside from the things I talk about above that, little things can go awry during the project. It’s important to remember that migrating to Office 365 is a big change from the way companies used to work back in the 90s. Be ready to expect and deal with the unexpected.
Here are some things we’ve seen happen in the middle of a project that can really get things out of whack.

Slippage
Sometimes it just takes longer to move files or e-mail than it seems like it should. It really helps to know exactly what we’re moving in the first place, but if your estimate and schedule were written sight unseen before we had access to the servers, then probably there are baked in assumptions that may prove to be wrong.

Even if we did a 1 day triage visit at the start of the project, sometimes the technology can make fools of us all. I had one customer where most mail moved over fine, but then one user’s mail dragged on and on weeks on end simply because their outdated server would not provide it any faster.

Needless to say, schedule creep can be very disruptive. As a result, we’ve learned to base our schedules on being 95% complete – anything more can be managed as ongoing support and needn’t cause everything else to back up waiting for it.

Limits of File Migration Tools
To move files into SharePoint is not a drag and drop operation. Fortunately, there are many good products on the market, and the state of the art is constantly changing. But, these products are not what I’d call mature - partly because Microsoft keeps changing the Office 365 platform itself. Over the years, we’ve seen file migration tools for SharePoint Online that don’t copy the date stamps on your documents, tools with poor or quirky support for Document Sets, and tools with draconian restrictions on the size of files that can be copied.

If we are copying a large volume of files, it is not uncommon that we may need to do a test run and then start over. We try to account for this in our estimates, but it’s not a perfect science. Tools are great, but if a tool or product does not get the results we want, we may have to switch tactics. This is not a sign of the coming apocalypse. Be prepared for this to be a part of the process.

Limits of E-mail Migration Tools
If you are migrating from Exchange 2007 or better, Microsoft has some great built in tools to make this possible. There are good third-party solutions for other platforms. Each of these has its own limitations. For example, Microsoft tools may not do well on extremely large mailboxes. Third party tools may be more robust, but they will take almost twice as long because they have to copy from the source and then copy to Office 365, whereas Microsoft has the benefit of running their tool in the same local network.

Limits of SharePoint
SharePoint is like any complex software product; it has boundaries. There are limits on the amount of storage you can have in a Site Collection, and limits on the number of items you can effectively put in a List or Library. Our job as consultants is to come up with plans and designs that avoid as many of these as possible. Still, it’s important to understand that Microsoft is constantly changing Office 365 – usually for the better. There have been times that we tried out a particular approach for organizing content and then had to change tactics because one of our assumptions proved to be incorrect.

Here are some examples of fiddly details that have sometimes pushed us around:

  • Flat views don’t work in large libraries (> 5000 items) even though you’d think they should be limited to the current folder.
  • In large libraries, indexes must be created before items > 5000.
  • Document Sets can only have one view inside the Document Set itself.
  • Nesting folders within Documents Sets is quirky.
    You cannot easily change the look and feel of the “my-sites” part of SharePoint.
  • And many more…


Shifting Requirements
Migrating to Office 365 is a big change. Training and discovery are a part of the process, and so you might learn something about the platform that you did not know at the beginning.

Likewise, we may learn something about your business that was not clear at the start and this could cause us to change our recommendations. Stay nimble and flexible; these moments can be opportunities to improve rather than a cause of stress.

15 Things: a Day in the Life of a SharePoint Life Coach

Recently, we launched a new service called SharePoint Life Coach. This service was designed to be of value of customers who need help with SharePoint but have a limited budget they can work with to get the support they need. To help folks understand this service better, I'd like to describe what sets this service apart and some of the questions we answer in a typical session.

With traditional consulting, you the customer tell us what to do and then we tell you how long it will take.  We then run off and accomplish these things for you, and sometimes we work with you along the way. Some consulting is about making recommendations, some is about troubleshooting. In general, the focus is to bring you a finished product, whether that deliverable is a document, a working system, or a piece of code. Of course, all of this is billed by the hour, and having a consultant working full-time is beyond reach for many companies.

SharePoint Life Coach service differs from traditional consulting in a couple of important ways.

  • The customer sets the pace for sessions, based on their time and budget.
  • Sessions follow a semi-structured format, so that desired material can be fit within the allocated time.
  • Focus on consistency and results will emerge - the idea is to have regular sessions over an extended period that help ensure better results.
  • The deliverable is you - our approach is "teach a person to fish and they'll eat for a year".


As we bring new folks into the Life Coach system, one of the first things we do is to set up topics for that all-important first session. Sometimes the hardest thing is knowing where to begin. Many times, our customers come to us after just getting started with Office 365 and SharePoint Online. They quickly realize that SharePoint is a very complicated product, and that there is more to managing it than just pulling some levers on the Office 365 management portal web site.

Over time, we've found that folks are asking some of the same questions over and over again. On some topics, we start to feel a bit like a broken record. Though we've answered those question many times, the answer varies from customer to customer based on their specific story - for example the size of their company, tech savvy of staff, etc.  As a result, while there are common themes, there is no one-size-fits-all solution for these things. Thus, being able to tailor these recommendations to your needs is what having a SharePoint Life Coach is all about.

Here are some of the popular topics that people have asked for:

  1. What are some of the pitfalls that I should avoid while working in SharePoint?
  2. What training do my end-users need to work effectively in SharePoint?
  3. How do I keep SharePoint from becoming a mess?
  4. What is the right way to structure my SharePoint web site and sub-sites?
  5. When should I use a sub-site, a list, a library, a document set, or folders?
  6. I know folders are bad, but my users love them; how do we cope?
  7. Everyone misses the network shared drive. How can people work with files quickly in SharePoint?
  8. I want to use SharePoint as an Intranet for my company; what kinds of content and things should I put on it?
  9. What's the best way to structure users, groups, and permissions?
  10. I've heard of SharePoint governance. It turns out it was a 500 page document. Is there anything for small businesses that is like governance-lite?
  11. Should I buy a file migration tool, move my files by hand, or just hire someone to do it for me?
  12. Should I use an Outlook Shared Calendar or a SharePoint Calendar?
  13. Can I organize our list of customers in SharePoint?
  14. How long does it take for stuff to show up in Search? Why aren’t my PDF files showing up?

Why Choose Office 365 Instead of Buying Office 2013?

Back in August, I received a call from a new customer asking for a quote for Office 2013 licenses.

While I was looking for pricing, I came across an interesting article, Microsoft really doesn't want you to buy Office 2013. The gist of this seemed to be that MS has structured things to make buying Office 2013 unattractive.

How outrageous! But can it be true? And if it was ever the case, does this still hold water today?

I was really curious about the ROI of the subscription pricing vs. pricing conventional software. I decided to do a little napkin math. So, let's do some cost comparison and explore the return on investment from renting Office 2013 instead of buying it.

Comparing Traditional Office 2013 with Office 365


Firstly, we need to decide what we're comparing to what.

For my example, I'll stick to pricing for small business. This is where most of the interest in Office 2013 prices seems to come from - at least based on the calls I get here in the office. Microsoft refers to their small business discounted pricing as "MS Open for Small Business".

Also, I am going to stick to MS Office Professional Plus. You could save some money buying MS Office Standard, but there are a lot of components missing from the cheaper package. Since Office 365 is Professional Plus edition, we'll compare similar options here.

Office 2013 Professional Plus comes in two flavors, you can buy it with software assurance or without. If you buy without SA, you're basically saying that you want to pay the full price to go ahead and upgrade to 201x whenever that version comes out. If you buy with SA, then you'll pay about 50% more, and you'll have to renew the SA in 2 years, but your cost for the upgrade is covered by keeping the SA agreement current.

Here are our MS Office Professional Plus prices for MS Open Small Business: 

  • without SA is about $475*
  • with SA is about $740* 
    *Prices are from October 2013; if you need current prices, please contact us using one of the the links below.

Let's use an office of 25 staff as an example. This is pretty typical, although I've certainly worked with customers who have as few as 10 - or even 5 - employees.

If you consider that Office P+ with SA is going to cost about $740 per user, then 25 users is an $18,500 up front investment. That’s not tiny. Assuming a shelf life of 3 years, which is typical for MS software, you’d be looking at a monthly “budget” of $513.89. Say I took that budget and broke it up across the 25 users. Your cost will be about $20.56 per user per month.

Suppose you run the same numbers without software assurance, which is probably the better apples-to-apples comparison - since at the end of the product's useful life you'll be spending the full amount all over again. Your total up-front investment would be $11,875 for the fleet, at a monthly budget of $329.86 or $13.19 per user per month – compared to $12.00 if you subscribe to the license via Office 365.

Can that really be right?

I ran these numbers again at the wholesale prices that my distributor gives us. Even at that slightly discounted rate, I was only able to shave a few cents off the monthly cost compared to Office 365.

And these calculations assume that you can actually pay the monthly cost over time, interest free. If you finance the purchase obviously you'd pay even more.

Different Prices for Different People


One important thing to keep in mind here is that Microsoft will require you to buy a different license - and thus pay different prices - depending on who you are and what kind of organization you work for.

There are also different prices for with and without Software Assurance and different editions of Office such as Standard, Professional, and Ultimate. And then there's OEM pricing, which assumes you're buying a new PC to go with your software. This leads to the most confusing matrices of prices and options humanly possible!

Here's some more example pricing for Office '13 (P+ w/o SA) that I found from CDW and other major chains**:

MS Select Plus Academic $58.99 (CDW)
MS Business Academic $86.99 (CDW)
Product Key Card $379.99 (NewEgg)
Retail Box $399.99 (Office Depot)
MS Select Level D $379.99 (CDW)
MS Select Level A $479.99 (CDW)
MS Open for Small Business $495.99 (CDW)
MS Corporate Business $505.99 (CDW)
**Prices as of 11/11/2013 - YMMV

So, as you can see, the ROI of subscription software is going to be different depending on whether you work for or attend a college, are employed by a gigantic mega-corporation, or are part of the government. Everybody else is paying more. I'm assuming this is usually because smaller groups buy less volume, though the evidence doesn't always bear that out.

So which way should you go? The magic number appears to be $432. Taken over a 3 year lifetime, this translates to $12 a month. So if you have a pile of cash sitting around, and you can get Office for less than this price, you may want to just go ahead and buy it. Everyone else most likely does better paying for it monthly.

Some More Reasons to Choose the Office 365 Subscription Model

Assets, liabilities, and business property taxes


This is the distinction between assets, capital expenses, and operating expenses. Office 365 is considered an expense and will generally treated that way by accountants. I don’t know if you pay business property taxes, but here in Maryland we pay a tax on any software “asset”, whereas with Office 365 we write 100% of it off, no depreciation, and no taxes. Also, there's no need to worry about depreciation with expenses. (Note that some localities will let you depreciate 100% of the cost of software, so it depends on where your business is located).

Up-front Costs, Budget Approvals, and CAPX vs. OPEX
In the above example $18k is a sizable investment. If you have more than 10 workstations to license, your finance people may prefer it if you subscribe rather than buy. Otherwise, it may be a while before you could get the funding approved. It might even be CAPX, and nobody likes that.

Buying it All at Once vs. Pay as You Go
In the above example, if you truly need all 25 copies right now, your cost would average out to $320 or $520 per month over the 3 years you use the software. (There are a lot of reasons that you wouldn't want to keep running back to buy more licenses every few months in this way.) With Office 365, you can start with only 10 or 15 copies and easily add new licenses as you need them. This way, your cost would only be $120 (for 10x) or $180 (for 15x) month to start. On that budget, it's about the same as buying just one “boxed” copy of Office every 3 to 4 months.

Additional Cost to Renew SA
SA (Software Assurance) is a 2 year deal, not three. You’d incur an extra fee to renew the SA agreement at the end of the second year. And - many companies either forget to do it, which means paying full price for Office anyway the next time you upgrade it. Others forgo the SA pricing, and end up buying the software all over again later. Some folks pay for SA and then later decide they can’t afford to renew. These folks then end up using older copies of office for 4 or 5 years which ultimately affects productivity, drives up IT support costs, and that adds up to greater expenses down the road.

Same Price with Added Value
Finally, there are other bundled cloud services that make Office 365 even more worthwhile. MS charges $12 a month for Office Pro 365 which is a pretty advantageous cost, but the whole suite including Exchange Online e-mail and a SharePoint intranet (and public facing web site) is just $20 a month. Just moving e-mail service alone can save many thousands per year in server maintenance and licensing costs.

Where to from Here?


Whether or not you're sure that what I've described here is the right option for your small business, you can reach out to me and I'll help you figure out your individual ROI on Office 365.

When you're ready to go, we can help you choose the right combination of Microsoft Online Services tailored especially for you. I’d also be pleased to set up an appointment to walk you through creating your O365 account and setting it up.

If your needs are straightforward and you already know the number of seats you need, here are some links where you can start a free trial or purchase a subscription. These links include delegated administration from LMS, so we can help you get the most out of your account.

Office 365 ProPlus

30 day trial 25 users

Buy 5 users

Buy 10 users

Buy 25 users

Mid-sized Buisness Plan

30 day trial for 25 users

Buy 5 users

Buy 10 users

Buy 25 users

Enterprise E3 Plan

30 day trial for 25 users

Buy 5 users

Buy 10 users

Buy 25 users


if you're outside the US, want to integrate your Office 365 accounts with your current Active Directory, or have more complex needs that don't quite fit into the table above, please contact me with your specific requirements so we can create a custom solution that's right for your business.

Also, please note that there are significant technical differences between the Mid-size Business and Enterprise plans - and you can't readily migrate from one to the other - so ask us if you have questions about which one is right for you.

Well, that’s about all I have to have about buying Office – rent it; don’t buy it. It’s a no brainer.

How to Smoothly Migrate Files to Office 365 SharePoint Online

Many clients ask me what they can do to make their Office 365 migration into SharePoint Online go as smoothly as possible. Often, they're sincerely looking for a way to reduce the costs for everyone involved.

In part 1 of this series, I'll focus mainly on the technical complexities of moving files from traditional shared folders into SharePoint Online. In future episodes (that's what my shrink calls them) we'll take a look at things from the architectural and strategic perspectives.

First, let me say that Office 365 customers come from all sorts and sizes of business. Some have IT staff supporting hundreds of users. Others are just a handful of folks with one or two office administrators - who often work double-duty as the IT help desk. Whether you're big or small, IT savvy or technophobic, on the bleeding edge or upgrading from Windows 3.1, planning to use commercially available migration tools or making your unpaid intern Carl copy all the files by hand, there's something here for you in the lessons we've learned.

Communication Plans
They say that communication is key. Like any other project, this is absolutely true with a major migration like moving into SharePoint Online. There are lots of things that probably should go into every communication plan. In a future part of this series, we'll tackle the fuzzy stuff. For now, let's focus on some of the hard technical details that many project managers forget to include when they coordinate an Office 365 migration.

Locked Files
It is important for users to understand that files which are open are likely to interfere with the migration procedure. This can even include folks who have a network share open in Windows Explorer on their desktop. While it's possible to work around these issues, during critical times, it might be a good idea to encourage users to close unused folders and documents in Office.

Note this is especially true for files open in MS Office, as it not only locks the file but also creates an auto-recovery file starting with ~$ that can't and shouldn't be migrated into SharePoint Online.

Backup Window
Often users will want access to their documents right up until the last minute. Whether you're using ZIP files, Acronis, or some other strategy to do a last minute backup - really any backup method, it is important to let the users know after what point their files will no longer have a backup.

File Move Timing and Locations
If you're doing everything under cover of darkness, then one day folks will come into the office to find all their files have been moved. If you're migrating during business hours (my personal preference is to not stay away all night babysitting a file move) then some users will still be working up until the last minute. Either way, you need to communicate to the users when the files will move and where they are going. This is especially true if you're doing some reorganization - whether elective or forced by Office 365 limitations.

Remember to Include in Your Communication Plans:
When will the move be taking place, including regular updates
Instructions about closing windows or office apps - or logging out completely
Advice on when files will no longer be backed up
Information about who to reach out to if there seem to be problems after the move


Technical Gotchas

Permissions


File permissions are one of those things that we've found can create a lot of problems when migrating to Office 365. Even though you may be using an administrator account with high level access, there may still be the occasional file buried deep in a folder structure that maybe was copied from a user's desktop and dragged very limited permissions along with it. Or, there may be entire folders that somebody locked down - sometimes with good reasons long forgotten about.

Obviously, you can't do much with these files if you can't get to them. It can be even more frustrating to find this kind of issue late in the game, when they'll blow up a move operation or a ZIP archive that's only halfway done.

Illegal Characters in File Names
One of those things that can be really vexing about moving to Office 365 is the limitations on the characters you can use in file names. The restrictions on Windows file names are not as stringent as they are on the web. So many users will have files with ampersands, hash-signs, and other "illegal" characters. But, I've also seen really exotic characters such as the registered trademark and copyright symbols, long-dash, and other unusual stuff.

Here's something to consider. When you get into Unicode and upper ASCII, the list of invalid characters can be quite long. If you're screening against the list of known illegal characters, you might let one slip through. Consider checking against known legal characters rather than known illegal ones.

Why is it that Office 365 has these restrictions?
Well, a lot of it has to do with the way the web actually works. For example, characters like “#”, “&”, and “?” All have special meanings when they are used in URLs. When you upload a file to SharePoint using the web site, these characters get converted to special codes that are safe in URLs; this process is called URL encoding. However, the encoded filenames look like unreadable gibberish when they're viewed through Explorer View (WebDAV) and other places, so we try to avoid using them at all.

Still other special characters may be using something called Unicode that allows the display of things like Chinese and Japanese characters alongside English. Unfortunately, web addresses use older standards called ANSI and ASCII which only allows English characters (and a few extended characters that are implemented so inconsistently across all the different computer systems that we really can't rely on them anyway).
Oh yeah, and these restrictions count for folders too! And while we're at it, did I also mention that there are certain names you're not allowed to give to a file in SharePoint, and certain words you cannot end the file name with? 

Renaming these files by hand is extremely tedious; it's so tedious that I actually made my son do this as a rite of passage when he first joined us here as an intern at Liquid Mercury Solutions. Needless to say he was very excited to have software that would do this instead.

Fortunately, you can use PowerShell, download a tool, or buy any of several products that will take care of this step for you.  You'll want to have some input into how files ought to be renamed, and the configurability will vary from solution to solution.

If you hire IT folks to do your migration for you, then they should have at least one of these methods.  Here at LMS, we generally use a combination of PowerShell and products from MetaLogix and ShareGate to complete Office 365 migrations. Other good choices come from MetaVis, Dell Software (formerly Quest Software), AvePoint and others.

File Name and Folder Structure
But, file names alone are not the only problem. There are additional limits.

For example, ZIP files have a limit in how long any given file name can be, so good luck backing your files up if you go past this threshold. (Some of this can be avoided by using WinRAR or newer versions of WinZip instead of the built in Windows ZIP file utility.)

Windows also has a slightly longer limit of no more than 248 characters in a file name and no more than 260 characters for the full file path including folders. This can be problematic if the file was copied to a network location using a mapped drive, but you need to access it using the longer UNC file share name.

SharePoint itself has a limit that the URL pointing to a file, including the DNS name of the server and path to the document library, can't be longer than 255 characters. So woe to you with lots and lots of sub-sites - or long domain names like liquidmercurysolutions.com.

Blocked File Types
There are a number of file types that are blocked in SharePoint Online. This is done for security reasons, including DLLs and executable files (EXEs).

It's worth noting that Microsoft has sole discretion on what file types are blocked. I was recently very surprised during a demo I tried to upload an MSI file to show that it would get blocked - and surprisingly it worked! So check to see what the latest list of block files is before you start; this can be done from the Office 365 management portal.

Miscellaneous Poor Fit for SharePoint
In the course of evaluating content, you're likely to encounter files or even whole folders that just aren't a good fit for the cloud. For example, thumbs.db and desktop.ini files are great for making Windows UI more useful, but they serve no real purpose in SharePoint - and they can lock the file system which can complicate moves. Likewise, Office recovery files (those that start with ~$) or their analog in WordPerfect (*.TMP) can be a nuisance since they're marked as hidden and system files.

Large files like AVIs, ISOs, and ZIPs may be a poor fit for SharePoint. While technically you can put up to a 2GB file into SharePoint Online, some folks just don't have the bandwidth to put the files into the cloud and also have them be useful. Even in circumstances where you're just archiving the file, you should consider that you might spend a very long time uploading it only to find that the operation will time out and the effort will be wasted. You best bet is to test your bandwidth, then decide where to draw the line.

A folder full of executable programs - and probably all the non-executable stuff in that folder too - is a poor fit for SharePoint. Use your own judgment, and don't be afraid to exclude certain parts of the folder structure altogether if they seem to support running software.

  • Before You Start: File Structure Check
  • Permissions
    • Take ownership of all the files you need to archive and/or move.
    • Make sure you have permissions to all the files by overwriting the current permissions.
  • Illegal Characters
    • Avoid &, ?, #, and others - get the complete list here
    • What tools are you using to rename files?
    • Check folders as well as files
    • Legal characters or illegal characters
  • File Types / Filename Extensions
    • All files must have an extension
  • Check for blocked file types - get the complete list here
    Length of Individual File Names
    • Beware any single file with a name longer than 60 characters; here there be dragons!
  • Best bet: try to keep them 50 characters or less
    Length of Folder Paths
    • Can your backup solution handle long file paths?
    • Check the windows path of all files to ensure it doesn't go over 248 characters
    • Check the intended destination URL for each file to ensure it isn't longer than 255 characters.
    • Best bet: try to keep them under 200 characters
  • Misc. "poor fit" for SharePoint
    • Delete desktop.ini, and thumbs.db files
    • Move or otherwise deal with any ~$*.* or *.TMP recovery files
    • Test your bandwidth to see if it supports very large files
    • Consider what to do with very large files such as AVI, ISO, ZIP, etc.
  • Optional, if using ZIP with legacy support:
    • Make sure your archives will not exceed the 4GB limit.
    • Make sure your archives will not have more than 65,535 files/folders total.

Office 365 and SharePoint Limitations

As if all this were not enough to consider, you're going to have a number of limits and options on top of this when moving into the SharePoint Online platform.

A lot of these impact choices about site and information architecture, which is where Liquid Mercury Solutions comes in to help our customers. Even if you're using custom tools, it's often best to have a SharePoint specialist who can help you choose the right way to organize your sites and files.

Here are a few of the not-so-fuzzy limitations that we often have to work around.

Begin Your Start: Check for SharePoint Limitations

  • User quotas - if you're not a site collection admin
  • No single file can be > 2 GB
  • No more than 5000 items in a single folder
  • No more than 100GB in a single site collection ("supported limit" is 200GB)
  • No more than 30,000,000 items total in a single document library, including all documents and folders
  • SkyDrive Pro has its own weird limits which are different


Other Best Practices


There are lots of other best practices to take into account when moving to SharePoint Online - so many that we'll have to save them for another episode in this series. So, tune in next time!

Meanwhile, here's some more information for you. We recently saw a very thorough presentation on this topic given to the Baltimore SharePoint User's Group by my friend Mark McGovern from MetaLogix. And, our colleagues over at ShareGate have written a good article that starts to scratch the surface on some of the complexities involved.

If you'd like to purchase tools to help with your Office 365 migration, or if you want a professional consultant's advice please contact us and we'll be happy to help you. If you find these tips helpful, and want to give migrating to SharePoint Online a try on your own, please consider joining our free support network. Whatever path you decide to take, we wish you the best of luck!

--------------------------------------------------------------------------------------------------------------------------------------------