Issues when deploying Access Services in SharePoint 2010

Access Services in SharePoint 2010 provides a neat mechanism for editing, updating and publishing a Microsoft Access databases in the browser. And there is no requirement for full blown copy of the Access client on your desktop unless you want to make any database changes.

This blog article is not going to provide a functionality overview of Access in SharePoint, or a break-down of basic the installation steps (which are pretty straightforward), but I wanted to highlight a few of the little gotchas which can cause grief once this Service Application has been instantiated.

The troubleshooting tips I’ve made assume the following:

• Your SharePoint 2010 Farm is running with an Enterprise license and is patched to SP1 or above
• Access Services is configured as a Service Application within your Farm and the service itself is set to ‘Running‘
• Either the ‘SQL Server Reporting Service 2012’ Service Application, or the ‘Reporting Services –Add-in’ for SharePoint Integrated mode has been applied
• The SharePoint ‘State Service’ has been configured (this is a ‘Powershell Only’ Service Application in terms of provisioning) http://technet.microsoft.com/en-us/library/ee704548(v=office.14).aspx
• You have created either a Web, Contacts or Project database using the standard SharePoint template

Issue 1

Once you’ve created your desired Access Services database and attempt to open it for the first time, you may find the ‘ever-so-helpful’ (not) failure notification: ‘An error has occurred. Click here to try again’

So where is my correlation ID to help me track the problem? In this instance, the root of the problem is loud and clear if you open the Windows Event Log.

Access Services runs with the context of it’s IIS Application Pool identity, and thus, this account requires ‘dbo’ access to the SharePoint content database. This can be achieved by locating the domain account (this should not be running under Local System by the way) in SQL Management Studio and editing its designated permissions (below shows an example of ‘db_owner’ granted database owne’ rights to the ‘Projects_DB’

 
 

 
 

Issue 2

When you try and run a report from within the Access Services ‘Report Centre’, you may witness the following error even if you have enabled Session State within your Farm environment: ‘This report failed to load because session state is not confirmed on’.

To confirm Session State is enabled, run ‘Get-SPServiceApplication’ from within Powershell and confirm it’s provisioned. One point to remember though, an IIS reset is required after provisioning Session State.

If ‘State Service’ is listed, the issue is down to Reporting Services not being configured correctly. And this is likely to be the cause of Issue 3.

Issue 3

With previous version of SQL Server Reporting Services (i.e SQL 2008 R2) there was a requirement to modify the ‘rsreportserver.config’ file to enable Access Services to leverage SSRS (and this was a rather painful exercise). However, the introduction of SSRS 2012 as a conventional Service App mitigates this manual step, although you still need to run some Powershell for a fully functional reporting experience.

$apps = Get-SPRSServiceApplication

foreach ($app in $apps)

{

New-SPRSExtension -identity $app -ExtensionType “Data” -name “ADS” –TypeName “Microsoft.Office.Access.Reports.DataProcessing.AdsConnection,

Microsoft.Office.Access.Server.DataServer, Version=14.0.0.0, Culture=Neutral,

PublicKeyToken=71e9bce111e9429c”

}

The first screenshot below displays the error you will witness if you don’t run the Powershell fix-up script

 
 

 
 

 
 

And now you have a working Access Reporting service…..

  

Oh dear…….

Run to the hills…….take cover!

Taken from a popular search engine

 

European SharePoint Conference Day 3

An early start to Day 3 was always destined to be a battle as it followed an evening of drunken shenanigans at the eagerly awaited third-party vendor bashes (thanks again to those fine folks from Axceler and AvePoint!) Fortunately, a bucket-load of Red Bull and a Bacon Sandwich provided the perfect remedy for soaking up the remains of a 4am Mojito, although things weren’t so straight forward for a unnamed colleague of mine who decided to ‘decorate’ the Copenhagen dual-carriageway on route to the conference out of a taxi window!

Session: 10 Things I like in SharePoint 2013 Search

Agnes Molnar, MVP, Hungary

No SharePoint conference would be the same without Agnes Molnar showing us what’s what with her expertise on everything Search. And since the copious amount of Red Bull I had consumed were starting to dwindle (and subsequently, the hangover was starting to kick-in) I’m a little worried the notes I took will not do the session justice. Therefore, the link below is to Agnes’ Sky-Drive which provides the slides for the presentations. And just for the record, this ‘Sky Drive’ link has absolutely nothing to do with SharePoint’s ‘Sky Drive Pro’ – great effort from the MS marketing folks J)

https://skydrive.live.com/?cid=d981a4ab2e8d4af8&id=D981A4AB2E8D4AF8%214741&authkey=!AKIECqkU5aGWvGc#!/view.aspx?cid=D981A4AB2E8D4AF8&resid=D981A4AB2E8D4AF8%215229&app=PowerPoint&authkey=%21AKIECqkU5aGWvGc

 

Session: 10 Steps to Creating a SharePoint Support Model

Geoff Evelyn, MVP

Finally someone decided to tackle a subject which has never previously been aired at a SharePoint conference; that of the SharePoint support model. Geoff Evelyn really did nail the subject and in many ways, I found this more informative and useful than some of the IT Pro tracks I attended over the four days. One of Geoff’s opening line’s summed-up the presentation perfectly; “User adoption does not work without a support model”. All of the slide decks are available on Geoff’s site which focuses heavily on the ‘SharePoint delivery’ workspace.

http://www.sharepointgeoff.com/ten-steps-to-create-a-sharepoint-support-model/

 

Session: User Profile Synchronization Best Practices in SharePoint Server 2013

Spencer Harbar, MCM, MVP

Doing battle with the User Profile Service in SharePoint 2010 is not something which should be taken lightly. We’ve all wasted countless hours trying to unpick its ridiculously complex architecture which uses the scaled-down, pre-release version of Forefront Identity Manager. Fortunately, Spencer Harbar’s excellent guide to tackling UPS (‘Rational Guide to implementing SharePoint Server 2010 User Profile Synchronization’ – http://www.harbar.net/articles/sp2010ups.aspx) provides all the information required to ensure your Synchronization Service doesn’t hang on ‘Starting‘ (yes, we’ve all been there!)

Spencer was therefore the ideal person to provide the nuts and bolt to the SP2013 iteration of UPS, which finally offers a direct ‘AD Mode’ import…..and thankfully, it has absolutely zero reliance on FIM!

• There is an increased dependency on UPS with SP2013. One of the new Service Apps, ‘Machine Translation’ requires user profiles to be populated
• UPS in SP2013 now provides three different synchronization modes; Active Directory Import (ADI), User Profile Synchronization (UPS) and External Identity Manager (EIM)

   

  

   

1. Active Directory Import (ADI)
   

   This is the lightweight LDAP import into SharePoint which doesn’t rely on FIM service and will probably fulfil most company import requirements. This mode is ‘Import only’ and is essentially the most straightforward option.

2. User Profile Synchronization (UPS)
   

   Provides the same approach as SP2010 with a number of improvements under the hood (includes some significant SQL enhancements). FIM is required

3. External Identity Manager (EIM)
   

   New addition to the User Profile Service which provides greater flexibility and allows for custom code. This is the most complex out of the three import modes.

    

   

    

• Your dedicated Synch account still requires the ‘Replicate Directory Changes’ ADD setting
• If your NetBios domain name is different to that of your the fully-qualified domain name (FQDN), you still need to enable ‘NetBiosDomainNames’ for the User Profile Service App – this setting is set to ‘False’ by default
• If you’re using Powershell to manage UPS, some SP2010 ‘cmdlets’ are no longer supported
• Try to avoid switching back and forth between ADI and UPS modes as you will run into problems (some associated bugs)
• New LDAP query filters are available (even in the UI) which provide great flexibility. You can also use ‘ADSI Edit’ to build LDAP queries
• The UPS ‘default schema issue’ still exists in SP2013 unfortunately (see below)

http://www.harbar.net/archive/2010/10/30/avoiding-the-default-schema-issue-when-creating-the-user-profile.aspx

• Since the conference, Spencer has blogged an article on the varying speeds of an ‘AD Import’ versus the ‘User Profile Synchronization’. And the outcome was a resounding victory for the AD Import! http://www.harbar.net/archive/2013/02/18/Quick-and-dirty-test-results-Active-Directory-Import-versus-User.aspx

 

 

Extending columns in Powershell

When executing a Powershell command, you may find that, depending on the amount of characters, some of the output is not visible as the underlying table view is truncated.

For example, within a SharePoint Powershell prompt, if you run ‘Get-SPFeature’, any ‘character-heavy’ ‘Display Names’ are masked:

However, if you add the following to your cmdlet, the full context of the output is visible:

European SharePoint Conference Day 2

As I mentioned in my previous blog, I was lucky enough to recently attend the European SharePoint Conference held in the ambient, albeit bloody expensive, city of Copenhagen. And following-on from the first full day or tutorials, it was time for the break-out sessions which I was eagerly awaiting.

Typically, most of the overviews I was keen to attend clashed with some equally mouth-watering sessions, and it was disappointing to learn that there wasn’t an opportunity to catch-up on a recording via the EPSC website later – I really think the organisers have missed a trick here (although more about them later!). Hopefully, the speakers will be blogging/publishing their notes and slides in the near future as it would be a shame to miss out on what was a very strong line-up from the SharePoint community (which easily surpassed what I witnessed in Berlin during ESPC 2012). Anyway, onto the important stuff as I give you a break-down of the sessions I attended and some of the key points which were made.

Keynote 1. Apps Everywhere: The New Office and SharePoint Store. Why Building a vibrant ecosystem makes great sense

Ludovic Hauduc, Microsoft USA

As you may have guessed by the title, the first session was all about SharePoint 2013 Apps and subsequently, that fluffy little thing called the ‘Cloud’! And I was somewhat surprised that it only took around 25 minutes for the words ‘on premises’ to be mentioned! J.

We were quickly reminded that SharePoint 2013 has three new virtues; apps, social search. Ludovic then provided some demonstrations highlighting the capabilities of the new MS cloud app model, which included Apps fully integrated into Office and SharePoint (pretty impressive stuff).

It was then time for a little SharePoint history lesson as we took a trip down memory lane….

• SharePoint 2007: code is trusted and operates within the SharePoint Farm
• SharePoint 2010: welcome to the world of ‘Sandboxed Solutions’, which provided isolation for custom extended code
• SharePoint 2013: the all new singing and dancing world of ‘Apps’, whether they’re hosted or on-prem

Keynote 2

SharePoint 2013 – Key Success Factors for a Successful Deployment

Spencer Harbar, MCM, UK

Mirjam van Olst, MCM, MVP, Netherlands

• Technical introduction into the role of the Apps model within a SharePoint 2013 Farm including details of the dedicated DNS namespace requirements
• Type of Apps; SharePoint-Hosted (Web App) Auto-hosted (Office 365), Provider-hosted
• Any SharePoint Web App aiming to leverage Apps has to be created without an IIS host-header. Therefore, create host-name site collections
• Apps are completely self-contained and cannot have a dependency on another App. They can also not depend on conventional Farm solutions
• New server topology factors to consider. With SP2010, a typical highly-available server configuration would include three tiers (Web, Application and Database), whereas in SP2013, this can include as many as 5 or above. There may be a desire to implement additional tiers to separate certain Service Applications -these include the ‘FAST-embedded and CPU hungry Search service, and two of the new additions: Request Management and Distributed Cache
• Requirements for migrating to Office 365 from on-prem
• Cleansing of AD is a prerequisite for any hosted ADFS requirements
• SP2013 relies heavily on the User Profile Service Application

   

Session: Best Practices & Considerations for Designing your SharePoint Logical Architecture

Mirjam van Olst, MCM, MVP, Netherlands

Web Application Influences

• Identify their intended use; MySites, Intranet, Extranet? How will these scale and do they require separate application pools?
  
• What authentication methods are required? FBA for Extranet and Windows Authentication for everything else?
• SharePoint Web Application policies remain (defined in Central Admin – ‘Resource Throttling > General Settings)
• Decide whether to implement Host-Named Site Collections or traditional Managed Paths within your Web App. There will be no host-header in IIS when using HNSC.
• HNSC were available in SP2010 and are now the ‘best practice’ for all new SP2013 deployments. HNSC are managed exclusively by Powershell and are the way forward for hosting and multi-tenancy purposes.
• Path-based site collections provide unique ‘wildcard’ managed paths and enable the creation of ‘self-service site creation’
• Don’t nest managed paths too deep as SharePoint will have to iterate through each level to resolve the URL.
• A Web App can be associated to a single App Catalog, or multiple Web Apps can consume a single Catalog. A Catalog cannot however, be shared across Farms, and thus consumed by separate Web Apps.

   

Web Application Boundaries

• 20 Web Apps per Farm is the maximum supported number although in reality, this should not exceed 10
• Five zones per Web App
• 20 Managed Paths per Web App
• Only ten IIS Application Pools per server

   

Custom Solutions

There are
still three deployment options when deploying custom solutions:

• All Webs
  
• Specific Web Application
• Entire Farm

   

Service Application Model

• Service Applications provide a flexible deployment model and are designed for scale
• All Service Applications can be configured and managed via Central Administration with the exception of the ‘State Service’ which can only be provisioned via Powershell. The ‘State Service’ and the ‘Usage and Health Data Collection’ service are pretty much a prerequisite for all SharePoint deployments, albeit not imperative.
• For multi-tenancy and hosting purposes, some Service Applications can be partitioned
• A single Farm can have multiple instances of a Service Application (i.e Managed Metadata), and can then be isolated to a dedicated Web App by virtue of proxy groups
• If deployed to a dedicated IIS Application Pool, only start the Service Application if required to utilise server resources
• SP2013 provides flexibility when creating a ‘Service Only’ Farm
• Six Service Apps can be shared across Farms:

1. BCS
2. Machine Translation
3. Managed Metadata
4. User Profile
5. Search
6. Secure Store

• Three can be consumed across a WAN (although not another continent. Trusts would need to be established)

1. Search
2. Machine Translation
3. Managed Metadata

    

Scaling of Services

• Scaling your service accordingly, is critical when implementing your topology. See the points Mirjam highlighted in the photo I took below (apologies for the camera quality of my mobile phone) The point which stood out for me relates to ‘Access’ and it’s creation of ‘databases on the fly’. The DBA’S among you will be pleased…… J

Service Apps Summary

• Only create what you need
• Plan your number of services instances
• Share application pools (within reason)

   

Software Boundaries

• Content databases should be between 100 and 200GB. MS does support* databases up to 4TB (yes, that is not a typo), but they’re unrealistic with the back-up and restore SLA’s
• Site Collections still cannot span across multiple databases. This is one factor being implementing multiple site collections.

   

Limit	Maximum Value	Limit Type
Search service applications	20 per Farm	Supported
Indexed Items	100 million per Search Service Application 10 million per index partition	Supported
Crawled Properties	500,00 per Search Service App	Supported
Managed Properties	50,00 per Search Service App	Supported
User Profiles	2,000,000 per Search Service App	Supported
External Content Types	5,000 per Web Server (per tenant)	Boundary
Max Number of Term Sets in a Term Store	1,000	Supported

 

Mirjam also covered the ‘Site Collection v Sub Site dilemma, although I had to dive out of the session at this stage due to my over indulgence of Italian meat-balls during the lunch break!

 

Session: Implementing Office Web Apps Server 2013

Thorbjorn Vaerp Atea, Norway

This may have been the last session of the day, but the speaker was certainly full of enthusiasm and had an obvious passion for the subject. And amongst the numerous gags he also provided some really useful demonstrations (all using Powershell) regarding how you implement Office Web Apps 2013 (OWA or WAC depending on which is your preferred acronym)

• Tip: NEVER uninstall Office 2010 Web Apps in a SharePoint 2010 Farm. Simply disable it.
• Office Web Apps 2013 is now exclusively a stand-alone product unlike the previous version. It has to be joined to a domain and will only function using a Claims-based Web App.
• Can server multiple products in addition to SharePoint:

  Exchange, Lync, File Shares

Prerequisites

• Server 2008 R2 or Server 2012
• Net 4.5 & Powershell 3.0
• Domain joined
• Powershell to enable Office Web Apps 2013 cmdlets
• SCOM for monitoring

Performance & Scale

• Single Server can support 20k users
  
• Use a load-balancer (F5) to terminate SSL. SSL is the default protocol
  
• Layer 7 routing
  
• Nice document preview when hovering over documents
  

Installation Steps

• Run set-up.exe on your OWA server
  
• Generates the OWA Farm (using Powershell)
• Add a second server for resilience and performance (Powershell)
• Nothing installed on SharePoint (i.e binaries)
• Using Powershell, connect to OWA Farm

 

 

European SP Conference – Real Time Upgrade to SP2013

I am currently in Copenhagen attending the European SharePoint conference. The conference began with a number of eight-hour tutorial sessions which covered a number of key IT Pro and DEV tracks.

After much deliberation, I decided to attend the ‘Real Time Upgrade SharePoint 2010 to SharePoint 2013’ session with Eric Harlan (Microsoft, USA). And it was an inspired choice, as Eric not only covered all of the upgrade steps in great detail, he also provided some useful demonstrations (all via Azure). In fact, the content was so comprehensive, we ran out of time and didn’t cover the migration of Service Applications in the kind of detail the subject deserves.

Hopefully, the topics we didn’t have time to cover in great depth, Eric will be blogging at some stage. One of these was the various permutations (and there’s plenty to ponder) when migrating the User Profile service. And unfortunately we only began tackling this subject when there was only 15 minutes left on the clock!

Below are some key points and guidelines which Eric highlighted, although this is only really a high-level overview when there was so much in-depth content covered. Hopefully the notes I made are accurate, so please correct me otherwise!

I’ll be bringing you further blog posts throughout the week as the conference progresses.

 

Requirements Overview

• SQL Server – physical server if at all possible.
• Search Server 8-16 GB RAM, FAST now imbedded so therefore, no requirement for a separate sku
• 24GB of RAM for single ‘all-in-one’ box
• App Fabric (Distributed Cache), new feature. Minimum of 8GB RAM, Max of 16GB RAM. Never more, never less
• Stretch Farms no longer supported. Latency between servers has to be under 1ms
• Gigabit bandwidth for each server
• .Net 4.5 and .Net 3.5 BOTH have to be installed
• People Picker now highly dependent on Authentication (i.e SAML Claims, Forms)

   

  Install
  

• Scripted install if at all possible.
• Never use the ‘Farm Configuration Wizard’. Significant limitations. (I.e databases created with GUID’s, single service account deployed as identity for multiple Service Apps. Best practice is for separate account per service app (although this would leave you with around 18 accounts)

  Only use in DEV environment which will never be used again

   

  New/Improved Features
  

• AppFabric (Distributed Cache). Provides ‘real-time’ social features in SharePoint
• Search.

  FAST is fully integrated

  Web Analytics as an independent service app, is deprecated, and is now included within Search. No supported way of migrating a SP2010 Web Analytics service app to SP2013 as the database is now contained within Search. Would require custom code to expose legacy analytical data.

• Translation Services

  Cloud-based translation of MS documents

• App Management.

  Welcome to the concept of an App Store! (Cooperate v MS)

• Access Services.
  

  2010 available for backward compatibility

  Possible alternative to InfoPath

  1GB SQL database per site

• Workflow Management
  
• Work Management Service
  
• Shredded Storage
  

  Decreased I/O costs with just file deltas

   

  Content Database Attach
  

• Distinction between ‘migrate and upgrade’
• ‘Timing’ is everything. Make sure you plan your steps carefully, ensuring ALL your dependencies are in place before your mount (i.e Managed Paths, all customisations)
• No ‘in-place’ upgrade available
• AAM deprecated, albeit still available. This has much to do with the advent of host-named site collections.

  Only Claims Web Apps (Windows Authentication deprecated). Classic Web App creation only available via PowerShell. Claims is default in Central Admin

• If no Managed Path defined, site will no longer be orphaned following migration to SP2013, but SharePoint will define a unique managed path. So, the site will be accessible (good thing), but you may have an ambiguous URL path (bad thing). Lesson to be learnt…….always ensure managed path defined in Web App before your mount.
• SP2010 Fast Search databases cannot be migrated in SP2013. Only the Admin database can be migrated. Therefore, everything will need to be re-crawled
• Two modes ‘2010’ and ‘2013’- ’14’ folder co-exists alongside the ’15 folder. Therefore, a SP2013 Site Collection can exist in SP2010 mode and be totally seamless to the user
• Upgrade takes place in TWO stages. ‘Mount-SPContentdatabase is a SP2010 command, and will provide a SP2013 site collection in SP2010 mode (database upgraded/schema aligned)

  Second step is ‘Throttle Controlled Site Upgrade’. Admin can determine whether site AND database is upgraded to begin with and also the timing, which is configured and controller by a timer job. This allows for a more granular, scheduled approach

  If you upgrade MySite host, all new MySites will go straight into SP2013 mode. Existing MySites are upgraded on the SECOND time they are accessed via end user (so warn your users!)

• Upgrade SP2010 content databases to Claims before upgrade to SP2013
• Always use ‘Test-SPContentdatabase’ before ‘Mount-SPContentDatabase’ to make yourself aware of any potential problems. This will also show any ‘blocking’ scenarios
• Most likely to cause upgrade issues:
  • Web Parts
  • MasterPages
  • Themes
• Dictate whether solutions should go to either 14/15 hive or BOTH
• Use Powershell to migrate a Web App from Classic to Claims

   

  Service Application Upgrade
  

• No direct path from Moss 2007 to SP2013 (possible 3^rd party solution)
• Database server level must be 2008 R2 or SQL 2012
• SP2010 Farms can consume SP2013 service apps (Search, MMD, US). But SP2013 cannot consume SP2010 service apps.
• Following databases can be migrated:
  • Search Admin
  • User Profile x3 (try to avoid upgrading ‘synchronisation’ database, unless you want a lot of stress!)
  • MMD (remember to consider Content Hub URL changes)
  • Secure Store
  • BCS
  • PPS
• Process to upgrade:
  • Create Service App Pool
  • Create New Service App
  • Create Service App Proxy
  • Attach Service App database
  • Clean-up any outstanding tasks

Shrinking the WSS_Logging database

The SharePoint 2010 logging database is a great tool for all SharePoint Administrators. Not only does it aggregate and consolidate a wide array of information across your Farm, it is also the ONLY SP SQL database which you can quite happily interrogate for your analytical and troubleshooting needs.

Details behind the WSS_Logging database can be viewed within the ‘Service Applications’ section of Central Admin, although once generated (by default), the only way to recreate this is via Powershell:

 

Set-SPUsageApplication –Identity “WSS_UsageApplication” –DatabaseServer “mysqlserver” -DatabaseName “WSS_logging_database”

 

Over time, the logging database can grow considerably and can cause storage concerns for your SQL DBA. You can however, put measures in place to purge unwanted data. The first option you have is reduce the data retention policy, which is set to 14 days by default.

Get-SPUsageDefinition

If for example, you wanted to reduce the data retention to three days, you can run the following Powershell:

 

$defs = Get-SPUsageDefinition

Foreach($def in $defs)

{

Set-SPUsageDefinition –Identity $def.Name –DaysRetained 3

}

 

Now finally, open Central Admin and choose, Monitoring > Configure usage and health data collection > Log Collection Schedule>

Execute the two Timer jobs:

The SQL logging database will now contain some free space which you SQL DBA can ‘free-up’ within SQL Management Studio or running the ‘DBCC ShrinkFile’ T-SQL command.