Pause a Nintex workflow for less than 5 minutes in SharePoint Server and Office 365

Last week I spent almost 2 days fixing a complex Nintex/SharePoint 2013 issue with one of our customers. The customer was not very big in terms of headcount, but they were using Nintex workflow to automate all sorts of processes(one of the biggest I have worked with).
There were Workflow timer jobs stucking/failing, workflows exiting with errors for no obvious reason and more. The issue was resolved with a couple of fixes and at least the workflows were executed when they should and were ending as they were designed to.
Some of the issues and improvement points I flagged were: not properly scaled Nintex deployment, incorrect service topology, outdated product versions and poor workflow design.
Now, the fourth (poor workflow design) was partially dictated by the inadequate scale of the deployment. They were using a lot Pause and Commit pending changes actions. Many of the workflows were designed to have two minute pause after the first couple of actions.
As maybe you know the pause action actually pauses the workflow instance for the defined period of time, but the workflow will not resume immediately, it will be resumed when the "Workflow" Timer Job is executed. The default schedule of this job is every 5 minutes. This means that you cannot pause a workflow for less than 5 minutes or pause it for exactly the time you have set. You can change the schedule of the Workflow timer job to workaround the first limitation, but this can put additional load on your system.
This is why I demonstrated an alternative of the Pause action that do not pause the workflow instance, but just waits a certain amount of time before continuing the execution. I have not seen this approach in other sources and this is why I decided to share and explain it in this post.
There is another alternative to pause a workflow for less than 5 minutes. It is described in this article.
As you can see this alternative requires "NTX PowerShell Action". This is great, but this action is open source, it is deployed with Farm solution and although developed and published by Nintex Employee this addon is not backed and supported by Nintex. The PowerShell action is fantastic, but in my opinion it is not worth to deploy it just to use it as Pause alternative. Also you cannot use it in Office 365(SharePoint Online).
The PowerShell example works by executing the powershell code that will just wait a certain amount of time, then it will continue the execution. Obviously to pause a workflow we need to do some sort of waiting. There is no out of the box action that just waits, as we know Pause action is not doing anything, but actually idling the instance execution at certain point and waits for the timer job to resume it after the time is elapsed. With the powershell example we use the powershell (.Net) framework to achieve wating without doing anything for certain time. The same thing can be achieved with T-SQL statement execution and in Nintex Workflow both On-Prem. and Office 365 we have "Execute SQL" action.
If we want to put a wait in our SQL query for two minutes we can use the code below:

---

WAITFOR DELAY '00:02'

---

I am not a SQL guy and was surprised to find out that this statement works outside of the SQL Management tools.
Below is the designer look of my demo workflow in SharePoint 2013. This should also work for SharePoint/Nintex 2010.

As you can see it is pretty simple just for PoC. Below is the configuration of the "Execute SQL" action.

I am using a connection string that is using "SQL" as server, this is alias to the SQL instance that hosts my SharePoint, I am using SSPI security with Windows credential that are actually my farm account saved as global constant.
Below are the details from the execution. You can see that Execute SQL actions took exactly 2 minutes to complete.

Unfortunately you cannot use this approach on-premise  for pauses longer than 5 minutes without doing a loop and in this loop execute multiple times delays that are less than 5 minutes. If you do set delay more than 5 minutes the workflow will fail with error "Error performing database operation. Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding." even if you set connection timeout in the connection string to be more than the 5 minute delay. I will do some more tests/research and might report this as bug.

As described in the MSDN documentation of the WAITFOR statement, it should work against Azure SQL Database. In Nintex workflow for Office 365 we also have Execute SQL action. I actually tested this and noticed two things, the connection timeout you specified in the connection string will be set to 365 if the number is bigger than that, also if you set a delay longer than 4 minutes you will get some unexpected http errors during the execution, the workflow manager will do a couple of retries and then it will fail. I think that both are issues with the Workflow Manager in SharePoint Online.
This is not so important because the Pause action in the SharePoint 2013 workflows (Workflow Manager) are not depending on SharePoint timer jobs and you will not get the same pause issues as in on-premise 2010 framework, but it is still an option. See example configuration of the action below.

