A Quick Look at Cloud and SQL Azure

What is a Cloud Database?

Cloud databases are a hot new idea to the world of computing. Everyone is talking about the “cloud” and how it is going to revolutionize database storage. But what is a cloud database, really?

The short answer is that “the cloud” refers to the internet. This is because the fuzzy edges of the cloud are reminiscent of the fuzzy edges of the internet, and how there is no defining line between the internet and your network. This can obviously cause some security problems, but software companies are beginning to take advantage of this fuzzy nature of the internet.

This means that a Cloud Database is your database storage – on the internet. This sounds redundant, so let me explain. Software companies – like Microsoft – are now coming out with programs that will resource your database needs with their programs, using abstract servers and server hosts rather than requiring you to buy a program and provide your own server. This not only means you have one less expense, but depending on your storage needs you can even choose a “pay-as-you-go” option that limits your daily use costs as well.

Saving money isn’t the only appeal of a Cloud Database. For companies that want to begin serving their customers quickly without all the programming and patching issues, a cloud database is the perfect option. For Microsoft’s SQL Azure at least, this program comes with tech support and professionals who will be working behind the scenes to make sure everything runs smoothly.

There are many, many cloud database options out there, but today I will be focusing on Microsoft’s SQL Azure. 

Source: Microsoft Developer Network

Why Use Windows Azure?

With all the database choices available today, what are the perks of using Windows Azure?

First of all, you can direct your resources towards more important areas, and away from buying expensive equipment. Also, Windows Azure offers automated service management, which will help you save time on repairs and protect you in case of a hardware failure.

Windows Azure is also easy to use. It features the same type of tools (Eclipse, Visual Studio) that you know and love, so you don’t have to learn new programs. Even better, Azure allows you to debug your applications before going live.

Windows Azure is compatible with the same programs you already know how to use. You can still create applications in Java or PHP. Best of all, with Windows Azure your business can become known worldwide. It features a global datacenter and CDN footprint so that you can take advantage of partner offers and programs. 

What is SQL Azure?

SQL Azure is cloud-based service from Microsoft. SQL Azure delivers cloud database services which enable you to focus on your application, instead of building, administering and maintaining databases. In a very generic statement we can say SQL Azure allows creating Database on the cloud.

SQL Azure will replace physical servers because it uses databases located all over the globe. This will not only speed up access for your users, but you will no longer have hardware costs eating up all your profits. And because SQL Azure features pay-as-you-go pricing, you can customize your usage to meet your needs. It is all highly affordable.

SQL Azure has been designed to make your life easier. Software installation and patching is included, and fault tolerance is built in. There is also a web-based database manager which will allow you to manager and administer your data quickly and easily. SQL Azure is compatible with T-SQL and earlier SQL Server models.

Given the compatibility between SQL Server and SQL Azure, there has been a lot of confusion about when to use Azure, and whether you should switch if you are already comfortable with SQL Server. To put it simply, SQL Server is a more traditional database server while SQL Azure is a cloud database – using the internet to act as your servers. Even better, SQL Azure features pay-as-you-go or a one-time “commitment” fee.

SQL Server is a program that an individual or company purchases and can be used on a variety of hardware – which is then hosted by a Service Provider or Hosting Partner. On the other hand, SQL Azure is a service that users can purchase in order to focus more on building up their business, rather than worrying about (and paying for) expensive hardware and the requisite management, programming, and patching.

SQL Azure is perfect for businesses that are looking for a database solution with a lot of “behind the scenes” support. There are still developers and IT technicians who will be installing and overseeing your database, so you know that things will be running smoothly. SQL Server is the perfect option if you’re looking to quickly start serving your customers without the need for the large expense of a traditional server and all the software.

For more information, check out these helpful links: http://shannonlowder.com/2010/05/what-is-sql-azure/ and http://www.microsoft.com/windowsazure/sqlazure/database/. 

Creating Database in SQL Azure

Creating database in SQL Azure is very easy process. Here it is described in few basic steps.

• Login SQL Azure portal with your live credential https://sql.azure.com/
• Click on SQL Azure tab and select Project
• Click on the project, this will list entire databases on SQL Azure
• Create database by clicking ‘Create Database’.
• While creating the database user have two options for editions 1) Web Edition 2) Business Edition. Choose appropriate edition and you have your database ready to use.

The basic difference between the editions is size. Web Editions supports up to 5 GB of size and Business Edition supports up to 50 GB of size. Here is the quick list of the salient features of both the editions. 

Web Edition Relational Database includes:

• Up to 5 GB of T-SQL based relational database
• Self-managed DB, auto high availability and fault tolerance
• Support existing tools like Visual Studio, SSMS, SSIS, BCP
• Best suited for Web application, Departmental custom apps

Business Edition DB includes:

• Up to 50 GB of T-SQL based relational database
• Self-managed DB, auto high availability and fault tolerance
• Additional features in the future like auto-partition, CLR, fan-outset
• Support existing tools like Visual Studio, SSMS, SSIS, BCP
• Best suited for SAAS ISV apps, custom Web application, Departmental apps 

New SQL Azure portal has a great Silverlight-based user interactive and many more operations can be performed through the UI. There is a new database manager and it allows us to perform operations at the table and row level.

Now we can perform many more operations through the database option of the Windows Azure portal.

