Using Windows Azure Management Portal with SQL Database

Windows Azure Management Portal gives you an idea of how your subscribed services are performing in the cloud environment. The Windows Azure Portal is supported in multiple languages so it gives you additional functionality. The portal supports the following services:

  • Web Sites
  • Virtual Machines
  • Mobile Services
  • Cloud Services
  • SQL Databases
  • Storage
  • HDInsight
  • Media Services
  • Service Bus
  • Visual Studio Online
  • BizTalk Services
  • Recovery Services
  • Networks
  • Traffic Manager
  • Management Services
  • Active Directory

Today we are specifically going to focus on SQL Databases component within the Windows Azure Management Portal. We have already provision a Web edition SQL Database with Windows Azure services. Please review our other blogs posts on how to deploy SQL Database with Windows Azure.

You can log into Windows Azure Portal from multiple sites. We are including two of them here:

http://www.windowsazure.com/en-us/services/management-portal/

http://www.windowsazure.com/en-us/

Afer we logged in, we got the following screen capture. It shows that we have a SQL database named Sales and Status=Online

Windows-azure-managment-portal1

Next we would like to manage our SQL Database that’s available under the Windows Azure subscription. For this click on SQL Databases in the left navigation bar. You will see two tabs in the right pane, one highlights the databases, the other one servers.

How-to-manage-sql-azure-database2

In order to administrator we need to connect to the actual SQL Azure database. So there really are two different steps:

  1. Log into the Windows Azure portal
  2. Then log into SQL database (formerly known as SQL Azure database)

Next click on Manage button available in the bottom middle. When we did that we got the following message.

Windows-Azure-SQL-Database3

This basically means that our current IP address is not included in the Firewall Rules, rules that are setup to control access to our data. My frens, that is a good thing! Click on Yes. If you get a Pop-up message, add Microsoft.com to the acceptable sites.
Next we are prompted to log into our server. You will need the following info:

  • Server
  • Database
  • User
  • Pwd

Here is what it looks like on our end.

Logging-into-sql-database4

After we enter the correct info, we are finally at the SQL Database administration level. In this blog post, we are not going to cover the details of DB Administration. We have existing blog post and plan on writing about new functionality.

Here is what it looks like on our computer monitor.

Administration-of-sql-cloud-database5

Now you are at the database level.

SQL Database Related Links

Happy New Year 2014!!

Posted in Administration, Cloud database, What is SQL Azure, Windows Azure Platform | Tagged , , | Comments Off

Windows Azure SQL Database – Tutorial video on Cloud database

In this tutorial video on Windows Azure SQL Databases , we cover Microsoft Windows Azure signup process. Windows Azure SQL Databases is a cloud based relational database built on SQL server technologies. It is a scalable, self managing and highly available database service hosted by Microsoft in the cloud. You can either sign up on the Windows Azure Platform website or the Microsoft online services customer portal.

For our demo on Windows Azure SQL Databases, we go ahead and sign into our Live account and select Windows Azure Platform Introductory Special. In this training demo on Windows Azure SQL Databases, we also cover some of the background information regarding pricing and features. We go ahead and create a SQL Azure server, setup administration username and password account and then walk you through rest of the setup process.

 

 

Posted in How to Video, SQL Azure tutorials, Video Training, What is SQL Azure | Tagged , | Comments Off

Resources on Windows Azure SQL Database

There is quite a bit of resources on Windows Azure SQL Database. However these websites and resources are not listed in one single location. I figured we should have a short list of vital websites you need to know about when it comes to Windows Azure SQL Database.

SQL Database documentation – Windows Azure

Blog posts on SQL Database

SQL Database Tutorials

Management Portal for SQL Database

Accounts and Billing in Windows Azure SQL Database

Windows Azure SQL Database

How to manage your Windows Azure subscription?

SQL Server Blogs

Posted in Uncategorized | Comments Off

SQL stored procedures in Windows Azure SQL database

