Wednesday, April 24, 2013

Sharding, Scale-out with Windows Azure SQL Database (Step by Step)


Database sharding is a technique of horizontal partitioning data across multiple physical servers to provide application scale-out. SQL Database combined with database sharding techniques provides for virtually unlimited scalability of data for an application.

Sharding:

Sharding is an application pattern for improving the scalability and throughput of large-scale data solutions. To “shard” an application is the process of breaking an application’s logical database into smaller chunks of data, and distributing the chunks of data across multiple physical databases to achieve application scalability. Each physical database in this architecture is what is referred to as a shard.

The benefits of sharding or Federation with SQL Database
  • Scale out using tens, hundreds or thousands of database nodes using commodity hardware instead of expensive scale-up systems,
  • To achieve scalable performance as the number of nodes increases,
  • Build a solution with an excellent price-performance ratio derived from the use of commodity hardware instead of expensive application servers
  • SQL Database provides a high availability SLA of 99.9% for all databases, no need to implement RAID and other availability techniques yourself.
 
 
How to Shard your database
To start working with Sql server sharding I have used SQLAzureMW tool to done sharding activity. You can download the tool from
 
Before starting sharding you need to design or update your database keeping following points in mind
  1. Every table must have a primary key.
  2. Sharded key must be a primary key or part of primary key in related tables (Clustered index required).
  3. Identity and Timestamp are not included as a part of data types for sharded tables
  4. Identity can be set in Root DB tables and Sharding reference tables. (both terms defined during step by step creation)
  5. Root DB collation and size are replicated to all sharded databases.
  6. You need to manage schema changes in all databases manually to avoid merging problems.
  7. Replication not support with table scripts.
  8. Need to mention Federation key at table creation level even root database are not created yet (using this tool).
Run the tool and select Database
 
Connect to your local db where Database exists that needs to shard or migrate on SQL Azure
 
If you want to migrate to simple Azure DB select SQLAzure for Federation, Select as selected.
Also select objects like Tables, procedures etc.
You can also select Table and Schema or Table or Schema only.
 
 
Script generated for Selected objects and if there are any errors it will show in red lines in Summary section.
For federation as we don't connect our Azure server yet. We need to add federation key manually so that when script were executed at the end table are ready to created on the basis of federation key.
 



 
On this screen you can view FEDERATED ON (UserId = UserId).
  • Federated On is keyword to add this table into federation
  • "UserId" is key attribute on which we need to generated shards.
  • "= UserId" is the Key column that has data and on the basis of this data definition shards data will be moved from one to other federation.
If you are planning to migrate your database onto Azure Plateform. You do not need to mention FEDERATED ON clause.
 

Use "Connect to Server" and by providing Azure credentials as we can see in next screen shot you will be connected to Azure Database server.
As there is no database created yet. We need a database or Root database to start our database migration.
Click on Create Database and You can et its size and Edition along with its name.
Following editions are available
  • Express -- Web Editions
  • Workgroup -- Business Editions
  • Standard
  • Enterprise
If you want to migrate your database without federation you can click next and deploy your changes to database.

for Federation:
You have successfully created Root database for your Federation database. Now connect again to create federations.
The difference is change your server type to SQL Azure Federation and mention your database that was recently created. In this case UserDBRoot
 
 
Now you have created Federation Root db and need to create it Federation Key on which you data will be moved to respective shards.
You can set Distribution data type as BigInt, Float and UniqueIdentifier.
 

 
Now you have successfully created Federation Member and you can see its range is Min value of BigInt to Max value of BigInt
In this Screen shot I am creating Split point that will be key decision factor to distribute data. For sample I have set to 5. (It depends on your requirements).
 

 
On click next scripts will generate member databases and also their respective table structures and data (if mentioned) using BCP command.

You can connect and query your database from Online Management Studio and Local management studio as well.
 

Merging Federated Members

If I want to merge my federation members I will use Delete button and specified options.
  • For merging you need to remember
  • All data of deleting member will be lost
  • Take backup of both members (merger and deleting)
  • After merging member you need to insert all deleted member data manually
  • If you delete Federation all members will be lost
  • You need to make your federation members offline to avoid problems to your customer
 
