How to troubleshoot SharePoint Usage Reports Usage Reports

We got our farm set up and running and after a few days went to check on the popularity trends.  The reports were coming up empty.

Through my searching I discovered the following troubleshooting steps to diagnose the issue:

1. Make sure the Usage and Health Data Collection was configured:
1. Go to Central Admin - Monitoring - Configure Usage and Health Data Collection.
2. Validate that Enable Usage Data Collection was checked.  
3. Validate that Events were selected to log.
4. Validate the Log File Location exists (SAVE THIS FOR THE NEXT STEP), it's usually "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\LOGS\" 
5. Validate the Database Server and Name of database are set and correct
2. Verify that the WSS_Admin_WPG and WSS_WPG groups both had full access to the Log File Location (FROM ABOVE) on all the servers.
3. I made sure both the Microsoft SharePoint Foundation Usage Data Import and Microsoft SharePoint Foundation Usage Data Processing timer jobs were enabled and able to run successfully. 
1. The easiest way to get there is to go to Central Admin - Monitoring - Configure Usage and Health Data Collection - Log Collection Schedule link.  
2. This will take you to the Timer job definitions.  
3. Make sure both definitions are enabled, the data import job should run every 5 minutes and the data processing job should run daily.  
4. Validate that they run by clicking run now and monitor the running jobs and job history.
4. Check the WSS_Logging  (the database name configured in the usage service application configuration) database's permissions, the web service web application account and the farm account both have ownership rights over it.
5. Check the contents of the WSS_Logging database, the request tables are populated with information.
6. Validate that there are receivers configured per this blog post: http://geekswithblogs.net/bjackett/archive/2013/08/26/powershell-script-to-workaround-no-data-in-sharepoint-2013-usage.aspx.  There is a fix within if they don't exist
7. If none of this works, you can try adding a SQL server account to the database authentication, but every time I set this it reverted...you may only be able to set this on initial configuration.
8. Check to make sure that data is being generated in the Log File Location in the RequestUsage folder.  There should be a .tmp file and occasionally a .usage file will appear.  After a few minutes the .usage files will disappear and be imported into the Event Store(every time the data import job runs).
9. Check the EventStore (found on the analytics server (check in Search administration topology) usually in C:\Program Files\Microsoft Office Servers\15.0\Data\Office Server in a folder named Analytics with a GUID, you can also try looking at the file shares on the active analytics server) verify information is being created there, it won't be identical to what is in the usage folder because that information gets filtered out.  
1. Check to see if there's anything there
2. Make sure file sharing is enabled between the servers to the analytics processing server and that the WSS groups have write access in both file sharing and NTFS permissions to the EventStore.

As a note for all of these changes, nothing will be seen in the reports until the processing job runs, often you'll have to wait over night to see if your changes worked.

In the end, I noticed the eventstore hadn't generated anything since a couple weeks ago.  I had to add permissions as described above to the log folder, the event store, enabled file sharing (port 445) and permissions, and made sure that permissions were set to the database.  After all that, nothing was working still.

After setting all of that I deleted the service application from Manage Service Applications and recreated it by going to Monitoring - Configure Usage and Health Data Collection  and checking the Enable usage data collection box. After that, the next day, we had results.

When in doubt, kick it.

For more info, here's an interesting blog post on how the data collection works with the EventStore: http://blogs.msdn.com/b/spblog/archive/2014/04/03/sharepoint-2013-usage-analytics-the-story.aspx 

PowerShell Script to remove all permissions for SharePoint user

