Ygthkb

A Wacky, Wacky Chessboard (That Makes No Sense)

Do my Windows system binaries contain sensitive information?

Table enclosed in curly brackets

When does coming up with an idea constitute sufficient contribution for authorship?

What happens if a wizard reaches level 20 but has no 3rd-level spells that they can use with the Signature Spells feature?

What is Crew Dragon approaching in this picture?

If I delete my router's history can my ISP still provide it to my parents?

What is the wife of a henpecked husband called?

Is Draco canonically good-looking?

How to push a box with physics engine by another object?

Why didn't Eru and/or the Valar intervene when Sauron corrupted Númenor?

Why do members of Congress in committee hearings ask witnesses the same question multiple times?

Criticizing long fiction. How is it different from short?

Why do neural networks need so many training examples to perform?

If all harmonics are generated by plucking, how does a guitar string produce a pure frequency sound?

Am I a Rude Number?

Is my plan for fixing my water heater leak bad?

Metadata API deployments are failing in Spring '19

How can I improve my fireworks photography?

Can a hotel cancel a confirmed reservation?

raspberry pi change directory (cd) command not working with USB drive

Could quantum mechanics be necessary to analyze some biology scenarios?

On what did Lego base the appearance of the new Hogwarts minifigs?

What is the meaning of "pick up" in this sentence?

Can I re-replicate a transactional publication? Should I?

SQL Backups - Full vs Differential vs Log ShippingSQL Server replication for off site copyTransactional Replication - Snapshot metricsTransactional Replication: can create 52 publication, but creating 53rd leads to problemsCreate transactional publication error in SQL Server 2014Transactional publication and database mirroring considerationsMultiple transactional publication: pros and consMigrate replication from SQL Server 2008 R2 to SQL Server 2016 - Old to new hardwareReplicating on-premise SQL Server instance with hundreds of databases to an Azure SQL Managed InstanceSQL Transactional Replication - some tables frozen, but others working fine

0

We use a cloud-based SAAS provider as one of our line-of-business applications. An on-site SQL Server 2008 R2 instance in our DMZ is a transactional replication subscriber to a number of publications on the provider's SQL Server.

