In my first sort-of-successful attempt to connect a local D7/IIS instance with SQL Azure, I got this:

Failed to INSERT a value into a test table on your database server. We tried inserting a value with the command INSERT INTO {drupal_install_test} (id) VALUES (1) and the server reported the following error: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again..

Failed to UPDATE a value in a test table on your database server. We tried updating a value with the command UPDATE {drupal_install_test} SET id = 2 and the server reported the following error: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again..

Failed to DELETE a value from a test table on your database server. We tried deleting a value with the command DELETE FROM {drupal_install_test} and the server reported the following error: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again..

I'll tag SQL Azure issues with the "azure" tag.

Just as a note, The sort-of-successful configuration came after

  • Creating a login and a user on the remote SQL Azure database using SSMS and command-line operations
    • SQLCMD -U masteruser@tr8jbxqqaw.database.windows.net -P passwd -S tr8jbxqqaw.database.windows.net -d rfayjunk5
    • create login junk with password='passwd'
    • create user junk from login junk
    • EXEC sp_addrolemember 'db_owner', 'junk'
  • Then use junk@tr8jbxqqaw.database.windows.net as the Drupal username
  • and tr8jbxqqaw.database.windows.net as the database host.

Comments

rfay’s picture

Basic information about the creation is at http://msdn.microsoft.com/en-us/library/ee336245.aspx.

CREATE TABLE Table1 (Col1 int, Col2 char(20))
CREATE CLUSTERED INDEX Table1_Index ON Table1 (Col1)
rfay’s picture

Status:Active» Needs review
StatusFileSize
new594 bytes
new1.25 KB

Here is a pair of patches that at least get Drupal installed.

The driver patch changes the indexes to be clustered.

And the core patch changes to a workable database test. It's completely custom to sql server, of course, but it's an attempt to figure out how to make this work.

The performance is so bad with my local machine communicating for its database up through my uplink to the cloud that the site barely runs, but it does get installed.

Damien Tournoud’s picture

Issue tags:+azure support
Damien Tournoud’s picture

Title:SQL Azure install fails with complaint about clustered index» Create all indexes as CLUSTERED for SQL Azure
Category:bug» task
david_garcia’s picture

Version:7.x-1.x-dev» 8.x-1.x-dev
Status:Needs review» Needs work
david_garcia’s picture

@Dave Gordon:

The installer fails with the following problem when used for AzureSQL

Resolve all issues below to continue the installation. For help configuring your database server, see the installation handbook, or contact your hosting provider.Failed to DELETE a value from a test table on your database server. We tried deleting a value with the command DELETE FROM {drupal_install_test} and the server reported the following error: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.: DELETE FROM {drupal_install_test}; Array ( ) .

david_garcia’s picture

@Dave Gordon:

I won't be able to provide much support on Azure.

Please try these changes:

In Schema::addPrimaryKey:

<?php
    $this
->connection->query('ALTER TABLE [{' . $table . '}] ADD CONSTRAINT {' . $table . '_pkey} PRIMARY KEY (' . $this->createKeySql($fields) . ')');
   
$this->connection->query($this->createIndexSql($table, $table . '_pkey', $fields));
?>

In Schema::createTechnicalPrimaryColumn:

<?php
$this
->connection->query('ALTER TABLE {' . $table . '} ADD __pk UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL');
$this->connection->query($this->createIndexSql($table, '__pk', array('__pk')));
?>

This would be for D7 syntax, but you can see that what we are doing here is creating indexes (be sure to have the other patche sin this issue applied) when primary keys are created, including the custom "technical primary key".