Threw this powershell script together to wipe out a user from your farm...I have this running in conjunction with other scripts that disable the user in active directory.

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue
[Void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

$claimsPrefix = "[Prefix for the user you're removing from the farm]"
$site = New-Object Microsoft.SharePoint.SPSite("[a site]");
$service = Get-SPServiceContext $site;
$site.Dispose();

$upm = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($service);

$user = Get-ADUser -Identity [THE USER]

#I use email as the identity in my farm, thus this line

$mail = Get-ADUser $user -Properties mail | Select -ExpandedProperty mail
$profile = $upm.GetUserProfile($claimsPrefix+$mail);

#Delete MySite

Remove-SPSite $profile.PersonalSite -Confirm:$false;

#Delete Profile

$upm.RemoveUserProfile($claimsPrefix+$mail);

$webApps = Get-SPWebApplication -IncludeCentralAdministration
foreach ($webApp in $webApps){

    #Remove explicit rights given in web application policies
    $webApp.Policies.Remove($claimsPrefix+$mail);

    $siteCollections = Get-SPSite -WebApplication $webApp -Limit All

    foreach($site in $siteCollections){
        $spuser = Get-SPuser -Web $site.RootWeb -Identity $claimsPrefix+$mail;

        #Remove from site collection admins
        $site.RootWeb.SiteAdministrators.Remove($spuser);
        foreach($web in $site.AllWebs){
            $spuser = Get-SPUser -Web $web -Identity  $claimsPrefix+$mail;
            foreach($group in $web.Groups){
                
                #Remove from all groups in website
                $group.RemoveUser($spuser);
            }
            if ($web.HasUniqueRoleAssignments -eq $true){

                #Remove any explicit role assignments
                $web.RoleAssignments.Remove($spuser);
            }
            foreach($list in $web){
                if ($list.HasUniqueRoleAssignments -eq $true){

                    #Remove any explicit list role assignments
                    $list.RoleAssignments.Remove($spuser); 
                }#list
            }#lists
        }#webs
    }#sites
}#webapps
    

How to brand MySites

The Default master page/template for MySites in SharePoint is very generic and not at all connected to the rest of the SharePoint farm.  To add some branding (or even a link back to the main portal), you'll have to modify the master page and add a feature stapler.  Here's a great walkthrough I found for setting up a branding feature stapler.

http://sharepointologic.blogspot.com/2013/04/branding-sharepoint-2013-my-sites-with.html

Troubleshooting UPS issues

After running into a User Profile Service issue (it was endlessly synchronizing, then when I stopped it it was endlessly stopping) I have the following tips for troubleshooting:

Check the ULS for exceptions, google extensively (I didn't have any exceptions, it just wasn't behaving)
 Then:

1. Verify Farm Service account and UPS account are in local admins. 
2. Restart Synchronization service 
3. Restart IIS 
4. Restart User Profile manager and Synchronization service 
5. Restart OWSTIMER.EXE service 
6. Restart IIS 
7. Clear File Cache 
8. Restart all service 
9. Restart service 
10. Set fire to office and run 

Usually that'll fix it before step 10, but if not, that's always an option.

ADFS 2.1 and SharePoint 2013 Authentication Timeout settings

When using SharePoint with an ADFS 2.1 Trusted Identity Provider, there are several authentication cookies and places where you can set the time outs. After much trial and error, I've discovered how to set it to properly time everything out (so that users aren't logged in perpetually). By default ADFS gives you a cookie that expires after a full month (MSISIPSelectionPersistent) and SharePoint gives you a cookie that expires after a day and a half (FedAuth). When the FedAuth ticket expires it directs you to the trusted identity provider (ADFS). ADFS checks to see if you have that cookie. If so, it logs you back in without asking for credentials. To stop that behavior, change the web.config in the /adfs/ls directory and set the persistIdentityProviderInformation key's enabled property to "false". After that, every time a user is directed to the TIP it will have the user authenticate again. In our environment (SmartCard) this is practically seamless (it just prompts them for their PIN). To lower the amount of time of the FedAuth cookie, go to your SharePoint server and open up the SharePoint Management PowerShell console. use the following: $sts = Get-SPSecurityTokenServiceConfig $sts.CookieLifetime = (New-Timespan -minutes [however long you want the cookie to last in minutes]) $sts.Update() iisreset The next time users have to check in they'll get the new cookies and timeout settings.

Importance of database management

On my sites, most developers turned SharePoint admins and most Desktop/AD administrators turned SharePoint admins tend to put up a "Somebody Else's Problem Field" around their databases, not realizing the implications improper configurations can have on their system.

From simple things like leaving the databases in Full restore with out accounting for the transaction logs or configuring backups but not checking to make sure they run, oversights like these and more complicated ones can come back to bite you in the end.

Here are some of the gotcha's I've run into if I turn my back on the implementation team for too long:

• Full restore mode vs Simple restore mode.
  What you chose can depend on several factors and can vary by database. You must factor in the difficulties and advantages of both and be vigilant once configured.
  
