SharePoint 2010: SharePoint Server Databases

 

 

The following is a list of databases installed in a “Basic” install of SharePoint Server 2010.

 

Notes:

• SQL Server Express 2008 Service Pack 1 was installed
  

     Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
         Mar 29 2009 10:11:52
         Copyright (c) 1988-2008 Microsoft Corporation
         Express Edition (64-bit) on
          Windows NT 6.1 <X64> (Build 7600: ) (VM)

• If you want to install the SQL Express Management Tools:
  • remember to download the 64 bit version
  • check out this article or you won’t guess the correct choice (New Installation, not Add new features) http://www.asql.biz/Articoli/SQLX08/Art3_1.aspx
  • the SQL Profiler is not part of the Express tools
    
• A new instance was created named SHAREPOINT  (yourservername.SHAREPOINT). The instance was created in
  C:\Program Files\Microsoft SQL Server\MSSQL10.SHAREPOINT
  
• The databases were created in:
  C:\Program Files\Microsoft Office Servers\14.0\Data\MSSQL10.SHAREPOINT\MSSQL\DATA
  (Foundation defaults to here: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Data\MSSQL10.SHAREPOINT\MSSQL\DATA)
  

Database Names:

• The “system” databases are named with GUIDs. We used to avoid this, or rename these, but it’s probably best to now leave these alone.
  
• In SharePoint Foundation 2010 these include:
• Bdc_Service_DB_guid   -  Business Connectivity Services (BCS)  (the old BDC)
• SharePoint_AdminContent_guid  - Central Administration
• SharePoint_Config_guid – the SharePoint Configuration database
  
• In SharePoint Server 2010 they add:
• Application_Registry_Service_DB_guid –
• Managed Metadata Service_guid –
• PerformancePoint Service Application_guid –
• Search_Service_Application_CrawlStoreDB_guid –
• Search_Service_Application_DB_guid –
• Search_Service_Application_PropertyStoreDB_guid –
• Secure_Store_Service_DB_guid –
• StateService_guid –
• User Profile Service Application_ProfileDB_guid –
• User Profile Service Application_SocialDB_guid –
• User Profile Service Application_SyncDB_guid –
• WebAnalyticsServiceApplication_ReportingDB_guid –
• WebAnalyticsServiceApplication_StagingDB_guid –
• Word Automation Services_guid –
  
• The content databases follow the 2007 pattern: WSS_Content or what ever you enter when you create a new application. (and I thought the “WSS” name was gone!)
  
• WSS_Search_yourservername – The WSS search database
  
• The new logging database! WSS_Logging. Think of this one as the one SharePoint database you are supposed to do direct TSQL queries from! As a teaser… here’s a list of the views (I’d show the tables, but each has 32 partitions): 
                                                

Files from my install (your GUIDs will vary)

   

  Databases and log files in SharePoint Foundation 2010:

• Bdc_Service_DB_778ccd3e047d45c1b71529c004ebbd2c.mdf
  Bdc_Service_DB_778ccd3e047d45c1b71529c004ebbd2c_log.LDF
  
• SharePoint_AdminContent_4ab2813a-fe77-4085-86df-41d3a79df116.mdf
  SharePoint_AdminContent_4ab2813a-fe77-4085-86df-41d3a79df116_log.LDF
  
• SharePoint_Config_e5403261-4d7a-4b9e-bef2-286c46b588da.mdf
  SharePoint_Config_e5403261-4d7a-4b9e-bef2-286c46b588da_log.LDF
  
• WSS_Content.mdf
  WSS_Content_log.LDF
  
• WSS_Logging.mdf
  WSS_Logging_log.LDF
  
• WSS_Search_MAXSP2010BETA2.mdf
  WSS_Search_MAXSP2010BETA2_log.LDF

   Additional databases and log files in SharePoint Server 2010:

• Application_Registry_Service_DB_b669b0a586ce4cfaa1020e9e75824843.mdf
  Application_Registry_Service_DB_b669b0a586ce4cfaa1020e9e75824843_log.LDF
  
• Managed Metadata Service_6b696a9bda574b29a0bba4009c782316.mdf
  Managed Metadata Service_6b696a9bda574b29a0bba4009c782316_log.LDF
  
• PerformancePoint Service Application_106ff2206d784297adbd4ec157aef7b5.mdf
  PerformancePoint Service Application_106ff2206d784297adbd4ec157aef7b5_log.LDF
  
• Search_Service_Application_CrawlStoreDB_16d445d62e314cd58c82d75fc304a225.mdf
  Search_Service_Application_CrawlStoreDB_16d445d62e314cd58c82d75fc304a225_log.LDF
  
• Search_Service_Application_DB_e172668d1a534fd981b53146014d5ef3.mdf
  Search_Service_Application_DB_e172668d1a534fd981b53146014d5ef3_log.LDF
  
• Search_Service_Application_PropertyStoreDB_10c4d562200442748ca6339d4b358c84.mdf
  Search_Service_Application_PropertyStoreDB_10c4d562200442748ca6339d4b358c84_log.LDF
  
• Secure_Store_Service_DB_edd7b8e261fa491bb336779301aa5bf9.mdf
  Secure_Store_Service_DB_edd7b8e261fa491bb336779301aa5bf9_log.LDF
  
• StateService_335919bb65d1430aa20424a67a99589e.mdf
  StateService_335919bb65d1430aa20424a67a99589e_log.LDF
  
• User Profile Service Application_ProfileDB_9e421f2d762c46609a8a15fab15a3d52.mdf
  User Profile Service Application_ProfileDB_9e421f2d762c46609a8a15fab15a3d52_log.LDF
  
• User Profile Service Application_SocialDB_81269e32afd049d48994a72362806da4.mdf
  User Profile Service Application_SocialDB_81269e32afd049d48994a72362806da4_log.LDF
  
• User Profile Service Application_SyncDB_8c10242fd8064682aa1ac620acfa1733.mdf
  User Profile Service Application_SyncDB_8c10242fd8064682aa1ac620acfa1733_log.LDF
  
• WebAnalyticsServiceApplication_ReportingDB_1684bae4-0271-40f0-9e4d-84c6660e99e0.mdf
  WebAnalyticsServiceApplication_ReportingDB_1684bae4-0271-40f0-9e4d-84c6660e99e0_log.LDF
  
• WebAnalyticsServiceApplication_StagingDB_13d64d79-9237-492c-ad2d-48785d13a1ff.mdf
  WebAnalyticsServiceApplication_StagingDB_13d64d79-9237-492c-ad2d-48785d13a1ff_log.LDF
  
• Word Automation Services_5fd65b951cf1499eaa72997b9668fd04.mdf
  Word Automation Services_5fd65b951cf1499eaa72997b9668fd04_log.LDF
  

 

  There are also the standard SQL Server databases there:

• master.mdf
• mastlog.ldf
• model.mdf
• modellog.ldf
• MSDBData.mdf
• MSDBLog.ldf
• tempdb.mdf
• templog.ldf

   And this file:

• MS_AgentSigningCertificate.cer
  

.

SharePoint 2010: Error Messages

 

A few end-user (browser) error messages common in SharePoint 2010 Beta 2…

 

Message from webpage:

 

- A datasheet component compatible with Microsoft SharePoint Foundation is not installed. - Your Web browser does not support ActiveX controls. - A component is not properly configured for 32-bit or 64-bit support.

 

The error above most often from not having a suitable version of Microsoft Office installed.

This error is often display when trying to use:

• Multiple file upload
• Datasheet view

 

This control is currently disabled

Error displayed from the ribbon:

 

This error is often display when trying to use:

• Multiple file upload
• Outlook sync when Outlook is not installed

Message from webpage – The document could not be created.

The following message is from clicking the New button in a document library when Office is not installed.

 

Excel Export

No Excel 2007 or 2010 installed.

SharePoint 2010: SharePoint Foundation Databases

 

The following is a list of databases installed in a “Basic” install of SharePoint Foundation 2010.

 

Notes:

• SQL Server Express 2008 Service Pack 1 was installed
  

   Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
       Mar 29 2009 10:11:52
       Copyright (c) 1988-2008 Microsoft Corporation
       Express Edition (64-bit) on
        Windows NT 6.1 <X64> (Build 7600: ) (VM)

• If you want to install the SQL Express Management Tools:
• remember to download the 64 bit version
• check out this article or you won’t guess the correct choice (New Installation, not Add new features) http://www.asql.biz/Articoli/SQLX08/Art3_1.aspx
• the SQL Profiler is not part of the Express tools
  
• A new instance was created named SHAREPOINT  (yourservername.SHAREPOINT). The instance was created in
  C:\Program Files\Microsoft SQL Server\MSSQL10.SHAREPOINT
  
• The databases were created in:
  C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Data\MSSQL10.SHAREPOINT\MSSQL\DATA
  

Database Names:

• The “system” databases are named with GUIDs. We used to avoid this, or rename these, but it’s probably best to now leave these alone.
  In SharePoint Foundation these include:
• Bdc_Service_DB_guid   -  Business Connectivity Services (BCS)  (the old BDC)
• SharePoint_AdminContent_guid  - Central Administration
• SharePoint_Config_guid – the SharePoint Configuration database
  
• The content databases follow the 2007 pattern: WSS_Content or what ever you enter when you create a new application. (and I thought the “WSS” name was gone!)
  
• WSS_Search_yourservername – The WSS search database
  
• The new logging database! WSS_Logging. Think of this one as the one SharePoint database you are supposed to do direct TSQL queries from! As a teaser… here’s a list of the views (I’d show the tables, but each has 32 partitions):
                                                

Files from my install (your GUIDs will vary)

      

  Databases and log files:

• Bdc_Service_DB_778ccd3e047d45c1b71529c004ebbd2c.mdf
  Bdc_Service_DB_778ccd3e047d45c1b71529c004ebbd2c_log.LDF
  
• SharePoint_AdminContent_4ab2813a-fe77-4085-86df-41d3a79df116.mdf
  SharePoint_AdminContent_4ab2813a-fe77-4085-86df-41d3a79df116_log.LDF
  
• SharePoint_Config_e5403261-4d7a-4b9e-bef2-286c46b588da.mdf
  SharePoint_Config_e5403261-4d7a-4b9e-bef2-286c46b588da_log.LDF
  
• WSS_Content.mdf
  WSS_Content_log.LDF
  
• WSS_Logging.mdf
  WSS_Logging_log.LDF
  
• WSS_Search_MAXSP2010BETA2.mdf
  WSS_Search_MAXSP2010BETA2_log.LDF

 

  There are also the standard SQL Server databases there:

• master.mdf
• mastlog.ldf
• model.mdf
• modellog.ldf
• MSDBData.mdf
• MSDBLog.ldf
• tempdb.mdf
• templog.ldf

 

  And this file:

• MS_AgentSigningCertificate.cer
  

 

 

SharePoint 2010: Select Template Later!

In SharePoint 2003 an administrator could create a new site collection without having to select a template. The link to the new site collection could then be emailed to the new owner and on their first visit to the new URL they could select a template. This option disappeared in 2007. It’s back in 2010! (but with a very interesting difference… see below…)

Note: This only works from Central Administration when creating a new site collection. It is not available when creating sub sites within an existing site.

 

When creating a site in Central Administration:

 

When first visiting the new URL:

 

What’s Different?

In 2003 you had to select a template before continuing. Now in 2010 you actually have a functioning, but template-less, site. See the Site Actions, View All Site Content and Recycle Bin options? They all work even though a template has not been selected yet.

The new (template-less) site defaults to /_layouts/templatepick.aspx as a home page. There is no default.aspx page or Site Pages library yet.

As a test I went to Central Administration and created a new site collection with this template option. I then went to the site and ignored the request to pick a template and went and changed the site title, the theme, activated the “Team Collaboration Lists” feature and added a Links List for “Vendors”. I then returned to templatepick.aspx page and selected a template. It all seems to work fine.

So now we can:

• Let the new site owner pick their own template
• Preload lists and libraries
• Preselect a theme

Let us know if you find any issues with this…

 

A final note…

If the template can be picked at a later time, can it now be changed?  Sorry, but no. If you return to /_layouts/templatepick.aspx and try to select a different template you will get:

 

.

SharePoint: List and libraries by SharePoint version (2007 & 2010)

Just a quick list… maybe I’ll find some time to add links to resources for each list and library type.

 

SharePoint 2010: Missing Search Scope Dropdown

 

In SharePoint 2007 there is a search scope dropdown:

 

This is missing in a default setup of SharePoint 2010:

 

It’s still available, you just have to turn it on. Just go to Site Actions, Site Settings, Site Collection Administration, Search Settings:

Note that this is a site collection setting, not per sub site.

 

Select “Show scopes dropdown and it’s back!

 

This was an option only in MOSS 2007. WSS 2007 always had the scope dropdown. I have not checked SharePoint 2010 Framework yet to see if is an option there…

SharePoint 2010: "the update is not applicable to your computer”

 

If you install SharePoint 2010 Beta 2 you will probably get this error when using search and other services:

