SQL Azure cross database queries – evaluation

Existing documentation to create external database seems fine. Links include:




Error: “The option ‘IDENTITY’ is not supported with external tables for sharded data”
Remove the IDENTITY from the CREATE EXTERNAL TABLE id column


Using a different name in target

If the target is already using the external name you may want to use a different name. See this article. Add following to the with statement.

SCHEMA_NAME = ‘Sales’, OBJECT_NAME = ‘Quote’


One option is to create a view in the source with the desired name. Then in the target reference that. Works.


Slightly slower than using the source database, but pretty good – certainly usable in most cases.

SQL Server hangs on drop foreign key

This actually happened on Azure during part of a SSIS SQL Server integration package, removal of key before delete and upload of data

Following post was very useful


To find sessions then

SELECT r.session_id, r.status, r.start_time, r.command, s.text,
r.wait_time, r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_level
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

then Kill session id, but you cannot kill your own session, so open another session to do the kill


Forms-Based Authentication with SQL Azure


Following is quite useful


I was able to get this to work without a Windows Azure Project

Worked on site. However the role manager did not wok


Changed following and it worked
<roleManager enabled=”true”>



Deleting an ASP.Net azure hosted website using Entity Framework can potentially delete the linked database

I had a database on Azure that was being updated using Windows Forms. I then created an ASP.Net MVC application using EF to work against the same database. However I following a tutorial which used Code First


This would not publish until I used Nu-Get to:


However I think I then used code first migrations on publish. However I published the website with high credentials and wanted to change the credentials to lower. Could not see how to do this in Azure, so deleted the website. At this point the database disappeared without warning.

This is why the last posts have been about SQL Azure backup. However recovered using on-site data and re-created database.

Microsoft support helped with the ASP.Net so:


Check the file created, but do not check the box on publish.

Tested by deleting the Website – This then asked if you wanted to delete the linked database. Answer: No. Database stays ok.

How do I backup SQL Azure to a Blob Storage Account using bacpac


My word press blog entry


How do I … backup by using Create Database … As Copy Of to an Azure database (to Azure)

–Start logged into Azure and with master as current database
–Does work 27/06/2012

–Must wait for state to be changed from Copying to ?Ready?
SELECT * FROM sys.dm_database_copies
SELECT * FROM sys.databases
SELECT name, state, state_desc from sys.databases where name = ‘_20120627’

–Test that data is there

Azure Storage – a pre-requisite to export SQL to blob storage

A storage account needs a container before it can be used.
The container may be made in Visual Studio using a new Project > Cloud > Windows Azure Cloud Service
When this project is created with any Web Roles you create the relevant projects will also be created and linked.
When this is created make this Cloud Service Project the start-up project and run-it in Visual Studio as an administrator you may then get further, it will not start the cloud service project but the web project. This will then give relevant keys.

I found this not to run in local. Needed to publish it first.
Then in Azure Management > Hosted Services > Locate > Run from DNS



To test the application in the staging environment

  1. From the portal, in the left pane, click Hosted Services.
  2. In the middle pane, expand GolferMessageBoard, and then click v1.2.0.0.
  3. In the Properties pane, click the URL in the DNS name box. The application is opened in a new browser tab or a new browser window depending on your browser configuration.
  4. Test the application by entering one or more entries.

After the application is working correctly in the staging environment, you are ready to promote it to the production environment.

To promote the application to production

  1. From the portal, in the left pane, click Hosted Services.
  2. In the middle pane, expand GolferMessageBoard, and then click v1.2.0.0.
  3. From the top menu, click Swap VIP.
  4. In the Swap VIPs dialog, click OK. Wait until the Status for the deployment changes to Ready.
  5. In the Properties pane, click the URL in the DNS name box. The application is opened in a new browser tab or a new browser window depending on your browser configuration.
  6. Test the application in the production environment by entering one or more entries.


Azure Export of SQL database to blob

The format of the file should be
http:// teststorage01.blob. core.windows.net/dbbackup/ebcgroup1.bacpac

I had following error:
“azure blob is not writeable … the specified container does not exist”

To create the container make sure the above has been published to Azure and run from there.


Using SSIS to import data from SQL Server on premises to SQL Azure

This assumes prior knowledge of SSIS, but just hi-lights the difference

  1. SSIS
  2. Data Flow Task
  3. OLE DB Source
    1. As normal
  4. ADO Net Destination
      1. .Net Providers\SqlClient Data Provider
      2. Login

SQL Azure differences

Keyword or statement option ‘pad_index’ is not supported in this version of SQL Server. Also list below

  • pad_index
  • allow_row_locks
  • allow_page_locks

”Filegroup reference and partitioning scheme’ is not supported in this version of SQL Server.

Not yet used it, but PluralSight video pointed that an easier way to script this is to use the SQL Azure Migration Wizard http://sqlazuremw.codeplex.com/