As we consider this further, in the meantime, is some sort of data dictionary
available? I've looked and found the diagram but it would be great to find a
description of some of those tables so that I can examine how to best go about
accomplishing the clustered indexes.—
Sean
E-"mauled" from my mobile device; please excuse typos and brevity.
On Thu, Apr 3, 2014 at 4:14 PM, Sean Killeen <seankill...@gmail.com>
wrote:
> @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
