Jesper M. Christensen

SharePoint and Security

Lookup the SharePoint 2013 app-web url


The SharePoint 2013 apps are easy to install, update and uninstall and our team has a lot of experience in developing and using apps at customers.

Sometimes, though, we need some more information about the app itself. I was troubleshooting a customer environment as an app did not work. It could install, update and uninstall but an error occurred when accessing the app. I detected that no DNS entry was made for the app (usually with the prefix app-<guid>), but I could not see from the error URL or other information what the <guid> was. In the ULS I could detect this, but wanted to check other apps – without using the ULSViewer.

Here is the method I used for looking up the app-web URL with Windows PowerShell:

(SharePoint snap-in already loaded in the Windows PowerShell)

 

Get the installed apps:

     $instances = Get-SPAppInstance -Web http://intranet.domain.com

List apps installed:

     $instances

Get a single app, here “RePoint Infoscreen”

     $instance = $instances | where {$_.Title -eq ‘RePoint Infoscreen’}

See properties of the app instance:

     $instance

With PowerShell you can also do other things to apps, and here is the command for uninstalling the app above.

To uninstall the app:

     Uninstall-SPAppInstance -Identity $instance

I hope this can help you also as I could not find the scripts easily on the internet. If you are interested in trying our apps, please visit the Microsoft Office Store

Changing the colors of your SharePoint 2013 or Office 365 My Site


