Martin, Regarding your closing of the bug report as a feature request, I'm not sure it necessarily has to be that way.
It is only a small subset of T-SQL Commands that are not supported in SQL Azure. The list: http://msdn.microsoft.com/library/azure/ee336250.aspx The elements that are not supported strike me as elements that OTRS would likely not be using (though of course, being new, it would take me a little while to confirm that.) Thanks for the continued discussion & consideration, -- Sean On Thu, Apr 3, 2014 at 11:09 AM, Sean Killeen <seankill...@gmail.com> wrote: > 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
