July 11, 2012 at 1:24 pm
We are in the planning process of moving our servers to multiple datacenters that are in the facility with the primary consumers. i.e. Operational Servers in the operational facilities, Reporting servers in the corporate offices, etc.
Today's configuration:
Data Center A (in Operational Facility 1)
- Operational Database
- Reporting Data Warehouse
- Analysis Services
- Distribution Database (separate SQL server)
- SSIS Server (hosting Distribution Database)
- Loads data from external sources to reporting warehouse and operational database
Data Center B (in Operational Facility 2)
- Disaster Recovery Site
Data Center C (in HQ)
- Finance and HR Servers
- SharePoint servers
Tomorrow's Configuration:
Data Center A (in Operational Facility 1)
- Disaster Recovery Site
Data Center B (in Operational Facility 2)
- Operational Database
- Distribution Database (separate SQL server)
Data Center C (in HQ)
- Finance and HR Servers
- SharePoint servers
- Reporting Data Warehouse
- Analysis Services
- SSIS Server
- Loads data from external sources to reporting warehouse (90%) and operational database (10%)
My question is where would the best location for the distribution server be? With the operational database, or with the subscriber (reporting data warehouse)?
I've already accepted that i will have to completely recreate the publication to use the new distribution server. I have not seen a good way to change the distribution server for an existing publication.
Greg Roberts
July 11, 2012 at 2:11 pm
In my experience you are better off having your distributor close to your published database. Depending on the speed of your connections between all sites it doesn't need to be in the same site as the published database. It just needs to be relatively quick.
Your snapshot folder on the other hand should be close to your subscribers. If you ever had more than one subscriber at the same site, you don't want them pulling multiple copies over the WAN if they have to (this applies to publication mods too). With three sites it'd make sense to have two publications so this is unique to the site.
I would also suggest you use a pull replication topology so the agents can be tuned to use larger packets, batches etc. It makes sense in my eyes to off load the work to.
July 13, 2012 at 1:51 pm
Thanks, that makes sense. I was already planning on changing to a pull model instead of push. It just makes sense to let the subscriber(s) get the data at their own pace.
Snapshots are a major challenge for me. The operational database is ~800 GB and we are replicating 90%+ of it. This was because of executive direction. I attempted to persuade them otherwise, but they were insistent. And at the end of the day, you have to follow the bosses direction.
The good news is we initialize the subscription from backup. So only new articles are snapshotted.
The bad news is, moving the subscriber to a different datacenter is going to increase my intialization time from 6 hours to 2 business days. We average 5 GB of transaction logs per day. I can't wait to see the impact that will have when i initialize remote subscribers.
Greg Roberts
July 13, 2012 at 2:04 pm
You could logship the existing subscriber and set up the new subscription without synchronizing.
No need to initialise from backup.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply