29 June 2010

Honeymoon – Day 0-1

WOW! WiFi speeds in Italy ROCKS!!! Jess and I just landed in Rome.  We got to our hotel and I connected the netbook through their wifi network.  I was curious, so I did a quick speed test.  Here are the results:
Upload is about what you’d expect, but 5.5 Mb download via wifi from a small hotel on the coast of Italy?!  Are you kidding me?!!!  I don’t even get that kind of download speed via hardline from home! 
Anyway, we had a wonderful trip to the IND airport.  Chad and Lisa surprised us with a Lincoln Towncar to the airport!  You guys rock!  We had safe flight and enjoyed the transfer from Rome to Civitavecchia via a Mercedes Benz! There’s lots of those around here apparently. 
So here are my very fist impressions of Rome/Italy as I’ve experienced them…
  • Their roads are in AWESOME condition.  Unlike American roads that are cracked and full of pot holes, their roads are excellently maintained.
  • Streets are narrow.  To be expected in cities older than the US I guess. 
  • No SUVs.  That’s right.  Lots of small cars.  No big honkers.
  • Drivers tend to make use of ALL the road they have.  If there’s no traffic around, they’ll drive in the middle of the two lane road, right on the center line.
  • Highway exits are small and narrow.  If you don’t slow waaaay down for these bad boys, you’ll run over the edge for sure.
  • Most building roofs are made of clay tile.  I’m yet to see shingles anywhere.  I like clay tile more anyway.
Photo time…

OK, OK.  Enough of that.  Time for Jess and I to go take a walk on the beach and explore Civitavecchia bit.


28 June 2010

SQL Clustering or SQL Mirroring

One of my readers, Steve, posed a good question to me asking which option would be the preferred option between SQL Clustering and SQL Mirroring.
As with all technology decisions, there isn’t any one answer that fits all.  There is only a decision that’s the right decision for any given company with a given culture, given specific constraints at a precise moment in time.  When the culture, technology (hardware or software) or any constraints (like budgets, available skill sets etc.) changes, the original decision may not remain the right decision and the evaluation *should* be recalculated.  I say should because it’s not always practical to revisit technology decisions frequently.  That being said, how often have you posed the question “Why is it being done like this?” and received the answer “Because that’s how we’ve always done it.” or “Because we’ve done it that way for years.”?  Given the fast pace at which technology changes, every major technology decision should be accompanied by a clear timeline for Return on Investment (ROI).  I believe at the end of that timeline, plus 50%, the original decision should be re-evaluated.  If not enough has changed in the equation to warrant an altering of the original decision, then savings continue post ROI.  If however factors have shifted dramatically, then it should be time to re-evaluate.  Change is the only constant…
OK, OK.  I’ll get off my soap box now and reflect, based on my own personal experience, on Steve’s question.  It is a very good question indeed and I’m just going to reflect my abbreviated view here.
  1. Hardware cost.  Clustering has greater hardware cost due to the shared storage requirement of the cluster.  Clustering generally means SAN storage is involved.  If you don’t already have a SAN, or have plans and budget for a SAN, clustering will be an expensive option. 
    Mirror + 1
  2. Idle hardware.  I’ve never met a CFO who liked the idea of having a Extra hardware idle, giving no benefit to the organization, or should I say active benefit.  That’s usually the tunnel vision view of the bean counters.  With Mirroring, the passive node is just waiting for failover and while we as technologists know and understand that in itself, that is an important function, CFOs generally want to see more Active/Active use of their hardware.
    Cluster + 1
  3. Automatic failover.  Yes, both solutions provide automatic failover… sort of.  Clustering is true hardware level failover without requiring applications to do anything because the instance is virtualized.  Mirroring on the other hand requires application code to be using ADO.NET or SQL Native Client in order to do Client Redirect properly and create the automatic failover ability. 
    Cluster + 1
  4. Clustering doesn’t do load balancing or scaling.  You have to manually spread your SQL instances across the cluster nodes in order to load balance.  Peer-to-peer mirroring allows data access from any of the peers, resulting in better scaling. 
    Mirror + 1
  5. Update conflicts.  Mirroring doesn’t resolve update conflicts resulting from multi peer updates mentioned in #4, out of the box.  As a result, the benefit in scaling listed in #4 could be offset by having to deal with update conflicts. 
    Mirror – 1
  6. Expertise required.  Clustering is very complex and most DBAs avoid it if they can.  If you’re going to cluster, you have to have people with the skill set on hand to maintain it.  Mirroring on the other hand is a less complex topic that can be mastered by most DBAs.  Mirror + 1
    Mirror + 1
  7. Assuming per #3 that not all your applications are “Designed for Mirror”, security and update patching can become a problem when you’re running something like a 99.999% uptime SLA.  A cluster backup node can be patched and failover forced to the patched/backup node.  You can then patch the primary node and let the system run on the backup node until the next time patching needs to be done.  At that point in time, the patch process will fail the cluster back over to the primary node. 
    Cluster + 1
  8. Storage redundancy.  A Mirror has redundant storage.  A Cluster has shared storage.  True, SAN technology will have RAID redundancy built in, but at least a RAID5 redundancy design is used in all data storage, so it stands to reason that all members of a Mirror will have some form of redundancy anyway.  The question is how far apart your mirror members are.  It’s not likely that a Cluster will survive a complete Datacenter loss, but a Mirror with an off site member could. 
    Mirror + 1
