Re: Thoughts regarding a separate DB for custom DB objects
We did that here on Oracle for a custom 7.5 app with one ARSystem and one Utility schemas. The utility schema contains all the temporary tables, procedures and packages for maintenance and imports (of users, entities, ...). This is very handy because this is all temporary stuff that we can afford to loose and restore a week old backup, not like AR System data that is so expensive to replicate. That said, we chose to create the views and materialized views needed by ARS workflow inside the AR System schema simply because we don't see a value add in using the utility schema. I mean, if you create views, you will probably create a view form in AR System anyway. If you use a View Form based on a complexe view, that uses the data contained in your AR System tables, are you sure that storing the view in a separate schema/DB doesn't add an overhead (like authentication, network loop, etc) ? I don't have the answer, maybe it depends on the DB engine you use ? Sylvain On Fri, Sep 7, 2012 at 2:40 AM, Easter, David david_eas...@bmc.com wrote: ** Just thinking somewhat inside the box – but wouldn’t overlays do the majority of this for you? Any custom objects you create would be well known/documented as would any modifications to BMC base objects. The base BMC objects would remain on the system unchanged. If you really then wanted to transfer only your extensions and modifications to a new box, you’d just transfer the custom and overlaid objects. -David J. Easter Manager of Product Management, AR System BSM Atrium Solutions Management BMC Software, Inc. The opinions, statements, and/or suggested courses of action expressed in this E-mail do not necessarily reflect those of BMC Software, Inc. My voluntary participation in this forum is not intended to convey a role as a spokesperson, liaison or public relations representative for BMC Software, Inc. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Jason Miller Sent: Wednesday, September 05, 2012 3:33 PM To: arslist@ARSLIST.ORG Subject: Thoughts regarding a separate DB for custom DB objects ** I would like to get some input on something I have been thinking about recently. I am considering creating a new DB on the same DB server as the ARSystem DB. This DB would be right next to the ARSystem DB but would house all of the custom DB work we do. My thought is to have all custom tables, views, stored procedures, functions, etc. in new DB which would ideally leave everything in the ARSystem DB created, maintained and modified via the AR System API or BMC installers. Basically I am picturing a layer of abstraction or a library of custom objects if you will. Maybe more like a different namespace or data set for those CMDB gurus out there. As it is right now in our very customized 7.5 ARS DB all custom tables, views, etc. are all intermixed with the ARS maintained objects. We try to keep our objects lumped together by naming convention but just when my team starts using the same convention a different DBA (for example) comes along and uses their favorite convention. If we all agree to only make changes to the custom DB then naming convention is not as important (DBAs usually ask which DB do you need something not so much “what is your naming convention?”). Another considerations is portability of the ARSystem DB. Right now when we refresh our production DB to another environment there are things like different linked server references that need to be updated, object owners, etc. All objects in the custom DB would have the same names between environments (Dev, QA, Prod). With all of the custom objects in their own DB we can move ARSystem DBs between environments and as long as the custom DB is configured correctly for the environment no changes are needed to any of ARSystem DB objects. Our new “out of the box” ITSM 7.6.04 system does not have any custom DB work done yet but I know it is only a matter of time before the need arises as we transition between old and new systems. If there are advantages and no major negative results to this architecture I would like to set this up while we are still out of the box. Can anybody think of any negative (or positive) effects of this approach? I am figuring because it still on the same DB server that there should not be much performance impact. Thanks! Jason _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Thoughts regarding a separate DB for custom DB objects
Now that is a bit tricky... To stay out of the box we need to think inside the box :) You are absolutely correct. All AR maintained objects will utilize overlays and best practices. What I am talking about are custom DB objects not maintained by the AR System. Most of (if not all of) our custom DB objects are for integration purposes Here are some examples: - The Views that View Forms are created against - Tables that cache data from other systems that View Forms are created against. - In the case of our HR and Financial systems creating View Forms over a Linked DB server were too slow for use in UI workflow (Cost Center and Sub Account Menus/Validation, HR/Finance Locations, etc.) - Stored procedures to populate the cache tables mentioned above - Functions converting to/from epoch time - Function for INITCAP (we're MS SQL so we had to build it) - Stored procedures that other systems use to access Remedy data (yeah we are trying to move most of these to web services when possible). - Views that other systems use to access Remedy data (yeah we are trying to move most of these to web services when possible). - Stored procedures to truncate import Forms (T and H tables) Currently all of these things are in our ARSystem DB. Moving these out would make the ARSystem DB more portable and pure. Jason On Thu, Sep 6, 2012 at 5:40 PM, Easter, David david_eas...@bmc.com wrote: ** Just thinking somewhat inside the box – but wouldn’t overlays do the majority of this for you? Any custom objects you create would be well known/documented as would any modifications to BMC base objects. The base BMC objects would remain on the system unchanged. If you really then wanted to transfer only your extensions and modifications to a new box, you’d just transfer the custom and overlaid objects. ** ** -David J. Easter Manager of Product Management, AR System BSM Atrium Solutions Management BMC Software, Inc. The opinions, statements, and/or suggested courses of action expressed in this E-mail do not necessarily reflect those of BMC Software, Inc. My voluntary participation in this forum is not intended to convey a role as a spokesperson, liaison or public relations representative for BMC Software, Inc. ** ** *From:* Action Request System discussion list(ARSList) [mailto: arslist@ARSLIST.ORG] *On Behalf Of *Jason Miller *Sent:* Wednesday, September 05, 2012 3:33 PM *To:* arslist@ARSLIST.ORG *Subject:* Thoughts regarding a separate DB for custom DB objects ** ** ** I would like to get some input on something I have been thinking about recently. I am considering creating a new DB on the same DB server as the ARSystem DB. This DB would be right next to the ARSystem DB but would house all of the custom DB work we do. My thought is to have all custom tables, views, stored procedures, functions, etc. in new DB which would ideally leave everything in the ARSystem DB created, maintained and modified via the AR System API or BMC installers. Basically I am picturing a layer of abstraction or a library of custom objects if you will. Maybe more like a different namespace or data set for those CMDB gurus out there. ** ** As it is right now in our very customized 7.5 ARS DB all custom tables, views, etc. are all intermixed with the ARS maintained objects. We try to keep our objects lumped together by naming convention but just when my team starts using the same convention a different DBA (for example) comes along and uses their favorite convention. If we all agree to only make changes to the custom DB then naming convention is not as important (DBAs usually ask which DB do you need something not so much “what is your naming convention?”). ** ** Another considerations is portability of the ARSystem DB. Right now when we refresh our production DB to another environment there are things like different linked server references that need to be updated, object owners, etc. All objects in the custom DB would have the same names between environments (Dev, QA, Prod). With all of the custom objects in their own DB we can move ARSystem DBs between environments and as long as the custom DB is configured correctly for the environment no changes are needed to any of ARSystem DB objects. ** ** Our new “out of the box” ITSM 7.6.04 system does not have any custom DB work done yet but I know it is only a matter of time before the need arises as we transition between old and new systems. If there are advantages and no major negative results to this architecture I would like to set this up while we are still out of the box. ** ** Can anybody think of any negative (or positive) effects of this approach? I am figuring because it still on the same DB server that there should not be much performance impact. ** ** Thanks! Jason _attend
Re: Thoughts regarding a separate DB for custom DB objects
Ah, that makes more sense now. I know that some folks move their AR indices off to a separate DB, under the control of the DBMS, so I don't know why you couldn't move these things, as long as the appropriate AR System calls knew where to find them. In the few instances where you're moving things that interact a lot with the AR System DB from the AR System DB, I wonder if the increased network traffic during those tasks would be a greater cost than the benefit of having a clean system. Perhaps if they were on the same DB Server/SAN, but under a different DB, that would provide benefit without cost. Rick On Fri, Sep 7, 2012 at 1:54 PM, Jason Miller jason.mil...@gmail.com wrote: ** Now that is a bit tricky... To stay out of the box we need to think inside the box :) You are absolutely correct. All AR maintained objects will utilize overlays and best practices. What I am talking about are custom DB objects not maintained by the AR System. Most of (if not all of) our custom DB objects are for integration purposes Here are some examples: - The Views that View Forms are created against - Tables that cache data from other systems that View Forms are created against. - In the case of our HR and Financial systems creating View Forms over a Linked DB server were too slow for use in UI workflow (Cost Center and Sub Account Menus/Validation, HR/Finance Locations, etc.) - Stored procedures to populate the cache tables mentioned above - Functions converting to/from epoch time - Function for INITCAP (we're MS SQL so we had to build it) - Stored procedures that other systems use to access Remedy data (yeah we are trying to move most of these to web services when possible). - Views that other systems use to access Remedy data (yeah we are trying to move most of these to web services when possible). - Stored procedures to truncate import Forms (T and H tables) Currently all of these things are in our ARSystem DB. Moving these out would make the ARSystem DB more portable and pure. Jason On Thu, Sep 6, 2012 at 5:40 PM, Easter, David david_eas...@bmc.comwrote: ** Just thinking somewhat inside the box – but wouldn’t overlays do the majority of this for you? Any custom objects you create would be well known/documented as would any modifications to BMC base objects. The base BMC objects would remain on the system unchanged. If you really then wanted to transfer only your extensions and modifications to a new box, you’d just transfer the custom and overlaid objects. ** ** -David J. Easter Manager of Product Management, AR System BSM Atrium Solutions Management BMC Software, Inc. The opinions, statements, and/or suggested courses of action expressed in this E-mail do not necessarily reflect those of BMC Software, Inc. My voluntary participation in this forum is not intended to convey a role as a spokesperson, liaison or public relations representative for BMC Software, Inc. ** ** *From:* Action Request System discussion list(ARSList) [mailto: arslist@ARSLIST.ORG] *On Behalf Of *Jason Miller *Sent:* Wednesday, September 05, 2012 3:33 PM *To:* arslist@ARSLIST.ORG *Subject:* Thoughts regarding a separate DB for custom DB objects ** ** ** I would like to get some input on something I have been thinking about recently. I am considering creating a new DB on the same DB server as the ARSystem DB. This DB would be right next to the ARSystem DB but would house all of the custom DB work we do. My thought is to have all custom tables, views, stored procedures, functions, etc. in new DB which would ideally leave everything in the ARSystem DB created, maintained and modified via the AR System API or BMC installers. Basically I am picturing a layer of abstraction or a library of custom objects if you will. Maybe more like a different namespace or data set for those CMDB gurus out there. ** ** As it is right now in our very customized 7.5 ARS DB all custom tables, views, etc. are all intermixed with the ARS maintained objects. We try to keep our objects lumped together by naming convention but just when my team starts using the same convention a different DBA (for example) comes along and uses their favorite convention. If we all agree to only make changes to the custom DB then naming convention is not as important (DBAs usually ask which DB do you need something not so much “what is your naming convention?”). ** ** Another considerations is portability of the ARSystem DB. Right now when we refresh our production DB to another environment there are things like different linked server references that need to be updated, object owners, etc. All objects in the custom DB would have the same names between environments (Dev, QA, Prod). With all of the custom objects in their own DB we can move ARSystem DBs between environments
Re: Thoughts regarding a separate DB for custom DB objects
I've been doing this for years. I've traditionally worked in Oracle shops, where we create a separate schema on the same instance. The data never goes over the network. All custom db objects go into this schema and not the AR owned schema. Each schema has a schema owner (schema name = account name), so there are some grants that have to be given to allow cross-talk, but the maintenance is minimal. The downside to using the views is that views can not be altered, only created and dropped. What this means is that if you apply a grant to a view that AR owns (a db view for a form), and you add a field to that form, the grants to that view are gone. For this reason, I use the T tables when constructing views. Tables can be altered, and this is how Remedy does it, so the grants persist through form modifications. As to the statement about lob/index storage, etc.; this is typically done using separate tablespaces. Tablespaces are like separate storage containers; probably the easiest way to think about them is that they are separate files that the db uses to store stuff. There are some characteristics that you can define at the tablespace level (extent size, lob characteristics, etc.) that make it advantageous to use separate tablespaces for different objects. The reason I separate these things out is for ease of maintenance. Have you ever walked into a shop that didn't do this and had an active Remedy system that was 6 years old and under constant development? It get's messy in the db. DB objects pile up; sometimes they are no longer used, and in the end, you have a pile of db objects and you don't have a good idea if it is something Remedy created or a person created. It's a hell of a ball of yarn to untangle. Axton Grams On Fri, Sep 7, 2012 at 1:02 PM, Rick Cook remedyr...@gmail.com wrote: ** Ah, that makes more sense now. I know that some folks move their AR indices off to a separate DB, under the control of the DBMS, so I don't know why you couldn't move these things, as long as the appropriate AR System calls knew where to find them. In the few instances where you're moving things that interact a lot with the AR System DB from the AR System DB, I wonder if the increased network traffic during those tasks would be a greater cost than the benefit of having a clean system. Perhaps if they were on the same DB Server/SAN, but under a different DB, that would provide benefit without cost. Rick On Fri, Sep 7, 2012 at 1:54 PM, Jason Miller jason.mil...@gmail.comwrote: ** Now that is a bit tricky... To stay out of the box we need to think inside the box :) You are absolutely correct. All AR maintained objects will utilize overlays and best practices. What I am talking about are custom DB objects not maintained by the AR System. Most of (if not all of) our custom DB objects are for integration purposes Here are some examples: - The Views that View Forms are created against - Tables that cache data from other systems that View Forms are created against. - In the case of our HR and Financial systems creating View Forms over a Linked DB server were too slow for use in UI workflow (Cost Center and Sub Account Menus/Validation, HR/Finance Locations, etc.) - Stored procedures to populate the cache tables mentioned above - Functions converting to/from epoch time - Function for INITCAP (we're MS SQL so we had to build it) - Stored procedures that other systems use to access Remedy data (yeah we are trying to move most of these to web services when possible). - Views that other systems use to access Remedy data (yeah we are trying to move most of these to web services when possible). - Stored procedures to truncate import Forms (T and H tables) Currently all of these things are in our ARSystem DB. Moving these out would make the ARSystem DB more portable and pure. Jason On Thu, Sep 6, 2012 at 5:40 PM, Easter, David david_eas...@bmc.comwrote: ** Just thinking somewhat inside the box – but wouldn’t overlays do the majority of this for you? Any custom objects you create would be well known/documented as would any modifications to BMC base objects. The base BMC objects would remain on the system unchanged. If you really then wanted to transfer only your extensions and modifications to a new box, you’d just transfer the custom and overlaid objects. ** ** -David J. Easter Manager of Product Management, AR System BSM Atrium Solutions Management BMC Software, Inc. The opinions, statements, and/or suggested courses of action expressed in this E-mail do not necessarily reflect those of BMC Software, Inc. My voluntary participation in this forum is not intended to convey a role as a spokesperson, liaison or public relations representative for BMC Software, Inc. ** ** *From:* Action Request System discussion list(ARSList) [mailto:
Re: Thoughts regarding a separate DB for custom DB objects
Overlays was my first thoughts as well.. Why another DB when we now have overlays..?? But for ‘some things’, I think it’s a absolutely brilliant idea.. These some things have nothing to do with ARS objects hence overlays wont even be in the picture.. These are ‘foreign’ tables we tend to create in the ARSystem schema, and have it reside in there resulting in many sometimes hundreds of foreign tables in there, and without a good naming convention, you can easily loose sight of whats internal and whats external to the database.. I think for this one and only reason, having a database of something like ARSExternal to keep all external objects outside, and then create DB links to reference this is an absolutely gem of an idea for the pure purpose of maintenance.. In that external space, you could even have a table with the same name – who cares.. Good idea.. But for anything internal, as David rightly pointed out, overlays man.. they will be your next best friend for a few years to come until something better comes out! Joe From: Easter, David Sent: Thursday, September 06, 2012 8:40 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Thoughts regarding a separate DB for custom DB objects ** Just thinking somewhat inside the box – but wouldn’t overlays do the majority of this for you? Any custom objects you create would be well known/documented as would any modifications to BMC base objects. The base BMC objects would remain on the system unchanged. If you really then wanted to transfer only your extensions and modifications to a new box, you’d just transfer the custom and overlaid objects. -David J. Easter Manager of Product Management, AR System BSM Atrium Solutions Management BMC Software, Inc. The opinions, statements, and/or suggested courses of action expressed in this E-mail do not necessarily reflect those of BMC Software, Inc. My voluntary participation in this forum is not intended to convey a role as a spokesperson, liaison or public relations representative for BMC Software, Inc. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Jason Miller Sent: Wednesday, September 05, 2012 3:33 PM To: arslist@ARSLIST.ORG Subject: Thoughts regarding a separate DB for custom DB objects ** I would like to get some input on something I have been thinking about recently. I am considering creating a new DB on the same DB server as the ARSystem DB. This DB would be right next to the ARSystem DB but would house all of the custom DB work we do. My thought is to have all custom tables, views, stored procedures, functions, etc. in new DB which would ideally leave everything in the ARSystem DB created, maintained and modified via the AR System API or BMC installers. Basically I am picturing a layer of abstraction or a library of custom objects if you will. Maybe more like a different namespace or data set for those CMDB gurus out there. As it is right now in our very customized 7.5 ARS DB all custom tables, views, etc. are all intermixed with the ARS maintained objects. We try to keep our objects lumped together by naming convention but just when my team starts using the same convention a different DBA (for example) comes along and uses their favorite convention. If we all agree to only make changes to the custom DB then naming convention is not as important (DBAs usually ask which DB do you need something not so much “what is your naming convention?”). Another considerations is portability of the ARSystem DB. Right now when we refresh our production DB to another environment there are things like different linked server references that need to be updated, object owners, etc. All objects in the custom DB would have the same names between environments (Dev, QA, Prod). With all of the custom objects in their own DB we can move ARSystem DBs between environments and as long as the custom DB is configured correctly for the environment no changes are needed to any of ARSystem DB objects. Our new “out of the box” ITSM 7.6.04 system does not have any custom DB work done yet but I know it is only a matter of time before the need arises as we transition between old and new systems. If there are advantages and no major negative results to this architecture I would like to set this up while we are still out of the box. Can anybody think of any negative (or positive) effects of this approach? I am figuring because it still on the same DB server that there should not be much performance impact. Thanks! Jason ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Thoughts regarding a separate DB for custom DB objects
Thanks Sylvain for the feed back! It is interesting that you called it Utility because I was also thinking about calling the DB Utilities. That is one of the things I am wondering about; will putting the views and tables used by AR System in the Utilities DB have much impact on performance. I tend to think it will not have too much impact since it is all on the same DB server, same disk volume, etc. But then again I am not a DBA, network engineer or storage engineer :) Here is scenario where I would like for custom views to be in their own DB space. Our convention is to prefix our custom views with uv_ for User View (ut_ for User Table). We also like to name our View forms with the View name they use. For example we have a view called uv_LAWItems and the View Form name is LAW:uv_LAWItems. The AR maintained view of the View Form is LAW_uv_LAWItems. There have been times where I was a little confused around the difference between uv_LAWItems and LAW_uv_LAWItems (usually late at night under some kind of time restriction). Now maybe our naming conventions could use some tweaking? However moving uv_LAWItems to a different DB would make it more clear which is the custom object and which is the AR maintain object referencing the custom object in another DB. The performance implication is one of the things I am the most curious about and hoping some of our DB gurus will chime in. I just realized I didn't include our specs in my initial post. I am working mostly with theory here so I am hoping the specs are not that relevant but here they are: ARS 7.6.04 SP3 ITSM .7.6.04 SP2 AR Server - Windows 2008 Standard 64-bit DB Server Windows 2008 Enterprise 64-bit DBMS - MS SQL 2008 Standard 64-bit Jason On Fri, Sep 7, 2012 at 12:05 AM, Sylvain YVON sylvain.y...@gmail.comwrote: We did that here on Oracle for a custom 7.5 app with one ARSystem and one Utility schemas. The utility schema contains all the temporary tables, procedures and packages for maintenance and imports (of users, entities, ...). This is very handy because this is all temporary stuff that we can afford to loose and restore a week old backup, not like AR System data that is so expensive to replicate. That said, we chose to create the views and materialized views needed by ARS workflow inside the AR System schema simply because we don't see a value add in using the utility schema. I mean, if you create views, you will probably create a view form in AR System anyway. If you use a View Form based on a complexe view, that uses the data contained in your AR System tables, are you sure that storing the view in a separate schema/DB doesn't add an overhead (like authentication, network loop, etc) ? I don't have the answer, maybe it depends on the DB engine you use ? Sylvain On Fri, Sep 7, 2012 at 2:40 AM, Easter, David david_eas...@bmc.com wrote: ** Just thinking somewhat inside the box – but wouldn’t overlays do the majority of this for you? Any custom objects you create would be well known/documented as would any modifications to BMC base objects. The base BMC objects would remain on the system unchanged. If you really then wanted to transfer only your extensions and modifications to a new box, you’d just transfer the custom and overlaid objects. -David J. Easter Manager of Product Management, AR System BSM Atrium Solutions Management BMC Software, Inc. The opinions, statements, and/or suggested courses of action expressed in this E-mail do not necessarily reflect those of BMC Software, Inc. My voluntary participation in this forum is not intended to convey a role as a spokesperson, liaison or public relations representative for BMC Software, Inc. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Jason Miller Sent: Wednesday, September 05, 2012 3:33 PM To: arslist@ARSLIST.ORG Subject: Thoughts regarding a separate DB for custom DB objects ** I would like to get some input on something I have been thinking about recently. I am considering creating a new DB on the same DB server as the ARSystem DB. This DB would be right next to the ARSystem DB but would house all of the custom DB work we do. My thought is to have all custom tables, views, stored procedures, functions, etc. in new DB which would ideally leave everything in the ARSystem DB created, maintained and modified via the AR System API or BMC installers. Basically I am picturing a layer of abstraction or a library of custom objects if you will. Maybe more like a different namespace or data set for those CMDB gurus out there. As it is right now in our very customized 7.5 ARS DB all custom tables, views, etc. are all intermixed with the ARS maintained objects. We try to keep our objects lumped together by naming convention but just when my team starts using the same convention a different DBA (for
Re: Thoughts regarding a separate DB for custom DB objects
If the database is MS-SQL and you want to organize your objects to reduce the risk of editing the wrong object, then I don't recommend you to create a separated DB. MS-SQL provides the schema functionality. Just create a new schema, and put all objects there. Then create a user with privileges only at this schema. Thus, this user will only see the external objects and wont see the Remedy ones. Give this user to people that must edit those objects. Remedy will be able to see those objects because they are at the same database. Also the impact on the database is null, since they are at the same database. Jose Manuel Huerta http://theremedyforit.com/ On Fri, Sep 7, 2012 at 9:08 PM, Jason Miller jason.mil...@gmail.com wrote: MS SQL 2008 Standard 64-bit ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Thoughts regarding a separate DB for custom DB objects
Thanks Joe, Rick and Axton! Axton pretty much summed up the reasons why I have been considering this and described our ARSystem DB at the same time :) I have only scratched the surface when working with Oracle had have never completely understood the difference between a user's schema and a different DB. I guess since the schema falls within the DB the files, maintenance and configuration are the same where as a completely different DB would have different file and configuration properties? I think the issue mentioned not being able to alter the AR maintained views is a little different and doesn't change in the proposed setup. We already know to keep a script handy to reset the grants after the drop/create and running the script is part of our move to production procedure. It really depends on what we are doing if we use the T table or the view. Jason On Fri, Sep 7, 2012 at 11:22 AM, Axton axton.gr...@gmail.com wrote: ** I've been doing this for years. I've traditionally worked in Oracle shops, where we create a separate schema on the same instance. The data never goes over the network. All custom db objects go into this schema and not the AR owned schema. Each schema has a schema owner (schema name = account name), so there are some grants that have to be given to allow cross-talk, but the maintenance is minimal. The downside to using the views is that views can not be altered, only created and dropped. What this means is that if you apply a grant to a view that AR owns (a db view for a form), and you add a field to that form, the grants to that view are gone. For this reason, I use the T tables when constructing views. Tables can be altered, and this is how Remedy does it, so the grants persist through form modifications. As to the statement about lob/index storage, etc.; this is typically done using separate tablespaces. Tablespaces are like separate storage containers; probably the easiest way to think about them is that they are separate files that the db uses to store stuff. There are some characteristics that you can define at the tablespace level (extent size, lob characteristics, etc.) that make it advantageous to use separate tablespaces for different objects. The reason I separate these things out is for ease of maintenance. Have you ever walked into a shop that didn't do this and had an active Remedy system that was 6 years old and under constant development? It get's messy in the db. DB objects pile up; sometimes they are no longer used, and in the end, you have a pile of db objects and you don't have a good idea if it is something Remedy created or a person created. It's a hell of a ball of yarn to untangle. Axton Grams On Fri, Sep 7, 2012 at 1:02 PM, Rick Cook remedyr...@gmail.com wrote: ** Ah, that makes more sense now. I know that some folks move their AR indices off to a separate DB, under the control of the DBMS, so I don't know why you couldn't move these things, as long as the appropriate AR System calls knew where to find them. In the few instances where you're moving things that interact a lot with the AR System DB from the AR System DB, I wonder if the increased network traffic during those tasks would be a greater cost than the benefit of having a clean system. Perhaps if they were on the same DB Server/SAN, but under a different DB, that would provide benefit without cost. Rick On Fri, Sep 7, 2012 at 1:54 PM, Jason Miller jason.mil...@gmail.comwrote: ** Now that is a bit tricky... To stay out of the box we need to think inside the box :) You are absolutely correct. All AR maintained objects will utilize overlays and best practices. What I am talking about are custom DB objects not maintained by the AR System. Most of (if not all of) our custom DB objects are for integration purposes Here are some examples: - The Views that View Forms are created against - Tables that cache data from other systems that View Forms are created against. - In the case of our HR and Financial systems creating View Forms over a Linked DB server were too slow for use in UI workflow (Cost Center and Sub Account Menus/Validation, HR/Finance Locations, etc.) - Stored procedures to populate the cache tables mentioned above - Functions converting to/from epoch time - Function for INITCAP (we're MS SQL so we had to build it) - Stored procedures that other systems use to access Remedy data (yeah we are trying to move most of these to web services when possible). - Views that other systems use to access Remedy data (yeah we are trying to move most of these to web services when possible). - Stored procedures to truncate import Forms (T and H tables) Currently all of these things are in our ARSystem DB. Moving these out would make the ARSystem DB more portable and pure. Jason On Thu, Sep 6, 2012 at 5:40 PM, Easter, David david_eas...@bmc.comwrote:
Re: Thoughts regarding a separate DB for custom DB objects
This is the second time using a schema has been mentioned. I am going to look into this. I like where you are heading with this. It keeps the objects organized and can limit permissions. The one issue I see with this approach is my team is local server admin on the DB server and local server admins are automatically sysadmin. I am not happy with with this because I don't like that it gives all server admins (outside of my team) sysadmin but according to our DBA it is just kind of how things work in this setup. The privileged restrictions you mention would not be enforceable for my team. Maybe it is time to remove our local admin access? The other thing I am considering is when we restore the ARSystem DB form another environment the Utilities DB remains the same and doesn't require any updates. A schema would be in the ARSystem DB and things like references to linked servers would need to be updated since they also came over with the DB. I a figuring we could export the schema before the ARSystem DB refresh and import it back after the refresh? A few questions: - Using a schema would we see the schema reference say a Direct SQL action or SQL Menu as we would with calling a different DB? I think there could be a benefit of seeing this reference when troubleshooting logs. - Do you think my original suggestion of using a different DB that uses the same infrastructure (SQL Instance, storage volume) would have a negative impact on performance? Thanks! Jason On Fri, Sep 7, 2012 at 12:15 PM, Jose Manuel Huerta Guillén arsl...@theremedyforit.com wrote: ** If the database is MS-SQL and you want to organize your objects to reduce the risk of editing the wrong object, then I don't recommend you to create a separated DB. MS-SQL provides the schema functionality. Just create a new schema, and put all objects there. Then create a user with privileges only at this schema. Thus, this user will only see the external objects and wont see the Remedy ones. Give this user to people that must edit those objects. Remedy will be able to see those objects because they are at the same database. Also the impact on the database is null, since they are at the same database. Jose Manuel Huerta http://theremedyforit.com/ On Fri, Sep 7, 2012 at 9:08 PM, Jason Miller jason.mil...@gmail.comwrote: MS SQL 2008 Standard 64-bit _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Thoughts regarding a separate DB for custom DB objects
Some terminology to help: - instance: an installation of oracle (can be multiple instances on 1 host, one instance on a host, etc.) - schema: a user that owns objects (schema name matches username); can be many schemas in a given instance, but certain system instances are always present. - tablespace: a storage container (defines data files and it's own set of storage characteristics) - user: an account to connect to oracle Each user has a default tablespace (new objects go here unless explicity defined otherwise) A database link lets a user on one instance connect to some remote instance. Assume you are logged into oracle as aradmin. If you want to retrieve data from a table in a different schema, arsexternal, your statement would look like this: select * from arsexternal.table Axton Grams On Fri, Sep 7, 2012 at 2:36 PM, Jason Miller jason.mil...@gmail.com wrote: ** Thanks Joe, Rick and Axton! Axton pretty much summed up the reasons why I have been considering this and described our ARSystem DB at the same time :) I have only scratched the surface when working with Oracle had have never completely understood the difference between a user's schema and a different DB. I guess since the schema falls within the DB the files, maintenance and configuration are the same where as a completely different DB would have different file and configuration properties? I think the issue mentioned not being able to alter the AR maintained views is a little different and doesn't change in the proposed setup. We already know to keep a script handy to reset the grants after the drop/create and running the script is part of our move to production procedure. It really depends on what we are doing if we use the T table or the view. Jason On Fri, Sep 7, 2012 at 11:22 AM, Axton axton.gr...@gmail.com wrote: ** I've been doing this for years. I've traditionally worked in Oracle shops, where we create a separate schema on the same instance. The data never goes over the network. All custom db objects go into this schema and not the AR owned schema. Each schema has a schema owner (schema name = account name), so there are some grants that have to be given to allow cross-talk, but the maintenance is minimal. The downside to using the views is that views can not be altered, only created and dropped. What this means is that if you apply a grant to a view that AR owns (a db view for a form), and you add a field to that form, the grants to that view are gone. For this reason, I use the T tables when constructing views. Tables can be altered, and this is how Remedy does it, so the grants persist through form modifications. As to the statement about lob/index storage, etc.; this is typically done using separate tablespaces. Tablespaces are like separate storage containers; probably the easiest way to think about them is that they are separate files that the db uses to store stuff. There are some characteristics that you can define at the tablespace level (extent size, lob characteristics, etc.) that make it advantageous to use separate tablespaces for different objects. The reason I separate these things out is for ease of maintenance. Have you ever walked into a shop that didn't do this and had an active Remedy system that was 6 years old and under constant development? It get's messy in the db. DB objects pile up; sometimes they are no longer used, and in the end, you have a pile of db objects and you don't have a good idea if it is something Remedy created or a person created. It's a hell of a ball of yarn to untangle. Axton Grams On Fri, Sep 7, 2012 at 1:02 PM, Rick Cook remedyr...@gmail.com wrote: ** Ah, that makes more sense now. I know that some folks move their AR indices off to a separate DB, under the control of the DBMS, so I don't know why you couldn't move these things, as long as the appropriate AR System calls knew where to find them. In the few instances where you're moving things that interact a lot with the AR System DB from the AR System DB, I wonder if the increased network traffic during those tasks would be a greater cost than the benefit of having a clean system. Perhaps if they were on the same DB Server/SAN, but under a different DB, that would provide benefit without cost. Rick On Fri, Sep 7, 2012 at 1:54 PM, Jason Miller jason.mil...@gmail.comwrote: ** Now that is a bit tricky... To stay out of the box we need to think inside the box :) You are absolutely correct. All AR maintained objects will utilize overlays and best practices. What I am talking about are custom DB objects not maintained by the AR System. Most of (if not all of) our custom DB objects are for integration purposes Here are some examples: - The Views that View Forms are created against - Tables that cache data from other systems that View Forms are created against. - In the case of our HR and Financial systems creating
Re: Thoughts regarding a separate DB for custom DB objects
A schema in MS SQL is just a way to group elements inside a database. So, you can access them as any other element at the database. But is very easy to define different properties or security based on schema. That means that SQL Direct actions or view forms are done as in they were on the same database (if fact they are). Using a different database just makes it more difficult to access form Remedy. If the only purpose is to group elements, then the schema was introduced with this objective. If you want to set different properties, then you need different databases. Anyway, it's a DBA decision. Regards, Jose Manuel Huerta http://theremedyforit.com/ On Fri, Sep 7, 2012 at 9:58 PM, Jason Miller jason.mil...@gmail.com wrote: ** This is the second time using a schema has been mentioned. I am going to look into this. I like where you are heading with this. It keeps the objects organized and can limit permissions. The one issue I see with this approach is my team is local server admin on the DB server and local server admins are automatically sysadmin. I am not happy with with this because I don't like that it gives all server admins (outside of my team) sysadmin but according to our DBA it is just kind of how things work in this setup. The privileged restrictions you mention would not be enforceable for my team. Maybe it is time to remove our local admin access? The other thing I am considering is when we restore the ARSystem DB form another environment the Utilities DB remains the same and doesn't require any updates. A schema would be in the ARSystem DB and things like references to linked servers would need to be updated since they also came over with the DB. I a figuring we could export the schema before the ARSystem DB refresh and import it back after the refresh? A few questions: - Using a schema would we see the schema reference say a Direct SQL action or SQL Menu as we would with calling a different DB? I think there could be a benefit of seeing this reference when troubleshooting logs. - Do you think my original suggestion of using a different DB that uses the same infrastructure (SQL Instance, storage volume) would have a negative impact on performance? Thanks! Jason On Fri, Sep 7, 2012 at 12:15 PM, Jose Manuel Huerta Guillén arsl...@theremedyforit.com wrote: ** If the database is MS-SQL and you want to organize your objects to reduce the risk of editing the wrong object, then I don't recommend you to create a separated DB. MS-SQL provides the schema functionality. Just create a new schema, and put all objects there. Then create a user with privileges only at this schema. Thus, this user will only see the external objects and wont see the Remedy ones. Give this user to people that must edit those objects. Remedy will be able to see those objects because they are at the same database. Also the impact on the database is null, since they are at the same database. Jose Manuel Huerta http://theremedyforit.com/ On Fri, Sep 7, 2012 at 9:08 PM, Jason Miller jason.mil...@gmail.comwrote: MS SQL 2008 Standard 64-bit _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Thoughts regarding a separate DB for custom DB objects
Thanks again for the info! As Axton explained for Oracle you would reference the schema.table but I was not sure if MS SQL would do the same. I like the idea of seeing something.table in definitions and logging to indicate the resources being used are outside the realm of ARSystem. The enhanced security at this time is not too much of a concern at this time (although I may go ahead since it is a new system and lock down ARSystem from the start). Both the schema and different DB approach will give us more flexible permissions than we have now. I am looking for group as you mention but also for separation and independence (within reason). Now I am going to bounce this off our DBAs. It is not so much their decision regarding the architecture however we greatly value their input as the experts. If we gave them too much say they would delete the thousands of AR maintained views that drive them crazy. I think we are going to go with a separate DB unless the DBAs or somebody on the list comes up with a good reason why it isn't good idea. Worst case after we set this up is we have to move those objects in to the ARSystem DB and use the schema approach :) Thanks everybody, Jason On Fri, Sep 7, 2012 at 3:42 PM, Jose Manuel Huerta Guillén arsl...@theremedyforit.com wrote: ** A schema in MS SQL is just a way to group elements inside a database. So, you can access them as any other element at the database. But is very easy to define different properties or security based on schema. That means that SQL Direct actions or view forms are done as in they were on the same database (if fact they are). Using a different database just makes it more difficult to access form Remedy. If the only purpose is to group elements, then the schema was introduced with this objective. If you want to set different properties, then you need different databases. Anyway, it's a DBA decision. Regards, Jose Manuel Huerta http://theremedyforit.com/ On Fri, Sep 7, 2012 at 9:58 PM, Jason Miller jason.mil...@gmail.comwrote: ** This is the second time using a schema has been mentioned. I am going to look into this. I like where you are heading with this. It keeps the objects organized and can limit permissions. The one issue I see with this approach is my team is local server admin on the DB server and local server admins are automatically sysadmin. I am not happy with with this because I don't like that it gives all server admins (outside of my team) sysadmin but according to our DBA it is just kind of how things work in this setup. The privileged restrictions you mention would not be enforceable for my team. Maybe it is time to remove our local admin access? The other thing I am considering is when we restore the ARSystem DB form another environment the Utilities DB remains the same and doesn't require any updates. A schema would be in the ARSystem DB and things like references to linked servers would need to be updated since they also came over with the DB. I a figuring we could export the schema before the ARSystem DB refresh and import it back after the refresh? A few questions: - Using a schema would we see the schema reference say a Direct SQL action or SQL Menu as we would with calling a different DB? I think there could be a benefit of seeing this reference when troubleshooting logs. - Do you think my original suggestion of using a different DB that uses the same infrastructure (SQL Instance, storage volume) would have a negative impact on performance? Thanks! Jason On Fri, Sep 7, 2012 at 12:15 PM, Jose Manuel Huerta Guillén arsl...@theremedyforit.com wrote: ** If the database is MS-SQL and you want to organize your objects to reduce the risk of editing the wrong object, then I don't recommend you to create a separated DB. MS-SQL provides the schema functionality. Just create a new schema, and put all objects there. Then create a user with privileges only at this schema. Thus, this user will only see the external objects and wont see the Remedy ones. Give this user to people that must edit those objects. Remedy will be able to see those objects because they are at the same database. Also the impact on the database is null, since they are at the same database. Jose Manuel Huerta http://theremedyforit.com/ On Fri, Sep 7, 2012 at 9:08 PM, Jason Miller jason.mil...@gmail.comwrote: MS SQL 2008 Standard 64-bit _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Thoughts regarding a separate DB for custom DB objects
Seperate DB adds complexity with no tangible benefits, that I can see. A seperate instance requires maintaining two instances (each patch separately, audited separately, two sets of accounts to maintain, the addition of db links, etc.); it also entails running two instances (you have overhead with each instance: cpu, memory, disk, etc.). I try to opt for the simplest solution that meets the need. The schema, as far as I can see, is that choice. Life and technology is too complicated to add complexity for complexity's sake. Axton Grams On Fri, Sep 7, 2012 at 6:44 PM, Jason Miller jason.mil...@gmail.com wrote: ** Thanks again for the info! As Axton explained for Oracle you would reference the schema.table but I was not sure if MS SQL would do the same. I like the idea of seeing something.table in definitions and logging to indicate the resources being used are outside the realm of ARSystem. The enhanced security at this time is not too much of a concern at this time (although I may go ahead since it is a new system and lock down ARSystem from the start). Both the schema and different DB approach will give us more flexible permissions than we have now. I am looking for group as you mention but also for separation and independence (within reason). Now I am going to bounce this off our DBAs. It is not so much their decision regarding the architecture however we greatly value their input as the experts. If we gave them too much say they would delete the thousands of AR maintained views that drive them crazy. I think we are going to go with a separate DB unless the DBAs or somebody on the list comes up with a good reason why it isn't good idea. Worst case after we set this up is we have to move those objects in to the ARSystem DB and use the schema approach :) Thanks everybody, Jason On Fri, Sep 7, 2012 at 3:42 PM, Jose Manuel Huerta Guillén arsl...@theremedyforit.com wrote: ** A schema in MS SQL is just a way to group elements inside a database. So, you can access them as any other element at the database. But is very easy to define different properties or security based on schema. That means that SQL Direct actions or view forms are done as in they were on the same database (if fact they are). Using a different database just makes it more difficult to access form Remedy. If the only purpose is to group elements, then the schema was introduced with this objective. If you want to set different properties, then you need different databases. Anyway, it's a DBA decision. Regards, Jose Manuel Huerta http://theremedyforit.com/ On Fri, Sep 7, 2012 at 9:58 PM, Jason Miller jason.mil...@gmail.comwrote: ** This is the second time using a schema has been mentioned. I am going to look into this. I like where you are heading with this. It keeps the objects organized and can limit permissions. The one issue I see with this approach is my team is local server admin on the DB server and local server admins are automatically sysadmin. I am not happy with with this because I don't like that it gives all server admins (outside of my team) sysadmin but according to our DBA it is just kind of how things work in this setup. The privileged restrictions you mention would not be enforceable for my team. Maybe it is time to remove our local admin access? The other thing I am considering is when we restore the ARSystem DB form another environment the Utilities DB remains the same and doesn't require any updates. A schema would be in the ARSystem DB and things like references to linked servers would need to be updated since they also came over with the DB. I a figuring we could export the schema before the ARSystem DB refresh and import it back after the refresh? A few questions: - Using a schema would we see the schema reference say a Direct SQL action or SQL Menu as we would with calling a different DB? I think there could be a benefit of seeing this reference when troubleshooting logs. - Do you think my original suggestion of using a different DB that uses the same infrastructure (SQL Instance, storage volume) would have a negative impact on performance? Thanks! Jason On Fri, Sep 7, 2012 at 12:15 PM, Jose Manuel Huerta Guillén arsl...@theremedyforit.com wrote: ** If the database is MS-SQL and you want to organize your objects to reduce the risk of editing the wrong object, then I don't recommend you to create a separated DB. MS-SQL provides the schema functionality. Just create a new schema, and put all objects there. Then create a user with privileges only at this schema. Thus, this user will only see the external objects and wont see the Remedy ones. Give this user to people that must edit those objects. Remedy will be able to see those objects because they are at the same database. Also the impact on the database is null, since they are at the same database. Jose Manuel Huerta
Re: Thoughts regarding a separate DB for custom DB objects
Just thinking somewhat inside the box - but wouldn't overlays do the majority of this for you? Any custom objects you create would be well known/documented as would any modifications to BMC base objects. The base BMC objects would remain on the system unchanged. If you really then wanted to transfer only your extensions and modifications to a new box, you'd just transfer the custom and overlaid objects. -David J. Easter Manager of Product Management, AR System BSM Atrium Solutions Management BMC Software, Inc. The opinions, statements, and/or suggested courses of action expressed in this E-mail do not necessarily reflect those of BMC Software, Inc. My voluntary participation in this forum is not intended to convey a role as a spokesperson, liaison or public relations representative for BMC Software, Inc. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Jason Miller Sent: Wednesday, September 05, 2012 3:33 PM To: arslist@ARSLIST.ORG Subject: Thoughts regarding a separate DB for custom DB objects ** I would like to get some input on something I have been thinking about recently. I am considering creating a new DB on the same DB server as the ARSystem DB. This DB would be right next to the ARSystem DB but would house all of the custom DB work we do. My thought is to have all custom tables, views, stored procedures, functions, etc. in new DB which would ideally leave everything in the ARSystem DB created, maintained and modified via the AR System API or BMC installers. Basically I am picturing a layer of abstraction or a library of custom objects if you will. Maybe more like a different namespace or data set for those CMDB gurus out there. As it is right now in our very customized 7.5 ARS DB all custom tables, views, etc. are all intermixed with the ARS maintained objects. We try to keep our objects lumped together by naming convention but just when my team starts using the same convention a different DBA (for example) comes along and uses their favorite convention. If we all agree to only make changes to the custom DB then naming convention is not as important (DBAs usually ask which DB do you need something not so much what is your naming convention?). Another considerations is portability of the ARSystem DB. Right now when we refresh our production DB to another environment there are things like different linked server references that need to be updated, object owners, etc. All objects in the custom DB would have the same names between environments (Dev, QA, Prod). With all of the custom objects in their own DB we can move ARSystem DBs between environments and as long as the custom DB is configured correctly for the environment no changes are needed to any of ARSystem DB objects. Our new out of the box ITSM 7.6.04 system does not have any custom DB work done yet but I know it is only a matter of time before the need arises as we transition between old and new systems. If there are advantages and no major negative results to this architecture I would like to set this up while we are still out of the box. Can anybody think of any negative (or positive) effects of this approach? I am figuring because it still on the same DB server that there should not be much performance impact. Thanks! Jason _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
