Post reply

Replication question

  • January 28, 2020 at 7:14 am

    #3720828

    Im an Oracle DBA by trade and been tasked with setting up transaction replication in SQL Server 2014 from one host to another.

    I set up a test DB, configured a publication, set up a subscriber DB, set up subscriptions and transactions are replicating.  All good to here.

    In oracle we have dataguard which allows us to replicate but we need to manually flip it to active mode to query or run transactions against it.

    In my replicated subscriber database in SQL Server, that doesnt seem to be the case, it seems to be open for any transactions as well as taking transactions from my publishing database..  Is there  a way that I can put it into a "standby" mode like oracles dataguard and still receive transactions so I'm in control of it?

    Ive gone over documentation but not seeing anything jump out that answers.

     

     

     

  • January 28, 2020 at 11:00 am

    #3720856

    Unfortunately you cannot set the database to read-only as that would break the replication topology as it couldn't write to the DB.

    The best you could do is to have a runbook for when you need to flip it to resolve the permissions needed.

    So people would have db_datareader only on the replicated databases, then when it needs to be made active you would change that to also include db_datawriter.  That's just a general thing, how you grant permissions in your environment may be different.

    You wont be able to limit people with sysadmin or db_owner on the databases, so it really does come down to ensuring proper security and least privilege needed etc

  • January 28, 2020 at 11:07 am

    #3720858

    most likely you have a few  solutions here

    1. lock down the permissions on the subscriber database so that only the distribution agent can write, but everyone else has read permissions
    2. choose a different solution - back in 2005 we used mirroring with snapshots , but you have to run this in "high performance mode" which requires enterprise edition... i'm not sure if this feature still exists
    3. what you most likely need is an always on availability group https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15

    option 1 is the easy one (you already have the replication set up) - option 3 depends on your setup

    MVDBA

  • January 28, 2020 at 11:31 am

    #3720861

    Thanks guys.   I'll check out mirroring as I have enterprise.

    how about having a check to say   "Is the subscriber database the same as the publishing database"

    I might be able to live with that and if something got in that shouldnt I can rebuild from a snapshot (And investigate how it changed) as this will be for a disaster recovery site where no-one should really have access and db is only a couple GB.   If something did change it would have meant someone manually got on and did something.

    What could I run to say "is database A the same as database B" if that was a runner?

    Also is there the principle of flashback points in a replicated database in SQL Server?

     

  • January 28, 2020 at 11:48 am

    #3720864

    pani wrote:

    Thanks guys.   I'll check out mirroring as I have enterprise.

    how about having a check to say   "Is the subscriber database the same as the publishing database"

    I might be able to live with that and if something got in that shouldnt I can rebuild from a snapshot (And investigate how it changed) as this will be for a disaster recovery site where no-one should really have access and db is only a couple GB.   If something did change it would have meant someone manually got on and did something.

    What could I run to say "is database A the same as database B" if that was a runner?

    Also is there the principle of flashback points in a replicated database in SQL Server?

    flashback points are called Snapshots in MSSQL - you can use them on any active database

    MVDBA

  • January 28, 2020 at 11:56 am

    #3720865

    you can have many snapshots of a database , they work in the same way as oracle - if you change data in the live data then the old data gets copied to the snapshot - you can query the snapshot and see the "flashback" point in time data... but be carefull not to leave them on too long. especially if you have big data churning jobs which update a lot of rows

    beware with mirroring - the mirror is realtime, but also read only... you have to put a snapshot on the target database to read it - you can also use a witness server to automatically flip over and make it live ( read the MS documentation first)

    as for comparing - not necessary with mirroring or availability groups - but replication can go a bit funny

    have a look at Redgate SQL data compare (part of SQL toolbelt if your boss will let you spend £1500) - it can compare data tables and come up with scripts to sync the 2 systems

     

     

    MVDBA

  • January 28, 2020 at 12:12 pm

    #3720870

    great stuff Mike,  thanks for the answers

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

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