There will be another post that will define following in detail
  • How to Query a specific Shard.
  • How to Check database usage size.
  • How to View your sharding database ranges and Stats by Query.
  • Querying your database using Online and On-premises database management studio.

Tuesday, February 26, 2013

OAuth and REST and OData in SharePoint 2013

Speaking at Perth's SharePoint Saturday - Registrations to attend a SharePoint event in Perth are now open! Spaces are limited register today to confirm your spot. 

SharePoint 2013 has introduced exciting new features for both the developers and business users alike. This session will focus on OData and Rest services. It will cover an overview of REST, OData and OAuth and how they can be used in SharePoint 2013. With the introduction of new App model in SharePoint 2013, these APIs are becoming increasingly important. How do you choose between them, to know when the REST/OData approach is best? In this session, I’ll cover 

• Understanding REST 
 OData Fundamentals
• OData Support in SharePoint 2013 
• OData Programming 
• Introduction to OAuth.

http://www.sharepointsaturday.org/perth/meetings/66/OAuthandRESTandODatainSharePoint2013.aspx

Monday, December 03, 2012

SharePoint Field set to Required but still doesn't require Input

One of common mistake when making  content type/list using sharepoint schema is to make the required field field as

Required="True" or Required ="ture"

if you pick the value true for the the field as specified above, Visual Studio doesn't give you any error and you can deploy your content type and list with out any errors.
But when you try to enter value for field, you will see that the field is not mandatory OR no redstar (required field indication) on add form of that field.

This will not make this field required.


 ID="{070e50ac-f9c1-4d4c-b1f9-de0bcac300f6}" Name="test" DisplayName="test" Type="text"  Required="true" Group="MytestGroup" ColName="test"/>

...

 ID="0x01000a4547a7e98c46248e063d79ddd67f5c"
           Name="TESTContent Type"
           Group="MyGroup"
           Description="Test Content Type Description"
           Inherits="FALSE"
           Overwrite="TRUE"
           Version="0">


 ID="{070e50ac-f9c1-4d4c-b1f9-de0bcac300f6}" Name="Price" DisplayName="test" Required="True" />    
This will not make this field required.
 ID="{070e50ac-f9c1-4d4c-b1f9-de0bcac300f6}" Name="test" DisplayName="test" Type="text"  Required="true" Group="MytestGroup" ColName="test"/>

...

 ID="0x01000a4547a7e98c46248e063d79ddd67f5c"
           Name="TESTContent Type"
           Group="MyGroup"
           Description="Test Content Type Description"
           Inherits="FALSE"
           Overwrite="TRUE"
           Version="0">


 ID="{070e50ac-f9c1-4d4c-b1f9-de0bcac300f6}" Name="Price" DisplayName="test" Required="True" />    
This will  make this field required.
 ID="{070e50ac-f9c1-4d4c-b1f9-de0bcac300f6}" Name="test" DisplayName="test" Type="text"  Required="true" Group="MytestGroup" ColName="test"/>

...

 ID="0x01000a4547a7e98c46248e063d79ddd67f5c"
           Name="TESTContent Type"
           Group="MyGroup"
           Description="Test Content Type Description"
           Inherits="FALSE"
           Overwrite="TRUE"
           Version="0">


 ID="{070e50ac-f9c1-4d4c-b1f9-de0bcac300f6}" Name="Price" DisplayName="test" Required="true" />    

Wednesday, November 28, 2012

Loading form Library(created programmatically) in InfoPath form

Scenario:
1) Form Library is created using Visual Studio 2010 ....follow the link below for steps of creating form library using visual studio.
http://microsofttechnologies.blogspot.com.au/2012/11/creating-sp-2010-form-library-using.html


2) Infopath form is created with few fields on it , when the user tries to publish the form to form library which was created using Visual Studio....it doesn't appear in Infpath Form Library list.


Solution:


Open List Definition in Visual Studio and change URL by removing List before slash 


Wrong-----This form library will not appear in infopath form when we try to publish a form and want to select a form library which was created before..


 
                OnQuickLaunch="TRUE"
                TemplateType="115"
                Url="Lists\ListURL"  
                Description="List Description">
 





