Jean Paul's Blog

There are 2 types of People in the World, One who Likes SharePoint and..

    Advertisements
  • Microsoft MVP

  • MindCracker MVP

  • CodeProject MVP

  • eBook on SharePoint 2010

  • eBook on Design Patterns

  • eBook on Windows Azure

  • NLayers Framework @ CodePlex

  • MSDN Forums

  • .Net vs. Java

    Due to Public Demand
  • Advertisements

Archive for the ‘SQL Server’ Category

What is AlwaysOn in SQL Server?

Posted by Paul on March 3, 2014

SQL Server 2012 introduced the new feature called AlwaysOn.

AlwaysOn provides High Availability & Disaster Recovery alternative to Database Mirroring.

What is Database Mirroring?

Database Mirroring is a technique to increase High Availability of database.  The topology consists of three server roles.

  1. Witness Server
  2. Principal Server
  3. Mirror Server 

Transaction Logs will be synchronized from Principal Server to Mirror Server.

image

The Witness Server determines whether to contact Principal or Mirror based on activity, thus increasing the availability.

Advantages of AlwaysOn over Database Mirroring

AlwaysOn provides the following advantages over Database Mirroring:

  1. More effective server utilization
  2. Less configuration hassles
  3. Better manageability
  4. Better failover situation
  5. Works best with multiple-database scenarios of SharePoint

More and More people are switching to AlwaysOn Availability Group technique.

References

AlwaysOn Experiment

Summary

Clustering, Mirroring, Log Shipping, AlwaysOn are different technologies to attain High Availability in database servers.

Advertisements

Posted in SharePoint, SharePoint 2010, SharePoint 2013, SQL Server | Tagged: , | Leave a Comment »

SharePoint 2010 – Business Connectivity Services (BCS)

Posted by Paul on July 31, 2012

In this article we can explore another feature of SharePoint 2010 – Business Connectivity Services. This service allows data reading and writing from external data sources.

Using BCS we can access and modify data from:

· WCF Service

· Database

· Business Applications

· .Net Assembly connecting to Business Data

· Custom Connectors

BCS Architecture

The BDC Runtime is responsible for connecting to the external data sources. External data sources connections are stored in BCS Metadata inside SharePoint. The BCS Metadata allows connecting to the data sources in offline mode too. SharePoint acts as a Presentation layer in this scenario.

clip_image002

Note

The previous version of BCS was BDC (Business Data Catalog)

Following are the components of BCS.

BDC Metadata Store

This component provides storage for metadata consisting of external data sources and content types and it will be part of the BCS layer.

BDC Server Runtime

This runtime component is responsible for connecting to the BDC Metadata Store and processing the requests.

BDC Client Runtime

Client runtime providing offline storage, synchronizing data back to server etc.

SharePoint Designer

We can use SharePoint Designer provided tools for creating BCS Solutions.

References

http://msdn.microsoft.com/en-us/magazine/ee819133.aspx

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

Summary

In this article we have explored the BCS feature of SharePoint 2010. In the next article we can experiment with creating a BCS external content type inside SharePoint.

Posted in SQL Server | Tagged: | Leave a Comment »

SharePoint 2010 – Business Connectivity Services (BCS)

Posted by Paul on July 31, 2012

In this article we can explore another feature of SharePoint 2010 – Business Connectivity Services. This service allows data reading and writing from external data sources.

Using BCS we can access and modify data from:

· WCF Service

· Database

· Business Applications

· .Net Assembly connecting to Business Data

· Custom Connectors

BCS Architecture

The BDC Runtime is responsible for connecting to the external data sources. External data sources connections are stored in BCS Metadata inside SharePoint. The BCS Metadata allows connecting to the data sources in offline mode too. SharePoint acts as a Presentation layer in this scenario.

clip_image002

Note

The previous version of BCS was BDC (Business Data Catalog)

Following are the components of BCS.

BDC Metadata Store

This component provides storage for metadata consisting of external data sources and content types and it will be part of the BCS layer.

BDC Server Runtime

