Differences between Microsoft SQL Server and SQL Azure

SQL Azure is a Relational Database Management System (RDBMS) in the Cloud. It is built on the SQL Server platform and has full support for using Transact SQL (TSQL) programming language. Unlike your local (On-premise) SQL server, SQL Azure is accessible via the Internet. If you prefer wizards, SQL Azure may cause you some grief because they’re not there. For the most part, SQL Azure is quite functional especially if you just want to house data in a “Highly Available” environment for a competitive price. Still there are quite a few differences between SQL Server and SQL Azure.

Lets take a look at few of these differences right below:

Product/Feature SQL Server SQL Azure Additional Info
SQL Database Engine Y Y
Management Studio (SSMS) Y Y http://www.telerik.com/help/openaccess-orm/getting-started-root-quickstart-azure-connect-through-ssms.html
SQL Agent Y N
SQL Tables Y Y In Azure, every table needs a clustered index
SQL Views Y Y http://msdn.microsoft.com/en-us/library/windowsazure/ee336244.aspx
Stored Procedures Y Y More info: http://www.youtube.com/watch?v=iRY5U0341bY
Windows Logins Y N
SQL Logins Y Y
Reporting Services (SSRS) Y Y http://sqlazuretutorials.com/wordpress/sql-azure-reporting-server-windows-azure-platform/
Integration Services (SSIS) Y N
Analysis Services (SSAS) Y N
Federations N Y http://geekswithblogs.net/shaunxu/archive/2012/01/07/sql-azure-federation-ndash-introduction.aspx
High Availability N Y http://blogs.msdn.com/b/jackgr/archive/2011/10/22/high-availability-on-the-azure-platform.aspx
SQL Profiler Y N
Execution Plan Y Y http://www.scarydba.com/2012/08/20/another-execution-plan-in-the-cloud/
Full Text Search Y N
Firewall N Y
TSQL Support Y Y http://msdn.microsoft.com/en-us/library/windowsazure/ee336281.aspx
.NET  Support Y N
Database Size limitation N Y In SQL Azure, database size is limited to 5 GB on Web edition, 150 GB on Business edition
Database backups Y N http://social.msdn.microsoft.com/Forums/en/ssdsgetstarted/thread/b2a3c161-96b1-4201-ac46-a747e1806be1

Note: High Availability is obviously available in SQL Server but you have to set it up. In SQL Azure, it is already built in.

Additional Info on SQL Server and SQL Azure:


Posted in SQL Azure tutorials, What is SQL Azure | Tagged | Comments Off

Manage a SQL Azure database via the portal

One way to manage SQL Azure database is through SQL Server Management Studio. Using the Azure web portal, you can also management the following database operations:

  • Create and modify tables
  • Program Stored Procedures and functions
  • Import and export data
  • Write and execute SQL statement

We will walk you through how to perform some of these tasks. After you have created a Windows Azure subscription and setup initial database, you can log into the account from this location:


Click on Portal. This will open up a new window shown below. Here is a screenshot of what it looks like:


Go ahead and enter the username and password information. This will connect you to your Azure subscription. Go ahead and expand Subscriptions > Kash LLC > mn51jiybe4 (left navigation bar). Next select Database from the left navigation. We choose Northwind database and select Manage from the top menu.

Here is a screen of what it looks like on our end:


This will launch the SQL Azure login screen. Go ahead and enter the database user credentials. These are different than the Windows Azure login credentials. We have included a figure right below.

Here is what it looks like on our end:


This launches screen of Database Properties with basic summary information. Here is what it looks like:


Select Overview from the left pane and choose Design database from the middle screen. Using this option, you will be able to modify database objects including:

  • Tables
  • Views
  • Stored Procedures


Now you can see the Tables option selected on the top. Right below you can see the different tables in Northwind database. You can design the table here and even create a New table if you like.

Here is a screen capture from our computer:


Next we select the Stored Procedures option. We highlight this proc PROC_CUSTOMER and choose Design. It will open up a new window where you have the ability to design the procedure using TSQL code. You can see input parameters and the actual code.

Here is a screenshot of what it looks like:


Using the SQL Azure portal, you can also write and execute Transact SQL (TSQL) statements. Lets say you would like to write a SELECT statement. We do that on Customers table and here is the SQL statement.



Posted in Administration, Development, What is SQL Azure | Tagged , | Comments Off

Security considerations in SQL Azure

Before we dig deeper into SQL Azure Security, here are some basic concepts:

