Collabshow.com

When you dig into SharePoint 2010 Upgrade you’ll find out about this new feature called “Read-Only Content databases.”  This feature actually came in SharePoint 2007 SP2.  The feature is something you should be aware of for scenarios not only relating to upgrade, while upgrade is the most common scenario.

We use to set our SQL databases read only when doing database attach between 2003 to 2007 anyway, so what has really changed?

There are a few ways to set your SharePoint content read only.

1. Read or Read/Write Lock your site collection from STSADM

2. Read or Read/Write Lock your site from Central Admin UI

3. Configure the SQL Content database to Read only

Configuring SQL to set the database Read-Only:

From SQL Management Studio, you right click on the content database you want to set read only choose properties, then click options in the tree menu, then scroll down and in the state box change read-only to true.  Seems easy enough…

You’ll then see it grayed out in your UI

If you were to configure a SharePoint Content database in SQL to read only in 2007 before SP2 you’d find that you’d get ugly errors that would say contact your administrator if people would upload a document or try to edit a list.  Imagine someone using a form and spending a long time working on a blog or something complex.  They could be pretty upset and have no idea what’s going on.

Now post SharePoint 2007 SP2 including WSS 3.0 SP2 you’ll find that when you set your databases to read only you now get three things.  

1. SharePoint will promote the SQL read only lock to a read lock for the Site Collection. 

2. You get a both have a simplified UI to minimize the write options and to minimize confusion.

Before Read-only

After Read-only

3. You’ll get friendly error messages that the Server is in Maintenance mode where the UI could not be UI trimmed as the content in your database remains static.

On the SharePoint 2010 side you’ll now actually see the status of the database as read only or not.  You can tell just by how much you see it in the UI that it will be much more common for people to use:

When would you use read only databases?

• When combined with Database attach for SharePoint 2010 Upgrade
• Using it on SharePoint 2010 Patching to upgrade the binaries only
• For SharePoint 2007 high availability migrations or consolidations
• Other Hybrid SharePoint 2010 Upgrade scenarios

Other Upgrade Considerations:

Note that PreUpgradeCheck will also check the status for read-only databases.  You should NOT use read only content databases with In Place upgrade. 

With a hybrid upgrade approach if you want to just upgrade the binaries and services you should simply detach the databases first.

In the alternate hybrid approach you would configure the databases as read only on your source, while the copy of the farm has read write databases while it’s being upgraded.

Also note, with database attach any database you attach should be read write while it’s being upgraded.  The original source which can be a copy could be configured as read-only.

Additional references:

There’s a good summary in “Run a farm that uses read only databases” on TechNet of what is trimmed in the UI and where you get access denied in the farm.  Also good recommendations on checking your ULS logs for failing timer jobs and how to disable timer jobs that are set to write to the content database that might be failing.

There’s a KB article: http://technet.microsoft.com/en-us/library/dd793608.aspx that goes into detail around the issues introduced with Read only content databases in  farms prior to SP2

For SPS 2003 I wrote a post titled What Not to do in SharePoint.  I updated that post for SharePoint 2007 titled What Not to Store in SharePoint. 

I think it’s time to visit this topic with SharePoint 2010.  It’s amazing both how much and yet how little things have changed in terms of what this list consisted of in SPS 2003 and now in SharePoint 2010.  I’ll call out what I think are the main differences to help you understand what has changed.

Start by researching: File name, length, size and character restrictions.  There are a lot of absolutes in that post that there’s really not much you could do without building an ISAPI filter which I don’t recommend doing.  This topic is also very relevant to various migrations including public folder migrations, notes, and file share / file server migrations.

Rather than rehash all my explanations of what I mean, you can read my previous posts on this topic to understand the background, rather, I’ll give you the updated thoughts.

Really Large Files

Guess what?  The 2GB limit persists.  I do think you’ll see many more pushing the limits way beyond the default 50MB with many more adopting the remote blob storage and yes based on document based on Microsoft R&D they suggest that those that store large files should be looking at the remote blob storage.  I do think people should still keep their limits at 2GB.

Files with Dependencies

The dependencies list has been reduced with the work in external lists.  I do think the relationship work in webparts and lists help a lot as well.  The multi user scenarios of Excel and the new work in Excel Services and Access and Visio Services in the application space take this a long way.  I still think people need to be smart about what excel spreadsheets they simply upload.  There are still some legacy SMB/CIFS calls that are made by Excel across workbooks that don’t work well with SharePoint when simply copied from a file server to SharePoint.  My encouragement… test.  While Access databases still shouldn’t just simply be uploaded, there are some great scenarios around publishing access tables as lists.  You just have to analyze what you’re doing.  There definitely IS new Scenarios in SharePoint 2010 and it’s more than simply uploading files, and more about rethinking the scenarios for how best to store the data in lists rather than in files or publishing the access, excel, etc… look at office web apps as the other new scenario.