Using stored procedures in Windows Azure SQL database is a conventional wisdom. Store procedures typically run faster than Adhoc SQL statements. The reason for this is that they are pre-compiled and SQL Server has generally a good idea about the execution plan it needs to take. Another advantage is that with stored procedures, you can use input and output parameters. You can call one store procedure from within another procedure. One of biggest benefit of using stored procs is code reuse, the concept you build it once and use it over and over again maybe from different application or interfaces. The Transact or TSQL code within the store procedure will run together as a unit and is typically used to do certain tasks like outputting a certain report, returning some sort of value back to the client application or at the very least execute a collection of SQL statements together as a batch. We are going to show you a simple example of using stored procedure utilizing Windows Azure SQL database.

/*————————————————————————————–

We create a simple stored procedure here:

-passing two parameters, city and country

-run a select statement on customers table

————————————————————————————–*/

 –SIMPLE PROCEDURE TO PULL CUSTOMER DATA BY CITY AND COUNTRY

 

Create PROCEDURE [dbo].[PROC_CUSTOMER]

–PASSING IN TWO PARAMETERS

@City VARCHAR(50),

@COUNTRY VARCHAR(50)

AS

–THIS SUPPRESSES MESSAGES ABOUT NUMBER OR ROWS RETURNED

SET NOCOUNT ON

SELECT

CustomerID

,CompanyName

,ContactName

,ContactTitle

,Address

,City

,Region

,PostalCode

,Country

FROM CUSTOMERS

WHERE

CITY=@City

AND

COUNTRY=@COUNTRY

/*

DECLARE

@CITY VARCHAR(50),

@COUNTRY VARCHAR(50)

SET @CITY=’LONDON’

SET @COUNTRY=’UK’

EXEC PROC_CUSTOMER @CITY, @COUNTRY

–OR

EXEC PROC_CUSTOMER ‘LONDON’, ‘UK’

*/

GO

 

Posted in Development, SQL Programming, TSQL | Tagged , | Comments Off

How to create Windows Azure SQL Database?

For this post, we are assuming you do not have a Windows Azure account. We will create a brand new Windows Azure Cloud SQL Database. You will also need to have a FREE Live.com. So lets start with the basics. Please browse to this location.

http://www.windowsazure.com/en-us/

Click on Free Trial. At this point they will have you log into Live.com account. If you have not signed up for an account, you can do that at this web address.

https://login.live.com

Once you are signed in, after that you will get the following screen shot: It will also take you to this location.

windows-azure-free-trial1

https://account.windowsazure.com/signup?offer=ms-azr-0018p

In Part 2, you can verify the account using a phone. We entered our phone and then clicked on Send text message. Got the code and then entered the verification code.

In Part 3, you enter the billing info. This includes info on:

  • Account
  • Address
  • Credit card info

After this we got confirmation that setting up our Windows Azure subscription was complete. After we clicked on Portal, here is what it looks like on our computer monitor,

windows-azure-database2

In case you cannot find the portal, here is the direct website address for Windows Azure portal.

https://manage.windowsazure.com

We also got the Windows Azure tour shown below.

When we selected SQL databases in the Portal, there was no database. We are going to create one in the next step. We also choose the Cloud database name, edition, size and pick New SQL Database server.

The two following screen captures display what this Cloud db creation looks like on our end.

sql-azure-database4

create-sql-azure-database5

