SQL Azure Database is a cloud based relational database service from Microsoft. SQL Azure provides relational database functionality as a utility service. Cloud-based database solutions such as SQL Azure can provide many benefits, including rapid provisioning, cost-effective scalability, high availability, and reduced management overhead. This document addresses some of the most frequently asked questions by our customers.
This FAQ is also available in 繁体中文, 简体中文,Français, Deutsch, Italiano, 日本語, 한국어, Русский, Español and Português.
Although SQL Server and SQL Azure at-times will address overlapping application workloads, one is an traditional database server with a software licensing model, while the other is a cloud database as a service running in Microsoft’s global datacenters, available in pay-as-you go or commitment rate plan models. Both offerings provide the flexibility to choose the platform and business model best suits your technology needs.
SQL Server is an enterprise-class database server that provides “scale-up” relational data storage and addresses a broad set of on-premises application types and scenarios. SQL Server is not a service offering and is not based on a pay-as-you-go consumption model. SQL Server is licensed per server or per processor and covers an extensive class of server hardware. SQL Server runs on hardware in your datacenter or your hosting providers datacenter.
SQL Azure is a highly available, distributed “scale-out” database service hosted by Microsoft in the cloud. SQL Azure enables easy provisioning and deployment of relational database capabilities as a service. Developers or administrators do not have to install, setup, and patch or manage any software. High Availability, backup and recovery, geo-distribution and disaster recovery are built-in.
In addition to the scale up/scale out differences, there are also key differences in the operations model. Because SQL Azure abstracts hardware and operating system management, you can focus on building solutions as opposed to procuring, managing, patching, and licensing hardware, virtual machines, operating systems, and applications platform software. With a dedicated database server (whether installed on-premises as a traditional server, hosted in a VM, or hosted by a 3rd party service provider) developers and IT Pros are still responsible for installing, setting up, updating and patching OS & database software. Additionally, users of database servers have to devise their own high availability, scale out, and disaster recovery solutions, thus increasing the total cost of administration.
SQL Azure is designed and optimized for applications that need high scale and high availability at a lower cost than traditional scale up architectures. To this end SQL Azure provides a highly available, and scale out solution on commodity hardware and deliver database capabilities as a service.
SQL Azure is optimized for applications that need high scale and high availability at a lower cost than traditional scale up architectures. To this end SQL Azure provides a highly available, and highly scalable solution on commodity hardware. To get maximum benefit from this platform there is a natural drive towards solutions that scale out using smaller partitions versus the traditional single server scale up model.
SQL Azure supports data sets up to terabytes in size. Individual SQL Azure databases can be up to 150GB in size. Data sets larger than 150GB can be partitioned across multiple Azure databases using SQL Azure Federations and other industry standard approaches. For example, a SaaS ISVs can easily provision each customer their own database or a retailer can partition sales data into databases based on regions.
It is also important to note that the 150GB limit per database does not include additional copies of data that are automatically maintained for higher availability, the transaction log or system level tables.
You can provision multiple databases in SQL Azure for one or more applications. The databases can be either Web or Business Edition Databases. A single Web Edition database can support up to 5GB. 1GB and 5GB are the billing increments. A single Business Edition database will support up to 150GB. The business edition uses 10GB billing increments (10GB, 20GB, 30GB, 40GB, 50GB, 60GB and so on).
You are billed based on the peak db size in a day rolled up to the next billing increment.
Example:
Assume we have a web edition database that has a MAXSIZE=5GB. If the database size is 800MB, the daily charge for the database will be at the 1GB rate for web edition. If the next day, the database size grows to 3GBs, the daily charge will be based on the next billing increment for web edition which is 5GB for that day. If the next day, after some data deletion, the size drops back to 900MB, the daily charge will be based on 1GB back again.
The same example applies to a business edition database. Assume we have a business edition database with MAXSIZE=150GB. If the total database size is 8GB, the daily charge for the database will be at the 10GB rate. If the next day, the database size grows to 25GB, the daily charge will be based on the next billing increment for the business edition which is 30GB and so on.
The daily charge is the monthly rate for that billing increment divided by the number of days in the month.
With a dedicated hosted database, developers and IT Pros are still responsible for installing, setting up, updating and patching OS & database software. Additionally, users of hosted database solutions have to devise their own HA, scale out and disaster recovery solutions thus increasing the total cost of administration.
SQL Azure is a highly available, scalable, distributed database service hosted by Microsoft in the cloud. SQL Azure enables easy provisioning and deployment of relational databases as a service. Developers do not have to install, setup, patch, or manage any software. HA, backup and recovery, geo-distribution and disaster recovery are built-in.
Because these are different products – one is a database server, the other is a cloud service – the two products cannot be compared directly based on price. SQL Server is purchased via a software license. In contrast, SQL Azure is a cloud service whose pricing reflects costs associated with server hardware, software, network bandwidth, storage and the management of the hardware running SQL Azure. We have designed each offering to provide you with the flexibility to choose the platform and business model that best suits your needs.
Many applications that utilize SQL Azure can be easily ported to an on-premises or hosted SQL Server. While some applications may need re-architecting to run on SQL Server, SQL Azure supports the sameT-SQL based relational model over TDS as SQL Server, so existing custom and LOB packaged applications can usually be migrated with minimal changes to the solution.
No. We do not currently allow SQL Server license mobility.
No. SQL Azure is a cloud database delivered as a service, and is separate from SQL Server. SQL Azure is a scale-out platform service that provides you with on-demand relational database as a service. Although SQL Azure and SQL Server products have different development schedules, they are on parallel code paths and actively sharing new features and innovations.
Microsoft provides you the flexibility to use on-premises technology, cloud technology or both, as part of its software-plus-services (S+S) strategy. Customers have expressed a strong interest in having the flexibility to picking deployment options as their business needs dictate. Microsoft will continue to invest heavily, to innovate and to ship new versions of Windows Server, Windows Azure, SQL Server, SQL Azure and System Center to ensure that you can have the benefit of cloud computing technologies whether your applications are running in your own datacenters, in a Microsoft Hosting partner’s datacenter or on our public cloud environment.
No. Each database hosted in the SQL Azure data center has three replicas: one primary replica and two secondary replicas. All reads and writes go through the primary replica, and any changes are replicated to the secondary replicas asynchronously. The replicas are the central means of providing high availability for your SQL Azure databases. For more information, see Inside SQL Azure.
SQL Azure Data Sync provides data movement manageability between SQL Azure and SQL Server databases through bi-directional data synchronization. Using SQL Azure Data Sync organizations can leverage the power of SQL Azure and Microsoft Sync Framework to build business data hubs in the cloud allowing information to be easily shared with multiple SQL Azure data centers, mobile users, business partners, remote offices and enterprise data sources all while taking advantage of new services in the cloud. This combination provides a bridge, allowing on-premises and off-premises applications to work together. You can sign up for the Customer Technical Preview (CTP) of SQL Azure Data Sync at SQL Azure Community Technical Previews.
Use SQL Azure Data Sync to:
Extend on-premises data to remote offices, retail stores and mobile workers via the cloud.
Currently the SQL Azure DataSync service is available as a community technology preview (CTP), we will listen to customer feedback during this public preview to help us determine what we need to do to make sure we deliver the best value to our customers; so the release date will depend on the customer the complexity of the changes required to address customer feedback.
There is currently no charge for the Data Sync service; however you will still accrue data transfer charges for data moving in and out of your SQL Azure database. For more details on this pricing please visit: http://www.microsoft.com/windowsazure/pricing/.
Return to Top
The SQL Server 2008 R2 release of SQL Server Management Studio (SSMS) provides full support for SQL Azure. The SQL Azure July 2011 service release requires SQL Server 2008 R2 SP1. For more information, see Announcing: SQL Azure July 2011 Service Release .
If you need to connect to a specific database, click on the Options button above, and enter the database name in the Connect to database box.
Note: USE <Database> is not supported. So if you need to connect to another database after you are logged in, right click anywhere in the Editor, click on Connection and then on Change Connection. If you are using SQL Server 2008 R2 Management Studio, you can click on the database you wish to connect to, and then click on the New Query button.
When using prior version of SSMS, the login shall be in the format: [Username]@[SQLAzureServerName].
You can check the Service Dashboard for the region of your SQL Azure database at http://www.microsoft.com/windowsazure/support/status/servicedashboard.aspx. You can also subscribe to the respective RSS feeds.
See the SQL Azure Connectivity Troubleshooting Guide for information on troubleshooting common connectivity problems.
When you provision your server, the SQL Azure portal provides connection strings that can be used in your application. Below are the generic format ADO.NET and ODBC connection strings:
ADO.NET
Server=tcp:<sqlazureservername>.database.windows.net;Database=<databasename>; User ID=user@<sqlazureservername>;Password=password;Trusted_Connection=False;
ODBC
Driver={SQL Server Native Client 10.0};Server=tcp:<sqlazureservername>.database.windows.net;Database=<databasename>; Uid=user@<sqlazureservername>;Pwd=password;Encrypt=yes;
In general it is a best practice to make a back-up to recover from logical data loss or corruption (e.g. accidental deletion of data by user apps). SQL Azure runs back-ups periodically and runs consistency checks to recover from a hardware failure, however this is an internal operation that supports the overall health of the service and is not exposed to SQL Azure users. You can, however, use the Bulk Copy utility (BCP) or SQL Server Integration Services (SSIS) to copy your data to an on-premise SQL Server database at any point in time. You can also use the Database Copy feature to create a copy of your database within the same sub-region which can used as a back-up in an event of user error.
The Windows Azure Training Kit provides samples, demos and presentations on SQL Azure including migrating databases.
The SQL Azure firewall prevents access to a SQL Azure server unless you have specifically allowed an IP address. The IP addresses of the machines that need access must be added to the firewall settings before they can attempt to connect to SQL Azure server. This can be done via the Firewall Settings tab under the Server Administration page on the SQL Azure portal. There is also a checkbox “Allow Microsoft Services access to this server”. Checking this will allow connections coming from your applications hosted in Windows Azure.
For more information on configuring the firewall, see How to Configure SQL Azure Firewall.
Check “Allow Microsoft Services access to this server” option under the Firewall Settings tab in the Server Administration page on the portal.
SQL Azure forces SSL encryption with all client connections at all times; however if your client application does not validate certificates upon connection, your connection may be suceptable to main in the middle attacks.
For more information on connection encryption, see Security Guidelines and Limitations.
No, Linked Server is not currently supported in SQL Azure. Hence you will not be able to enable linked server either between on-premise solution and SQL Azure or inside the cloud as well.
Read Committed Snapshot Isolation.
Change Tracking is not currently available in SQL Azure.
Support for Spatial data types was added in SU3 (June 2010).
Yes, the Bulk Copy utility (BCP) and SqlBulkCopyAPI are supported in SQL Azure. The TSQL command BULK INSERT is not supported.
This is a limitation in SQL Azure. The login Data Definition Language (DDL) has to be the only statement in a batch and they cannot be looped inside an IF.. ELSE statement. Please refer Guidelines and Limitations.
When executing the CREATE/ALTER/DROP LOGIN and CREATE/DROP DATABASE statements in an ADO.NET application, using parameterized commands is not allowed. Each of these statements must be the only statement in a SQL batch.
CREATE
ALTER
DROP LOGIN
DROP DATABASE
SQL Agent is not currently supported in SQL Azure.
SQL Profiler is currently not supported in SQL Azure. However, certain troubleshooting Dynamic Management Views (DMV) are enabled to help diagnose performance issues. A detailed list of supportability for DMVs is addressed at http://msdn.microsoft.com/en-us/library/ee336238.aspx#dmv
Service Broker is not currently supported in SQL Azure.
Replication is not currently supported in SQL Azure. There are few options available as stated below:
SQL Azure Migration Wizard also provides the ability to move data between on-premise SQL Server and SQL Azure or between two SQL Azure instances.
Yes. Entity Framework is supported with SQL Azure. See How to: Connecto to SQL Azure Using the ADO.NET Entity Framework for more information.
SQL Azure Reporting was announced at PDC 2010, and will be released as a Community Technology Preview (CTP). For more information, see Introduction to SQL Azure Reporting. You can participate in the CTP by signing up at SQL Azure Community Technical Previews.
SQL 2008 R2 (Reporting Services, Analysis Services and SSIS) is also supported and optimized for SQL Azure and can access SQL Azure as a data source from your on premise Business Intelligence projects.
There is currently no support for automatic partitioning of SQL Azure databases. Users must take care of partitioning their data in their application logic. See Sharding with SQL Azure for more information. SQL Azure Federation has been announced, but is not yet available. This feature will provide easy repartitioning and redistribution of data with a robust connection routing mechanism.
No, SQL Server error log is not currently available in SQL Azure.
No, IPV6 is not available currently. We will include support for IPV6 if we see an increase in need.
The Office 2010 version of Excel can use SQL Azure as a data source. See Connect Microsoft Excel to SQL Azure Database for more information.
Yes, however a specific update to the SQL Server JDBC Driver 3.0 is required. See http://support.microsoft.com/kb/2504052 for more information.
Session state providers are not supported with SQL Azure. For more information, please review Updated ASP.NET scripts for use with Microsoft SQL Azure.
Joining tables across two databases is not supported. A SQL Azure server is a logical grouping of databases. Databases in the same server are spread across the nodes of the environment. You may write a fan-out query in the Application logic to accomplish the same functionality.
An auditing feature is not included in the current version of SQL Azure.
The alternative is to use uniqueidentifier as the column type and then use NEWID() to generate guids at insert time.
CREATE TABLE MyTest ( MyID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY, Name VARCHAR(10)) INSERT INTO MyTest (name) VALUES ('string1') INSERT INTO MyTest VALUES (newid(), 'string2')
Regardless of the datacenter location chosen, your SQL Azure server is set to the UTC timezone. The current version of SQL Azure does not support changing the timezone.
There is no upper limit on the number of connections that can be established to a database or server. However we do recommend Connection Pooling to avoid the initial cost of connection establishment (lookup, SSL handshake, login process, etc) and use your connection directly.
The password must be at least 8 characters long, and contain at least one number, one letter, and one symbol. The password cannot contain any three consecutive characters in the username.
This feature has been added in the SQL Azure July 2011 service release.
You can use Database Manager for SQL Azure to connect and manage SQL Azure without opening port 1433. It is designed specifically for web developers and other technology professionals who seek a straightforward solution to quickly develop, deploy, and manage their data-driven applications in the cloud.
There are several ways to migrate data between on-premise SQL Server and SQL Azure.
Also, please review the demo about migration data from on-premise to SQL Azure included in Windows Azure Training kit.
The version of SQL Server Management Studio (SSMS) included with SQL Server 2008 R2 includes support for SQL Azure. The Express edition of SSMS can be downloaded from Microsoft SQL Server 2008 R2 RTM - Management Studio Express. To generate an SQL Azure compatible script using SSMS, perform the following steps:
1. In SSMS, select the database, right-click, select Tasks, and then select Generate Scripts.
2. Under Set Scripting Options, click Advanced and set Script for database engine type to SQL Azure Database
3. Under “Set Scripting Options”, click Advanced and set “Script for database engine type” to SQL Azure Database
The SQL Server Migration Assistant tool kit 2008 for MySQL v1.0 will help with migrating your databases from MySQL to SQL Azure. This tool dramatically cuts the effort, cost, and risk of migrating from MySQL to SQL Server 2008 and SQL Azure. SSMA 2008 for MySQL v1.0 CTP1 provides an assessment of migration efforts as well as automates schema and data migration.
SQL Azure does not run in backward compatible mode. SQL Azure is built on SQL Server 2008 code base. Thus all deprecated SQL Server 2000 and SQL Server 2005 features will not work in SQL Azure. You can use the SQL Server 2008 Upgrade Advisor to test your database compatibility before migrating to SQL Azure. You may useSQL Azure Migration Wizard to check compatibility between SQL Server 2008 and SQL Azure. This tool can also analyze SQL Profiler traces to check the compatibility of the T-SQL statements generated by the application.
In general it is a best practice for customers to take back-ups to recover from logical data loss or corruption (e.g. accidental deletion of data by user apps). The service also runs back-ups periodically and runs consistency checks to recover from a hardware failure but this is available only internally to the service. Customers can, however, use the Bulk Copy utility (BCP) or SQL Server Integration Services (SSIS) to copy their data to an on-premise SQL Server database at any point in time. For the latest information on SQL Azure backup and restore strategy and disaster recovery techniques, see Business Continuity in SQL Azure article in the SQL Azure MSDN library.
There is soft limit of 150 databases per server. Customers can get that limit removed by calling the Azure Help Desk and going through a credit check process.
There are two editions being offered – Web Edition and Business Edition. The current maximum allowable database size for Web edition and Business editions are 5GB and 150GB respectively. We will be increasing this limit in the future. Please stay tuned for updates through the SQL Azure Team Blog
Customers will see an error code of 40544 when the max size of the edition that was provisioned has been reached. Following is the expected behavior:
More information is available on Guidelines and Limitations.
The database size is calculated by counting the number of pages in the database that contain their tables, index and objects. This is similar to calculating the size of a database in standard SQL Server – not the disk space consumed by the instance but just the space consumed by each database. Some things that do not affect the size are:
There is no programmatic way to find this information in our current offering. We are working to add this feature in the near future. The SQL Azure portal will show the actual usage numbers.
You may also use the following Data Management View (DMV) to get an approximate size of the database used:
SELECT SUM(reservered_page_count)*8.0/1024 as ‘SizeInMB’ FROM sys.dm_db_partition_stats
No, the size of the database includes data, indexes and objects alone. For more information please refer Accounts and Billing in SQL Azure.
The billing model for SQL Azure is very simple. It is the based on the total number of databases and their editions and bandwidth charges for the duration. When you create a database (of either type) we will start the meter. When you drop the database, we will stop the meter. If the database exists for the entire month, you will be charged $9.99 for a Web edition database and $99.99 for an Enterprise edition. If you have databases that existed for only a few days, we charge by the “database day”. For a Web edition database, that’s about $0.30.
The bandwidth usage details are available via the DMV sys.bandwidth_usage and the database usage details are available from the DMV sys.database_usage. More information about the pricing model can be found at SQL Azure Pricing.
Some metrics are stored in the master database. New views such as sys.database_usage and sys.bandwidth_usage have been added that show the number, type and size of the databases and the bandwidth usage for each database.
No, there is no charge for the master database.
When you call to UN-subscribe from Azure, you will essentially be cancelling subscription to all the Azure services. For SQL Azure in particular, the subscription will change from Active to Disabled state. Your SQL Azure databases will not be deleted immediately; there is a 90 day retention period where you can choose to re-enable the subscription on Azure or can simply export your SQL Azure databases. After the 90 day grace period, the subscription enters De-provisioned state and the SQL Azure databases are deleted permanently.
SQL Azure deletes the SQL Azure databases 90 days after the you UN-subscribe.
Cancellation will affect all 3 available Azure services: Windows Azure, SQL Azure, and AppFabric. However, since the services are “pay for what you use”, if you want to keep the subscription but not be billed for SQL Azure then you can just delete your databases and servers and you will not be billed for SQL Azure.
You can use the the CREATE DATABASE .... AS COPY OF ... T-SQL statement, the Generate Scripts Wizard in the SQL Sever 2008R2 version of SQL Server Management Studio (SSMS,) SQL Server Integration Services (SSIS,) or the Bulk Copy utility (BCP) to migrate databases from one subscription to another. Since a SQL Azure server is a logical server and the server name is auto-generated, your client connection strings need to be updated. If your subscriptions are located in the same sub-region, you may contact help desk to move your server and databases from one subscription to another. The current version of SQL Azure only supports single server per subscription.
You can use the Generate Scripts Wizard in the SQL Sever 2008 R2 version of SQL Server Management Studio (SSMS,) SQL Server integration Services (SSIS,) or the Bulk Copy utility (BCP) to migrate databases from one subscription to another. You need to manually configure the SQL Azure Firewall on the new server. Since the SQL Azure server is a logical server and the server name is auto-generated, your client connection strings need to be updated. We are working on a tool which will be available in the near future to aid moving subscriptions from one account to another.
A SQL Azure Server is just a logical grouping of your databases. SQL Azure provides one server per subscription. However you may create many subscriptions with one common Azure platform account. This will allow you to create many servers as needed.
Yes, service updates are rolled out for all the customers, so everyone will be on the latest version.
Patches and service updates are rolled out to all the machines in the SQL Azure environment. We do testing and assessments to make sure that our updates are backwards compatible.
In general, throttling conditions can be categorized into the following:
For more information about throtting see SQL Azure Engine Throttling. For more information on how to handle the connection-loss errors, see SQL Azure Connection Management. This article lists the reasons for connection-losses and provides guidelines and best coding practices on how to manage the connection life cycle in SQL Azure. In addition, read Troubleshooting SQL Azure topic in the MSDN library.
SQL Azure provides the platform for customers to scale out to unlimited potential. Customers can partition the data in the application level. We will be increasing the allowable database size in the future. Support for partitioning in the platform will be added in the future.
Case studies including SQL Azure are available at Windows Azure Case Studies.
The authors of the original information contained in this article are Abi Iyer and Dinakar Nethi.
Good layout Q and A.
Thanks for sharing this wonderful information.
Thanks for sharing this Details very usefull
Congrats on getting this article featured on the Wiki Ninjas blog: blogs.technet.com/.../tnwiki-article-spotlight-sql-azure-faq.aspx