Principal: This is an entity that can request SQL Azure resources. Principals can include sql logins (access to SQL Azure server) and database users (access to SQL Azure database)

Role; A role in SQL Azure is analogous to a group in Windows. So if the Accounting dept has database users with the same level of permissions, it is better to create a role and assign perms to it

Schema: Container of database object like tables in SQL Azure

Securables: Actual resources in SQL Azure that can be access. An example of this is a table or even a database.

Steps for Security in SQL Azure

  1. Create a login, has to be done in master database
  2. Create a database user for the previous login
  3. Assign the user to a role (optional but highly recommended)
  4. Assign permissions to the user

Here is a sample video tutorial that covers SQL-Azure-Security-Youtube (Use Right click – Save As)

I am including the Transact SQL scripts for creating and managing the security in SQL Azure database

–create sql login

/****** Object: Login [hr_owner] Script Date: 4/26/2012 9:41:57 PM ******/

–create database user

/****** Object: User [hr_owner2] Script Date: 4/26/2012 9:53:28 PM ******/
CREATE USER [hr_owner2] FOR LOGIN [hr_owner2] WITH DEFAULT_SCHEMA=[dbo]

–create database role

/****** Object: DatabaseRole [hr_role2] Script Date: 4/26/2012 9:59:43 PM ******/
CREATE ROLE [hr_role2]

–add user hr_owner2 to database role hr_role2

sp_addrolemember ‘hr_role2′, hr_owner2

–How to lookup members (users) in a database role

select p.name as role_name, m.name as user_name
from sys.database_role_members drm
join sys.database_principals p
on drm.role_principal_id=p.principal_id
join sys.database_principals m
on drm.member_principal_id=m.principal_id

Posted in How to Video, Security, SQL Azure tutorials, TSQL | Tagged , | Comments Off

How to rebuild an Index in SQL Azure using ALTER INDEX

Indexes help with database performance. If you do not have an index on a table, SQL Azure has no choice but to use a Table Scan. If you do not know, Table Scan is slowest method of retrieving data from SQL. You can either have a clustered or a non-clustered index in SQL azure. As new data is inserted in a table and existing data gets updated, table indexes can become scattered or fragmented. This typically happens when the logical ordering of a data page does not match the physical ordering. In order to defrag an index, you can either rebuild or reorganize an index. If there is heavy fragmentation, rebuilding an index is a better choice.

This method is what we are going to use in today’s topic on SQL Azure.

–How to rebuild an Index in SQL Azure using ALTER INDEX ALL command


Here are the specifics of this script:
-Using Try Catch block
-Create a temp table
-Load the table names into the temp table
-Loop through the records (table) one by one
-Rebuild the index on a table using ‘alter index all’ statement
-Drop the temp table














































PRINT ‘—————————————————’






PRINT ‘—————————————————’





PRINT ‘—————————————————’








For additional reading, please visit this page:


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

What is SQL Azure? From SQL Azure Tutorials.com

What is SQL Azure?

SQL Azure is a Relational Database Management System (RDBMS) in the Cloud. It is built on the SQL Server platform and has full support for using Transact SQL (TSQL) programming language. Unlike your local (On-premise) SQL server, SQL Azure is accessible via the Internet. In some sense with this cloud database from Microsoft, you are outsourcing your data layer to the cloud. By taking such a step, you will be cutting down the costs associated with:

  • Installing and configuring SQL server
  • Implementing High Availability
  • Disaster recovery and Fault Tolerance
  • Maintenance and Service Packs application

These are great advantages as you can focus your efforts more on your Application and Database Development rather than Administration. You will still have to do things like creating databases, designing tables, building stored procedures, configuring users and other development activities on the cloud database. Still this is a BIG step in the right direction especially for small companies as there databases will be managed by Microsoft in the cloud environment.

How to you setup SQL Azure server and cloud database?

In order to get SQL azure, you have to create a Windows LIVE ID and sign up for service at Windows Azure Portal. Currently they are offering the Cloud service as a 3 month FREE SQL Azure trial. Speaking of pricing, Windows and SQL Azure use the “utility model”. You will only be billed for the amount of usage that you incur just like an electric/gas utility model. This is a new paradigm for computing as it lets you scale up or scale down as your business needs change through the project lifecycle. We have been using SQL Azure mostly for development and pay around $10/month for  a High Available, Fault Tolerant database!

If you have now not signed up for Windows or SQL Azure service, you can review of one of our old blog posts that discuss this in detail. Here is the complete address

