Create all indexes as CLUSTERED for SQL Azure [#1044028]

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.

Comment	File	Size	Author
#2	core_test_patch_1044028_02.patch	1.25 KB	rfay
#2
#2	sqlsrv_clustered_index_1044028_02.patch	594 bytes	rfay
#2

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

rfay

English

Palisade, CO, USA

CreditAttribution: rfay commented 28 January 2011 at 21:40

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)

Comment #2

rfay

English

Palisade, CO, USA

CreditAttribution: rfay commented 29 January 2011 at 03:07

Status:

Active

» Needs review

File	Size
sqlsrv_clustered_index_1044028_02.patch	594 bytes

core_test_patch_1044028_02.patch	1.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.

Comment #3

Damien Tournoud CreditAttribution: Damien Tournoud commented 31 January 2011 at 19:07

Issue tags:

+azure support

Comment #4

Damien Tournoud CreditAttribution: Damien Tournoud commented 31 January 2011 at 19:07

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

Comment #5

david_garcia CreditAttribution: david_garcia commented 26 November 2014 at 14:48

Version:	7.x-1.x-dev	» 8.x-1.x-dev
Status:	Needs review	» Needs work

Boosted this to D8.

Comment #6

david_garcia CreditAttribution: david_garcia commented 26 November 2014 at 14:52

@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 ( ) .

Comment #7

david_garcia CreditAttribution: david_garcia commented 26 November 2014 at 15:01

@Dave Gordon:

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

Please try these changes:

In Schema::addPrimaryKey:

    $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:

$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".