SQL Azure FAQ

Question

Hi Folks,

Abi put together a great list of frequently asked questions as shown below. We will be updating this periodically.

Thanks for using the service,
Dave

SQL Azure Frequently Asked Questions

Prepared by: Abi Iyer, Sr. Support Escalation Engineer (SQL Azure BETA)

 

Q: How can I use SSMS to connect to SQL Azure?

A: Zach has documented this in detail in www.zachowens.net . Below are the steps from his blog:

The current version of SSMS 2008 is able to connect to SQL Azure but the Object Browser doesn’t work.  There is also a trick to getting connected.

When you fire up SSMS and are presented with connection dialog box, if you enter your credentials you will get the following error.

TITLE: Connect to Server
——————————
Cannot connect to MY_SERVER_NAME.ctp.database.windows.net.
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Invalid object name ’sys.configurations’. (Microsoft SQL Server, Error: 208)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.9006&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476

The key to getting around this is to click on the Cancel button on the initial dialogue box.

Then click on the New Query button.

You will be presented with a new connection dialogue that looks the same as the initial one. 

Enter your credentials, if you are trying to connect to a specific database click on the Options and enter a database name because USE <database> is not supported.

Once you connect you will get an error

TITLE: Microsoft SQL Server Management Studio
——————————
Unable to apply connection settings. The detailed error message is: ‘ANSI_NULLS’ is not a recognized SET option.

You can just click OK.

Now you are good to go!  You can issue queries, visualize explain plans, etc.

 

Q: Where can I find sample connection strings for SQL Azure?

A: As soon as you provision your server, SQL Azure portal provides connection strings that can be used in your application. Below is a generic format:

Server=tcp:server.ctp.database.windows.net;Database=master;User ID=user@server;Password=password;Trusted_Connection=False;

 

Q: I am unable to connect to SQL Azure. I get one of the following errors:

TCP Provider: An existing connection was forcibly closed by the remote host.

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Communication link failure.

Named Pipes Provider: Could not open a connection to SQL Server [53].

Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..

Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)"

 

A:  The most likely cause is your firewall rules. Please ensure that the firewall has port 1433 open to allow outbound traffic. You might also need to add 65.55.*.* to your firewall configuration rules to allow access to both ctp.database.windows.net and sql.azure.com

 

Q: Are there any hands-on labs or demos available for moving data between on-premise SQL Server and SQL Azure?

A: Windows Azure Training Kit provides samples, demos and presentations on SQL Azure including migrating databases. This training kit can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=413E88F8-5966-4A83-B309-53B7B77EDF78&displaylang=en

 

Q: Is MARS supported with SQL Azure?

A: No, MARS is not supported in V1 of SQL Azure.

 

Q: Is Linked Server supported in SQL Azure? Can I enable Linked Server between on-premise SQL Server and SQL Azure servers or between two instances of SQL Azure servers?

A: No, Linked Server is not supported in v1 of 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.

 

Q: What is the default isolation level supported in SQL Azure?

A: Snapshot Isolation

 

Q: Is Change tracking supported in SQL Azure?

A: Change Tracking is not available in V1 of SQL Azure.

 

Q: Is spatial data types supported in SQL Azure?

Spatial data types are not supported in SQL Azure since they have a dependency in .Net CLR which is not supported in SQL Azure.

 

Q: Is Bulk Copy supported in SQL Azure?

A: Bulk Copy is supported.

 

Q: What are the supported methods of moving data between on-premise SQL and SQL Azure?

A: SSIS can be used with ADO.Net connection managers with parallelism enabled for improved performance. You may also script your data depending on the size and needs. An unsupported scripting tool developed by the community is also available from codeplex at http://sqlazuremw.codeplex.com/

Also, please review the demo about migration data from on-premise to SQL Azure included in Windows Azure Training kit at http://www.microsoft.com/downloads/details.aspx?FamilyID=413E88F8-5966-4A83-B309-53B7B77EDF78

 

Q: SSIS performs very poorly when migrating large amount of data to SQL Azure. Are there any workarounds?

A: ADO.Net connection manager inserts data one row at a time and this factor induces a lot of latency. Once we enable bulk operations in our next service update, users can see huge performance gain. Please consider parallelism in the mean time to improve performance in your SSIS packages.

 

Q:  Is Sync Services supported in SQL Azure?

A: It is not supported in v1 of SQL Azure. We are working to add support for it in our future release.

 

Q: Is Reporting Services or Analysis Services supported in the cloud solution?

A: BI (Business Intelligence) is not part of our cloud offerings in v1 of SQL Azure. However they are our incubation projects and we are considering adding them as a service in the Azure platform. In the short term, we are working to enable SQL Azure as a datasource for your BI solutions which would include Analysis Services and Reporting Services. You can, however, access SQL Azure from within your locally running Reporting Services and Analysis Services projects.

 

Q: If I include DROP Database inside an IF..Else loop, I get an error saying that DROP Database is not supported even though I am connected to Master database?

