For years, small and medium business users needing desktop database capabilities have turned to Microsoft Access. Over the product’s 18 year history, Access has developed a great combination of performance and functionality. On the flip side, as a desktop database Access has its own challenges bridging over from the desktop to the web paradigm. It takes a lot of work to share and manage a database with a large number of users using a custom web front end. You’d typically need to set up an IIS web server and develop with ASP.NET to build the web application, pages, forms and scripts.
With Access 2010 and SharePoint 2010 Access Services, sharing an Access database application to a large number of users just got a lot easier. Just publish the database to a SharePoint site with a few simple steps. Well, this is more likely the case for smaller, simpler database migrations. Caveats aside, Microsoft offers recommendations to support those larger more complex database applications too.
If you’ve worked with Access in SharePoint 2007, you know that the entire database file saved as a file in the document library and database tables were converted into SharePoint lists on the server. This integration model works best on small databases with small user bases. SharePoint 2010 and Access 2010 improve the integration model with better database centralization, web access, manageability and even performance to expand the range of integration scenarios. With this latest round of improvements, organizations have a lot more opportunity to leverage existing investments in Access.
You can now choose varying degrees of integration depending on the complexity of your existing Access database. In the simplest case, Access 2010 can push a database directly into SharePoint lists. This is largely unchanged from how Access 2007 published databases to MOSS 2007. All of the user interface elements like queries, forms and reports remain in their native format within the ACCDB file. With the database tables now in SharePoint lists, users can leverage SharePoint’s data management services like versioning, permissions and the recycle bin. Users can also perform normal SharePoint operations on those database tables, just as they would with any other SharePoint list. In this simple-case scenario, the ACCDB database file, which still contains the interface components, is uploaded to a SharePoint document library just as in the SharePoint 2007 paradigm. The key difference with Access 2010 is in improved performance, specifically with local caching. The Access client application is still where the majority of user interaction will occur. The biggest benefit of moving to SharePoint in this scenario is taking advantage of the centralized services available to the database tables that now reside on the server.
Taking integration between Access 2010 and SharePoint 2010 to the next level is where the power of Access Services comes in. In this more advanced scenario, the front-end Access interface components (queries, forms, and reports) now run on the SharePoint Server as components within the Access Services application model. Using the Publish to SharePoint wizard, compatibility checks are run to ensure that tables and integrated components are configured to work within SharePoint’s application model requirements. Once compatibility tests are passed, database tables are still pushed to SharePoint lists, a new SharePoint site is created for the published database, and the interface components now run in the context of Access Services on the SharePoint Server. Once the database has been successfully published to the server, you now have the ability to extend the functionality of your Access database using SharePoint’s web capabilities to broaden the reach of your data to users across the network , AND still retain the ability to work with your data using the rich Access client application. This hybrid usage model is going to be the migration path of choice for many organizations because the web interface supports a core subset of database functions, so the client application still plays an important role for a number of users. Modifying the design of a published database also requires the Access application and but you’ll still use the browser to make structural changes to the underlying SharePoint lists.
Are you considering migrating your Access database application to SQL Server? This might be a recommended migration path if the complexity of your Access database poses compatibility problems with SharePoint. This too is a supported migration path in SharePoint 2010. If your organization has a shared SQL Server available, you can migrate your complex database tables to SQL, and still integrate your Access database interface components with SharePoint Access Services. Smaller organizations that may not have the resources to support a standalone SQL Server for this purpose may choose to leverage the SQL Server supporting the SharePoint farm. As a best practice, the database server behind the SharePoint farm should be dedicated for SharePoint data only to limit risk of another application downing the server and possibly losing or corrupting data. Some organizations do support shared SQL Servers, and you may have to ensure compliance with IT policy and governance as part of an approved migration. Another point to consider is licensing — depending on your application usage model (which users, which devices) your SQL Server client licensing requirements may also change.
SharePoint 2010 gives business users the ability to build their own database applications using Microsoft Access 2010, affording them administrative control of their own database application within the robust functionality, security and permissions model of SharePoint. IT administrators also win because database applications can be centrally supported and managed to a consistent service level. Through the CA UI and PowerShell scripts, IT administrators can monitor and tune the performance of Access Services to ensure operational conformance to the desired service level.
Requirements
- SharePoint Server 2010 Enterprise is required. Neither SharePoint Foundation nor the Standard edition support Access Services.
- SQL Server 2008 R2 Reporting Services add-in is also required.
- Enterprise CALs are also needed.
- SQL Server 2008 R2 Report Server may also need to be deployed depending on business requirements. Small deployments with a single web front end server can run a lightweight version or “Local Mode” configuration that only requires the installation of the SSRS add-in to the SharePoint deployment, but limits users to viewing reports. The default configuration recommended for Reporting Services is known as Connected Mode and requires SQL Server 2008 R2 Report Server to be deployed in addition to the SSRS add-in.
A quick note about Access and the new Office Web Application offering — even though Access is part of the Office Professional Suite, Access Services and SharePoint’s integration with the Access client have no relationship to the Office Web Application offering. Office Web Applications are online versions of Word, Excel, PowerPoint and OneNote. Office Web Apps are not required to deploy or use Access Services.
Deployment Considerations: Performance and Capacity
Access 2010 improves on linked database performance compared to Access 2007 on MOSS. 2010 utilizes an improved local cache mechanism that greatly reduces the data load over the wire after the first transaction. Between better caching, fewer web service calls, more efficient bulk queries, users should notice significant performance gains with Access 2010. Compared to performance of Access 2007 on MOSS, form open and data load times showed roughly 60% faster times, to as much as an order of magnitude better performance.
SharePoint 2010 offers a new service architecture that didn’t exist in MOSS 2007, along with a wealth of other non-Access service applications for the enterprise user. A small farm deployment will typically want a dedicated application server to run a few SharePoint services such as Access Services, Excel Services, Visio Graphics Service and so on. Your farm’s performance and capacity needs will depend on your application workloads. Partners like bluLink Solutions can help you analyze business requirements to estimate the extent to which you should scale your application servers up and/or out.
As far as server-side performance goes, Microsoft has a created great set of performance and test whitepapers for SharePoint 2010, including one for Access Services. You can download this document in Word format at:
This paper contains a lot of useful information, but a couple of points stood out to me so I thought I’d mention it here:
1. Access Services is a stateful service application, unlike typical SharePoint client-server interactions. Cursors and record sets are kept in memory between requests to accommodate common, long-running Access usage patterns, inferring that memory capacity could be a limiting resource. However, the test team found that CPU utilization on the application server was the typical bottleneck for most scenarios. Scaling up a multi-proc capable server is the first remedy, followed by scaling out with an additional application server once the server had maxed out CPU capacity. The test team went on to say that SharePoint Server’s recommended application server hardware requirements including those for memory were sufficient for application servers running Access Data Services.
2. Microsoft’s test profile is based on a farm dedicated only to Access Services. In the real world, we would expect some smaller organizations to run several service applications such as Excel Services on the same application server. Keep in mind the standard disclaimer, “your mileage may vary” when planning your service application deployment.
Summary
Access Services is another example of Microsoft’s stronger integration between the Office application suite and SharePoint Server 2010. Existing Access databases can be quickly ported over to SharePoint Server 2010, giving organizations the opportunity to leverage previous investments in Access applications. It puts the power of extensible online database applications in the hands of SharePoint end users, while still giving IT the ability to centrally support and manage service availability of Access databases across the organization.
