SharePoint 2010 Recycle Bin Capabilities – Do you know how it really works?

Do you really know how 2-stage SharePoint Recycle bin works? If your answer is yes, please beware and ensure you are aware of what’s in this article. Recently one of my clients asked me question regarding how 2-stage SharePoint Recycle bin works and its impact on the database sizing. It was interesting because recycle bin and its impact on database sizing is one of the most overlooked topic in the SharePoint world. Not many people know that by default, SharePoint 2nd stage recycle bin would increase site collection storage quota to 50% of originally specified during site collection creation process. For the small site collection, it may not matter but for the larger site collections, this would make huge impact on overall architectural decisions.

I had been taking notes on how 2-stage SharePoint recycle bin works since MOSS 2007 days but never blogged about it because of its trivial nature. Knowing how 2-stage recycle bin really works is one of the most important weapons in SharePoint architect armory. Thanks to Todd Klindt and Shane Young’s SharePoint 2010 Administration book, I was able to refine my notes and here are hopefully all the information you need to know how 2-stage SharePoint recycle bin really works and how it can help you to make intelligent decisions around this most overlooked functionality.

First-Stage/Site Level/End-User Recycle Bin

 

• Accessible from the “Recycle Bin” link on the Quick Launch bar at the top level or sub site level
• Available to the users with Contribute, Design, or Full Control Permissions
• When user deletes the item in the list/library, items are moved to the first level recycle bin. It stays there until it remains purged by either the user or automatically deleted after number of days based on central admin recycle bin retention settings.
• Users can restore the item from the recycle bin or permanently delete the item from the recycle bin
• If second-stage recycle bin is not enabled, items deleted or automatically purged from this recycle bin will be permanently deleted from the content database.
• Items located in this stage counts towards the Site Collection Quota.

Second-Stage/Site Collection Level Recycle Bin

•  Accessible from the Site Collection Administration section from the Site Settings page
• Available to the users with Site Collection Administrators Role
• When end-user deletes the items from their site recycle bin, it will be moved to the Site Collection recycle bin. It stays there until site collection administrators flush them or automatically deleted after number of days defined in central admin recycle bin retention settings or until the second stage recycle bin has reached its allocated size limit defined in the central admin second stage quota settings, in which case the oldest items are permanently deleted.
• Items located in this stage doesn’t count towards the Site Collection Quota. Second-Stage Recycle Bin quota separately defined in the Central Administration per application basis.
• Site Collection Administrator can manage both recycle bin
• Rollup View of All First-Level/End-User Recycle Bin – Site Collection Admin can restore one or many items, delete one or many items or empty end-user recycle bin. This is rollup view of all the site level recycle bin at the Site Collection level.

• Site Collection Second-Stage Recycle Bin – All the items deleted from site level/first-level recycle bin gets moved to the second-stage recycle bin. Site Collection Admin can restore one or many items or permanently delete the items from the 2nd stage recycle bin and content database. Items deleted from this recycle bin is not recoverable.

 

Recycle bin settings are managed at the Web Application Level.

 

• By default, Recycle bin is ON.
• You can enable/disable recycle bin at the web application from the Central Admin -> Application Management -> Manage Web Applications -> Select Web Application -> Select General Settings from the Ribbon
• Disabling Recycle in at the web application level will still show the “Recycle Bin” link on all the pages but it wouldn’t hold any information to restore at the site or site collection level.
• Please note. This is very Important Info => Disabling Recycle bin even for a minute will immediately flush all the data from both recycle bins and those contents are unrecoverable.

• Configure the recycle bin retention period – define when items in the recycle bin will automatically purged – by default is after 30 days.
• You can configure to never purge from the recycle bin.
• Note that this retention period reflects the total time after the item was initially deleted. In other words, total time spent by the item in both recycle bins.
• Example: With the default value of 30 days, if item never deleted by the user in the first recycle bin, it will be automatically deleted permanently from the first and second recycle bin after 30 days. If item is deleted from the first recycle bin after 10 days, item will be permanently deleted from the second stage recycle bin after 20 days.

• Configure the second-stage recycle bin – You can define the second stage recycle bin storage quota. by default, it’s a 50% of site quota. Maximum is 100% of site quota.
• You can completely turn off the second-stage recycle bin which will disable the second-stage recycle bin.
• Keep in mind that first stage of the recycle bin counts towards your site collection quota but Second stage of recycle bin has its own quota defined by these settings.
• Example: If you define 100 GB site quota per site collection in the web application and allocate 50% quota for the second stage recycle bin, SharePoint will allocate 50 GB storage quota to the second-stage recycle bin making it 150 GB storage quota per site collection on the web application in the content database. This may have large implication of content database sizing and SQL Server storage requirements.
• Here is most important and potentially scary nature of 2nd stage recycle bin => If site quota is not configured at the site collection level then recycle bin quota is unlimited.

SharePoint Cross-Farm Documents Sharing – Pull vs Push vs Mixed Approaches

In the organizations where SharePoint is used for both Extranet and Intranet, extranet or partner hub is commonly used to collaborate on documents with customers or partners. Additionally there are fairly common needs where internal documents stored in corporate intranet needs to either make available or publish to the customer extranet portal.

I have been recently involved in the series of architectural discussions where we discussed various ways to make internal facing documents stored in the corporate farm available to the customer facing extranet farm.  This article provides high level overview of different architectural options and pro and cons of each approach.  There are basically three approaches when it comes to share documents or data between two systems – Pull Methodology, Push Methodology, or Mixed Approach with Document Warehouse.

Security and Architectural Assumptions

• No direct authorization of Customer or Partner Credentials in Internal systems
• No Pass-through of Customer or Partner User Security using Kerberos or Claims, Instead use Metadata
• Typically there are Three Major Types of Documents
• Non-User Specific documents – e.g. Corporate Communication, Public Facing Documents
• Semi-User specific documents – e.g. Industry Specific Documents
• User specific documents – e.g. Customer Specific Documents

 Pull Methodology – Pull documents directly from Source System

• How?
• Most of the work happens on the destination System
• Destination System will Search and Download documents from the source system using Client Object Model and WCF Services Model. WCF Services plays worker role as middle tier between two farm and it can be hosted on Destination or Source SharePoint Farm in ISAPI folder running in SharePoint Process or Standalone server on IIS. There are also needs for Masking Internal Documents URLs in External Farm.
• Internal documents are accessed by External Systems using combination of Service Accounts and Metadata. There are also needs for Metadata Sync process to make sure both internal and external systems accessing data with same metadata. May be common Metadata Management System would help.
• Requires source system documents flagged as internal vs. external or publish to external facing unless all the documents available for external systems

• Pros
• Single Version and Single Source of Documents
• No additional storage considerations

• Cons
• Requires Source System should be available all the time. Outage of source system for maintenance purpose makes documents unavailable in destination farm.
• Data Security is huge concern because source system may contain both external and internal facing documents. Security Hardening concerns to pull the documents directly from the source system. Requires proper metadata, service accounts, and IT governance to harden the security.
• Latency and Performance Issues to pull the documents or data from source system in real-time.
• Searching source system documents requires search connectors or BCS or cross-farm search configuration
• Requires proper asynchronous document download process to download documents from the source system.

• Final Thoughts
• Having single source of documents may be great idea but accessing Internal Documents in Real-time with service accounts and metadata may not be best secured approach. Internal systems  may contain both internal and external facing documents and poor service accounts & metadata management along with improper code logic may expose non-external facing documents to the external systems. Additionally, outage of the internal systems would directly affect the documents availability on the external farm.

Push Methodology  – Push documents directly to destination System

• How?
• Most of the work happens on the Source System
• Destination Farm will provide document center or document libraries where source system would publish documents to share with the destination farm.
• It would require Workflow or Document Publishing process to push documents from source farm to the destination farm. Additionally, there are considerations for Sync, Versioning, Archiving, and Deleting documents if needed.

• Pros
• No Security Hardening and Data Security concerns since documents are pushed to the destination system. Data Security is handled by the destination SharePoint sites.
• Search against Pushed documents. No need for search connectors or BCS or cross-farm search.
• Best Performance or latency due to accessing documents from the destination systems directly.
• Doesn’t Require Source System should be available all the time. Outage of source system for maintenance purpose doesn’t affect documents availability in destination farm.

• Cons
• Additional Storage Consideration
• Multiple copies of same documents in Source and multiple Destination Systems. Requires Sync or Versioning or requires proper workflow or business rules to publish to the destination systems.
• Multiple destination systems requires pushing documents to the multiple destination systems. In future, additional destinations requires updates on the source systems to push the documents to additional destination systems.

• Final Thoughts
• This approach may work best when you have 1 to 1 internal and external environments. In case of multiple external facing environments, internal documents gets copied to multiple places and there might be situation where lots of duplicate documents and contents gets created over the time.

Mix of Pull vs Push Methodology – Document Warehouse or Publishing Hub

• How?
• This is combination of both Pull and Push Approach.
• Needs to create Publishing Hub or Document Warehouse to host the external facing documents. Content Type Hub may help to publish documents from Internal Farm to Publishing Hub Document Center. This may allow opportunity to flatten out hierarchical internal document libraries structure in publishing hub.
• It would require Workflow or Document Publishing process to push documents from source farm to the publishing hub. Additionally, there are considerations for Sync, Versioning, Archiving, and Deleting documents if needed.
• Destination System will Search and Download documents from the Publishing Hub using Client Object Model and WCF Services Model. There are also needs for Masking Internal Documents URLs in External Farm.
• Publishing Hub documents are accessed by External Systems using combination of Service Accounts and Metadata. There are also needs for Metadata Sync process to make sure both internal and external systems accessing data with same metadata. May be common Metadata Management System would help.

• Pros
• Better Data Security than Pull Methodology. Managing data security in the publishing hub is less critical than source systems since publishing hub would contain mostly external facing, read only documents. It would require proper metadata, service accounts, and IT governance to harden the security for customer specific documents.
• Doesn’t require source system should be available all the time. Outage of source system for maintenance purpose doesn’t affect documents availability in destination farm.

• Cons
• Additional Storage Consideration
• Latency and Performance Issues to pull the documents or data from source system in real-time.
• Multiple copies of same documents in Source and Publishing Hub Systems. Requires Sync or Versioning or requires proper workflow or business rules to publish to the destination systems.
• Searching source system documents requires search connectors or BCS or cross-farm search configuration
• Requires proper asynchronous document download process to download documents from the publishing hub.

• Final Thoughts
• This approach would provide best of both worlds. It would provide adequate data security, control over what documents available for external facing systems, and isolation of both internal and external farm.

On the final note, although this article discusses Internal vs External farm scenario, It can be easily applied to two internal and external web applications within same farm.

Good, Better, and Best – SharePoint 2010 Team-based Development Environments

SharePoint 2010 Development Basics

• Minimum Hardware Requirements for the SharePoint 2010 development is 64-bit OS, 8 GB RAM, 60-80 GB hard disk space for local development.
• Local SharePoint 2010 installation is required to develop against Server Side Object Model and debugging support. SharePoint 2010 can be installed as farm as long as SharePoint 2010 is installed on only 1 server in the farm. SQL Server can be installed on same machine or another machine. Visual Studio must be installed on the same machine as SharePoint.
• For remote SharePoint 2010 development, you must use the SharePoint 2010 SOAP or REST Web Services Interface or Client Object Model. Please note that you can’t attach the SharePoint 2010 IIS worker processes to debug in remote deployment.
• Preferred Microsoft Desktop or Laptop x64 Operating System is Windows 7 and Server x64 Operating System is Windows 2008 and Windows 2008 R2. Microsoft offers only x64 server virtualization technology in form of Hyper-V. Microsoft doesn’t offer x64 desktop virtualization technology as of writing. Both Virtual PC and Virtual Server is not supporting x64 guest OS.
• This article assumes that developer desktop/laptop has Windows 7 x64 installed on the metal/physical hardware. If you have Windows Server 2008 installed on the developer host machine, you can use hyper-V as free software. Windows 2008 with Hyper-V may be a good choice for server or desktop virtualization but not recommended for laptops. Hyper-V was really never intended for use on laptops because of missing features like Sleep and Hibernation in Windows 2008. I have never liked this option for local development so, we will ignore this option.
• Developer machines can be standardized in all three approaches. Since developers will be local admin on their individual environments, they can install additional software and configure their environment for specific needs.
• This article assumes the individual SharePoint development environment. You can install Single Server SharePoint Farm on the fast, large machine and multiple developers can log in to the server to perform their development work. Having Shared SharePoint development environment can cause major contention of hardware resources during development having multiple Visual Studio processes running at the same time. Additionally, SharePoint solution deployment framework would bounce the IIS worker processes during local debugging and deployment process causing unpredictable issues.
• Ability to share the network resources like AD, TFS, OCS/Lync, Exchange/Lotus Notes, or SQL Server are available in all three options as long as you are connected to the Network and able to access these software. Consultants or Third-party development environment may or may not connect to organization resources to get the common network resources like TFS or SQL Server because their domain can be different than client organization.

Good Approach – Develop directly on Local Developer Machine

• Install Windows 7 x64, SharePoint 2010, Office 2010, Visual Studio 2010, and SQL Server on the developer machine’s physical hardware.  SQL Server 2010 and TFS 2010 can be configured as local or network shared resources.
• Another option is booting up Hyper-V VHD on Windows 7 and install development software directly on VHD. Alternatively, If you are using Windows 7, you can also create a virtual hard drive (VHD) out of an existing Windows Server 2008 image on which SharePoint is installed in Windows Hyper-V, and then configure Windows 7 with BDCEdit.exe so that it boots directly to the operating system on the VHD. I would personally like this option for demos but it’s hard for daily development work.
• Read Joel Oleson’s article for more options – The Great Virtualization Debate: What to do? SharePoint 2010 for Laptops – http://www.sharepointjoel.com/Lists/Posts/Post.aspx?ID=298
• Pros
  • Offline Ability if all required software are installed on the Local Machine
  • Easy access to the environment. No need to boot up the local or network VM.
• Cons
  • Must meet minimum hardware requirements for SharePoint 2010 development – 64bit windows OS (Windows 7, Windows 2008, Windows 2008 R2), 8 GB RAM, 50-70 GB Free Hard Disk Space, Faster SSD hard drive for better performance
  • Can’t be managed by central IT. User Responsible for their own backup. If developer machine is infected by virus or  crashes for some unknown reasons, developer environment can’t be recovered unless its backed up.
  • No ability to take the snapshot except inbuilt windows backup/restore.
  • Requires rebuilding the machine if major changes in the development environment in future. There may be situation if developer needs to work as CRM, BI or other role. In this situation, developer machine needs to be reimaged to avoid the dependency between different software and projects.
  • Overall cost may be cheap or higher depends on the hardware cost for each developer machine. Each developer machine requires minimum 8 GB RAM and 100 GB hard disk space.

Better Approach – Develop on Local VM – Desktop Virtualization on Windows 7 x64

• Only VMware Workstation 7.0 and above supports this option for 64bit Windows 7, it’s paid software. Please note that VMware offers free product called VMware player. VMware Player is the stripped-down version of VMware Workstation and it enables you to quickly and easily create and run virtual machines. However, VMware Player lacks many powerful features, such as Teams, multiple Snapshots and Clones, or Virtual Rights Management features for end-point security found in VMware Workstation and VMware ACE. I would suggest to use VMware Workstation 7.0 and above for 64bit OS support. http://vmfaq.com/entry/5/
• As a central IT in the organization, you can standardize the VM and distribute the VM whenever new consultant or employee joins the team. Team member would perform local development in his VM running on his machine locally and move the code to the integration environment before moving through the staging and production environment.
• Pros
  • Offline Ability if all required software are installed on the Local VM
  • Ability to take the snapshot of VM and go back and forth in the Snapshot tree.
  • Ability to backup the VM on the External hard drive.
  • Ability to run the VM from the External hard drive, no need for enough hard disk space to run the VM
  • Developer can have multiple VMs for different projects and different software requirements. Easy to avoid the dependency by dedicated VMs for specific projects.
  • Easy to port modified VMs to another developer.
• Cons
  • Must meet minimum RAM requirements for the SharePoint 2010 development on the developer machine – 8 GB, Faster SSD hard drive for better performance
  • VMware workstation is paid software. Overall cost may be cheaper or higher depends on the hardware cost for each developer machine. Each developer machine requires minimum 8 GB RAM.
  • Can’t be managed by central IT. User Responsible for their own backup. If developer machine is infected by virus or  crashes for some unknown reasons, developer environment can’t be recovered unless its backed up.
• Recommendation
  • If offline development is major requirement, this approach provides best option as long as Shared network resources like TFS or SQL Server are duplicated to local VM and required sync to shared resources whenever developers able to connect to the network.

Best Approach – Develop on Network VM – Centralized Server Virtualization

• Both VMware ESX and Windows 2008 Hyper-V supports this option
• As a central IT in the organization, you will maintain the centralized virtual environment where you will spin off the VMs as needed on the server. You can standardize the VM and spin off the VM whenever new consultant or employee joins the team. This can be similar as provisioning new AD account.
• Pros
  • Enterprise Level Development Environment.
  • Light developer machine hardware requirement – Developers machine doesn’t require minimum hardware requirements for the SharePoint 2010 development like 64-bit OS, 8 GB RAM, fast SSD hard-disk drive, or enough hard-disk space. They can even use Windows XP machine as long as they can access the network VM. They can even can access the network VMs from any machines. They are not dependent on their machines.
  • Improved IT Manageability – Centrally managed by IT. On-boarding and off-boarding is very easy and managed by IT. VMs can be snapshot every night and backed up as IT intellectual property.
  • Easy to provision/configure the development environment for large project teams. Additionally, Developer can have multiple VMs for different projects and different software requirements. Easy to avoid the dependency by dedicated VMs for specific projects.
  • Both Consultants and employees can work on similar environment including accessing network resources like AD, SQL Server, or TFS.
  • Efficient utilization of hardware resources based on project demand. Easy to scale up and scale-out. Easy to adjust RAM and Hard Drive resources assigned to each VM based on number of developers and project teams.
  • Developers can continuously use their current desktop or laptop for their daily activities like email, non-project tasks, and non-development tasks.
