Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

How to Run Grid-based Web Applications and Databases at Scale in the Cloud

, 16 Sep 2013
Rate this:
Please Sign up or sign in to vote.
This post will cover the whole spectrum of downloading data from a government site, loading it into an on premise SQL Server database, migrating this on premise SQL Server database to SQL database in Windows Azure.

Editorial Note

This article appears in the Third Party Product Reviews section. Articles in this section are for the members only and must not be used by tool vendors to promote or advertise products in any way, shape or form. Please report any spam or advertising.

Cloud-hosted, grid-based applications

Running grid-based web applications in the cloud is a growing trend. The ability to host grid-based applications and scale them as needed is a very common use case scenario that I plan to cover in this post.

DevExpress offers one of the most powerful grids in existence. I've used it for many, many years - across web and thick client applications. They now support many other platforms as well.


afanqkbi

This post will cover the whole spectrum of downloading data from a government site, loading it into an on premise SQL Server database, migrating this on premise SQL Server database to SQL database in Windows Azure. Next, we will add the powerful DevExpress ASP.NET Data Grid, which will read the data from the SQL database in the cloud. After that, we move on to the express grid, which will read the data from the SQL database in the cloud. As a final exercise, we will migrate the entire web application into the cloud as well, so that we can provide needed scale.

My Learnings

I've help the couple of developers get over the hump and I want to share my learnings with you here today.

DevExpress and Windows Azure can be used to create solutions that support an almost unlimited number of users.

Build or Buy?

As a developer, it is crucial to be able to provide a highly functional grid-based user interface to end-users. Ideally, this grid would behave like Excel, since spreadsheets are one of the best understood user interfaces on the planet. Minimal training is required and it can be very productive to use.

Not trivial to build

Providing grid-based functionality in a browser is very challenging. Writing your own just doesn't make any sense. It can become very complex extremely quickly. To start, there's the whole technical challenge of supporting multiple browsers. Even for a single browser, there are many different versions to support. Second, supporting basic CRUD operations isn't trivial either, as the grid must now support the ability to insert, update and delete data. Third, there is the challenge of good performance and fast scrolling. Imagine a data store that has hundreds of thousands of records that need to be scrolled efficiently. How do you plan to implement paging inefficiently? And fourth, it’s important that the grid be able to support touch on mobile devices.

Huge Challenges
How do you plan to support the moving of columns?
How do you plan to support sorting and grouping?
Will your grade be able to create SQL queries, on-the-fly, depending on what the end user is doing with the grid?
What about caching - how minimize roundtrips between server and client?
Do you plan to use sprites, which is the ability to put all images on a single PNG file, and then have the code pull the appropriate image from that single PNG file - to minimize chatter on the wire?
What about custom HTTP handlers to handle callback errors?
Do you plan to compress your JavaScript?
Do you plan to support themes, such as the office 2013 theme?
Are you going to support both web forms and MVC?
What about WPF and Windows 8, not to mention HTML 5 and JavaScript?

Simple Walkthrough

Let's walk through a simple end to end example.

Starting with data

I've downloaded movie location data as the example from San Francisco.


hyperlink2 SF Gov Data - Movies Shot in SF

Choose CSV format.

image001

The data is in comma separated format.

image003

Before we can import this into a table in SQL Server, we will need to add a column that represents the primary key for the clustered index. The bulk insert command will fail unless we do so. We will insert a column to the left of column A. Simply right mouse click on column A, within Microsoft Excel

image005

In cell A1 , I simply put in some text to indicate that this will be the fake ID. We can leave all the rows blank.

image007

We will just save over the original file. Take note of the folder since it will be needed in the bulk insert command.

image009

DevExpress Tooling

The assumption that I am making is that you have downloaded and installed the latest DevExpress products,

hyperlink2 DevExpress Download

Getting to a database

Since we are going to run this in the cloud, it makes sense to get this data into a SQL database, which is Microsoft’s solution for running a relational database in one of the many global data centers.

Start with a local database

One of the easiest ways to get started is to create a database and the corresponding tables using a local, on premise version of SQL Server. From there we can use some of the tooling inside of SQL Server Management Studio to migrate our database up into the cloud. So start SQL Server Management Studio.

image011

Start by creating a new database. Right mouse click on databases.

image013

Let us call this database SFMovies.

image015

Bring of a query window so that we can issue a create table statement.

image017

Here is a query to create a table called Movies.

Create table [dbo].[Movies]
(
   MovieRowId [int] IDENTITY(1,1) NOT NULL,
   Title [char] (255) NULL,
   Release_Year [char] (255) NULL,
   Locations [char] (255) NULL,
   Fun_Facts [char] (255) NULL,
   Production_Company [char] (255) NULL,
   Distributor [char] (255) NULL,
   Director [char] (255) NULL,
   Writer [char] (255) NULL,
   Actor_1 [char] (255) NULL,
   Actor_2 [char] (255) NULL,
   Actor_3 [char] (255) NULL
    CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED
(
    MovieRowId ASC
)   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
          ALLOW_PAGE_LOCKS = ON)
)


