Migrating MOSS to BPOS (Microsoft SharePoint Online) –Part I

MOSS 2007 is also known as Microsoft Office SharePoint Server, and BPOS is also known as Business Productivity Online Suite, but more specifically Microsoft SharePoint Online since BPOS is actually more than just SharePoint. Every since the beginning Microsoft has never really known how to market / name SharePoint, and they still don’t IMHO, but that isn’t the point of this blog.

When you start Googling around you will most likely come across Metalogix tools. The two most notable are BPOS Commander and SharePoint Migration Manager. I have tried to get pricing or a demo on both of these products and the company has been 100% non-responsive other than an automated email that says an account rep will contact me shortly. It has been a while now, and no response, so I tried again. Still no response. I expect the product is priced like Oracle… “how much money do you have to spend with us” is the answer you will get when you read between the lines. With that said, the tooling looks WONDERFUL and should make the task much simpler than many other ways.

The big issues to consider are

• Mapping Users from current domain to BPOS users.
• Cleaning up users that are no longer with company
• Keeping Permissions
• Domain Groups and SharePoint groups
• User account activation since they are not active initially
• Hard coded Urls in things such as Wikis, Announcements, Tasks, HTML Content Areas, etc.
• Changing Site Hierarchy
• Identifying Owners of sites… Abandoned sites, SPS 2003 migrated sites so no Owner groups in some cases, etc
• Determining what sites are still used
• Converting custom Site Definitions that are basically the same as original Site Definitions to a standard Site Definition that BPOS will accept.

 

Given that I have not heard back from Metalogix and I don’t even know if we will have a budget for such a tool, I decided to start looking elsewhere. This brought me to the SharePoint Web Services 3.0 SDK which is a Windows Help file. It is much easier to use than the online version in my opinion. Though if you want just the docs for each of the web services, it is pretty good. Though, the downloaded versions has code samples for each web service, I find that VERY useful to get started. It does a good job of getting you started. There are two section of interest. Programming Tasks –> Programming Web Services for Windows SharePoint Services and Reference –> Web Services.

Two other good places to start are below. They do a good job of explaining what is available and how to start coding:

• Talk to SharePoint Through Its Web Services
• Getting Started With SharePoint Web Services

I wrote up an Example of how to use the SharePoint Web Services here.

In the past I used the SharePoint Object Model and .NET to develop migration scripts. I also did some direct database updates. The problem with these is that they require console (Server) access and I won’t have that with BPOS. So, the only option if I want to use the SP Object Model is to do the change before hand, then export the data to BPOS and hope for the best.

Since I won’t have console access, I think it makes sense to look at the web services route. The best part about this is that I can do the development (including debugging) on my laptop instead of the SharePoint server itself. The big downside is that the web services seem to be much more complicated to use. Everything seems to be XML based and a special schema just for querying. I would much prefer working with strongly typed objects and being able to use Intellisense.

One helpful thing is that InfoPath can be used. It appears dealing with the namespaces can be a bit tricky, but can be done and hopefully abstracted away. Here and here are posts on how to use XPath and SharePoint Web Services.

In the quest for being able to use strongly-typed objected I thought maybe someone created a LINQ to SharePoint provider. That would be great! As it turns out, someone did. It can be seen here. The problem is that it is ALPHA and appears to have died quite a while ago. The good news is that SharePoint 2010 has been released and it appears that they have included the LINQ to SharePoint Provider. Click here to see it. It looks similar, but I’m not sure if it is the same as the ALPHA one or not.

If you are not working with lists specifically then SharePoint Web Services are a good option.

Can’t access SQL Server Cluster or non-standard port or named instance after upgrading to Windows 7

After I migrated to Windows 7 we were not able to connect to the SQL Cluster using MS SQL Management Studio, Linqpad,Visual Studio, my apps, odbc, etc. The reason is that Windows firewall blocks non-default ports for SQL Server.

Determine if the Windows Firewall is the cause

The first thing you probably want to do is confirm that it is actually a Firewall issue. The easiest way (if you are lucky) is to just turn off the Windows Firewall. If you are not lucky enough to be able to turn off your firewall due to corporate policies then there is still hope. To help troubleshoot this, I created a rule in the Firewall that opened up all ports for all programs. Click here to see the details of how to do this.

Once you have determined that the firewall is the issue, you may want to open the port instead of opening it for individual programs or open all ports between your computer and the server you are trying to access.

Option 1: Open Firewall for server

This seems to be the easiest by far since you only have to do it once unless you change server ip addresses or ports.

To add a rule, go to the Start Menu | Administrative Tools | Windows Firewall with Advanced Security. Right-click the Inbound Rules on the left navigation area and then select New Rule…

