Archive for June, 2011

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

After many-a-requests I’ve decided to do an article on how you can work with an Azure-hosted SQL Server and consume that data in SharePoint 2010.

Related articles for working with external data in SharePoint 2010:

A few introductory details about this article…

In this article I will discuss and guide you through how you can utilize the power, scalability, flexibility and awesomeness that comes with the cloud. By following this article you will get an introduction to how you can work with SharePoint together with Windows Azure to store business data.

This article will be an introduction to developing SharePoint solutions to work with Windows Azure, and in later articles I will discuss other approaches where Windows Azure may be a good solution to incorporate in the plans for your organization together with Office 365 and SharePoint Online.

Please note that this article is NOT intended to be an introduction to setting up Windows Azure. Its an introduction to setting up the connection from SharePoint to SQL Azure. More in-depth articles are coming up later.

Prerequisites

In order to follow along with this article and repro these steps yourself, you will need to have the following things in place already:

  • A Windows Azure developer account
  • An SQL Azure database and a table in that database
  • Visual Studio 2010
  • SharePoint Designer 2010
  • A few sprinkles of awesomeness in your pocket would be nice, just for fun

Please note that in SQL Azure you’d need to hook up the IP-address of the machine running this code or service in order to enable it for connectivity with the SQL Azure database. You’ll see more about that in your SQL Azure portal.

Connect to SQL Azure using Business Connectivity Services in SharePoint 2010

In this section I will talk about how we can create a connection to our SQL Azure database from SharePoint by utilizing BCS. I will for the ease of demo use SharePoint Designer to set it up – and to prove that it works!

1. Make sure you’ve got existing data in one of your SQL Azure databases

In my setup, I’ve got a database called ZimmergrenDemo and a table called ProductSales. I can access the database either from the Windows Azure Platform portal or directly from the SQL Server Management Studio:

image

image

I’ve got some sample data that I’ve popped into the SQL Azure Database:

image

2. Setting up a Secure Store Service configuration for your SQL Azure connection

In order for the BCS runtime to easily be able to authenticate to the SQL Azure database (which is using different credentials than your Windows Server/Domain), you can create a Secure Store application and use that for authentication.

1. Create a new Secure Store Application

Go to Central Admin > Mange Service Applications > Secure Store Service > New

Create a new Secure Store application, looking something like this:
image

2. Configure the Secure Store application fields

I add one field for User Name and one for Password, something like this:

image

3. Add the administrator account(s) needed

image

Voila! Your Secure Store application is setup, now let’s move on to working with the data in our SQL Azure database.

3. Working with the data though Business Connectivity Services

Now that the SQL Azure database is available and your Secure Store application is configured, it’s time to get the BCS up and running with SharePoint Designer.

The first and foremost option to get up and running quickly is of course to hook up an External List and be able to see your data straight through the standard SharePoint UI.

For a detailed step-by-step instruction for the whole routine to set up a new BCS connection, please refer to my previous articles.

1. Configure the BCS connection using SharePoint Designer

Launch SharePoint Designer and create a new External Content Type and select the SQL option for the data source. Enter the information to your SQL Azure database and the application ID for your Secure Store application.

Connecting to your SQL Azure database through BCS via SPD:
image

Since you need to enter the credentials for your impersonated custom identity (the SQL Azure database credentials) – you’ll get this dialog:

Enter the credentials to your SQL Azure database:
image

Once that is taken care of, you will be able to follow the normal routines for configuring your BCS connection.

My SQL Azure database, right inside of SPD:
image

2. Create an external list and navigate to it in your browser

In whatever way you prefer, create an external list for this External Content Type and navigate to it. You will probably see a link saying “Click here to authenticate“.

Click the link, and you will be provided with this interface:

image

I probably don’t have to explain that this is where you’ll enter your SQL Azure User Name and Password to make sure your BCS connection authenticates to your SQL Azure database properly.

Okay, when the external list is created and you’ve configured the authentication – you’ll see your data flying in directly from SQL Azure into your SharePoint external list for consumption!

image

And as always, the coolest thing here is that it’s read and write enabled straight away – you can work with the items in the list much like normal items in any list. Sweet.

