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

Posts Tagged ‘database’

Windows Azure – Create new SQL Azure Server and Database

Posted by Paul on November 13, 2011

Now we can proceed with creating the database in the azure account. The steps involved in this are following.

  • Create new subscription
  • Create new server
  • Create new database

New subscription is already created and we can reuse it. So we can proceed with the next 2 steps.

Create new server

Sign in to the windows azure portal and click the Database category from the left pane as shown below.

clip_image002

Wait for a few seconds and you will be getting the main pane ready as below:

clip_image004

Click on the Create a new SQL Azure Server item from the above screen. The following screen appears.

clip_image006

In the above dialog select the Subscription and click next to continue.

clip_image008

In the above screen select the Region and click Next to get the next screen.

clip_image010

In the above screen enter the administrative login and password. (The usual names like sa, admin etc. are reserved and entering is not allowed)

Note: On entering a valid login name and passwords only, the Next button will get enabled.

On clicking next, we get the following screen.

clip_image012

Use the Add button to add the IP address range. We need to enter the range for getting client access to the server. For demonstration I have entered 0.0.0.0 to 255. 255. 255. 255 to open all IP get access to it.

Now click the Finish button.

You can open the subscription item from the left top pane to see the new server and default databases inside it. You will be provided with a master database with read only access to it.

clip_image014

Create a new Database

Now we can proceed with creating our database. To achieve this click on the Create button from the top ribbon as shown below.

clip_image016

The following dialog box appears:

clip_image018

Enter the database name you prefer and select the default options for other entries, click Ok button.

You are now ready with the new database.

clip_image020

Now we are ready with the new database. In the upcoming articles we can see how to use the database using ADO.NET classes.

Summary

In this article we have seen how to create a database server and database in SQL Azure. You can find more information on SQL Azure here.

Advertisements

Posted in C# | Tagged: , , , , | Leave a Comment »

Windows Azure – SQL Azure Introduction

Posted by Paul on November 13, 2011

Remembering from the storage type article, the 3 types of storage were:

  • Local Storage
  • Windows Azure Storage
  • SQL Azure Storage

In this article we are emphasizing on SQL Azure Storage. The important features are listed and we will be creating an database in the online storage account.

The features are mentioned below:

  • SQL Server equivalent in Cloud
  • Built on SQL Server Technologies
  • No installation needed
  • Transact SQL Support
  • ADO.NET and ODBC drivers supported
  • Available anywhere
  • Existing .Net Database skills are reusable here
  • Easier Scalability
  • Highly Fault Tolerant
  • Pricing involved

clip_image002

Note: The Sql Azure can be accessible from Azure applications as well as other applications like ASP.NET, Console Applications. Also the complete SQL Features are not available in SQL Azure. You can find more info here.

Features Explained

We can relate everything in our windows/asp.net world to azure world. The Sql Azure is the Sql Server equivalent in the cloud. There will be a server, database and connectivity using ADO.NET which we are familiar with. (Creation of server and database are discussed in the upcoming article.)

Connecting using ADO.NET

We can use the existing ADO.NET SQL Server classes to connect and manipulate the SQL Azure database.

Operation

Class

Connection

SqlConnection

Command

SqlCommand

Read

SqlDataReaders

Advantages

The SQL Azure database will be replicated on multiple servers and is more fault tolerant than the SQL Server database.

The automatic switching of server in case of server fault makes the SQL Azure have increased availability.

SQL Azure helps the database management easier by freeing up from:

  • Server Operating System Setup Overheads
  • SQL Server Setup Overheads
  • Scalability Setup Overheads

In the pre SQL Azure world, we need to setup the database server with the appropriate operating system, install the sql server, configure the server etc. But in SQL Azure we are free from all these overheads. This makes concentrating in the core aspects of the application.

In the SQL Azure, scalability is an easier task otherwise in Sql Server we have to deal with replication server configuration, principal and mirror server configuration etc. We can scale up and down based on the requirements of the application.

More information can be found here
Types of Database

In the SQL Azure there are two types of databases:

  • Web Edition
  • Business Edition

The Web Edition is the basic edition and right choice for a typical web application. This Database provides up to 5 GB of T-SQL based relational database. This edition is best suited for Web application, and Departmental custom apps.

The Business Edition SQL Azure DB provides up to 50 GB of T-SQL based relational database. This edition is best suited for Software as a Service applications, custom applications etc.

clip_image004

The billing also varies based on the edition. In our examples we will be using the Web Edition only.

Posted in C# | Tagged: , , , , , | Leave a Comment »

Windows Azure Platform Management Tool (MMC)

Posted by Paul on November 13, 2011

In this article, I would like to introduce a tool useful while developing Windows Azure applications.

The Platform Management Tool is a desktop application and it allows the developers to manage:

  • Hosted Services
  • Perform Diagnostics
  • Certificates
  • Storage Services
  • Blob, Table, Queue
  • Command Based Operations

Download

The latest tool can be downloaded from codeplex.com

Source Code

The source code of the tool is available here.

Using the MMC

Download the tool and execute the setup.

clip_image002

The files will be extracted to the specified folder. Now execute the StartHere.cmd file from location C:\WindowsAzure\MMC. The following wizard will appear.

clip_image004

Click Next to continue and it will do a scan of the system.

clip_image006

If all the required software found the status will be green. Click Next to continue and the installation will be started. A command window will be prompted like below:

clip_image008

Now press any key to continue and within few seconds the installation will be completed. The following screen will be visible.

clip_image010

The MMC tool will be automatically started as below. (You can also launch it from the desktop icon)

clip_image012

Expand the Windows Azure Management item and click on the Service Management item.

clip_image014

Enter your Subscription ID in the appearing dialog. (Subscription ID can be retrieved from the Windows Azure Portal)

Then Click on the API Certificate button. Your certificate will be automatically populated in the appearing dialog as shown below: (If no certificates are listed, then you need to create and add certificate into your azure account. This is explained the previous article)

clip_image016

Select the certificate and click Ok.

Now click Connect from the right pane as shown below:

clip_image018

Wait for a while and you will be connected to the service. Now you can explore the hosted services, storage services by clicking the respective item from the left pane.

The figure below shows the content of Blob Containers.

clip_image020

Summary

In this article the MMC tool was introduced. It should be noted that the Storage functionalities of the tool can be achieved with the Server Explorer of Visual Studio.

Posted in C# | 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 »