Build on SharePoint

Set SharePoint Content Database in Read-Only Mode

2013-05-06T18:23:56Z

This a quick & easy one; I couldn’t find any MSFT material on how to do this as easily as I thought I might, hence this article.

A simple question: how do you set a SharePoint content database to read-only? You may’ve seen the setting “Read-Only” in Central Administration and wondered “how do I make it say ‘yes’?”. Well the answer is surprisingly simple – you set it as read-only in SQL Server - SharePoint then picks up on this change and then disables any operations that would allow users anything more than read access to that content databases – for all sites in it.

The following is supported in all versions of SharePoint from 2007 service-pack 2 (SP3 is the only currently supported patch-level at the time of writing) to SharePoint 2013.

Setting a Content DB to Read-Only

Here we have a typical content database, in read-write mode as we’re accustomed to.

Let’s change that in SQL Server Management Studio. Right-click on the database in question & go-to the options section:

Change this property to “True” and select OK. You’ll be prompted with this message:

This is warning you any open connection will be dropped. SharePoint should be fine as it’ll just reconnect transparently thanks to some ADO.Net SQL connection abstraction trickery but as SQL Server doesn’t know this so it’s warning you just in case. If you’re worried about this, change the read-only property in a maintenance window.

Confirm the change; SQL won’t restart, neither will SharePoint (although there’ll be a very small pause as SharePoint re-establishes new connections to SQL Server). That’s it, you’re done!

Just for the record, if you don’t have SSMS installed you can just run the same in a direct command of course:

ALTER DATABASE [WSS_Content] SET READ_ONLY WITH NO_WAIT

GO

Checking Read-Only Mode

You should now see this in Central Admin:

That’s pretty much it. SharePoint will now disable anything that’s related to modifying the database on any sites in that content database. More info on exactly what happens to a site in read-only mode can be found @ http://technet.microsoft.com/library/dd793608.aspx

Cheers!

// Sam Betts

My Sites Error - HTTP 403 (forbidden) on EditProfile.aspx

2013-04-26T10:09:25Z

Just a quick one as I had this error occur on a test environment and couldn’t find much about it online.