This runtime component is responsible for connecting to the BDC Metadata Store and processing the requests.

BDC Client Runtime

Client runtime providing offline storage, synchronizing data back to server etc.

SharePoint Designer

We can use SharePoint Designer provided tools for creating BCS Solutions.

References

http://msdn.microsoft.com/en-us/magazine/ee819133.aspx

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

Summary

In this article we have explored the BCS feature of SharePoint 2010. In the next article we can experiment with creating a BCS external content type inside SharePoint.

Posted in SQL Server | Tagged: | Leave a Comment »

Sql Server – Transact Sql to make a column unique

Posted by Paul on April 23, 2011

ALTER TABLE TableName
ADD CONSTRAINT NewConstraintName
UNIQUE(ColumnName)

Posted in SQL Server | Tagged: , , | Leave a Comment »

Sql Server – Transact Sql to make a column unique

Posted by Paul on April 23, 2011

ALTER TABLE TableName
ADD CONSTRAINT NewConstraintName
UNIQUE(ColumnName)

Posted in SQL Server | Tagged: , , | Leave a Comment »

How to shrink Sql Server Database?

Posted by Paul on April 21, 2011

The Enterprise Management studio provides an option to shrink the database.

 

Step 1: Open Enterprise Management Studio (i am using sql server 2008) and select database, right click and choose Tasks>Shrink

 

image

Step 2: In the appearing dialogue press Ok to start the shrink process.

image

Posted in SQL Server | Tagged: , , | Leave a Comment »

Automatic History using Triggers

Posted by Paul on February 5, 2011

I would like to take a moment to explain the following problem I am facing:

I do have a table named Equity which contains the company name and the current PE Ratio. 

image

The PE Ratio will be updated daily or weekly based on the wish of user through a web application in ASP.NET.  When the process runs for many years, there will be many updating happening to the same record. 

Problem

Our problem is to get the historical PE Ratio values, so that we can find out the maximum and minimum values for decision making.

High Cost Solution

One of the solutions, we can prefer is while updating the record from our web application, take a copy of the record and insert it into another table. 

This involves changing the associated business logic layer in c# or changing the stored procedure.  I would say  this is more time consuming and lot of code we have to write to write to manage the new table if schema changes are involved.

Simple Solution

We can use database triggers to achieve the same.  The solution steps are as following.  Belive me, this will take only 5 minutes to do.

1. Create a new History Table with same structure

2. Create a Trigger for UPDATE operation

As our History table is not involved in any business logic currently, we can use the Trigger to solve the problem and move off.

Pre-requisites

First, we have to create our original Equity table.  The script is given below.  The script was generated from Sql Server 2005.

CREATE TABLE [dbo].[Equity](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PERatio] [float] NULL,
    [ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_Equity] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Create the History Table

We can create the EquityHistory table using the following script.  Please note that there is an additional column HistoryId which keeps track of the History record as there will be multiple history records for each original table record.

CREATE TABLE [dbo].[EquityHistory](
    [HistoryId] [int] IDENTITY(1,1) NOT NULL,
    [Id] [int],
    [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PERatio] [float] NULL,
    [ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_EquityHistory] PRIMARY KEY CLUSTERED
(
    [HistoryId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Create the Trigger

The following Trigger script will do all the magic involved.  It says that create a trigger on table Equity whenever UPDATE statement is used.  The last line says that get the record from table inserted and insert it into the EquityHistory table

CREATE TRIGGER Equity_Update_Trigger
ON Equity
FOR UPDATE
AS
INSERT INTO EquityHistory SELECT * FROM inserted

The table inserted  is a pre-defined table insided triggers.  It will give the current record structure while using with INSERT/UPDATE triggers.

Testing the Whole

You can try inserting a new record to the original Equity table.  The corresponding record will get automatically inserted in the EquityHistory table.

Now, try changing the PERatio from the first able.  You can see a new record gets added to the history table.

image

Attachment

The attachment file contains queries to create table, insert data, select data.  You can try running the file against an sql server database.

Posted in SQL Server | Tagged: , , , | Leave a Comment »