Windows Azure SQL Database (formerly SQL Azure) is a cloud based relational database service from Microsoft. SQL Database that provides relational database functionality as a utility service. Cloud-based database solutions such as SQL Database 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 Database 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 Database is a highly available, distributed “scale-out” database service hosted by Microsoft in the cloud. SQL Database 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 Database 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 Database 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 Database provides a highly available, and scale out solution on commodity hardware and deliver database capabilities as a service.
SQL Database is optimized for applications that need high scale and high availability at a lower cost than traditional scale up architectures. To this end SQL Database 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 Database supports data sets up to terabytes in size. Individual SQL Database databases can be up to 150GB in size. Data sets larger than 150GB can be partitioned across multiple Azure databases using SQL Database 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 Database 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 Database is a highly available, scalable, distributed database service hosted by Microsoft in the cloud. SQL Database 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 Database is a cloud service whose pricing reflects costs associated with server hardware, software, network bandwidth, storage and the management of the hardware running SQL Database. 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 Database 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 Database 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 Database is a cloud database delivered as a service, and is separate from SQL Server. SQL Database is a scale-out platform service that provides you with on-demand relational database as a service. Although SQL Database 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 Database 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 Database 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 Databases. For more information, see Inside Windows Azure SQL Database.
SQL Data Sync provides data movement manageability between SQL Database and SQL Server databases through bi-directional data synchronization. Using SQL Data Sync organizations can leverage the power of SQL Database and Microsoft Sync Framework to build business data hubs in the cloud allowing information to be easily shared with multiple SQL Database 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 Data Sync at SQL Database Community Technical Previews.
Use SQL Data Sync to:
Extend on-premises data to remote offices, retail stores and mobile workers via the cloud.
Currently the SQL 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 Databases. 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 Database. The SQL Database July 2011 service release requires SQL Server 2008 R2 SP1. For more information, see Announcing: SQL Database 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 Database at http://www.microsoft.com/windowsazure/support/status/servicedashboard.aspx. You can also subscribe to the respective RSS feeds.
See the SQL Database Connectivity Troubleshooting Guide for information on troubleshooting common connectivity problems.
When you provision your server, the SQL Database 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 Database 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 Database 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 Database including migrating databases.
The SQL Database firewall prevents access to a SQL Database 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 Database server. This can be done via the Firewall Settings tab under the Server Administration page on the SQL Database 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. You can specify firewall settings at the server and database level.
For more information about Windows Azure SQL Database Firewall, and how to configure it, see Windows Azure SQL Database Firewall.
Check “Allow Microsoft Services access to this server” option under the Firewall Settings tab in the Server Administration page on the portal.
For more information on configuring the server-level firewall settings, see How to Configure the Server-Level Firewall Settings.
SQL Database 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 susceptible to man in the middle attacks.
For more information on connection encryption, see Security Guidelines and Limitations.
No, Linked Server is not currently supported in SQL Database. Hence you will not be able to enable linked server either between on-premise solution and SQL Database or inside the cloud as well.
Read Committed Snapshot Isolation.
Change Tracking is not currently available in SQL Database.
Support for Spatial data types was added in SU3 (June 2010).
Yes, the Bulk Copy utility (BCP) and SqlBulkCopyAPI are supported in SQL Database. The TSQL command BULK INSERT is not supported.
This is a limitation in SQL Database. 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 Database.
SQL Profiler is currently not supported in SQL Database. 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 Database.
Replication is not currently supported in SQL Database. There are few options available as stated below:
SQL Database Migration Wizard also provides the ability to move data between on-premise SQL Server and SQL Database or between two SQL Database instances.
Yes. Entity Framework is supported with SQL Database. See How to: Connecto to SQL Database Using the ADO.NET Entity Framework for more information.
Windows Azure SQL Reporting was announced at PDC 2010, and will be released as a Community Technology Preview (CTP). For more information, see Introduction to SQL Reporting. You can participate in the CTP by signing up at SQL Database Community Technical Previews.
SQL 2008 R2 (Reporting Services, Analysis Services and SSIS) is also supported and optimized for SQL Database and can access SQL Database as a data source from your on premise Business Intelligence projects.
There is currently no support for automatic partitioning of SQL Databases. Users must take care of partitioning their data in their application logic. See Sharding with SQL Database for more information. SQL Database 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 Database.
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 Database as a data source. See Connect Microsoft Excel to Windows Azure SQL 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.
The only supported session state providers with Windows Azure SQL Database are the ASP.NET Universal Providers.
Joining tables across two databases is not supported. A SQL Database 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 Database.
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 Database server is set to the UTC timezone. The current version of SQL Database 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 Database July 2011 service release.
You can use Database Manager for SQL Database to connect and manage SQL Database 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. Q: Can I do context switching in SQL Azure? No, SQL Azure do not support context switching, please refer KB Article 2292807 for more details, this means that you can not query with "USE DBNAME", also, you can only use schemaname.objectname notation according to this article
There are several ways to migrate data between on-premise SQL Server and SQL Database.
Also, please review the demo about migration data from on-premise to SQL Database included in Windows Azure Training kit.
The version of SQL Server Management Studio (SSMS) included with SQL Server 2008 R2 includes support for SQL Database. The Express edition of SSMS can be downloaded from Microsoft SQL Server 2008 R2 RTM - Management Studio Express. To generate an SQL Database 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 Database
3. Under “Set Scripting Options”, click Advanced and set “Script for database engine type” to SQL Database
The SQL Server Migration Assistant tool kit 2008 for MySQL v1.0 will help with migrating your databases from MySQL to SQL Database. This tool dramatically cuts the effort, cost, and risk of migrating from MySQL to SQL Server 2008 and SQL Database. SSMA 2008 for MySQL v1.0 CTP1 provides an assessment of migration efforts as well as automates schema and data migration.
SQL Database does not run in backward compatible mode. SQL Database is built on SQL Server 2008 code base. Thus all deprecated SQL Server 2000 and SQL Server 2005 features will not work in SQL Database. You can use the SQL Server 2008 Upgrade Advisor to test your database compatibility before migrating to SQL Database. You may useSQL Database Migration Wizard to check compatibility between SQL Server 2008 and SQL Database. 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 Database backup and restore strategy and disaster recovery techniques, see Business Continuity in SQL Database article in the SQL Database 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 Database 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 Database 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 Windows Azure SQL Database.
The billing model for SQL Database 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 Windows Azure SQL Database 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 Database in particular, the subscription will change from Active to Disabled state. Your SQL Database 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 Databases. After the 90 day grace period, the subscription enters De-provisioned state and the SQL Databases are deleted permanently.
SQL Database deletes the SQL Databases 90 days after the you UN-subscribe.
Cancellation will affect all 3 available Azure services: Windows Azure, SQL Database, 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 Database then you can just delete your databases and servers and you will not be billed for SQL Database.
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 Database 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 Database 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 Database Firewall on the new server. Since the SQL Database 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 Database Server is just a logical grouping of your databases. SQL Database 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 Database 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 throttling see Windows Azure SQL Database Engine Throttling. For more information on how to handle the connection-loss errors, see SQL Database 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 Database. In addition, read Troubleshooting Windows Azure SQL Database topic in the MSDN library.
SQL Database 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 Database are available at Windows Azure Case Studies.
The authors of the original information contained in this article are Abi Iyer and Dinakar Nethi.
Carsten Siemens edited Revision 82. Comment: Fixed misspellings
Ed Price - MSFT edited Revision 76. Comment: Updated See Also link; added tags
patmas57 edited Revision 69. Comment: Branding update
Windows Azure Product Team edited Re