If you get the error HTTP 403 when you try and edit a profile in My Sites (/_layouts/EditProfile.aspx?accountname=[account]") then fear not, it’ll most likely be because you’ve set the ‘portalsuperreaderaccount’ and ‘portalsuperuseraccount’ properties for the web-application and you’ve not given these accounts access to the My Sites web-application.

In the ULS logs of the web-front-end in question you’ll probably see errors like this:

The Fix

Add the users you’ve set for these properties to the web-application “User Policy" with “full-read” and “full control” rights as appropriate.

That’s it – no restarts or anything required – hit F5 and you’re good to go!

SharePoint 2013 and SQL Server AlwaysOn - High Availability SharePoint

2013-04-24T16:10:10Z

What is AlwaysOn and why use it for SharePoint?

AlwaysOn is a great new feature to SQL Server 2012 that allows consuming clients to have a mirroring and failover database solution all transparently for a single named instance. SharePoint is one such consumer of SQL and depends on having a solid connectivity to run. If SQL goes down, so does SharePoint so it’s well within our interests to configure a bullet-proof SQL Server solution that just won’t die.

In this blog post we’ll set up an AlwaysOn cluster for a SharePoint 2013 farm in the following stages:

Create SharePoint databases.

Configure a SQL alias for a consistent SQL instance name.
Create farm on SQL instance/replica 1, creating all databases needed (service-apps & web-apps).
Stop SharePoint so databases remain static during migration to an AlwaysOn cluster.

Create AlwaysOn high-availability group.

Restore all the DBs onto SQL replica 2 (with NORECOVERY).
Create AlwaysOn availability group
Join the replica 2 databases to availability group.
Create listener.

Migrate SharePoint onto AlwaysOn on cluster

Reconfigure SQL alias for new listener.
Restart SharePoint services with updated alias.

Test failover and that SharePoint can survive it.

The failover process in action
Why SharePoint doesn’t survive the 1^st time.

Test Environment Details

For this demonstration I’ll be using a “super-cluster” configuration just because it’s the mother of all high-availability solutions. Each member for the AlwaysOn cluster will itself be a failover cluster with shared disks rather than a standalone machine. Why? That way we have instance redundancy and data redundancy too, all for the same logical SQL Server instance. Normally the two instances will be mirroring each others’ data but if one replica node goes offline the affected replica will live on no worries, and even if a whole replica (both nodes) does goes offline we can just failover to the other. The two replica members for this cluster will be:

SQL cluster 1 (primary replica) - SQL-SP2013\SP15
SQL cluster 2 (secondary replica) - SQL2-SP2013\SP15B

Both these instances are also on their own subnet so we’re covered for a whole bunch of doomsday scenarios – that being the whole point of this setup and blog-post. The parent cluster uses a file-share witness on a 3rd subnet to verify connectivity out of the subnet, or a routing failure.

To span both subnets we’re going to create a single logical SQL Server instance with IPs in both subnets 1 & 2, for SharePoint to connect to.

This is the SQL backend we’re aiming for in this example – it’s a cluster of clusters! Please note and to reiterate; for AlwaysOn to work this “cluster-of-clusters” setup isn’t required; you can setup an AlwaysOn cluster with just x2 standalone SQL Servers too, it’s just a single node failure would result in loss of mirroring whereas both replicas being failover clusters themselves allows us to survive up-to 3 simultaneous machine failures in our SQL Server cluster without breaking into a sweat! And that’s the goal here; a zombie SQL setup that just won’t die, for a SharePoint farm to lean on.

Create a SharePoint Farm on an AlwaysOn Cluster

Before we create a farm let’s connect to our two instances that’ll make up the replicas in the AlwaysOn cluster.

Both SQL clusters instances are ready and empty.

Create SharePoint Databases

Prepare SQL Alias

We need to fool SharePoint into thinking the SQL server where we'll create the configuration database is called something else. That’s because when you create a farm, whatever destination server you use during the “create new farm” stage isn’t forgotten (to simplify), so first thing’s first let’s create fake alias for our SQL “server” – “SP15” as it’s SharePoint 2013 that’ll use it (version 15). As far as SharePoint needs to know everything is going into a SQL box called 'SP15'.

Now assuming you don’t have SQL Server tools installed (which you shouldn’t if it’s on production!) you’ll need to use a tool called “cliconfg.exe” to create a SQL alias. Run it; click the “Alias” tab, and the Add button.

Add a name – SP15 for the alias name, but it can be anything - instead of referencing a server-name we’ll reference the name of the alias which will transparently redirect traffic to the alias destination instead.

Install & Configure Farm

The next thing we need to do is create all the databases we’ll need on the farm. For this example I’ll use a standard, home-baked script to create my basic services, accounts, and applications – and their corresponding databases:

Farm (1 database; SharePoint configuration)

Service Apps:

User-profiles (3 databases; profiles; social; sync)
Enterprise search (4 databases;
State service (1 database)
Usage & health service (1 database)

Content apps:

Default port-80 web-application (1 database)

We’re not so worried about having the perfect configuration at the moment so much as just getting the databases created.

The PowerShell script, doing the business. Notice the “database server” name is our faked alias and yet there’s no problem; aliases are very useful for this.

SQL instance 1 now with all the databases we’re now going to add to a High-Availability (HA), AlwaysOn group.

Stop SharePoint so databases remain static during migration

We now have everything we need to create the single SQL point of access. First thing’s first though, let’s stop any updates happening to any SharePoint database happening until we're all setup by shutting the SharePoint services down – IIS, SharePoint timer are the important ones. We’ll restart them once we’ve updated the alias to use the AlwaysOn group and SharePoint will be none the wiser.

And we’re done with SharePoint for now.

Create SQL Server AlwaysOn Group

Restore all the DBs onto SQL instance 2 (with NORECOVERY)

Next thing we need to do is copy the databases to the 2^nd instance. Part of the AlwaysOn wizard can do this automatically for you if both instances have exactly the same database file locations however we can't do this as each failover replica has its own shared cluster-disk for its’ data files, and each disk has its’ own drive letter, therefore the paths will never match-up. And that’s a shame, but we have to do this the hard way.

To join the 2^nd replica we need to run a full backup of all the databases on replace 1 but for now we’ll start with just the Central Administration content DB.

Take a full backup. Copy to destination server. Restore.

No need to create a new empty database. Just directly restore & SQL will create the necessary bits & pieces for you.

Important: the restore needs to be done with the NORECOVERY option. If this isn’t done, AlwaysOn can’t use the database.

Once successful, the database will stay in the “restoring” state. Don’t worry about this – it’s just SQL is basically saying the DB isn’t usable from this instance, for now.

Create AlwaysOn availability group on one database

Next, we’re going to create our AlwaysOn instance. We’re going to only add the Central Admin content DB to the group for now…it’ll be clear later why.

On the primary server (SQL-SP2013 in this example), expand “AlwaysOn High Availability” (this requires you to enable AlwaysOn in the SQL Server Configuration Manager) and click “New Availability Group Wizard…”

Add name. This will be the name of the role added to the parent cluster. Click “Next”.

This is where we specify which databases to add to the HA group. We’ve only backed up one the CA content DB so this is only one we can add for now.

Also this screen is a good place to point out something this technique requires that SharePoint doesn’t by default configure – a full recovery model for databases. We’ll change this later, but for now we just want to create the HA group. Click Next.

Here’s where we specify which SQL instances will make up our AlwaysOn HA group. Click “Add Replica” and connect to the 2^nd instance. We’ll create a listener later; just accept the defaults for now.

Important notes here about the AlwaysOn configuration:

The default is “asynchronous commit” for data-safety settings which basically means the primary instance doesn’t wait for the secondary instance to confirm its’ COMMIT until the primary does its’ own COMMIT for any updates to the AlwaysOn databases. This isn’t necessarily unsafe but there’s a small potential for data loss should the network breakdown between instances during an update of some kind, however performance is much better. We care about safety over speed in this example, so we’re going to “synchronous commit”.
You can have the secondary instance as a read-only instance but this slows down the machine so it’s off by default. Obviously if there’s a failover the secondary instance will start accepting sessions again from SharePoint but enabling this is probably not necessary right now.

Here’s where we kick-start the synchronisation. Again, SQL Server can save a lot of pain here & do everything for you (saving you the whole backup/restore process above) but as I mentioned, my SQL instances have different drive-letters for their data-stores so a full synchronisation will never work. We’ll go with “Join”.

After a while, this should be what you see. If you get any error it’s likely because the backup/restore wasn’t done correctly – check the backup was a full backup?

Also it’s worth mentioning there’s some clustering considerations AlwaysOn requires like making sure each replica failover instance can’t be cross-hosted; I’m not sure why this is, but you’ll need to make sure if you have a cluster-of-clusters as I do here each cluster member can only host one failover cluster or the other, but not both.

Assuming it worked you should you be able to right-click on the availability group, select “Dashboard” and see this wonderful screen of green:

A couple of points here:

We’ve configured the group for synchronous commits for both nodes. Again, this has a performance hit as a round-trip “ok” is needed before any writes are completed, but it is the safest. This makes little difference for SharePoint sites that are read-heavy compared to writes; a publishing site say, but could drag if lots of updates occur instead, say in a collaboration site. You can run in asynchronous mode which doesn’t wait for the ACK from the secondary node before continuing, but that’s at the risk of losing data.
For the same reason the “failover readiness” says “no data loss”. If we were running async commits this would say “data loss” for the secondary databases but this is just over-dramatizing the fact replica 1 doesn’t wait for replica 2 to commit before committing itself, so it can’t know the failover commit occurred and therefore it assumes the worst. If you want absolute guarantees and don’t mind the performance hit, make the AlwaysOn group perform synchronous commits.
Green is good; if one day it turns not-green (i.e. you have an unexpected failover or communication failure) then you need to figure that out with your DBA/networking people – don’t blame me, I’m just a SharePoint guy :)

Restore remaining databases to SQL machine 2

Next up; we need to set every database that isn’t already configured to full recovery that mode. I’d point out that there are possible supportability issues here in that the product-group haven’t officially said this is a supported action or not – I say this as a disclaimer on the very slim off-chance there are issues with changing the recovery model on SharePoint databases. There shouldn’t be any issues though although performance is obviously faster if simple transaction-logging is used.

It’s fairly simple to change the recovery model – right-click on a database, open “options”, select the recovery model there and press OK. That’s it – no downtime or restarts or anything needed.

Do this for any database not in full-recovery mode. That’ll be:

Any search database.
Any user-profile app database.

Next, backup each database as before. Copy as before.

Restore as before (WITH NORECOVERY). Now your SQL Server Management Studio should look something like this:

Scripting restores can be quicker than managing the GUI for multiple DBs at once (if they’re not overly complicated in file-structure that is). All databases are now ready to add to the high-availability group!

Add databases to availability group

On the primary instance, expand the AlwaysOn groups; right click on ours and select “Add Database”. We should now see the following:

Select them all & click next. Again, we’re joining only as we’ve restored the exact copy to the 2^nd SQL replica. The process is basically as before just with a “connect” section to authorise.

This is hopefully what you’ll see. If some database(s) don’t join because of insufficient transaction-log data with this error:

The mirror database, "[database]", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

If you see that persistently despite being sure of a full backup & restore, try again but just backing-up and restoring the transaction log to the same database(s) this time. It worked for me anyway.

Create SQL AlwaysOn Listener

We’re almost done with SQL; our SharePoint databases are mounted and are perfectly syncing one to the other, and in fact there’s nothing stopping us from directly using the old connection-string again knowing that our changes will be mirrored to the 2^nd SQL instance. However we want a more automatic failover process than that – we want an abstract instance name that’s independent of which replica is the active one, and that’s what SQL AlwaysOn listeners give us.

Right-click on the HA group “SP15-SQL” again and click “Add Listener”. This is more of a network-admin type thing but the idea is to create IPs in both subnets. We give it a name which will be the equivalent of a generic clustered end-point and a port-number to listen on. Behold:

Click OK. This has now happened:

The listener has a DNS record, computer account in AD, and the assigned IPs. Test it by pinging it. Clustering is awesome!

Add SharePoint Servers to the farm

Configure SQL alias for listener

So that’s about it for the SQL side. We now need to add our SharePoint servers to use this new “server” we’ve setup, but given it’s on a non-standard port we need to use SQL aliases to get there with a simple name. How handy we have one as we needed to fool SharePoint into thinking the SQL Server name so we’re in luck – we’ll just update the alias with the new details!

Open “cliconfg.exe” again and edit the alias. Enter the listener name, select TCP/IP, unselect “dynamically determine port” and enter the port-number we assigned for the listener.

Accept all changes. Repeat this on all SharePoint servers.

Restart SharePoint services (with updated SQL alias)

The final stage in this journey now we’ve moved everything and have updated the alias is to restart all SharePoint services on our starting server and finally add any further servers to the same configuration database necessary. Start IIS & SPTimer and SharePoint will have no idea it’s now looking at a difference SQL instance.

Connection success (again)! Continue the wizard as normal & repeat, lather and rinse for any remaining SharePoint servers.

All SharePoint cares about is that its’ databases are on a SQL Server called “SP15”. Make sure this alias is consistent for all your SharePoint servers.

Test the Failover – Will SharePoint Survive?

So now’s the important bit – the main reason for doing all this is to ensure the continued uptime of SharePoint should the SQL back-end drop unexpectedly (even failover clusters go down sometimes). Suffice to say this blog is long enough as it is; I’ll just show what happens on a manual failover and you’ll have to believe that it kicks in automatically when various nodes go offline or don’t (assuming configured correctly).

The keyword here is “test” because as we’ll see, SQL will failover perfectly to the 2^nd replica but SharePoint won’t survive for reasons we’ll see in just a moment…

So once we’re convinced we have both replicas talking and syncing to each other perfectly, we’re going to failover to the 2^nd replica (SQL2-SP2013\SP15B). Right-click on the HA group again and select “Failover…”

This confirms what we’re about to do is “safe” – we’re running in synchronous mode so all update transactions are always guaranteed safe. Click next, then connect to the 2^nd replica with an appropriate login and start the failover.

Success! If you have the dashboard of the AlwaysOn group of the 1^st replica, it might show all sorts of critical errors but it’ll straighten itself out once it works out the primary is now the secondary. Close it and open the dashboard on the previously secondary replica and it should show all OK.

Everything is now being run by our 2^nd replica. Ping SP15 and you’ll see the DNS has updated too.

But SharePoint is Down!

Agghh! The farm has been floored! Nightmare!

Yes indeed. This is also why planned, regular failovers are a good idea because you can never be certain of what’ll happen on a failover until it happens. You’ll notice SharePoint may now be complaining that various key accounts are being denied login by SQL Server. Well that simple why; your farm accounts probably aren’t known by the 2^nd replica endpoint.

Solution: add the accounts to SQL as per this article (http://technet.microsoft.com/en-us/library/cc678863.aspx) and check everything is working as with both replicas being used. Make sure to check:

All web-applications (default web-app; my-sites)
All service applications (Search; UPA; Excel Services, etc.)

If something isn’t working with one replica active but on another one it is then there’ll be a difference in permissions. Testing is vital here; make sure you run through every service & app that’ll use different credentials and ensure each one works with each replica member in the cluster being the primary.

Important

Don’t change anything in the failover cluster manager! I’m told by my SQL friends this will render the cluster in an unsupported state as there’s all sorts of meta-data that Management Studio needs & reads which the Cluster Manager does not.

Wrap-Up

This has hopefully shown the benefits in mounting SharePoint in an AlwaysOn cluster and how that can work. Later we’ll experiment with some of the options AlwaysOn gives us and the farm configuration in this example is far from ideal, but the key stage explained here is how to mount the SharePoint databases at least.

SharePoint is increasingly becoming critical to have online; this is yet another way we can make sure it stays online!

Cheers,

Sam Betts

Multi-Subnet SharePoint Farms - High Availability SharePoint

2013-04-17T16:40:38Z

Why is Multi-Subnetting Awesome?

Having a SharePoint farm span across multiple subnets can improve the reliability of a SharePoint farm against networking failures. Reasons why a network could fail are mentioned below, but they are just another reason SharePoint can go offline and removing them as a factor is the focus of this post. We’ll describe the idea first, then there’s a demonstration below.

Introduction

When SharePoint falls over it’s not a pretty sight – more & more SharePoint is becoming an application platform on top of collaboration and data-storage and therefore it’s becoming increasingly important it does not keel-over unexpectedly.

Many farm outages occur when the transition from web-front-end to app-server isn’t possible for whatever reason but many other outages are because of communication breakdowns in the chain of communication from client to web-front-end-“server” (whichever server that may be).

It’s possible of course to make the “web-app to service-app” transition more resilient to failure by providing more endpoints in case one dies for service-app redundancy - this is nothing new for anyone familiar with SharePoint farms.

What I really want to explore is extending this thinking into a networking paradigm by stretching our single SharePoint farm into multiple subnets with service failovers in each subnet. This way any one network (and servers inside) could go down and our SharePoint farm could live on anyway – front-end and back-end. If you like, this post is “network considerations for high-availability SharePoint farms”, or rather how to leverage networking tricks to allow failing services to have a lesser impact. Services will always fail, the only question is our ability to react to that happening.

So let’s step-back a second and work out…

When do SharePoint Outages Occur?

Let’s rephrase that question slightly – what’s the minimum that SharePoint needs in order to give successful responses to client?

A working network for SharePoint + relying services to work together

A web-front-end.
A server that has all the services that the user web-requests require.
SQL Server.
Active Directory.

Connectivity & routing to the SharePoint network from the requesting client

Routing is a chain – if any parts break SharePoint won’t respond.

As you can see, the network is key to all of this playing nicely. If any one of the above fails, you have an outage and if you have an outage you have many hacked-off users, bosses, etc. We’re focussing on how to be able to survive a network outage specifically, or at least keep things usable until we can restore full service.

When do Network Outages Occur?

Many reasons:

Routing failure to/from (network inaccessible even if working internally).
Network/subnet flooding:

Bad driver/broken network-card
Broadcasting is contained to subnets

Cable interference & site disruption:

Broken or damaged cables
Unplugged or failing router(s) or switch(es)

Any one of those events would plunge a SharePoint farm into murky darkness if it happens to be in just one subnet or network, and most network engineers would tell you they’re not as uncommon as you’d think. Subnets are nice safety barrier for networking screw-ups – a fallout in one probably won’t affect the other, and we want to harness this for making sure our SharePoint farm stays online for longer.

A Typical SharePoint Network

This is a typical, basic network setup for SharePoint. A client from another location entirely connects over TCP/IP and HTTP to SharePoint and the web-front-end that gets the request responds calling whatever local network services it needs to formulate a complete response.

Blue arrows show HTTP traffic; orange is SQL, and green is Active Directory traffic. Any break in any one arrow is an outage to the whole system. And this is a bad thing, of course.

We know how little it takes for a failure to occur somewhere and there are several failure points in this simple diagram but in this post I want to focus on just networking for now so here’s how we can evade network failures from a network outage…

A Multi-Subnet SharePoint Network

This is a far superior solution for its redundancy capacity as now without impacting SharePoint we could suffer the following outages (aside from the usual AD/app-server failovers):

Route from site A à sites B or C. Assuming sites B & C can communicate this won’t impact application servers, only web-front-ends.
Entire network outage on either B or C (see below for diagram).

Of course, we’ve not just increased redundancy just because of the multiple subnet factor; the design of the above farm also gives x2 AD and SharePoint application servers too, for the multiple subnet design to be worth it. AD calls will use the server in the same subnet (depending on config) but WFE1 will at some point invoke APP2 for example as SharePoint isn’t site aware like AD so internal calls are done on a round-robin basis. SQL, being a failover cluster will use one subnet address or another depending on where the failover nodes are located but the idea is to have at least one failover node on each subnet with local routing between sites for inter-site traffic.

Regardless, we can now have a whole bunch of things die on us and SharePoint will carry on living (subnet 1 has died):

Routing Reliability

Improving the route-failure resiliency from client to SharePoint WFE is only as good as the chain to & from the client. Take our example in more detail:

If we have a routing/network failure in location X we’re doomed. If however Z or Y goes down (taking with it B or C respectively) we’re fine. The further up the chain we can split the routes the better and this depends purely on the IP allocation used. Obviously if the client’s ISP goes down then there’s nothing we can do (nor would we have to – everyone else should be able to access the site no problem) but if our subnets routes only split at the data-centre then equally the multiple subnets become less advantageous.

Subnet Failover in Action

Preparation

I’ve tested this on SP2010 just because my test environment doesn’t have the memory for a HA SharePoint setup in 2013. This is the same setup as the above diagram; x2 SharePoint subnets, web-front-ends & app-servers with each subnet containing an app + WFE + AD server.

Currently the client machine is configured to point at the web-front-end on subnet 1.We’re going to kill the router on subnet 1 so everything on that network goes dark, update manually the DNS for the client to point at subnet 2 instead (this can be automated of course), then watch to see how SharePoint survives.

Of course we have SharePoint configured so that both app-servers mirror each other for services-on-farm. Here’s the search topology in particular – notice the query component in particular is setup to failover to the 2^nd subnet app-server:

Here’s a search page working with all machines up:

Both WFE’s online, our host-header using WFE1, and search working perfectly (WFE1 and our host-name “SP14” use the same IP):

Subnet Outage!

Now to cause an outage: break router from client network to WFE1 network.

Sure enough, the client can now only see one WFE (notice SP14/WFE1 aren’t responding to the ping request anymore):

When we detect the error we change the DNS for the SharePoint URL (manually in this example) – “SP14” responds to ping:

Now we load the site. With the WFE alone obviously the content will load but the question is whether the depending service-apps will too as just web-front-ends working is a long way from having an operational SharePoint. Let’s test the search functionality:

Everything’s working as expected – the WFE has picked up the 2^nd application server for search and queried there as appropriate. If there’d been a problem we’d be looking at an error right now; restarting IIS would show an error if the restart happened after the outage (see below for why).

SharePoint will complain there’s an issue with a server, but will carry on regardless. It’s not supposed to be beautiful, just work well enough until you get the rest of the farm back online.

Caveats

Big one for search: if the new web-front-end server hasn’t loaded the application-pool for the website exposed to the users AND the search admin component was on the inaccessible server, the search service will fail even though a mirror has been configured. This is because the WFE needs the search admin component to get topology information to then know that there’s a local failover mirror. If the server’s sat around doing nothing with the SharePoint content-app not loaded your search service will never survive for traffic sent to that server.
On that note, if the only accessible app-server(s) aren’t running the services you need then obviously that’ll fail too. This basically means having duplicate servers & services for everything that’s critical.
Per subnet, it’s probably more realistic to have the destination IP set as a network-load-balancer rather than a single web-front-end. It’s done this way here for simplicity.
This depends on the SQL cluster failing over to subnet 2 successfully. It’s possible to have the cluster work out there’s been a network outage and failover automatically but the failover needs to happen if a subnet failure has occurred.
AD too needs to be working & each server have a local AD server as its %logonserver% environmental variable. No accessible AD, no SharePoint. Same with SQL.
In the search administration page you’ll notice various components say “Online” even when the affected server has died. Think of it more like “Could be online” – meaning you’ve not taken it down for maintenance :)

DNS & Supportability Issues – Important!

The whole theory described in this post relies on the client knowing the IP address for the SharePoint site has changed. This is a DNS issue and I’m leaving it out of this discussion for now, but needless to say a DNS update is critical to clients being able to locate the new web-front-end(s).

Finally, for a SharePoint farm to be officially supported the latency between machines needs to be 1milisecond or less. Don’t save money on hardware in other words :)