Improvements: Service Applications: Access, Visio, Excel and new editing scenarios in Office Web Applications

Transactional Applications, Logging and files which require locks
While you still shouldn’t save your .pst to SharePoint, you definitely should consider syncing your contacts, tasks, and calendar.  I do think more in SharePoint 2010 will look at their lists as closer to relational, and tiny bit closer to transactional.  It still is NOT a transactional application and you still can’t wrap your changes.  Yes you have events and workflows, so you do have to think differently about using SharePoint as application storage.  Full CRUD operations against external lists. As objects SharePoint list scale takes this a long way.  I do think people will find using SharePoint for storing changes has come a long way.  Simply the scale of task lists and building applications on SharePoint has come a long way.

Using SharePoint lists as tables and building applications on those tables won’t give you primary keys and foreign keys, but you will find usefulness in the new external list functionality where you can do your work on the SQL side and bring SharePoint in as your interface.  This has come a long way.  I also think you’ll appreciate ECM enhancements like Document ID.

I would still suggest that people NOT store their logs or even be very careful about .NET applications that are highly transactional and highly relational in SharePoint. Consider something like IIS logs.  You wouldn’t store them on SharePoint because they are large, and it will be more difficult to work with them.  SharePoint as an interface, that’s another consideration.  If you’re going to have more than 10 million entries in the life of the application then it’s a no starter.  Better to put it in SQL and look at integrating SQL 2008 R2 and PowerPivot for SharePoint 2010.

Improvements: External lists, Scalable lists, Enhancements in Workflow, and document ID

Application Developer Resources and Source Control (i.e. files commonly generated by Visual Studio)
First I think the packaging built into saving SharePoint’s own applications as solutions is a big deal in SharePoint 2010.  You still shouldn’t store your your C# code in SharePoint.  I think that makes sense these days.  TFS itself has come a long way, especially in it’s SharePoint story.

I do think it’s great to see improvements in file groups.

Improvements: Better Visual Studio Packaging for SharePoint, File Groups

Application Distribution
I do more and more see SharePoint as a front end for product distribution, but those packages are difficult to manage in SharePoint not only due to blocked file types, but also in the management of the groups of files.  File groups should make it easier to bring things together like a folder to build a package, but the access would be strange for both loading and extracting from a file and run perspective.  I don’t like the idea of storage of a product like Office or IE the application itself.

Improvements: File groups

Server Side Scripts
Bad idea.  Still not a good idea to store random ASP or .NET files.  From a pure SharePoint perspective there is a new scenario you should be aware of.  Administrators can now allow client solutions to be uploaded.  These WSP web solution packages known as sandbox solutions can be performance limited and controlled.

Improvements: Sandbox Solutions for templates, the new client API, and new packaged changes produced by SharePoint designer

Backup
SharePoint is not designed to act as a backup (i.e. *.bak, *.iso, etc) resource.   Along with the large files, SharePoint while you will find it more common as a backup for collaboration data and even more and more files with SharePoint seen as an archiving system with remote blob storage and it’s usefulness as an ECM platform repository, it is still not a place to store large ISO files. 

The new scenarios are record repository, file repository, and as people are working with file formats that are compatible with SharePoint and leveraging common desktop applications that have been updated to work with the web, like pictures, desktop applications are becoming more compliant and understanding the way we work with the web.  So essentially SharePoint does not become a tape device, but will more and more be seen as a mechanism for archive.

Improvements: ECM and RM enhancements including remote blob storage in SQL 2008 increase it’s viability

Large Media
Media itself has taken a turn in SharePoint 2010.  With the silverlight webpart and support for streaming media straight from document libraries, SharePoint 2010 takes a leap forward in support for media.  If you were to analyze youtube.com you’d find that the maximum upload is 10 minutes or 100MB.  I think that’s totally acceptable for SharePoint 2010 and with the large list enhancements and RBS you’ll find SharePoint as digital asset management more and more common.  Note the 2GB limit mentioned above.  For feasibility I still think it’s a bit crazy to store more than a few hundred MB in SharePoint databases, but RBS enhancements SQL 2008 R2 with third party do make me consider exceptions of up to 1GB.

I still don’t think you should dump most Training CDs or Product or that kind of media to SharePoint.  Most are still designed to run from the file system.

Improvements: Streaming media support, silverlight webpart for media, better image and thumbnail support, better all around digital asset management including remote blob storage considerations with third party vendors with SQL 2008 R2 (I don’t recommend using the SharePoint 2007 SP2 API)

