Comparing Characteristics of On-Premises with Office 365’s Hosted Share Point Server 2010

Updated 3/13/2011: Added links to MSDN’s Configuring Reporting Services for SharePoint 2010 Integration topic and other information about SharePoint Server 2011’s Access Services (see end of post.)

Following are a list and table that describe differences between SharePoint Server 2010 running on premises and hosted by Office 365:

The main differences between Access Services on Office 365 and setting it up on your own SP server are as follows:

1. The throttles are set to the default values on Office 365 whereas if you set it up yourself you can configure it to whatever is in between the throttles we have. See the chart below for details.

2. Access Services on Office 365 will not have reporting configured when it releases. If you set up reporting yourself, you can configure reports to run in local or connected mode. * [Emphasis added.]

3. Access Services on Office 365 has a money back guarantee for 99.9% uptime. If you set it up yourself, there is no guarantee it will have the same uptime.

4. Access Services on Office 365 permissions use LiveId whereas you can use other permission models setting up Access Services yourself.

5. Access Services on Office 365 has a set cost per month whereas setting the server up on prem the cost is more variable.

 

Access Services On-Premises	Office 365
Max Columns Per Query: 1-255	Max Columns Per Query: 40
Max Rows Per Query: 1-200000	Max Rows Per Query: 25000
Max Sources Per Query: 1-20	Max Sources Per Query: 12
Maximum Calculated Columns Per Query: 0-32	Maximum Calculated Columns Per Query: 10
Maximum Order By Clauses Per Query: 0-8	Maximum Order By Clauses Per Query: 4
Maximum Records Per Table: any	Maximum Records Per Table: 500000
Maximum Application Log Size: no limit	Maximum Application Log Size: 3000 records
Max Request Duration: 24 days The maximum duration (in seconds) allowed for a request from an application.	Max Request Duration: 30 seconds
Maximum Sessions Per User: no limit The maximum number of sessions allowed per user. If a user has this many sessions and starts a new session, the user's oldest session is deleted.	Maximum Sessions Per User: 10
Maximum Sessions Per Anonymous User: no limit: no limit The maximum number of sessions allowed per anonymous user. If this maximum is hit the oldest session will be deleted when a new session is started.	Maximum Sessions Per Anonymous User: 25
Cache Timeout: 1- 24 days The maximum time (in seconds) that a data cache can remain available, as measured from the end of each request for data in that cache.	Cache Timeout: 1500s
Maximum Session Memory: 0-4095 The maximum amount of memory (in MB) that a single session can use.	Maximum Session Memory: 64 MB
Maximum Private Bytes: no limit The maximum number of private bytes (in MB) allocated by the Access Database Service process.	Maximum Private Bytes: 50% of physical memory on the machine
Maximum Template Size: no limit The maximum size (in MB) allowed for Access Templates (ACCDT).	Maximum Template Size: 30 MB

Note that Office 365 characteristics may differ in the release version.

Thanks to Access Program Manager Ryan McMinn (@msaccess) for the preceding data, which will be incorporated in my first and third Webcasts presented by QUE Publishing on 3/29 and 5/10/2011.

---

* Updated 3/13/2011: Printing reports requires configuring SQL Server Reporting Services to view and print reports. To learn more about configuring SQL Server 2008 R2 Reporting Services for Access Services, see MSDN’s Configuring Reporting Services for SharePoint 2010 Integration topic.

AccessHosting.com’s online implementation of Access Services provides fully preconfigured Reporting Services (see my Access Web Databases on AccessHosting.com: Viewing, Printing and Editing Reports post of 3/13/2011.)

Derek Goodridge’s Thoughts on Access Services in SharePoint 2010 blog post of 1/16/2011 offers insights primarily about on-premises Access Services installations and includes links to these Web Database resources:

• Access Product Team Blog: Access Services Overview and Install Information
• Access Product Team Blog: Publish to SharePoint (part 1)
• The Access Show: Access 2010 Demo of Access Services and Web Databases
• The Access Show: Publish an Access 2007 Database to SharePoint with Access 2010
• Albert Kallal’s Access 2010 web database demo video

