Options to create readonly near realtime copy of a database

  • ricardo_chicas

    SSCertifiable

    Points: 5024

    Hello all,

    I have a 2 node cluster, with a database that is part of the AG, now I have a third machine were I need a copy of just one of the databases of the cluster, that copy must be near-realtime, and allowing reads, in the same way as the second node of the cluster, so far I think my options are replication, log shipping,  any others?

    I am biased against replication due to the size of the objects (about 1TB) and transactions and the horrible experiences we had in the past where that thing failed and that lead to days trying to recreate it.

    It would have been ideal if when adding a db to the AG I could select on which nodes I wanted to be replicated

    So I am open to suggestions

    thanks

     

  • Site Owners

    SSC Guru

    Points: 80373

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Jeffrey Williams

    SSC Guru

    Points: 88654

    Are you on Enterprise Edition - or are you using Standard Edition?

    Assuming Enterprise Edition, you can add that third machine into the cluster, install a stand-alone SQL Server instance and setup a new AG from the primary to this new instance.  Set that secondary as read-only with asynchronous and then add the database(s) you want to be accessible from that node.

    You do not need a listener for this - have the users access that instance directly for read-only reports and extracts.  You will need to create the login and users on the primary then create the login with the same SID on this secondary.  Once the logins have been created on both nodes (SQL logins only - windows users don't need a login on the primary) - you can disable or delete the login on the primary.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • ricardo_chicas

    SSCertifiable

    Points: 5024

    Thanks that was my original idea (yes I am in enterprise)

    the thing is that at the second node I have that database replicated already, and when I created a new AG it didn't let me add the database to it because it existed already at another AG...

    and if I just add all the dbs for the third node that will replicate many dbs that I do not need and do not have the space for it...

  • Jeffrey Williams

    SSC Guru

    Points: 88654

    I missed that this database was already included in the other AG.  You are fairly stuck here...

    If you already have a secondary that is set to read-only, why do you need another secondary setup as read-only?  I assumed you had a secondary specifically for HA and it isn't set to read-only.

    If you just need a secondary for HA - setting up a FCI (failover cluster instance) satisfies that requirement much easier than an AG.  Which would then allow for a read-only secondary of only those databases needed for reporting/ETL processes.  But, it all depends on what you are trying to accomplish with the cluster.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • ricardo_chicas

    SSCertifiable

    Points: 5024

    it is for a very specific use, where I need that db to be in a completely separate server, since the secondary copy has its uses and can't be share, so I guess replication and log shipping are my only options

  • Jeffrey Williams

    SSC Guru

    Points: 88654

    Log shipping won't get you near-real time data in a read-only database.  You have to stop applying log files and switch the database to STANDBY for it to be accessible, then switch back and apply log files up to current.

    Replication may be the only option - but now you have the issue of which tables need to be replicated and what indexes.

    If I already had a secondary setup as read-only I would just grant access to that secondary.  Not sure why it can't be shared if it is already available in that capacity.  Barring that...I would push for enough storage to be made available to meet the requirements.  The person/group requesting this access shouldn't expect to get it for free - if that is the requirement then they should purchase the additional storage that is needed.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • ricardo_chicas

    SSCertifiable

    Points: 5024

    yeah, so, I have an idea, I could remove that db from its current ag, create a new one and have it at the three machines, how about that?

  • Jeffrey Williams

    SSC Guru

    Points: 88654

    That works, but you lose the fail over if that is a concern

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • ricardo_chicas

    SSCertifiable

    Points: 5024

    I think that is something we can handle, thanks, by voicing this out , was able to find a good solution

     

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

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