For today’s tutorial, we are going to login to the new SQL Azure portal and highlight a few things so you will know “What is SQL Azure”. Next we are going to use the Management Portal for SQL Azure to connect to our database server. We will then create a new database table under the Northwind cloud database. First we went to this site: http://sql.azure.com

We logged in with your Windows Live ID credentials. When we did that, it took us here: https://windows.azure.com/default.aspx

In the following screen shot, you can see that we are logged  into the Windows Azure Platform. You can see that we have one subscribtion named Kash LLC. When we select the Database tab in the left Nav bar, you can see our Azure database server (mn51…) in the middle portion of the screen.


Next we want to lookup our cloud databases on the this SQL Azure server. We go one level deeper and expand the Subscription node. You can now see that we have two databases, Master and Northwind under our Cloud server.


We select Northiwnd database, then click on Manage (Database) in the top Toolbar menu. This will launch the newly designed Management Portal for SQL Azure shown below. Notice that you do have the ability to do the same thing using ssms 2008 R2.


We enter our login information here and then press Log on. If you receive an error, please make sure that the Firewall is accessible to the computer you are trying to login from. This is a common issue when logging into SQL Azure.

Next you should see the dashboard within the Management Portal for SQL Azure. Here is what is look like on our machine.


We are going to create a new table in our Cloud database. We already have the TSQL ready for it. Just need to execute in SQL Azure. Click on New Query. You will get a window that looks somewhat similar to SQL Server Management Studio. Go ahead and paste the SQL here.

This is how it looks on our end.


After you click Run from the Toolbar, this should go ahead and create the table. If you want to verify it, you can select Design option from the left menu. This will take you to the Object Browser where you can see database objects like tables, views and stored procedures.


We conclude this blog post and hope you found it helpful in understanding SQL Azure and Management Portal for SQL Azure. Please visit our SQL Azure videos page for further information.

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

SQL Azure Reporting Server – Windows Azure platform

Using SQL Azure Reporting, you can deploy a reporting solution to the Windows Azure platform environment. This solution will not only use data from the cloud SQL Azure database, you will also be deploying the actual Azure report up to the cloud environment.  This will make the Cloud database report accessible to all to the end users who can access the data via the internet.

In order to create the SQL Azure report, we need to do the following three things:

  1. Create a new SQL Azure reporting server in Windows Azure platform
  2. Build a report using SQL Server business intelligence studio development studio (BIDS)
  3. Deploy the SQL Azure report to the cloud environment

Today we are going to simply look at the first step,

Create a new SQL Azure Reporting server in Windows Azure platform

We are assuming you already have a Windows Azure account and that you have already setup a SQL Azure cloud database. If you have not, please read this blog post on the topic.


We login to our Windows Azure account at this site:


Next we are going to create a SQL Azure Reporting server. This will be used to render reports from our SQL Azure cloud database to the Windows Azure platform. From the left menu, click on Reporting tab and then select Create a new SQL Azure Report Server from the right side.

This is shown right below.


Next you will get the SQL Azure Reporting server agreement. Go ahead and click “I agree …” and then click Next. This is shown in the screen shot right below.


In the next section, you can select the SQL Azure Subscription that you would like to use for the cloud database report. Select the Region for the SQL Azure Reporting Server should use. Ideally this location should be the same as the one you selected when you set up your original SQL Azure Server.

Since we are in Dallas TX, we have listed ours as South Central US. This is shown right below.


In the next step, we are going to setup the administrator account for SQL Azure Reporting Server. You will need to have a username and password for this account.  We enter our account information in the next dialog box. Click Finish after this.


Next it will go ahead and create the reporting server.  In our case the Azure SQL Report Server has the web service URL are listed below.


After we create the SQL Azure reporting server, we can look at some of the options available to us. When you are logged in your Windows Azure platform account and select the Reporting tab on the left, you will see these options on the Report server taskbar:

Server: Create and delete Report server
Manage User:  You can create and delete report users
Data Source: Ability to manage multiple SQL Azure data sources
Folder and Permissions: This can help you organize the reports and control access to them

After we created the SQL Azure report server, we see the following options for Reporting Services tab.


Finally lets go ahead and log into the SQL Azure Report Server. Go ahead and type the Web service URL for the reporting server that was provided to you. Enter the username and password to login. Here is what we see on our end.


Once you login you should see something like this as follows. Notice we do not have any report folders or actual reports create just yet. We will do that in another blog post.


Related links to SQL Azure Reporting Services:

