I'd be interested in all the details Chad Hall (501) 342-2650
-----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of DILL, PATRICK A Sent: Tuesday, November 14, 2006 5:42 PM To: [email protected] Subject: Re: DSO replacement (UNCLASSIFIED) We found several limitations in Reporting using Remedy, such as the Remedy ODBC driver not supporting joining of multiple tables, having to create join forms for this purpose or combining two distinct Remedy ODBC drivers, and the slow speed of the driver (plus we have millions of records in our tables). I came up with a Reporting innovation solution and worked with another CS intern to design a solution that would accomplish these goals: 1. The reporting DB must be available 100% of the time for reporting purposes (not another Remedy Server) 2. Any changes to Remedy Form schema would be updated in the reporting DB (field database names: adds, deletes, changes) 3. Any changes to Remedy data would be updated in the reporting DB 4. The process must run constantly, the reporting DB is essentially a transformed copy of the Remedy DB and the reporting DB should be within an hour of live Production. 5. The reporting DB must be flexible for row level security, max time before releasing reporting connections, tables to copy, data deletion on, account info, etc... (in the form of an INI or XML settings file) 6. Other processes should be allowed to occur before the processing loop is finished and repeated (example, we have Store Proc's that kick off other SP's to create additional reporting tables) The solution was designed for SQL 2000 and has been running without any issue for over two months. We always have one reporting DB offline being updated and one online for live reporting. I would like to finish designing the solution for SQL 2005, a GUI for the settings file, easy install and maybe recode this in Oracle with some assistance. Others could or probably already do some of this with the myriad of ETL tools available; however, this solution is much more dynamic and was designed just for Remedy. If there is enough interest I can provide some input on how to design this. Pat Dill Safeco Insurance Enterprise Tools Team Roosevelt Commons, Floor 5 206-545-3217 Office 206-931-3006 Cell -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Hall Chad - chahal Sent: Tuesday, November 14, 2006 1:30 PM To: [email protected] Subject: Re: DSO replacement (UNCLASSIFIED) These are the main advantages we saw in using DSO for our reporting server: 1. Fairly simple to setup and maintain 2. Row-level security maintained 3. Remedy ODBC ensures proper handling of date/time and selection values 4. Improved performance on production since reports are ran on completely separate app/DB servers I agree it's much, much more efficient to report directly against the database. However, we have LOTS of people creating reports and very few have the DBA skills to properly handle conversion of timestamps, selection values, etc. Those that do are allowed to go straight to the reporting DB if they want. We also must maintain row-level security on the data for many users, which would be hard to ensure for going directly to the DB. DSO on a separate server does have its price tag, but worth it for us because of the low complexity and inherent security of ARS. Chad Hall (501) 342-2650 -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Will Du Chene Sent: Tuesday, November 07, 2006 7:49 PM To: [email protected] Subject: Re: DSO replacement (UNCLASSIFIED) Database replication is something that requires planning, at least as much as one would give the application. It can be challenging to set up (what tables do you plan on replicating - all or just a few key ones?), and does have a cost in terms of performance that is associated with it (because each transaction is sent from your controller to the other nodes and traverses the network). The exact cost will vary, depending on your platform, network, the amount of data and implementation. Log shipping is simple, easy, and can be scripted. IMHO - it makes point-in-time recovery easier, as you can recreate the database from the latest full dump and then apply the logs forward. Replication does not necessarily give you that option. I suppose that the arguement could be made that if your recovery database is dumped (both full with differentials) you could get the same effect, but that is beside the point. Unless there is some sort of bona-fide need to use replication, I would stick with shipping the logs. If your application cannot stand any down time for a partial recovery, you probably shouldn't be using replication anyway - forsaking it instead for a cluster. Warning: I am about to interject my opinion in these next few paragraphs. You are free to agree or disagree as you might see fit. DSO works fine for what it was intended. It's an option, and has a price associated with it. Moving a ticket around here and there is not a bad thing. Using DSO for keeping your servers in sync, however, is not something that I would give consideration to. There are other options at the database level for doing this. I noticed there was mention of using DSO below for a reporting server. If you are using DSO, then you have to another AR System server installation. This confuses me: why would anyone pay for another server, purchase another database license, another AR System server license, and the DSO option simply for reports? This just does not seem to compute (na-nu, na-nu, shazbot!), and is - IMHO - a very poor use of the resources that are at hand. Reporting data does NOT have to be in the AR System database. Likewise, it does NOT have to be within the confines of the AR System to be able to pull a report based on it. Reporting data does NOT even have to be on the same database platform to be usable. Now before the eyebrows start to raise, let me explain... It is entirely possible to create another database (indeed another entire instance) and then copy the data over to it, using an export script that could be written in any of the myriad of libraries and languages that get used in conjunction with the AR System. If the API is used, all of the quirky date issues, and enumerated values get (generally) translated for you. At the same time, it's possible to use a vendor form to view the data from within the AR System across databases and certainly even across servers. If permissions are a concern, create several VIEWS of your reporting table, and then qualify each of the views such that they query records only of a certain type - which can be set during export time and then build your vendor forms on top of that. (You could also grant the appropriate permissions to a group of views and then move your database users in and out as necessary.) >From a reporting perspective, most of the reporting tools are going to use an ODBC connection to the database to get the data anyway, so to the report - it doesn't make a difference. If views are used, then any sort of application specific 'features' are a mute point. I guess that I would be interested in hearing about how people handle warehousing AR System server data. Someone please don't tell me that the standard is to construct another server just for that. > Totally agree - DB replication is much faster, more scalable, and more > easily managed (if you have a competent DBA) than DSO. > > Rick > > On 11/7/06, Hall Chad - chahal <[EMAIL PROTECTED]> wrote: >> >> ** >> >> I would suggest a standby database server instead of DSO if all you need >> is failover. SQL Server Log Shipping or Oracle Data Guard are both good >> options for this. Use another server (or it could be the same one) and >> install AR Server for app failover. This gives you failover at the app >> and >> db layers without the AR Server overhead (and synchronization problems) >> associated with DSO. >> >> >> >> However, if your goal is to have a reporting server then you may want to >> stick with DSO. >> >> >> >> *Chad Hall* >> (501) 342-2650 >> ------------------------------ >> >> *From:* Action Request System discussion list(ARSList) [mailto: >> [EMAIL PROTECTED] *On Behalf Of *Frank, Gordon M Mr NISO/Lockheed >> Martin >> *Sent:* Tuesday, November 07, 2006 10:36 AM >> *To:* [email protected] >> *Subject:* DSO replacement (UNCLASSIFIED) >> >> >> >> Classification: *UNCLASSIFIED* >> >> Caveats: NONE >> >> Hello all, >> >> We are currently utilizing DSO to mirror two production servers. We use >> Like IDs to do this. We are getting performance hits because of this. >> I'd >> like to replace this with something else. I can think of several >> options, >> but I'm looking for other's ideas. >> >> >> >> Is there a preferred method for producing mirror images of two >> productions >> servers? >> >> >> >> The goal is to always have a fall back server in case the primary goes >> down. >> >> >> >> Thanks up front, >> >> Gordon Frank >> >> Lockheed Martin >> >> >> >> >> >> Classification: *UNCLASSIFIED* >> >> Caveats: NONE >> > > ________________________________________________________________________ _______ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where > the Answers Are" > ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" ************************************************************************ *** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. ************************************************************************ **** ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