• Cons
  • Requires additional dedicated resource/employee to manage the centralized virtualization environment.
  • Developers required to have connectivity to the network over LAN, VPN, or Remote gateway/terminal servers to access the Network VM
  • Overall cost may be cheaper or higher depends on the hardware cost for each Network VM. Each VM will require 8 GB RAM and 50-60 GB hard disk space on the centralized virtualization environment. Major advantage of this option is you can allocate hardware resources as needed on-demand compare to developer local VM. Additionally, developers can start and stop the Network VM on-demand saving network/server resources during ideal phase.
  • Heavy I/O utilization and SAN storage is key for performance degradation in the centralized  virtualization environment.
  • Possible single-point of failure. Requires in-depth disaster planning and high availability needs.
• Recommendation
  • If you are serious about team based SharePoint development in your environment, centralized server virtualization is most cost effective, flexible,  and sophisticated team based development model as long as network connectivity/offline development wouldn’t be a big issue.

Online Resources

• Application Lifecycle Management in Microsoft SharePoint 2010 – Microsoft SharePoint Developer Documentation Team Blog – http://blogs.msdn.com/b/sharepointdeveloperdocs/archive/2010/11/17/application-lifecycle-management-in-microsoft-sharepoint-2010.aspx
• SharePoint 2010 team development environment – http://blogs.msdn.com/b/vesku/archive/2010/02/01/sharepoint-2010-team-development-environment.aspx

Hope it helps.

SharePoint 2010 Wiki Capabilities

When Microsoft unveiled SharePoint 2010 during SharePoint Conference 2009, there was a great buzz around the new site template called Enterprise Wiki, which was replacement for ever popular MOSS 2007 Collaboration Portal Template. Both Collaboration Template and Enterprise Wiki templates were targeted for intranet and Microsoft was pushing for building internal knowledge management system using the Enterprise Wikis.

As many of us are aware, after nearly 18 months, Enterprise Wiki hasn’t been that much popular nor that much information is available. Even without much information from Microsoft or community events, make no mistake, if used correctly, Enterprise Wiki template along with Managed Metadata Service and Web content management capabilities can build great wiki experience in any organization to collect ad-hoc information from the employees.

As Agile development is becoming more and more popular, formal documentation has become less and less norm. Many software development teams uses agile development as an excuse for avoiding documentation. Although Agile doesn’t support formal documentation, requirements analysis and architectural decisions has to be documented. SharePoint wikis could be great place for informal ad-hoc documentation which can be used to collect the team knowledge over the time.

With this blog post, I have tried my best to list all the core features of the Enterprise Wiki in the SharePoint 2010 and what level of capabilities are available in the free version SharePoint Foundation vs paid version of SharePoint 2010 Server.

OOB SharePoint 2010 Wiki Experience