Consume the data programmatically from SQL Azure instead

If you don’t want to go with the BCS-approach and just do code directly instead then all you need to do is make sure that you wear the developer-hat and start hacking away a few simple lines of code.

Working with SQL Azure is like working with any other data source, so there’s really no hunky dory magic going on behind the scenes – it’s all just pretty basic.

Here’s a sample Web Part I created to collect the data from SQL Azure and display in SharePoint 2010.

image

Here’s most of what the code could look like:

     public partial class VisualProductSalesUserControl : UserControl
     {
         private const string connectionString = "Server=tcp:YOURSERVER.database.windows.net;Database=ZimmergrenDemo;User ID=Username@YOURSERVER;Password=myAwesomePassword++;Trusted_Connection=False;Encrypt=True;" ;
         private string selectCommand = "select * from ZimmergrenDemo.dbo.ProductSales;" ;
         private DataTable productSalesData = new DataTable ("ProductSales" );
         protected void FetchAndFill(string connectionString, string selectCommand)
         {
             using (var connection = new SqlConnection (connectionString))
             {
                 var adaptor = new SqlDataAdapter
                 {
                     SelectCommand = new SqlCommand (selectCommand, connection),
                 };

 

                 adaptor.Fill(productSalesData);
                 salesGrid.DataSource = productSalesData;
                 salesGrid.DataBind();
             }
         }
         protected void Button1_Click(object sender, System.EventArgs e)
         {
             FetchAndFill(connectionString, selectCommand);
         }
     }

 

Summary

In this article I talked briefly about how you can connect to your SQL Azure database using BCS and then utilize that information from SharePoint – or create a custom solution to access the data.

The reason for this article is to show you that working with Azure isn’t a big and scary task to take upon you – it’s actually all very straight forward!

Enjoy.

Office 365 – Part 2: Getting started with Office 365

June 12th, 2011 by Tobias Zimmergren

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

o365_thumb[22]

In this series I will cover the aspects of Office 365 briefly and of course dig deeper into the SharePoint-bits. The article series is meant to give you a heads up about Office 365, and of course give you the information you need to get started with the services offered!

Related articles in this article series

Part 1: What is Office 365 and how can my organization benefit from using it?
Part 2: Getting started with Office 365

(This section will be updated as new articles are produced)

Getting Started with Office 365, step by step!

In this section I will briefly guide you through the creation of an account in the Office 365 beta.

Sign up for the beta

Step 1)

Visit http://www.microsoft.com/en-us/office365/online-software.aspx and click the big green button saying "JOIN THE BETA":
image

Step 2)

Next, choose the preferred type of subscription – Small Business or Enterprise:

image

Step 3)

Fill in your company details and preferred domain names and just follow along with the guide:

image

Step 4)

If everything went well, you should see this dialog appear for a little while:

image

Step 5) Take a look at your new Office 365 beta account, up and running:

image

This scenario literally took me less than 2 minutes to do. That’s right, I’m saying that I got up and running with Office 365 (beta) in less than 5 minutes and am now totally in control – without the need of any hardware or deployment configurations. Awesome.

Note: You can see that the services are saying "Setting up <service name>… this may take a few minutes" – this is done in the background and takes literally just a minute or two and you’re all done – and all configured to get started with the Office 365 beta.

Accessing your new services

That’s it. You’re done.

When the background tasks have completed and your Exchange, Lync and SharePoint online bits are configured – you can access them easily by clicking the links below their titles like so:

image

Configure your computer to run the newly setup Office 365 services and apps

In order to properly configure all services for your machine, you should click the link "Set up now" next to the first bullet point on the landing page:

image

Next, you just simply follow the instructions neatly presented to you and install the Lync software as well as the Office desktop apps and configures them to work with your Office 365 subscription.

image

Summary

That’s it – there’s nothing to it. A few clicks and you’re up and running. There’s really no reason for NOT checking this out. True story.

Anyway, since I’ve been getting way too many e-mails asking me about how to get started with Office 365, I thought I’d post a detailed brief here for you. Done.

In the next article I will give you and administrative tour of the Office 365 admin center – the place where you can configure the general settings for your Office 365 subscription.