Robert L. Bogue’s SharePoint 2010 and Access Services’ Place blog post of 1/10/2011 is a reasoned third-party view of appropriate use of on-premises Access Services.

Clint Patterson’s More Office 365 Answers post of 10/22/2011 to the Microsoft Office 365 Blog provides additional details about Office 365’ implementation of Access Services.

Technorati Tags: Microsoft SharePoint 2010,SharePoint Server 2010,Office 365,Microsoft Access 2010,Access 2010 Web Databases,Access Services,SQL Server Reporting Services,SQL Server 2008 R2 Reporting Services

How to Set Up a SharePoint Server 2010 Developer Machine without Hyper-V

If you’re interested in testing on-premises SharePoint Server 2010 with Access Services for Access Web Databases, Bruce Kyle posted How to Set Up SharePoint 2010 Developer Machine without Hyper-V to his US ISV Evangelism blog on 3/11/2011:

SharePoint Easy Setup Script is a new tool released by Chris Johnson and Paul Stubbs to help you set up a SharePoint 2010 developer machine without requiring Hyper-V. The Easy Setup script will set up the machine, either as a native install or on a bootable VHD file.

Paul and Chris have a video on Channel 9 that shows you how. See Building a SharePoint Development Machine Using the Easy Setup Script.

In order to make this process as easy as possible, we have created a series of PowerShell scripts that automate the entire process of building a fully running and ready-to-go SharePoint 2010 environment. The scripts are highly configurable, but out of the box they will download all of the required software and install all of the SharePoint prerequisites as well as other software such as Visual Studio and Office. The script will then install and configure SharePoint 2010 and, finally, the script will provision a site.

Download the Easy Setup Script

http://bit.ly/EasySetupScript

For More Information

Chris Johnson’s blog: Announcing SharePoint Easy Setup for Developers
Paul Stubbs: SharePoint 2010 Easy Setup Script

ISV Video on SharePoint development, see Quark Extends SharePoint for Dynamic Publishing.

For Free Assistance with SharePoint 2010

Join Microsoft Platform Ready for free assistance in making your applications compatible with SharePoint 2010.

Also see the SharePoint Developer Center on MSDN for videos, training, and how to get started with SharePoint development.

Bruce is a Microsoft ISV Developer Evangelist.

The Specs for my Primary Development Server

My self-assembled development machine has an Intel DQ45CB 64-bit motherboard, an Intel Core2 Quad Q9550 processor (2.83-GHz, 12M cache, 1333 MHz FSB) with VT-x and VT-d, 8 GB DDR RAM, and two 750-GB Seagate SATA-2 drives in RAID1 configuration with the on-motherboard RAID controller. I run SharePoint 2010 Server on a Windows Server 2008 R2 virtual machine with 4 GB RAM assigned by Hyper-V under Windows Server 2008 R2 as the host operating system. I’ve been very pleased with the performance of this machine.

The VM also runs SQL Server 2008 R2 Standard Edition for testing upsizing Access 2010 databases to SQL Server.

Technorati Tags: Microsoft SharePoint 2010,SharePoint 2010 Server,Hyper-V,Windows Server 2008 R2,Intel DQ45CB Motherboard,Intel DQ45CB,Intel Q9550 CPU,Microsoft Access 2010,Access 2010 Web Databases,Access 2010 Data Projects,Access 2010

Buck Woody Includes Microsoft Access as an SQL Server Alternative in His “Choosing the Back End” Guide

Buck Woody (@buckwoody) hosts the SQL Server Reference Guide blog on the InformIT Web site; see his bio at the end of this post. His Choosing the Back End post of 2/18/2011 begins:

You might think that as the SQL Server Guide host I would always recommend that SQL Server be used in every development situation. But there’s an old saying: "When all you have is a hammer, everything looks like a nail." Perhaps you’ve run into consultants who find that whatever problem you have, the solution is the same (theirs). That usually comes from being familiar with only a few products.

