Friday, May 27, 2011

Access to SQL Server Migration: Understanding the Assessment Report and Error List

image Bill Ramos posted Access to SQL Server Migration: Understanding the Assessment Report and Error List on 5/27/2011. This information will be useful to folks who watched my Upsizing Access 2010 Projects to Web Databases with SharePoint 2010 Server Webcast:

The SQL Server Migration Assistant for Access assessment report displays the results from converting a Jet based Microsoft Access solution to use SQL Server. The report displays errors, warnings, and information settings based on either the Default Project Settings or the Project settings you have when set when you run the report. For information regarding the project settings, see “Access to SQL Server Migration: Understanding SSMA Project Settings”. The Error List user interface provides a convenient way to navigate to the specific object that has the error, warning, or informational message. This blog builds on Access to SQL Server Migration: How to Use SSMA using the Access Northwind 2007 template.

Running the Assessment Report

There are two ways of viewing the assessment report in SSMA. The first is way is to access the report after running the Migration Wizard as shown below.

00 Error Report using Migration Wizard

When you run the report via the wizard, you’ve already completed the migration so there is no opportunity to correct any errors before perform the Convert Schema and Migrate Data commands. There is a nice feature within the Migration Wizard dialog that allows you to click on the hyperlink total of messages to display the Error List dialog.

The second way of launching the error report is to not use the wizard and follow these steps:

  1. Create a New Project and complete the dialog.
  2. Issue the Add Databases command and select the database you want to migrate.
  3. Expand the Access-metadata node in the Access Metadata Explorer. Clear the checkboxes except for the database you want to run the report on.
  4. Right-click on the database and select Create Report.
Elements of the Assessment Report

For the Northwind 2007 database, your assessment report will look like this.

03 Assessment Report UI

  1. The navigation pane allows you to drill down to see which objects had problems reported as an error.
  2. The details pane provides a summary of the errors when you select a folder in the navigation pane or all of the errors, warnings, and info messages when you select the actual object.
  3. The tool bar allows you to filter the lower pane by errors, warnings, or info messages by clicking on the element.
  4. The tool bar displays the estimated manual conversion time required to resolve all of the errors displayed
  5. The display pane allows you to group by specific error, warning, or info messages. It also provides a way to navigate the specific object and all messages associated with the object. The image below shows that happens when you select the Employees table under the “Datatype is not supported” error message.
    04 Assessment Report detail for object Employee
Considerations for Migrating Access Queries

When you manually run the report by selecting all objects, you may get more errors reported because SSMA reports against potential issues when attempting to migrate Queries into SQL Server View statements. In general, you will want to take a linked table approach when migrating your solution. This approach keeps the Query definition in Access as is and relies on using the linked table references in the query to process the results using the Jet engine.

The recommended approach is to uncheck Queries in the Access Metadata Explorer before running the report. This approach also prevents SSMA for creating Views on SQL Server when you eventually issue the Convert Schema and Synchronize with Database commands.

Using the Error List

Assuming that you converted Northwind 2007 using Migration Wizard with the Linked Tables option, you will see and Error List when you close the Assessment Report and the Migration Wizard as shown below.

06 Error List Basics

By clicking on the 245 Warnings and 233 Information controls highlighted above, you can show just the 6 Errors reported after the conversion.

07 Error List Details

By double clicking on the error, SSMA takes you to the object associated with the message in both the Access Metadata Explorer and SQL Server Metadata Explorer and displays the definition of the objects as shown below.

08 Error List Customer Example

You can then see how SSMA mapped the unsupported data type in SQL Server from Access by scrolling through the Access and SQL Server table definitions.

Dealing with Attachment, Hyperlink, and ComplexLong Data Types

It’s been our observation that Access solutions that need migration to SQL Server very seldom use the problem data types used for the Northwind2007 sample database. Here are some things to consider if you do need to migrate these types.


The Attachment type stores the file in Access database. In SQL Server, you have several options to consider. You will want to extract the files from the Access database and then consider storing links to the files in your SQL Server database or using the FILESTREAM feature to maintain the attachments. In the upcoming SQL Server code name “Denali” release, you can use the new FileTable feature.


The Hyperlink data type ends up getting mapped to a nvarchar(max) type by SSMA. You may want to alter the result using the table editing feature in SSMA’s SQL Server Metadata Explorer or using SQL Server Management Studio to use a smaller value. In your Access solution, you can still use the hyperlink behavior in Forms and Reports if you set the Hyperlink property for the control to true.


The ComplexLong data type is the result of using a lookup query and choosing the multi-selection option for the Long data type. Underneath the covers in Access, there is a junction table that records the results of the multiple selection. In SQL Server, you can emulate the same behavior with a junction table based on the query and then reference the junction table in your Access solution.


As you can see, SSMA provides two mechanisms to help you identify potential issues that you may need to deal with for migrating an Access database to SQL Server. The Assessment Report and Error List user interfaces are also part of SSMA for MySQL, Oracle and Sybase and work in a similar way. The time estimates included in the Assessment Report are just a guideline to help in estimating the scope of the changes needed and can vary greatly in your approach to solving a specific issue.

Additional Resources and References

For specific details on each of the project options, please refer to the Project Settings help topics for SSMA for Access.

Learn How To Create Access Web Databases with Office 365’s SharePoint Online Beta from my Latest Webcast

image My Moving Access Tables to SharePoint 2010 or SharePoint Online Lists Webcast (posted 5/24/2011) describes how to move and link Access tables to SharePoint lists. As a bonus, it also includes detailed instructions for and an example of using SharePoint Online‘s Access Services to create a Web Database from a SharePoint (not Access) Online Contacts template.

Click here to watch the Webcast and download the PowerPoint slides.

image If you’re familiar with the process of moving Access 2007 or 2010 tables to SharePoint Server 2007 (MOSS), Windows SharePoint Services (WSS) 3.0, or SharePoint 2010 lists, skip directly to 00:22:16 for a detailed demo:


You also can learn how to create and synchronize a SharePoint 2010 or SharePoint Online Workspace to your local computer, starting with this slide at 00:16:00:


Here are live links to the the bibliography for the latest Webcast and to earlier Webcasts:

Read Office 365 vs. Google Apps: Microsoft Comes Out Firing, an 5/20/2011 interview of Microsoft’s Tom Rizzo (pictured below) by Paul Thurrott of Windows IT Pro. A few choice excerpts:

image Cue Microsoft Senior Director Tom Rizzo, who I'd categorize as a breath of fresh air at a company I was getting seriously worried about.

"I am not going to let a competitor determine the dialog about my product," Rizzo told me during a phone interview last week. "I'm going to uncover the truth. We were a bit of the silent giant before. Now we're not so silent."

Those are fighting words. And while it's perhaps a bit of a stretch to suggest that I actually leapt out of my seat and punched the air with my fist, it happened in spirit. This is the Microsoft I've been missing for these many years.

So what was Rizzo talking about? We met to discuss the competitive landscape in the online services space, which is to say he wanted to compare how Google's offering—Google Apps—stacked up against Microsoft's forthcoming Office 365. As a matter of full disclosure, I've been using Office 365 for months, and as a long-time Gmail (and Google Calendar) user, I had previously evaluated Google Apps and found it—how you say?—lacking. So I was already on board with this line of thinking, frankly, because in my (hopefully non-partisan) mind, Microsoft clearly has the superior solution of the two.

But let's see what Mr. Rizzo has to say about this. After all, I love the approach.

"Google is making a lot of noise these days," Rizzo noted. "But the interesting thing is that Google's bark is worse than its bite. And of those few customers who dropped Microsoft solutions to 'go Google,' many are now coming right back." …

"Google Apps has made absolutely no dent in the market at all," Rizzo claimed. "They are failing. If I had to give them a grade, it would be an F. They're just throwing darts at the wall."

