SharePoint

Do we still need Structure in SharePoint?

I love Delve, it’s a great way of quickly accessing documents I’ve been working on recently and is particularly useful in when collaborating with people across our business. It is particularly useful when someone moves on and you need to find that document they were recently working on.

I’ve had conversations with a wide range of people over the past few days about the use of Delve and where you still need to structure content to find it in traditional ways. In my opinion structure is still very important for several reasons:

  1. Compliance – documents that must be keep for compliance reasons benefit from structured storage. It allows content to be easily identified, grouped and makes it easier to apply policies.
  2. Archive (High value) –  where you have high value content, the ability to classify the documents, track the approval history and ensure you know which version is authoritative can be important.
  3. Security – structure makes security easier to apply, maintain and audit.
  4. Third-party integration.

You may also have documents that don’t have these requirements. It is still important to think about the life-cycle, particularly what happens if the owner of the documents leaves the organisation and how do I find documents that have been cold for a longer time period but still have value.

Why don’t I just store everything in OneDrive for Business? For all the reason listed above and because we want to ensure the documents are retained over the long term.

Is traditional Site Structure and Search dead? Delve improves user experience and making content more discoverable but it doesn’t suit every use case. Delve also helps address issues such as content stored in Office 365 Groups, OneDrive for Business and other services that work with Office Graph.

What are your thoughts on Delve and SharePoint Structure?

Data Channel : Managing SharePoint Databases

I was recently interviewed for the Data Channel about managing SharePoint databases on SQL Server.

In the interview with Nagaraj Venkatesan (SQL Server MVP from Singapore) I cover many of the common questions DBA’s have about SharePoint including:

  • Things that make SharePoint databases unique
  • Capacity Planning
  • Remote Blob Storage (RBS)
  • Backup / Recovery
  • SSRS Integration
  • Patching

You can download a copy of my presentation from #SQLsat614 which covers the basics for DBA’s and is also useful for SharePoint Admins.

Thanks to The SQL Saturday team for giving me the opportunity to present and Nagaraj for the interview.

 

 

SharePoint for DBA’s

Yesterday I presented at #SQLSat614 in Christchurch. SQL Saturday was a full day event with 20 experts talking on topics related to the Microsoft Data Platform. Since this wasn’t my normal audience, I thought I’d ask the twitter-sphere what questions DBA’s have when it comes to managing SharePoint and the responses helped formulate my talk.

I attended another session where the speaker raised culture as an important aspect of ‘getting things done’. He spoke about the disconnect between different groups within IT, Ops team vs Dev team vs Systems team etc and how communication is often lacking.

Over the years I’ve seen many instances where people using SharePoint do things that ‘upset’ either the Ops team, DBA or someone else in the team. The is often swiftly followed by finger pointing and limited understanding of each others needs only makes things worse.

The point I would make is that SharePoint is a platform. In many cases a group of people are responsible for different elements of the platform – server infrastructure, SQL servers, backups, administration of SharePoint, site builds, content migrations, governance etc. If we can clearly identify the needs of each team and get better understanding across the group, then things will run more smoothly, especially when a crisis occurs. We want the group to become a team.

This leads me to my presentation, Introduction to SharePoint for DBA’s. I made a deliberate decision not to dive in to deep or create a comprehensive guide covering every aspect of SharePoint that a DBA might need to understand, but just enough that they would see how different it is to other workloads and provide a few tips for doing things better.

I’ll leave you with a few simple rules to remember and a link below to download the presentation. Here are the rules:

  • Always use SQL Aliases when installing SharePoint
  • Pre-grow your databases for planned data migration or bulk loading
  • Create one Site Collections per Content Databases were possible
  • Understand your recovery options (see more here)
  • Have a regular catch up with your SharePoint admin
  • Never update SharePoint databases directly, use the SharePoint UI or API’s

Download Presentation

During the questions I was asked, what is the difference between a DBA and a SharePoint Admin? A difficult question for a guy who spends his days working with SharePoint to answer, when the room is full of DBA’s!