I’ve found, however, that each situation is unique. After close evaluation, solutions are normally dictated by several factors, some of them "weighing" more than others. I’ll cover a few of those factors in this article, to help you understand when to pick SQL Server as the back-end architecture. In fact, you may not need a Relational Database Management System (RDBMS) at all — you might be able to relax the requirements you have enough to use all kinds of storage for your applications. An RDBMS isn’t suited for every situation.

Buck continues with with comparisons of:

• Oracle
• Sybase
• IBM DB2
• Open Source RDBMS platforms: MySQL, PostgreSQL
• Microsoft Access
• OpenOffice Base

Here’s his commentary about Microsoft Access:

It might be a little strange to some to find Microsoft Access as a competitor to SQL Server, but in fact sometimes it is. Microsoft Access is a complete database, development and reporting environment which is highly integrated into the Microsoft Office suite of programs. It uses the "Jet" engine for storage technology, but it can also use SQL Server, Excel or even XML files as a back-end as well. In fact, it can use all of these at once.

The main strengths of Microsoft Access are its availability, its low cost and its ease of use. It seems almost everyone has access to Access, and it’s quite easy to understand, even for writing code. It has several built-in wizards, a graphical reporting tool and the ability to export included in the base product. There are hundreds of applications already written using Microsoft Access.

New developers are sometimes confused by Access — is it a database file or a program? The answer is both. An MDB file is an access database that you can use in VB or C# code, and it also has all the plumbing for the Microsoft Access program to store reports, programs and more right in the same file. That feature sets it apart from the other engines I’ll discuss.

Access’s primary drawback is its scalability. Granted, Microsoft doesn’t tout Access as an enterprise system, but some applications try to force it into that mold. The point is that Access just isn’t suited for more than a few concurrent users.

Another drawback is that Access doesn’t have a true transaction log, and it doesn’t allow you to create multiple files to house the data. For that reason, it isn’t as safe as an enterprise model requires.

Access doesn’t have great security, either. You can create users and restrict them to certain parts of the program, but it’s easy to circumvent.

Don’t write this product off, however. A modified version of the Jet engine was used in Windows NT’s naming database (WINS) and another version of it (Jet Black) was the early database engine for Microsoft Exchange.

You can think of Microsoft Access as inexpensive and feature-rich but having very low-scalability. It runs on all Microsoft platforms.

Buck’s a bit behind the times in his analysis of Microsoft Access, which applies primarily to Access 2003 and earlier. Here are my updates for Access 2007 and later:

• Access 2007 and 2010 use *.accdb (ACE) not *.mdb (JET) files.
• Access 2007 and 2010 also support SharePoint lists as a back-end data source. Access 2010 Web Databases require SharePoint Access Services as the back end.
• Access 2007 and later don’t support creating user accounts or securing data tables and programming objects. All Access databases can be password-protected; share and file security can limit user access to networked back ends.
• Blue Jet, not “Jet Black” was the foundation for Microsoft Exchange’s Extensible Storage Engine (ESE). See my Red vs. Blue JET Database API Confusion post of 7/23/2006.
• Access databases have limited scalability for updates; most Access developers consider about 10 users updating tables simultaneously to be the upper limit. However, up to 255 read-only users can be accommodated. See MSDN’s Microsoft Data Engine (MSDE) for Microsoft Visual Studio 6.0: An Alternative to Jet for Building Desktop and Shared Solutions for more details.

Here’s Buck’s OpenOffice Base review:

In my experience, the OpenOffice Product has been pretty good. That being said, the “Base” product within it, which is often touted as an Access replacement, doesn’t quite do the same thing, especially in the reporting and coding capabilities. You also can’t download just the “Base” product, at least in my experience (I could be wrong here). I’ve had to download the whole package, customize the install, and then install only the parts I wanted.

But if you are looking for an Open Source alternative (once again, this one is now owned by Oracle), there are forks available.

I don’t consider my limited experience with OpenOffice Base to be “pretty good.” See my posts about Open OpenOffice Base 2.0:

• OpenOffice Base 2.0 vs. Microsoft Access 2003 - Part I (10/15/2005)
• OpenOffice Base 2.0 vs. Microsoft Access 2003 - Part II HSQLDB Tables (10/23/2005)
• OpenOffice 2.0 Base Matches Microsoft Access? Surely You Jest.(10/30/2005)