So while Rizzo makes up, and then some, for almost a decade of quiet acceptance at the software giant, and I try to wipe this crazy grin off my face, let's consider some numbers. And there are a lot of numbers:

  • 30 million. Google claims that it has 30 million users. But it also noted that these users were spread out between 3 million businesses, schools, and governmental agencies. This means that, on average, there are 10 users per deployment. "These guys are just dipping their toes in the Google water," Rizzo said. "And more often than not, they come back to Microsoft because Google doesn't meet their IT requirements, whether it's a small business or an enterprise. And Google promised it would 'kill Office,' but its [online apps] don't have functionality; this isn't coming to fruition."
  • 9 out of 10. Furthermore, Rizzo says that a survey of Google Apps customers revealed that fully 9 out of 10 were also Microsoft Office users. This means that these customers are not abandoning Office as Google had hoped. "These guys aren't replacing Office," Rizzo said. "They're just trialing Google."
  • <1 percent. According to a late 2010 Gartner survey, less than one percent of enterprise users were using Google Apps. And that's after 4 years in the market. "These customers trust Microsoft," Rizzo noted. "We provide privacy, security, manageability, and a financially-backed SLA. Google is an ad company. They shoehorn consumer stuff into the commercial space and do not understand the needs of commercial users."
  • 10. When Google came to market with Google Apps, it offered both free and paid versions. But the numbers of users it allowed into the free version has dropped precipitously over the years, going from 250 to 100 to 50 and now to just 10, Rizzo said. (Microsoft doesn't offer a free version of Office 365; its small business version supports up to 25 users.) So why the retreat on the free version of Google Apps? "Google is not making money in this space," Rizzo said.
  • $5 vs. $6. "Google just raised prices [on the paid version of Google Apps] to $5 per user per month," Rizzo said. This compares to $6 per user per month for the small business version of Office 365, or a $1 difference. "So for the price of one cup of coffee at Starbucks, the additional value here is astronomical," Rizzo said. "There are no ads in Office 365, whereas Google makes the customer the product by putting ads in there. We earn your trust, we respect your privacy, and you pay us to run a valued service."
  • 97 percent. Google makes 97 percent of its revenues from web ads, Rizzo said. It's an ad company. And when you look at the remaining 3 percent, which includes Google Apps, it has declined 23 percent year over year. "Google is under a lot of pressure to make it a viable product," Rizzo said. "They are feeling the pressure."

Tom runs the SharePoint Product Management team at Microsoft. I worked with him for several years on many stories for Visual Studio Magazine while he was Director of Product Management in the SQL Server team. Tom was the Technical Editor for my Expert One-on-One Visual Basic 2005 Database Programming book (WROX/Wiley, 2006.)

Sunday, May 22, 2011

Bibliography for My “Moving Access Tables to SharePoint 2010 or SharePoint Online Lists” Webcast of 5/23/2011

Downloading and from Windows Live SkyDrive (1 MB) contains the NwindMove.accdb database and a NwindMove_Backup.accdb file for moving tables from an Access application to SharePoint lists with the Export Tables to SharePoint Wizard. These two files are slightly modified versions of those included in the \Chaptr22 folder of the Microsoft Access 2010 in Depth book’s sample files. Download the file here.

imageThe GroupPol.accdb (11,944 KB) database has contact data for 2,275 fictitious employees and 25,344 students of an equally fictitious OakLeaf University. The file is suitable for adding contacts in bulk to Access databases and Web databases created from the Contacts template. GroupPol.accdb is based on the Oakmont database that’s included in the downloadable sample files for my Microsoft Access 2010 In Depth book. You can download, which contains GroupPol.accdb, from my Windows Live SkyDrive account here.

Office 365 SharePoint Online and Administration Forums, Blogs

Office 365 Forums Websites and Collaboration with SharePoint Online:
Office 365 Service Administration:
Office 365 Blogs:
  • Microsoft Office 365 Blog: Interesting in learning more about Microsoft Office 365? Follow our blog to stay up to date with the latest news and information on taking your business to the cloud!
  • Office 365 Technical Blog: The Office 365 engineering team blog, supporting our customers whether getting started, performing advanced tasks or troubleshooting common issues. We love to hear from you, so comment and share your thoughts on the latest and greatest cloud service!
  • The Grid: The Grid is a new community that brings together IT pros and other experts who will share their considerable expertise about technology and Office 365.

SharePoint 2010 TechNet Forums

Note: SharePoint Online doesn’t support Business Connectivity Servces (BCS)

Downloading and Installing SharePoint Foundation 2011

Microsoft’s Download Center’s Microsoft SharePoint Foundation 2010 page contains the following information:


imageSharePoint Foundation 2010 is the new version of Microsoft Windows SharePoint Services. It is the essential solution for organizations that need a secure, manageable, web-based collaboration platform. ​SharePoint helps teams stay connected and productive by providing easy access to the people, documents, and information that they need to make well-informed decisions and get work done. Use SharePoint Foundation to coordinate schedules, organize documents, and participate in discussions through team workspaces, blogs, wikis, and document libraries on the platform that is the underlying infrastructure for SharePoint Server.
Search Server 2010 Express: If you’re using SharePoint Foundation, you can easily add search capabilities to your collaboration environment across SharePoint sites, file shares, web sites, Exchange Public Folders, and third party repositories using Microsoft Search Server 2010 Express. Click here to download.

System Requirements

  • Supported Operating Systems:Windows Server 2008 R2;Windows Server 2008 Service Pack 2

Please review the SharePoint Foundation 2010 system requirements before you proceed. Actual requirements and product functionality may vary based on your system configuration.

SharePoint Foundation 2010 can also be installed for development purposes on Windows 7 and Windows Vista Service Pack 2. Installing on these operating systems is not supported for production deployments. For instructions on how to install on these operating systems, refer to Setting Up the Development Environment for SharePoint 2010 on Windows Vista, Windows 7, and Windows Server 2008.

Signing Up for the Office 365 Small Business (P1) Beta Program


Click the Join the Beta button on the Office 365 for Small Business page.

Evaluating database needs across SQL Server, SQL Azure, SharePoint and Access

imageThe Access Team’s Evaluating database needs across SQL Server, SQL Azure, SharePoint and Access post of 1/10/2011 to the Microsoft Access blog explains how to choose between the four primary back ends for Access front ends:

imageEvery now and then we hear from customers who want some cheat sheet or Excel-based "calculator" to help them decide what technology to base their application on: SQL Server, SharePoint, Access, etc. While in some cases (especially in the extremes) the decision is relatively easy to make, it is very common for the decision to be more complex than what can be obtained from such "calculators." There are many issues to consider: from technical requirements of the application in terms of performance, availability, maintenance, backup, scalability, security, etc., to important business requirements in terms of agility, time to value, and the changing business needs of the organization.

We put together a white paper that provides an overview of a few of these products, including:

  • Access
  • SharePoint
  • SQL Server
  • SQL Azure

The paper also discusses some of the requirement areas to consider as part of the decision-making process.

The most important take-away of this document may be common sense: in many cases, deciding how to best solve data-centric problems requires careful consideration and participation of technical and business experts who are able to articulate multiple angles of the problem (both business and technical) and are able to make an informed decision that addresses the immediate and future needs of the organization. In other words, avoid the temptation to use a cheat sheet! Look around at the people in the room: do you have the right people for the scope of the application you are building? Do you need, and if you do, do you have representation from both IT and the business organization? Do you have the right experts on these technologies helping you make a choice? If you don't, what are the implications if you make the wrong choice? Do you understand how the application and business problem will evolve in the future?

Those are questions that only you can answer and for which a calculator (or a white paper!) is really not the best approach. We hope that this white paper helps you understand the core strengths and capabilities of these technologies and gives you a starter set of issues to consider in discussing your selection. The good thing is that we got you covered: from Access to SharePoint to SQL Server and SQL Azure, your application will likely benefit from one or more of those products.

(Word 2007-2010 .DOCX format)

(Word 97-2003 .DOC format)

SharePoint Lists

imageMicrosoft Access 2010, 2007 and 2003 can link front ends to SharePoint 2010 Foundation, Server 2010 Standard or Enterprise Edition, or SharePoint Online lists. A SQL Server [Express] instance holds the data for SharePoint lists. Following are sub-topics of the Microsoft patterns & practices described in the MSDN Library’s SharePoint Guidance topic:

SharePoint Columns, Lists, and Content Types

imageData models in SharePoint 2010 are implemented using columns, lists, and content types. A full understanding of these constructs underpins every effective data model in SharePoint 2010.

List Relationships in SharePoint 2010

SharePoint 2010 allows you to create relationships between lists in the same site collection. List instances are related through lookup columns (as known as lookup fields). The real benefit of this functionality is that SharePoint 2010 allows you to use join statements, in LINQ to SharePoint or in collaborative application markup language (CAML), to query across lists where lookup column relationships are defined. By default, SharePoint permits a maximum of eight joins per query, although administrators can change this limit through the Central Administration Web site or by using PowerShell. However, queries that contain large numbers of join statements are resource-intensive, and exceeding eight joins per query is likely to have a significant detrimental effect on performance.

Although this newly introduced ability to query across lists brings the capabilities of SharePoint data models closer to those of relational databases, SharePoint support join predicates only where a lookup column relationship exists between the lists. In this regard, the join functionality in SharePoint is less powerful than the JOIN predicate in SQL.

Query Throttling and Indexing

A familiar challenge when you work with SharePoint lists is how to address the performance degradation that can occur when your list contains a large number of items. However, SharePoint is capable of managing extremely large lists containing millions of rows. The often-quoted limit of 2,000 items per list, actually refers to the maximum number of items that you should retrieve in a single query or view in order to avoid performance degradation. Effective indexing and query throttling strategies can help you to improve the performance of large lists.

Note: For more information about working with large lists, see List Patterns, "Designing Large Lists and Maximizing Performance" from Performance and capacity test results and recommendations on TechNet and Handling Large Folders and Lists on MSDN.

Data Platform Improvements in SharePoint 2010

imageThe Access Team’s Data Platform Improvements in SharePoint 2010 post of 2/15/2011 describes changes to SharePoint lists that apply to their use as linked Access 2010 back ends:

imageDatabase Relationships, Unique Columns and validation rules make it easy to validate your data. This creates much more maintainable applications and helps users to do the right thing the first time.

Some of the feedback we received from blog readers and developers for Access/SharePoint integration was to have better data platform features to ensure the data integrity they expect from a relational database.

In SharePoint 2007 validation such as required fields and min/max values are only enforced through the browser user interface. This means that applications talking to SharePoint via the SOAP Web services or the Object Model will be able to bypass those rules.

The data platform improvements we included in SharePoint 2010 are enforced at the at the data layer to make sure that no matter how an application/user interacts with the backend lists (Browser, Office Client, Web Service, Object Model), the rules will be enforced.

List Relationships

In SharePoint you can create lookups between lists, in 2010 lookups can enforce cascade delete and restrict delete relationships between the two lists.

In Access you can use the lookup wizard to set up a relationship.


You can use this to create simple 1:Many or Many:Many relationships between lists that are enforced at the data layer.

TIP: Use the lookup wizard to create SharePoint supported relationships in Access 2010. If you are working against a published web database—you will need to sync the table after creation before you can create lookups

TIP: Web Databases do not support relationship view. So if you want to see a view of all your relationships use the database documenter or create a dummy query with all the tables.

Unique Column Constraints

You can use unique constraints to make sure that duplicate values are not entered in specific column. Multiple unique constraints can be defined on a table and they allow for NULL values.

You can use these to enforce rules such as making sure an order number is unique across all orders. These constraints are enforced at the data layer.

The property is set through the Table Tools | Fields | Field Validation | Unique check box in the ribbon.


Tip: Unique columns are not supported for attachments, table based lookups (foreign keys), multi-value lookups, memo, hyperlink and Boolean fields.

Data Validation

As in Access, validation can be enforced at the table/list level and at the field/column level. You can supply an expression that must be true whenever you enter or change data. This allows a developer to validate data before it goes into the database. If the condition is not true the developer can provide a customer error message. The properties are set through the Table Tools | Fields | Field Validation | Validation dropdown in the ribbon.


When you are in SharePoint you use Excel expressions to create your rules but in Access you continue to use the familiar Access expressions and we handle the translation back and forth from the server.

In the browser we remote simple expressions to JavaScript to notify the user of any issues before they try to commit the record. This creates a more responsive user experience and reduces calls to the server.

This is our Country field in the browser


If I enter invalid data and tab off the field is automatically highlighted


When I place my focus back into the field the validation message is displayed so that I know how to correct my issue.


More advanced expressions like the sample below get evaluated at save and produce an error prompt.


Tip: You can use NOT, <>, <, >, <=, >=, IN, BETWEEN, LIKE (?, *, # as wildcard characters) and IS NOT NULL as comparisons on your expressions. Table and field validation rules are enforced at the data layer.

One of our testers and author of Access 2007 Inside Out, Jeff Conrad, sent me this great sample;

In the EmailAddress field, we want to be sure the e-mail address provided by the user appears to be a valid e-mail address. We can verify the e-mail address meets most standards of valid syntax by using a combination of the LIKE operator and wildcard characters in a field validation rule. In the blank text box at the top of the Expression Builder dialog box, type Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*")) for the field validation rule.

This field validation rule ensures that every e-mail address provided by the user starts with at least one character followed by the @ symbol, contains at least one more character following the @ symbol, and contains the dot symbol followed by at least one more character after the dot symbol.

Also, this field validation rule does not allow a space, a comma, or a semi-colon anywhere in the email address.”

Enforcing SharePoint validation at the data layer

SharePoint also has a couple more validation options for required fields and min/max values for numbers. For Access Services lists we enforce those at the data layer as well.

Tip: There is a list OM property called SPList.EnforceDataValidation that gets or sets a Boolean value specifying whether required fields and min/max values are enforced at the data layer. You can set this on any SharePoint 2010 list to get this behavior.

More Reading

List Relationships and Unique Columns from the SharePoint perspective(

More info on data validation in general for Access from 2007 (

Leveraging Excel and Access Services in SharePoint Online

imageMSDN’s Leveraging Excel and Access Services in SharePoint Online page provides links to hands-on labs and videos for Access Services and Web Databases:

Excel and Access Services provide powerful features for building SharePoint Online solutions. In this session, you’ll get an inside look at both Excel and Access services and how each can be accessed programmatically when building SharePoint Online solutions.

Hands-On Labs
  • Excel Services

    In this lab you will learn how to add Business Intelligence (BI) capabilities to your SharePoint Online site using Excel Services.

  • Access Services

    imageIn this lab, you will learn how to use SharePoint Access Services to publish an existing Microsoft Access database to SharePoint Online. Doing so will create a new SharePoint web and convert Access tables into SharePoint lists, and Access forms into corresponding HTML and CSS. You will then use this database as a bug tracking database. You will learn how to proactively log exceptions in SharePoint Online.


Note: Office 365 Small Business’ P1 subscription includes Access Online but not Excel Online.

Microsoft SharePoint Online Developer Guide (Beta)

imageThe Microsoft SharePoint Online Developer Guide (Beta) is a 44-page PDF File that the Overview describes as follows:

imageThis guide walks you through some of the rich features that are available to developers and designers in SharePoint Online in Office 365. It provides an overview of the feature set and extensibility points for SharePoint Online, and a discussion of how to create solutions for this new environment. This guide begins by describing the types of solutions you can build, and then addresses the developer tools for SharePoint 2010, the new platform features, and the solution deployment architecture

SharePoint Online’s Enterprise and Storage Features

imageSharePoint Online’s Small Business (P1) and Information Workers (E3/E4) plans offers most of features of SharePoint Server 2010 Standard Edition plus Access Services, which are required to generate Web Databases from Access databases. The E3/E4 plans include Excel Services, Forms Services and InfoPath Services. Kiosk Workers in plans K1/K2 and Information Workers in plan E1/E2 don’t have Access, Excel, Forms or InfoPath Services. All plans include an initial 50 GB of online storage, plus 500 MB for each additional licensed user.

imageFor more information on what’s included in the five different beta licensing plans, download the “Microsoft Office 365: Microsoft SharePoint Online Beta Service Description” as Microsoft_SharePoint_Online_Standard_Beta_Service_Description_Final.docx from the Office 365 Beta Service Descriptions link. From the Beta Service Descriptions:

Service Availability

Like all Microsoft Online Services offerings, SharePoint Online has 99.9-percent scheduled uptime with financially backed SLAs covering any instance when this service availability standard is not met.


The SLA is not offered during the Office 365 Beta period.

Deleted Item Recovery

SharePoint Online users have a Recycle Bin where deleted content is stored. They can access the Recycle Bin to recover deleted documents and lists if they need to. Items in the Recycle Bin are retained for 30 days. The following data types are captured by the Recycle Bin:

  • Lists
  • Libraries
  • Folders
  • List items
  • Documents
  • Web Part pages

The following data types are not captured by the Recycle Bin:

  • Deleted Sites
  • Deleted web pages
  • Site customizations made through SharePoint Designer 2010
Data Backup

Data protection services are provided to prevent the loss of SharePoint Online data. Backups are performed every 12 hours and retained for 14 days. Note that this describes the data backup services as offered when SharePoint Online is generally available.


The data protection services policy may be different than described during the Beta period

Service Continuity Management

SharePoint Online is hosted in Microsoft-managed, enterprise-level data centers that are designed to operate highly available online services. Because of this, the Microsoft SLA with SharePoint Online subscribers is set at 99.9 percent uptime.

However, service availability can be affected by hardware failures, natural disasters, and human error. To address this, SharePoint Online offers service continuity management, a process for managing risks to ensure that a company’s IT infrastructure is capable of providing continuing services if normal availability solutions fail. Service continuity management for SharePoint Online includes provisions to quickly recover from such unexpected events.

Two metrics commonly used in service continuity management to evaluate disaster recovery solutions are a recovery time objective (RTO), which measures the time between a system disaster and the time when the system is again operational, and a recovery point objective (RPO), which measures the time between the latest backup and the system disaster, representing the nearest historical point in time to which a system can recover. SharePoint Online has set an RPO and RTO in the event of a disaster:

  • 12-hour RPO: Microsoft protects an organization’s SharePoint Online data and has a copy of that data that is equal to or less than 12 hours old.
  • 24-hour RTO: Organizations will be able to resume service within 24 hours after service disruption if a disaster incapacitates the primary data center.
Virus Filtering

Forefront Security for SharePoint is included with SharePoint Online to help protect the SharePoint Online environment from viruses while maintaining uptime and optimizing performance.

Forefront Security for SharePoint provides comprehensive protection for SharePoint document libraries using multiple scan engines and content controls to help eliminate documents that contain malicious code.

Understanding Office 2010’s SharePoint Workspace

TechNet’s SharePoint Workspace 2010 overview topic of 5/12/2010 describes Office 2010’s Sharepoint Workspace feature as follows:

imageThis article describes the key functionality and architecture of Microsoft SharePoint Workspace 2010. SharePoint Workspace 2010 is the new name for and succeeds Microsoft Office Groove 2007. SharePoint Workspace 2010 is a client application that provides fast, any-time interactive access to document libraries and lists on Microsoft SharePoint Server 2010 and Microsoft SharePoint Foundation 2010. SharePoint Workspace 2010 also provides options for creating Groove peer workspaces and Shared Folder workspaces. SharePoint Workspace 2010 is more versatile than Microsoft Office Groove 2007 and can be integrated with Microsoft SharePoint Server 2010 or can run independently.

imageMicrosoft SharePoint Workspace 2010 provides a client for Microsoft SharePoint Server 2010 and Microsoft SharePoint Foundation 2010 that enables real-time synchronization of desktop content with SharePoint documents and lists. SharePoint Workspace 2010 also provides options for creating Groove collaboration workspaces and synchronized shared folders. By using SharePoint Workspace 2010, information workers can easily synchronize online and offline content with a designated SharePoint site or collaborate with external partners and offsite team members through shared workspaces. SharePoint Workspace 2010 is included with Microsoft Office Professional Plus 2010. …

The article continues with “Key features of SharePoint Workspace 2010.”

Creating SharePoint workspaces from Office 365’s SharePoint Online is disabled in Safe Mode.

Thursday, May 19, 2011

Office 2010 SP1 on track for late June

Ryan McMinn posted Office 2010 SP1 on track for late June to the Microsoft Access blog on 5/17/2011:

image We're on track to deliver Office 2010 SP1 in late June 2011 (here's yesterday's official announcement). We definitely recommend this update for all Office 2010 users. There has been great work put into this service pack that further enhances the performance and security of Access. Here are a couple of highlights:

  • Fixed issue in export to Excel to make sure it always export the data based on the current view.
  • Improved the performance of publishing client forms from Access with embedded images.

Additionally, if you're one of the few 64-bit Access users working with compiled Access databases (ACCDE, MDE, and ADE files), be sure to check out this KB article that details an update process you'll need to go through to have your files work properly with SP1.

Let us know what you think!

-- Ryan McMinn, Senior Program Manager Lead

Access to SQL Server Migration: Understanding SSMA Project Settings

Bill Ramos published Access to SQL Server Migration: Understanding SSMA Project Settings on 5/17/2011:

In many cases, the Migration Wizard for Access will successfully migrate your Jet based Access solution to SQL Server. You may find a need to change your project options for the desired result. In this blog, I’ll describe how to use the project options available in the SQL Server Migration Assistant (SSMA) for Microsoft Access. This blog builds on Access to SQL Server Migration: How to Use SSMA using the Access Northwind 2007 template. In addition, I’m using SSMA for Access v.50 that is now available for download.

Getting Started with Project Options

For all versions of SSMA, there are two levels of project options: Default Project Settings and Project Settings. Changing the Default Project Settings affects subsequent projects that you create in SSMA. These settings can be overridden using the Project Settings option from the Tools menu and saved with your project. If you are using the Migration Wizard and you want to control the project settings, you should use the Default Project Settings dialog before running the wizard.

New with SSMA for Access v.50, the Default Project Settings has a Migration Target Version that allows you to select SQL Server 2005, SQL Server 2008 (the default), SQL Server “Denali” (the next major release of SQL Server) or SQL Azure. The global options you set will be specific to the target you select when you create a new project.

Default Project Settings

Project Options for SQL Azure

If you are migrating to SQL Azure, an extra page is available as shown below.

01 SQL Azure Default Project Settings

The Heartbeat Interval allows you to adjust the time interval that SSMA uses to keep the project connection alive to the SQL Azure database. The SQL Azure Server Suffix value allows you to adjust the server name suffix for connecting to your SQL Azure server.

Using the Mode Option for Controlling Conversion and Migration Project Settings

Before you go about making changes to your settings, SSMA provides four ways to control the settings used for the Conversion and Migration project settings that you can select using the drop down list control shown below.

02 Mode options

The four options are called Default, Full, Optimistic and Custom. These modes have to do with the level of tolerance that SSMA applies to the settings. When you change the value, all of the values are adjusted to one of the three default settings. If you make any changes, from one of these default settings, SSMA changes the mode to Custom. If you want to revert your custom settings back to one of the three default modes, just select the desired node from the drop down.

Impact of Mode Settings for Conversion Options

The following table shows the impact of the conversion options settings based on the mode you select.


With regards to the above options, here are a few points to consider.

  • Add primary key. This is generally a good option to have set true to make sure that you can uniquely identify records for update and delete actions. SSMA will provide you a notification for tables that it adds a primary key to so that you can double check the result.
  • Warn when a complex query uses a wildcard. This option is generally good to consider setting as true to help in limiting the data based between SQL Server and Access. You will need to look at the objects like forms and reports that refer to the query to specify the columns really needed for your application.
  • Add timestamp column. This option helps applications interacting with the table to implement optimistic concurrency. The timestamp data type is marked for deprecation and is replaced by the rowversion data type. SSMA still uses the old timestamp data type. You can still use the timestamp data type for SQL Server code named “Denali” so there is no need to update change to rowversion for now.
Impact of Mode Settings for Migration Options

The following table shows the impact of the data migration settings based on the mode you select.


The mode setting does not impact the options for Replace unsupported dates and Batch size.

With regards to the Keep identity option, I recommend always keeping this set to True to make it easier to validate the result with primary and foreign key values for related tables.

Loading Objects Project Settings

There are five settings as shown below for controlling how SSMA attempts to load objects into SQL Server and for synchronizing your project as shown below.

04 Loading Objects

You can generally stick with the default options for Synchronization for SQL Server items.

The Attempts option may need adjustment if you have complex foreign key relationships. SSMA loads objects into SQL Server typically using multiple passes. Objects that fail to load in the first pass, such as foreign keys, might successfully load in the next pass. You may need to increase this value for your Project Settings if SSMA reports errors while loading objects.

GUI and Type Mapping Options

The GUI tab allows you to configure the number of rows that SSMA displays using the data tab for the source and target tables. In general, there is little need to change these default values. Just be aware of them when you are comparing data using SSMA that you will only see the first 100 rows by default.

For information about the Type Mapping options, please refer to the blog post “Access to SQL Server Migration: Understanding Data Type Conversions”.

Additional Resources and References

For specific details on each of the project options, please refer to the Project Settings help topics for SSMA for Access.

Optimizing Microsoft Office Access Applications Linked to SQL Server describes in detail many of the techniques that were only mentioned briefly in this blog post.

The UtterAccess forum has a discussion group dedicated to SQL Server questions You can post questions and get answers from Access experts who work with SQL Server on a daily basis.

The FMS Upsizing Resource Center contains links to various resources

JStreet Technology's developer downloads section has useful tools and presentations to help you get up to speed.