• Not configuring multiple content databases for web applications before utilizing it.
  Each new site collection will be created on a new database in a round-robin fashion when it is created. This makes it easier to restore a site collection if necessary, less collateral damage if something goes wrong with a single database, also if space issues are unaccounted for it's a lot easier to shuffle several small databases for compaction than one huge one.
• Just because you've allocated a separate drive for it, doesn't mean the database is there.
  You still have to move search databases and system databases to the proper locations after they're created. Make sure you verify that taking the database down will not affect any running processes.
• Make sure you have enough space for growth.
  In SQL server when a database grows it needs to be able to make a copy of itself. If it can't make this copy in the same drive that it exists in, it will not grow and your users will receive errors. So, not only do you have to account for actual growth of data, you also have to account for the databases to grow as well. I'll discuss this more below.
• A data externalizer like StoragePoint won't work until configured. Additionally, just because you remove data from a database won't automatically make it smaller.
  

I may have to come back and update this later, but these are a good starting point. As you can see, it's mostly common sense planning items, but things tend to get overlooked usually in the excitement and joy of a new implementation or migration.

Full restore mode vs. Simple restore mode is a decision that will have to be made. Sometimes your hand will be forced in the matter by what form of high availability and disaster recovery you chose to implement. Due to the nature of database Mirroring and Transaction log shipping you'll have to use Full restore mode...at least on the databases you'll be mirroring or shipping. You may want to use full restore mode to be able to get a very granular restore with less overhead than frequent database backups. When you use full restore mode you can make restores down to the transactional level. The problem there is that with SharePoint, the database communication portion of the product is designed to the administrator to be a closed book. It's not impossible to figure out what's going on back there, but most of the time it's unnecessary to know and until you do figure it out you can spend far too much time figuring out how it works rather than just getting the user's data back. The other concern with Full restore mode is that you'll have to manage the transaction logs for those databases, which means a lot of backing up and compacting depending on the space you've allocated for logs and how active your user base is. Going along with that, unless it's absolutely necessary to have full restore mode on, I recommend from experience that you move crawler databases and service databases to Simple mode. These databases have the potential (particularly products like Nintex reporting) to be very active and maintain several GB of transaction logs, even if backed up every minute. (I'll probably get some push back from actual DBAs for this, but...) I would recommend changing to simple mode to make transaction log maintenance a non-issue.

I recently went through a migration where utilized 2010 tenants to pull 6 web apps into one tenant web app. After I let the migrators go on the content I realized they'd only created one database for the web app. I took a look at said database and it was several hundred gigabytes and the drive was filling up fast. When performing an ALTER on a database, the smaller it is the quicker it goes. I tried to take a 200GB database off line at one point and it ended up taking over 8 hours before it completed. Additionally, it's important to remember that when a database in MS SQL server needs to expand, it first has to copy itself. As a rule you should make sure you have as much free space on a drive for 2 of your largest databases. If you run out of space, there's numerous options for cleaning up space but most of them involve compacting databases. Even if a database is going to be smaller, it still has to build out the structure before moving it. Setting the site collection creation to round robin between the databases is a cheap way of trying to balance out database sizes and keep them small, the obvious problem is that you don't know which site collections will be big and which will be small. If you leverage the SharePoint Admin Toolkit you can shuffle the site collections after creation to balance the collections by how much their used between the databases. This isn't the kind of maintenance that will need to be run frequently, probably once or twice a year and after a new group of users starts using it and after your initial set up is completed. 4 databases 50 GB each is much easier to manage than 1 database that's 200 GB.

Another thought in addition to this is to set the databases to start at the size that you hope is larger than they expect them to be. The size of a database does not always reflect the contents of the database. The database engine manages the data within the database and makes the database larger if required. The DBA has to manually shrink the database if needed. So rather than have your database grow as needed make it as large as it needs to be or as large as you expect the data to grow to over a period of time. It is a little more difficult to monitor that growth than just eyeballing the drive space, but you should monitor with this method to make sure that your expectations are accurate.

Remember, in all systems the devil is in the details, and SharePoint is a complicated system, don't ignore the simple stuff.

Disposing SharePoint objects

Here's an article about when you need to dispose of SharePoint objects and when you absolutely shouldn't.

