Ah-ha! No sooner did I send that message than I stumbled upon the real reason:
Why Do I Need a Clustered Index?<http://blogs.msdn.com/b/sqlazure/archive/2010/05/12/10011257.aspx>( http://blogs.msdn.com/b/sqlazure/archive/2010/05/12/10011257.aspx) "SQL Azure requires clustered indexes for our replication technology. At any one time, we are keeping three replicas of data running - one primary replica and two secondary replicas. We use a quorum based commit scheme where data is written to the primary and one secondary replica before we consider the transaction committed." -- Sean On Thu, Apr 3, 2014 at 11:08 AM, Sean Killeen <seankill...@gmail.com> wrote: > Hi Martin, > > Thanks for the response. > > The best documentation I've been able to find so far: > http://msdn.microsoft.com/library/azure/ee336275.aspx. "If a table is > created without a clustered constraint, a clustered index must be created > before an insert operation is allowed on the table." > > It appears that SQL azure will create the tables, but will assume that > you'll perform additional design on them before inserting data, which is > why it doesn't throw on create. > It only throws the error when you attempt to store data in a table without > a clustered index of any kind. > > From a conceptual perspective I agree with you -- I would have never > thought to put a primary key on tables like those that I listed either; > they do not seem to require one. > > My guess (which is all I have at this point as I research further) is that > Azure needs a clustered index because it is looking for us to tell it how > to best store the data, for efficiency reasons. > > > -- > Sean > > > On Thu, Apr 3, 2014 at 10:13 AM, Martin Gruner <martin.gru...@otrs.com>wrote: > >> Hello Sean, >> >> thanks a lot for your mail and your willingness to help out here. For >> now I'd like to ask how it could be that SQL Azure does create the >> tables but then fails on running INSERT statements. That doesn't seem >> right to me. Can you provide a link to some documentation on this? >> >> These are all tables where we don't need a primary key, that's why they >> don't have one. >> >> Regards, mg >> >> Am 29.03.14 02:50, schrieb Sean Killeen: >> > *Summary: *I'd like to alter some DB scripts to add clustered indexes so >> > they work better for MSSQL (and work at all for SQL Azure). How should I >> > go about this? >> > >> > Hi all, >> > >> > New to contributing to OTRS and to the dev mailing list so please >> > forgive me if I stumble around a bit. >> > >> > *Background / Issue* >> > >> > I wanted to set up OTRS on Azure using a SQL Azure database back-end, so >> > I thought I'd play around a bit. >> > >> > SQL Azure apparently chokes when confronted with tables that don't have >> > any clustered indexes. Womp womp. >> > >> > As of v3.3.5, the setup script for OTRS apparently creates 33 tables >> > without clustered indexes or primary keys (the list in full is at the >> > end of this post). This leads to inoperability and a ton of errors in >> > the log when attempting to use OTRS with SQL Azure. >> > >> > *What I'd like to do about it * >> > * >> > * >> > I'd like to help out with this and submit a pull request for the MSSQL >> > setup to deal with this (as well as make things a little more snappy for >> > MSSQL users). Was hoping to start this out as a Github issue, but I >> > don't believe Issues are enabled for the otrs/otrs project. >> > >> > However, I'm as of yet completely unfamiliar with the data structure, so >> > I would be guessing at best here and I don't think that's wise. :) >> > >> > Is there a solid reference point for the database architecture available >> > that I could reference to deduce where primary keys or clustered indexes >> > could be used in the affected tables? Or is there some other approach >> > the community would prefer I take to addressing this? >> > >> > OTRS is a great product, and I'm looking forward to being able to >> > hopefully give something back. >> > >> > Thanks, >> > -- >> > Sean >> > >> > *Appendix: The list of tables without clustered indexes* >> > >> > Found by running: >> > >> > SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID) >> > FROM SYS.INDEXES >> > WHERE INDEX_ID = 0 >> > AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1 >> > ORDER BY [TABLE] >> > >> > acl_sync >> > article_flag >> > customer_preferences >> > generic_agent_jobs >> > gi_object_lock_state >> > group_customer_user >> > group_role >> > group_user >> > link_relation >> > notification_event_item >> > personal_queues >> > pm_entity >> > pm_entity_sync >> > postmaster_filter >> > process_id >> > queue_preferences >> > queue_standard_template >> > role_user >> > search_profile >> > service_customer_user >> > service_preferences >> > service_sla >> > sla_preferences >> > support_bench_test >> > ticket_flag >> > ticket_index >> > ticket_lock_index >> > ticket_loop_protection >> > ticket_watcher >> > user_preferences >> > virtual_fs_preferences >> > web_upload_cache >> > xml_storage >> > >> > >> > >> > >> > _______________________________________________ >> > OTRS mailing list: dev - Webpage: http://otrs.org/ >> > Archive: http://lists.otrs.org/pipermail/dev >> > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev >> > >> >> -- >> Martin Gruner >> Senior Developer R&D >> >> OTRS AG >> Bahnhofplatz 1a >> 94315 Straubing >> >> T: +49 (0)6172 681988 0 >> F: +49 (0)9421 56818 18 >> I: www.otrs.com/ >> >> Geschäftssitz: Bad Homburg, Amtsgericht: Bad Homburg, HRB 10751, >> USt-Nr.: DE256610065 >> Aufsichtsratsvorsitzender: Burchard Steinbild, Vorstand: André >> Mindermann (Vorsitzender), Christopher Kuhn, Sabine Riedel >> >> Einfache Planung, bessere Übersicht - Mit OTRS 3.3 einfach besseres >> Service Management - Jetzt downloaden und testen >> _______________________________________________ >> OTRS mailing list: dev - Webpage: http://otrs.org/ >> Archive: http://lists.otrs.org/pipermail/dev >> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev >> > >
_______________________________________________ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