A big thank you to the Microsoft SQL community (#sqlfamily) for having me at their event.

Why is Office 365 going slow?

Is your Office 365 running slow at random times? Does it seem to happen at work but be fine from home (or some other location)? Here is a short check list to help diagnose the problem.

Where is your Office 365 Tenant located?

Make sure your tenant is hosted in a location that makes sense. For us New Zealanders, the nearest location is Australia. Check the Office 365 Datacenter map.

Are you behind a Firewall or Proxy Server?

All Office 365 services use SSL. Firewalls and Proxy server with SSL Packet Inspection enabled can be a source of latency, especially if they are under a heavy load. Does turning off packet inspection improve performance? Does the Firewall’s console show high memory or CPU usage? This article ‘Should you use SSL Inspection’ by Forinet is a good read and applies to other vendors too.

Check your international bandwidth

In New Zealand some ISP’s limit the amount of international bandwidth allocated to each customer. If you have a large number of users, this could be a bottleneck. Talk to your ISP about the bandwidth allocation. Some may also have Office 365 specific plans.

Express Route is another technology that can improve performance for Azure and Office 365. See Microsoft’s Express Route partners and peering locations document. Talk to your ISP about Express Route.

Are you connecting across a WAN to your company internet connection?

If you are working from a branch office, then your internet traffic may be passing over a WAN link before getting to the internet. How much bandwidth do you have and are you sharing it with other traffic? Are you slowing down when someone prints a big file?

Other things to check

  • Network switches on your local LAN. Look for packet loss, latency and retry errors
  • Wireless network. Is the issue related to your WiFi only?
  • Your PC (or Mac). Is the problem specific to one device?
  • Run a speed test on your internet connection.
  • Are you over your data cap?

For a details example of how to troubleshoot Office 365 performance, read this article from Microsoft Premier Support.

There are other reasons Office 365 might be running slow, but in my experience most issues relate to the environment users are in. Try to eliminate the easiest things first.

PowerShell:Bulk load files into SharePoints

Here is a script I wrote to bulk upload files and metadata into SharePoint. To make this work you need two things, a CSV file containing the names of the files to upload and the metadata associated with the item.

In this example, the CSV file has the following format:

  • filename,cust_number,document_type

The script reads the CSV file, creates a folder in the document library named with the value of the “cust_number” field, and then uploads the file “filename” and populates the “document_type” column.

The WebClient command is used to upload the file into the document library. The script also checks the item in (if required).

Write-Progress -Activity “Connecting to SharePoint Site,” -Status “Please wait …”
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$CSVFile = “C:\FilesToImport\filelist.csv”

$SPWebURL = “http://sharepoint/site”
$SPListURL = “http://sharepoint/site/library/”
$BaseFolder = “C:\FilesToImport\Files”
$Credentials = [System.Net.CredentialCache]::DefaultCredentials

$SPWebObject = Get-SPWeb $SPWebURL
write-host $SPListURL
$SPListObject = $SPWebObject.GetListFromUrl(“library/Forms/AllItems.aspx”)
$WebClient = New-Object System.Net.WebClient
$WebClient.Credentials = $Credentials

Write-Progress -Activity “Importing CSV File,” -Status “Please wait …”

$CSVObject = Import-CSV $CSVFile
$Index = 0
$Max = $CSVObject.Count

ForEach($CSVItem in $CSVObject)
{
$Index++
Write-Progress -Activity “Updating Metadata” -Status “Processing Item $Index of $Max”

$FileName = $CSVItem.File_name + “.pdf”
$ID_Number = $CSVItem.Cust_Number
$DocumentType = $CSVItem.Document_Type

$FullFileName = $BaseFolder + “\” + $FileName
write-host $FullFileName
if (Test-Path ($FullFileName))
{
$UploadPath = $SPListUrl + “/” + $Cust_Number + “/” + $FileName
$WebClient.UploadFile($UploadPath, “PUT”, $FullFileName)$SPListItemsObject = $SPListObject.Items | where {$_[‘Name’] -eq $FileName}
ForEach($SPListItem in $SPListItemsObject)
{
$SPListItem[‘Document_Type’] = $DocumentType

$SPListItem.Update()
if ($SPListItem.file.CheckOutStatus -ne “None”)
{
$SPListItem.file.CheckIn(“”)
}
}
}
else
{
Add-Content ErrorLog.txt $FullFileName
}
}

I’ve used this script in a few scenarios. I hope you find it useful too.

SSIS, InfoPath and SharePoint Lists

Have you ever needed  a low cost solution for capturing data in a form on mobile device and then pull the data back into an in house SQL database. In this solution data is entered into an InfoPath form on a tablet (Windows, iOS or Android), submitted to a SharePoint Online form library and then pulled into a SQLserver database using SQL Integration Services (SSIS).

screen-shot-2017-01-21-at-10-17-01-am

This solution will also work with standard SharePoint lists and can be used with SharePoint Online, SharePoint 2013 or SharePoint 2016.

The things you need to know:

  • InfoPath forms can be hosted in a SharePoint Online forms library
  • InfoPath can ‘promote’ fields from the form into metadata fields in the SharePoint Online forms library e.g. each form will be saved and selected fields will appear as columns on the form library.
  • SharePoint Online form libraries can be queried using an oData connection
  • SQL Server Integration Services (SSIS) can use oData as a data source and push or pull data to and from the data source
  • SSIS can be scheduled

Note: SQL Server Standard Edition (or better) includes SSIS

InfoPath steps:

  1. Using InfoPath Designer, create a form and publish the form to a SharePoint Form library. The easiest option is to do this from InfoPath and let it create the Form library for you.
  2. In Form Options, select the fields that you want to ‘promote’ to SharePoint.
  3. Publish the form
  4. In SharePoint verify that the promoted fields are showing as columns in the Forms library

SQL Server and SSIS steps:

In this step we setup a database and SSIS package. The connection is initiated from the server running SSIS and so this server must be able to make an outbound connection to Office 365.

SSIS packages are created using SQL Server Data Tools

  1. Create a table in your database with fields to store the data from the form
  2. Using Visual Studio create an SSIS package
    • Create the  SQLserver data connection for SQL table created in step 1
    • Create the oData connection to the SharePoint Online list. You will need:
      • The URL for the Form library (the site URL)
      • Office 365 credentials with permission to read the list. These credentials will be saved in the SSIS package
      • See the authentication note below
    • Map the fields from the oData Connection to the SQLserver Connection fields
    • Save
  3. Once you have tested the process (see steps below), you can schedule the SSIS package to run on as frequently as you need.

Testing:

  1. Create a new InfoPath form and submit the form to the library
  2. In SharePoint, check to see that the promoted fields are populated
  3. In Visual Studio run the SSIS package (you may need to ‘Run as Administrator’ when you start Visual Studio for this to work
  4. Check the table in SQLserver to see if any new data has arrived

Authentication issue:

When creating the oData connection to Office 365, you must manually change the setting for ‘Microsoft Online Services Authentication’ to true. This is in the oData Connection Manager settings, under Connection in the Security section. More details here.

Resources:

How to promote fields from InfoPath to SharePoint

How to create an SSIS package with SQL Server Data Tools

MSDN: SSIS oData tutorials

Microsoft MVP award

On the 1st of January 2017 I was awarded the Microsoft MVP award for Office Servers and Services (Yes, I am a SharePoint nut). I feel honoured to be a recipient of this award because it recognises something which I personally think is very important, contributing to the IT community.

screen-shot-2017-01-17-at-8-16-46-pm

I encourage everyone to find a community of interest and participate regardless of whether it is technology based or something completely different. Being part of a community is rewarding in many ways. You’ll meet new people, make great friends, learn new things and get enormous satisfaction from helping others who share you interest.

My involvement in the IT community was sparked by a need to learn about things that weren’t written in books or covered by professional training courses. I quickly discovered that putting myself forward to talk on a topic was a great motivator for learning (I was worried about embarrassing myself). I also discovered that embarrassment doesn’t really happen, people are supportive, audiences are kind and many people will take a moment to say a quiet thank you.

I never set out to get an award, that is something that came from giving time, sharing experience and talking about what goes wrong in the real world. Thank you very much to the people who support me, participated in the events I have organised, turned up to my presentations, watched my YouTube video and read my blog. I feel very thankful and motivated to keep it up!

Thank you! Now go and sign yourself up to your local user group!