Quick caution I just found out about: never dispose an object from SPContext.

http://blogs.msdn.com/rogerla/archive/2008/02/12/sharepoint-2007-and-wss-3-0-dispose-patterns-by-example.aspx

Here's the MSDN white page on it, but the first article is a bit easier to read

http://msdn.microsoft.com/en-us/library/aa973248.aspx

To Update() or not to Update()...

You can call the Update() method on just about any kind of collection in SharePoint, question is, when do you need to call it?

Simple answer: whenever the object has been directly modified. If you change the metadata on a column, update the column and that's it. If you change the value of a list item, update the list item, and that's it.
Update: you don't need to update a list if you've deleted an item from it or added an item to it, only if it's been changed.

Tricky one now: you add a list to a web then you add a list item to a list and then fill in the field values on that item. Update the list item, then update the list, then update the web.

Ok, it wasn't really that tricky.

Programmatically working with SharePoint list choice fields

SharePoint Fields, Columns and ListItems can get confusing at first glance, adding a choice field to the mix doesn't help things at all.

First, a quick note on nomenclature. Generally, when someone refers to a SharePoint "field", they're referring to the value of a field in a ListItem.
For example, when I say that I am checking the value of the Id field of a ListItem, I am referring to the value of the field which is an integer unique to that ListItem in that list.

When someone refers to a "column" they're referring to the definition of the field, i.e. the object that you can use to define the type of the column, the name of the column, the ID of the column, among many other values. Site columns are columns that are defined at the site or site collection level that can be used in any list. The values of the fields the columns they represent can be different, but the basic definition of the columns are the same.
So if I want to get the value of the Id of a choice site column, I'm referring to the Unique identifier that identifies the column, not a value of the field in a ListItem.

Adding to the confusion is that the class that you use to get and set column specific properties are all named "Fields" (like SPFieldChoice). More on this later.

What if you want to have a choice site column that is the same in all respects except for different choice values? In your code you will want to get the site column and cast it as an SPFieldChoice object. Once you have an instance of that column you can manipulate the choices in the "Choices" collection, which is basically a generic string array. For example:

//Using will close the web object automatically when I'm done with it.
Using (SPWeb myWeb = SPContext.Current.Web)
{
SPList myList = myWeb.Lists["MyList"]; //Gets a list named "MyList"

//Gets the field named "ChoiceField". Since ChoiceField is an SPFieldChoice I can cast it as
// such. SPFieldChoice also inherits SPField.
SPFieldChoice dropDown = (SPFieldChoice)myList.Fields["ChoiceField"];

dropDown.Choices.Clear(); //Clears out the list of options

//Add some values
dropDown.Choices.Add("a");
dropDown.Choices.Add("b");

dropDown.Update(); //Need to update the column for the changes to take affect
}

So that's how we edit the column. Now how do we get and edit the value of the field that is already stored in a ListItem?

Accessing the value of the field using the traditional ListItem[Field] method will yeild an int32 object whose value is the zero-based index of the selected Choice. To get the value that is selected you will have to use the SPFieldChoice method GetFieldValueAsText(string). You can do this in the following manner:

//Taking the list from the example above,
//lets assume the ListItem has the choice of "b" selected...
int SelectedIndex = myList.Items[0]["ChoiceField"] //will return a value of 1
SPFieldChoice dropDown = (SPFieldChoice) myList.Items[0].Fields["ChoiceField"];

string SelectedValue = dropDown.GetFieldValueAsText(myList["ChoiceField"]);
//SelectedValue will now be equal to "b"

Finally, to set a the value of a Choice field you use the same method as you normally would, bearing in mind that the field type is an integer. If you aren't sure what the field type should be, you could use the GetFieldValue(string) method and send it the text value that you would like the field to be and it will return the Field value that SharePoint Object Model is expecting.

myListItem["ChoiceField"] = 0;

Or

myListItem["ChoiceField"] = myList.Fields["ChoiceField"].GetFieldValue("a");

Both will have the same effect. Don't forget that when you modify the value of a field, call the Update() method of the ListItem or your changes will be lost.

myListItem.Update()...

Snowburnt.Update()

How Does SharePoint Outbound Email Work?