Blocked Files:  Careful with Non "Web Friendly" Characters and Long File and Long Site Names
More data on this topic at the post referenced in the first paragraph.  SharePoint sites may not include the following characters:

/ : * ? " < > | # { } % & <TAB>”.

Additionally, the following characters cannot be used in the naming of files to be uploaded to SharePoint:

" # % & * : < > ? { | } ~ .

SharePoint file names cannot exceed 128 characters in length.  Filename + folder structure + site structure + hostname should not exceed 256 characters.

Understanding the Evolution of SharePoint as a cloud based storage not traditional file system

While SharePoint isn’t a file system, there have been a couple of significant changes that should be noticed.  One Azure and the SharePoint Service, and the other the Office Web Applications.  With Word, Excel, PowerPoint and One Note as not only viewers, but editors in the SharePoint 2010 interface it is now even more easy to see SharePoint 2010 as a kind of file system, but sure you can’t run your desktop from it.  As those who design applications consider the cloud more and more applications will work better with SharePoint. Many would point to quotes from Ballmer in the past when asked if SharePoint was the future operating system in the cloud, and his flippant response.  It’s interesting to watch this evolution, but please don’t look at this as a pure replacement for file shares as it’s far from that.  The better you understand what works well and what doesn’t, you can make better decisions about how and when data should and shouldn’t be moved.

With Permission from SharePoint Expert Mike Herrity who writes the popular blog “SharePoint in Education.” I’m happy to pass along the news on their 3 year anniversary of the Twynham School SharePoint deployment.  Mike has been capturing screenshots, and documentation of their experience along the way.  Mike has been furiously documenting  in an E-book their SharePoint deployment experiences on their learning gateway now cover over 7 chapters.  They are happy to share their experience to help other deployments especially education based SharePoint deployments.

From Mike: “As a number of you will know over the last month I have been serialising parts of an E-book I have been writing. The E-book entitled Twynham School Learning Gateway 2007-10 is launched today, exactly three years from the first piece of work we did with SharePoint at Twynham School.”

Download or view the E-book SharePoint based “Twynham School Learning Gateway 2007-2010”

Mike recommends…“I hope you find it of some use and please do pass it on to others as one example of what schools are doing with a Learning Platform.”

Just found out my Interview on TechNet radio was featured on the home page of TechNet.  Pretty cool.  Here are the details of the interview by Chris Caldwell…

“In this episode of TechNet Radio, our host John Baker interviews Joel Oleson, Sr. Product Manager and SharePoint Evangelist at Quest Software. Listen in as they discuss what considerations you should make around large scale SQL Server deployments with SharePoint. They also take an in-depth look at SQL Server Enterprise edition, database sizing, backup methodology and SharePoint recovery.”

Total Duration: 18:32

[ 1:30 ] Does Enterprise edition really matter when doing large scale deployment? 
[ 5:20 ] Database sizing, what works?
[ 11:03 ] What is the best method for backing up data in SharePoint?

Downloads (18 minutes):

• iPod (MP4)
• MP3
• WMA
• WMV
• WMV (High)
• Zune
  

Videos:

• Considerations for Large-Scale SharePoint Server Deployments on SQL Server

Powershell commands that are useful during SharePoint 2010 Upgrade

Get-Command –noun sp* – this is the equivalent of the stsadm –help or doing a what feels like a command line dir for the list of commandlets.  When you start off, this seems like it should be easier.  If you simply stick with the SharePoint commandlets in the beginning, it will feel A LOT like running operations and parameters against STSADM.

Test-SpContentDatabase – Used to validate a database against a given web application.  This command can be used with any post SP2 2007 database or with any SharePoint 2010 database.  While not required the insight gained will essentially reveal the WHAT IF errors and warnings associated with upgrading the database while making NO changes to the database.  The database itself need to be part of any farm and simply needs to be online in SQL.  Also works for version to version and even incremental upgrades between patches.

You should always run test-spcontentdatabase on your 2010 farm before adding ANY content database to your farm despite the version.  Even if you can spin up a 2010 instance you should run this command even if you’re planning on using in-place upgrade.  This insight is invaluable and is NOT the same as what you’ll get out of stsadm –o preupgradecheck.

>Test-spcontentdatabase –name %Nameofdb% –webapplication %http://nameofwebapp%

Mount-SPContentDatabase – Used to attach a database or multiple parallel databases to the farm.  Essentially is nearly the same as the STSADM –o addcontentdatabase.  It does actually have more options when you start looking at parameters on the surface in each command.  This is where the power of powershell competes on a command level.  If you’re doing parallel content database attach for example, this powershell method is recommended.  You’d simply run each of these in different command/management windows.

Running mount-spcontentdatabase –? will provide the context for adding a content database into the farm.  Note you can run multiple of these at the same time by simply opening up multiple windows.