Posted in Reporting, SQL Azure tutorials, Windows Azure Platform | Tagged , , | Comments Off

Transact SQL scripts – SQL Azure Video Tutorials

As part of the SQL Azure Video Tutorials – Unlimited Package, we have added some Transact SQL scripts.  These will help you in understanding SQL Azure and how to work with objects in the cloud database environment. Some of the SQL scripts are used to create objects like database, table, indexes, primary keys and foreign key constraints. Other SQL statements include the DML commands like: INSERT, SELECT, UPDATE AND DELETE. Here’s the complete list of SQL statements so far:

(Note: The ones with active links can be downloaded for FREE!)

Transact SQL scripts:

  1. create_database_SQL_Azure_0001.sql
  2. create_table_SQL_Azure_0002.sql
  3. how-to-create-sql-azure-login-0003.sql
  4. sql-azure-database-user-0004.sql
  5. using-aggregate-functions-sql-server-0005.sql
  6. missing-extended-stored-procedures-Azure-0006.sql
  7. sql-statements-not-supported-sql-azure-0007.sql
  8. alter-table-cloud-database-sql-azure-0008.sql
  9. create-primary-key-foreign-key-constraints-0009.sql
  10. assign-database-role-and-manage-permissions-0010.sql
  11. how-to-use-sql-select-in-azure-0011.sql
  12. using-sql-update-qry-azure-0012.sql
  13. delete-records-in-sql-azure-database-0013.sql
  14. insert-data-into-sql-azure-table-0014sql.sql
  15. how-to-create-stored-procedure-cloud-db-0015.sql

More details can be found on this SQL Azure Training page.


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

ATTENTION Guest Bloggers:

ATTENTION Guest Bloggers:

Please contact us at blog@sqlazuretutorials.com if you:

-Would like to post SQL Azure or Cloud database related topics to our blog. This would be free advertising for you via a backlink to your site or blog! We will review the content and pick ONLY the best articles.

-Have suggestions on what topics should we cover on this blog.


Posted in Cloud database | Comments Off

Database.com – Another database in the cloud

Database.com – Another database in the cloud

Database.com is an enterprise level cloud database service offered by Salesforce.com. The platform is highly scalable so as your database needs grow with time, this cloud database system will scale up nicely. In addition Database.com is highly available so you do not have to worry about a database server going down as your databases are replicated throughout the server farm at Salesforce.com. This database platform is supported for Java, .Net and other platforms. One cool feature is development and stage environments are already set up for you from the get-go. The best part I like about Database.com is that it is free to use when you have less than 3 users and then the number of records in your cloud database is below 100,000.

Create an account with Database.com

Before we create a database with Database.com, we need to create an account. You can go to this site:


You will need to click on the Login/Signup link in the top right corner. Go ahead and enter information regarding your name, username, company and email address. Next you will get account activation information via email and you will need to follow instructions. Finally it will take you back to the Salesforce.com site where you have the ability to create a new password and security question.

Database.com layout – System Overview

Once you log into your account, you will see System Overview. This is the main dashboard with all the information related to your cloud database account. In the middle section, you have information on your database  schemas, users and business logic within the dashboard. Here a screen capture of what it looks like:

Cloud database dashboard

In the left pane, you will see the following sections:

Personal Setup: Breakdown of basic account information
App setup: Here you can create objects (database tables) and deployment tasks
Administration Setup: You can perform critical tasks related to database administration like managing users, security controls data management and monitoring

Create a new table in database.com

We’re going to use App setup to create a new database table Customer_list. After we enter the table name, some system fields are created by default including: CreatedBy, LastModifiedby and Owner. After that we create a custom field, choose the data type and the field size. Here is more information on the fields:

Data Field Data Type Field Size
CompanyName Text 100
City Text 50
Country Text 50
Phone Phone NA

How to insert or load data into Database.com table?

Now that we have the table ready in our cloud database, we need to load some customer data. In order to do that, first we need to get our customer data in a .csv (comma separated) file format. We will need information for our custom fields, CompanyName, City, Country, Phone and two additional fields CustomerListName (unique identifier) and Owner. Here is the link to the .csv file we are going to use.

Download the csv File

We’re going to use the Custom Object Import Wizard to load the data from the file to our cloud database table that we just created above.. Here is the path you need to follow:

Admin Setup – Data Management – Import Custom Object

After you start the wizard, you can follow the steps to import the file. We can also use this step to create mapping between the source field and the target field.

Browse the customer data in our Cloud database table

