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.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.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"_ >> >> >> _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"