We are interested in building a reporting server containing much if not all of the data currently being replicated from our provider. What is the most appropriate mechanism, if any, to support this objective if we wish to accomplish it in-house (as opposed to the possibility of adding the reporting server as a second subscriber to the provider's publications)?

Conceptually, the most appealing prospect would be to establish our current subscriber as a publisher in its own right and re-replicate the data to the reporting server, but that seems fraught with risk -- our publications total more than 250GB of data, and any issue necessitating the re-initialization of every subscription to our provider's publications would result in a 24-to-36-hour outage. The biggest upside to "re-replicating" would be the ability to optimize indexes on the reporting server, which to my knowledge would not be practical or even possible using log shipping or database mirroring.

Is there a way to accomplish this in-house using the standard SQL Server stack? If so, what are the pros and cons? Or would our best option be to look into additional subscriptions to the SAAS publications?

sql-server sql-server-2008-r2 transactional-replication

share|improve this question

edited Mar 2 '15 at 20:59

LowlyDBA

7,07252542

asked Mar 2 '15 at 20:29

Bob CBob C

5915

bumped to the homepage by Community♦ 9 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've not ever seen a re-replication before. Do you have the option of setting up a distributor and pointing the publications there? If so, you can set up as many subscriptions to the data as you would like with out impacting your publishers.
  
  – Steve Mangiameli
  Mar 2 '15 at 21:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've never seen such an arrangement before, either. The distributor is currently on the provider's network; I don't believe they would support relocating it to our network, if I've understood your follow-up correctly.
  
  – Bob C
  Mar 2 '15 at 22:34
  

  
  
  
  

add a comment | 

0

We use a cloud-based SAAS provider as one of our line-of-business applications. An on-site SQL Server 2008 R2 instance in our DMZ is a transactional replication subscriber to a number of publications on the provider's SQL Server.

We are interested in building a reporting server containing much if not all of the data currently being replicated from our provider. What is the most appropriate mechanism, if any, to support this objective if we wish to accomplish it in-house (as opposed to the possibility of adding the reporting server as a second subscriber to the provider's publications)?

Conceptually, the most appealing prospect would be to establish our current subscriber as a publisher in its own right and re-replicate the data to the reporting server, but that seems fraught with risk -- our publications total more than 250GB of data, and any issue necessitating the re-initialization of every subscription to our provider's publications would result in a 24-to-36-hour outage. The biggest upside to "re-replicating" would be the ability to optimize indexes on the reporting server, which to my knowledge would not be practical or even possible using log shipping or database mirroring.

Is there a way to accomplish this in-house using the standard SQL Server stack? If so, what are the pros and cons? Or would our best option be to look into additional subscriptions to the SAAS publications?

sql-server sql-server-2008-r2 transactional-replication

share|improve this question

edited Mar 2 '15 at 20:59

LowlyDBA

7,07252542

asked Mar 2 '15 at 20:29

Bob CBob C

5915

bumped to the homepage by Community♦ 9 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've not ever seen a re-replication before. Do you have the option of setting up a distributor and pointing the publications there? If so, you can set up as many subscriptions to the data as you would like with out impacting your publishers.
  
  – Steve Mangiameli
  Mar 2 '15 at 21:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've never seen such an arrangement before, either. The distributor is currently on the provider's network; I don't believe they would support relocating it to our network, if I've understood your follow-up correctly.
  
  – Bob C
  Mar 2 '15 at 22:34
  

  
  
  
  

add a comment | 

We use a cloud-based SAAS provider as one of our line-of-business applications. An on-site SQL Server 2008 R2 instance in our DMZ is a transactional replication subscriber to a number of publications on the provider's SQL Server.

We are interested in building a reporting server containing much if not all of the data currently being replicated from our provider. What is the most appropriate mechanism, if any, to support this objective if we wish to accomplish it in-house (as opposed to the possibility of adding the reporting server as a second subscriber to the provider's publications)?

Conceptually, the most appealing prospect would be to establish our current subscriber as a publisher in its own right and re-replicate the data to the reporting server, but that seems fraught with risk -- our publications total more than 250GB of data, and any issue necessitating the re-initialization of every subscription to our provider's publications would result in a 24-to-36-hour outage. The biggest upside to "re-replicating" would be the ability to optimize indexes on the reporting server, which to my knowledge would not be practical or even possible using log shipping or database mirroring.

Is there a way to accomplish this in-house using the standard SQL Server stack? If so, what are the pros and cons? Or would our best option be to look into additional subscriptions to the SAAS publications?

sql-server sql-server-2008-r2 transactional-replication

share|improve this question

edited Mar 2 '15 at 20:59

LowlyDBA

7,07252542

asked Mar 2 '15 at 20:29

Bob CBob C

5915

share|improve this question

edited Mar 2 '15 at 20:59

LowlyDBA

7,07252542

asked Mar 2 '15 at 20:29

Bob CBob C

5915

share|improve this question

edited Mar 2 '15 at 20:59

LowlyDBA

7,07252542

asked Mar 2 '15 at 20:29

Bob CBob C

5915

edited Mar 2 '15 at 20:59

LowlyDBA

7,07252542

edited Mar 2 '15 at 20:59

LowlyDBA

7,07252542

asked Mar 2 '15 at 20:29

Bob CBob C

5915

asked Mar 2 '15 at 20:29

Bob CBob C

5915

1 Answer
1

active

oldest

votes

0

This is possible. I have seen it in production, enterprise level.

Cons: The subscriber has to be initialized and completely caught up with undistributed transactions before it can be set it up as a publisher.
Initializing the second subscribers must be done via backup/restore.
The re-distributor can block the first distributor if you create a snapshot and the articles are set to drop and recreate.
Any latency problems with the first replication trickle down to the re-distributed replication.
Make sure you have plenty of disk space for transaction log growth should you need it.
Make sure you have plenty of disk space for distribution growth should you need it.

share|improve this answer

edited Mar 3 '15 at 15:46

answered Mar 3 '15 at 8:18

stacylaraystacylaray

2,211717

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, @stacylaray. What happens if you try to set up the subscriber as a publisher before it has completely caught up with undistributed transactions? Is the attempt prevented, or does it fail gracefully?
  
  – Bob C
  Mar 3 '15 at 14:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, Bob, I corrected my answer. It won't fail, you just don't want start off already behind.
  
  – stacylaray
  Mar 3 '15 at 15:49
  

  
  
  
  

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%2f94214%2fcan-i-re-replicate-a-transactional-publication-should-i%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

This is possible. I have seen it in production, enterprise level.

Cons: The subscriber has to be initialized and completely caught up with undistributed transactions before it can be set it up as a publisher.
Initializing the second subscribers must be done via backup/restore.
The re-distributor can block the first distributor if you create a snapshot and the articles are set to drop and recreate.
Any latency problems with the first replication trickle down to the re-distributed replication.
Make sure you have plenty of disk space for transaction log growth should you need it.
Make sure you have plenty of disk space for distribution growth should you need it.

share|improve this answer

edited Mar 3 '15 at 15:46

answered Mar 3 '15 at 8:18

stacylaraystacylaray

2,211717

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, @stacylaray. What happens if you try to set up the subscriber as a publisher before it has completely caught up with undistributed transactions? Is the attempt prevented, or does it fail gracefully?
  
  – Bob C
  Mar 3 '15 at 14:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, Bob, I corrected my answer. It won't fail, you just don't want start off already behind.
  
  – stacylaray
  Mar 3 '15 at 15:49
  

  
  
  
  

add a comment | 

0

This is possible. I have seen it in production, enterprise level.

Cons: The subscriber has to be initialized and completely caught up with undistributed transactions before it can be set it up as a publisher.
Initializing the second subscribers must be done via backup/restore.
The re-distributor can block the first distributor if you create a snapshot and the articles are set to drop and recreate.
Any latency problems with the first replication trickle down to the re-distributed replication.
Make sure you have plenty of disk space for transaction log growth should you need it.
Make sure you have plenty of disk space for distribution growth should you need it.

share|improve this answer

edited Mar 3 '15 at 15:46

answered Mar 3 '15 at 8:18

stacylaraystacylaray

2,211717

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, @stacylaray. What happens if you try to set up the subscriber as a publisher before it has completely caught up with undistributed transactions? Is the attempt prevented, or does it fail gracefully?
  
  – Bob C
  Mar 3 '15 at 14:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, Bob, I corrected my answer. It won't fail, you just don't want start off already behind.
  
  – stacylaray
  Mar 3 '15 at 15:49
  

  
  
  
  

add a comment | 

This is possible. I have seen it in production, enterprise level.

Cons: The subscriber has to be initialized and completely caught up with undistributed transactions before it can be set it up as a publisher.
Initializing the second subscribers must be done via backup/restore.
The re-distributor can block the first distributor if you create a snapshot and the articles are set to drop and recreate.
Any latency problems with the first replication trickle down to the re-distributed replication.
Make sure you have plenty of disk space for transaction log growth should you need it.
Make sure you have plenty of disk space for distribution growth should you need it.

share|improve this answer

edited Mar 3 '15 at 15:46

answered Mar 3 '15 at 8:18

stacylaraystacylaray

2,211717

share|improve this answer

edited Mar 3 '15 at 15:46

answered Mar 3 '15 at 8:18

stacylaraystacylaray

2,211717

answered Mar 3 '15 at 8:18

stacylaraystacylaray

2,211717

answered Mar 3 '15 at 8:18

stacylaraystacylaray

2,211717