Re: [dev] OTRS on SQL Azure: Question regarding DB Scripts
Hi Sean, if you find a simpler way that will work in the OTRS architecture, I'm absolutely open. OTRS defines database schemas and schema changes in XML, please see scripts/database/otrs-schema.xml. From this, the DBObject creates the SQL files for the different DB types with the help of our drivers, mssql for example. So there is one common XML source that feeds all the different database types (see also http://doc.otrs.org/developer/3.3/en/html/ch02s02.html for a summary). This means we have no options to perform exceptions for particular databases, which is why I believe adding primary keys to all tables would be the right thing to do. If you have another proposal, please explain it. Regards, mg Am 07.04.14 17:56, schrieb Sean Killeen: Hi Martin, Thanks for being so receptive to the idea and continuing the conversation. I'm wondering if there might be a simpler answer than what you've proposed. SQL Azure doesn't require a primary key on the tables -- rather, it only requires a clustered index so that it has an indication of how to store the data. I believe it's very possible that if we create the appropriate clustered index to instruct SQL Server how to store data at the leaf level, we don't necessarily need to create a primary key. Additionally, if these tables were previously small enough to be heaps, we may not need to create the clustered indexes perfectly. Given my understanding of the above, I was wondering if this couldn't be accomplished with changes to the MSSQL database schema setup transformation scripts alone, saving us the necessary work on the drivers, etc. since they'd continue to operate normally. Unlike a primary key, adding a clustered index wouldn't introduce constraints -- though I might be ignorant of problems it would cause elsewhere somehow. Is there something I'm missing that would require us to do more work than that? Happy to put in the work, of course, but I'm thinking we might be able to accomplish this with less ripple effect and I'm all for that. Thanks again, -- Sean On Mon, Apr 7, 2014 at 7:30 AM, Martin Gruner martin.gru...@otrs.comwrote: Hi Sean, hi Mike, this is a very interesting, but also challenging project. Essentially this boils down to adding primary keys to tables that don't have them yet. There are a few obstacles though: - The database drivers of OTRS don't support adding of primary keys to existing tables (yet). - Once this is done for the framework, we will also need to enforce primary keys for all OTRS modules. So the steps to achieve this would be: 1. Add the possibility to add primary keys to all database drivers in OTRS (mysql, postgresql, mssql and oracle). Maybe Mike could help here with the non-mssql databases. This must be covered well with unit tests and should be merged as a separate pull request before the work continues= -- Martin Gruner Senior Developer RD 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
Re: [dev] OTRS on SQL Azure: Question regarding DB Scripts
Martin, I think I may have misled myself but would like your confirmation. I got the impression from browsing https://github.com/OTRS/otrs/tree/rel-3_3/scripts/database. Here, it appears that there are database scripts for different providers, one of which was an MSSQL provider. My thought was that if we could edit the otrs-initial_insert.mssql.sql to include clustered indexes on some of the tables it creates, that this would solve the problem without altering the Schema from the perspective of the XML file. But it sounds from your explanation like these files may not be used and the XML file / driver may be used now instead. Is that the case? Thanks for clarifying, Sean — Sean E-mauled from my mobile device; please excuse typos and brevity. On Tue, Apr 8, 2014 at 3:07 AM, Martin Gruner martin.gru...@otrs.com wrote: Hi Sean, if you find a simpler way that will work in the OTRS architecture, I'm absolutely open. OTRS defines database schemas and schema changes in XML, please see scripts/database/otrs-schema.xml. From this, the DBObject creates the SQL files for the different DB types with the help of our drivers, mssql for example. So there is one common XML source that feeds all the different database types (see also http://doc.otrs.org/developer/3.3/en/html/ch02s02.html for a summary). This means we have no options to perform exceptions for particular databases, which is why I believe adding primary keys to all tables would be the right thing to do. If you have another proposal, please explain it. Regards, mg Am 07.04.14 17:56, schrieb Sean Killeen: Hi Martin, Thanks for being so receptive to the idea and continuing the conversation. I'm wondering if there might be a simpler answer than what you've proposed. SQL Azure doesn't require a primary key on the tables -- rather, it only requires a clustered index so that it has an indication of how to store the data. I believe it's very possible that if we create the appropriate clustered index to instruct SQL Server how to store data at the leaf level, we don't necessarily need to create a primary key. Additionally, if these tables were previously small enough to be heaps, we may not need to create the clustered indexes perfectly. Given my understanding of the above, I was wondering if this couldn't be accomplished with changes to the MSSQL database schema setup transformation scripts alone, saving us the necessary work on the drivers, etc. since they'd continue to operate normally. Unlike a primary key, adding a clustered index wouldn't introduce constraints -- though I might be ignorant of problems it would cause elsewhere somehow. Is there something I'm missing that would require us to do more work than that? Happy to put in the work, of course, but I'm thinking we might be able to accomplish this with less ripple effect and I'm all for that. Thanks again, -- Sean On Mon, Apr 7, 2014 at 7:30 AM, Martin Gruner martin.gru...@otrs.comwrote: Hi Sean, hi Mike, this is a very interesting, but also challenging project. Essentially this boils down to adding primary keys to tables that don't have them yet. There are a few obstacles though: - The database drivers of OTRS don't support adding of primary keys to existing tables (yet). - Once this is done for the framework, we will also need to enforce primary keys for all OTRS modules. So the steps to achieve this would be: 1. Add the possibility to add primary keys to all database drivers in OTRS (mysql, postgresql, mssql and oracle). Maybe Mike could help here with the non-mssql databases. This must be covered well with unit tests and should be merged as a separate pull request before the work continues= -- Martin Gruner Senior Developer RD 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
Re: [dev] OTRS on SQL Azure: Question regarding DB Scripts
Hi Sean, exactly. These files are generated from the XML (with bin/otrs.xml2sql.pl) and will be overwritten whenever there are changes to the XML and/or the drivers. Regards, mg Am 08.04.14 12:04, schrieb Sean Killeen: Martin, I think I may have misled myself but would like your confirmation. I got the impression from browsing https://github.com/OTRS/otrs/tree/rel-3_3/scripts/database. Here, it appears that there are database scripts for different providers, one of which was an MSSQL provider. My thought was that if we could edit the otrs-initial_insert.mssql.sql to include clustered indexes on some of the tables it creates, that this would solve the problem without altering the Schema from the perspective of the XML file. But it sounds from your explanation like these files may not be used and the XML file / driver may be used now instead. Is that the case? Thanks for clarifying, Sean — Sean E-mauled from my mobile device; please excuse typos and brevity. On Tue, Apr 8, 2014 at 3:07 AM, Martin Gruner martin.gru...@otrs.com mailto:martin.gru...@otrs.com wrote: Hi Sean, if you find a simpler way that will work in the OTRS architecture, I'm absolutely open. OTRS defines database schemas and schema changes in XML, please see scripts/database/otrs-schema.xml. From this, the DBObject creates the SQL files for the different DB types with the help of our drivers, mssql for example. So there is one common XML source that feeds all the different database types (see also http://doc.otrs.org/developer/3.3/en/html/ch02s02.html for a summary). This means we have no options to perform exceptions for particular databases, which is why I believe adding primary keys to all tables would be the right thing to do. If you have another proposal, please explain it. Regards, mg Am 07.04.14 17:56, schrieb Sean Killeen: Hi Martin, Thanks for being so receptive to the idea and continuing the conversation. I'm wondering if there might be a simpler answer than what you've proposed. SQL Azure doesn't require a primary key on the tables -- rather, it only requires a clustered index so that it has an indication of how to store the data. I believe it's very possible that if we create the appropriate clustered index to instruct SQL Server how to store data at the leaf level, we don't necessarily need to create a primary key. Additionally, if these tables were previously small enough to be heaps, we may not need to create the clustered indexes perfectly. Given my understanding of the above, I was wondering if this couldn't be accomplished with changes to the MSSQL database schema setup transformation scripts alone, saving us the necessary work on the drivers, etc. since they'd continue to operate normally. Unlike a primary key, adding a clustered index wouldn't introduce constraints -- though I might be ignorant of problems it would cause elsewhere somehow. Is there something I'm missing that would require us to do more work than that? Happy to put in the work, of course, but I'm thinking we might be able to accomplish this with less ripple effect and I'm all for that. Thanks again, -- Sean On Mon, Apr 7, 2014 at 7:30 AM, Martin Gruner martin.gru...@otrs.comwrote: Hi Sean, hi Mike, this is a very interesting, but also challenging project. Essentially this boils down to adding primary keys to tables that don't have them yet. There are a few obstacles though: - The database drivers of OTRS don't support adding of primary keys to existing tables (yet). - Once this is done for the framework, we will also need to enforce primary keys for all OTRS modules. So the steps to achieve this would be: 1. Add the possibility to add primary keys to all database drivers in OTRS (mysql, postgresql, mssql and oracle). Maybe Mike could help here with the non-mssql databases. This must be covered well with unit tests and should be merged as a separate pull request before the work continues= -- Martin Gruner Senior Developer RD 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
Re: [dev] 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 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.comwrote: 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
Re: [dev] OTRS on SQL Azure: Question regarding DB Scripts
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.comwrote: 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.comwrote: 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
Re: [dev] OTRS on SQL Azure: Question regarding DB Scripts
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
Re: [dev] OTRS on SQL Azure: Question regarding DB Scripts
@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.comwrote: 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
[dev] OTRS on SQL Azure: Question regarding DB Scripts
*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
