Move SP databases to SQL 2016 Cluster

This post describes how to move SharePoint 2013 SP1 OnPrem server SQL content databases from SQL 2012 to 2016. This means that there is no upgrade involved, only a move to a new SQL Server Cluster. It worked really well. Even if I read lots of articles that it is not supported to run SharePoint 2013 on SQL 2016 it does work if you follow the steps below.

Step 1 Check the Health Analyzer in SP
This is an on prem server and the customer does not have an active SP admin, so I wanted to make sure the server was in a fresh state. I checked the messages in Health Analyzer and noticed that there was an Upgrade message which I wanted to correct first. All content databases had the message “Database is in compatibility range and upgrade is recommended”. I guess some other administrator had done an update on the server but missed the part with running the SharePoint Config Wizard afterwards. So I did just that, and once the Wizard was completed the messages were gone.

I also checked that the UPS and Search etc was running fine without any errors.

Step 2 Backup and Snapshots
Now that the servers are fresh, do an entire backup of all content databases and take a snapshot before you start, in case you must make a rollback.

Step 3 Document your server configuration

I always document the server configuration before I start any changes on a server, to make sure all services runs with the same accounts and mostly because my memory is really bad and it is easy to forget something. So I take screen shots of the Services with the SP accounts that are running, I document “Services on server” inside SP, etc. I also look at the SQL server instance in the “Security” and “Permissions” areas to see what accounts are there and the roles they have. This is important, otherwise you will not be able to connect to the config database later.

Step 4 Verify what content databases that should be moved

Do not move over old databases, keep the servers fresh and without old data. So I checked what databases to be moved. I discovered that there were two Config databases, so only one of them should be moved into the new SQL server. Also, some “test” databases were there for old web applications that no longer was in use. So only move the databases that you actually use. You can see a list of which databases that are in use, if you go to “Upgrade and migration” in Central Administration and click on “Review database status”. There are all the content databases listed that SharePoint uses.

Step 5 Setup the new SQL server

Make sure all accounts that are used on the “old” SQL server are setup the same way on the new SQL server instance. Check dbo, security roles and permissions. You may have to select the dbo account in “Permissions” and check what roles it has, like “Connect to databases” etc. Then select the service accounts, they may have a different role setup.

Step 6 Stop the SP services

