Post reply

Can I purge data on the principal without the purging it on the mirror?

  • January 29, 2013 at 6:57 am

    #273455

    Hi,

    I'm planning on using asynchronous, high performance mirroring to maintain an archive of my production database.

    The mirror will be used for reporting (via snapshots). My issue is that the production database will maintain a [configurable] 30 to 90 days of data. I want the mirror to retain all data in perpetuity.

    How can I execute my purge operation on the principal, but have the mirror ignore the deletes (and possibly the dropping of a partition - this part of design is tbd).

    Thanks for any help.

  • January 29, 2013 at 7:27 am

    #1582132

    not with a mirror; the mirror is a read only copy of whatever is in production, right?

    It's been a while since i did this, but i think you can do it with transactional replication, either as a setting in the process, or by modifying the special procedure that gets created for the process; you would remove the code that would normally delete rows. My memory was a bit hazy

    till my Google-Fu found tis:

    Options to not replicate SQL Server DELETE commands[/url]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 29, 2013 at 7:33 am

    #1582136

    Mirroring is a high-availability feature that can additionally be leveraged for reporting. It is not primarily a reporting solution. As a HA feature, the mirror is an exact, identical copy of the principal database

    I would recommend transactional replication slightly customised so that the deletes don't replicate. This additionally gives you the ability to have different indexes on the reporting replica.

    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

  • January 29, 2013 at 9:01 am

    #1582194

    I would recommend transactional replication slightly customised so that the deletes don't replicate. This additionally gives you the ability to have different indexes on the reporting replica.

    Sorry to hijack this thread...but off hand do you have any information on how to go about customizating transactional replication to omit deletes???

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • January 29, 2013 at 9:48 am

    #1582230

    MyDoggieJessie (1/29/2013)

    I would recommend transactional replication slightly customised so that the deletes don't replicate. This additionally gives you the ability to have different indexes on the reporting replica.

    Sorry to hijack this thread...but off hand do you have any information on how to go about customizating transactional replication to omit deletes???

    The link Lowell posted?

    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

  • January 30, 2013 at 11:58 am

    #1582684

    Thanks Lowell and Gail for your input.

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

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