As you can see, there are many pros and cons to each solution and what is the right solution for you, will depend on your given factors in the equation.
If you can afford both the hardware and the technical expertise involved in doing clustering, then it has been the tried and true method to use.  True, mirroring is making inroads on this title, but most DBAs will still opt for clustering if given the choice. From personal experience, I’ve encountered less problems with *PROPERLY* run SQL Clusters than with Mirrors.


24 June 2010

How do I – Install PowerPivot into an EXISTING SharePoint 2010 farm

This one was a BEAR to work through.  There’s not much out there for it and everywhere I turned it seems everyone else was either having the same problem OR suggestion we “start with a clean install”.  Nice suggestion that last one.  Almost like… try rebooting the computer… but I digress.  So hopefully this guide will help some other poor soul save some time when they run into the same issue.
  1. Due to a farm admin error during PowerPivot install that incorrectly claims that the current user is NOT a Farm Administrator, you’ll need to logon to your SharePoint server, using the SharePoint Admin Service account credentials.
  2. Startup the SQL Server 2008 R2 installer in Admin mode:
  3. Browse to the CD folder of the install media.
  4. Right click “Setup.exe”.
  5. On the popup menu, click “Run as administrator”.

  6. image_60_05C9C7A4
  7. On the SQL Server Installation Center menu, click “Installation”.

  8. image
  9. Click “New installation or add features to an existing installation”.

  10. image
  11. On the Setup Support Rules page, correct any issues that are identified and then click “OK”.

  12. image
  13. Now click “Install” to install the Setup file required.

  14. image
  15. On the Setup Support Rules page, Setup will run it’s support rules to check for valid install.  Correct any issues that are identified and then click “Next”.

  16. image
  17. On the Installation Type page, Setup will list already installed instances of SQL Server.  Here you want to select “New installation or add shared features”.  DO NOT select the “Add features…” option.
  18. Click “Next”.

  19. image
  20. On the Product Key page, select the “Enter the product key” radio button.
  21. Enter your SQL Server 2008 R2 product key that you received from Microsoft in the edit box.
  22. Click “Next”.

  23. image
  24. On the License Terms page, check the “I accept the license terms” checkbox.
  25. Click “Next”.

  26. image
  27. On the Setup Role, select the “SQL Server PowerPivot for SharePoint” radio button.  DO NOT select the SQL Server Feature Installation option.
  28. From the dropdown list box, select “Existing Farm”.
  29. Click “Next”.

  30. image
  31. On the Feature Selection page, Setup will show you the installation options to be added.  These cannot be changed.  Click “Next”.

  32. image
  33. On the Installation Rules page, Setup will run rules to check for blocks.  Correct any issues that are identified and then click “Next”.

  34. image
  35. On the Instance Configuration page, you cannot change the Named Instance name, but you can change the Instance ID, if you wish.
  36. Click “Next”.

  37. image
  38. On the Disk Space Requirements page, click “Next”.

  39. image
  40. On the Server Configuration page, Setup requires that you configure an account for SQL Server Analysis Services.  Best Practices dictates that this should NOT be a network service or system account, but rather it should be a dedicated domain account.
  41. Enter the domain account ID e.g. “DOMAIN\ServiceAccountName” in the Account Name edit box.
  42. Enter the domain password in the Password edit box.
  43. Leave the Startup Type as “Automatic”.
  44. Click “Next”.

  45. image
  46. On the Analysis Services Configuration page, Setup is asking you to identify the administrators of Analysis Services.
  47. Click “Add Current User”.
  48. The SharePoint Admin Service account should now be listed as an admin.
  49. Click “Add…” to add each account that needs to have Admin rights to Analysis Services and PowerPivot.
  50. Click “Next”.

  51. image
  52. On the Error Reporting page, click “Next”.

  53. image
  54. On the Installation Configuration Rules page, Setup will run some more rules.
  55. Resolve any identified issues and click “Next”.

  56. image
  57. On the Ready to Install page, STOP RIGHT HERE!!!
  58. If you click “Install” here, your installation WILL fail… but you already knew that. 😉  That’s why you’re reading this post. 😀
  59. HACK #1
  60. At the bottom of the window, Setup is displaying the path to the “ConfigurationFile.ini” file.  Copy the path to the file.  We have to pause the install here because the location of this .ini file changes on every install as Setup will take the current date/time stamp and use it for the …Log\<YYYYMMDD_HHMMDD>\… part of the folder location.

  61. image
  62. Open the “ConfigurationFile.ini” file with Notepad.
  63. Using Ctrl+F, locate the reference to “FARMADMINPORT”.

  64. image
  65. Your port number will show something random.  This is because PowerPivot assumes that its installing at the same time as a default SharePoint install.  Since we already have SharePoint installed, we need to edit this value and change it to match the port of our Central Admin location.
  66. Change the value between the quotes to match the port number of your current Central Admin.

  67. image
  68. Exit and save the .ini file.
  69. HACK #2
  70. The other problem with the current PowerPivot install is that it looks for the Microsoft.AnalaysisServices.SharePoint.Integration.dll file in the bootstrap folder, but it doesn’t exist there.  In past failed installs of PowerPivot, you may have encountered references to this DLL being “missing”.
  71. Copy Microsoft.AnalysisServices.SharePoint.Integration.dll to the PowerPivot bootstrap location of C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64
  72. You may, or may not have a copy of this DLL file handy.  If you don’t, you can download the copy I placed on my SkyDrive here:
  73. HACK #3
  74. Now we have to ensure that there are no remnants of this dll left over within the GAC.  Open Windows Explorer.
  75. Browse to “C:\Windows\assembly”.
  76. Scroll down and look for Microsoft.AnalysisServices.SharePoint.Integration.
  77. You should not find it because it’s not installed yet.  This was the case in my install as can be seen below.

  78. image
  79. That’s not necessarily true though as can be seen from my environment screen shot below:

  80. image
  81. Open a command shell via Start/Run/cmd
  82. Execute “cd\windows\assembly\gac_msil” from the command line.
  83. Execute “dir microsoft.analysisservices.s*” from the command line.
  84. Your output SHOULD show 0 files and 0 dirs.  If it doesn’t, like in my example above, the odds are you’ve had a failed install of AnalysisServices before and remnants were left behind when the installer didn’t cleanup behind itself.
  85. If there is a folder named “Microsoft.AnalysisServices.SharePoint.Integration”, you need to delete it by executing “rd  Microsoft.AnalysisServices.SharePoint.Integration” from the command line.
  86. Now that we’ve cleared the path for the installer by deleting remnants from the GAC, hacking the configuration file and manually providing a copy of the integration DLL, we can switch back to Setup and click “Install”.
  87. Setup should complete successfully this time around as in my example below:

  I hope this saves somebody some time and frustration out there.


