September 8, 2016 at 7:55 am
I think this should be a new forum.
I have a Project and would like advice from anyone who has experience in this area.
My company is expanding to a new building about 20 miles from the Main location. I'm bringing up 2 new SQL2016 servers one will be in each location. I will be limited to a bandwidth of 10 Mbps, (Considering batch replication every 30 minutes). The Primary application is program created with Clarion (a lot of RBAR). Data currently stored in SQL 2008R2. Recovery model is currently set to Simple, As I understand this was required due to how Clarion writes to the tables.
I'm in the process of reading tons of references, and would like some advice / answers.
Is 10Mbps enough?
What are the pros and cons that you see for each type of replication in a situation like this?
Thanks
September 8, 2016 at 8:41 am
Budd (9/8/2016)
What are the pros and cons that you see for each type of replication in a situation like this?Thanks
The pros are that SQL Server Replication is stable and reliable when set up correctly. The biggest "con" is that setting up and maintaining replication is not a simple task especially when done correctly. There's a lot of moving parts and it's easy to break. There's lots of gotchas like making sure you are/are not replicating triggers when need be. Snapshot replication is the easiest, transactional replication is notably more complicated and Merge is even more complicated. If you are looking to do "batch replication" every 30 minutes you can probably get away with snapshot replication.
If you have not implemented replication before I would suggest doing so first on a dev box and replicate between two local DBs. Play around with it and study up. Once you are comfortable with that set up replication between two test boxes. Learn how to use the Replication Monitor - it's a vital tool for managing replication. I would document the steps you take to create setup and modify your replication.
-- Itzik Ben-Gan 2001
September 8, 2016 at 10:30 am
Thank you Alan.
"Not Replicating Triggers" the trigger itself, or the results after a triggered event?
I have just completed the install of my 2 test servers SQL 2016 DEV, Will I need Enterprise ?
I also need to set them up with TDE. Any knowledge around using Replication with TDE ?
September 8, 2016 at 11:10 am
Budd (9/8/2016)
Thank you Alan."Not Replicating Triggers" the trigger itself, or the results after a triggered event?
I was talking about the triggers themselves. I had a situation a few years back where we had triggers doing a bunch of things on a publisher that we did not want happening anywhere else and, on one of my subscribers, we left the checkbox checked for triggers. This was painful to identify and extremely painful to fix. I mentioned that as an example of a replication "gotcha".
I have just completed the install of my 2 test servers SQL 2016 DEV, Will I need Enterprise ?
You can run replication on any version of SQL Server, the features differ per version. See the section on replication to better understand this here.
I also need to set them up with TDE. Any knowledge around using Replication with TDE ?
I haven't done too much with TDE and have never used it with Replication. Sorry.
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply