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 <[email protected]> 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 <[email protected]> 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 <[email protected]>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 <[email protected]>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: >>>> [email protected]] *On Behalf Of *Jason Miller >>>> *Sent:* Wednesday, September 05, 2012 3:33 PM >>>> *To:* [email protected] >>>> *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"_ >>> >>> >>> _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"