>Mount-SpcontentDatabase –name %nameofdb% –webapplication %nameofwebapp%

Upgrade Status in SharePoint 2010

Viewing Upgrade status in Central Admin provides much more insight into the upgrade process including how long it’s been running, number of errors & warnings, and even what step it’s on in the process.

Note scrolling down provides even more insight including the thread and process ids, path to the upgrade and error logs for this session.

Looking and the error logs

Looking at the screenshot above you can see a path to the log file.  This is also where you’ll find the upgrade-timestamp.log and error-timestamp.log

Opening up the Error log, it’s amazing how similar the errors are to the test-spcontentdatabase almost identical.

Upgrade-SPContentDatabase – Don’t be confused about the this command as doing upgrade, it is strictly for resuming.  This command is used to only to resume a failed Upgrade.  It can be used on in-place upgrade failures for failed databases or database attach upgrade failures.  In contrast the STSADM –o Upgrade command can resume binary and database upgrade, but more holistically.  I’ve found the STSADM to be the fail safe command as it will check both the binaries and all of the databases.  If you have problems with one particular database the upgrade-spcontentdatabase will obviously the best choice to target the resume of the upgrade.  It’s not just used for failures, but to restart the upgrade of a database where issues have been addressed.

One other option is the psconfig.exe –cmd upgrade –inplace v2v –passphrase < passphrase > –force this command should be used for troubleshooting in-place upgrades with early binary upgrade failures and even goes another level higher than stsadm.

Upgrading the SSP to Services Apps with Powershell

Upgrading the SSP is definitely the tricky part of db attach, while I do think most small and medium organizations will skip this in lieu of building out the services fresh, those with large complex search configuration will likely not want to loose their content sources at a minimum. 

Upgrade-SPEnterpriseSearchServiceApplication – Upgrade the Search Service Application Instance.  This commandlet it designed to upgrade the content sources and configuration of search and to upgrade it into the new service application.

Upgrade-SPSingleSignOnDatabase – Upgrade the SharePoint Single Sign on database to Secure Store Service Application.

Upgrade-SPProjectWebInstance – Upgrade Project Server databases.  Obviously this only applies with Project Server deployments, but I know there are definitely many out there.

Get more insight on these commands at TechNet or by simply typing the listed command followed by –? or –examples or –detailed or -full

If you really care about your SSP, In-Place Upgrade gives you more of your settings and config in SSP, Why not consider a hybrid approach…

While I’m really not a fan of in-place as a primary strategy to upgrade you farm, I do think many will choose to do database attach for the main portion of their farms.  Many will choose to use a hybrid approach to upgrade the services configuration.

SharePoint 2010 replaces the SSP concept with “service applications.” So, each SSP upgrades these service apps.  If you have many SSPs you’ll find a TON of databases as each of your SSPs will be exploded into these services.

• Search Service application
• User Profiles Service application
• Excel Service application
• App Registry (for backwards compatibility)
• Managed Metadata Service application

Much of what will be missed in the database attach upgrade is simply settings and configuration with the exception of search, audiences, and profiles. 

The biggest complaint I expect to hear is profiles.  Why is there no profile powershell script for upgrading the profiles?  There is obviously a lot of data in the profiles that is driven by the users that is not just settings.  I think this will ultimately drive a lot of people into the hybrid approach for getting the actual profile data.

Using Hybrid Upgrade Approaches

Imagine using db attach for all your content databases, then simply detaching them and running in-place upgrade on a farm with no content databases.  This will upgrade the SSP into those service applications above which can then be backed up with the 2010 built in backup tools and then restored into your 2010 farm.

See Hybrid approach #2 in the MS Upgrade Approach poster for Microsoft’s view of detaching databases to get your services and settings across by driving a post in-place upgrade of the services.  I do think there’s a lot of ways to do this.

There’s a lot more flexibility for thinking outside the box here….

For example, why not make a backup of your farm, then restore to a new farm.  Detach all the content databases run In-Place upgrade to upgrade farm services and settings.  Then now you’ve got a 2010 farm you can go with your scheduled database attach upgrade.

Hybrid approach #1 (read only databases) really speaks to something that everyone should be doing with every db attach which is simply setting the databases to read only even if you use #2 it doesn’t keep you from running with #1 (read only databases).

Want to talk more about this?

I’ll be including this walk through demo with more discussion on hybrid approaches and SSP considerations and more in detail in my 400 Level Upgrade Drill Down session at the Experts Conference in LA April 25-28.  If you haven’t registered, be sure to use the Discount Code: ATGNVET

See you there.

For more Resources and insight on 2010 Upgrade see my SharePoint 2010 Upgrade Insight series.