My final words are that this might be extremely useful if you need to put some short pauses(not more than 5 min.) in your on-premise workflows. I hope you find this useful!

Capture SQL IO latencies for a period of time - The PowerShell Script

In this post I am going to share a PowerShell script that is not directly related to SharePoint, but can be a powerful tool for troubleshooting SharePoint performance issues.

Earlier this year I published an article called Test and Monitor your SQL storage performance and some good practices for SharePoint, in this article I showed some tools I use to troubleshoot SQL Server storage performance. One of the tools I mentioned there was a SQL script written by Paul Randal from SQLskills.com, it will let you capture the IO latencies for a period of time.

As with my previous post I wanted to transform the SQL script to PowerShell, so it can be used in more scenarios, get the result directly and present the result as objects in PowerShell.

I contacted Paul and he gave me permission to write and publish this script. Note that the original script(this one too) is copyrighted and SQLskills.com have all rights reserved. You can see the original copyright in Paul's post, in my script and in the TechNet Gallery post. Respect it!

Since we've said that, here are a few words about my powershell script. It is really simple, just issuing a T-SQL commands against the SQL instance. You do not have to be on the SQL server as long as you have connectivity and appropriate permissions. The script should work against SQL Server 2005 and newer. You can use Windows integrated authentication, where the identity of the account that is running the script will be used to connect to the sql or you can use SQL Authentication.

The output can be in powershell as System.Data.DataRow type or in CSV file that will be displayed in GridView at the end.

I think that this script is a good example and you can use it as reference to transform some of you SQL scripts to PowerShell. If you look at the original SQL code you will notice that it is one script and in my script I have 5 SQL commands. This is because 'GO' is not T-SQL statement and it will not work directly with the .Net SqlClient. This is why I am executing every batch as separate command.

You can see how the result looks and a download link below. For more information see the help section in the script, ask a question in the Q&A section in Technet Gallery and if you like it Rate it.

 Download the script from: TechNet Gallery

(You may experience issue with Chrome, use Firefox/IE to open the link)

Get the slowest request in SharePoint - The PowerShell script

In April this year I published a post called "Query Usage and Health Data collection database for slow requests execution". In this post I have explained the limitations of the "Slowest Pages" report page in SharePoint and provided a SQL query script that can overcome this limitations and you can receive a valuable information about the requests logged in the Usage and Health database.

After I created this script I have used it in multiple occasions and it turned out to be extremely useful! However, there is one big disadvantage(in my opinion) about this script, it is T-SQL script.

This is why I started to think how I can transform it into a PowerShell script, so I will not need to know which is the logging database, on which server it is, go to SSMS, copy to CSV and so on.

Now I might be wrong, but there is no SharePoint API that will help me to get such result in PowerShell. I also did some googling on the subject and I was unable to find anything remotely to what I was looking for, maybe there is some chance to get something like this in SharePoint 2010 where we have Web Analytics Service application.

If I am correct, this can be an idea for improvement in the SharePoint web analytics field, something that I think was announced to be better in SharePoint Server 2016.Usage and Health database captures a lot of useful information and it is a shame that SharePoint Admins cannot take advantage of it without being SQL master or BI guru. My solution was just to embed the SQL query in the script. 

In the script I put some sweet stuff like: Support for SQL Authentication, The output can be in PowerShell with type System.Data.DataRow and in CSV file/Grid View, you can pass the Start and End times as DateTime objects and many more.

If you run the script from SharePoint server the script will automatically determine the SQL server, the Usage and Health database, will do the connection to the SQL Server by impersonating the identity of the user that is running the script and you can filter by web application by passing Url,GUID or WebApplication object.

Of course you can successfully run the script when you are not on SharePoint or SQL server, you will just need to enter more parameters, use GUID for Web Application and have connectivity to the SQL server itself.

I have successfully tested the script with SharePoint 2010/SQL 2012, SharePoint 2013/SQL 2012 and SharePoint 2016 TP/SQL 2014. You can check the output and download the script below, if you have issues read the Help section, ask a question in the Q&A section in Technet Gallery and if you like it Rate it.

