Re: OTRS on SQL Azure: Question regarding DB Scripts

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 <[email protected]>
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 <[email protected]>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 <[email protected]>
>> 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 <[email protected]>
>> 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 <[email protected]>
>> 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 <[email protected]>
>> 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