There are a few things to notice about this bulk insert command. First, we can issue this command inside of a query in SQL Server management studio. Also notice that the first row property is set, because we want to skip over the column headings. Since this is a comma delimited file you need to specify that as well, in addition to the newline which acts as terminators for each row.

BULK INSERT SFMovies.dbo.Movies  FROM 'C:\Users\Bruno\Downloads\Film_Locations_in_San_Francisco.csv ' 
WITH (FIRSTROW = 2,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n');

image021

Not relational

One thing to note is that the data is not structured well. The data actually does contain one-too-many relationships, but is stored in SQL Server as one big table. Normalizing the data to multiple tables is left as an exercise to the reader. Because the data is not properly normalized, it will prevent optimal behavior of the underlying data grid.

image023

Provisioning a database in the cloud

Before we can export the on premise database to the cloud we need to establish a server. A single server can service many databases. You can provision more servers as needed.

The Windows Azure portal allows you to provision a server. Notice that in the figure below, we can list all the databases. But what we want is to list the services, because we will migrate a database to the server. We do not need to provision a separate database through the portal you see below. Instead, the tooling will provision this for us using the on premise database.

 

You can see the name of the server below. You will need the server name as you begin to migrate the database into the cloud. You will also need a login ID and the password that you indicated when you first provisioned the server.

image027

You can provision new servers as seen at the bottom of the portal. Simply click the add button below.

image029

Let’s turn our attention back to SQL Server management studio. Within object Explorer, we can right mouse click on the database that we wish to migrate, and select tasks, then deploy the database to SQL Azure. It is no longer SQL Azure and has been rebranded to "SQL database" so the menu selection is wrong below. But it does what we needed it to do.

image031


We will now go through a wizard within SQL Server management studio to do the deployment. You will be asked to connect to the server we noted in the portal in an earlier step.

As you can see in the figure below you do need to have your login information to be able to connect to your server. The same information will be needed later, when the web application needs to create a connection to the database.

image033

Once the deployment is complete, you should see the confirmation screen below.

image035

One of the things you need to consider with the database server machine is that you need to have "allowed IP addresses" configured. You probably would not want to do what you see below, because it allows any connections to the server in the Microsoft data center.

In this sample the best thing to do is to not allow any IP addresses to connect directly to SQL Server. That is because we will deploy our web after the cloud and only allow cloud-based web app to access the database. That’s the point of the "allowed services" section in the figure below. But if you wish to connect to your database in the cloud using SQL Server management studio, you will need to specify the IP address of your computer that is running SQL Server management studio.

Selecting "yes" allows that web application to have access to the server and the underlying database.


image037


You can obtain the needed connection information to the cloud hosted database directly from the Windows Azure portal. From the bat dashboard section, you will see a selection called, "Show connection strings."

If you click on the SFMovies database from the dashboard section of the portal, you will be able to get a needed connection string.

image039


image041

Creating the Web Application

We are now ready to create the web application. The assumption is that you have already installed the DevExpress tooling, which embeds itself into the Visual Studio New Project dialog boxes. So the task remaining at this point is to create a web application that leverages a DevExpress grid, which will connect to the database we just created in a Microsoft Data center. The final step in this post will be to migrate this web application into the cloud. At that point, both the database and the web application will be hosted in the Microsoft data center. In addition, a great deal more scale can be leveraged should that be needed.

From Visual Studio, create a DevExpress Template Library.

image043

We selected "web forms" as the template. You could choose MVC as well, if desired. In the lower right corner of the dialog box below you will click on "Create Project."

image045

The project will get created with all the necessary references specified. What we need to do next visit is add a blank form and then use the toolbox to drag a grid control to the blank form. From there we will connect to the database and start to set some properties for the grid to meet our functionality requirements.


image047

Name the new web form that we just added.

image049

Within solution Explorer, you can see the new platform. It is currently empty, and we will now use the toolbox to add a DevExpress grid to it.

image051

When you do this for the first time and select the toolbox, you may have to wait a few moments as the toolbox gets updated and will see the dialog box below.

image053

There is a very rich set of controls, that you can select from. We are interested in the grid view control. Drag the grid view control onto your form.

image055

Now that the grid view control is on the form, we will need to connect it to the database in the cloud, in addition to setting other features that we wish to include.

The "choose data source" option will allow us to connect to the database. Inside the combo box, select "New."

image057

The data source type is a database. And we will specify an id for it, DataSourceMovies.

image059

Since this is a new connection click on the button, "New Connection."

image061

You will need the connection information for the server running in the Microsoft data center. You will need four things:

The server name, which is preceded by TCP
The username
Password
The database on that server


image063

Click next to "Save the connection."

image065

For the sake of simplicity, we will set up a grid with the minimum of requirements. There are a vast amount of options and capabilities that we will not leverage in this example. What we will do is specify the columns that we are interested in, as well as an ordering sequence for the data itself. We will sort that data by release year, as well as by the movie title.

You can see that the wizard will build the SQL statement for you.

image067

Click "Next," then "Finish."

Now you are ready to run the application. But this web application is running locally on the development computer. Ultimately, we want to migrate it to a Microsoft data center. First, let’s validate that it runs locally.

Specify the start page.


image069


Compile the application.

image071

Start to debug it.

image073

Validate that it works. Notice that it is running on localhost, on port 37553.


image075

It’s important to note that there are a huge amount of capabilities that we are not leveraging in this example. This post is already quite long so for additional capabilities, visit the DevExpress website.

This dialog box below provides some indication of all these capabilities.

image077

Migrating the app to the cloud

We are now ready to take this locally deployed grid application and migrate to Windows Azure. There are numerous data centers throughout the world where you can deploy this web application. It makes a lot of sense to deploy it to the same data center as the database, which will minimize latency and maximize performance.

To enable this you will need to add a Windows Azure cloud service project to the existing grid view project. This can be achieved with the simple right mouse click.


image079

As you can see now there are two projects within the solution. The first project is the one we felt that contains the grid view control. You can think of the second project as the configuration and deployment project.

image081

Although we will not change the instance count, this is the way you can scale your application. If you to type in the number 10 in the instance count field, that means that 10 copies of this web application would be running on 10 separate virtual machines running IIS (with all 10 being load balanced). In addition, they would be behind separate fault domains and update domains. This means that they could be updated by the cloud service without bringing more than one of them down at a time. This also means that if there is a crash on any one of the 10, the other nine would be on a separate rack, on a separate router to minimize the potential for downtime.


image083

Before we actually publish this to a Microsoft data center, you can run the application in the Windows Azure emulator. By compiling and running this application you can see that the address contains 127.0.0.1 on port 81. The beauty of the emulator is that you can test your cloud application locally before going to the trouble of deploying it to the cloud, then testing it.


image085

One final step to accomplish before publishing is to set the "copy local" property of each of the references to true. This means that the actual DevExpress assemblies will be copied along with the rest of our project once we deploy.

image087

We are now ready to deploy the application. Right mouse click on the deployment project and select publish. It is very easy to set up the ability to publish.

You can read more about it here:

hyperlink2

How To Publish a Web App to the Cloud



image089

A publish wizard will now appear allowing you to walk through the publishing process.

image091

You can deploy or publish to an existing virtual machine, or you can create a new one. There are other options here as well. For example, you could choose to enable remote desktop, which provides you the ability to actually remote into the virtual machine and view the exact configuration and setup.

image093

The Windows Azure cloud offering is not to be a platform as a service offering. This means that you are not bound to individual virtual machine names. Rather, you are to an entire service, and the concept of individual virtual machines has been abstracted away by the service.

I’ve written about platform as a service here:

hyperlink2 Why Platform as a Service will rule the world


image095

Select "Publish."

image097


image099

So this is the end game right here. Both the database and the web application itself are running inside of one of Microsoft’s huge mega data centers. Our SQL Server data is not triply replicated and we also have the ability to scale the web application to meet virtually any demand. There is also a host of other capabilities within the Windows Azure platform that we can leverage, which are not covered in this post. There is also a tremendous amount of customization that can be done with the DevExpress tooling.

image101

With a little more work you can make your application with like this:

image103

Conclusion

So this post covered some powerful concepts. First, we talked about leveraging some powerful gridview technologies by establishing that it makes sense to buy instead of build. We talked about starting with on premise databases and web applications and then migrating both pieces to the cloud. We talked about the ability to scale code data as well as the web apps. Ultimately, this all boils down to one question - how do we provide highly functional web applications to meet any level of demand, while maximizing performance and uptime?

1xfsiuab

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Bruno Terkaly

Canada Canada
Starting with Turbo C and 8086 Assembler in the late 80s, Bruno has kept busy teaching and writing code in a multitude of platforms, languages, frameworks, SDKs, libraries, and APIs.

Bruno's depth of knowledge comes from years of experience in the field, where he can bring real-world knowledge and combine it with forward thinking that is required for his current role as a Developer Evangelist at Microsoft. Prior to evangelism, Bruno was a Microsoft Premier Field Engineer, helping customers in remote locations on a moment's notice to help with extreme troubleshooting scenarios, including problem isolation and correction, live and post-mortem debugging, on-the-fly application design and code reviews, performance tuning (IIS, SQL Server, .NET), application stability, porting / migration assistance, configuration management, pre-rollout testing and general development consulting.

As an evangelist, Bruno spends time writing code and giving live presentations on building cloud based applications, specifically using the Windows Azure Platform. He also takes a strong interest in Mobile Computing and is convinced that both mobile and cloud platforms, separately and together, are poised for huge growth over the next 10 years.

Bruno is very optimistic about the potential for new interactions with software using Kinect. The software industry has been languishing in aging approaches to human interactions and software. Kinect opens up an brave new world of possibilities to next generation software engineering.

Comments and Discussions

 
GeneralMy vote of 5 PinmemberVolynsky Alex16-Sep-13 14:57 
GeneralRe: My vote of 5 Pinmembervicky.ritesh29-Sep-13 22:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140921.1 | Last Updated 16 Sep 2013
Article Copyright 2013 by Bruno Terkaly
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid