| Author |
Topic |
|
agiotti
Starting Member
37 Posts |
 Posted - 2012-01-11 : 13:53:06
|
| Publisher: Transactional Replication (Push one-way) Windows 2008 R2 Enterprise SQL Server 2008 R2 Standard Clustered Configuration.Subscriber: Windows 2008 R2 Enterprise SQL Server 2008 R2 Enterprise Cluster ConfigurationDR Server: Windows 2008 R2 Enterprise SQL Server 2008 R2 Enterprise Stand-alone serverPublisher and Subscriber communicate over a dedicated circuit.Note: Subscriber is an external client.Problem Statement:External client would like to perform DR tests. They do not have a real-time DR site, they utilized a build and restore to a new server scenario.1. DR server would have a new name. a. Note: They would not build the DR server using the production server name or SQL Server name.2. DR server would have a new IP address3. DR Server would utilize the same dedicated circuit.4. How can we configure the Publisher to begin pushing to this newer server?Thought: Create DR push subscription (at the publisher) and leave it disabled until required.Another question.1. If the replication is broken, (server failure at the client site), transactions will continue to be logged within the distribution database, correct?2. If so, once we bring up the DR server will these queued transactions sync up to this DR database? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
agiotti
Starting Member
37 Posts |
Posted - 2012-01-11 : 14:05:55
|
| I may be off base, however, it is my understanding that transactional replication is controlled by the log reader agent. For each publication there are SQL Server agents, can we disable that particular agent.Cannot use database mirroring because the DR server is not on the air. It is built upon a disaster at their DR site. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-01-11 : 14:09:11
|
| You can't do that as the distribution database would be HUGE needing to store all transactions in there forever. I think the default for retention is 48 or 72 hours. We set ours down to 24 hours for performance reasons. Yours would have to setup so that it doesn't purge anything and that would be impossible. Plus your distribution server would have to be at the DR site.You should instead consider shipping the tlogs to a server at the DR site. So you'd need a location to ship them to.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
agiotti
Starting Member
37 Posts |
Posted - 2012-01-11 : 14:25:26
|
| I was informed that they have the retention set to 10 days. Let me set the picture for you. We have a dedicated line to the external client, Therefore Publication and Distribution server (same), can access their production and DR segments over this circuit. With that said, would we be able to leave that agent disabled and would it still require the distribution server at the DR site? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-01-11 : 14:37:52
|
| I don't see how this is a valid DR configuration. If you lose the publisher server, you lose everything as your distribution database is there too.How big of a database are we talking about? Have you tested setting up replication without a subscriber and having the retention be 10 days? Our distribution database would be HUGE if we used your setup, plus performance would be horrific.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
agiotti
Starting Member
37 Posts |
Posted - 2012-01-11 : 14:54:54
|
| These are all valid questions and concerns. At this time they are only testing DR at the client, not at the primary site. Therefore, the publisher and distributor are not affected only the subscriber. They have talked about taking a backup, restoring to the DR server and then reset the replication to this DR server. Does this make sense? Or are they way off base? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
agiotti
Starting Member
37 Posts |
Posted - 2012-01-11 : 15:51:49
|
| Primary Site:Server 1 - publisher/distributorClient Site:Server 2 - subscriber (this is the environment we simulate losing in a DR scenario)DR Site:Server 3 - subscriber Note: Server 2 and Server 3 are at different data centers however, we can reach both from Server 1. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-01-11 : 16:01:29
|
| Thanks! That definitely clears things up for me.You wouldn't even need to disable anything. You could build server3 using server1 directly. Just start with a snapshot (or similar) and off you go. Depending how long it takes to build server3, that's how long you'll want to set the retention for the distribution database. We have a 400GB database that takes about 6 hours to build if we need to start from a snapshot on the subscriber. That 6 hours includes the snapshot, time to apply the bulk files, and the time to get caught up with new transactions. Now if your network is slow or whatever and takes 2 days to do this, then you'd want 3-4 days of retention in the distribution database. We have ours set to 24 hours due to performance reasons as the size of the distribution database does affect performance of replication.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jestadt
Starting Member
1 Post |
Posted - 2012-01-12 : 11:11:25
|
| Thanks for your assistance thus far ... I am a colleague of AGiotti and am hoping to dive a little deeper and gain further understanding of replication.In this DR test, one scenario to be tested is the communication breaking between the Primary Site and Client Site.As I understand, the Distribution database at the Primary Site will begin queuing transactions (for up to 10 days in our config) once the link between the Primary Site and Client Site is broken. The next steps would be to:1. Take a backup of the Client Site2. Restore backup from Client Site To DR Site (network communication not impacted from Client Site to DR Site)3. Implement subscription to Primary Site from DR SiteAfter the subscription is implemented, LSNs will be used to determine what transactions from the Distribution database at the Primary Site need to be sent to the DR Site to “sync” the Primary Site and DR Site.Is this correct? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Remote Replication DR Scenario