Next it is asking for Azure SQL database access info. Please remember to make it secure. Go ahead and enter the login name /pwd info. The password must meet the following requirements:

  1. Is more than eight characters in length
  2. Does not contain all or part of the login name
  3. Contains characters from at least three of the following categories:
  • English uppercase characters (A through Z)
  • English lowercase characters (a through z)
  • Base 10 digits (0 through 9)
  • Non-alphanumeric characters (for example: !, $, #, %)

We have included a graphic display for this:

Click on the check mark at the end. At this point, you are all set with a new Cloud database. In the next posts, we will show you how to create and design objects in Azure database. We will also import and export data. Here is what the final SQL database in Windows Azure looks like:

 

 

 

 

Posted in Cloud database, SQL Azure tutorials, What is SQL Azure, Windows Azure Platform | Tagged , , , | Comments Off

How to create table in SQL Azure – Cloud Database

In this SQL Tutorial video, we show you how to create a table in SQL azure database. This is the cloud database from Microsoft. We use CREATE TABLE sql command to create two tables. Next we go over the Alter table command which can be used to add (ADD), modify (ALTER COLUMN) and delete columns (DROP COLUMN) in an Azure SQL table. You can also add constraints like Primary key constraint, foreign key constraint using the Alter Table command. We also go over how to delete the table from SQL azure cloud database using the DROP TABLE command. For this video we have to use SQL Server Management Studio from SQL server 2008 R2 version

 

For additional videos, please visit our YouTube Channel:

https://www.youtube.com/user/baghul?feature=watch

 

Posted in Development, How to Video | Tagged , | Comments Off

Microsoft drops SQL Azure cloud database pricing

In terms of low cost structure for cloud database service , Microsoft SQL Azure is already a viable option. However there seem to be two issues with their cost structure.

  1. One was for databases that are smaller in size between range of 50 – 100 MB in size.
  2. The other issue was with VLDB that are greater than 150 GB in size.

With their new pricing structure introduced last year, they are addressing both these issues. In addition there is a significant reduction in price for other cloud databases as well. The table below goes over in detail the database size, before and after pricing structure and the percentage decrees from the original cloud database price plan.

GB Previous Pricing New Pricing New Price/GB Total % Decrease
100 MB NA $4.99 NA NA
5 $49.95 $25.99 $5.20 48%
10 $99.99 $45.99 $4.60 54%
25 $299.97 $75.99 $3.04 75%
50 $499.95* $125.99 $2.52 75%
100 $499.95 * $175.99 $1.76 65%
150 $499.95* $225.99 $1.51 55%

Source:

http://blogs.msdn.com/b/windowsazure/archive/2012/02/14/announcing-reduced-pricing-on-sql-azure-and-new-100mb-database-option.aspx

As you can see there is significant lowering of cost for these databases in the cloud service. If you are interested in finding further details feel free to check out the following websites:

-https://www.windowsazure.com/en-us/pricing/details/#header-4

-http://blogs.msdn.com/b/windowsazure/archive/2012/02/14/announcing-reduced-pricing-on-sql-azure-and-new-100mb-database-option.aspx

 

 

Posted in Cloud database, Installation | Comments Off

Import data from SQL Server 2008 to SQL Azure

In this training demo on SQL Azure, we will demonstrate how to move data from local on premise SQL Server 2008 to SQL Azure in the cloud environment. Before you get started, you do need to have the destination table in Azure SQL database first. Using transact SQL code we create the PRODUCTS table and also create clustered index on it. Next we use Business Intelligence Development Studio (BIDS) to create a SQL Server Integration Services (SSIS) project…

You can watch the sample video on this training at this location: (Right click – Save As)
http://www.sqlazuretutorials.com/free-videos/Import-data-SQL-Azure-utube.wmv

Complete information on SQL Azure Cloud database can be found at Sql Azure Training page.

 

Posted in How to Video, Migration, Video Training, What is SQL Azure | Tagged , , | Comments Off

How to create database objects in SQL Azure

In this blog post, we are going to show you how to create the following important database objects in SQL Azure cloud database:

  • Database
  • Table
  • Stored procedure

If you want more information about our TSQL scripts on SQL Azure, plz visit this blog post:

http://sqlazuretutorials.com/wordpress/transact-sql-scripts-sql-azure-video-tutorials/

 

Create Database in SQL Azure

You can create a database in SQL Azure with the CREATE DATABASE command.
Here is the syntax:

CREATE DATABASE database_name
{
   (<edition_options> [, ...n])
}
<edition_options> ::=
{
   (MAXSIZE = {1 | 5 | 10 | 20 | 30 | 40 | 50} GB)
    |(EDITION = {‘web’ | ‘business’})
}
[;]

One item to note is the missing key clause ON PRIMARY. In addition notice you do not need to define the Data and Log file info as you typically do in an On-Premise SQL Server. These are taken care of by Microsoft Cloud services.

USE [master]
GO

CREATE DATABASE [HR]
GO

 

Create Database Table in SQL Azure

You can create a table in SQL Azure with the CREATE TABLE command.
Here is the syntax:

Create table [table_name]
(
    [field 1] [data type]
    [field 2] [data type]
    [field 3] [data type]
)

Please note the missing key clause ON PRIMARY, we have included the error we received below. Another fact is that you cannot specify the “USE” clause in SQL Azure. You have to open a session with a specific database and then execute the SQL code.

–USE HR does not work

CREATE TABLE dbo.Customer(
    CustomerId int IDENTITY(1,1) NOT NULL,
    CompanyName varchar(100) NULL,
    CustomerLastName varchar(100) NULL,
    CustomerFirstName varchar(100) NULL,
    CustomerDOB date NULL,
    CustomerLastName varchar(100) NULL,
    City varchar(100) NULL,
    Country varchar(100) NULL,
    ModifyDate date NULL,
 CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED
(
    CustomerId ASC
)
)
GO

 

Create a Stored procedure in SQL Azure database

Stored procedure are a vital component of a database as they provide the following:

  1. Performance gain
  2. Reusability
  3. Security
  4. Manageability

We create a simple stored procedure here that we pass in two parameters, city and country. It then runs a select statement on customers table.

CREATE PROCEDURE [dbo].[PROC_CUSTOMER]

–PASSING IN TWO PARAMETERS

@City VARCHAR(50),
@COUNTRY VARCHAR(50)

AS

–THIS SUPPRESSES MESSAGES ABOUT NUMBER OR ROWS RETURNED
SET NOCOUNT ON

SELECT
CustomerID
,CompanyName
,ContactName
,ContactTitle
,Address
,City
,Region
,PostalCode
,Country
FROM CUSTOMERS
WHERE
CITY=@City
AND
COUNTRY=@COUNTRY

/* Execute the following to test the Stored Procedure

DECLARE
@CITY VARCHAR(50),
@COUNTRY VARCHAR(50)
SET @CITY=’LONDON’
SET @COUNTRY=’UK’
EXEC PROC_CUSTOMER @CITY, @COUNTRY

–OR

EXEC PROC_CUSTOMER ‘LONDON’, ‘UK’

*/

Posted in Development, SQL Programming, TSQL | Tagged , , | Comments Off

Windows Azure subscription: How to cancel SQL Azure subscription?

SQL Azure is Microsoft’s version of Cloud Database. SQL Data Services are now known as Microsoft SQL Azure and SQL Azure Database. There are a few changes, but fundamentally Microsoft’s plans to extend SQL server capabilities in cloud as web-based services remains the same. SQL Azure will continue to deliver set of services for relational databases. The reporting, analytics and data synchronization with end-users remains unchanged. This makes it most appealing to current users of SQL Server.

We have been running SQL Azure on a few servers for a while. The service is great and Microsoft continues to add new components and features to its cloud database. Now what if you wanted to cancel a SQL Azure subscription? If you are in SQL Azure portal, good luck finding this! In order to make this happen you have to follow a certain number of steps from a different site. Here are they:

Login in to https://account.windowsazure.com

 

windows-azure-portal
Sign in with your Live ID Account. Next click on “Subscriptions”.

This is what we see on our end:

 

windows-azure-subscription
Select the Azure subscription which you would like to cancel. In our case we are going to choose Pay-As-You-Go Azure subscription.

This is what we see on our computer screen.

 

ms-sql-azure
Scroll all the way to the bottom right side of the page. Go ahead and click on “Cancel Subscription”.

We have included a screen capture for you.

cancel-windows-azure-account
This will take you to a new screen. Love the “Hearbroken” message . Select “yes, cancel my subscription” option. Click on the tick mark to complete cancellation.

 

cancel-windows-azure-subscription

 

Now it should take care of it. When it returns you to the Subscription page, you should see Azure subscription gone!

For more cloud database related topics, visit our main site on SQL Azure.

Posted in Administration, Windows Azure Platform | Tagged , | Comments Off