Now stop all SP related Windows Services on each SharePoint server in your farm. Do it in this order (thanks to Dan Holme and his article about moving databases http://sharepointpromag.com/sharepoint-administration/simple-guide-moving-sharepoint-content-databases-new-server):

SharePoint 2013: W3SVC, SPSearchHostController, OSearch15, SPWriterV4, SPUserCodeV4 (was not running), SPTraceV4, SPTimerV4, SPAdminV4, FIMSynchronizationService, FIMService, DCLoadBalancer15 (disabled), DCLauncher15 (disabled)

Then, open a command prompt with Run As Administrator, and enter the command IISRESET /STOP

Step 7 Detach databases on the source SQL server

In SQL Server Management Studio, right-click each SharePoint database (the ones you decided to move), point to Tasks, then click Detach. In the dialog that appears, click OK

Step 8 Copy databases to the target SQL server

We did not move the databases, but copied them to the new SQL Server. You’ll need both .mdf (database) and .ldf (log) files.

Step 9 Attach databases to the target SQL server

In Management Studio on the new SQL server, right-click the server or instance, then click Attach. In the dialog box, click Add, then select one database. Click OK to finish attaching the database. Repeat for all databases.

Step 10 Reassign the DBO of the databases

This is an important step. If you don’t assign the correct security roles, permissions and dbo to the databases the connection will fail from the SP server. When you attach databases to the target SQL server, your user account becomes the DBO of the databases so make sure you assign that back to the Farm Account.

Step 11 Create an Alias on the SharePoint server

You can now shut down the “old” SQL Server to make sure this step below really works.

Now we will force the SharePoint servers to use the new SQL Server instance. To do this, we will add an Alias in CLICONFG.EXE so run that on the SP server. Click on the tab “Alias”, and then on “Add” (if it is empty, which it was on my server. If not, then “Edit”). Do the setup according to this image, it is self explanatory. Very smart actually. It may take a while before this connection works, so maybe restart the “Browse SQL” service to refresh that and if you have done all steps above correct, it should work as soon as you have done the last step below. The alias is used to redirect the connections from the original source SQL server to the new SQL server. Repeat this on each and every SharePoint server.

NOTE: Do NOT forget to add the SERVER INSTANCE after the SQL Server names (on both the original and the new target SQL servers) otherwise the connection will fail.

Step 12 Start the Windows Services SharePoint services and IIS

Start the SharePoint services in Windows Services again on each SharePoint server. They should be started in the reverse order from how you stopped them.

So on SharePoint 2013 start them in this order: DCLauncher15, DCLoadBalancer15, FIMService, FIMSynchronizationService, SPAdminV4, SPTimerV4, SPTraceV4,  SPUserCodeV4,  SPWriterV4, OSearch15, SPSearchHostController, W3SVC,

Then start the IIS by running as an admin: IISRESET /START

Step 13 Start Central Admin and fingers crossed 🙂

Check that Central Administration fires up, if you are lucky – then all is back up again! Also make sure that all service applications like UPS (I always have to start that service manually from within SharePoint CA), Search etc are up and working. For me, all this was working as before. And last but not least, make sure all your sites are running!

Step 14 Reboot the SP server/s

This is maybe not necessary but I always do this to make sure that everything really runs… I reboot the SP servers one last time to be certain. And – all running fine after that too 🙂

Done!



Content database is in compatibility range and upgrade is recommended

Incident

This warning message has turned up in the Health Analyzer on a SharePoint 2013 OnPrem Server.

“Product / patch installation or server upgrade required”

productpatch

Finding the root cause

I opened the “Version history” to see when this message started to show up:

verhistorymes

I also checked the “Database status” in “Upgrade and Migration” and found that the same message was displayed on all web applications in the server farm:

dbupgradestatus

According to some articles out there, this can happen if a CU / patch is installed. Now this server is maintained by a host provider so it could of course have happened and they don’t have in their routines that they must run the SharePoint Configuration Wizard after a CU or any other SharePoint related patch has been installed. Normally the security updates should not affect this, but this time it seems like these patches (Security Updates for Microsoft SharePoint Foundation 2013…) has caused the incompatibility as the warnings started coming after these were installed:

winupdateslist

Solution

The solution was to complete the upgrade by either:

  • run the SharePoint Configuration Wizard
  • run the PowerShell command as stated in the above message

I decided to run the SharePoint Configuration Wizard and after quite a long time running the upgrade step, it gave me an error message first saying it could not start the User Profile service and then I got the Configuration Failed message. But no worries, it is because it could not start the ups service.

messconffailed

First I went back into the Health Analyzer and clicked the button “Reanalyze now” on the message and then it was gone. Also checked the “Upgrade and Migration” link, “Review database status” to see that the normal “No action needed” message was back on the databases. And it was, so the upgrade was indeed successful.

dbupgradeviewstatus

Went into “Services on server” and started the UPS service again.

Done.

Thoughts on branding in Office 365/SharePoint

Sometimes I wish that there was just one way to brand SharePoint and it would be fast and easy. Well I can dream on, right? There are so many ways to do this, and I have tried to sort out what the best way would be to brand a site with least amount of work and to avoid user interaction on new sites. You who have branded sites using Master Pages knows what I am talking about: yes, you create a fancy

Continue reading “Thoughts on branding in Office 365/SharePoint”

How to create cascading dropdown boxes in InfoPath

Learn how to use SharePoint lists as data connections in InfoPath and how to use that as cascading drop down lists in InfoPath. Often when I build forms I find it very useful to have SharePoint lists as lookup information, both because it is easy to setup and also the users can change the information without having to design the form in InfoPath and also because it is the easiest way to create cascading drop down list boxes. This video shows how to achieve this. Thanks for watching!

QuickTip: Embed a video from Office Video on a page

It’s a new feature in Office Video (for #Office365 licenses) that you can now embed one of the uploaded videos on a page. Which is great! You can also control the size in the code.
Go to your Office Video portal
Click on the video you want to embed
Click on “Embed” up in the right corner

Then copy the code inside the box

Then go back to your site, Edit the page and select “Embed code” up in the ribbon
Paste the code and make any changes
Save the page and your video is on a page 🙂
Now I just want to be able to select which thumbnail the video should display!

Display comments and likes in CQWP

This is an update from my former blog post about showing likes and comments in a news web part. And I also get questions about how I have done this, and people saying that they never managed to do the same. So even if I updated the former blog post with this solution, it may not have been clear enough how I did it.

First of all, I changed from using the CSWP (Content Search Web Part) to use the CQWP (Content Query Web Part) as I never got the Comments to work without always running a Full crawl (and we don’t want that!).

Then I create the news on a separate Blog site. The news will then be rolled up on the intranet front page in the following format:

As shown in the picture above, the news item is rolled up displaying Published Date, Title, Likes and Comments. Sometimes I also add the Category, but that part is easy and if you can add one you can add another of these fields, so I will mostly focus on how I did the Like and Comments part.

To modify the CQWP you must first add your own xsl files.
NOTE: do NOT modify the existing files – they might get overwritten in an update OR you might write something totally wrong and nothing will work on your site. You always create a new folder called “Custom” (or whatever you want), then you COPY existing files and paste them into your folder and there you can modify them without taking a risk.

Easiest is to open your site in SharePoint Designer, browse to the folder Style Library > XSL Style sheets > Create your own folder “Custom”. Then copy the file called “ItemStyle.xsl” and paste into this new folder.

Now go to the site where you first add the CQWP and then export that same web part. You export a web part by clicking the arrow on the web part and then select “Export”:

Open the file, it might be called “News.webpart” or whatever you named the web part, and find the row with the property name “ItemXslLink” and change the path to the new that was created above:

Tag: property name=”ItemXslLink” type=”string”
New value: /Style Library/XSL Style Sheets/Custom/ItemStyle.xsl

It should look like this in the webpart file now:

Then add the fields that should be displayed in the web part, they need to be added in a special way and you must find out their GUID. Find the property tag called “DataMappingViewFields” and the fields must be added in this syntax: {GUID}, Type and then separated with ;

To find the GUID of the fields that should be displayed, go to the list where the columns are and hover each column or click on it and look in the address field. The URL will contain the GUID. Be sure to remove the ASCII for % and – since they are in the URL.
Example:
I want to get the field where Comments are, (which is a Lookup Field) so I go to my news blog and open the Posts list. Go to the List settings and scroll down to find the Column field. Click on it and copy the URL and then paste that into Notepad. You will get something like this:

Then in Notepad, copy only the GUID from the URL:

%7B6026020B%2D0EB4%2D42D0%2DAA51%2D2BBA3A3CD9DF%7D

And then replace all ascii characters with the normal character like

%7B which is {
%7D which is }
%2D which is –
and you will get

{6026020B-0EB4-42D0-AA51-2BBA3A3CD9DF}

Do this for all fields that you want to display in the web part, like Comments, Likes, Category etc

Before you upload the web part file, go back to SharePoint Designer and open the custom xsl file. Add a new xsl:template in the file like this (it is an image):

The entire code in this is here, but the < tags are exchanged to [] so that the code can be displayed here on the page (just replace the [] with the regular < tags):

[?xml version=”1.0″ encoding=”utf-8″ ?]

[xsl:stylesheet
  version=”1.0″
  exclude-result-prefixes=”x d xsl msxsl cmswrt”
  xmlns:x=”http://www.w3.org/2001/XMLSchema”
  xmlns:d=”http://schemas.microsoft.com/sharepoint/dsp”
  xmlns:cmswrt=”http://schemas.microsoft.com/WebParts/v3/Publishing/runtime”
    xmlns:ddwrt=”http://schemas.microsoft.com/WebParts/v2/DataView/runtime”
  xmlns:xsl=”http://www.w3.org/1999/XSL/Transform” xmlns:msxsl=”urn:schemas-microsoft-com:xslt”]
[xsl:param name=”ItemsHaveStreams”]
    [xsl:value-of select=”‘False'” /]
  [/xsl:param]
[xsl:variable name=”OnClickTargetAttribute” select=”string(‘javascript:this.target=”_blank”‘)” /]
[xsl:variable name=”ImageWidth” /]
[xsl:variable name=”ImageHeight” /]

[xsl:template name=”BigImage” match=”Row[@Style=’BigImage’]” mode=”itemstyle”]
[xsl:variable name=”SafeImageUrl”]
            [xsl:call-template name=”OuterTemplate.GetSafeStaticUrl”]
                [xsl:with-param name=”UrlColumnName” select=”‘ImageUrl'”/]
            [/xsl:call-template]
        [/xsl:variable]
[xsl:variable name=”SafeLinkUrl”]
            [xsl:call-template name=”OuterTemplate.GetSafeLink”]
                [xsl:with-param name=”UrlColumnName” select=”‘LinkUrl'”/]
            [/xsl:call-template]
        [/xsl:variable]
[xsl:variable name=”DisplayTitle”]
            [xsl:call-template name=”OuterTemplate.GetTitle”]
                [xsl:with-param name=”Title” select=”@Title”/]
                [xsl:with-param name=”UrlColumnName” select=”‘LinkUrl'”/]
            [/xsl:call-template]
        [/xsl:variable]      
     
[div id=”Item_LargePicture” style=”padding-bottom:10px”]

        [div class=”cbs-largePictureContainer” id=”_#= containerId =#_” data-displaytemplate=”ItemLargePicture”]
            [div class=”cbs-largePictureImageContainer” id=”_#= pictureContainerId =#_”]
                [xsl:if test=”string-length($SafeImageUrl) = 0″]
[a href=”{$SafeLinkUrl}”]
[img src=”/_layouts/15/images/custom/emptynews.png” class=”cbs-pictureImgLink” /]
[/a]
[/xsl:if]
[xsl:if test=”string-length($SafeImageUrl) != 0″]
[a href=”{$SafeLinkUrl}” class=”cbs-pictureImgLink” ]
[xsl:if test=”$ItemsHaveStreams = ‘True'”]
[xsl:attribute name=”onclick”]
                          [xsl:value-of select=”@OnClickForWebRendering”/]
                        [/xsl:attribute]
[/xsl:if]
[xsl:if test=”$ItemsHaveStreams != ‘True’ and @OpenInNewWindow = ‘True'”]
[xsl:attribute name=”onclick”]
                        [xsl:value-of disable-output-escaping=”yes” select=”$OnClickTargetAttribute”/]
                        [/xsl:attribute]
[/xsl:if]
[img src=”{$SafeImageUrl}?RenditionID=5″ title=”{@ImageUrlAltText}” class=”cbs-pictureImgLink” id=””/]
[/a]
[/xsl:if]

            [/div]
                     
            [div class=”cbs-largePictureDataOverlay”  id=”_#= dataContainerOverlayId =#_”][/div]
            [div class=”cbs-largePictureDataContainer” id=”_#= dataContainerId =#_”]
             
                [a class=”cbs-largePictureLine1Link”]

[h2 class=”cbs-largePictureLine1″]

[a href=”{$SafeLinkUrl}” title=”{@LinkToolTip}” class=”cbs-largePictureLine1Link”]
[xsl:if test=”$ItemsHaveStreams = ‘True'”]
[xsl:attribute name=”onclick”]
                  [xsl:value-of select=”@OnClickForWebRendering”/]
                    [/xsl:attribute]
[/xsl:if]
[xsl:if test=”$ItemsHaveStreams != ‘True’ and @OpenInNewWindow = ‘True'”]
[xsl:attribute name=”onclick”]
                      [xsl:value-of disable-output-escaping=”yes” select=”$OnClickTargetAttribute”/]
                    [/xsl:attribute]
[/xsl:if]
[xsl:value-of select=”$DisplayTitle”/]
[/a][/h2]

[/a]
           
                [span title=”_#= $htmlEncode(line4) =#_” id=”_#= line4Id =#_” ]
                [xsl:value-of select=”ddwrt:FormatDateTime(string(@PublishedDate) ,1053 ,’dd MMMM’)”/][br /]
                [xsl:value-of select=”@PostCategory”/][/span]
                [span title=”_#= $htmlEncode(line5) =#_” id=”_#= line5Id =#_”]
                [span style=”padding-left:180px;”][xsl:if test=”string-length(@LikesCount) = 0″] 0[/xsl:if]
[xsl:value-of select=”@LikesCount” /][span style=”padding-left:3px;vertical-align:text-bottom”][img src=”/_layouts/15/images/custom/like.png” border=”0″ /][/span][/span][span style=”padding-left:10px;”][xsl:value-of select=”@NumComments” /][span style=”padding-left:3px;vertical-align:text-bottom”][img src=”/_layouts/15/images/custom/comment.png” border=”0″ /][/span]
[/span]
[/span]            

            [/div]
        [/div]
    [/div]      

[/xsl:template]
[/xsl:stylesheet]

Okey so now we have a style for the news items. But there still are some things more to do.

Enable Image Rendition and Blob Cache to make the images render in the correct size, like thumbnails.

First upload this new web part into the sites WebParts gallery (its better to do that than to just upload it straight into the page, and you can also give it a proper name and put it in its own folder so its visible in the gallery when you add a new web part)

Then go to the start page and add this new web part. You have to modify it to point out the news blog location, and to select the correct display for it. And to make other settings that you need, like how many items that should be shown, what sorting order you want, if you want to add any filters etc.

In the web part settings, also go down to the section where the fields are mapped. Then add the names of the columns that you want to display, like PublishedDate, Category, Likes etc and you will find that there are new placeholders for these as we added those in the webpart file:

Yes it is in Swedish, but if you are familiar with CQWP (which I really hope you are, since this is quite complex to setup) then you should recognize the fields I am talking about. So, type in the name of the fields you want to display and DONE. But what? No, you cannot find a field for Comments? How surprising 😉 This is where we need to go to Central Administration on the server and a new Mapping property in the Search app.

Go to Central Admin, open the Search service app, click on the link “Search schema” in the left menu and there add a “New managed property”:

Call it what you want, like “NumberOfComments”. Then set it to be Searchable, Retrievable, and then scroll down to the mapping. Add a new mapping and find the field called “ows_NumComments” and then Save this. You MUST run a full crawl for this to apply.

Okey, that was it. Simple right?? 🙂 🙂

QuickTip: Enable Anonymous access in SharePoint 2013

When you want to publish a SharePoint site as a public website, you must enable anonymous access – otherwise the user will be prompted with a login. Note: This is for on premises servers and not the cloud/Office365.

Open Central Administration on your server. Go to the Application Management section and then click the “Manage web applications” link.

Select the web application where you have your public web site collection and then click the “Authentication providers” button available in the Ribbon. It will most likely show a “Default” link:

Click on the “Default” and then check the “Enable anonymous access” option and then Save.

Now open the site collection that will be used as public website and go to the Site Settings. Click on “People and Groups”. In the ribbon there is a button for “Anonymous Access” that allows you to configure how anonymous users can access to the site.

Click the “Anonymous Access” button in the Ribbon and find three options, select the one that is suitable for your site. In this case I would check the “Entire Web Site” to give the users full anonymous access to the site.

Now when you go back to the “People and Groups” page, there should be a new group called “Anonymous users” available in the list and it shows what permission they have on the site.

To test the anonymous web site, make sure you are signed out of the browser you are testing in, and see if you can access the site withouth having to sign in.

 

Change favicon in SharePoint 2013

This is a quick fix!
To change the icon that is displayed on the bookmark in a browser from the:

To something more sexy, then do the following:

Create a new folder in this location C:Program FilesCommon Filesmicrosoft sharedWeb Server Extensions15TEMPLATEIMAGES
I named mine “Custom”. This is always best practice, so that your changes wont be overwritten in any update.
Paste your new favicon file, dont forget to rename the file extension to .ico

Open your html version of the master page (if you have a custom)
Change the location inside the tag