23 June 2010

Step-by-Step – Installing the Microsoft Access Database Engine 2010 Redistributable

This is a prerequisite to Installing PowerPivot for SharePoint into an EXISTING SharePoint Farm.  PowerPivot seems to work just fine being installed together with a clean install of SharePoint, but when you already have SharePoint installed and try to add PowerPivot… all hell breaks loose.  I was never able to find proper documentation on how to pull this off and managed to hack and debug my way through it like Holmes unraveling a mystery.  Hopefully this will save someone else some time and frustration…
  1. Logon to your server with the SharePoint service account.
  2. Download and run the appropriate setup package for the target version (x64 or x86) of your farm http://go.microsoft.com/fwlink/?LinkId=180629
  3. Click “Run” to execute the install package.
  4. image
  5. On the Welcome screen, click “Next”.
  6. image
  7. Check the “I accept the terms of the License Agreement” check box and click “Next” to continue.
  8. image
  9. Select the folder location to which you wish to install and finally click “Install”.
  10. image
  11. The installation should complete successfully.
  12. image


21 June 2010

The node to be inserted is from a different document context

If you’re receiving this error, you’re probably trying to merge one XML node into another.  Your code could look like this:
XmlDocument xmlDoc = new XmlDocument();



XmlNode nodParent = xmlDoc.SelectSingleNode(strParentXPath);
XmlNode nodFragment = xes.GetXmlNode(strFragmentXml).FirstChild;