Unrecognized attribute 'allowInsecureTransport'

 

If you do a little web searching you will find that you need this update:

KB976462: https://connect.microsoft.com/VisualStudio/Downloads/DownloadDetails.aspx?DownloadID=23806&wa=wsignin1.0

 

If you have already installed SharePoint 2010 Beta 2 then go ahead and run this patch. But, be aware: “Service Applications that have been successfully provisioned without the update installed may need to be removed and re-provisioned once the update has been successfully applied”

 

If you start a new server build and try to install this patch you will get:

"the update is not applicable to your computer"

You must install the SharePoint 2010 prerequisites first, then install this patch.

 

Then I got this error: (but only on my second build! Go figure…)

“Failed to create sample data”

and found the fix here:  (and then restarted the Configuration Wizard)

http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/thread/f239de4a-488e-47e1-8f1e-b382fd4668fa

 

Here is the correct install order (or at least what worked for me):

1. Install Windows Server 2008 (I installed 2008 R2 Standard and did no additional configuration except for networking)
2. If you are going to rename the server, do it before installing SharePoint (or at least before you install SQL Server).
3. Run the SharePoint install and click “Install SharePoint 2010 prerequisites” and complete this install.
4. Now install the “patch” (you will need to restart after the install)
5. Now return to the SharePoint install and click “Install SharePoint Server”

 

Just in case you are curious… the SQL Server installed with a “standalone” install is the 64bit version of SQL Express:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
       Mar 29 2009 10:11:52
       Copyright (c) 1988-2008 Microsoft Corporation
       Express Edition (64-bit) on
        Windows NT 6.1 <X64> (Build 7600: ) (VM)

 

 

.

SharePoint 2010 Beta 2 now on MSDN and TechNet!

Got an MSDN or TechNet subscription???

But don’t start your download until I’m finished!!!

 

First notes:

• Both Foundation and Server are available for download
• The downloads are installers. No virtual disks yet.
• The download is a single file install. When started in offers a link to “Read the install guide” that when clicks opens a browser to a Microsoft SharePoint 2007 web page. (It’s a beta…)
  
• There is a bug!  (Unrecognized attribute 'allowInsecureTransport'") See here for the pending fix, and for additional installation notes:
  http://blogs.msdn.com/opal/archive/2009/11/16/installation-notice-for-sharepoint-2010-public-beta.aspx

Exam Discounts

This is a note for people who have attended my classes. (Everyone else go see their own MCT!)

If you are thinking about Microsoft certification, check with me before signing up for an exam. I can usually get you a discount on both the exam and on practice exams.

Currently:

• 10% off of the exam
• 40% on any MeasureUp 60 Day Online Practice Test

Not all exams qualify, but most MCP exams do. MeasureUp does not have a practice test of every Microsoft exam.

If you would like a discount voucher, email me at the email address I gave you during class or just call MAX Technical Training.

 

Know any college or tech school students? They can 55% off of exams here:
http://www.prometric.com/microsoft/student

.

SharePoint: Hide a web part with zero rows

 

(I wrote this up a few months ago, and then forgot to post it. So here it is finally!)

 

Here’s a JavaScript trick to hide a list or library web part when there are no items in the list, or the current view used in the web part.

 

Steps:

• On the same page as your web part, add a Content Editor Web Part (CEWP)
• Select Edit and then Modify Shared Web Part
• In the Advanced section set the Chrome to none
• Click the Content Editor button and add the JavaScript from below
• Replace "Test List" with your web part's name
  (If you don’t know the name or your guess does not work, view the HTML source of the page (View, Source in IE) and do a search for “summary=”.)

Note that the commented out section can be used to just hide the title bar instead of the entire web part.

 

<script>
function HideWebPartWithZeroRows()
{

  var a = document.getElementsByTagName("TABLE")
  for (var i=0;i<a.length;i++)
  {
    if (a[i].summary=="Test List")
    {
      if (a[i].rows.length==1)
      {
        //hide all but the title bar
        //var x =  a[i].parentNode.parentNode.parentNode.parentNode.parentNode

        //hide the entire web part
        var x =  a[i].parentNode.parentNode.parentNode.parentNode.parentNode.parentNode.parentNode.parentNode.parentNode

        x.style.display="none";
      }
    }
  }

}

_spBodyOnLoadFunctionNames.push("HideWebPartWithZeroRows") 

</script>

 

 

.