SQL Server Replication 101 RRS feed

  • Question

  • So we are trying to create an Enterprise Data Warehouse and Operational Data Store(ODS) Data Environment. Our idea is to spin-up a Replication Environment in order to isolate our EDW/ODS Environment and keep it away from our main Production Server where our main application data resides. Obviously, we DO NOT want to impact our normal business day operations.

    Our benchmarks tell us it's going to be a couple of days of wall time to get our replicated environment up and running based on complex queries which are aggregating the data from various resources for our EDW/ODS Environment.

    We are hoping that respondents can point us in the right direction in regards to the SQL Server steps we need to implement to accomplish this. As well, we want some Sync Points along the way if that's possible. If the load is going to take 3-4 days, we DO NOT want to re-start from the beginning...merely start where it left off or aborted for whatever it a data it an electrical outage...anything.

    Thanks for your review and am hopeful for a reply. 

    Monday, September 30, 2019 8:03 PM

All replies

  • Replication is resilient to transient errors, in other words it will pick right up where it left off.

    When you talk about data loads, what are you referring to? The initial sync? or subsequent data loads in your environment. Replication struggles to replicate large batch processes. If you are able to break them down into smaller chunks it will work better.

    Monday, September 30, 2019 8:47 PM
  • Not so much concerned with the data replication piece as we are once we get the replicated environment up and running and then running our EDW/ODS Data Load...And that potentially failing 3-4 days in...Like we'd like to create a Sync Point...just don't know how that is done. So if our data load aborts or abends for whatever reason, we can resume where it left off.

    And is there some reference Sites, YouTubes as to how to set-up Data Replication. 

    Again...we're not so much concerned about the Data Replication Environment as we are with syncing our data load once we spin-up a Data Replication Environment.

    Monday, September 30, 2019 9:29 PM