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

http://weblogs.asp.net/jeffwids/lock-request-time-out-period-exceeded

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
,r.*
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

End.

Advertisements

Forms-Based Authentication with SQL Azure

 

Following is quite useful

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

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

Worked on site. However the role manager did not wok

http://stackoverflow.com/questions/5541868/problem-with-your-selected-data-store-on-the-asp-net-website-administration-tool

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

http://www.windowsazure.com/en-us/develop/net/tutorials/web-site-with-sql-database/

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

enable-migrations

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:

enable-migrations

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

http://msdn.microsoft.com/en-us/library/windowsazure/hh335292

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
DROP DATABASE _20120627
CREATE DATABASE _20120627 AS COPY OF

–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
SELECT * FROM .

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

http://social.technet.microsoft.com/wiki/contents/articles/2153.windows-azure-and-sql-azure-tutorials-tutorial-3-using-windows-azure-blob-service-en-us.aspx#__Lesson1Proc1

 

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/