SQL Azure is the cloud based relational database from Microsoft. Businesses can take advantage of SQL Azure which provides availability, scalability, and security of
data without being really concerned about the infrastructure.
The key principles of SQL Azure are:
Managed Service - delivering information platform capabilities as fully managed services which consumers can
consume, without having to worry about provisioning, deploying, and managing underlying hardware and software.
Scale on Demand - flexibility to scale out depending on needs and a pay-as-you-grow business model.
Instead of maintaining our own server or machines, we can exploit the acres of internet-accessible servers. We can rely on service providers who maintain servers
for our business data. Applications that run on desktops and mobile devices can use services in the cloud to synchronize information across many systems or in
other ways. This is done exploiting the cloud’s capabilities. Microsoft’s Azure Services Platform is a group of cloud technologies, each providing a
specific set of services to application developers.
This is a Silverlight based Windows Azure platform (www.windows.azure.com)
that provides access to service deployment and management tasks as well as at-a-glance status information that lets you know the overall health of your
deployments and accounts. The Management Portal organizes the components of your Windows Azure deployments with constantly refreshed information that’s
easy to discover and understand. Below is the figure of management portal.
Technique to upload on premise Database on SQL Azure
different ways to upload our on premise database into SQL Azure. From SQL Azure
through 1433 port we can connect to the server of management portal. If you
have an account in Azure you can upload your database into the cloud. Below are
different ways to upload the DB in SQL Azure.
- SQL bulk copy API and SQL Server Integration Service (SSIS).
Copy Function (SQL Azure model Wizard).
above 3 ways of the article covers the first 2 options.
Generate script wizard
- Right click on database.
- Click Next.
- You will have two options. Select accordingly.
- Select the script type follow the wizard.
In the Set Scripting Options page choose "Advanced". Set the script type to "Schema and Data" and "script Use Database" to False.
- Progress status
- Uploading Database to SQL Azure à all steps are below:
One of the basic steps in migrating an application to the cloud is uploading the database to the cloud. With Windows Azure Connect it is possible
to bind the cloud application to a SQL server running on premise but this is not recommended. The reason is simple. Traditional SQL is running on a single
server which by design does not scale and is not very reliable. The cloud is all about scalability, reliability and availability. Running your data on a
single server might break that. I consider connecting on premise databases to Azure only as the first step in the migration process. The goal is to bring the data to the cloud.
- Create a new empty database in SQL Azure.
- Using SQL Management Studio 2008 R2 Create a script that creates
- Convert the script to be compatible with SQL Azure
In this post I created an empty database and called it pubs.
Access the SQL Azure DB from management studio using
your credential with SQL Azure server details. Select the appropriate DB and
run the script.
Click "Execute" and that is it!!! Refresh the database and
make sure that all the data is here.
Common problems while porting SQL 2008 on-premise DB to SQL Azure
Problem 1: If you get error as shown below then
you have to remove all ‘N’ from SQL SP Script. But if you generate the script
from SQL 2008 R2 SP1 or SQL 2012 then it will work properly.
Problem 2: Another problem is that SQL Azure doesn’t support heap tables. A heap table is one without a clustered index. SQL Azure currently
supports only clustered tables.
You need to make some changes for your script to run under SQL Azure. Here’s what to do:
- Delete all instances of
SET ANSI_NULLS ON.
- Delete all instances of
- Delete all instance of
PAD_INDEX = OFF as well as
ALLOW_ROW_LOCKS = ON and
ALLOW_PAGE_LOCKS = ON.
- In the Users table, modify the rowguid column, changing
DEFAULT NEWSEQUENTIALID() to
- In the stored procedure, remove the
- Add a clustered index to any heap tables.
There are some guidelines below which we have to keep in mind when we will upload the data using this option.
- ON [PRIMARY] isn’t needed because, SQL Azure hides all hardware-specific access and information. There is no
concept of PRIMARY or file groups because disk space is handled by Microsoft, so this option isn’t required.
- According to SQL Server Books Online (BOL) you can remove the entire WITH clause that contains the table
options. However, the only table options you really need to remove are those listed in above step 3 (
NEWSEQUENTIALID () function isn’t supported in SQL Azure because there is no CLR support in SQL Azure, and
thus all CLR-based types aren’t supported. The
NEWSEQUENTIALID () return value is one of those types. Also, the ENCRYPTION option isn’t supported because SQL
Azure as a whole doesn’t yet support encryption.
SQL bulk copy API and SQL Server Integration Service (SSIS) Steps
- Right click on DB.
- Click Next.
- Default it will display selected db with the below details. Don’t change anything if database in local system else select SQL server authentication and provide credential.
- Give here your SQL Azure connection details with credential. Select destination “.NET Framework Data Provider for
- Select copy data from one or more tables or views then click next.
- Here you can uncheck some table if you don’t want to export the data for a specific table.
- Select the option like below and save the file in a path.
Here if we select “Run immediately” then it will start uploading data in SQL Azure. This
process is known as “SQL Bulk Copy”. If you select “Save SSIS Package” then below are the pending steps which we have to complete.
- At the end create a SSIS project steps below:
- Delete the existing package and add this newly created package by right clicking on the add package and select add new item.
- While selecting the package we have to make sure that we have selected the newly created package. See 7.1.
After adding new package successfully execute this package by right clicking on the new package. Then data will be upload from SSIS to SQL Azure DB.
In this UseCase we took a database for uploading to SQL
Azure. There are total 40 tables and 238 stored procedures and the database
size is 599.13 MB. We have uploaded table schema and stored procedures. It took
exactly 8 minutes 4 seconds with speed of 0.40 mbps using the generate script
upon the database size we can chose one the above stated option if it is a
large database then use SQL bulk copy API option. From the developer
prospective there is no need of extra knowledge to upload the data on SQL
Azure. There are various ways to upload the data from on-premise data to SQL
Azure and also some tools are available like SQL Azure Migration Wizard which
could help us to upload the data from on-premise to SQL Azure.
- SQL Azure Tutorials - Helping you with Cloud Databases