Next choose Custom rule. Click the next button.

Leave all programs setting. Click the next button.

Leave Protocol type to Any. Click the next button.

Leave local IP addresses as Any. Add ALL IP addresses that you server has to the list of remote IP Addresses. I was surprised that I needed all the ip addresses for the server (use ipconfig from the command line if you don’t know them) and not just one, so to be safe add them all. Click the next button.

Make sure Allow the Connection radio button is check. Click the next button.

Choose only Domain (you may need to add private if you run into issues with vpn, etc). Click the next button.

Give the rule something descriptive.

NOTE: A slightly more secure version of this would be to specify TCP as the Protocol instead of Any. Copy the rule and edit it such that you change TCP to UDP. Then you’ll have one rule for TCP and one for UDP. This is the method I personally chose.

NOTE: You could do the same, but do it for you subnet, but that is a bit riskier from a security standpoint, though not bad on a corporate network. Just be certain to select domain (no public or private).

Option 2: Open Firewall for each program

To fix the issue, go to your Start menu | Control Panel | Windows Firewall | Allow a program or feature through Windows Firewall | Change Settings | Allow another program…

Next choose your program such as MS SQL Management Studio, Linq pad, Visual Studio, odbc, etc. Be sure to click the Network location types… button and select Home Network if you connect from home (and have selected that connection to be a home connection). FYI, to create an odbc connection using a non-standard port you will need to add the C:\Windows\System32\odbcad32.exe program.

Side note on MS Access: If you are trying to have your MS Access application access a linked server that uses ODBC to connect to the SQL Server you will need to create a new ODBC Data Source, but be sure NOT to make it a System DSN. You want to select User DSN otherwise MS Access will not be able to access it. Though, I imagine if you ran MS Access as administrator you could make it work that way with a System DSN.

I still had trouble when running in Visual Studio 2010 when debugging (worked fine if just ran without debugging). I never did figure out how to get past this. If anyone knows, please let me know.

Option 3: Hardcode server ip address

Workaround, if you can’t change your Firewall, you need admin rights on your machine I think. If you can’t do that, then you can always access the server using the ip address and port number separated by a comma. I don’t know why it works, but it does (at least on my machine).

 

Legal like warning: I am by no means a security or firewall guru. Please don’t take these as security recommendations. I simply figured out how to solve the problem while taking the security issues into consideration the best way I know how (I’m a programmer not a security expert). So, please don’t make these changes unless you understand the risks as you are messing with firewall rules that are meant to protect your computer from attack. You assume all responsibility for your changes.

Tips on How to install SQL Server 2008 R2

First this is NOT a step by step installation. I assume you can find the setup.exe and run it. I do address some of the less obvious issues that you may encounter.

BEFORE you run setup.exe: If you have Visual Studio 2008 installed be sure that you also have Service Pack 1 installed for VS2008 (Visual Studio 2008). Otherwise, you will have to stop the SQL Server 2008 R2 installation, go install SP1 for VS2008, then run the SQL Server 2008 R2 installer again.

Once you launch the setup.exe you will see the SQL Server Installation Center. Click the Installation navigation link on the left, and then select New installation or add features to an existing installation.

 

After a number of straight-forward screens you’ll come to this screen. This is the screen were you determine what you want to install. Basically the first 5 checkboxes (the ones under Instance Features) are for the server and the rest are for the most part for client, though they are “shared” so some of them are for server I assume also.

 

 

Check the items you want. I suggest the following.

 

NOTE: In the installation it will create a Integration Services (service). I guess it is needed even though I am not hosting it.

Visual Studio 2010 won’t debug under Windows 7

I found out that Visual Studio 2010 won’t let the debugger started until I run Visual Studio 2010 with Run as Administrator. I think I figured out why. Here is an account of what I was seeing and how I fixed it.

I could open my C# ASP.NET web application just fine using Run (Control-F5). Then I tried to do it with Debug (F5) and I got the following message:

Unable to launch the ASP.NET Development Server.Uncaught  exception thrown by method called through Reflection.

I then Googled that error message. I found this link. I go to the section 2.4.11.2 (shown below).

2.4.11.2 Debugger start fails if the IntelliTrace recordings directory is Read-Only

By default, IntelliTrace files are located in the "%ProgramData%\Microsoft Visual Studio\10.0\TraceDebugging\" directory.  If this directory is made read-only, or the directory is changed to a read-only location, an uncaught exception gets raised to the user.  "Error while trying to run project: Uncaught exception thrown by method called through Reflection."

To resolve this issue:

Option 1: Modify the permissions of the directory to be writable.

