Windows Azure SQL Database : How to create Windows Azure SQL Database
Windows Azure SQL Database is a full featured relational database-as-a-service offering formerly known as SQL Azure.
You can attach more than one subscription with your Microsoft Account and every subscriptions can have more then one Windows Azure SQL Servers and every Windows Azure SQL Server can have more than one Windows Azure SQL Databases.
A simple illustration for provisioning model for Windows Azure SQL Database service. When you create a Windows Azure SQL Server (Note that these are logical servers, not dedicated physical servers), a master database (System Database) has been created by default. You should not put any data in master DB. Master DBs are used for credentials and audit. We’ll need to work with master DBs while securing our database, creating or tracking logins but not for storing our data. Master DBs are free. Every DB in Windows Azure SQL Database services has been replicated between 3 physical servers for high availability. Windows Azure SQL Database Service is using TDS Protocol. TDS Protocol is used by Microsoft SQL Server also. Which means TDS (version 7+) enabled applications should be able to connect to
I’ve talked about Windows Azure SQL Database on our weekly community call, you can reach to the recordings at MEA Windows Azure Champ Call #6 Windows Azure SQL Database.
In this blog entry I’ll cover different methods of Windows Azure SQL Database creation.
1. Create a Windows Azure SQL Database using Windows Azure Management Portal
2. Create a Windows Azure SQL Database using SQL Commands or programmatically
3. Create a Windows Azure SQL Database using Microsoft SQL Server Management Studio or Visual Studio
4. Create a Windows Azure SQL Database using PowerShell
You can also create your Windows Azure SQL Database with SQL Database Management REST API.
1. Create a Windows Azure SQL Database using Windows Azure Management Portal
a. Login to Windows Azure Management Portal and navigate to SQL Databases section and click “New” button on bottom-left.
b. Select “SQL Database” then “Custom Create”
c. You’ll see a modal pop-up for database settings
NAME : Name of the database
EDITION : WEB | BUSINESS ; WEB Edition supports up to 5 GB maximum and BUSINESS Edition supports up to 150 GB maximum size T-SQL based relational database. If you don’t need more than 5 GB choose WEB.
COLLATION : Default database rules for sorting and comparing. Note that collation cannot be changed after creation
SUBSCRIPTION : Select your subscription for billing purposes. You can move your databases between your subscriptions
SERVER : Choose and existing SQL Server or create new SQL Database Server. Note that these are logical servers, not dedicated physical servers
d. If you chose to create a new SQL Database Server, you’ll have another modal pop-up.
LOGIN NAME/PASSWORD : create default login for your SQL Database Server
REGION : Windows Azure Datacenter
ALLOW WINDOWS AZURE SERVICES TO ACCESS THE SERVER : If you select this your applications and services running in Windows Azure Datacenters may access to this server, equivalent to 0.0.0.0 to 0.0.0.0 firewall exception rule. If you don’t select it only allowed IP addresses may reach to your SQL Database Server.
2. Create a Windows Azure SQL Database using SQL Commands or programmatically
You can also create your databases with SQL Commands or programmatically. It is very similar to Microsoft SQL Server. You’ll need to connect to master DB and run a SQL Command.
CREATE DATABASE umitsDB (MAXSIZE=1GB, EDITION='web');
You can set the properties below in your SQL Command.
- DB Name
- Collation
- Edition
- MAXSIZE
You can also create a copy of your existing Windows Azure SQL Database with SQL Commands. You can use copy DB as a backup or analytics DB. For advanced backup/restore options I’ll post another blog entry soon. For full reference of CREATE DATABASE SQL Command visit : https://msdn.microsoft.com/en-us/library/windowsazure/ee336274.aspx
Note : If you have issues connecting to Windows Azure SQL Database service, make sure you added your IP address to the allowed IP addresses list and you can create a network connection using port 1433.
3. Create a Windows Azure SQL Database using Microsoft SQL Server Management Studio or Visual Studio
You can connect to you Windows Azure SQL Database Service using Microsoft SQL Server Management Studio. Microsoft SQL Server 2008 R2 or 2012 Management Studio (including free Express Edition) can be used.
Server Name : Your Windows Azure SQL Database Server Name
Authentication : SQL Server Authentication
Login/Password : Credentials you’ve set while creating your DB
Then you can follow option 2 “Create a Windows Azure SQL Database using SQL Commands or programmatically”.
You can also create a Windows Azure SQL Database while you’re developing. Open your SQL Server Object Explorer in Visual Studio and select “Add SQL Server”. You’ll be asked for credentials to add Windows Azure SQL Database Server. Then you can create your database easily by selecting “Add New Database”.
Note : If you have issues connecting to Windows Azure SQL Database service, make sure you added your IP address to the allowed IP addresses list and you can create a network connection using port 1433.
4. Create a Windows Azure SQL Database using PowerShell
Final option in this blog is to create your Windows Azure SQL Database using PowerShell. Most of the infra people like PowerShell and it is really helpful for some scenarios.
First, you need to prepare your environment to be able to run PowerShell commands. It’s pretty easy, Manage your Windows Azure with PowerShell just keep reading my blog.
Then you’ll use two variables : $cred and $ctx to store credentials and database context
You can create Windows Azure SQL Database Server using
New-AzureSQLDatabaseServer and
Windows Azure SQL Database using New-AzureSqlDatabase command. Don’t forget to update <username>, <password>, <servername>, <dbname> and other properties.
$cred = Get-Credential
New-AzureSqlDatabaseServer -Location "West Europe" -AdministratorLogin "<username>"
-AdministratorLoginPassword "<password>"
$ctx = New-AzureSqlDatabaseServerContext -ServerName "<servername>" -Credential $cred
New-AzureSqlDatabase $ctx -DatabaseName "<dbname>" -MaxSizeGB 1 -Edition Web
-Collation "SQL_Latin1_General_CP1_CI_AS"
Get-AzureSqlDatabase $ctx
I wanted to show you different approaches regarding your requirements. I think for SaaS architecture you’ll need to use more than Windows Azure Management Portal and you have a lot of options.