More info on supported network response times here - http://technet.microsoft.com/en-us/library/cc262485(v=office.15).aspx#hwLocServers

Conclusion

This hopefully showed how it’s possible to avoid some extra outage scenarios by designing your farm into a network-spanning zombie-farm that just won’t die. Well, never say never but you can at least make it less likely by providing redundancy this way. When downtime isn’t a possibility and there’s the investment to make it work, this is something that should be considered.

Cheers!

PSConfig System.InvalidOperationException - Cannot start the service SPAdmin on computer

2012-10-03T13:27:34Z

Yes, this has been covered already but this problem caused me real headaches on the last CU upgrade I did for my test farms and nothing I tried seemed to help. All the machines patched just fine but when it came to finishing the upgrade with the psconfig/SharePoint wizard operation it all collapsed on every machine, seemingly for no good reason.

Looking at the upgrade logs we see:

ERR                              Task configdb has failed with an unknown exception
ERR                              Exception: System.InvalidOperationException: Cannot start service SPAdmin on computer '.'. ---> System.ComponentModel.Win32Exception: The service did not respond to the start or control request in a timely fashion
   --- End of inner exception stack trace ---
   at System.ServiceProcess.ServiceController.Start(String[] args)
   at Microsoft.SharePoint.Win32.SPAdvApi32.StartService(String strServiceName)
   at Microsoft.SharePoint.Administration.SPWindowsServiceInstance.Start()
   at Microsoft.SharePoint.Administration.SPFarm.Join()
   at Microsoft.SharePoint.PostSetupConfiguration.ConfigurationDatabaseTask.CreateOrConnectConfigDb()
   at Microsoft.SharePoint.PostSetupConfiguration.ConfigurationDatabaseTask.Run()
   at Microsoft.SharePoint.PostSetupConfiguration.TaskThread.ExecuteTask()