Finally I would like to have a minute for shameless self-promotion and say that last week I passed 3000 downloads mark in the Gallery and I received a Gold medal for my Gallery contributions. Thank you!

 Download the script from: TechNet Gallery

(You may experience issue with Chrome, use Firefox/IE to open the link)

Migrate all SharePoint databases to new SQL Server with minimum clicking and a few words about my new job!

Here we are, a new post after a long pause. I will try to change this in future and blog as much as I can. If you are not visiting my blog for first time I guess you have noticed that the branding is slightly different. The change is mainly in the bottom right where is located the logo of my current employer.

At the moment I am writing this post I am in my fourth week as Senior SharePoint Engineer for bluesource.I think that I made a good decision and this change is a step forward, something that I needed and wanted.

Bluesource is a great company, not very big(at the moment), but currently present on three continents Europe, Australia and North America. It has wide portfolio of technologies and services.As a Senior SharePoint Engineer I will be the main person involved in the ongoing SharePoint support of our customers and our internal SharePoint infrastructure.

I am working with many customers and this is a great opportunity to see different solutions, implementations, requirements and requests. I will support not just SharePoint 2013, but 2010 and Online. A lot of the customers are looking at fast,cheap and supportable solutions, this is why they are heavily using Nintex Workflow, Nintex Forms, InfoPath and more. I am especially excited about Nintex Workflow, this is the perfect tool/platform for developing  no-code, robust workflows for SharePoint.

This is why you can expect more diverse post for interesting and useful(I hope) real-world examples from my day-to-day job. 

One of my first tasks was to plan and execute the migration of our public/extranet SharePoint 2010 farm to new SQL Server instance as part of internal infrastructure restructuring.

As I mentioned this farm is hosting our public and extranet sites, so no downtime was allowed, this required the migration method to be database backup/restore instead of detach/attach. There are many resources how to do this, one of the best overviews of the process can be found in the posts of Thuan Soldier and Todd Klindt.

To be exact, stopping all SharePoint services is mentioned in both posts, this means complete outage, I did it only with complete content management change freeze and stopped Timer Services in order to prevent any timer job execution, and components that can write in the databases or in the local configuration cache. This is not the best way, but I and my stakeholders took the risk to do it that way and the migration went smoothly without content outage and issues.

To minimize the risk I had to do the backup and restore as fast as possible. I had to do backup/restore for up to 40 databases with combined size of 70 GB. Database backup and restore is a click intensive, repeatable operation and people tend to do mistakes when a lot of clicking and repeating is involved, computers can do better repeatable,boring things.

My solution to automate this process was a great powershell script from Chrissy LeMaire  (PowerShell MVP). I just cannot express how good that script is! Although I haven't tested it in all possible scenarios that can be used in, I think it will work great just how it worked in my migration.

The script is called Start-SqlServerMigration and you can check it out and download below.

Here are some of the features: support detach/attach and backup/restore for db migration, can use Windows and SQL authentication, migrate Windows and SQL logins(with correct SID), supports old SQL Server versions, migrate jobs and SQL server objects, sets the initial source DB owner, export/migrate SQL global configuration and many more. See some screenshots of backup/restore migration in my dev. environment.

A video from Chrissy:

                             Download the script from: TechNet Gallery

Query Usage and Health Data collection database for slow requests execution

Here is another article I am going to dedicate to the SharePoint performance monitoring.

For me one of the most useful out of the box tools to gather information for the farm performance is the Usage and Health data collection done by the Usage and Health Data Collection Service Application. This service application can be easily provisioned and set if you have installed your SharePoint farm with AutoSPInstaller.

I highly recommend to plan,provision and use this service application in your SharePoint farm!

If you have not done this yet, you can see how to provision the application here. I recommend to specify at least the logging database name in the New-SPUsageApplication  command, for further configuration see following  article, there you will find how to set up various properties like log location, what to monitor and the retention of the logs. Here is an official resource from TechNet.

Above I mentioned the logging database, this is the database used by the Usage and Health Data Collection Service Application to store all the logs. The logs are collected on each SharePoint server and stored in the log location you have set. This logs are then pushed into the logging database by a timer job called "Microsoft SharePoint Foundation Usage Data Import ", by default it is running every 5 minutes.