SharePoint outgoing mail is one of the simpler aspects of configuring your farm. You pretty much just point it to an outgoing email server. It can get slightly more complicated when you think about securing that outgoing email server.

SharePoint has no mechanism for authenticating with and SMTP server. That means that for SharePoint to be able to send out email notifications you'll have to leave it open for anonymous relaying and connections. Your SMTP server can be secured by only allowing connections to and relaying from known, safe servers.

So, which servers do you need to allow connections from in your SharePoint farm? The answer is ll of the front ends and the Central Administration(CA) server. At first I thought it was only the CA, but when we had some email issues I took a look at the SMTP logs and the SMTP server was recieving connections from all of the front end servers. It appears that the on-demand emails that are generated (access request emails, workflow start emails) are sent directly from the server the person is connected to at the time and the scheduled alert emails are sent from the Central Admin server.

One way of configuring you SharePoint mail environment would be to configure one of your front ends or your Central Administration server to be an SMTP server (this will also be beneficial for if/when you want to configure incoming email). Set it up to only allow relaying from the other front end servers in the farm and set up your network's SMTP server as a Smart host.
In this configuration you will have the ability to gather metrics on emails coming from SharePoint and you can worry about reconfiguring the SMTP server for relaying if you add new servers rather than bother your networking folks, they only have to worry about your single SMTP server now.

Getting Stats on documents added to SharePoint

I was tasked recently to figure out how many files were being stored in our SharePoint farm. Seems simple enough. Unfortunately there isn't a simple way built into SharePoint to give you simple information. Double unfortunately we didn't own the third party tools that would have made it easy. So I went right to where all the files were kept: the Content Databases.

Glancing through the tables a few of their names jumped right out at me: AllDocs and AllDocStreams. "This is going to be easy" I thought, just get a count(*) of these tables and I'm done. Just to be on the safe side I took a look at the data that was actually stored in these tables. It appeared that not only user loaded documents, but any page that sharepoint delivers is contained in these tables (AllForms.aspx, EditForms.aspx...).

Rather than try to hack away at the database myself, I found this blog which explained what data the tables contain and he had some clever SQL queries for delivering monthly growth by file quanty: http://suguk.org/blogs/sharepointhack/archive/2008/03/17/9161.aspx. I grabbed a piece of one of his queries to just a count of how many files are stored in one database:

USE (databasename)

SELECT COUNT(*) AS added
FROM
AllDocs INNER JOIN
AllUserData ON AllDocs.LeafName = AllUserData.tp_LeafName AND
AllDocs.DirName = AllUserData.tp_DirName AND
AllDocs.ListId = AllUserData.tp_ListId
WHERE (AllDocs.SetupPath IS NULL) AND alldocs.id IN (SELECT id FROM alldocstreams)

The problem now was that our installation had four content databases per application and 20 applications total. Rather than copy this query 80 times and changing the USE statement for each of them, I built out a stored procedure. The stored procedure cycles through all of the SharePoint content databases, inserts the number of files into a Table variable and finally spits out the final count with a summation query.

One note is that the entire procedure hinges on the fact that all of the content databases in our farm have the word "Content" in their name. Also they are the only databases that contain the word "Content" in the name on that server.

Here it is:

SET NOCOUNT OFF

DECLARE @strSQL NVARCHAR(1000)
DECLARE @strDatabasename varchar(200)

DECLARE @numFiles int

DECLARE @CountingTable TABLE(numberFiles int)

DECLARE MyCursor CURSOR FOR
select sys.databases.name as DB from sys.databases where sys.databases.name like '%Content%' AND state_desc='ONLINE'

open MyCursor
FETCH Next FROM MyCursor INTO @strDatabaseName
WHILE @@Fetch_Status = 0
BEGIN
SET @strSQL = 'SELECT @RecordCount = COUNT(*) FROM ['+@strDatabaseName+'].dbo.AllDocs INNER JOIN ['+@strDatabaseName+'].dbo.AllUserData ON ['+@strDatabaseName+'].dbo.AllDocs.LeafName = ['+@strDatabaseName+'].dbo.AllUserData.tp_LeafName AND ['+@strDatabaseName+'].dbo.AllDocs.DirName = ['+@strDatabaseName+'].dbo.AllUserData.tp_DirName AND ['+@strDatabaseName+'].dbo.AllDocs.ListId = ['+@strDatabaseName+'].dbo.AllUserData.tp_ListId WHERE (['+@strDatabaseName+'].dbo.AllDocs.SetupPath IS NULL) AND ['+@strDatabaseName+'].dbo.alldocs.id IN (SELECT id FROM ['+@strDatabaseName+'].dbo.alldocstreams)'