Nothing really interesting there; the SharePoint admin service just timed out starting. However doing the same from services.msc also gave the same inexplicable timeout, even when changing the registry to allow for more time.

So to the point then; I’d seen from others this might’ve been an issue with network connectivity. Sure enough, when starting the service manually we see this network traffic coming from WSSADMIN.EXE:

In case you can’t see that’s basically the service sending TCP packets to two IP addresses (92.122.127.241 and 92.122.127.40), not getting anything back again, and retrying. A quick look up the conversation and we see where these IPs came from; the proceding DNS query & response show us what’s really going on:

The IPs belong to “crl.microsoft.com”; this being a certificate revocation URL for revocation checks . Since a while back SPAdmin will try to check with the Microsoft certificate authority for revoked Authenticode signatures as a security measure. This is normally good but when the machine in question has no line of sight to crl.microsoft.com then this fails; in this case causing a service timeout, and stopping psconfig from finishing the upgrade & bringing the server back online after a patch. This can have the unintended effect of leaving a half-baked SharePoint farm so is very far from ideal.

But Why Does SPAdmin Fail to Start?

Pinging the IP address from the machine in question tells us why - 92.122.127.241 for example. We get a response from a router saying the address isn’t accessible – that it has no route to that address. This has the effect of the host not replying whatsoever so the connection times-out. As is normal the connection is retried a few times, but the problem is this is all as the service is starting so Windows is waiting for SPAdmin to say “I’m started!” which it never gets round to doing in time.