You could even be trying .Clone()
XmlDocument xmlDoc = new XmlDocument();
XmlNode nodParent = xmlDoc.SelectSingleNode(strParentXPath);
XmlNode nodFragment = xes.GetXmlNode(strFragmentXml).FirstChild;
But alas… the .ImportNode() is what is needed…
XmlDocument xmlDoc = new XmlDocument();
XmlNode nodParent = xmlDoc.SelectSingleNode(strParentXPath);
XmlNode nodFragment = xes.GetXmlNode(strFragmentXml).FirstChild;
nodParent.ImportNode(nodFragment.Clone(), true);


15 June 2010

How do I – Customize the NewForms.aspx or EditForm.aspx of my SharePoint list

Sometimes, it is necessary to override the default list edit forms provided by SharePoint. A common example is the “Title” column. In most cases, we design our lists to have some field that is a single line text field that we can map to the Title field. The reason that has to be done is because SharePoint throws the Title field into the list by default. This behavior cannot be changed, so we have to work around it.
In some cases, we don’t have any single line text fields in the list, and in such cases, the only thing we can do is to IGNORE the field. It’s pretty hard to ignore the field if we still see it though. This is when we would want to override the NewForm.aspx and EditForm.aspx pages.
Another example would be when we are designing a Support Request type of form. In such a case, the user filling out the form doesn’t care about all the tracking information we tie to the form, such as who it’s assigned to etc. All they care about is to give us a description of the error. A minimalist approach would have the user making the support request fill out just a Title and Description of the error.
If the form the user is filling out has lots of other fields in it, it can be confusing for the user. This is another good example of when we would want to override at least the NewForm.aspx page.
To override default forms and hide fields:
  1. Begin by launching SharePoint Designer.
  2. Using the File/Open Site menu, open the target site.
  3. As always, backup, Backup, BACKUP!!!
  4. Navigate to the target list.
  5. You should see the forms AllItems.aspx, DispForm.aspx, EditForm.aspx and NewForm.aspx in the listing.
  6. Right click the form you’re about to edit.
  7. On the popup menu, click “Copy”.
  8. clip_image002
  9. Now right click somewhere in the white space of the listing.
  10. On the popup menu, click “Paste”.
  11. clip_image004
  12. The forms list should now show a backup copy of the form we’re about to edit e.g. “NewForm_copy(1).aspx” if we backed up the NewForm.aspx.
  13. clip_image006
  14. Double click the NewForm.aspx to open it. It should look like this:
  15. clip_image008
  16. What we want to do, is insert a new “Custom List Form” in order to control the controls used on the page.
  17. Select the list form that is there by clicking on it. The entire thing should be highlighted, as above.
  18. VERY IMPORTANT!!! Do NOT miss this step. With the list form selected, press the RIGHT ARROW key on your keyboard, one time.
  19. This moves the cursor just to the right of the list form. It also unselects the list form. This is the exact position where we wish to insert our custom list form content.
  20. From the menu, select “Insert/SharePoint Controls/Custom List Form”.
  21. image
  22. In the modal window that pops up, change the “List or document library…” dropdown to target your current list.
  23. Leave the “Content type…” dropdown value as “Item”.
  24. Check the radio button for the type of form to insert. In our example, we’re overriding the NewForm.aspx so we select the first value.
  25. Click “OK” to insert the content.
  26. clip_image012
  27. Your page should now look like this with the standard list form content on top and the new custom list form content right below it:
  28. clip_image014
  29. Scrolling to the bottom of our list, we find the controls we wanted to remove from the NewForm.aspx.
  30. Select the entire row.
  31. clip_image016
  32. Right click on the selected row.
  33. On the popup menu, click “Delete/Delete Rows”.
  34. clip_image018
  35. The row and it’s controls should now be missing from view.
  36. clip_image020
  37. Now that we have the custom content ready, we need to hide the default content.
  39. Select the default list content by clicking on it.
  40. clip_image022
  41. Now click the “Split” view button below the preview window. This should show you both the preview AND the source code behind it. Note the <IsVisible> node value of “true”.
  42. image
  43. Double click on the <IsVisible> line to edit it.
  44. Change the value to “false”.
  45. image
  46. Click the “Save” icon to commit your changes.
  47. image
  48. If it’s the fist time editing the form, you will be prompted with this warning:
  49. clip_image030
  50. The warning basically just warns that the list is being customized, which we already know because that was our intention from the outset.
  51. Click “Yes” to continue.
  52. Now when you go to add a new item to the list, the new form we just designed, is used and the fields hidden.
  53. clip_image032


SharePoint Remote Event Receivers are DEAD!!!

 Well, the time has finally come.  It was evident when Microsoft started pushing everyone to WebHooks, but this FAQ and related announcement...