Sfrgttk

Where was Karl Mordo in Infinity War?

What is the wife of a henpecked husband called?

Is there a way to help users from having to clicking emails twice before logging into a new sandbox

What can I substitute for soda pop in a sweet pork recipe?

Meth dealer reference in Family Guy

Do authors have to be politically correct in article-writing?

Can I retract my name from an already published manuscript?

Can I become debt free or should I file for bankruptcy? How do I manage my debt and finances?

Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?

Do my Windows system binaries contain sensitive information?

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

LTSpice: When running a linear AC simulation, how to view the voltage ratio between two voltages?

Has the Isbell–Freyd criterion ever been used to check that a category is concretisable?

Where is this triangular-shaped space station from?

Why is c4 a better move in this position?

Auto Insert date into Notepad

Incompressible fluid definition

Wanted: 5.25 floppy to usb adapter

Eww, those bytes are gross

How to properly claim credit for peer review?

Which aircraft had such a luxurious-looking navigator's station?

Predict mars robot position

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

Obtaining a matrix of complex values from associations giving the real and imaginary parts of each element?

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♦ 12 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♦ 12 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