Option 2: Change the directory where IntelliTrace recordings are stored.

• ○ In Visual Studio, open the Options dialog box.
• ○ Expand the IntelliTrace item in the left pane of the dialog box.
• ○ Click the Advanced item under IntelliTrace.
• ○ Change the IntelliTrace recordings directory to one that is not read-only.
• ○ Click OK.

I tried both option 1 and Option 2. The problem is that when I tried Option 1 and went to the directory suggested, it was not there. When I tried Option 2 I got the following message where I was expecting to see some kind of useful UI:

An error occurred loading this property page.

Then I got the idea, what if Visual Studio 2010 needs to be run as administrator even though I am an admin under. I am knew to Windows 7, but I am quickly learning that most techie tasks require the programs to be Run As Administrator; Visual Studio is no exception to this. It seems that I am not the only one to come to this conclusion. Here Microsoft suggests changing Visual Studio 2010 to run as Administrator and I have shown instructions below:

If you are running Windows Vista, you must start Visual Studio by going to the Start menu and right clicking Visual Studio 2010 and selecting Run as Administrator. To always launch Visual Studio 2010 as an administrator you can create a short cut, right click the short cut, select properties, select the Compatibility tab, and check the Run this program as an administrator checkbox. When you start Visual Studio 2010 with this shortcut, it will always run as administrator.

The good news is that after to run VS2010 (Visual Studio 2010) as Administrator the proper directory structure will be created and the if you happen to run VS2010 without running it as Administrator you can now run as Debug (F5) and you can also go to the Options | IntelliTrace | Advanced and see a useful UI with no errors.

I think to keep things simple I will run VS2010 as administrator at all times.

Export to Excel using Dynamic Data

If you are using Dynamic Data ASP.NET web application you are likely using the Entity Framework and a Domain Service. With these key technologies it is easy to add an “Export to Excel” link to all your List pages in your project. The best part is that once you add this, you don’t have to write it again for each table. This is a write once use for everything solution.

To implement the writing of data to Excel I don’t want to use anything that requires Microsoft Excel to be installed (except by the people that are downloading the file). I chose a free solution from CarlosAg.net called Excel Xml Writer. Click here to go to the product page and download it. It is implemented using C# and is a managed solution (no COM, interops, etc). The file that is generated is a xml version of the Excel file format so it is easy to debug and tweak if needed. Best of all the installation is simple: just add a reference to the single .dll. There should be no other dependencies.

I wanted to use the same column names that are in List page that I am exporting and I didn’t want to have to hard code them. I wanted them to be read from the MyDomainService.metadata.cs. If you use the DisplayAttribute to specify user friendly names, my code uses them instead of the column names. Also, not every property in the table are shown. It depends on what the MetaTable.GetScaffoldColumns() returns which is what the DefaultAutoFieldGenerator() for the List.aspx.cs page uses.

To encapsulate this logic, I created a class called PopulateExcelWorksheetForDynamicData.cs.  To download the source for the file click the filename. I’m not going to go through all the code step by step. However, the basic idea is that the Populate() method is called. You pass it an IQueryable object and the MetaTable information. Based on that the MetaTAble.GetScaffoldColumns() is called to determine what columns should be shown. For any foreign key columns I resolve to the related (called the parent table) and display the appropriate column based on the DisplayColumnAttribute or default DisplayColumn if one is not specified via the attribute.

The datatypes that are specified in the MetaTable are converted to Excel types. Special formatting such as DateTimes require a special format in order for Excel to know how to show them as dates. I make some assumptions about the format the user will want to see. This is hardcoded, but could be extended or changed.

You can tweak the formatting of the file as well. There are two formats I use. See: SetCannedStyle1(), SetDefaultStyle(). I haven’t used SetCannedStyle1() in a while, but I think it should still work.

That is the guts of the logic. Now it is just a matter of creating a button or a link on the List.aspx.cs page and executing code similar to the following.

protected void btnExportToExcel_Click(object sender, EventArgs e)
       {
           IQueryable queryResults = null;
           using (var service = new DistiPromoDomainService())
           {
               // execute the query that was used to populate the GridView.
               var selectMethodInfo = service.GetType().GetMethod(GridDataSource.QueryName);
               queryResults = selectMethodInfo.Invoke(service, null) as IQueryable;

  // add in any filters that are applied via the QueryExtender as well
   var queryExtenderExpressions = queryExtender.Expressions;

                foreach (DataSourceExpression expr in queryExtenderExpressions)
                {
                    queryResults = expr.GetQueryable(queryResults);
                }

               var workbook = new Workbook();
               var sheet = workbook.Worksheets.Add("Sheet 1");
               var excel = new PopulateExcelWorksheetForDynamicData(sheet);
               excel.Populate(queryResults, table);
               PopulateExcelWorksheetForDynamicData.SetDefaultStyle(workbook);
               // don't use spaces as this will cause issues. You can also try .xls, but you will get an corruption warning in IE.
               string filename = Title.Replace(" ", "-") + "-Export.xml";
               PopulateExcelWorksheetForDynamicData.SendToBrowserForDownload(workbook, filename, Context.Response);
           }
       }