This is a re-post from my blog-entry in the SharePoint-Community.net (http://sharepoint-community.net/profiles/blogs/changing-the-colors-of-your-sharepoint-2013-or-office-365-my-site). Please join us 🙂

-o-

As you may have noticed the link to change the look of the SharePoint 2013 and Office 365 My Sites has been removed. This is most certain for a reason and one of these is that choosing one of the other looks (masterpages) removes the image in the top left corner of the page.

Please be aware that this is most likely not supported by Microsoft.

I will demonstrate how you can make a new “Composed Look” that points to the correct MySite15.master masterpage and let you choose the colors. This method can also be used to restore the MySite to it’s default – if you have changed it by “Accident”… like me

Okay, let’s start.

  • Go to the Site Settings and click the “Composed Looks”

Capture1

Here you will see all the default combinations, and all of these refers to the default masterpages that is NOT made for My Sites. You can also see the other default settings for these looks. You need to add a new look that is pointing to the mysite15.master page and a spcolor-file as a minimum.

  • Click “New Item” and fill out the following:

Capture2

Now you need to create a preview file for your My Site masterpage, as the element will not show up in the design gallery.

  • Go back to “Site Settings” and choose “Masterpages”

Capture3

  • Click on the arrow next to the seatle.master and choose “Download a Copy”

Capture4

  • Rename your downloaded file to: mysite15.preview

Note: The name must be identical of the masterpage you are going to show in the gallery – in this case the masterpage filename is mysite15.master

  • Upload your renamed file to the masterpages. Please make sure the file is checked in and published as a major version, or non-admins cannot use it.
  • Go to the hidden design gallery by adding the following to your mysite root-URL: /_layouts/15/designgallery.aspx

Capture5

Choose your new composed look and change the colorscheme and background if you want. Don’t mind the “incorrect” preview here, as it was a copy of the seatle preview file.

Click “Try it out” and afterwards “Yes, keep it”.

Now you should have your My Site “back” and with the new colors.

SharePoint 2013 limits


During some reading up on the SharePoint 2013 exams I created a little remember-diagram for myself to help me memorize the limits/boundaries and recommendations of the different SharePoint elements.

SharePoint 2013 limits

Optimizing SQL Server performance for Microsoft SharePoint 2010/2013


This is a duplicate post from my SharePoint Community blog: http://sharepoint-community.net/profiles/blogs/optimizing-sql-server-performance-for-microsoft-sharepoint-2010

– | –

During my time as a consultant I see a lot of environments. More and more I have been involved in setting up the SQL Servers for SharePoint and I would like to share this information with you all.

Please consult a SQL Server Database Administrator and a SharePoint Administrator and discuss the settings before making any adjustments in your environment(s) – I can only give advice and not be responsible for any wrong configurations – remember that every environment is different!

90% of all data of a Microsoft SharePoint 2010/2013 farm is placed in the SQL Server databases, and every transaction from the users, all SharePoint services and SharePoint timer-jobs are working with these databases at the same time. SharePoint is depends on the performance of the web frontend servers and SQL servers. If the users should have the best experience of SharePoint, you need to optimize the resources that are used by SharePoint.

On the web frontend servers the Internet Information Server (IIS) delivers the content to the users. CPU and especially memory is important here as SharePoint uses the server memory to cache the information requested by the users. Usually this is not an issue as IT administrators can monitor these resources and do as a normal performance evaluation of Microsoft servers.

Fewer IT administrators or Database Administrators has knowledge of how SharePoint use the SQL Server and which components to optimize for delivering the best SharePoint end user experience. This article is about giving a little insight on this subject.

Physical or virtual servers?
Running the Windows Server and SQL Server directly on the hardware itself will give you the optimal performance. You will not have the advantages of the virtual environments such as failover, backup/restore and flexibility for the IT administrators. In my experiences a good virtual setup is as good as a physical one – and is the most chosen of my customers.

The SQL Server instance
On the SQL server we have some general best practices to follow and this means that the database administrators must change some default values to make the SQL server, and thereby also the applications, to perform better. SQL servers that host SharePoint databases are no exception, but certain settings should be changed to give even more power to the SharePoint servers. These settings can also have influence on other databases in the same SQL instance so it is advised to have a separate SQL Server for SharePoint – or at least a separate instance on an existing SQL Server.

If you choose to run multiple instances of SQL Server on a single Windows Server you need to control your CPU usage, minimum + maximum memory usage and database placement on disks. If the load on the databases and the SharePoint servers is small to medium you will achieve okay performance. But still, try to separate the SQL Servers to multiple Windows Servers as the used network bandwidth also has an effect on performance.

Memory is also used by the SQL Server for caching data requested by the applications. Therefore you should have plenty memory allocated to the SQL Server for best performance. It can be hard to evaluate how much memory is required as the SQL Server adjust the memory used depending on how much memory is available.

The CPU is used to perform calculations, queries and sorting the data. CPU usage can easily be monitored and evaluated if more CPU power is needed.  Multiple processors with multiple cores is highly recommended as the SQL Server can take advantage of these.

The default file location of database and log should be set according to your needs (see the section Disk and file locations)

SQL Server version
The SQL Server should be a SQL Server 2008 (64 bit) with SP1 and CU 5 (or later version) installed even though former versions this edition of SQL Server is optimized for 64-bit and Microsoft SharePoint 2010/2013 can take advantage of the new functionality giving you’re a better overall performance. SharePoint will detect the SQL Server version and adjust the queries accordingly to the features available.

Memory limits
Add as much memory as possible on the Windows Server – the SQL Server will automatically allocate and cache based on the amount of RAM for the SQL Server instance.

The SQL Server will control the memory usage according to predefined rules in the SQL Server dynamic memory management.

If you only have one SQL Server instance on the server there’s not need to do memory adjustments!

If other applications or instances of SQL Server run on the same Windows Operating system then you need to adjust every SQL Server instance memory limits. By default the SQL Server dynamic memory management will use all available RAM on the operating system and release memory of this is required. Microsoft states this at http://msdn.microsoft.com/en-us/library/ms177455(v=sql.105).aspx

The minimum memory limit is set to a value that the SQL Server instance cannot go below if it passes this limit. This value should be set to avoid the server to avoid unnecessary release of memory resources that the serve

The maximum memory limit defines the maximum memory the SQL Server instance is allowed to use. If you have multiple instances on the same Windows Server then evaluate how much each SQL Server instance should be allowed to use and how much should be available to the operating system and other programs.

Disk and file locations
You have the possibility to set the default locations for the database and the database log files for each SQL Server instance on the Windows Server. It is advised to use separate physical disks as the requirements for database files and log files are different. Performance wise you should also evaluate the load of the different databases as some might require more resources than others. Log files requires good write performance and the database files most read and caching performance

It is advised to place non-SharePoint databases on separate physical drives because SharePoint is very database transaction intensive.

The optimal disk cluster size (is set during formatting the drive) is 64k. Using this recommended cluster size the server reads 64k at the time and can deliver larger chucks of data to the SQL Server.

For the best performance you can add additional transaction log files to separate disks (not disks you are using for database files) Create same sized files on separate disks for tempdb, Content and Search databases. Use the same number of files as physical processor socket on your SQL Server to take advantage of the CPU’s (e.g. 2 data files on a 2 socket machine, 4 data files on a 4 socket machine).

System databases and SharePoint
The Model database, “Model”, is a system database that defines how a new database is default created by administrators and SharePoint. Set the default size on the Model-database to what size the future databases should be. If is advised to set this to what size each of your content database is believed to be in 1½-2 years. You should set the size to one value before the SharePoint installation and change this to your future default content database size afterwards.

The default created size could differ from what settings you want on non-SharePoint databases and therefore it is advised to have a separate SQL Server or SQL Server instance for the SharePoint databases.

In my experience the growth rate is not adjusted on new databases with these are created by SharePoint – so please check the database file growth periodically (you can use my remote Windows PowerShell script which can be found here: Checking SQL database db and log file sizes and growth with PowerSh…)

The Temp database, “Tempdb”, is also a system database and this is used in a special way by SharePoint. It is used only as a working area and do not contain data that is important. At every SQL Server reboot the Temp database is rebuild. We should set the recovery mode to SIMPLE as we do not need to recover any information in the tempdb database.

The Temp database should be placed on a very fast drive for best performance on sorting and filtering operations. Microsoft SharePoint views are never faster than the “tempdb” can deliver data.

Database settings
Microsoft SharePoint use different databases for different purposes. Some contain configuration data and others the actual content. Most databases are created during the installation of the SharePoint farm so you must have your SQL Server settings correct before installing the product if you want to avoid performance issues or reconfiguration.

To make sure that putting content in your SharePoint databases is fast I recommend you to pre-grow these. Set the Database and log-sizes to what you expect within a year. This will also help reducing fragmentation and performance impact.

You should evaluate the usage of every database and adjust the

  • Database and log files
    • Location (fast separate drives)
    • Size (~ what you expect within 1 years)
    • Growth (~ 25% of the db size)
    • Recovery Model (FULL recommended)
    • Auto-shink -> set to the default: OFF

You should set the recovery mode to FULL on every SharePoint database to make sure that your data can be restored properly if a disaster happens – remember to do database and log backups to avoid running out of disk space. Also consult a SQL Server Administrator to ensure this is set up correctly – and check the backups with test-restore so you know that everything is good (and the restore time is okay with you).

It is advised that the Search databases are placed on even different physical disks or even a separate SQL Server, as the crawler- and index service is performing many transactions during a crawl and index propagation.

A small side-note: If you database administrators creates new databases for you, they must create these with a collation of Latin1_General_CL_AS_KS_WS. During the installation of the SQL instance they could set this to the default for new databases. If databases is created from the SharePoint user Interface, this value is set automatically.

Shrinking the logfiles – when?
The database transaction logfiles seems to keep on growing – especially if you do not back these up. This is because every transaction is put in these and not “flushed” until you do a backup. When you perform a backup the file gets “empty” and is ready for new transactions. If your transaction logfiles is too big then you need to shrink these – but please leave some empty space and don’t shrink to the minimum size. When a file needs to grow it has a large impact on performance.

Network
As all data is sent over the network the bandwidth between all servers must be optimal. A 1Gbit network connection on the servers is advised at all times. Also be sure that devices can handle the load – such as network routers, switches and firewalls.

Key takeaways
Here are some key adjustments from this article:

I made a little graphical overview also:

Optimizing SQL Server Performance for Microsoft SharePoint 2010-2013

What I did not cover
Adjusting the SQL Server for optimal performance is not easy and I can only give you my experiences. There are a lot of possibilities and scenarios that I did not cover here and some of these are

  • SAN optimization
  • Database partitioning

Please contact me or reply to this article if you have comments, suggestions or any other thoughs.

SP Conference for Business users


If you are interested in my summary for the conference days on the SharePoint Conference for Business Users arranged by T. A. Cook, then please read this at the SharePoint Community net:

SP Conference for Business users – Workshop day


If you are interested in my summary for the workshop day on the SharePoint Conference for Business Users arranged by T. A. Cook, then please read this at the SharePoint Community net:

http://sharepoint-community.net/profiles/blogs/sp-conference-for-business-users-workshop-day

SP2013 problem: Office 2010 and an Office 2013 product on same client PC


This is a duplicate post from my SharePoint Community blog: http://sharepoint-community.net/profiles/blogs/sp2013-problem-office-2010-and-an-office-2013-product-on-same

– | –

I ran into an issue at a client that we are testing the migration to SharePoint 2013. Everything has been a smooth transition – thanks to the SharePoint 2010 support in SharePoint 2013!

The testing started and we discovered that some clients had no problem in opening documents, whereas other clients got strange error messages such as:

“Page cannot be displayed” – with a reference URLs starting with ms-word:ofv|u|

We narrowed the problem to the installation of SharePoint Designer 2013 on the client that also had Office 2010 installed. The installation af SharePoint Designer 2013 updated the OpenDocumentClass (OWSSUPP.DLL) in Internet Explorer – and the Office 2010 programs do not recognize these links.

The problem, which also is described in the Technet forum post http://social.technet.microsoft.com/Forums/en-US/sharepointitpropreview/thread/163114c0-ff7b-4726-a319-05d828eecbb3/, also appears if you install the Microsoft Lync 2013 client on a computer with Office 2010.

The solution seems to be (at least for the moment):

  • Uninstall the Office 2013 product (SharePoint Designer/Lync) and run a repair on Office 2010 on the client computer
  • installing a full Office 2013 program suite on the client computer

So testing is a really good idea (as always) – especially when installing new versions of products with older versions already installed.

Please respond to this post if you have any other solution 🙂

Checking SQL database db and log file sizes and growth with PowerShell – from your Windows 7/8


When you are working with products that are using SQL Server databases it is important that the performance is good. By default the SQL Server sets a very bad default-value for size and Growth, and should always be changed! What to put in really depends on the usage of each database.

When I configure Microsoft SharePoint farms I performance optimize the SQL databases and wants to check the size and Growth of the database and log files, as this is essential for good performance. I want to do this from a client computer using Windows Powershell, and put this script together from various internet articles. If this can be done more efficieltly then please write me 🙂

You need to have the SQL Server Management Tools/SDK installed on the client running this script. The free SQL Server 2012 Management Tools can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=29062

##############################################################
 #
 # Powershell script by Jesper M. Christensen
 # Blog: http://JesperMChristensen.wordpress.com
 #
 # Check SQL Server for all databases matching a query
 #
 # Display database name, db+log file size and growth
 #
 # GetSQLDBInfo.ps1 Version 1.0
 #
 ##############################################################

CLS
Set-StrictMode -Version 2
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
$Conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$Conn.applicationName = "PowerShell GetSQLDBInfo (using SMO)"

#Set the parameters for the environment
$Conn.ServerInstance="SQLServer\Instance"
$Conn.LoginSecure = $false                  #Set to true connect using Windows Authentication
$Conn.Login = "sa"                          #Do not apply if you use Windows Authentication
$Conn.Password = "SAPassword"               #Do not apply if you use Windows Authentication

#Connect to the SQL Server and get the databases
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $conn
$dbs = $srv.Databases

#Process all found databases
foreach ($db in $dbs)
 {

 #Process databases if the starts with the database pattern
 if ($db.name -like "SP2013_*") {
  write-host $db.Name

  #Process all database files used by the database 
  foreach ($dbfile in $db.FileGroups.files) {
   $dbfilesize=[math]::floor($dbfile.Size/1024)           #Convert to MB
   if ($dbfile.growthtype -eq "KB") {$dbfilegrowth=[math]::floor($dbfile.growth/1024)} else {$dbfilegrowth=$dbfile.growth}                    #Convert to MB if the type is KB and not Percent
    write-host $dbfile.filename, "Size:"$dbfilesize"MB", "Growth:"$dbfilegrowth, $dbfile.growthtype
   }

  #Process all log files used by the database 
  foreach ($dblogfile in $db.logfiles) {
   $dblogfilesize = [math]::floor($dblogfile.size/1024)   #Convert to MB
   if ($dblogfile.growthtype -eq "KB") {$dblogfilegrowth=[math]::floor($dblogfile.growth/1024)} else {$dblogfilegrowth=$dblogfile.growth}     #Convert to MB if the type is KB and not Percent
    write-host $dblogfile.filename, "Size:"$dblogfilesize"MB", "Growth:"$dblogfilegrowth, $dblogfile.growthtype
   }
   write-host "-"
  }
 }
 #Disconnect from the SQL Server database
 $srv.ConnectionContext.Disconnect()

A screenshot of the output that can be analyzed and should be changed:

sqldbsize

I hope this will help you get an overview of your databases.

Set SharePoint 2013 Navigation Settings from PowerShell


Hi everyone,

I just got a large SharePoint 2013 site to set up and wanted to set all the sites navigation settings through Windows PowerShell.

It seems to be quite easy, but when I set the “Current Navigation” settings to Structured the UI (user interface) settings page do not show these settings – it Works though, but is a bit confusing. Actually nothing is checked in the boxes, but for now I am good – hope I found out why this is happening. Anyone knows this?

navigationsettings

I use the script below to connect to a site Collection, set this navigation settings and afterwards all sub sites. This script do not handle Metadata Navigation.

################################################################
#
# Powershell script by Jesper M. Christensen
# Blog: http://JesperMChristensen.wordpress.com
#
# Set the SharePoint 2010 and 2013 Navigation Settings on Sites
#
# SetupNavigationSettings.ps1 version 1.0 - Edited January 2013
#
################################################################
Write-Host -ForegroundColor White "Set the SharePoint 2010 and 2013 Navigation Settings on Sites"

#Set the Site Collection
$SPSite = Get-SPSite -Identity "http://extrico-demo2.dev.extrico.local/sites/demo1"

#Go through each site in the Site Collection
foreach ($SPWeb in $SPSite.AllWebs)
{
if ($SPWeb.IsRootWeb)
{#Process the root web
Write-Host -ForegroundColor Gray $SPWeb.Url":" -NoNewLine
#Save the AllowUnsafeUpdatesStatus property value
$AllowUnsafeUpdatesStatus = $SPWeb.AllowUnsafeUpdates
$SPWeb.AllowUnsafeUpdates = $true

#Set the Publishing Web
$SPPubWeb = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($SPWeb)

#Global Navigation Settings
$SPPubWeb.Navigation.InheritGlobal = $false
$SPPubWeb.Navigation.GlobalIncludeSubSites = $true
$SPPubWeb.Navigation.GlobalIncludePages = $false
$SPPubWeb.Navigation.GlobalDynamicChildLimit = 21

#Current Navigation Settings
#
# -Display the same navigation items as the parent site: InheritCurrent = $true and ShowSiblings = $false
# -Structural Navigation: Display the current site, the navigation items below the current site, and the current site's siblings: InheritCurrent = $false and ShowSiblings = $true
# -Structural Navigation: Display only the navigation items below the current site: InheritCurrent = $false and ShowSiblings = $false
#
$SPPubWeb.Navigation.InheritCurrent = $false
$SPPubWeb.Navigation.ShowSiblings = $true
$SPPubWeb.Navigation.CurrentIncludeSubSites = $false
$SPPubWeb.Navigation.CurrentIncludePages = $false
$SPPubWeb.Navigation.CurrentDynamicChildLimit = 21

# Sorting
$SPPubWeb.Navigation.OrderingMethod = "Manual" # "Automatic" "ManualWithAutomaticPageSorting"
$SPPubWeb.Navigation.AutomaticSortingMethod = "Title" # "CreatedDate" "CreatedDate" "LastModifiedDate"
$SPPubWeb.Navigation.SortAscending = $true

#Update the Publishing Web Navigation Settings
$SPPubWeb.Update()
Write-Host -ForegroundColor Green " Done"
}
else
{#Process all sub-webs to the root web

Write-Host -ForegroundColor Gray $SPWeb.Url":" -NoNewLine

#Save the AllowUnsafeUpdatesStatus property value
$AllowUnsafeUpdatesStatus = $SPWeb.AllowUnsafeUpdates
$SPWeb.AllowUnsafeUpdates = $true

#Set the Publishing Web
$SPPubWeb = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($SPWeb)

#Global Navigation Settings
$SPPubWeb.Navigation.InheritGlobal = $true
$SPPubWeb.Navigation.GlobalIncludeSubSites = $true
$SPPubWeb.Navigation.GlobalIncludePages = $false
$SPPubWeb.Navigation.GlobalDynamicChildLimit = 21

#Current Navigation Settings
$SPPubWeb.Navigation.InheritCurrent = $false
$SPPubWeb.Navigation.ShowSiblings = $true
$SPPubWeb.Navigation.CurrentIncludeSubSites = $false
$SPPubWeb.Navigation.CurrentIncludePages = $false
$SPPubWeb.Navigation.CurrentDynamicChildLimit = 21

# Sorting
$SPPubWeb.Navigation.OrderingMethod = "Manual" # "Automatic" "ManualWithAutomaticPageSorting"
$SPPubWeb.Navigation.AutomaticSortingMethod = "Title" # "CreatedDate" "CreatedDate" "LastModifiedDate"
$SPPubWeb.Navigation.SortAscending = $true

#Update the Publishing Web Navigation Settings
$SPPubWeb.Update()
Write-Host -ForegroundColor Green " Done"
}

#Revert the AllowUnsafeUpdatesStatus property value
$SPWeb.AllowUnsafeUpdates = $AllowUnsafeUpdatesStatus

#Dispose the SPWeb object
$SPWeb.Dispose()
}

#Dispose the SPSite object
$SPSite.Dispose()

Windows Phone 8 – Outlook sync error code: 80070057


My boss got a brand new Nokia Lumia 920 with Windows Phone 8 and was very happy about it – as I am also with mine. He got a problem though with synchronizing his Outlook account from Office 365 to this phone though. It worked on his HTC with Windows Phone 7.5 and also from an iPhone!

The error showed on the WP8 device was:

“We’re having a problem synching your information. Try again later. Last tried 2 seconds ago. Error code: 80070057”

After checking the device setup, deleting/adding the account and Microsoft Live ID and much more we found this Microsoft Knowledge base article: http://support.microsoft.com/kb/2792112

It clearly was the same error – but we could not find a folder with this problem… After renaming quite a lot folders, we contacted the Office 365 support which helped us pinpoint the folder with the issue. This Microsoft knowledgebase article explains the steps you need to take to make and retrieve a log of the communication from the Windows Phone to the Exchange Server: http://support.microsoft.com/kb/2461792

It simply say that you log on the the Outlook Web Access, go into “Options”, “Show all Options” and to the “Phone” pane. Turn on logging for the Windows Phone device, try a sync.and click “Retrieve log” from the same page. Afterwards you will get an e-mail with the communication.

If you see a special char or CRLF (Carrige Return Line Feed – which means a new line) in the XML that lists the mailbox folders, then you need to rename this in Outlook Web Access to something else.

In my case the XML showed a line feed, as the </DisplayName> was pushed to a new line after the folder name “Kladder” as the example below shows:

      <Add>     
            <ServerId>23</ServerId>      
            <ParentId>0</ParentId>       
            <DisplayName>Journal</DisplayName>    
            <Type>11</Type>   
        </Add>
        <Add>
            <ServerId>24</ServerId>
            <ParentId>0</ParentId>
            <DisplayName>Junk E-Mail</DisplayName>
            <Type>12</Type>
        </Add>
        <Add>
            <ServerId>25</ServerId>
            <ParentId>0</ParentId>
            <DisplayName>Kladder
</DisplayName>
           <Type>12</Type>
        </Add>
        <Add>
            <ServerId>26</ServerId>
            <ParentId>0</ParentId>
            <DisplayName>Notes</DisplayName>
            <Type>10</Type>
        </Add>

Renaming this to “K” and back to “Kladder” did the trick and the Nokia Lumia 920 could synchronize the mailbox immediately.