According to Wikipedia’s OpenOffice.org Base article, Sun Microsystems released OpenOffice Base 3.3 on 1/25/2011. Oracle became the owner with its recent Sun acquisition. Wikibooks offers a very abbreviated OpenOffice User’s Manual: OpenOffice.org Base, which a reader defined “as [an] undeveloped draft or outline.”

Buck’s bio:

Buck Woody (blog | twitter) is a Senior Technical Specialist for Microsoft, working with enterprise-level clients to develop computing platform architecture solutions within their organizations. With over twenty-five years professional and practical experience in computer technology, he is also a popular speaker at TechEd, PASS and many other conferences; the author of over 500 articles and five books on databases; and teaches a Database Design course at the University of Washington.

Free “Access 2010 for Access 2007 Users: What's New” Chapter from “Microsoft Access 2010 in Depth”

Catch the complete Chapter 1, “Access 2010 for Access 2007 Users: What's New” from my new Microsoft Access 2010 in Depth book courtesy of Que Publishing. Here’s a preview:

Technorati Tags: Microsoft Access,Microsoft Access 2010,Microsoft Access 2010 In Depth,Access 2010,Microsoft Access 2007,Access 2007,Access Web Databases,SQL Azure,Windows Azure,SharePoint,SharePoint 2010,Access Services

Migrating Access 2010 Applications to SQL Azure

Steve Yi produced a 00:08:50 Migrating Microsoft Access Applications to SQL Azure video segment for Microsoft Showcase of 2/8/2011:

In this tutorial Steve Yi walks through the migration of a Microsoft Access 2010 expense application to SQL Azure. When done, Steve is able to use Microsoft Access 2010 and have his data live in SQL Azure in the cloud.

Technorati Tags: Access 2010,SQL Azure,Migration

AccessHosting.com Adds New Features in 2011

Chris Downs posted Go ahead, click the button! to the Microsoft Access Team blog on 1/14/2011:

Most of you are already aware that Access 2010 lets you create "web-enabled" databases that can be published to a SharePoint server that is running Access Services. Of course, many of you don't have such a server just sitting around, so that's where hosting services such as AccessHosting.com come in. They have their own blog where you can learn more about the service.

AccessHosting has announced some improvements for 2011, such as a new control panel for administration of account passwords, support for SharePoint Designer outside of Access Web Databases, and the "Access Services 2010 Virtual Appliance" which provides a turnkey cloud-based solution for customers requiring dedicated server performance and features. All their plans are offered on a free trial basis for 30 days, so if you've never clicked the Publish to Access Services button, you should give it a try. More information about web-enabled databases is also available in the Office.com article Build an Access database to share on the Web.

Technorati Tags: Microsoft Access 2010,Access 2010,Access Web Databases,AccessHosting,AccessHosting.com,SharePoint 2010 Access Services,Access Services

Received My Author Copies of “Microsoft Access 2010 In Depth”

I received two heavy boxes containing 15 author copies of Microsoft Access 2010 In Depth this afternoon and gave the printed copy the once-over.

I estimated that the print chapters would total 1,000 pages with 600 pages of advanced material to be made available for downloading from Que Publishing’s Web site.

It turns out that the text of the print chapters (1 through 25) runs 1,006 pages and the online chapters (26 through 33 plus two appendices) occupy 521 pages.

Moving less-frequently used content online saved 33% of the paper required by the preceding edition.

The book’s inside back cover provides the URL for downloading the PDF version of the entire book, which is free to print purchasers, plus the 77.7-MB *.zip file of the sample databases.

Purchasers also are entitled to 45 days of free access to the full online version from http://www.informit.com/safarifree with the provided coupon code:

I’m pleased with the clarity of the screen captures and other illustrations. This edition marks the change from 800x600-pixel to 1,024x768-pixel resolution, which is more representative of today’s displays for PCs, laptops and netbooks.

If you have any problems downloading your PDF copy or the sample code, add a comment to this post.

Technorati Tags: Microsoft Access 2010 In Depth,Microsoft Access 2010,Access 2010,Que Publishing