You may want to tweak the filename. That should do it.

Automatic Auditing of Changes using Domain Service and Entity Framework

A special thanks to Fredrik Normen for providing the basis for my code. His great article is here. For a better understanding of the basic code, please read Fredrik’s implementation first. He does a good job of walking you through his code.

With that said I have changed most of his code and added logging to a database table using another Entity Framework (EF) model. You can download my updates to his code under the Source Code section.

Please note that only changes made through the Domain Service will be logged. If you don’t use a Domain Service to modify all your data that you want logged this solution is probably not for you. However, if you use a Domain Service and Entity Framework this is a pretty robust solution for adding auditing to your ASP.NET web application in minutes. I am using this with my Dynamic Data web application and it works great!

The code works because the Domain Service has a concept of a ChangeSet. When you save (update or insert or delete) a ChangeSet is created that basically shows what the original entities look like and what the current entities look like. This is all standard Domain Service stuff. Nothing I did. However, what I do is look at the original and current values and create EntityChangeInfo objects and PropertyChangeInfo objects. These then get written to the database where there is a record in the database for each field. This is very verbose for Inserts and for Deletes, but just write for Update. If you don’t need insert and delete auditing, you can easily comment them out.

To implement my solution, here are the basic steps (from my memory) to add auditing to you application.

1. Download and use the AuditLog-Create.sql to create an AuditLog table in a database.
2. Create an Auditing directory in your Services directory (if you have one).
3. Download and copy the AuditLogger.cs, ChangeSetHelper.cs, EntityChangeInfo.cs, PropertyChangeInfo.cs to the Auditing directory you just created.
4. In your Models directory (if you have one) create a new ADO.NET Entity Framework Data Model that points to the database where you created the AuditLog table. Call the model AuditModel. The only table you want to add to it is the AuditLog table. You don’t need a Domain Services or anything else for this. We will reference it directly.
5. Download and copy the snippet from the DomainService-PersistChangeSetSnippet.cs and paste it in your domain service file that you already should have in your project. This is the file for the domain service that you want to audit. If you don’t want INSERT or DELETE auditing you can comment out the appropriate line in this snippet.

That is it. You should now be able to run your application, make some changes and see the changes show up in the AuditLog table.

You can then execute a query something like this to see the history of a particular record in a table.

select * from auditlog where EntityName = 'Activity' and EntityPKValues = '46' and operation = 'Insert'

select * from auditlog where EntityName = 'Activity' and EntityPKValues = '46' and operation = 'Update'

select * from auditlog where EntityName = 'Activity' and EntityPKValues = '46' and operation = 'Delete'

My Comments

You may notice that I log my times in UTC and that the date/time is the same for a given update, insert, or delete. In other words, when an update is written to the database, multiple records are created (one for each column that changes), but the CreatedDateTimeUTC field is stamped with a ONE datetime.

When values are inserted, the OriginalValue column will ALWAYS blanks. When a record is deleted, the CurrentValue column will ALWAYS be blank.

If you have a properties that you have added to an entity they will have an initial value and the computed value in some cases. If this is the case for you, you may want to hard code these property names the ChagneSetHelper.GetPropertiesChangeInfo method as properties to not log. Just like I have done for the EntityState property. Up to you.

Source Code

AuditLog-Create.sql

AuditLogger.cs

ChangeSetHelper.cs

EntityChangeInfo.cs

PropertyChangeInfo.cs

DomainService-PersistChangeSetSnippet.cs

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'

You will get this error when you try to use the msdb.dbo.sp_send_dbmail stored proc and the user that you are using does not have permissions to execute the stored proc.

The user must be a member of the DatabaseMailUserRole in the msdb database. To give the user permission to do so, execute the following:

EXEC msdb.dbo.sp_addrolemember 'DatabaseMailUserRole', 'username here'

NOTE: If the user has not be added to the msdb database, then you will get an error like the following:

Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'username here' does not exist in this database.

To fix this, you can execute the following

use msdb
go
CREATE USER [username here] FOR LOGIN [username here]

You can also use MS SQL Management Studio to add the user to msdb and to the DatabaseMailUserRole in one step if you like the UI. :)