execute sp_executesql @strSQL, N'@RecordCount int OUTPUT', @numFiles OUTPUT
INSERT INTO @CountingTable (numberFiles) SELECT @numFiles

FETCH Next FROM MyCursor INTO @strDatabaseName
END
SELECT Sum(numberFiles) FROM @CountingTable

close MyCursor
DEALLOCATE MyCursor

Enjoy!

SharePoint and Mirroring

Rather than deal with issues we've run into with the Microsoft Clustering service, it was decided that we try SQL database mirroring...and in the process opened up another can of worms.

Careful planning and thorough design are the keys to success in any IT project, this article will show some of the things to consider before going with mirroring or clustering for you high availability solution.

The differences between SQL mirroring and clustering:

1. A SQL cluster uses a virtual server instance that the active server uses to host the Server...the entire server.
   A SQL mirror mirrors individual databases between two servers. By mirroring I mean it passes the transaction logs between servers.
2. SQL cluster shares a single storage location for databases
   With mirroring each server manages it's own databases, so there isn't a single point of failure.
3. A mirror can have a 3rd server that acts as a witness...it receives and passes on the transactions to ensure no transactions are lost.

The big advantage to clustering is that it is completely transparent to outside servers since no matter which server is active the servers will always connect to the virtual server which never changes. SharePoint doesn't have any method natively of detecting or handling a database failover. So this leads me to ask, how do we handle a failover?

There are two ways of managing this, the first is through stsadm. Run the commands to change the database server and reconnect the databases...I'll look those up and get back to you...This method seemed cumbersome to me, so of course there's more than one way to skin a SharePoint instance.
Another alternative is to use SQL client aliases. A SQL alias is basically a local nickname for a SQL server. You can configure Aliases by running "cliconfg.exe" and going to the alias tab. Add one, give it a logical name, select tcp/ip as the protocol and put in the SQL server FQDN.


Repeat for each of the front end and application servers. If the DB server goes down, or the databases fail over, just change this value on each of these. Sharepoint will refresh the connection within about 60 seconds and your users will just assume it was a blip in the system.

Now the next question: Do we really have to do this manually? on every one of the servers? What a pain! Yes, you will have to do it manually and there's no "Microsoft" way of doing it...I also couldn't find any third party tools for making it happen either. On the advice of another blogger I wrote my own asp.net windows service to take care of this. The logic is pretty simple, connect to the witness server, query the sys.mirroring_databases view and check the principal server of one of the SharePoint databases (I usually have it check the config DB) and change the SQL client alias information accordingly. You do this in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
There is a seperate value for each alias in this registry key.

So...what happens if a single database fails over and not the entire server? That's right, more SharePoint Instances to be skinned. One thing you can do is add a SQL server alert to catch WMI event changes to the DATABASE_MIRRORING_STATE_CHANGE object which then tells all the databases to fail over. A good way of doing this is documented on this page: http://www.mssqltips.com/tip.asp?tip=1564.
We ran into a problem when our server lost connectivity briefly and started failing databases over. When it came back online the remaining databases stayed Primary but the others stayed mirrored. I haven't investigated entirely to see if in that situation if the server would register a WMI event for a fail over. In the event that it doesn't, you're stuck with databases scattered on two different servers and your SharePoint server doesn't know where to find it's data. My recent idea was to make a separate alias for each database and tell the mirror watch service which database belongs to which alias. Now it doesn't matter where your databases are because SharePoint will be able to find them. This method will increase initial administrative overhead when creating new databases and installing initially, and will make SharePoint's server list in CA look huge. I'll have to do more research into any other side effects this may have on SharePoint as a whole.

A couple closing notes...and precautions about SQL mirroring with SharePoint.

