Offline Secondary
Hi experts, A customer wants to have a secondary read only instance as base for his DWH. The trick is that the server should have a consinstent state as read-only secondary, which is based on logical logfiles (like continous logical log restore, they want to be able to roll forward the logs up to a specific point in time/logs and then do ETL to their external DWH). And - no direct connectivity between the servers, we are only able to transfer backup files (Level 0 and Logs) via an intermediate system using sftp and setup some cron stuff to automatically process the logfiles when the time is reached. Of course, they do not want to transfer always the L0, but to export data and then rollforward the logs from the day to get to the next timepoint. Intended setup: - IDS11.70FCxGE with Linux as master instance - IDS11.70xCxIE (Innovator) with Linux as secondary instance Would that work ? By standard, continous logical log restore would leave the server in recovery mode and not allow any queries. Is it possible to set a server in secondary mode in this state, without having connectivity to the primary ? Anbody done this before ? Any thoughts are welcome ! Thanks, Marcus Haarmann Geschäftsführer Midoco GmbH Otto-Hahn-Str. 12 40721 Hilden Tel. +49 (2103) 28 74 0 Fax. +49 (2103) 28 74 28 www.midoco.de Member of Pisano Holding GmbH Better Travel Technology www.pisano-holding.com Amtsgericht Düsseldorf - HRB 51420 - USt.ID DE814319276 - Geschäftsführer: Steffen Faradi, Marcus Haarmann, Jörg Hauschild -
Continuous log restore has to start with a full restore of a level 0 archive (and possibly level1 & /or level 2 archives if you don't have logical logs going back far enough). You cannot initiate continuous log restore using an export/import. If you wanted to read the CLR secondary you would have to put it into full online mode but to return it to CLR "mode" you would have to restore another archive from the primary. Another option would be to use the Change Data Capture API to capture changes to the production database and apply them as SQL insert, update, & delete statements to a "live" secondary that is always in a usable mode. However, that server would, obviously, not be in a read-only state and would require a full server license. Indeed, any secondary that you actively read from, even periodically to export data to a data warehouse, would require a full server license. Have you considered buying the Informix Warehouse Accelerator (IWA) instead? You can run it on a separate Intel/Linux machine connected to the production server and periodically upload tables you need to perform complex DW style queries against to it. Your BI tools would connect directly to the production server, but their queries would actually be satisfied by data from the IWA many times faster than most data warehouses loaded into Informix, Oracle, DB2, or other RDBMS's without something like IWA (and there ISN'T anything like IWA out there). I have seen queries run 400 times faster and more in IWA than in a 'normal' RDBMS. Most queries, regardless of how much data is loaded into IWA or how complex the query is, run in under 2 minutes many in under 2 seconds and all without placing any strain on production queries or affecting the Informix cache's contents. IWA has the ability to allow you to refresh the tables uploaded to it periodically without performing another full upload. If you are currently running Informix on Linux, you can upgrade to Informix Ultimate Warehouse Edition for a reasonable additional license which is usually less expensive than a full readable secondary server license. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Feb 25, 2013 at 8:20 AM, Marcus Haarmann <marcus.haarmann@midoco.de>wrote: > Hi experts, > > A customer wants to have a secondary read only instance as base for his > DWH. > The trick is that the server should have a consinstent state as read-only > secondary, > which is based on logical logfiles (like continous logical log restore, > they > want to be able > to roll forward the logs up to a specific point in time/logs and then do > ETL > to their external DWH). > And - no direct connectivity between the servers, we are only able to > transfer > backup > files (Level 0 and Logs) via an intermediate system using sftp and setup > some > cron stuff > to automatically process the logfiles when the time is reached. > Of course, they do not want to transfer always the L0, but to export data > and > then > rollforward the logs from the day to get to the next timepoint. > > Intended setup: > - IDS11.70FCxGE with Linux as master instance > - IDS11.70xCxIE (Innovator) with Linux as secondary instance > > Would that work ? By standard, continous logical log restore would leave > the > server > in recovery mode and not allow any queries. Is it possible to set a server > in secondary mode in this state, without having connectivity to the > primary ? > Anbody done this before ? > > Any thoughts are welcome ! > > Thanks, > > Marcus Haarmann > Geschäftsführer > > Midoco GmbH > Otto-Hahn-Str. 12 > 40721 Hilden > > Tel. +49 (2103) 28 74 0 > Fax. +49 (2103) 28 74 28 > www.midoco.de > > Member of Pisano Holding GmbH > Better Travel Technology > www.pisano-holding.com > > Amtsgericht Düsseldorf - HRB 51420 - USt.ID DE814319276 - > Geschäftsführer: Steffen Faradi, Marcus Haarmann, Jörg Hauschild - > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f22beb98f632a04d68e6cb1
How about using Remote Standalone Secondary server(RSS) and STOP_APPLY feature ? Thanks & Regards, Nagaraju From: "Marcus Haarmann" <marcus.haarmann@midoco.de> To: ids@iiug.org, Date: 02/25/2013 07:20 AM Subject: Offline Secondary [29590] Sent by: ids-bounces@iiug.org Hi experts, A customer wants to have a secondary read only instance as base for his DWH. The trick is that the server should have a consinstent state as read-only secondary, which is based on logical logfiles (like continous logical log restore, they want to be able to roll forward the logs up to a specific point in time/logs and then do ETL to their external DWH). And - no direct connectivity between the servers, we are only able to transfer backup files (Level 0 and Logs) via an intermediate system using sftp and setup some cron stuff to automatically process the logfiles when the time is reached. Of course, they do not want to transfer always the L0, but to export data and then rollforward the logs from the day to get to the next timepoint. Intended setup: - IDS11.70FCxGE with Linux as master instance - IDS11.70xCxIE (Innovator) with Linux as secondary instance Would that work ? By standard, continous logical log restore would leave the server in recovery mode and not allow any queries. Is it possible to set a server in secondary mode in this state, without having connectivity to the primary ? Anbody done this before ? Any thoughts are welcome ! Thanks, Marcus Haarmann Geschäftsführer Midoco GmbH Otto-Hahn-Str. 12 40721 Hilden Tel. +49 (2103) 28 74 0 Fax. +49 (2103) 28 74 28 www.midoco.de Member of Pisano Holding GmbH Better Travel Technology www.pisano-holding.com Amtsgericht Düsseldorf - HRB 51420 - USt.ID DE814319276 - Geschäftsführer: Steffen Faradi, Marcus Haarmann, Jörg Hauschild - ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.