• Informal Centralized Knowledge Repository – Simple and easily accessible content creation using both structured and unstructured contents on ad-hoc basis in collaborative manner
• SharePoint Foundation Capabilities (of course, still available on the SharePoint Server Product)
  • Implemented as Team Site Template. By default, every team site has a “Wiki Page Home Page” feature enabled which sets the wiki page as Site Home Page.
  • Use “Wiki Pages” document library to create the wiki pages on the SharePoint Foundation Team Site. This feature doesn’t require Publishing feature enabled. Wiki Library Pages uses the in-built SharePoint foundation “Text Layout” feature with limited layout options.
  • Team Sites with Web Edit – Easy In-line Page editing and Cross-browser Rich Text Editor. This allows content to be added quickly including images and web parts without the use of web part zones.
  • Ability to add link to existing content to the Wiki using double bracket (e.g. [[)
  • Wiki Pages content is search enabled
• SharePoint Server Enterprise CAL Capabilities using Enterprise Wiki Template
  • Built on SharePoint Publishing Infrastructure. Enterprise Wiki is a publishing site with the stripped down the publishing process, heavy workflow, and ribbon experience.
  • Unlike the wiki pages in the Team Site, Enterprise Wiki Template offers following major features
    • Ability to create the custom wiki page templates – Enterprise Wiki Pages can be extended using Wiki Page Templates
      • Page layouts provides structured page types – Page templates via content types and page layouts
      • Wiki Categories Site Column -  Custom managed metadata column, Use Terms Management Tool to manage the open term sets.
      • Enterprise Wiki Page Content Type – It adds columns for wiki page  content, ratings, and tracking metadata (e.g. Page Content, Rating (0-5), Number of Ratings, and Wiki Page Categories)
      • Enterprise Wiki Page Layout in the Master Page and Page Layout Gallery -  EnterpriseWiki.aspx uses the PublishingWebControls:RichHtmlField, SharePointPortalControls:AverageRatingFieldControl, and Taxonomy:TaxonomyFieldControl to maintain Wiki Contents, Ratings, and Wiki Categories.
  • In-built Social Experience
    • Allow pages to be rated – Page Ratings – Web Analytics Service
    • Mark pages for easier reference by tagging them with enterprise keywords – Social Tags, Notes, and I Like It for the social feedback – Social Store in User Profile  Service
  • Ability to categorize the wiki pages
  • Customizable  Branding -  Same as in Standard Publishing Site – Upload or create master page or CSS file
  • Improve scalability using output Caching
  • Create a new Enterprise Wiki => Create a new site collection or site – Enterprise Wiki under Publishing Template Section (this replaces a collaboration template)
  • Enterprise Wiki Pre-requisites
    • SharePoint Server Enterprise CAL Product
    • Publishing Feature is enabled at the Root Site Collection if Enterprise Wiki is not at the root folder
    • Managed Metadata service application – Provides storage for social tags and notes.

 Customization Opportunities

• Browser Customizations
  • OOB Customization => Branding – Apply Theme, Add OOB Web Parts – Calendar, Images, Annoucements etc.
  • Build Wiki Pages Navigation and Wiki Pages rollup views using Show Pages on the Navigation Settings or Wiki Links on the home page or adding content query or table of contents web parts.
  • Modify the open term sets or create new term sets  using browser.
  • Create custom site columns, site content types, and page layouts using browser
• SharePoint Designer 2010
  • Create custom site columns, site content types, and page layouts, or custom branding (CSS and master pages) using SharePoint designer
• Automation and Reusability using Visual Studio 2010
  • Custom Branding – Upload Master Pages and CSS
  • Custom Term Sets
  • Custom Site Columns based on Managed Metadata column to use Custom Term Sets
  • Custom Site Content Types based on Enterprise Wiki Page Content Type to use the Custom site Columns
  • Custom Page Layouts based on Enterprise Wiki Page Layouts to use the Custom Site Content Types

How to prevent SharePoint Designer 2010 users from changing a SharePoint Server 2010 site

One of the new features in the SharePoint 2010 is administrative ability to lock down the usage of SharePoint Designer usage in the SharePoint farm environment using the browser interface. In MOSS 2007, there were multiple ways to prevent the SharePoint Designer 2007 to update the SharePoint Site by setting the site template property DisableWebDesignFeatures=wdfopensite either directly by modifying the Onet.XML on 12-Hive (http://support.microsoft.com/kb/940958) or programmatically changing site template property (preferred method).

In SharePoint 2010, locking down SharePoint Designer 2010 usage has been improved to govern the SharePoint Designer 2010 usage/policies in the organization. Here is the high level overview of the configuring SharePoint 2010 Designer usage in the SharePoint farm. 

• Two levels of Setting
  • Granular level control of Site Designer Usage at the Web Application or Site Collection Level
  • Accessing SharePoint sites using the SharePoint Designer 2010 is enabled by default.
  • Web Application Level
    • Manage SharePoint Designer Usage policies at the Web Application Level
    • Two Places to manage
      • Managed at the Central Administration ->  General Application Settings -> SharePoint Designer
      • Managed at the Central Administration -> Application Management -> Manage Web Applications -> Select Web Application -> Select SharePoint Designer from the General Settings on the Ribbon Bar
    • Four Settings
      • Allow SharePoint Designer to be used in this Web Application  – Specify whether to allow users to edit sites in this Web Application using SharePoint Designer.  Please note that this option will still show the “Edit in SharePoint Designer” in the Site Actions menu but it won’t allow the editing in the SharePoint Designer.
      • Allow Site Collection Administrators to Detach Pages from the Site Template  – Specify whether to allow site administrators to detach pages from the original site definition using SharePoint Designer. 
      • Allow Site Collection Administrators to Customize Master Pages and Layout Pages  – Specify whether to allow site administrators to customize Master Pages and Layout Pages using SharePoint Designer. 
      • Allow Site Collection Administrators to see the URL Structure of their Web Site  – Specify whether to allow site administrators to manage the URL structure of their Web site using SharePoint Designer. 
  • Site Collection Level
    • Manage SharePoint Designer Usage policies at the Site Collection Level
    • Managed at the Site Collection Administration section on the Site Settings Page
    • Site Collections can further delegate the SharePoint Designer Permission to the Site Users.
    • Four Settings
      • Allow Site Owners and Designers to use SharePoint Designer in this Site Collection  – Specify whether to allow Site Owners and Designers to edit the sites in this Site Collection using SharePoint Designer. Site Collection Administrators will always be able to edit sites. 
      • Allow Site Owners and Designers to Detach Pages from the Site Definition  – Specify whether to allow Site Owners and Designers to detach pages from the original Site Definition using SharePoint Designer. Site Collection Administrators will always be able to perform this operation. 
      • Allow Site Owners and Designers to Customize Master Pages and Page Layouts  – Specify whether to allow Site Owners and Designers to customize Master Pages and Page Layouts using SharePoint Designer. Site Collection Administrators will always be able to perform this operation.
      • Allow Site Owners and Designers to See the Hidden URL structure of their Web Site  – Specify whether to allow Site Owners and Designers to view and manage the hidden URL structure of their Web site using SharePoint Designer. Site Collection Administrators will always be able to perform this operation.

 

MOSS 2007 to SharePoint 2010 Upgrade Assessment Questionnaires

If you are at the client upgrading existing MOSS 2007 environment to new SharePoint 2010 installation, use this questionnaire to understand the existing SharePoint 2007 environment for SharePoint 2010 SharePoint Migration and Upgrade. This can be during pre-sales process to assess the client environment. I am planning to update this over time.

• Basic Information
  • Do you have any existing SharePoint 2007 environment? If you do, which version of SharePoint is configured – WSS 3.0 or MOSS 2007 standard edition or MOSS 2007 Enterprise Edition? If you do and planning to upgrade to the SharePoint 2010, please provide following information.
  • How many users are currently using the site?
  • What is the current usage profile?  Light, Typical, Heavy, and Extreme.

• Upgrade and Migration Needs
  • Do you have test environment where you can perform the trail upgrade and migration?
  • Do we have any communication plan for planned upgrade and downtime from SharePoint 2007 to SharePoint 2010?
  • Do we have any user adoption policy for SharePoint 2010 and end-user training plans?
  • Do we have any governance plans to support the ongoing growth of the SharePoint 2010 usage?
  • Do we have needs for the Visual Upgrade capability of SharePoint 2010 or do we need to upgrade SharePoint 2007 to SharePoint 2010 permanently?
  • Do we need to access the content in read-only mode while moving from SharePoint 2007 to the SharePoint 2010?
  • How much downtime is afforded during migration and upgrade?
• Are you planning to restructure your current SharePoint 2007 environment in the SharePoint 2010 or are you expecting to use the same taxonomy and URLs?

• Analyze SharePoint 2007 Server Infrastructure
  • Have you run any pre-upgrade checker on your MOSS environment? Please execute the pre-upgrade scan utility and provide us the report.
  • What level of service packs and cumulative updates are installed on the SharePoint 2007 farm? Do you have WSS SP2 or MOSS 2007 SP2 installed?
  • Please provide details about current hardware configurations for each server in the farm?
    • CPU, # of processors, dual core vs. quad core
    • 32-bit vs. 64-bit
    • RAM
    • Hard disk space
  • Networking capabilities
  • Any Additional Hardware Configuration customized to the environment
• What version of Windows Server Infrastructure is running SharePoint 2007 environment? 32-bit Windows 2003 Server, 64-bit Windows 2003 Server, 64-bit Windows 2008 Server, 64-bit Windows 2008 Server R2
• Is the current SharePoint environment configured on a single server or multiple server farms? If it’s configured on a multi-server farm, please provide us details:
  • How many web front end servers are used?
  • Is web front servers are Load balanced? If yes then is it software or hardware Load Balanced?
• How many database servers are used?
• Is database servers are clustered? If yes then is it software or hardware Load Balanced?
• What version of SQL Server is used in the SharePoint environment?
  • SQL Server 2000, SQL Server 2005, SQL Server 2005 Express, SQL Server 2008, SQL Server 2008 R2
  • 32-bit or 64-bit
• If the department SharePoint environment uses MOSS 2007, please provide details about SSP, My sites, Profiles, Index, or Query servers, if applicable.
• Is your SharePoint environment deployed on virtual servers? If yes, please provide details about virtualization technologies, host servers, virtual server details. Please provide the virtual infrastructure diagram, if available
• Please provide details about your SharePoint hosting environment specific to Active Directory, Domain name, and Forest information.
• Please provide the farm topology diagrams, if available – Physical Diagram and Logical Diagram

• Analyze SharePoint 2007 Information Architecture
  • How many sites or site collections or web applications need to be migrated? Please provide the number of sites, URLs for each Site, and any DNS configurations used for each site.
  • Please document the current Site Navigation, Sub Site Structure, or Site Map structure as an outline or Visio format.
  • What is the current size of the SharePoint content databases? Please provide the name and size for each content database.
• Is your current SharePoint environment using Search?  If yes, what kinds of search capabilities are used?
  • Out of box search capabilities or third-party tools
  • Sources used in search environment – SharePoint site, file shares, or any other Non-SharePoint environment
• Search Index File Size

• Analyze SharePoint 2007 Customizations
  • Have you customized the SharePoint 2007 environment using the SharePoint Designer?
  • Does your SharePoint environment have a customized look and feel – Master Pages, Page templates, Style Sheets, Themes, Custom site logos? If yes, please provide details?
• Have you customized the SharePoint 2007 environment using custom code? Do you have custom code available? Have you deployed custom code using Solutions Packages and Features framework? Please provide us details.
• Have you deployed Custom Site Definitions in SharePoint 2007 environment?
• Does your SharePoint environment contain custom web parts or custom workflows or any custom components – Third party web parts, .NET custom components?  If yes, please provide details.
• Have you installed “Fabulous 40 Application Templates” in your environment?

• Analyze SharePoint 2007 Integration
  • Is your SharePoint environment integrated with Email?  If yes, is it integrated with SMTP or Exchange?
  • What version of Office is currently integrated with the SharePoint environment? Office 97, Office XP, Office 2000, Office 2003, Office 2007, or any other
  • Is your SharePoint environment integrated with Microsoft Outlook? If yes, what kinds of Outlook features are integrated with the SharePoint environment – e.g. Calendar, Task Lists, Contact Lists, and Alerts etc.?
  • Is the current SharePoint infrastructure integrated with InfoPath technologies?  If yes, what version is used – InfoPath 2007, InfoPath Form Services, or Office Form Server 2007?
  • Is the current SharePoint infrastructure integrated with SSRS? If yes, what version of SQL Server SSRS is used – SQL Server 2005 or SQL Server 2008?  How many reports are deployed in the current SSRS infrastructure (# of reports)?
  • Is the current SharePoint infrastructure integrated with Office Communication Server for online presence? If yes, what version of OCS is used – OCS 2007?
  • Is the current SharePoint environment integrated with Non-SharePoint systems to present information in SharePoint lists (e.g. Line of Business (LOB) Applications, Business Data Catalogs (BDC) components)?

• Analyze SharePoint 2007 Backup and Restore Process
  • What are the RPO and RTO of the SharePoint Environment? Is this a Mission Critical System?
  • What kind of backup methodologies are used to backup SharePoint environment? Farm Level backup, Site Collection Backup, Data Protection Manager, or third-party/ISV tools? How often do you backup your SharePoint environment?
• Have you ever restored the farm from the backups? Do you have formal recovery farm?
• What kind of backup methodologies are used for Server backups? Are you backing up on Tapes? Do you move the tapes to another location?

SharePoint 2010 Deployment Assessment Questionnaires

If you are at the client deploying new SharePoint 2010 installation, use this questionnaire to install and configure the new SharePoint 2010 environment in the organization. This can be during pre-sales process to assess the client requirements and needs. I am planning to update this over time.

• Basic Information
  • Do you have any existing SharePoint 2007 environment and planning to upgrade to the SharePoint 2010 environment?  If yes, please refer the SharePoint 2007 Upgrade and migration section. If no, are you planning to build new SharePoint 2010 farm?
  • What is the main purpose of the SharePoint 2010 environment? Intranet, Extranet, Document Management System, Digital Asset Management, , or Web Content Management
  • How many users are using the SharePoint 2010 environment? Internal vs. External Users.
  • What is average usage of the SharePoint 2010 environment? Light, Typical, Heavy, or Extreme
  • Do you have any compliance or regulatory needs to isolate the SharePoint Security and SharePoint Content? This would allow you to make single farm vs. multi-farm decisions.
  • Does your SharePoint environment support multi-lingual sites for international users?
  • Do we have any user adoption policy for SharePoint 2010 and end-user training plans?
  • Do we have any governance plans to support the ongoing growth of the SharePoint 2010 usage?
  • Do we have any wireless users accessing SharePoint environment? What are the mobility needs?

• Feature Analysis
  • Do you plan to use the SharePoint Foundation 2010 or SharePoint 2010 Standard Edition or SharePoint 2010 Enterprise Edition?
  • Do you like to configure the Enterprise Search or High-end FAST search product?
  • Do you have any immediate needs of SharePoint 2010 Service Applications? User Profiles, Managed Metadata, Performance Point, Excel Services, Access Services, InfoPath Form Services, Office Web Applications, and Web Analytics Services
  • What are the needs for the basic social features of the SharePoint 2010 – My Sites, Newsfeed, Wikis, Blogs, Ratting, Tagging, Notes, and Bookmarks etc.? What are the needs for advanced social features of the SharePoint 2010 with NewsGator integration – social sites, micro-blogging, newsfeed, and onboarding etc.?

• Server Infrastructure
  • Please provide your current Server, Desktop, and IT infrastructure standards
    • What version of Windows Server Infrastructure is used in your organization? Windows Server 2003, Windows Server 2008, or Windows Server 2008 R2? 32-bit or 64-bit?
    • What version of desktop operating system is used in the organization? Windows XP or Windows 7
    • What version of Office is currently used in the organization? Office 2007, Office 2010, or other
    • What version of browser is supported by the SharePoint 2010 environment? Internet Explorer 7, Internet Explorer 8, Internet Explorer 9, Mozilla Firefox, Google Chrome
    • Is your SharePoint environment will be deployed on physical servers or virtual servers? If yes, please provide details about preferred virtualization technologies.
    • Is your SharePoint environment accessible from outside of the network? Have you configured any extranet technologies? E.g. UAG
    • Do you have a dedicated Active Directory, Windows Infrastructure, Virtualization, and SQL Server teams?
  • Do you meet following server infrastructure pre-requisites?
    • SharePoint and SQL Server Hardware Requirements – 64bit processor
    • SharePoint Foundation 2010 and SharePoint 2010 are available only as 64bit. It requires 64-bit architecture from top to bottom – 64bit Hardware, 64bit Windows OS, 64bit SQL server, and 64bit SharePoint Server Software. There is no 32-bit version of SharePoint 2010 Products.
    • Windows Server Requirements
      • Windows Server 2008 R2
      • 64-bit edition of Windows Server 2008 Standard with SP2
    • Development Desktops
      • 64-bit edition of Windows 7
      • 64-bit edition of Windows Vista with SP1
    • Database Requirements
      • Microsoft SQL Server 2008 R2
      • 64-bit edition of Microsoft SQL Server 2008 with SP1 and CU2
      • 64-bit edition of Microsoft SQL Server 2005 with SP3 and CU3
    • Browser Support
      • Level 1 – Internet Explorer 7, Internet Explorer 8 (32-bit), Mozilla Firefox 3.5
  • Additional Support
    • Microsoft SQL Server 2008 R2 to work with Power Pivot workbooks
    • Microsoft SQL Server 2008 R2 Reporting Services Add-in for SharePoint Technologies (SSRS) to use Access Services for SharePoint Server 2010
    • Microsoft Server Speech Platform to make phonetic name matching work correctly for SharePoint Search 2010
  • How would you like to configure advanced SharePoint needs?
    • What are your needs for high availability, disaster recovery, backup and restore, and redundancy? Do you have any preferred backup and disaster recovery software? Are you planning to use the OOB SharePoint backup and restore methodologies or use the Microsoft Data Protection Manager?
    • Are you planning to configure and implement SharePoint environment for international users? How are you planning to deploy the SharePoint topology – Geographic topology or Centralized Farm? Are your users will be connected via LAN or WAN to access SharePoint environment?
    • Do you have contact number for your Microsoft representative for licensing discussion?

• Security
  • Are all your users access the SharePoint 2010 content using their active directory accounts or via multiple authentication systems?
  • Are you planning to have least-privileged SharePoint 2010 installation and configured environment?
  • What are your server anti-virus standards for the Windows Server infrastructure? E.g. MacAfee
  • What are your SharePoint Antivirus needs for SharePoint environment to scan the uploaded documents? E.g. Forefront Antivirus for SharePoint
  • What is your server security updates and patches schedule? Do you install only critical updates? Do you allow automatic updates? Do you test or evaluate the windows update before patching up the production servers?
  • What are your standards for the SharePoint Service Accounts? What are your domain accounts AD password policies? Do you need service accounts configured with automatic password?

• Monitoring
  • What are your plans to monitor the SharePoint and SQL Server environment? If yes, what technology you prefer?
  • Does your organization use the SCOM and System Center? Are you planning to use SQL and SharePoint Management Pack?

• Integration
  • Do you have any incoming or outgoing email configured? Can you please provide us the SMTP server and Exchange email-relay configuration?
  • Is your SharePoint environment is integrated with any other Microsoft or Non-Microsoft technologies
    • Microsoft Dynamics CRM
    • SQL Server Reporting Services
    • Lotus Notes
    • SAP
    • OCS or LYNC

All you need to know about the Access Services 2010

Access Services 2010 is the new feature to host the entire Access databases within SharePoint 2010. It is only available in the SharePoint 2010 Enterprise Edition. This article not only covers the New Features in the Access 2010 and overview of the Access Services but also covers the benefits (both for business and IT), limitations, architectural details, typical Access Services Application Lifecycle (development, deployment, management process), and migration considerations for the Access 2007 (or earlier) databases to the Access Services 2010.

Access Services 2010 is the game changer in the Access 2010 space and may open new avenue for the departmental no-code database applications.  Hold tight and enjoy the reading.

Improvements in Access 2010

• Integration of the Microsoft Office Backstage as part of the Microsoft Office Fluent User Interface
• Web Database Format, Support for the Web Compatibility Checker, and Publish to Access Services
• New Controls – Web Browser Control, Navigation Control ( Replaces the Switchboard View from previous version to navigate the Access objects)
• New Data Type – Calculated Column Based on Expressions, much better improvements over the calculated values based on the queries
• Support for the Office Themes for the Professional look and feel
• Application Parts – Reusable forms, reports, or any other Access objects.
• Data Type Parts – Custom Grouped Field Types, create your own group data type with the collection of fields to reuse in other tables.
• Form and Report Designer – Live Data Preview , Improved Conditional Formatting on Reports & Forms (Conditional Formatting was added in the Access 2007)
• Expression and Query Builder Intellisense
• BDC Integration
• Macros
  • Revamped Macro Designer
  • Form Macros/ UI Macros – Introduced in the Access 2007, Declarative Actions along with VBA Code for the UI logic, Additional Declarative Actions for the interactive forms programming in 2010
  • Data Macros – Works similar as SQL Server Triggers, Reusable Macro logic to create events at the table level (form level UI Macros are not reusable), Supports Before (Before Change, Before Delete) and After (After Insert, After Update, After Delete) events
  • Named Macros – Data level macros not attached to any events and can be called from the UI
  • Macros are XML based and easily portable from one environment to another
• Ability to export to PDF and XPS files as a built-in feature (It was released in Access 2007 SP2) – Allows users to export forms, reports and datasheets to PDF and XML formats for easy distribution
• Ability to connect to a web service as an external data source – Linked table to the Web Service Interface
• Save as Template for creating access database templates, Download the Sample Templates from the Microsoft – http://office.microsoft.com/en-us/templates/CT010214400.aspx
• Last but not least, Access 2010 (even 64-bit edition) has a limit of 2GB size.

  SharePoint Access Services 2010 – Benefits, Limitations, and Architectural Details

• Access Services are nothing but Data driven web applications called web databases hosted in the SharePoint 2010 environment
• Access Services Benefits and Usage
  • Balance between business agility and IT manageability
  • No Install Solution -  Web based Access
  • Improved Collaboration – Share and collaborate on the declarative RAD no-code web based team databases.
  • Centralized Data Storage – Single truth of the Application Logic and Data
  • Improved Reliability, Scalability, Security, and Manageability – Central IT management
  • Improved Backup/Restore – Access databases are part of the SharePoint Backup and Restore Process
  • Increased Concurrency – Locks the database at the object level, instead of database file level resulting in fewer conflicts
  • Access Applications Standardization using Templates – IT can configure/support services, start building out the web database standards, and let end-users manage it
  • RAD No-Code SharePoint Applications – Useful to build web based powerful RAD applications which supports query engine that can perform complex joins, filtering, aggregations, and parent-child relationships between lists.
  • RAD Reporting Tool – Useful as reporting tool to generate the customized reports (RDL files) based on SharePoint Lists hosted in the SharePoint.

• Architecture Details
  • Access Service is a middle-tier service, which handles the  query processor and data access layer. It also manages communication between Access Web Application and SharePoint Content Databases.
  • Support for the Relational Database Data Integrity using SharePoint Lists Enhancements Infrastructure - Lists Relationships, Cascade Deletes, Unique Constraints, and Data level validations at the List and Item Level
  • Improved Performance/Scalability – Offers caching layer that addresses the limitations of the maximum number of list items that a query can return at one time by ignoring the List View Threshold. Improved Performance by allowing large record sets in the SharePoint List. Access DB supports more than 100K records. SharePoint Lists has filtering or sorting restrictions if you have more than 5K records in the list because content database blocks large calls. Access Services Data Sheets retrieves all the 100K SharePoint list items in the 2K records in chunk and later stitches them together in the ADO.NET record set in memory Layer as cached data for the performance. This allows sorting and filtering against cache instead of content database on the more than 50K records in the Access datasheet. Access datasheet View is rendered by the Project Datagrid object and it is smart enough to bring only 200 records at a time in the browser. With the smart navigation using AJAX experience, as you scroll down, it will bring additional records from the cache without affecting user experience. Access List Views are continuous forms supports paging, sorting, and filtering, and behaves same way as Datasheet View by retrieving data from the ADO.NET cache in the middle tier.
  • Concurrency Conflicts – Different users can make changes to different objects or different data items in a list without causing conflicts. When data conflicts do occur, a conflict resolution wizard enables the user to choose which version of data items to preserve. For object conflicts, Access provides the name of the user who made the saved changes and creates a renamed backup copy of the local object before downloading the other user’s changes.
  • Source Control – While downloading the database locally on file system,  Access client downloads the entire database only when a user doesn’t have local copy. Access fetches only objects or data items that have changed.
  • Only Web databases are supported in the Access Services. Web database supports two types of Access Objects – Web Objects that can run either in browser or Access Client and client objects (non-web objects) that can only run in the Access client. All design changes for both web and client objects must be made in Access Client. It is important to remember that client object definitions are published and stored in the SharePoint but they can accessed and executed during runtime only in the Access client.
    • All linked tables are client tables. Only client objects like reports, forms, macros can work with linked tables. Linked tables aren’t available to the web objects.
    • All the reports ,forms, and macros with VBA code makes these objects client objects.
    • All the web objects like reports, forms, and macros would work only with the web tables (Note web tables are Access DB tables fully compatible with SharePoint lists)
    • When working in the Access client and connected to the network where Access Service App is running, data and design changes to web tables automatically synchronize with the server.  When disconnected, Access client works with local copy and doesn’t allow changes to the tables. When reconnected, Access notifies users to synchronize and resolve any conflicts. Design changes to objects other than web tables synchronize only when users explicitly request sync by clicking Sync All button.
  • Forms and Reports gets rendered in the Data Form Web Part
  • Data Sheets rendered in the Project Datagrid JS Object to support large data sets
  • Reports are rendered in the Report Viewer Web Part installed with the SSRS Add-in
  • Access Service AJAX WS provides AJAX experience on the Access Web Applications
  • Better Administrative Control – Uses the OOB SharePoint Security for the Security Layer 
  • Managing and Fine Tuning the Access Services – From the Central Admin -> Manage Access Services Settings e.g. Max columns/rows per query, Max sources per query, Max calculated columns per query, Max order clauses per query, Max records per table in the join, Max sessions per users etc.

• Limitations
  • Advanced SharePoint functionalities like Site Content Type, Metadata/Taxonomy, BCS are not supported in the Access Services 2010
  • Advanced Access functionalities like Linked Objects (Linked Tables, Linked SharePoint Lists, or BCS Links), VBA/Modules, Action Queries, Traditional Full UI Macros are not supported in the Access Services 2010
  • SharePoint Designer can’t open the Access Services Site. Any modifications in the Access Services Site requires Access 2010 client.
  • Access Services Application adopts look and feel from the Access Client. It  can’t be configured to inherit the master page branding or color scheme from the parent site collection/web application.
  • There is no site actions menu from the Access Services Web Application.

  Prepare the Environment to host the Access Services 2010

• Access Services is installed part of the SharePoint 2010 Enterprise CAL
• Make sure Service Infrastructure is enabled
  • Manage Services on Server – Access Database Service is started
  • Manage Services Applications – Review the Settings, No need to change any performance tuning settings
  • Manage Web Applications – Make sure application is associated with the Access Servic
• Make sure all the features are activated
  • Central Admin -> System Settings -> Manage Farm Features -> Access Services Farm Feature
  • Central Admin -> Manage Web Applications -> Select Web Application hosting the Access Services Site -> Manage Web Application Features -> SharePoint Server Enterprise Web application features
  • Site Collection -> Site Settings -> SharePoint Server Enterprise Site Collection features
  • Site Collection -> Site Settings -> SharePoint Server Enterprise Site features
• To the Access Reports on the web, Access Services requires
  • SSRS Add-in for SQL Server 2008 R2 (It doesn’t require SQL 2008 R2)
  • Enable Session State Service, Name the DB = “SessionStateDB” – http://geekdeck.com/sharepoint-2010-this-report-failed-to-load-because-session-state-is-not-turned-on/

  Typical Access Services Application Lifecycle (Development, Deployment, and Management Process)

• Development
  • Create Access Database using Access 2010 and Start with “Blank Web Database” or OOB/Custom Web Database Templates
  • Design Tables, Queries, Forms, Queries, Reports, Macros/Business Logic, Navigation, and Style/Theme

• Deployment from the Microsoft Access Client using the Access Services “Publishing” Model
  • From the Backstage -> Publish to Access Services.  If there are errors during the Publishing Process, “Web Compatibility Issues” table will be created in the Access database for further review.
  • Publishing process creates a brand new SharePoint Site at the specified Path. Access Services Web Site is an isolated environment to host the Access database information.
  • Transformation Process to SharePoint (Client Objects  -> Web Objects, full fidelity between client and web objects)
    • ACCDB -> SharePoint Site
    • Access Tables and Data -> SharePoint Lists
    • Queries -> CAML Entries in the System Tables Rendered through Query Processor
    • Access Forms -> ASPX Pages using the Data Form Web Parts stored in SharePoint Document Libraries
    • UI Macros -> JavaScript Attached to SharePoint ASPX Pages
    • Popup Forms -> Floating Divs
    • Design Themes -> CSS Style Sheets
    • Access Reports -> RDL files (Requires SSRS 2008 R2 Add-in for the SharePoint 2010)
    • Data Macros -> SharePoint Workflow Actions
    • Modules, VBA Code Library -> It stored in the Access Services but not accessible/supported through browser interface. You can access them through the Access 2010 client.
    • Linked Tables, Linked SharePoint Lists, or BCS Links -> Linked Table Definitions or BCS Links Definitions are stored in the Access Services but not accessible/supported through browser interface. You can access them through client. Client objects in the Access Services will access to the linked table definitions but web objects in the Access Services can’t access the linked table definitions. Until Data in the SharePoint Lists (Natively as tables in the Microsoft Access), Web Objects in the Access Services can’t talk to data.
  • After the publish, what happens to the Accdb hosted on the server? Answer is during publishing process, Access 2010 Database Client stay as it is on the desktop or server in Accdb format. Access Services creates the backup copy of the Accdb on the server/desktop and makes the published Accdb as connected Access database to the Access Services Web Application. If you access the connected database after weeks or months from the desktop or server, Access DB client will sync/update the database with Access Services Site.

• Deployment from the SharePoint without Access Client
  • Save the Access Database as a Template from the Backstage in Accdt format. 
  • Browse the SharePoint Site Collection’s Solutions Gallery and upload/Activate Accdt file in the SharePoint solution gallery, SharePoint is smart enough to treat it as a solution package file.
  • Visit the New Site Creation Section and Create a new Access Services site based on the custom Accdt web template database.
  • Use this approach if the person creating the new site based off the Access template need not have Microsoft Access installed on their machine or if you need to standardize the Access Services Web Database templates in your organization to create more than one sites based on same template.

• Management  of Access Applications – To Modify Access DB After its been published using the Access Services “Sync” Model
  • Best Option: From the Options Menu, Click on the “Open In Access” to download the “Connected Microsoft Access” database for further modification
  • Any data changes on the client syncs to the web in real time. Any client changes of  the Table Schemas, Forms, Macros, Queries, reports doesn’t sync to web automatically. You have to use “Sync All” from the backstage to sync interface changes.
  • Individual objects in the Access Database on client can be modified independently and resynced to the SharePoint without affecting other objects. This allows better shared design and concurrency while updating the Access object design.
  • Please note that Access Services runtime environment is both browser and client but design environment is only Microsoft Access client. If you need to make changes in the reports or forms or schema, download the access db on the client, make the changes, and sync back to the SharePoint.

  Typical ways to integrate the Access database with the SharePoint 2010

• MOSS 2007 supported sharing the Access DB on the SharePoint by uploading Access DB on the SharePoint Document Library just as any other documents and required Access 2007 on the user’s machine.
• Three major options available
  • Good Solution – Basic Integration in the SharePoint Foundation 2010 (without Access Services 2010) 
    • Requires Access 2010 Client software must exists on the end-users machine.
    • Requires Access Database data must be compatible with the SharePoint.
    • Data and UI is centrally stored in the SharePoint and there is only one version of truth. Data is stored in the SharePoint Lists and UI is stored in the Document Library.
    • From the Access Client DB, visit the Database Tools Tab and Export the Access Tables to the SharePoint Site as a Lists using “SharePoint” ribbon button and Upload the Access DB to the SharePoint Document Library. 
    • Lock is per database. When multiple users are making design changes simultaneously, last person who upload the changes back to the SharePoint overwrite the other user changes. Make sure versioning and check out is required to avoid concurrent changes while making design changes.
    • Using this option, if you open the database read-only from the SharePoint, you can only change data in the linked tables from the Access Client. To make design changes, download the copy of the database to the hard drive, make changes, and upload it back to the document library replacing the previous version of the database. This option doesn’t provide the web UI on the SharePoint to view the data.
  • Better Solution – Supported as Access 2010 Services, Requires SharePoint Server 2010 Enterprise CAL
    • Doesn’t Require Access 2010 Client software exists on the end-users machine.
    • Requires Access Data and UI must be compatible with the SharePoint
    • Data and UI is centrally stored and there is only one version of truth. Access Database is published to SharePoint and hosted in the SharePoint as Access Services Web Application.
    • Lock is per object, as opposed to per whole database file. Using this option, while one user can make report changes, another user can create or modify the new form, and both changes can be synced back to the Access Services.
    • This will migrate the data as SharePoint lists and UI components as a client and web objects in the Access Services. Most of the Access Objects like web forms, reports will be transalated as web objects and can be accessed through the web UI. Many of the other objects like VBA code will be hosted in the Access Services as a client objects and will require Access Client software to view them.
    • Using this option, to make design or schema changes, save copy of the database to the hard drive.
  • Hybrid Solution
    • This scenario will cover most of all the Incompatible Access DBs in your organizations especially database applications created/maintained using the Access 2007 and prior versions.
    • What if you don’t have data compatible with the SharePoint? Can you share the Access database with SharePoint with above two solutions (good and better solutions)? Answer is No, Access DB must contain the SharePoint Compatible Data. Data must be compatible with SharePoint Lists. If any of the data can’t be moved to list, publishing can’t happen unless data is exported to separate database or changed to be compatible.
    • Instead use hybrid step by using the Linked Tables in the Access Services
      • Move any of the Incompatible Access Data to the SQL Server or another access db (from the database tools tab -> SQL Server or Access). This will move all the incompatible data to the access or SQL DB and keep the incompatible UI in the existing Access DB.
      • Next step is publishing the Access database with compatible data components and UI components to the Access Web Services. Client objects in the Access Web Services will reference to the external SQL Server for data and client objects can remain in the database without interfering with publishing process.
      • Later on, gradually, fix the incompatible data. Migrate the external data to the SharePoint lists and generate the web objects in the Access Web Services for full migration. To migrate the external data in the SharePoint lists, import the previously incompatible external SQL server or access db tables into the Access DB tables with valid UI and fix all the browser compatibility issues and republish it to the Access Web Services
• Access Services feature comparisons supported in the SharePoint Foundation and SharePoint Enterprise CAL
  • Data in SharePoint Lists – SharePoint (included in the SharePoint Foundation)
  • Centrally Deployed Interface – SharePoint (included in the SharePoint Foundation)
  • Collaborative Design – Access Services only (Requires Enterprise CAL)
  • Web Forms in the Browser – Access Services only (Requires Enterprise CAL)
  • Web Reports in the Browser – Access Services only (Requires Enterprise CAL)
  • Server Macros – Access Services only (Requires Enterprise CAL)
• Going forward, future direction should be Access 2010 and Access Services 2010
  • Start all the client Microsoft Access databases with the web database template to make sure its always compatible with SharePoint and Access Services.
• How can you lock down the Access Services Publish Locations?
  • Client Level – Lockdown the registry key – HKCU\Software\Policies\Microsoft\Office\14.0\Access\Security\Allowed Publish Locations
  • Server Level – SharePoint security, define permission levels, Design, Contribute and Read only, Designers and above has permission to create the access web applications, contributors can read and write data, Users with read-only permission can only read data.

  Access 2007 to Access 2010 Migration Considerations for the Access Services 2010

• Access 2010 will share the same native file format as Access 2007 – ACCDB format.
• For Pre-Access 2007 version, follow the guide – “Transitioning Your Existing Access Applications to Access 2007″ to upgrade from MDB to the ACCDB version – http://msdn.microsoft.com/en-us/library/bb203849(office.12).aspx
• Access 2007 Deprecated Features
  • Microsoft Calendar control (mscal.ocx) – Use Date Picker control as an alternative
  • Microsoft Replication Conflict Viewer
  • Option to export a report as a Snapshot file
  • Data Access Pages (DAPs
• Rationalization Process – Analyze Access Databases in the Organization
  • Use the OMPM tool – Office Migration Planning Manager – http://technet.microsoft.com/en-us/library/ff453909.aspx
  • OMPM 2007 – http://www.microsoft.com/downloads/details.aspx?FamilyID=13580cd7-a8bc-40ef-8281-dd2c325a5a81&displaylang=en
  • OMPM 2010 – http://www.microsoft.com/downloads/details.aspx?familyid=81D208F0-7ACE-499C-8975-728C648B8B43&displaylang=en
  • OMPM tool analyzes the Access databases and generates in-depth reports. This tool doesn’t analyze the database web compatibility for the Access Services.
  • OMPM is a Geeky tool – Runs by INI file and command line interface.        
  • Scanning Tool
    • Scan across network share or hard drive. Alternatively, you can deploy this tool as SMS to scan all the user’s desktops in your organization
    • This tool will generate the cab file/log files about the ACCDB details
  • Analysis Tool
    • Import the scanned data into Access Db for detailed analysis
    • Reports on Database and Issues, Active vs. Old DBs, DBs participated in replication etc.
• Migration Process – Convert Access 2007 Databases in the Web Database Format
  • Look at Third-Party Web Conversion Service Offering:  http://www.access2010converter.com/index.html
  • Open the Access 2007 or Previous Versions of DB in the Access 2010 client
  • Fix the Access 2007 database objects web compatibility issues – Run the Web Compatibility Checker on the Access database to conforms the web compatible schema to meet the SharePoint requirements. This will create the “Web Compatibility Issues” table in the Access Database. The errors in table are explanatory with which table, which control you have and there is also a link for each error where you can also get info about what to do.
    • Analyze the Web Compatibility Issues Table , Fix the compatibility issues, and rerun the Web Compatibility Checker until Access database is web  compatible with Access Services and ready to be run on SharePoint.
      • Common Issues – Formatting, Data type, Validation Rules, and Structural Issues
      • Invalid Names -Tables and Columns Name should not conflict with reserved words or illegal characters. Changing the column names would not change all the Access objects. The Access Name AutoCorrect feature will propagate the changes to the queries and bound controls as long as objects are open at least once and saved. Please note that VBA Code and expressions are not automatically updated.
      • Compound Indexes – Indexes based on multiple columns are not supported over the web. One workaround for enforcing uniqueness on multiple columns are use the BeforeChange data macro.
      • Declarative Referential Integrity – Referential Integrity configured using the Relationships window that are not associated with a Web-compatible lookup are incompatible with the Web. You have to delete all relationships not based on lookups and create them using lookup wizard. Tables with relationships that aren’t implemented in lookup can’t be published. These lookups must be based on numeric data types.
      • Composite Keys – Composite keys are not supported in the Access Web Databases. If you have a database with composite indexes, you have to create a new primary index with AutoNumber type and create a data macro to preserve the uniqueness of your fields making up the composite key. Here is the article to write a data macros to resolve these issues – http://blogs.msdn.com/b/access/archive/2010/02/18/composite-keys-in-web-databases-through-data-macros.aspx
      • Primary and Foreign Text Keys -  Access supports primary and foreign keys on the text or dates.  SharePoint Supports relationship on numeric keys, not on the text based keys. Web database Primary key must be long integer. Easiest way to fix this add an auto number column to the parent table and add a corresponding long integer column in the child table as foreign key. You can then use the update query to sync the foreign key values in the child table. You would also need to delete the old relationship and create a new one using the lookup wizard. Make sure all other objects like queries, reports, forms are updated to use the new autonumber column before deleting the old text keys and relationships. You can also use data macros discussed in the Composite Keys section.
      • OLE data types needs converted into attachment data types. Update all the forms and reports where OLE data types were used for the pictures. Also, SharePoint supports only one attachment column per list so, make sure you have only attachment field per table in Access.
      • Tables with the recursive relationships to manage parents and child data in same table are not supported in SharePoint.
    • Please note that web compatibility checker doesn’t check all the database issues. Many of these issues may cause publishing fail. Any errors during the publishing process, Access logs the issues in the “Move to SharePoint Site Issues” table.
      • Incompatible data (e.g. invalid date, hyperlinks etc.) may cause publishing process fail.   Web compatibility checker checks data schema but  doesn’t check data values. Some of the Number/Currency/Date Time formats are not supported in Access Services and here is the guidelines around the supported format.
        • If you have a Number field, make sure it is formatted as General Number/Standard/Percent.
        • If you have a Currency field, make sure it is formatted as Currency/Euro.
        • If you have a Date/Time field, make sure it is formatted as General Date/Short Date. 
        • If you have hyperlink field, make sure it as fully qualified URL (relative URL doesn’t work) and URL length is less than 255 characters.
      • VBA code – It would require migration strategy to rewrite VBA code in the Data and UI macros. Please note that VBA code doesn’t cause publishing fail and require Access client to access them from the browser.
      • Invalid Expressions – Invalid Expressions entered manually without Expression Builder may cause publishing process fail. Invalid expressions in data schema definitions (e.g. validation rules or calculated column) will cause the publishing errors but Invalid Expressions in the web forms, reports, and queries can be found out only during runtime after publishing has been succeeded.  Access Services logs the compilation issues in the USysApplicationLog table.
  • Once Access 2007 objects are converted to the Access 2010 web legal format, Access 2007 (now converted to the Access 2010 web legal format) database is ready to publish to the Access Services. However, you can’t open your objects (Forms/Reports/Reports/Modules) other than tables in the browser if they are using the VBA code since they are still client objects. These client objects will reside in Access Services and Access s clients can open the forms and reports but they will not render in the browser.
  • For full web compatibility, plan for new web objects like navigation (tabbed interface), form and reports for the web interface with the new themes and look and feel, etc. You have to create new web objects if you want to open forms and reports in the SharePoint. You have to set the Web Display Form which will be displayed as splash page when you open your access application in the browser. To set your Web Display form go to the Backstage | Options | Current Database page and select a form from the Web Display Form dropdown menu.  Typically, it should be the main navigation form.

 Access Services 2010 Resources

• Access Team Blog – http://blogs.msdn.com/b/access/
• Access 2010 Web Conversion Service Offering – http://www.access2010converter.com/index.html
• TechNet – Access Services Planning  - http://technet.microsoft.com/en-us/library/ee683869.aspx
• TechNet – Access Services Administration – http://technet.microsoft.com/en-us/library/ee692394.aspx
• TechNet – Access Services Whitepaper – http://technet.microsoft.com/en-us/library/ff397963.aspx
• Access 2010 New Features Demo – http://blogs.msdn.com/b/access/archive/2010/06/09/access-2010-new-features-demo.aspx
• Access 2010 Web compatibility checker – http://blogs.msdn.com/b/access/archive/2009/12/21/access-2010-web-compatibly-checker.aspx
• Tips to migrate legacy databases to SharePoint and the Web – http://blogs.msdn.com/b/access/archive/2010/02/20/tips-to-migrate-legacy-databases-to-sharepoint-and-the-web.aspx
• Access 2010 Create Ribbon for Client and Web Objects – http://blogs.msdn.com/b/access/archive/2009/11/17/access-2010-create-ribbon.aspx
• Access 2010 Navigation UI – http://blogs.msdn.com/b/access/archive/2009/11/14/build-navigation-ui-with-access-2010.aspx
• Data Platform Improvements in SharePoint 2010 – http://blogs.msdn.com/b/access/archive/2010/02/15/data-platform-improvements-in-sharepoint-2010.aspx
• Two Parts Series of Access 2010 Publishing to SharePoint
  • Part 1 -  http://blogs.msdn.com/b/access/archive/2009/11/23/publish-to-sharepoint-part-1.aspx
  • Part 2 -  http://blogs.msdn.com/b/access/archive/2009/12/01/publish-to-sharepoint-part-2.aspx
• Access 2010 Themes – http://blogs.msdn.com/b/access/archive/2009/09/23/access-2010-themes.aspx
• Access 2010 Custom Themes – http://blogs.msdn.com/b/access/archive/2009/09/25/access-2010-custom-themes.aspx
• Access 2010  Application Parts and Data Parts: http://blogs.msdn.com/b/access/archive/2010/03/31/access-2010-application-parts-and-quick-start-fields.aspx
• Access 2010 Macro Designer – http://blogs.msdn.com/b/access/archive/2009/07/28/meet-the-access-2010-macro-designer.aspx
• Access 2010 Data Macros – http://blogs.msdn.com/b/access/archive/2009/08/13/access-2010-data-macros-similar-to-triggers.aspx
• Access 2010 Expression Builder and Intellisense – http://blogs.msdn.com/b/access/archive/2009/08/11/write-expressions-faster-with-less-errors.aspx
• Access 2010 Improved Layouts – http://blogs.msdn.com/b/access/archive/2009/10/01/access-2010-improved-layouts.aspx
• Use OMPM to Prepare for Migration to Access 2007 – http://blogs.msdn.com/b/access/archive/2009/06/09/use-ompm-to-prepare-for-migration-to-access-2007.aspx
• Access 2010 and SQL Azure – http://blogs.msdn.com/b/access/archive/2010/06/07/access-2010-and-sql-azure.aspx
• Northwind Web Database Template – http://blogs.msdn.com/b/access/archive/2010/07/19/northwind-2010-web-database-is-now-available.aspx
• Access Services support for the OData Protocol – http://blogs.msdn.com/b/access/archive/2010/07/20/get-to-access-services-tables-with-odata.aspx
• Web Database Conversion Service – http://blogs.msdn.com/b/access/archive/2010/07/26/web-database-conversion-service.aspx

Reality Check – Thoughts on the SharePoint 2010 List Relationships

One of the highlights of the SharePoint 2009 Conference was the new list relationships improvements and many of us excited to build the future systems based on the SharePoint List’s many to many relationship improvements.  As many of us aware, there are two kinds of list relationships – parent/child or master/detail relationship (1-n relationship in database terms) and many to many relationships. In SharePoint 2007, there was a lookup column where you can define the columns based on another list. It enabled the linking one list to another and end-users can easily build the parent-child related lists. If you would like to bring another column from the parent list to the child list, it wasn’t possible through out of the box features. 

In SharePoint 2010, Microsoft improved list capabilities which would not only bring the additional columns from the another list based on the lookup columns but also enforce the relationships. To create the related lists, you have to use the lookup columns to form the relationship between lists. 

• Projected Fields or Secondary Columns - Users should be able to reference the additional fields from the parent list to the child list to provide the read-only view of the data from the another list.
• Relationship Behavior and Data Integrity – There are three possibilities for the list relationship integrity and deleting parent item would have different behavior based on different options. Please remember that all three options would allow deleting child list items without affecting parent list items.
  • Restricted Delete with enforcing relationship behavior – This option would prevent the end user deleting the parent item if it’s used in the child lists by enforcing the data integrity. Lookup column enforcing the relationship would also create the index on the column. If user tries to delete the parent item referenced in the child lists, they will get the message – “This item cannot be deleted because an item in the “Child” list is related to an item in the “Parent” list.”. This option prevents creating the orphaned records.
  • Restricted Delete without enforcing relationship behavior – This option would allow deleting the parent item without enforcing the data integrity but clears the references of the parent item in the child item. Child list item will contain “Blank” value for the each deleted parent referenced column. This option will create the orphan records in the child list and you need mechanism like SharePoint workflows to clean up the orphan records.
  • Cascade Delete (You have to enforce the relationship behavior) – This would allow deleting not only parent item but all the child items referencing the parent item.  Lookup column enforcing the relationship would also create the index on the column.
• Optionally you can make the fields required and enforce the uniqueness on the column. Please remember that Uniqueness is not the case-sensitive and will create the index on the column.
• You can also decide to allow single valued selection or multi-valued selection on the look up columns. Single-Valued Lookup columns are supported as a unique column and Multi-valued lookup columns are not supported as a unique column.

  

One thing potential SharePoint implementers needs to keep in mind that building SharePoint List relationships with lookup columns isn’t same as the true SQL-like many to many relationships. SharePoint doesn’t enforce having an intermediate list to hold the many too many relationship data.  As a SharePoint Architect, you may able to skip the intermediate SharePoint list to build the one-to-many relationship or design the intermediate SharePoint list like intermediate SQL table and enforce the relationships to build the many-to-many relationships. Things will get tricky when you try to crate the data entry pages or forms to maintain these relationships (this may be a great reason to build the custom ASP.NET applications instead of customizing the SharePoint). You may able to use the SharePoint Designer Linked Data Sources or Related Item View (new feature to create the master-detail views) to create the mashups or programmatically write a code using Linq-to-SharePoint to implement the renormalized views to retrieve the data by joining the multiple lists. 

There are handfuls of blog entries discussing newly improved SharePoint 2010 list capabilities and near parity with SQL Server based database relationships. 

http://www.zimmergren.net/archive/2010/01/05/sp-2010-how-to-relational-lists-in-sharepoint-2010.aspx
http://sharepoint.microsoft.com/blogs/GetThePoint/Lists/Posts/Post.aspx?List=8d9e2a99%2Df288%2D47c2%2D916b%2D2f32864f7b82&ID=316

Hope this blog would clarify many details around the SharePoint 2010 List relationships.

Nik