Log-shipping - How to sync two different databases - Primary is Live and Seconday is for Archiving

Question

Log-shipping - How to sync two different databases - Primary is Live and Seconday is for Archiving

iBar

SSCertifiable

Points: 5872
More actions
October 28, 2010 at 3:48 pm

#230964

Hello,
SQL server 2005 64 bit standard edition sp3. I have performed the following steps
1. Created a full back of MainDB Employee database.
2. Restored as PDB1 with Full recovery (Test Primary Database) from same above backup.
3. Restored as SDB2 with Stand by option(test Secondary database) from same above backup.
4. Deleted few OLD employees records in PDB1 (Pupose is to remove unwanted hisoric records from the Live OLTP DB).
5. Changed the recovery model of PDB1 to simple and then shrinked the log file and the change the recorvey model back to FULL.
6. Now Created log-shipping between PDB1 as primary and SDB2 as seconday. Setuped successfully. No problem
7. Now LSBackup job creating .trn transaction files after every specific interval on primary PDB1 and also LSCopy job successfully copies all transaction files to seconday SDB2.
8. Problem is LSRestore job runs successfully but skip all transaction file every time. "Could not apply this log to SDB2....."
9. I think its something to do with LSN ... may be.
The reason i am doing this all is to remove historic record from live database every month and then also keep latest records in Sync with Archived SDB2 database which will contain everything. This approach will also give automatic update of latest records and sync them with SDB2 complete archive database.
I think, if this works then its an easy data Archiving strategy. but not working so far. Otherwise you might need to write very complex logic in SP OR an SSIS to delete OLD and Insert/Update latest records from Live DB to Archival DB.
So any thought, any way round, any other better and easy option of archiving database OR Could the above setup can work some how ?
Thanks and Cheers.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

MibD (10/28/2010)
5. Changed the recovery model of PDB1 to simple and then shrinked the log file and the change the recorvey model back to FULL.

This broke the log chain. After that switch to simple you'll need to take another full (or diff) backup to restart the log chain and then you'll need to recreate the logshipping secondary completely.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

MibD (10/28/2010)
I think, if this works then its an easy data Archiving strategy. but not working so far. Otherwise you might need to write very complex logic in SP OR an SSIS to delete OLD and Insert/Update latest records from Live DB to Archival DB.

Log shipping is not for archival and your idea won't work. Log shipping is for DR and the log restores will keep the secondary DB identical to the primary. Hence when you delete the old records from the primary, the delete will be logged and when that log backup is restored on the secondary the old records will be removed from that database too.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

iBar SSCertifiable Points: 5872 More actions · Answer 3

Oh yes agreed. Thanks.

But thats what i am trying to do. Delete record before setup log-shipping and then remove Log of that deletion before i create a Log-shipping. So whenever needed, i drop the log-shipping delete records from Primary and then create the Log-shipping back.

So if this is not possible then Can i achive this thing without using any external tool OR SSIS.

Can i do better archiving using any avaialble technique within SSMS ?

Much appreciate your reply.

Thanks and Cheers.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

MidBar (10/28/2010)
But thats what i am trying to do. Delete record before setup log-shipping and then remove Log of that deletion before i create a Log-shipping. So whenever needed, i drop the log-shipping delete records from Primary and then create the Log-shipping back.

Won't work. Any break in the log chain will require you to recreate the secondary from scratch. SQL won't allow you to restore with missing log records

So if this is not possible then Can i achive this thing without using any external tool OR SSIS.
Can i do better archiving using any avaialble technique within SSMS ?

SSIS will work, you can write packages to do the archiving. You can use the import/export wizard, but that's manual. Don't know about 3rd party tools, but it's not that hard a thing to write.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

iBar SSCertifiable Points: 5872 More actions · Answer 5

Thanks Gail,

SSIS maintenance cost is very high. There are so many tables in the production. Everytime there is a change in structure, you might need a change in SSIS as well to map the columns accodingly.

You might need to define data flow task for each table. Seems more time consuming and thats why more maintainance cost.

Do you know any easy way in SSIS to Auto pick all tables with primary key first and transform the data into seconday db with same object name and keep looping on all objects and then run same loop on child objects... OR do i really need to map each object one by one ?

Cheers.