Why the no-route? Well, just like many production environments we’ve blocked off these servers from getting further than the internal router unless it goes via a web-proxy. Standard security stuff – anything not proxy aware stops at the network boundary, and anything through the proxy is scanned for security threats in a single point of control.

Solutions

Anyway, I needed a quick solution to this problem as I’m sure many other have too. CRL checking can be disabled in group-policy as far as I’m aware (I’m not a domains engineer) but even if that was true, all my machines were down and needing a quick & dirty hack to bring them back to life again. Even worse, this error was happening on newly installed VMs too just trying to join the farm.

The (Dirty) Solution

Add crl.microsoft.com to the hosts-file of the failing machine to point to 127.0.0.1. The service then started fine and the upgrade could complete. This isn’t ideal and I don’t recommend it as a long-term solution but it should work well enough to get up & stumbling again. If for some reason this doesn’t work try clearing DNS cache and repeating. If that doesn’t work use the same network tracing process to figure out what the service is trying to check against and for each DNS host, add a loopback address in the hosts file.

Either way this hack works because now we get to the target host for any revocation check (now ourselves), so the request to check revocations is replied with a concrete “I don’t know what you’re talking about” network response as we’re now hitting something rather than just a router. This means of the start-up revocation check is quickly failed instead of hanging waiting for a reply from no-on, and finally SPAdmin can complete it’s start-up in a timely fashion.

Another Solution (for Upgrades Only)

If this is failing only to complete an upgrade; another option is to run psconfig with the –force parameter. This won’t obviously help when adding a new server but it will do the job of completing the upgrade at least.

Happy SharePointing!

SharePoint 2010 Apps for n00bs – Forms

2012-04-19T15:06:21Z

By this point in you should’ve seen the default form engine that SharePoint provides already and most likely you would’ve thought “not bad, but I bet it doesn’t do exactly what I want it to do”. You’d probably be right; the normal engine just tries to best-guess the presentation of the fields and other screen elements based on the meta-data of your list – the field sizes, descriptions, types etc. We saw some forms already; here’s one:

Anyway, the good news is that you can build your own forms if you want - InfoPath forms in this example, and then use those instead of the out-of-the-box default forms. A nice thing about InfoPath forms in general is that they’re cross-platform; in that they can work on the web or in a rich-client if you so wish, and also you can auto-generate forms from SharePoint lists so it doesn’t take much work to get something up & running. Multiple forms can be associated with a single list but we’re just going for the one for now, because we’re n00bs.

Editing Thy Forms

If you want to edit just the normal SharePoint provisioned forms, you can do so. Open SharePoint Designer again & navigate to the list you’re interested in:

These forms are provisioned with your list automatically, meaning when you create a list, these files are created too for this list-type. Other form templates get previsioned with other list-templates but that’s waaaaaay too advanced for today. The point is that if you’re feeling brave you can just directly edit these files that exist already for your list; doing so won’t affect anything other list.

Click on one of the forms, and the SharePoint Designer editor will load it in design mode – from here you can edit it in the GUI or the underlying code…

…although if you really want to hack the page, you’ll have to do so in “advanced mode” which is basically a button that says “I’m not a n00b – let me break my site if I so wish”, but as you’re reading this guide, you probably are a n00b for now so I’d not recommend it normally. There’s a code-view for editing the raw ASPX and everything, just like in Visual Studio.

Our First InfoPath Form

Anyway all of that’s too complicated - we don’t want to modify the existing forms, we want to create new forms with InfoPath to override the old completely. We’ll start with the “New Sale” form because, let’s be honest; it’s a bit bland. Plus we want to through on some extra validation in there (i.e. don’t sell what you don’t have).

So in the list setting in SPD, click the “Design Forms in InfoPath”, then click on “item”. The reason it says “Item” is obviously significant (we’re talking about the “content-type” for this list) but that, like many other areas isn’t for today. Edit the “item” content-type.

By the way, in case you hadn’t figured it out yet you obviously need InfoPath Designer installed (and the enterprise edition of SharePoint 2010). Clicking this button will open a new form in InfoPath Designer and pre-populate all the list-fields for you, which is awful friendly I thought.

On the right we see a list of fields available to us, and the main bit is the auto-generated, best-shot version InfoPath Designer has of it just to get started with. We however want to improve this.

First, we want to get rid of “sale confirmed” from this form – it’s something the workflow fills in, not the user so select the bottom row like so:

…and hit the delete key. Goodbye field we don’t want to edit.

Next, we want some validation on “customer email” to actually check we’re entering a real email address. Click the field, then add a rule like so:

This will add the following rules:

…and that’ll do; in just a few clicks we’ve significantly improved on the default SharePoint form for our own InfoPath-generated one.

The only thing left to do now is publish it to our SharePoint list (specified in the bottom-right corner of InfoPath). This is a fairly unemotional affair – click on the “file” tab, then “Quick Publish”.

Doing so will show this:

Done! If you look at the “forms” view of your list now in SPD, you’ll notice a few more files in addition to the forms you had before. These are basically ASPX wrappers to InfoPath form you’ve created here, and these will be set to be the default for your list – all by magic.

So now when we go to our sales list and click “New”, we get our much-nicer form…

Everything works as expected – all the hard work is actually done by the InfoPath form wrapped into an ASPX form. For the record InfoPath forms can be integrated into and interacted from all sorts of things; emails, apps (and app-panels), pages, and mobile phones, and more – it’s a very powerful form technology.

Wrap-Up

We’ve looked at 2 ways of customising forms here; the first one by editing the out-of-the-box SharePoint built forms in SharePoint Designer but also very briefly at the InfoPath route of replacing the list forms for our own InfoPath built ones. InfoPath is a powerful form engine once you get to know it, and easier too – play around with it; we just skipped over the very basics for today to get used to the idea of being able to customise forms, but it is a fully-featured form engine with all sorts of exotic goodness you can build into your forms for validation, display, etc.

Happy SharePointing!

SharePoint 2010 Apps for n00bs – Reports

2012-04-19T15:04:34Z