1. Be very careful how many databases you put into a mirrored set. Unless you have a real beefy SQL server, you will get very strange behavior from your mirrored databases, 10 per server is one recommendation that I heard, but it's possible to have thousands, provided you have the processing power and RAM.
2. When designing your maintenance plans, keep in mind that the databases won't always be live on the same server. If you are planning to have databases live on different servers, you might want to write a T-SQL script that only backs up databases that are currently principals on both databases. If possible, make sure they write to the same backup location also to prevent confusion.

There are a few different whitepapers from Microsoft out there detailing the steps to take to get database mirroring functioning, the purpose of this article was to detail to you some further concerns you might want to consider when designing your SharePoint archicture. With SharePoint sometimes it's not so easy to make a second go of it.

To sum up, when considering an SQL high availability solution, some things to consider: how will the application fail over? How will you maintain the databases? How many databases are you planning to host? What are you looking for your solution to achieve? After considering these points you'll be more prepared to proceed successfully with your deployment.

Programming SharePoint permissions, simplified

The other day I ran into an issue that I'd had a frustrating time wrapping my head around in the past: assigning SharePoint permissions programmatically.

I have found that there is very little on the internet that gives a clear explanation of how permissions work or how to do it on a general basis. Mainly I found examples without clear explanations for what was happening or what needed to be done.

So I'll try my best in this post so summarize it for anyone else that may be running into similar issues.

SharePoint permissions function with 4 general parts: (bear with me, I'll break this down)

• The Role
• The Principal
• The Securable object
  
• The Assignment (the key to it all!)
  

The Role is the set of permissions that will be assigned. These can be set in a number of ways from an existing role or by defining your own set of base permissions that the role will posses. The possible permissions are contained in the SPBasePermissions enumeration.

Defining your own role is done like this:

//Create Role
SPRoleDefinition newRole = new SPRoleDefinition();

//Name the role, then assign permissions to it
newRole.Name = "New Role";
NewRole.BasePermissions = SPBasePermissions.EditListItems | SPBasePermissions.AddListItems | ...;

The Principal is the object that you will be binding the permissions to. This is usually either an SPUser or SPGroup. What this means is that you have a group (or user) that you want to assign permissions to, so you create an SPRoleDefinition object that defines what permissions the group (or user) needs to have then you bind these permissions to the group.

The Securable object is what you want to lock down. This can be a list, web, site, library, library item, even a group.

The Assignment object is what ties everything together. It binds the Principal to the SPRoleDefinition and it assigns the Group to the securable object.

Demonstrated: (First, creating a new list to be secured; Second, creating a group to be assigned the permissions and securable object; Finally, tying the RoleDefinition created earlier to the group, then assigning the group to the new list...pardon the lack of indentation)

//Don't want to get caught with your SPWeb open...
using (SPSite mySite = new SPSite(SPContext.Current.Site.Url)
{
using (SPWeb myWeb = mySite.OpenWeb())
{

//Creates list
Guid ListId = myWeb.Lists.Add("New List", "Demo List", SPListTemplateType.GenericList);

//Create Group
myWeb.SiteGroups.Add("NewGroup", myWeb.Users[myWeb.CurrentUser.Name], myWeb.CurrentUser, "Demo Group");

SPGroup myGroup = myWeb.SiteGroups["NewGroup"];

//Creates Role Assignment and ties it to the new Group
SPRoleAssignment NewRA = new SPRoleAssignment((SPPrincipal)myGroup);

//Binds the role to the group
NewRA.RoleDefinitionBindings.Add(newRole);

SPList myList = myWeb.Lists[ListId];

//You have to break inheritance to assign different principals to inheriting securable objects
if (myList.HasUniqueRoleAssignments)
{
//This can cause problems on postbacks, so as always in sharepoint, use try catch blocks
myList.BreakRoleInheritance(false);
}

//Add RoleAssignment to the new List
myList.RoleAssignments.Add(NewRA);

//You can also set item level read and write security (for lists)!
// If you set this, make sure someone has the SPBasePermission: ManageList or no one will be able to see all the items.
//for read: 1 is no security, 2 is read only your own.
myList.ReadSecurity = 2;
//For write: 1 is no security, 2 is edit your own, 4 is edit no items.
myList.WriteSecurity = 2;

}
}//All done!

Hopefully this will help someone else and at least for me I can look back to remember how to do it when I have to do it again.