Enjoy.

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

o365

This is the first post in a series about Office 365. In this series I will cover the aspects of Office 365 briefly and of course dig deeper into the SharePoint-bits. The article series is meant to give you a heads up about Office 365, and of course give you the information you need to get started with the services offered!

Related articles in this article series

Part 1: What is Office 365 and how can my organization benefit from using it?
Part 2: Getting started with Office 365

(This section will be updated as new articles are produced)

Office 365 – An overview

In this first article in a series of many, I will cover some of the fundamental aspects of what Office 365 is. We will take a look at why your organization might want to start looking at Office 365 and we’ll take a look at what services are included in the package(s).

What is Office 365?

If we’re talking about Office 365 in general it’s all about being connected and be able to work virtually anywhere. In my personal opinion, it’s a huge step in the right direction for getting all those on-premise installations out of the door and into a hosted scenario where you don’t need to consider the hardware and maintenance as much.

With Office 365 we can basically ease our IT-departments of the pain of administration and support for these types of servers – no hardware and no patching or upgrades needs to be taken care of. Microsoft will do this for us which is totally awesome.

Put simply its a secure and reliable Microsoft-hosted solution for collaboration, communication and productivity!

One of the best things is that you can in a matter of minutes have your organization hooked up with all the core services of any organization – connectivity!

How can my organization benefit from using Office 365?

For small to mid-sized organizations I have already seen an upturn in the feedback and demand for a package deal like Office 365. For me and my organization, there is no better solution to keep my communication and collaboration a-float. I basically clicked a button and then I had my Exchange Server for e-mail and a hosted SharePoint team site up and running within minutes.

Most importantly, you don’t need to worry about purchasing costly servers and spend hours, days or weeks configuring, monitoring and maintaining those machines. With Office 365, all the administration and management of the hardware is taken care of by Microsoft in their data centers across the globe.

Key points include

  • Hosted services
  • Reduced costs
  • Reduced routine management of your environments (Patching, Upgrades, Hardware, HW Failures etc…)
  • Increased availability

What services are included in Office 365?

The following awesome services are included in Office 365 in general (See the plans further down in this article):

  • SharePoint Online
    • Intranets
    • Extranets
    • Collaboration, connectivity, productivity
  • Exchange Online
    • E-mail, calendars, contacts and tasks
    • Cross platforms (PC, Web, Mobile)
    • Collaboration, connectivity, productivity
  • Lync Online (The new version of OCS)
    • Communication capabilities like chat, audio- or video calls.
    • Collaboration, connectivity, productivity
  • Office Web Apps
    • Online version of Word, Excel, PowerPoint and OneNote
    • Totally awesome.
    • Collaboration, connectivity, productivity
  • Office Professional Plus
    • All the familiar Office applications you’re used to playing around with is of course included

What about the pricing?

If you cut down on costs for purchasing and maintaining hardware, eases the burden of administration and skip the old licensing details – then what am I actually purchasing?

The answer is: A subscription.

In Office 365, just like in BPOS, you purchase subscriptions and only pay per user in your system.

Here’s a simple schematic from the Office 365 web site over what the pricing currently looks like for the Enterprise-edition of Office 365:
image

There’s an overview of the different plans on the Office 365 web site, where you can compare the different versions of Office 365 and conclude which one fits your organization.

So what are these Office 365 plans that I’m talking about?

In Office 365, you’ve got a few different options for what services to include in your subscription. This varies depending on what plan you sign up for:

I want to try it out, now!

So you’ve decided to at least take a look at Office 365? Good for you.

SIGN UP FOR THE OFFICE 365 BETA
http://www.microsoft.com/en-us/office365/online-software.aspx

Summary

Speaking generally, Office 365 gives you the advantage of collaborating and connecting your organization and being more productive without the hassle of the on-premises servers. This ultimately gives us the ability to focus on our actual business, instead focusing too much on our internal IT.

This is meant to be a small intro to what Office 365 is. If you want to learn more in-depth about all the awesomeness that comes out of the Office 365-package, please don’t hesitate to visit http://office365.microsoft.com for further details!

Enjoy.