A: This is a limitation in SQL Azure. The login DDL’s have to the only statement in a batch and they cannot be looped inside IF..ELSE statement. Please refer Guidelines and Limitations topic in

http://msdn.microsoft.com/en-us/library/ee336245.aspx

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.

 

Q:  Is SQL Agent supported in SQL Azure?

A: SQL Agent is not supported in v1 of SQL Azure.

 

Q: Is SQL Profiler supported in SQL Azure?

A: We do not support SQL Profiler in v1 of SQL Azure.

 

Q: Is Replication supported in SQL Azure? Can I replicate my data from on-premise SQL to SQL Azure?

A: Replication is not supported in v1 of SQL Azure. Please use SSIS to move your data between on-premise SQL and SQL Azure. Support for more tooling including SSMS, BCP will be available in the next service updateQ: Is Entity Framework supported in SQL Azure?

A: Although Entity Framework should work well with SQL Azure, we are still doing some tests to provide our supportability statement. Please stay tuned for updates. Please be aware that MARS is not supported in SQL Azure when you write Entity Framework solutions.

 

Q: What is the limit with the number of databases that can be created in SQL Azure?

A: Users can create 6 database including master in our CTP offerings. However users can create any number of databases with our PDC release.

 

Q: Is there a limit on the max database size that is supported in CTP offerings?

A:  The max database size is 10GB/database.

 

Q: Does SQL Azure support automatic partitioning mechanism so that I can easily partition my data across multiple databases?

A: This feature is not supported in v1 of SQL Azure. Users must take care of partitioning their data in their application logic. We will be providing application patterns and guidance on how to do this.

 

Q: What will be the behavior of the system when the database size is reached?

·          All insert/update/create/alter add operations will be throttled and any attempts to add more data to the database will result in the termination of the session

·         Reads will continue to work

·         Delete/drop and other operations will work so you can reduce your database size

 

Q: How can I find the actual size of my database so that I know how much space is left and plan accordingly?

A: We are adding support for this feature in the next service update and SQL Azure portal will have this information handy for our users.

 

Q: Is there a programmatic way of finding the actual size of my database?

A: There is no programmatic way to find this information in our current offering. We are working to add this feature in the near future. Please stay tuned for updates on the same.

Q: How is the size of my database calculated?
A: 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:

    1. Logs are excluded from the size calculation
    2. Master DB is excluded from size calculation
    3. All other system databases and server catalogs are excluded from the calculations
    4. All customer database replicas are excluded from the calculations (we only count one of the three replicas of the customer database)

Q: Can you please explain about your pro-rated billing model?

A: The overwhelming feedback we’ve gotten from customers is to keep this simple and predictable.  In fact, we’ve debated and abandoned many models simply on the basis that they didn’t meet this litmus test.  We’ll almost certainly offer other options in the future but both we and customers need some experience with usage patterns, workloads, etc. to know what is best in a given situation.  So, for now, simple, flexible and as economical as possible is what we’re driving toward.

So, we will meter on the existence of a database (in addition to the ingress/egress from the data center which is standard across all Windows Azure services).  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. 

The only thing we have not yet announced is the unit of time that we will pro-rate over.  We’re all but locked down although we’re still getting some sign-off by the business owners.  Here we are as likely to be governed by what I think of as one of the laws of small numbers.  For example, say we decided to charge by the “database day”.  For a Web edition database, that’s about $0.30 and it’s hard to imagine a need to slice this up more granularly than that.

Also, even for pretty extreme burst conditions, there isn’t much customer value for databases to come and go on a minute-by-minute basis.   They tend to be more enduring.

 

Q: Since RowGUID column is not supported in SQL Azure, what is the recommended alternative to customers?

A: 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 clustered,

name varchar(10))

 

insert into mytest (name) values ('string1')

insert into mytest values (newid(), 'string2')

 

 

Q: What’s the story regarding the backup of data stored in SQL Azure? How can customers recover from corruption or data loss?

A: 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). Customers can use SSIS to take back-up of their database. We are working to add BCP support in our next service update. We also run back-ups periodically and run consistency checks to recover from a hardware failure but this is used internally by the service only.

 

Q: Is SQL Server error log available in SQL Azure?

A: No, SQL Server error log is not available in v1 of SQL Azure.

 

Q: What are the different conditions under which my workload can get throttled?

A: In general, throttling conditions can be categorized in to the following:

1.      Customer reaches the size quota for the database – all update/insert/create/add activity will be throttled until the database size is reduced.

2.      There is something wrong on the service side and the throttling errors will indicate that.  This should be an extremely uncommon condition

3.      Short term transient load condition -- the error would indicate that the customer needs to retry.

 

Q: Where can I find some metrics for my SQL Azure service?

A: Some metrics are stored in the master database in our current CTP offering and we are working to add more metrics in our next service update.

Answer 1

Man can we have backup from your database is a dead or live situation :(