Version History in SharePoint via SQL

Posted by Keith Tuomi on February 21, 2012 in Nintex, SharePoint 2010, SQL | 0 comments

Recently I posted about how to get check-in comments with Nintex via MS SQL – turns out there was a bit more complexity involved in the structure of the version history then first thought (surprise surprise). Below is the stored procedure created to reliably extract the highest MAJOR version of a SharePoint document. So, if a document is currently v5.4 in your SharePoint library, this will grab the 5.0 version:

USE [MySharePoint_Content_DB]
GO
/****** Object:  StoredProcedure [dbo].[proc_GetDocVersion]    Script Date: 02/17/2012 13:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_GetDocVersion](
@LeafName nvarchar(260)
)
AS
SET NOCOUNT ON

SELECT TOP 1 x.UIVersion FROM (

SELECT
AllDocVersions.UIVersion
AS UIVersion
FROM
AllDocVersions
JOIN AllDocs ON AllDocs.[ID]= AllDocVersions.[ID]
WHERE
AllDocs.LeafName = @LeafName
AND ((CONVERT([nvarchar],AllDocVersions.UIVersion/(512),0)+'.')+ CONVERT([nvarchar],AllDocVersions.UIVersion%(512),0)) LIKE '%.0'
UNION ALL
SELECT
AllDocs.UIVersion
As UIVersion
FROM
AllDocs
WHERE
AllDocs.LeafName = @LeafName
AND ((CONVERT([nvarchar],AllDocs.UIVersion/(512),0)+'.')+ CONVERT([nvarchar],AllDocs.UIVersion%(512),0)) LIKE '%.0'

) x
ORDER BY UIVersion Desc;

Accessing the SQL DB in SharePoint 2010 directly (as opposed to using the SharePoint API’s etc.) is generally considered a cowboy maneuver and can get in you in lot’s of trouble with inconsistent results as well as performance hits. Use this SQL at your own risk, if not as just a means to better understand the plumbing that goes on in the basement of SharePoint.

Additionally, note that if you are accessing version history via the /vti_history// method, there are some major caveats as described in the following (note it’s referring to SP 2007 which uses single-digit version numbers but the description of the potential run-on situation still applies): http://blogs.msdn.com/b/roberthorvick/archive/2007/01/04/wss-rant-linking-to-the-latest-version-of-a-sharepoint-document-considered-harmful-lessons-6-and-7.aspx

Business Hours in Nintex Workflows

Posted by Keith Tuomi on February 16, 2012 in Nintex, SharePoint 2010, Workflow | 0 comments

Create a List View with PowerShell

Posted by Keith Tuomi on February 14, 2012 in Powershell, SharePoint 2010 | 0 comments

1. Grab the GUID of the List. Simple way to do it is to go your existing List you want to create the view in, click the “Create View” button (but don’t actually create it in the UI) and copy the value of the ListID querystring variable. You then need to clean it up a bit. For example, take the bold portion of the following URL:

/site/_layouts/ViewType.aspx?List=<strong>%7B8DF8879E%2D5046%2D406D%2DBCBD%2D50C4777AF50D%7D</strong>&Source=http%3A%2F%2Fmy%2Eitgroove%2Enet%2Fsite%2FMyList%2FForms%2FAllItems%2Easpx

Take the extracted string and delete the portions in red, which are the first and last three characters (which are the HTML encoded characters { and } respectively), and replace the instances of %2D in green (HTML encoded characters to indicate a blank space) with a single blank space .

%7B8DF8879E%2D5046%2D406D%2DBCBD%2D50C4777AF50D%7D

So we are left with the string 8DF8879E 5046 BCBD 50C4777AF50D , which is a properly formatted GUID and is your List ID.

2. Plunk the following script into a new text file called CreateListView.ps1 and put it on your SharePoint server:

[System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=1"4.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c") | out-null
# reading command line arguments
$siteURL = $args[0]
$strViewName = $args[1]
$ListGUID = $args[2]
# enter your CAML query for the view here...
$strQuery = "0"
# create a new SPsite object and recursively go through all webs
# until a matching list GUID is found
$site=new-object Microsoft.SharePoint.SPSite($siteURL)
foreach ($web in $site.AllWebs)
{
foreach ($list in $web.Lists)
{
$ListTempGUID = $list.ID.ToString()
if ($ListTempGUID.Contains($ListGUID))
{
write-host "************"
write-host "Match has been found. Preparing to create a view: ", $strViewName
write-host "List Title: ", $list.Title
write-host "List GUID: ", $list.ID
$fields = $list.Views["All Items"].ViewFields.ToStringCollection()
$result = $list.Views.Add($strViewName, $fields, $strQuery, 100, $True, $False , "HTML", $False)
write-host "View ", $strViewName , " was created successfully."
break
}
}
}
write-host "Done."
$site.Dispose();

3. To execute the PowerShell, go to your PowerShell command line and execute the following. Note that you need to replace the following sections in green:

- “your_site_collection_URL” with the URL of your site collection e.g. “http://my.itgroove.net/sitecollectionname”
- the “TestView” with your new list name
- the ListID GUID with the List GUID we just extracted

powershell CreateListView.ps1 “your_site_collection_URL” “TestView” “List GUID 8DF8879E 5046 406D BCBD 50C4777AF50D“

Fix: SharePoint Designer 2007 after SharePoint Designer 2010

Posted by Keith Tuomi on February 14, 2012 in SharePoint 2010 | 0 comments

I posted on http://yalla.itgroove.net/2012/02/install-sp-designer-2007-after-sp-designer-2010/ about my woes after I made the ill-informed decision to install SP Designer 2007 AFTER SP Designer 2010 – quickly trying to get it in there to work on a 2007 client project caused much shenanigans.

There were lots of nasty side effects including not being able to use the “Open in SharePoint Designer” links in SharePoint.

Solution: Run the Office 2010 repair routine -

• Open Control Panel and click Programs and Features.
• Right click the Office 2010 program that you want to repair, and select Change.
• Next click Repair > Continue.
• Restart your computer.

So far, so good, SP 2010 Designer behaviour is back to normal.

SharePoint SQL Server Performance Tuning Roundup

Posted by Keith Tuomi on February 13, 2012 in SharePoint 2010, SQL | 0 comments

On my mission to take what I know about SQL Server performance tuning and expand it into to the world of SharePoint, i’ve come across many docs and blog posts that, while helpful for symptomatic isolation, miss promoting a basic healthy lifestyle for the poor SQL servers that get hammered by SharePoints DB-centric usage profile.

Frequently you read about how to mitigate aggravating factors that can come up with SharePoints relationship to SQL Server, but it’s harder to find info that addresses the root causations that lead to the “problems” in the first place. “Get faster disks” or “buy more RAM” is a response to an architecture issue akin to telling the Dutch boy to grow a bigger finger in response to a widening dam leak. Let’s look at what causes SQL issues to overflow in the first place.

To troubleshoot performance issues, you must complete a series of steps to isolate and determine the cause of the problem. Possible causes include:

• Blocking
• System resource contention
• Application design problems
• Queries or stored procedures that have long execution times

Apply Filegroups for the Search DB’s

The whole goal of using filegroups is to improve the performance of the system. This is done by providing an additional file. This file must be placed on a different set of spindles to see any kind of performance enhancement. If your SQL machine is not IO bound for the Search database then implementing filegroups will not provide you with any benefits.

http://blogs.msdn.com/b/enterprisesearch/archive/2008/09/16/sql-file-groups-and-search.aspx

Configure Blob Cache in the SharePoint web.config

The BLOB cache is disk-based caching that increases browser performance and reduces database loads. When you open a web page for first time, the files will be copied from the database to the cache on the hard drive on SharePoint server and then all subsequent requests to this site will be accessed from the local hard drive cache instead of issuing a resource intensive request to the SQL Server database.
“enable” attribute to “true”. It is strongly recommended to store the cache on a dedicated partition, which isn’t a part of the operating system (C: partition is not recommended).]

Manage Index Fragmentation

As data is modified in a system, pages can split, and data can become fragmented or physically scattered on the hard disk. Contrary to popular belief, Microsoft SQL Server is not a self-healing system. Use the DBCC SHOWCONTIG command to see the density and the degree of fragmentation for an index for a table. The SQL Fool Index Defrag Script (http://sqlfool.com/2011/06/index-defrag-script-v4-1/) is a great tool for dealing with SQL fragementation.

Locate Logs and the Tempdb Database on Separate Devices from the Data

You can improve performance by locating your database logs and the tempdb database on physical disk arrays or devices that are separate from the main data device. Because data modifications are written to the log and to the database, and to the tempdb database if temp tables are used, having three different locations on different disk controllers provides significant benefits.

Provide Separate Devices for Heavily Accessed Tables and Indexes

If you have an I/O bottleneck on specific tables or indexes, try putting the tables or indexes in their own file group on a separate physical disk array or device to alleviate the performance bottleneck.

Pre-Grow Databases and Logs to Avoid Automatic Growth and Fragmentation Performance Impact

If you have enabled automatic growth, ensure that you are using the proper automatic growth option. You can grow database size by percent or by fixed size. Avoid frequent changes to the database sizes. If you are importing large amounts of data that tend to be of a fixed size on a weekly basis, grow the database by a fixed size to accommodate the new data.

When an index is created or rebuilt, the fill factor value determines the percentage of space on each leaf level page to be filled with data, therefore reserving a percentage of free space for future growth. Based on past performance and index expansion rates, the SharePoint Operations team reccommends the database fill factor to 70 percent on all content databases.

Maximize Available Memory

Use performance counters to decide the amount of memory that you need. Some performance counters that you can use to measure your need for memory are listed below:

• The SQLServer:Buffer Manager:Buffer cache hit ratio counter indicates that data is retrieved from memory cache. The number should be around 90. A lower value indicates that SQL Server requires more memory.
• The Memory:Available Bytes counter shows the amount of RAM that is available. Low memory availability is a problem if the counter shows that 10 megabytes (MB) of memory or less is available.
• The SQLServer:Buffer Manager: Free pages counter should not have a sustained value of 4 or less for more than two seconds. When there are no free pages in the buffer pool, the memory requirements of your SQL Server may have become so intense that the lazy writer or the check pointing process is unable to keep up. Typical signs of buffer pool pressure are a higher than normal number of lazy writes per second or a higher number of checkpoint pages per second as SQL Server attempts to empty the procedure and the data cache to get enough free memory to service the incoming query plan executions. This is an effective detection mechanism that indicates that your procedure or data cache is starved for memory. Either increase the RAM that is allocated to SQL Server, or locate the large number of hashes or sorts that may be occurring.

Install the latest BIOS, storage area network (SAN) drivers, network adapter firmware and network adapter drivers

Hardware manufacturers regularly release BIOS, firmware, and driver updates that can improve performance and availability for the associated hardware. Visit the hardware manufacturer’s Web site to download and apply updates for the following hardware components on each computer in the BizTalk Server environment:

• BIOS updates
• SAN drivers (if using a SAN)
• NIC firmware
• NIC driver

Disable hyper-threading

Hyper-threading should be turned off for SQL Server computers because applications that can cause high levels of contention (such as SharePoint) may cause decreased performance in a hyper-threaded environment on a SQL Server computer.

Defragment all disks on a regular basis

Excessive disk fragmentation in the SQL Server will negatively affect performance. Defragment all disks (local and SAN/NAS) on a regular basis by scheduling off-hours disk defragmentation. Defragment the Windows PageFile and pre-allocate the Master File Tables of each disk in the BizTalk Server environment to boost overall system performance.
Use the PageDefrag Utility (http://go.microsoft.com/fwlink/?LinkId=108976) to defragment the Windows PageFile and pre-allocate the Master File Tables.

Synchronize Time on All Servers

Many operations involving tickets, receipts and logging rely on the local system clock being accurate. This is especially true in a distributed environment, where time discrepancies between systems may cause logs to be out of sync or tickets issued by one system to be rejected by another as expired or not yet valid.

For more information on configuring a server to automatically synchronize time, see Configure a client computer for automatic domain time synchronization (http://go.microsoft.com/fwlink/?LinkId=99420).

Disable real-time scanning of data and transaction files

Real-time scanning of the SQL Server data and transaction files (.mdf, .ndf, .ldf, .mdb) can increase disk I/O contention and reduce SQL Server performance.

Review disk controller stripe size and volume allocation units

When configuring drive arrays and logical drives within your hardware drive controller, ensure you match the controller stripe size with the allocation unit size that the volumes will be formatted with. This will ensure disk read and write performance is optimal and offer better overall server performance. Configuring larger allocation unit (or cluster or block) sizes will cause disk space to be used less efficiently, but will also provide higher disk I/O performance as the disk head can read in more data during each read activity.
To determine the optimal setting to configure the controller and format the disks with, you should determine the average disk transfer size on the disk subsystem of a server with similar file system characteristics. Use the Windows Performance Monitor tool to monitor the Logical Disk object counters of Avg. Disk Bytes/Read and Avg. Disk Bytes/Write over a period of normal activity to help determine the best value to use.

Although smaller allocation unit sizes may be warranted if the system will be accessing many small files or records, an allocation unit size of 64 KB delivers sound performance and I/O throughput under most circumstances. Improvements in performance with tuned allocation unit sizes can be particularly noted when disk load increases.

Monitor drive space utilization

The less data a disk has on it, the faster it will operate. This is because on a well-defragmented drive, data is written as close to the outer edge of the disk as possible, as this is where the disk spins the fastest and yields the best performance.

Disk seek time is normally considerably longer than read or write activities. As noted above, data is initially written to the outside edge of a disk. As demand for disk storage increases and free space reduces, data is written closer to the center of the disk. Disk seek time is increased in locating the data as the head moves away from the edge, and when found, it takes longer to read, hindering disk I/O performance.

This means that monitoring disk space utilization is important not just for capacity reasons but for performance also.
As a rule of thumb, work towards a goal of keeping disk free space between 20% to 25% of total disk space. If free disk space drops below this threshold, then disk I/O performance will be negatively impacted

IE=Edge in SP 2010

Posted by Keith Tuomi on February 9, 2012 in Development, Errors, Master Page, SharePoint Designer | 0 comments

We have experimented quite a bit with having IE9 Compatibility mode turned on in custom SharePoint 2010 Branding.

Out of the Box SP 2010 Masterpages come with the following tag:

<meta http-equiv="X-UA-Compatible" content="IE=8"/>

You may try changing it to IE=9 to get some of the goodies but will quickly notice things blow up and there is simply no choice for the time being except to remain in IE=8 mode.
Recently we tried IE=Edge, which indicates “hey, use the latest IE version”. I guess this could save you some maintenance in the future in some scenarios but really seems like it’s prone to just cause future headaches. If your code outlives IE version iterations you will probably have plenty of scope to evaluate browser compatibilty once in a while.

A particular symptom of what will break with IE=Edge / IE=9, is that the SharePoint user picker UI will not work for multi user selection. That would classify as a showstopper:

Install SP Designer 2007 after SP Designer 2010

Posted by Keith Tuomi on February 5, 2012 in Development, SharePoint Designer | 0 comments

Don’t!  Microsoft tries to warn you in the install.. if you are a cowboy and just want to get SharePoint Designer 2007 installed to accomplish a specific task requiring it, don’t install it on the same workstation as SP Designer 2010. Put it on another VM, do the task at home, wherever – anywhere but the same box as your dev SP Designer 2010 install.

Short of some registry hacking it looks like although SharePoint Designer 2010 will function in it’s base form after uninstalling 2007, if you try it open a site from the SP Site Actions menu it will fail to make the association and prompts you instead to “download SharePoint Designer”:

 
Full uninstall of both SP Designer 2007 and SP Designer 2010, resintall 2010. Same result: your SP Designer will have little birds circling it’s head and X’s for eyes.

I’m sure the answer is out there somewhere on how to restore the association between the site actions “Open in SharePoint Designer” link, and I will post here when I find it. In the meantime, all indications continue to it being wise not crossing the streams with 2007 & 2010 Designer!

Get Check-in Comments with Nintex

Posted by Keith Tuomi on January 23, 2012 in Best Practices, Nintex, SharePoint 2010, SharePoint Enterprise, SharePoint Foundation, SharePoint Standard | 0 comments

NOTE: I’ve posted a revised version of this type of method for getting version history. The SQL in this post is not to be trusted! (but the rest is still good so I’ll leave the post as-is for posterity).

I needed to grab the document check-in comment insides of a Nintex 2010 / SharePoint 2010 list workflow. I couldn’t seem to locate the field anywhere Was told by Nintex support the following:

“It is not possible to reference the version history of a document within a workflow similar to how you can reference standard SharePoint fields. Even the web service method GetVersionCollection: http://msdn.microsoft.com/en-us/library/lists.lists.getversioncollection(v=office.12).aspx only returns the version history for a specific field. There may be another web service Microsoft exposes to enable this however this would be a question directly for MS.”

Oh well, right? Never say die! Use the Execute SQL Query widget in Nintex and let’s do some data mining:

1. Add an Execute SQL Query action to wherever in your Nintex workflow you need to access the Check-in comment.

2. Create a variable to store the check-in comment, in this case I call it vCheckInComment.

3. Add your SQL connection string. Considering that using Windows Authentication is probably what you’re going to be dealing with when using SharePoint, you will need the DB service account credentials.

4. Create your SQL query. For getting the check-in comments it is simple : In your SharePoint Content database there will be a table called AllDocs which contains two columns of interest: LeafName and CheckInComment.  You Can also grab the ListID GUID in there if needed – in this example I am already inside a Query List / For Each loop which has filtered the records based on the current list I am working with. The LeafName column stores the document e.g. MyDocument.doc. The CheckInComment column stores the comment for that file. In this example the {WorkflowVariable:vDocumentName} Nintex variable is my document name variable:

And that’s that – the check-in comment query associated with that documents record goes in the vCheckInComment column.

Disclaimer: there’s much more to the DB structure then just the document name and check-in comment. Versioning isn’t this simple. Also, running ad-hoc SQL queries on SharePoint is generally not best practice and you will get chewed out by MVP’s.  Reasons why ad-hoc SQL is bad news (see http://www.sharepoint4arabs.com/AymanElHattab/Lists/Posts/Post.aspx?ID=99 for a good summary)

1. This is completely unsupported by the EULA you agreed to when you installed SharePoint.
2. Your queries are not guaranteed to work after applying any patches or service packs to SharePoint since Microsoft could change the database schema anytime.
3. Directly querying the database can place extra load on a server and hence performance issues.
4. Direct SELECT statements against the database take shared read locks at the default transaction level so your custom queries might cause deadlocks and hence stability issues.
5. Your custom queries might lead to incorrect data being retrieved.

Definitely run your SQL queries with NoLock on and keep it to simple, read-only queries if you’re going to pull stunts like running SQL queries from Nintex as described in this post. Also carefully consider versioning and data commit issues inherent from the SharePoint architecture side of things before counting on any of the results you pull out as gospel.

Central Administration typo

Posted by Keith Tuomi on January 13, 2012 in SharePoint 2010 | 2 comments

Looks like Microsoft “sanboxed” their spellchecker somewhere in the SP 2010 dev process:

Central Admin > Sandboxed Solution Management

Either that or their spellchecker did not go through “activiation” :
http://www.microsoft.com/learning/en/us/exam.aspx?id=70-573#tab2

Yeah , i’m anal.. but when programmers make typos things, blow up. When marketers make typos nothing happens except a general dumbing down. Let’s hope Microsoft can run down some of this content to make it more befitting the professionalism of the enterprise-scale software it’s associated with.

SharePoint Designer 2007 & 2010 removing closing tags

Posted by Keith Tuomi on January 11, 2012 in Development, SharePoint Designer | 0 comments

Problem:
You put an <h2> or other element tag around masterpage or page layout content in SharePoint Designer 2007 or 2010.

Everything is great. You can’t believe your geeky HTML skills are still getting things done. Check in the file, approve.. BLAM you look at the page and everything is messed up! What the.. go back to Designer, check your handiwork, and you see some glaring yellow highlighting indicating that suddenly, your H1 tag or whatnot no longer has a closing element – If you HAD put <h2>..content..</h2>, the CLOSING TAG is now evaporated through sheer lunacy. Or maybe it’s following XHTML standards. Who knows – all i know is when I put in a closing tag it damn well should stay there or at least warn me that I made a boo boo:

Solution:
Add an extra closing tag. So, in our example, we tried adding an <h2> </h2>tag around the ContentSection1Title tag. Designer promptly ate the closing </h2> tag. So, just add </h2></h2> at the end. Ignore SharePoint Designer highlighting the duplicate closing tag as erroneous: as soon as you press Ctrl-S and save the file it will remove one closing tag but leave the other:

There you go, hacking SharePoint Designer for stress-free WYSIWG.