Failover strategy for SQL Server 2016 Standard EditionBackup VLDB replicated database in SQL Server?Restore databases from prod to test server with data over X daysLog shipping large DBs - what about the log?Replication To Enterprise & Standard Edition subscribersHigh availability SharePoint farm with restricted conditionsDoes SQL Server 2016 Standard Edition include replication?Always On/SQL Server Replication for DummiesLog shipping from SQL Server 2014 Enterprise db to SQL Server 2017 Standard with partitioning

Corroded Metal vs Magical Armor, should it melt it?

Are differences between uniformly distributed numbers uniformly distributed?

Is it okay for a ticket seller to grab a tip in the USA?

Is there a SQL/English like language that lets you define formulations given some data?

Why is tert-butoxide often used in elimination reactions when it is not necessary?

Why is statically linking glibc discouraged?

Can a bald person be a Nazir?

How to Check all AD userers for "blank" password?

How would you translate this? バタコチーズライス

Help, I cannot decide when to start the story

How can I find an old paper when the usual methods fail?

How can God warn people of the upcoming rapture without disrupting society?

Are employers legally allowed to pay employees in goods and services equal to or greater than the minimum wage?

How can I see if the data in a SQL Server table is page-compressed?

A continuous water "planet" ring around a star

Boss asked a co-worker to assault me

Running code generated in realtime in JavaScript with eval()

How would timezones work on a planet 100 times the size of our Earth

Do Reform Jews believe in a theistic God?

How do I call a 6-digit Australian phone number with a US-based mobile phone?

What is a "soap"?

Does one make a shehecheyanu on "used" jewelry?

Tempoverlustspiel

Will using a resistor in series with a LED to control its voltage increase the total energy expenditure?



Failover strategy for SQL Server 2016 Standard Edition


Backup VLDB replicated database in SQL Server?Restore databases from prod to test server with data over X daysLog shipping large DBs - what about the log?Replication To Enterprise & Standard Edition subscribersHigh availability SharePoint farm with restricted conditionsDoes SQL Server 2016 Standard Edition include replication?Always On/SQL Server Replication for DummiesLog shipping from SQL Server 2014 Enterprise db to SQL Server 2017 Standard with partitioning






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








1















I am using SQL Server 2016 Standard Edition.



I have two database servers, SQL01 and SQL02 with only one database, and I am utilizing transactional replication for bringing data from SQL01 to SQL02.
Users should be able to read from database in both server at any time and transactional replication allows that.



I am thinking of a failover strategy to failover to SQL02 and then back to SQL01 if there is such a situation (patches/maintenance).



I know that transactional replication is not a HA solution, but since I am using SQL Server 2016 Standard edition I have Log shipping/Database mirroring options only.



Current failover strategy for a patch/maintenance in SQL01:



  • Transactional Replication on from SQL01 to SQL02.

  • So point the application to SQL02. Now new data comes into SQL02.

  • To keep SQL01 in synch with SQL02, restore full backup of the database from SQL02 and keep the SQL01 initialized.

  • Implement Log Shipping from SQL02 to SQL01.

For me this looks like a solution I can use. Possible issues I see are:



  • My database is like 2TB, so a backup of that in SQL02 will require 1 hour and restore in SQL01 will require 3 hours. Maybe I can avoid that by using a previous-night full backup for SQL02 and restore that at an earlier time. Please comment if it is okay to do that.

  • Other issue is, if I have regular transactional log backups of a SQL02 database (not from log shipping), would that also be restored by log shipping?

Do you see any issues other than these?



Please add your suggestion(s).










