@Mike, thanks for the additional interest! I understand that some of these tables may have no need for primary keys at all. In this case, would it make the most sense to create just a clustered index that included all columns (without adding a PK constraint?)
This would serve SQL server by telling it how to store the columns & replicate, without necessarily introducing any additional constraints on the data. Not sure if it makes sense but wanted to add it as an option. -- Sean On Thu, Apr 3, 2014 at 3:19 PM, Michiel Beijen <michiel.bei...@gmail.com>wrote: > Hi, > > I saw a requirement for having indexes on all tables for replication > before; also as a need for replication, but on a different database > than SQL Server. > > For Sean it would be possible to add indexes of identity columns to > these tables (caveat: untested) > > ALTER TABLE [yourTable] ADD MyID INT IDENTITY(1,1) > CONSTRAINT PK_My_Current_PK PRIMARY KEY CLUSTERED (MyID) > > This would enable SQL Server to do it's replication, and you'd be able > to insert records after this. > > For OTRS in general, I think it might be great if for the upcoming > OTRS version 3.4 all tables would have PK's with unique indexes and > identity columns if needed, so these replication scenarios can be > supported. > > @Martin would you be supportive of such a change? If the answer would > be yes, maybe I can work on this with Sean. > -- > Mike > > > > On Thu, Apr 3, 2014 at 5:16 PM, Sean Killeen <seankill...@gmail.com> > wrote: > > > > 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) > >> > >> "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 > _______________________________________________ > 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