Finally we are going to look at the data using SQL. For database.com, we’re going to use the Workbench which is a free hosted tool that will help you write queries against your data from Database.com.  Here’s the website:


You can login using your existing credentials. Under Select an action to perform. use the following:

Jump to: SOQL Query
Object: CustomerList__c

In the next window, you will be able to select objects, fields and criteria to build a SOQL query. Under object, you will see CustomerList__c. Using Ctrl key go ahead and select these fields, City__c, CompanyName__c, Country__c, Phone__c. you will notice that as you are selecting these, it will generate the actual SQL statement. Here is what the workbench looks like on our computer monitor:

Cloud Database.com Workbench

We have included the sql statement right below.

SELECT City__c,CompanyName__c,Country__c,Phone__c FROM CustomerList__c

When you click on Query it will go ahead and return the actual data from the table. Here is part of the screen shot of what it looks like.

Database.com Query

Overall we are satisfied with using Database.com. One thing we struggled is the familiarity with SOQL query language syntax or the Apex language that they use.  Regardless that is a viable alternative to SQL azure. Part of this tutorial is also available as a video. We have included the download link below. For an interesting article on pricing between the two, please visit the first link under Related Links.

Related Links to Database.com

-Cost comparison between SQL Azure and Database.com

-Review on Cloud database from Salesforce.com

-Download sample video (this tutorial) on Database.com

Posted in Cloud database, Database.com, SQL Programming | Tagged , , | Comments Off

How to build a SQL Server Reporting Services report with SQL Azure?

SQL Server Reporting Services report with SQL Azure

Today we are going to look at how to create a report with SQL Server Reporting Services (SSRS) and Microsoft SQL Azure. In this manner we are using the database that is housed on the cloud server whereas we will be running SQL Reporting Services in the local environment. For today’s topic we are using Visual Studio 2008 with Microsoft SQL Azure RTM.

Go ahead and launch Microsoft Visual studio. Select File – New – Project. Next under Business Intelligence Projects, go ahead and select Report Server Project Wizard, given it a name and then click OK. This is shown in the following screen capture.


This will invoke the report wizard of as shown below. Using the report does serve we can select a data source, build a SQL query, design and create a SQL report.


Next the report wizard will take you to the Data Source dialog box. This is where you will select New data source, choose Microsoft SQL Azure as data source type, give it a name and then proceeds to build the connection string. We have shown this in the following screenshot.


After you click Edit, you can configure your SQL Azure cloud server connection information. This includes the Server name, SQL Server Authentication information, specific database that you’re going to connect to. Next you click OK. The following image displays our connection information.


You may get another dialogue box, where you may have to verify the login information. Here’s a screen shot of this right below


Moving onto the next step which is going to be the Query Builder. Go ahead and click Query Builder. This will take you to the Query Designer screen. This is where you can select the tables and the fields that will become part of the SSRS report. You also have the ability to add filters which we’re not going to use for now.

The following two screen captures display what we are talking about.



After you have selected the fields and clicked OK, you can see the actual SQL Azure Select statement in the next screen. Go ahead and click Next.


Next up in the Report Wizard is to actually select the report type. You can either choose Tabular or Matrix format. We are going to go ahead and select Tabular and click next.

This is shown below.


After this you can actually a group of data by different report sections if you like. You can group either by Page, Group or by the Details section. We’re not going to select anything for now and simply click Next. This is what it looks like on our computer display.


Now we will choose a table style from different custom choices. For this SQL Azure demo we are going to use Ocean and then click Next.


The report is almost complete. We would like to go ahead and deploy this SQL Azure report to a report server. For our case we are using a local report server. In your scenario it may be different so please choose accordingly. Go ahead and click Next.


We are almost done with the SSRS wizard. It is asking us to give the report a name which we do. You will notice that it is also including a little bit of summary regarding the SQL Azure server location and some of the choices that we had made to earlier.

So here is what it looks like so far.


We finally have a design view of our report pulling data from SQL Azure. The report is shown in the middle of the section. As you can see we have other screens like the Report data (all the selected fields), on the left we have the Solution explorer on the right side. The following images displays all these sections


When you select the Preview tab, the report tries to make a connection to the SQL Azure server one more time. It is asking for the login information which we provided and then click View report.


As you can see, the report is complete. We have the report name, field information and all the underlying customer data. Remember that all this information has been pulled from the Northwind database in the Azure cloud server. Right above the report, you will see options like record navigation, printing capabilities and export functionality to other formats.

Posted in Development, Reporting | Tagged , , , | 1 Comment