This part assumes you have done the “data” part of this workshop – here. If you haven’t, you can do similar with your own lists of course.

This workshop & the one on data is also in video form - http://youtu.be/iyQmam_fvdg

Reports in Excel

So it just so happens, SharePoint supports hosting an online version of Office inside a web-browser. That’s pretty handy because Excel was built to visualise large amounts of data, and actually can pull data out of a variety of external sources – SharePoint lists included. So the idea is simple – get Excel to generate reports based on external SharePoint data (our lists), make it all nice & pretty, but then put the Excel sheet inside our SharePoint site so we can see it all together. I bet you didn’t see that one coming!

Reading List Data in Excel

For any given list, we can work with the list in Excel if we want. As it happens, Excel’s got some pretty handy data visualisation abilities so I figured why reinvent the wheel, let’s do our reporting there. Click “Export to Excel” and SharePoint will export an IQY file (Excel Web Query File) that tells Excel to open a data-source from a certain location (our site/list) – much like SQL.

Open the web-query file; Excel should know what to do with it. A web-query file is just meta-data for Excel to know what data to grab and from where – SharePoint generates it automatically.

We now have a blank Excel spread-sheet with 1 data-connection to our SharePoint list, waiting for customisation & saving.

Generate Nice-looking Sales Report

Just seeing list data is a bit rubbish so now I’m going to knock-up a quick report to show what product has sold best. Given we’re in Excel-land this isn’t exactly rocket-science, but let’s run through it anyway…

Insert a new PivotTable:

Now we’re going to use the SharePoint data-source to base the table on so select is as below:

…and say OK. Now we need to say which fields we’re interested in; in this case, what was sold & how many. Thus giving us this:

Yay! Stats and stuff! Now for the finishing touch…

No report is complete without a 3d pie-graph thrown in for good measure:

Now one last things; let’s sell another cat-food bag and see the report update itself (although this time I’m doing it from the standard view – just because I can).

And now in our report when we refresh the data-connections we can see the cat-food count rise and our graph update itself. Magic!

Save Report in Application

Now we need to put the report in the site so we can open it from anywhere. To do this we want a “Document Library” to store it, so in the browser click “Site Actions” and then select “New Document library”. Fill it out to be like so (click to see more options if necessary):

Then in the list, let’s add a new item – our saved Excel spread-sheet.

Once it’s done your list should look like this:

Now it is possible to open the spread-sheet directly in the browser, in SharePoint but that’s for another day – for now we just want to be able to save it centrally.

We’re done!

So that’s about it for now; here’s some customisation I’ve done to the homepage from the web-browser, just because I can:

Here’s a customised homepage thanks to some web-parts I threw onto the page & a picture of Tristan the cat. Play & tweak this to your heart’s content.

Wrap-Up

Excel is a great way of presenting data in general, and SharePoint allows Excel to be embedded directly in your site. How marvellously convenient!

SharePoint 2010 Apps for n00bs – Workflows

2012-04-19T15:02:18Z

Another integral feature in SharePoint is the “workflows” concept that has been baked into the system since ye olde times and they’re basically just data-oriented application processes that can run automatically (or manually) when data changes. Specifically, they normally run important data operations that define how the business operates. Workflows are normally associated with a list, are triggered automatically when data changes in the list and the idea is they are basically automatic processes for the business that run as autonomously as possible.

For our pet store we’re going to try automating a key process – stock control. In plain English we want the following process when there’s been a new sale:

Subtract store quantity for sale amount in inventory
Warn people that care if stock reaches a low limit
Confirm sale is OK (i.e. someone else didn’t take the last item(s) just before you).

All of that we want to just happen when we add a new sale item in our list.

Demo App Prerequisites

For any of this to work we need to add a new column to our “inventory” list – “quantity in store”. Also we want a new column on “Sales” – “Sale confirmed” (yes/no) to be populated by the workflow itself. Create these columns and add some fake quantities to your stock items, otherwise none of this example stuff will work.

What Will the Workflow do?

We want stock to be automatically taken care of so to be precise, we want a workflow to do the following when someone makes a new sale:

1. Check we’re only running the workflow on “new” items only.

o Done by comparing the “created” and “modified” dates on the “current item” (workflow talk for “item that’s changed & the reason I’m even running”).

o We want to do this because it is possible to set workflows to only run on “create” but there is the possibility it can be triggered other times too – like manually for example, so we want to take this into account.

2. Update the stock-levels as appropriate.

3. Email some people when stock gets low.

Creating the Workflow

First up we’re creating a workflow for a list – specifically “sales” and we’re going to do it in SharePoint Designer (SPD) again so open SPD & navigate to the “sales” list we created back in tutorial 1. You can create completely custom-coded workflows in Visual Studio but that’s way too advanced for this tutorial so for now we’re going to use the very simplified SharePoint Designer workflow Designer – it’s so easy even my grandma could manage it.

The first thing to notice is there are no workflows for this list, but that’s where we’re going to see any we make & publish for the list.

To begin making a new workflow, click this button:

You’ll be asked to give a name for the new workflow; fill it out something like this:

You’ll now be presented with this, a blank editor for our 1^st workflow!

Structure the Workflow Tasks

The first thing I want to do is outline our three major actions this workflow will do:

Load & validate what just happened.
Update data
Notify users of low stock

So let’s do that as 3 major “steps” – click the “Step” button in the toolbar. Bear in mind whatever you insert will be inserted wherever the orange cursor is set, so don’t do this…

It is possible to nest steps in steps; for now just put 3 steps in like so:

Note: you can click on the step title to change it. Amazing! Next, to actually put some “code” in.

Load & Validate the Data

As we said before we want to load & validate the data we’re passed before doing anything; specifically we want to check the “created” and “modified” dates on the “current item” are the same so let’s add a new condition in the 1^st step:

…which should give us this:

Now those two links are clickable, and need to be. Because we inserted an “If current item field…” then SPD knows we’re talking about the current item.

Current item by the way just means “Item this workflow is executing against because a [verb] just happened” – where [verb] could be “insert”, “update”, or even “manually launched”.

So it should be self-explanatory at this point, but select “created”…

Next we’re only interested in taking a branched action if the dates don’t match, so click on “equals” and change to “not equals”. Now we need to specify is what “created” needs to be compared against so click “value” link and you’ll see this:

Here we can specify either a function or a concrete data lookup to compare against. Seeing as we’re comparing a concrete lookup (same item, different field) click the “fx” button and fill out the next form like this:

Click “OK” and your workflow should look like this:

Congratulations, you’ve made your first “IF” statement. Yay! Next to make something happen when our condition is met.

Set the WF cursor to inside the condition block and type (without quotes) “stop”, like so:

…and press ENTER. SPD will set the command and allow you to pass a “message” parameter – click it and set a message like “Oh dear, this wasn’t a new sale”, like below:

Notice you’re using the string-builder here – you can mix dynamic (data) & static (text) elements here.

Next for the next step (Update Data) we’re going to just output some text for now & test our not-really-so-awesome-yet workflow so add the following step (Log to History):

Let’s insert the item being sold name into the log too, just because we can:

Now when you click ok, we want to save the workflow.