share|improve this question


























  • @LowlyDBA : Basic Availability Groups won't allow read in the secondary server.

    – user9516827
    8 hours ago











  • @LowlyDBA:Added that to question.Thanks for pointing out.

    – user9516827
    8 hours ago











  • I would suggest adding another server to the mix and use either FCI or Basic Availability Groups for your actual HA, while keeping the transnational replication for reporting. With FCI/BAG the cold (emphasis on cold) spare is free. But anything you read from (such as your transnational replication recipient has to be licensed.

    – Jonathan Fite
    1 hour ago

















1















I am using SQL Server 2016 Standard Edition.



I have two database servers, SQL01 and SQL02 with only one database, and I am utilizing transactional replication for bringing data from SQL01 to SQL02.
Users should be able to read from database in both server at any time and transactional replication allows that.



I am thinking of a failover strategy to failover to SQL02 and then back to SQL01 if there is such a situation (patches/maintenance).



I know that transactional replication is not a HA solution, but since I am using SQL Server 2016 Standard edition I have Log shipping/Database mirroring options only.



Current failover strategy for a patch/maintenance in SQL01:



  • Transactional Replication on from SQL01 to SQL02.

  • So point the application to SQL02. Now new data comes into SQL02.

  • To keep SQL01 in synch with SQL02, restore full backup of the database from SQL02 and keep the SQL01 initialized.

  • Implement Log Shipping from SQL02 to SQL01.

For me this looks like a solution I can use. Possible issues I see are:



  • My database is like 2TB, so a backup of that in SQL02 will require 1 hour and restore in SQL01 will require 3 hours. Maybe I can avoid that by using a previous-night full backup for SQL02 and restore that at an earlier time. Please comment if it is okay to do that.

  • Other issue is, if I have regular transactional log backups of a SQL02 database (not from log shipping), would that also be restored by log shipping?

Do you see any issues other than these?



Please add your suggestion(s).










share|improve this question


























  • @LowlyDBA : Basic Availability Groups won't allow read in the secondary server.

    – user9516827
    8 hours ago











  • @LowlyDBA:Added that to question.Thanks for pointing out.

    – user9516827
    8 hours ago











  • I would suggest adding another server to the mix and use either FCI or Basic Availability Groups for your actual HA, while keeping the transnational replication for reporting. With FCI/BAG the cold (emphasis on cold) spare is free. But anything you read from (such as your transnational replication recipient has to be licensed.

    – Jonathan Fite
    1 hour ago













1












1








1


0






I am using SQL Server 2016 Standard Edition.



I have two database servers, SQL01 and SQL02 with only one database, and I am utilizing transactional replication for bringing data from SQL01 to SQL02.
Users should be able to read from database in both server at any time and transactional replication allows that.



I am thinking of a failover strategy to failover to SQL02 and then back to SQL01 if there is such a situation (patches/maintenance).



I know that transactional replication is not a HA solution, but since I am using SQL Server 2016 Standard edition I have Log shipping/Database mirroring options only.



Current failover strategy for a patch/maintenance in SQL01:



  • Transactional Replication on from SQL01 to SQL02.

  • So point the application to SQL02. Now new data comes into SQL02.

  • To keep SQL01 in synch with SQL02, restore full backup of the database from SQL02 and keep the SQL01 initialized.

  • Implement Log Shipping from SQL02 to SQL01.

For me this looks like a solution I can use. Possible issues I see are:



  • My database is like 2TB, so a backup of that in SQL02 will require 1 hour and restore in SQL01 will require 3 hours. Maybe I can avoid that by using a previous-night full backup for SQL02 and restore that at an earlier time. Please comment if it is okay to do that.

  • Other issue is, if I have regular transactional log backups of a SQL02 database (not from log shipping), would that also be restored by log shipping?

Do you see any issues other than these?



Please add your suggestion(s).










share|improve this question
















I am using SQL Server 2016 Standard Edition.



I have two database servers, SQL01 and SQL02 with only one database, and I am utilizing transactional replication for bringing data from SQL01 to SQL02.
Users should be able to read from database in both server at any time and transactional replication allows that.



I am thinking of a failover strategy to failover to SQL02 and then back to SQL01 if there is such a situation (patches/maintenance).



I know that transactional replication is not a HA solution, but since I am using SQL Server 2016 Standard edition I have Log shipping/Database mirroring options only.



Current failover strategy for a patch/maintenance in SQL01:



  • Transactional Replication on from SQL01 to SQL02.

  • So point the application to SQL02. Now new data comes into SQL02.

  • To keep SQL01 in synch with SQL02, restore full backup of the database from SQL02 and keep the SQL01 initialized.

  • Implement Log Shipping from SQL02 to SQL01.

For me this looks like a solution I can use. Possible issues I see are:



  • My database is like 2TB, so a backup of that in SQL02 will require 1 hour and restore in SQL01 will require 3 hours. Maybe I can avoid that by using a previous-night full backup for SQL02 and restore that at an earlier time. Please comment if it is okay to do that.

  • Other issue is, if I have regular transactional log backups of a SQL02 database (not from log shipping), would that also be restored by log shipping?

Do you see any issues other than these?



Please add your suggestion(s).







sql-server replication transactional-replication log-shipping failover






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 8 hours ago







user9516827

















asked 8 hours ago









user9516827user9516827

5531 gold badge2 silver badges12 bronze badges




5531 gold badge2 silver badges12 bronze badges















  • @LowlyDBA : Basic Availability Groups won't allow read in the secondary server.

    – user9516827
    8 hours ago











  • @LowlyDBA:Added that to question.Thanks for pointing out.

    – user9516827
    8 hours ago











  • I would suggest adding another server to the mix and use either FCI or Basic Availability Groups for your actual HA, while keeping the transnational replication for reporting. With FCI/BAG the cold (emphasis on cold) spare is free. But anything you read from (such as your transnational replication recipient has to be licensed.

    – Jonathan Fite
    1 hour ago

















  • @LowlyDBA : Basic Availability Groups won't allow read in the secondary server.

    – user9516827
    8 hours ago











  • @LowlyDBA:Added that to question.Thanks for pointing out.

    – user9516827
    8 hours ago











  • I would suggest adding another server to the mix and use either FCI or Basic Availability Groups for your actual HA, while keeping the transnational replication for reporting. With FCI/BAG the cold (emphasis on cold) spare is free. But anything you read from (such as your transnational replication recipient has to be licensed.

    – Jonathan Fite
    1 hour ago
















@LowlyDBA : Basic Availability Groups won't allow read in the secondary server.

– user9516827
8 hours ago





@LowlyDBA : Basic Availability Groups won't allow read in the secondary server.

– user9516827
8 hours ago













@LowlyDBA:Added that to question.Thanks for pointing out.

– user9516827
8 hours ago





@LowlyDBA:Added that to question.Thanks for pointing out.

– user9516827
8 hours ago













I would suggest adding another server to the mix and use either FCI or Basic Availability Groups for your actual HA, while keeping the transnational replication for reporting. With FCI/BAG the cold (emphasis on cold) spare is free. But anything you read from (such as your transnational replication recipient has to be licensed.

– Jonathan Fite
1 hour ago





I would suggest adding another server to the mix and use either FCI or Basic Availability Groups for your actual HA, while keeping the transnational replication for reporting. With FCI/BAG the cold (emphasis on cold) spare is free. But anything you read from (such as your transnational replication recipient has to be licensed.

– Jonathan Fite
1 hour ago










2 Answers
2






active

oldest

votes


















2














Separate from your Replication solution, could use a Failover Cluster Instance or Basic Availability Group for HA of your publisher.



Or, if you have a stomach for obscure replication solutions, you could implement Bidirectional Transactional Replication, which is similar to Peer-to-Peer replciation, but older and available on Standard Edition.






share|improve this answer
































    2














    Is there a reason, other than tradition, that you need SQL01 to return to being the "primary"? You could do just a single flip, and skip the confusing mess of short-term log shipping:



    1. Original state: Primary database on SQL01, set up to replicate all tables to SQL02.

    2. When you do maintenance on SQL02, replication will queue up changes in the distribution database while SQL02 is down, and catch up when it is back online.

    3. To do maintenance on SQL01, do the following:

      • Pause/stop the application

      • Break replication

      • Redirect application to SQL02

      • Do maintenance on SQL01

      • When SQL01 is back up, re-establish replication from SQL02 to SQL01, since SQL02 is now the "primary"


    Your final state is now: Primary database on SQL02, set up to replicate all tables to SQL01



    Now for a 2TB database, re-establishing replication (either direction) won't be fast or trivial; I'd recommend starting with a backup instead of trying to push a full snapshot.






    share|improve this answer

























    • For some reason my client wants SQL01 as primary and SQL02 and secondary.Currently i am using backup instead of snapshot in the replication process.The reason i was looking at log shipping is that ,i can setup/configure log shipping earlier itself.With replication,i should configure replication after redirecting the application and also i may have to manually synch some tables once replication is set up.

      – user9516827
      7 hours ago













    Your Answer








    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f245253%2ffailover-strategy-for-sql-server-2016-standard-edition%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    2














    Separate from your Replication solution, could use a Failover Cluster Instance or Basic Availability Group for HA of your publisher.



    Or, if you have a stomach for obscure replication solutions, you could implement Bidirectional Transactional Replication, which is similar to Peer-to-Peer replciation, but older and available on Standard Edition.






    share|improve this answer





























      2














      Separate from your Replication solution, could use a Failover Cluster Instance or Basic Availability Group for HA of your publisher.



      Or, if you have a stomach for obscure replication solutions, you could implement Bidirectional Transactional Replication, which is similar to Peer-to-Peer replciation, but older and available on Standard Edition.






      share|improve this answer



























        2












        2








        2







        Separate from your Replication solution, could use a Failover Cluster Instance or Basic Availability Group for HA of your publisher.



        Or, if you have a stomach for obscure replication solutions, you could implement Bidirectional Transactional Replication, which is similar to Peer-to-Peer replciation, but older and available on Standard Edition.






        share|improve this answer













        Separate from your Replication solution, could use a Failover Cluster Instance or Basic Availability Group for HA of your publisher.



        Or, if you have a stomach for obscure replication solutions, you could implement Bidirectional Transactional Replication, which is similar to Peer-to-Peer replciation, but older and available on Standard Edition.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered 7 hours ago









        David Browne - MicrosoftDavid Browne - Microsoft

        14.2k1 gold badge12 silver badges38 bronze badges




        14.2k1 gold badge12 silver badges38 bronze badges


























            2














            Is there a reason, other than tradition, that you need SQL01 to return to being the "primary"? You could do just a single flip, and skip the confusing mess of short-term log shipping:



            1. Original state: Primary database on SQL01, set up to replicate all tables to SQL02.

            2. When you do maintenance on SQL02, replication will queue up changes in the distribution database while SQL02 is down, and catch up when it is back online.

            3. To do maintenance on SQL01, do the following:

              • Pause/stop the application

              • Break replication

              • Redirect application to SQL02

              • Do maintenance on SQL01

              • When SQL01 is back up, re-establish replication from SQL02 to SQL01, since SQL02 is now the "primary"


            Your final state is now: Primary database on SQL02, set up to replicate all tables to SQL01



            Now for a 2TB database, re-establishing replication (either direction) won't be fast or trivial; I'd recommend starting with a backup instead of trying to push a full snapshot.






            share|improve this answer

























            • For some reason my client wants SQL01 as primary and SQL02 and secondary.Currently i am using backup instead of snapshot in the replication process.The reason i was looking at log shipping is that ,i can setup/configure log shipping earlier itself.With replication,i should configure replication after redirecting the application and also i may have to manually synch some tables once replication is set up.

              – user9516827
              7 hours ago















            2














            Is there a reason, other than tradition, that you need SQL01 to return to being the "primary"? You could do just a single flip, and skip the confusing mess of short-term log shipping:



            1. Original state: Primary database on SQL01, set up to replicate all tables to SQL02.

            2. When you do maintenance on SQL02, replication will queue up changes in the distribution database while SQL02 is down, and catch up when it is back online.

            3. To do maintenance on SQL01, do the following:

              • Pause/stop the application

              • Break replication

              • Redirect application to SQL02

              • Do maintenance on SQL01

              • When SQL01 is back up, re-establish replication from SQL02 to SQL01, since SQL02 is now the "primary"


            Your final state is now: Primary database on SQL02, set up to replicate all tables to SQL01



            Now for a 2TB database, re-establishing replication (either direction) won't be fast or trivial; I'd recommend starting with a backup instead of trying to push a full snapshot.






            share|improve this answer

























            • For some reason my client wants SQL01 as primary and SQL02 and secondary.Currently i am using backup instead of snapshot in the replication process.The reason i was looking at log shipping is that ,i can setup/configure log shipping earlier itself.With replication,i should configure replication after redirecting the application and also i may have to manually synch some tables once replication is set up.

              – user9516827
              7 hours ago













            2












            2








            2







            Is there a reason, other than tradition, that you need SQL01 to return to being the "primary"? You could do just a single flip, and skip the confusing mess of short-term log shipping:



            1. Original state: Primary database on SQL01, set up to replicate all tables to SQL02.

            2. When you do maintenance on SQL02, replication will queue up changes in the distribution database while SQL02 is down, and catch up when it is back online.

            3. To do maintenance on SQL01, do the following:

              • Pause/stop the application

              • Break replication

              • Redirect application to SQL02

              • Do maintenance on SQL01

              • When SQL01 is back up, re-establish replication from SQL02 to SQL01, since SQL02 is now the "primary"


            Your final state is now: Primary database on SQL02, set up to replicate all tables to SQL01



            Now for a 2TB database, re-establishing replication (either direction) won't be fast or trivial; I'd recommend starting with a backup instead of trying to push a full snapshot.






            share|improve this answer













            Is there a reason, other than tradition, that you need SQL01 to return to being the "primary"? You could do just a single flip, and skip the confusing mess of short-term log shipping:



            1. Original state: Primary database on SQL01, set up to replicate all tables to SQL02.

            2. When you do maintenance on SQL02, replication will queue up changes in the distribution database while SQL02 is down, and catch up when it is back online.

            3. To do maintenance on SQL01, do the following:

              • Pause/stop the application

              • Break replication

              • Redirect application to SQL02

              • Do maintenance on SQL01

              • When SQL01 is back up, re-establish replication from SQL02 to SQL01, since SQL02 is now the "primary"


            Your final state is now: Primary database on SQL02, set up to replicate all tables to SQL01



            Now for a 2TB database, re-establishing replication (either direction) won't be fast or trivial; I'd recommend starting with a backup instead of trying to push a full snapshot.







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered 7 hours ago









            BradCBradC

            6,7026 gold badges34 silver badges67 bronze badges




            6,7026 gold badges34 silver badges67 bronze badges















            • For some reason my client wants SQL01 as primary and SQL02 and secondary.Currently i am using backup instead of snapshot in the replication process.The reason i was looking at log shipping is that ,i can setup/configure log shipping earlier itself.With replication,i should configure replication after redirecting the application and also i may have to manually synch some tables once replication is set up.

              – user9516827
              7 hours ago

















            • For some reason my client wants SQL01 as primary and SQL02 and secondary.Currently i am using backup instead of snapshot in the replication process.The reason i was looking at log shipping is that ,i can setup/configure log shipping earlier itself.With replication,i should configure replication after redirecting the application and also i may have to manually synch some tables once replication is set up.

              – user9516827
              7 hours ago
















            For some reason my client wants SQL01 as primary and SQL02 and secondary.Currently i am using backup instead of snapshot in the replication process.The reason i was looking at log shipping is that ,i can setup/configure log shipping earlier itself.With replication,i should configure replication after redirecting the application and also i may have to manually synch some tables once replication is set up.

            – user9516827
            7 hours ago





            For some reason my client wants SQL01 as primary and SQL02 and secondary.Currently i am using backup instead of snapshot in the replication process.The reason i was looking at log shipping is that ,i can setup/configure log shipping earlier itself.With replication,i should configure replication after redirecting the application and also i may have to manually synch some tables once replication is set up.

            – user9516827
            7 hours ago

















            draft saved

            draft discarded
















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f245253%2ffailover-strategy-for-sql-server-2016-standard-edition%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            ParseJSON using SSJSUsing AMPscript with SSJS ActivitiesHow to resubscribe a user in Marketing cloud using SSJS?Pulling Subscriber Status from Lists using SSJSRetrieving Emails using SSJSProblem in updating DE using SSJSUsing SSJS to send single email in Marketing CloudError adding EmailSendDefinition using SSJS

            Кампала Садржај Географија Географија Историја Становништво Привреда Партнерски градови Референце Спољашње везе Мени за навигацију0°11′ СГШ; 32°20′ ИГД / 0.18° СГШ; 32.34° ИГД / 0.18; 32.340°11′ СГШ; 32°20′ ИГД / 0.18° СГШ; 32.34° ИГД / 0.18; 32.34МедијиПодациЗванични веб-сајту

            19. јануар Садржај Догађаји Рођења Смрти Празници и дани сећања Види још Референце Мени за навигацијуу