Mdthbs

Is it possible to grow new organs through exposure to radioactivity?

Does EU compensation apply to flights where the departure airport closes check-in counters during protests?

"Table of Astronomy's" depiction of the solar system models

Are there any lower-level means of travelling between planes of existence?

Doesn't the speed of light limit imply the same electron can be annihilated twice?

Modeling the uncertainty of the input parameters

What kind of liquid can be seen 'leaking' from the upper surface of the wing of a Boeing 737-800?

Crippling fear of hellfire &, damnation, please help?

Where can I find Rav Kook's writing of this?

How would armour (and combat) change if the fighter didn't need to actually wear it?

Transition to "Starvation Mode" in Survival Situations

Why does the cable resistance jump from a low value to high value at a particular frequency?

An array battle with odd secret powers

Did DOS zero out the BSS area when it loaded a program?

@ in the beginning of a formula

NRPE script for monitoring load average

"Mouth-breathing" as slang for stupidity

Chunk + Enumerate a list of digits

Escape Velocity - Won't the orbital path just become larger with higher initial velocity?

Graphs for which a calculus student can reasonably compute the arclength

Dogfights in outer space

Are those flyers about apartment purchase a scam?

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

Why is the result of ('b'+'a'+ + 'a' + 'a').toLowerCase() 'banana'?

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;
}

2

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).

sql-server replication transactional-replication log-shipping failover

share|improve this question

edited 23 hours ago

user9516827

asked yesterday

user9516827user9516827

55811 gold badge22 silver badges1212 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @LowlyDBA:Added that to question.Thanks for pointing out.
  
  – user9516827
  23 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
  17 hours ago
  

  
  
  
  

add a comment | 

2

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).

sql-server replication transactional-replication log-shipping failover

share|improve this question

edited 23 hours ago

user9516827

asked yesterday

user9516827user9516827

55811 gold badge22 silver badges1212 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @LowlyDBA:Added that to question.Thanks for pointing out.
  
  – user9516827
  23 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
  17 hours ago
  

  
  
  
  

add a comment | 

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

edited 23 hours ago

user9516827

asked yesterday

user9516827user9516827

55811 gold badge22 silver badges1212 bronze badges

share|improve this question

edited 23 hours ago

user9516827

asked yesterday

user9516827user9516827

55811 gold badge22 silver badges1212 bronze badges

share|improve this question

edited 23 hours ago

user9516827

asked yesterday

user9516827user9516827

55811 gold badge22 silver badges1212 bronze badges

asked yesterday

user9516827user9516827

55811 gold badge22 silver badges1212 bronze badges

asked yesterday

user9516827user9516827

55811 gold badge22 silver badges1212 bronze badges

2 Answers
2

active

oldest

votes

4

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

answered 23 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

14.3k11 gold badge1313 silver badges3838 bronze badges

add a comment | 

3

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

answered 23 hours ago

BradCBradC

6,71266 gold badges3434 silver badges6767 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
  23 hours ago
  

  
  
  
  

add a comment | 

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

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

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

Name

Email

Required, but never shown

4

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

answered 23 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

14.3k11 gold badge1313 silver badges3838 bronze badges

add a comment | 

4

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

answered 23 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

14.3k11 gold badge1313 silver badges3838 bronze badges

add a comment | 

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

answered 23 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

14.3k11 gold badge1313 silver badges3838 bronze badges

share|improve this answer

answered 23 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

14.3k11 gold badge1313 silver badges3838 bronze badges

answered 23 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

14.3k11 gold badge1313 silver badges3838 bronze badges

answered 23 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

14.3k11 gold badge1313 silver badges3838 bronze badges

3

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

answered 23 hours ago

BradCBradC

6,71266 gold badges3434 silver badges6767 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
  23 hours ago
  

  
  
  
  

add a comment | 

3

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

answered 23 hours ago

BradCBradC

6,71266 gold badges3434 silver badges6767 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
  23 hours ago
  

  
  
  
  

add a comment | 

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

answered 23 hours ago

BradCBradC

6,71266 gold badges3434 silver badges6767 bronze badges

share|improve this answer

answered 23 hours ago

BradCBradC

6,71266 gold badges3434 silver badges6767 bronze badges

answered 23 hours ago

BradCBradC

6,71266 gold badges3434 silver badges6767 bronze badges

answered 23 hours ago

BradCBradC

6,71266 gold badges3434 silver badges6767 bronze badges