Post reply

Add second replication

  • November 10, 2019 at 3:20 pm

    #3696549

    Hi all

    We have a 2TB SQL2012 database on prem that is being push replicated to a reporting server. This reporting subscriber has additional views and tables that the publisher doesn't have.

    I now need to make a second copy of the 2TB database, also for reporting (PowerBI) but in Azure SQL server 2016. This second subscriber has to be exactly the same as the first on-prem subscriber.

    I do not want to disturb the current replication.

    How can I achieve this.

    The 2TB prod DB has once a week Full Backup, Daily Differentials and half-hourly transaction log backups. Our link to Azure is shared with the company internet connection and the upload speed for the backup of the 2TB database is taking 21 hours!!!

    Your advise hints and tips will be greatly appreciated.

  • November 10, 2019 at 7:13 pm

    #3696570

    The basic process is simple, you just need ta add the Azure server as a subscriber to the existing Publication, and use a backup to establish it. Te actual step by step may b e a bit more involved.

    Given 2TB and 21 hours to copy I'm assuming your compressing the backup. You need to look at ways to improve your copy time, even splitting your backup and copying multiple files may improve the time a bit. It has the advantage that if there is a copy failure only a subset of the data needs to be copied again.

    You'll also need to apply transaction logs at least on the new Subscriber, but depending on timing you may want to restore one or more differentials along the way.

    Once the DB is in place add the views that are in the current Subscriber.

    Conceptually this is not a difficult process, the issue is with getting that size DB up to Azure, and the fact that the transaction log isn't going to be released until synchronization is working to the new Subscriber, so your log may blow out. You may need to research and see what Microsoft have in place to improve data copy rates into Azure.

     

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • November 10, 2019 at 10:19 pm

    #3696580

    Hi Leo

    Thanks so much for the quick reply.

    1. Will this work break the current replication in place to the on-prem reporting DB?

    2. Because the second subscription is an exact copy of the first subscription, in terms of structure and objects, can I use back up of the first subscription, instead of the main publisher?

    3, Could I setup an availability group between first subscription (SQL 2012) and the new DB in Azure (SQL2016)?

    Many thanks

     

  • November 11, 2019 at 7:38 pm

    #3696845

    1. It shouldn't break the current subscription HOWEVER... If the Publication has not been configured to allow Synchronize from Backup, you will need to set this option. It may, but shouldn't break anything. The trick of course is to set up a small system and check. Given the size of the databases involved I'd be surprised if the current Subscriber wasn't set up from backup.
    2. No, you can't use a backup from the first Subscriber. This has to do with how SQL tells that the databases are in sync when you finally add the Subscriber, if the LSNS don't match then it won't work.
    3. That's a really interesting question. I'm not aware of any reason why you can't although I've never tried it. Articles on the Net indicate it can be done. There are two issues in your case.

      1. You can't fail over to the 2016 server and then fail back again because the Primary is only 2012. Probably not an issue if this is just for reporting but you need to note it and ensure the fail over is MANUAL only.
      2. The Subscription Sync will fail if you fail over the AG. If you wanted to be able to do a fail over you would have to set the Listener name as the Subscriber Server, which would mean rebuilding the Subscription from scratch.

    Cheers

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • November 11, 2019 at 9:51 pm

    #3696880

    Hi Leo

    Once again thank you for your response.

    1. I will try and check if allow synchronise from backup option has been set. The current subscription WAS setup from a backup of the publisher.

    3. The Publisher has a mirror that is in a WFC at our DR centre. So none of the subscribers will require a failover. Luckily. Since failover is not a requirement, I am inclined to attempt an AG between the first subscriber and the new (second) copy/replica/subscriber. These two servers (2012 on prem and the 2016 in Azure) are not clustered. What do you think?

    Many thanks, Hirantha

     

     

  • November 11, 2019 at 10:18 pm

    #3696886

    1. If the original Subscription was setup with a Backup, then the option must be set, but I'd check any way
    2. Since one of the nodes in your proposed AG configuration is 2012 you will have to set the two servers up as a cluster. You can do some reading on this, but my understanding is from SQL 2016 and Windows 2016 you can have AGs without WFCS. It's not something I've tested.

    You can read this SSC post https://www.sqlservercentral.com/forums/topic/sql-server-2017-availability-group-without-windows-server-failover-clustering

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • November 15, 2019 at 10:55 pm

    #3698223

    HI Leo

    Yes, I can confirm all that now, sadly.

    If I create a snapshot on the same 2012 publisher, will it affect the current replication that was set up using backup and restore.

    What benefits can you think for doing a backup and restore replication as opposed to a snapshot.

     

  • November 20, 2019 at 2:15 am

    #3699065

    No, a new Subscription shouldn't affect the existing subscription.

    Why use a backup and restore rather than Snapshot? Because it's going to take a looooooooong time to snapshot a 2TB database and use a lot of space. Using SNAPSHOT to set up the Subscription actually does a BCP out of every table, and then does a BCP in to the Subscriber. For one client we gave up after about 24 hours, and we now do Backup and Restore for them. We can compress the backup to get it to go through faster and copy between servers faster. Split the backup over multiple files gives more performance improvements.

     

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

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

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