The logging database is the only database in SharePoint where running SQL queries is supported in order to analyze our logs. See some official guidance View data in the logging database in SharePoint 2013.

The easiest way to get some information from the logging database is from the CA interface described here. There you can see some statistics for the Slowest Page request and you can filter by Server,Web Application, maximum range of 100 rows and predefined timeframe by day, week, month, as you can see on the screenshot below.

We are getting essential information from this page, but in my case I needed to track some occasional slow request reported by the users. Here are some disadvantages of the result that we get from "View Health reports" page.

1. This is the major one, because it is limiting the amount of information we can receive. The results are aggregated averages for a certain request. We can have one slow request or we may have many. In this case we are going to receive Average, Minimum, Maximum values for Duration for example, we cannot see when a peak had occurred, the requests are grouped.

2. The columns we can see is predefined. This is again because of the request grouping. If you do a simple Select Top 100 for example from one of the dbo.RequestUsage (this is the table where the requests are logged) partitions you will see that we have quite a lot of columns and some of them can be really useful for our troubleshooting process.

3. The timeframes are predefined, the result is limited by 100.

This is why I created a SQL query that will output all request for a certain timeframe that is configured by you, it can be less than a second or days. The output will show you all requests in that timeframe, they will not be grouped, you can add additional conditions and columns to be displayed and the most important, you can see the time that the request was logged. I find this extremely helpful for troubleshooting, you can narrow down the results for a very small timeframe, you can filter by user login for example and track the request in the ULS logs by Correlation Id. See how it looks from the screenshot below and you can compare it with the result from the Slowest Pages report.

[*UPDATE*]: I have published a PowerShell version you can find it and read more HERE

The screenshot above is an older version, in the current version that you can download below I have included filtering by UserLogin. As I mention you can modify the script to show you whatever column  you want (include them in SELECT statement and the GROUP BY clause) and include additional filters. I have put example values for some of the variables, many of them can be Null and some useful comments as well.

A common use case will be if you receive a notification that the SharePoint was "slow" for a certain user in a certain time. You can use this script and it will show you all requests for this user, this information will help you in your investigation of potential performance issue.You can then export this result in CSV and play with it in Excel for example.

Keep in mind that this query can put some load on your SQL server, the timing is in UTC and the duration is in seconds.You should run the script against your Usage and Health (logging) database. The script is tested on SharePoint 2013/SQL 2012.

 Download the script from: Technet Gallery

(You may experien issue with Chrome, use Firefox/IE to open the link)

Test and Monitor your SQL storage performance and some good practices for SharePoint.

In the last couple of weeks I've been busy with analyzing and optimising one of our customer SharePoint 2013 deployment. This is a huge topic to discuss, SharePoint itself is not a slow application. However, there might be countless reasons that can make your SharePoint work and feel slow. In my case the source of the performance issues was the SQL.
You should pay as much attention to your SQL when you plan and deploy your SharePoint solution, as other components. The SQL server is referred as  the storage tier for SharePoint. Except the binaries and the static application files SharePoint stores almost everything(94% to be accurate) in the SQL, after all SharePoint farm is just a Configuration database and all your user content.sites,webs are stored in content databases.
Now, I am not a SQL Server DBA nor a storage expert, but for me the storage that hosts you databases is one of the most important components for the performance of your SQL. If your RAM is enough for your workload,you have enough CPU power or you have followed all good practices for SQL that hosts SharePoint, but if your storage needs more time accessing and delivering certain data(latency) or the debit (IOPS) is not small enough or big enough the SQL will work slow, therefore your SharePoint will work and feel slow.
In this post I am going to give you links and short description to articles and tools(free/open source) you can use for testing and monitor your SQL storage at the planning phase or as it was in my case after the system was deployed.
I mentioned the good practices for SQL that hosts SharePoint. Well if you do not know what this practices are you can learn about them from the following resources:

    1. Tuning SQL Server 2012 for SharePoint 2013 - This are MVA videos hosted by Bill Baer(Senior Technical Product Manager) and Brian Alderman. In this demo-rich videos they will cover some of the best practices for integrating SQL Server 2012 and SharePoint 2013

    2. Maximizing SQL 2012 Performance for SharePoint 2013 Whitepaper - This is a great whitepaper for the good practices that can maximize your SQL Server performance for SharePoint. The author is Vlad Catrinescu (SharePoint MVP) that covers most of the points from the videos above, plus many additional.