1. Create a database
2. Create/ delete a table
3. Create/edit/delete rows of table
4. Create/edit stored procedure
5. Create/edit views
6. Create / execute queries

In this way, you can perform almost all the basic operations from new SQL Azure Data Base Manager.

For security we can configure the firewall rules as well. By default, a database created on SQL Azure is blocked by the firewall for the security reason. Any attempt at external access or access from any other Azure application is blocked by the firewall. 

Connecting from a Local system

When we want to connect to SQL Azure portal from a network system or local system, then we need to configure firewall at the local system. We need to create an exception for port 1433 at the local firewall. 

Connecting from the Internet

The request to connect to SQL Azure from the Internet is blocked by the SQL Azure firewall. When a request comes from the Internet:

1. SQL Azure checks the IP address of the system making the request
2. If the IP address is in between the range of IP addresses set as a firewall rule of SQL Azure portal, then the connection is established.

Firewall rules can be Added, Updated and Deleted in two ways:

1. Using SQL Azure Portal
2. Using SQL Azure API

SQL Azure Architecture

SQL Azure resides in the Microsoft Data center, which provides a relational database to applications with four layer of abstraction.

Four layers of abstraction can be depicted as below:

Client Layer

This layer is closest to the application. SQL Azure connects to client applications with a Tabular Data Stream interface in exactly the same way SQL Server does. The client layer can range from a managed .NET application to a PHP application. It could be a SilverLight web application or WCF Data Service. Client layers connect with other layers using a Tabular Data Stream. The client layer may be in a client data center [On premise] or it may be in a Data center of service provider [Cloud].

Service Layer

The service layer performs the task of provisioning the database to the user, cross-ponding to Azure account. This mainly performs

1. Provisioning of Database to an Azure account
2. Billing of the usage
3. It performs the task of connection routing between client applications from the client layer to the actual physical SQL server in the Platform layer.

Platform Layer

Physically, SQL Server resides in this layer. There could be many instances of SQL server. 

SQL Azure Fabric manages the different SQL Servers.

Tasks performed by SQL Azure Fabric:

1. It enables automatic failover
2. It does load balancing
3. It does automatic replication between physical servers.

Each instance, SQL Server is individually managed by SQL Azure Fabric.

Management Services performs these tasks:

1. Monitoring health of individual servers.
2. Installing patch up
3. Upgrading services
4. Automated installations 

Infrastructure Layer

In this layer IT, level administration tasks are performed. Physical hardware and operating systems are being administered in this layer.

Scalability in SQL Azure

Federations are introduced in SQL Azure for scalability. Federations help both admins and developers scale data. It helps admins by making repartitioning and redistributing of data easier. It helps developers in routing layers and sharding of data. It helps in routing without application downtime.

Federation is the basic scaling of objects in the SQL Azure Database. Federations are the partitioned data. There can be multiple Federations within a database, and each different Federation represents a different distribution scheme.

We create Federations based on different distribution schemes and requirements. The Student table and Grades table of a School Database may have different distribution requirement so they are put into different Federations.

Each Federation object scales out data to many system-managed nodes. Federation objects contain: Distribution Scheme and Range, Distribution key and Data type.

Federation members are managed directly by the federation while data are partitioning. All Federation objects are stored in a central system Database called the Federation Root. Each Federation object is identified by a Federation Distribution key. The Federation key is used for partitioning the Data. A Federation key represents Distribution style and Data Domain.

Data is being scaled out in Atomic Units. All Data of a particular atomic unit stick together. Atomic units can be separated and all the rows of a particular Partition key stay together and are distributed together. An Atomic unit is an example of a Federation key. 

Billing of SQL Azure

1. Web edition databases are billed at the 1GB rate for databases below 1GB of total data or at 5GB rate for databases between 1GB and 5GB size.
2. Business edition databases are billed at 10GB increments (10GB, 20, 30, 40 and 50GB).

In a particular day, billing is done on the pick DB size. To understand billing better let us look at an example. Suppose there is a Web Edition DB with a maximum size of 5 GB. On a particular day if database usages are 800MB then you will be charged for 1 GB on that day. If next day DB usage increases to 2 GB then you would be charge for 5 GB. In the Business edition the charging window is 10 GB.

SQL Azure introduces two dynamic management views: database_usage and bandwidth_usage. These views can be used in TSQL for billing and bandwidth information.

Summary

So how do you know if SQL Azure is right for you? If you're a small company and don’t want to worry about learning all about networks and programming and server needs – SQL Azure will have you covered. If your customers have begun complaining about loading speed, the multiple servers of a cloud database will help and you should check out SQL Azure. If you’re interested in making your business a global enterprise, SQL Azure will also help with this. 

A simple explanation of SQL Azure is that it is a database storage provider. It is not a licensed program that requires your own servers, but a service provider that creates “virtual” databases for you and even helps you administer them. It cuts down on the costs of running a business and the headaches associated with database creation.

Creating a database using SQL Azure is very simple, and your knowledge from other products like SQL Server will transfer over. Azure also features easily customized security options that you can set with a few clicks of the mouse. One of the most popular features of SQL Azure is the pay-as-you-go option, which breaks your daily usage down into easily billable units.

Cloud databases like SQL Azure are a great new option for small businesses, or anyone looking to streamline their storage processes. SQL Azure has the edge that it is easy to use, has great support, and is backed by a reputable company like Microsoft.