Save & Publish Your Workflow

So anyway, let’s test it now so we can see how workflows even look like. But before we do any of that we need to configure when our workflow is run automatically so click on the original workflow to get back to its configuration, as so:

From here let’s set the WF to be triggered on new & update – if only to test our infallible sanity checking coded into the WF itself:

Now we want to publish the workflow now everything’s ready for our test-run. What’s the difference between saving & publishing? Save will stop you losing any work; publish will actually make any changes since the last publication live – if you’re ever wondering why your workflow seemingly ignores you, it’s probably because you’ve only saved it. So click “Publish”!

Let’s do a Test-Run

Now we have a published & primed workflow let’s see the awesomeness in action. In the browser, go to your “sales” list. Add a new item; fill in anything you want – we just want to invoke the workflow which we can see when we do:

That column shows the status of the workflow (it’s actually a virtual column but more on that another day). Click on it and we can see the result of that workflow. Note that sometimes you have to refresh the list in browser to see “Completed” reported.

Here we can see what route our workflow execution took – the description of the sold item is a bit odd as in this example, to save some time we’re just flattening out the related record to text, which includes all the ID field too hence the weirdness. It’s possible to grab a specific property; we’ll look at that later – the important thing is we see some kind of audit/tracing going on with the data we’re reading.

Anyway to test the sanity checking of our workflow, go back to the sales list but this time edit the sale instead of creating a new one.

When we edit a sale, the history shows us this:

Tada!

Read in Inventory Stock

So now we know our workflow works and we’ve covered how to debug them, let’s actually get it to do something useful – the first step of which is actually getting the WF to do what it says on the tin – update the stock level, but to do that we need to know what the current stock-level is for our associated inventory item.

Now first, let’s create 2 variables – one to hold the current stock level, and another to hold a new stock level. The first one isn’t desperately necessary but holding the current level in a variable means we can also log the value to the workflow history for debugging purposes.

Anyway, we create the variables in the “Local Variables” dialogue. Add like so:

Now once you’ve added these two number variables we’re going to set the “current stock level” var. Set the next action in “Update” data as “Set variable” by typing “var” and pressing ENTER like so:

When you press ENTER you’ll be able to select which one; pick the “Current Stock Level”.

The next step is where it gets interesting, because we’re reading from a record that isn’t the current one or even in the same list – we’re reading from our inventory list. This means we have to tell SharePoint how to find the inventory record we want. Anyway for now, set the “lookup” for our variable setting as so:

This tells SharePoint Designer which value from the inventory list we’re going to insert into the variable. Next we need to tell SPD how to find the record we’re interested in which we do in the 2^nd part of this dialogue “Find the List Item”. In short, we’re going to find the inventory item by ID as it’s a unique value always so where it says “Field” select “ID” – the fields in this list are from the inventory list. Next we need to match inventory ID to a value in the current item and we do this by clicking the “fx” button at the bottom-right (the lookup button). Make your form look like this:

…and say “OK” to all forms. To make sure the variable is being read in ok we can output the value to the workflow history if only temporarily.

...and here’s the result:

This is an important step; it shows we can debug our workflow if necessary (it will be if you get into writing these things).

Set New Stock Level

So let’s delete the last “log to history” task because we know it now works…

…and now let’s add a new command “Calculate Variable” – this is where we’ll do our super-complex maths of “New stock level = old stock level – order quantity”. So click in the right location:

(Starting to type while the cursor is here will enter a new action)

The command is “calculate”, and we take the variable “Current Stock Level”, minus the “current item (sale) quantity” and output to our other variable “New Stock Level”.

Finally, one more step – “Update List Item” the action is called.

We’re updating not the current item, but another record – the sale item. We need to locate it – just as we did before and also set the new stock level quantity to our variable value.

Your WF should look like this now:

Let’s test it!

Testing Stock Updates

As before let’s make a new sale, but let’s also make sure there is some stock first…

If we buy 4 of these, there should be 2 left, right? Right?! Let’s save our workflow and test it.

Well this is odd; the workflow’s complete but it’s not updating. Why?

Well because we didn’t publish the changes so it’s our last-published workflow running still. A common mistake to make, worry ye not.

So publish it and try again…

Yay!

Send Email Notifications

Last things to do then should be easy.

1^st thing in the “notify users” block – an “if” statement that says:

“If there are less than 3 items left in inventory, alert people with an email.”

So insert a new condition, and in the condition block add “email” action (without specifying how/who) like so:

Click the “these users” link to begin defining an email to be sent automatically. Here’s where we’re going to generate a nice automatic email to send to a group of people with auto-generated messages just like we did above. Fill it all out like so, adding lookups where we need dynamically inserted data:

Now in the “to” field, click the address-book. This will show all the contacts relevant to this site; we can email 1 user in the system or out, or a whole group, or several combinations of the two. Let’s email anyone in the Pet Shop “owner” SharePoint group.

That’s it; the SharePoint emailing robot (assuming it’s been setup by your administrator) will now do thy pet-shop bidding.

Set “Sale Confirmed” Flag

Finally, and as a last step we’re going to set the “Sale Confirmed” flag as “true”. You’ve done this already, but here it is:

This last step means we’re assuming that if we got there, it all went well. It’s something of an assumption but this isn’t a lesson in system design so it’ll do for now. Publish & test…

If the email doesn’t arrive, there’s not much more you can do than speak to your SharePoint administrator – it’s setup at a farm level so way, way outside your control in this instance. You as the programmer though don’t care; someone else can deal with actually making the email arrive where & when it should.

Wrap-Up

That’s it for now. The idea was to give a basic idea of debugging, decision-making & performing actions all automatically for a list. You should have a basic understanding of how workflows work. For serious workflows that SPD can’t/won’t be able to do, there’s always Visual Studio too but that, like many other subjects in SharePoint, is for another day. Congrats on programming your first workflow!

SharePoint 2010 Apps for n00bs – Data and Lists

2012-04-19T14:56:00Z

We’ll start off by running through the very fundamentals of any application – storing data in lists, with some basic interfaces to get & set the data too. In SharePoint land, unlike SQL, we always have a basic interface to any list with the out-of-the-box forms SharePoint will provision for any given list store. This is nice because sometimes writing a GUI for doing it ourselves can be a pain, especially if you rarely ever want to interact with the list so can’t be bothered to write one especially. Incidentally all lists will always have automatically various web-service interfaces to interact with them too, but that too is for another day.

There are many types of lists – Document Libraries you’ve probably heard of for example, but all lists effectively inherit from a base “List” implementation so that’s what we’re going to build for now.

This workshop & the one on reports is also in video form - http://youtu.be/iyQmam_fvdg

Steps to Build a SharePoint Application

There’s multiple ways to build things in SharePoint. The most common & easiest way is to use the web GUI to do it so that’s how we’re going to start; later I’ll dive into how to programmatically do the same but for now it’s a case of:

Create a (blank) site.
Create lists to hold products and sales.
Add product/sale data.

Create a Site

From Central Administration, I’ll create a blank site for my application. There’s only 1 user, me, AKA “Administrator”.

My rather barren looking site. Even with the “blank” site we get various master-pages and a default page as seen above; if a site was truly blank, you wouldn’t see anything in a browser. You could think of the different site-types as different application templates – we want enough functionality that we can use the default SharePoint GUI to play around and create more things, but little else.

Create lists

I’ll do the next bit in SharePoint Designer 2010 (free download, also called “SPD” for short) as it’s just quicker. From SPD click “Open Site” and enter the URL of your new site, in this case http://sfb-ntlm-ad:39712/sites/PetStore (or you can click “Open in SharePoint Designer” from the web-browser site-actions menu).

Our new site in SharePoint Designer (SPD). Already some basic security & UI functionality is provided (master-pages etc.).

In “Lists and Libraries” we want to create two custom lists, “Inventory” and “Sales” – custom just means “blank” in this instance.

Once you’ve created both, let’s edit one. Click on it in the list (of lists), then click the toolbar button “Edit Columns” to add any missing columns (“Title” & others are created by default)

Let’s add a “price” field:

Now edit the column settings:

Done; the full list schema is as follows (with hidden out-of-the-box columns too):

Now do the same, but for “Sales”; making custom-field “Game Sold” a “lookup” field to “Games” as follows:

Your list should look something like:

Note: I renamed “Title” to “Customer Name” as all lists in SharePoint-land have to have a title field as every list inherits from a base content-type “Item” which has this field, but more on that another day. In short, you have to have it in one form or another, even if you don’t use it. Apart from that I’ve set “Sale Date” to default to today’s date and “Quantity” to default to “1”.

Now you’re done in SharePoint Designer. You can do the exactly same list-creation process via the browser too, but just because there’re fewer clicks so I chose to do it in SPD.

Add some data

I’ll do this in the GUI; there’s a great datasheet view that speeds this up greatly. This is one of the many things that come even with blank sites, but more on that later.

Open/refresh the homepage again, navigate to the Inventory list and then click:

Select “Datasheet View” & enter data like you would a spread-sheet.

Please note, I have no idea how much dogs/cats actually cost, however I did learn there is such a thing as a “Munchkin cat”. You learn something every day, thanks Wikipedia.

Now do the same for “sales”

It turns out the Munchkin cat is very popular. Who knew?

Wrap-Up

You’ve created your first lists to store and link data - well done! It’s not rocket-science of course but it is important obviously, and this is just the absolute very basics. What we’ve done here you can automate of course – the same way you don’t need a GUI to create SQL Server tables, and as for the data-input/forms of the data mentioned – don’t worry if you don’t like them, you can change them but we’re looking at that separately in the “Forms” demo where we’ll customise and modify the forms SharePoint has given us by default, but for now just getting a reasonable schema in and some data will be enough.

SharePoint 2010 Apps for n00bs – Introduction

2012-04-19T14:55:18Z

Hello! Welcome to my blog section “SharePoint 2010 Apps for n00bs”. This mini-section is all about building SharePoint apps obviously but the bigger point is to highlight where SharePoint can be used to build your generic app with faster & better than traditional development frameworks. Given I’m assuming no experience with SharePoint previously I’m starting at the very beginning – something even one’s Grandma could follow, if she were that way inclined.

Most of SharePoint apps are designed for a web interface but it’s ultimately just an apps platform; one that can be extended into mobile, rich-client apps, or you can use SharePoint to just build web-service end-points. My focus in this is going to be web-based apps and as I said we’re going to start very basic (oh so basic) with objects created manually by hand and build up from there. No custom-code; no complex solutions - the idea just for now is to build a simple application using as many major parts of SharePoint as possible to get an understanding of what the platform is good at doing, then to look at automating some of it all later. A bit like how you can build a SQL database in a nice GUI, or you can generate your own scripts to do the same – for now we’re living in shiny GUI land.

So What do SharePoint?

We have 4 major focus areas we’re going to start with to demonstrate building apps in SharePoint. In this blog there are blogs & video-blogs for:

Pretty much any system of any type needs all four of these activities; my idea is to show you how SharePoint does them in its own unique & awesome way. At the end of all the workshops/demos we’ll have a basic system that provides some functionality you would otherwise have to write by hand, except it’ll take us no more than 30 minutes. And that my friends is why SharePoint rocks – because you can build this stuff very quickly if you have to. Sure it’ll be rough at the beginning but some very shiny sites run SharePoint – perhaps none more so than http://www.ferrari.com as just one example but just for now we’re covering the basic building-blocks – later we’ll look at making Ferrari-type systems in SharePoint too.

But Why Build on SharePoint?

There are lot’s of reasons; speed of development is one, but my favourite would probably be that the programmer doesn’t need to be a system admin too. Your code is the same on 1 server as 100 – good programmers tend to make rubbish system admins and visa versa (I’m generalising of course), so it’s good to keep the two camps separate if possible and SharePoint does that nicely.

Genius code & content reuse is another, but that’s for another day.

As this is a series for “n00bs” (newbies/new people) we’ll going to do everything by hand. Later we’ll look at automating all of these and how it’s possible to reuse functionality in SharePoint in very cool ways that set it apart from other app-building frameworks. We’re looking at stuff that’s just using the SharePoint foundation framework principally – if you want to try this out you can either get SharePoint 2010 Foundation for free (not everything we do here will work in the freebie version), or get yourself a SharePoint online account for “proper SharePoint”.

Example Application (The Pet Shop)

For all of these demos we’re going to be building a very simple application. Let’s pretend we have a friend that has a pet store and we had the genius idea of automating his paper-based sale tracking system. So anyway, for a first version it needs to do the following:

Keep track of inventory in the store – food, actual pets, etc.
As above, but for sales too.
Show basic report of what has sold the best.
Automate stock-control.
Not look & feel terrible.

This is very basic indeed, which is perfect for this series. For the time-being, a sale just consists of entering a person’s name & email-address + quantity into a basic table that relates to an item.

Anyway, the result needs to be that our friend could run his pet-shop better with a SharePoint cat-themed application even a n00b could build. Do not underestimate the power of learning with cats.

Platform Comparison

To get started let’s have an idea what it would take to build our pet-store with normal programming languages. Remember, SharePoint isn’t looking to replace these but just in some scenarios it’s quicker/better and that’s what this is all about. There are some situations where SharePoint would be a nightmare to use over some other languages but for some stuff it’s a breeze to do in SharePoint land.

Building in Bespoke

If we were to hack this out in ASP.Net for example, this application specification would imply the following development work:

Storage in a database.
Code to save & load from it DB.
Custom written forms to interact with the data.
Reporting tools – either implement existing or write bespoke.

The data structure would look something like this (with ID fields, created & last-edited dates too):

This isn’t much, but certainly this is not an insubstantial amount of coding to do would it be done in ASP.Net or PHP or whatever is your programming language/framework of choice. Here’s how the application would be built if it were bespoke; the textbook N-tiered application setup, just like they taught you at college:

Building in SharePoint

Building this same model in SharePoint however is much easier - it’s all there with the toys baked into SharePoint 2010:

Click on each subject to learn how to do it, although if you’re really new to this you might want to start with “lists”.

Anyway, enjoy!