I had to implement lots of this practices before going to investigate any storage bottlenecks.

Now I would like to bold on one thing that has became more important with the development of the Cloud services. Distribute your database files on as many disks as possible. Every cloud provider I've been working with limits the VHDs by IOPS. For example in Azure class A and D VMs you have 500 IOPS per disk. However with the different VM sizes you can attach different number of disks. With A4 for example you can attach up to 16 data disks and that will make 16x500 IOPS. So why not attach as many disk as possible if it will not affect your budget(most of the providers charge storage per GB)?

One of the first thing to check if you are experiencing SharePoint slowness and you are suspecting slow SQL is the "Developer Dashboard". There you can find the SQL execution time and many more useful stats for your request execution.

You can find information how to enable and use the dashboard for SharePoint 2013 in following Article.

At the planning phase of your solution, before you have installed anything you can use various tools to test the performance of your storage, here are some of them:

     1. SQLIO - In this article from Brent Ozar you will find download link for the tool and how to use it. The tool is really straightforward and can show you basic performance information for your storage, IO/S, MBs/S and the latency from your output. In this article you will also find links for other tools like SQLIOSim that can simulate SQL Server OLTP and OLAP.

     2. DiskSpd - Now this is more robust tool for testing your storage. With this tool you can test local disks and SMB shares as well. This is a great article from Jose Barreto (member of the File Server team at Microsoft). There you will find everything about DiskSpd. The tool also comes with very comprehensive documentation. However this is a powerful tool  and you should not run it against storage with live data because it can destroy it.

Tools that you can use to monitor after system deployment:

     1. PAL  - This is a great universal tool for Performance Log Analysis. It is written by Clint Huffman (PFE at Microsoft). It is a tool not just for SQL, but for number of products. If you do not know how to interpret the performance logs for certain Microsoft product or you need aggregated performance data report , the PAL is just the tool for you. PAL is working with templates for the different products. If you do not know what PerfMon counters to capture you can export the PAL template to PerfMon template and then you can create regular Data Collector Set in the Performance Monitor MMC. You can see an example for PAL HTML report below.

     2. SSMS Activity Monitor - This is a great tool that you can launch in the SQL Server Management Studio. Just open SSMS right click on the instance and open Activity Monitor. There you will find current stats for Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries. If you do not know how the Activity Monitor looks like you can see it below:

     3. Triage Wait Stats in SQL Server -  Here is another article from Brent Ozar where you can find an explanation of what Resource Waits are, a script that can capture stats for a period of time and references to other articles on the subject. For me the Waits are very important thing to look at when it comes to SQL performance in general.

     4. Capturing IO latencies for a period of time - This article is last but not least. It is written by Paul Randal from www.sqlskills.com. There you can find a script that for me is the ultimate tool to identify some storage bottlenecks. Because it is not mentioned in the article you can set the time period with changing WAITFOR DELAY. In the output you can see the average latency (Read/Write) per file Average Bytes for Read/Write operations and more. You can see example output below.

And here are the final notes. SharePoint is application where we cannot edit some DB schema for example. It is enterprise product, not some inhouse build. If you have followed above mentioned Best Practices, you have correctly scaled your deployment, you are sure that there is no customisation that can cause issues, but you suddenly receive performance hit most, probably it is caused by other systems that are more likely to change like storage,network, Windows etc.. And of course there is no magic number or silver bullet to fix all issues.
If you are just SharePoint Administrator and deep diving in SQL and storage is not your responsibility, notify and push the SQL and Storage guys to do their work and fix the issues. If you are not sure with above mentioned tools, test them in non-production environment first. As far as I understand T-SQL the above scripts do not touch any user database.

I am hoping this was useful for you.