Correct-----This form library will appear in infopath form when we try to publish a form and want to select a form library which was created before..
 
                OnQuickLaunch="TRUE"
                TemplateType="115"
                Url="ListURL"  
                Description="List Description">
 

Creating SP 2010 Form Library using Visual Studio 2010

1) Open Visual Studio, in New Project select SharePoint 2010 and then select an empty project. (Create an empty SharePoint project)

2) Add a new List or List based on selected content type. 

3) Open List template definition and change it to (List Template not list definition)
  
        Name="ListName"
        Type="115"        
        BaseType="1"  
        OnQuickLaunch="TRUE" 
        SecurityBits="11" 
        Sequence="120" 
        DisplayName="List Display Name" 
        Description="List Description" 
        Image="/_layouts/images/itfl.png"  
        DocumentTemplate="1000"/> 


4) Build the project and deploy it, it will create a form library in SharePoint with the name you specified above. 

Promoting Infopath form fields to SharePoint - with out duplication

Scenario:
SharePoint 2010 form library is created using object model or schema definition in Visual Studio 2010 and form is created in InfoPath 2010 using InfoPath designer, When the fields are promoted to SharePoint Form library from InfoPath form it duplicates the filed in Form Library.


Solution:
When you promote a field from infopath form to sharepoint it creates a new field in form library with the name of promoted field, even if the field with that name exist it  creates a new field and give it different internal name.

Solution is when you are promoting a field from infopath form , select that field modify it select the SiteColumn group and SiteColumn from dropdowns as shown in figure belw....


For Example: if you have created a column which exist in DSL Columns group select DSL Columns from drop down.

now select the name of field from Site Column and click ok

Do same steps for all promoted fields...if you have already created those fields in FormLibrary...

Check the Image below with screenshot highlighting points above ...


Tuesday, November 20, 2012

Create a content type with out Title Field

Scenario:-


Create a content type in SharePoint 2010 based on item content type but it should not have a title field.


Solution:-
1) Create an empty solution in visual studio 2010.

2) Add new item-> Content Type

3) Extend your content type from item content type.

4) now open the file and change inherits to False.



                 Name="ContentTypeName"
               Group="ContentType Group"
               Description="ContentType Description"
               Inherits="FALSE"
               Version="0">

Sunday, November 18, 2012

Set Default Value in People Picker Field

This post explains that how to set default value in people picker field in infopath 2010, i have picked current user in my example but it can be another field value on form, any function etc...
People picker field has
Display Name
AccountID
Account Type

in it....
you can set default values for each of these fields....
1) Add a people picker on infopath form
2) Take properties of any of above (sub fields) [I have picked Account ID in my example]
You can take properties by selecting AccountID in left hand field pan and right click, properties.
3) Set the default value for this as show in image below....
[i set it to current user, you can pick any other field value or function etc]

Sunday, October 07, 2012

SharePoint 2010 - Programmatically Copy Picture to Image Library on Item Added

SharePoint 2010 - Pro grammatically Copy Picture to Image Library on Item Added
Scenario:
Two Picture Libraries
PicLib and PicLib2

Requirement:
When an image is uploaded by user in First Picture Library upload the same image in PicLib2.
Solution
Create a new Project in VS 2010 (Event Receiver Project)
Select the site where you want to deploy this
Select the source List (PicLib in our scenario) and ItemAdded event
Write Code below in ItemAdded

public override void ItemAdded(SPItemEventProperties properties)
{
base.ItemAdded(properties);

try
{
SPList lib1 = (SPDocumentLibrary) properties.Web.Lists ["piclib"];
SPList lib2 = (SPDocumentLibrary)properties.Web.Lists["piclib2"];
SPListItem item1 = properties.ListItem;
byte[] fileBytes = item1.File.OpenBinary();
string destinationUrl = lib2.RootFolder.Url + "/" + item1.File.Name;
SPFile destFile = lib2.RootFolder.Files.Add(destinationUrl , fileBytes, true);

}
catch (Exception e)
{
throw e;
}
}