August 19, 2009 at 3:28 am
Hi, I am hope to get some advice from you with regards to setting up replication between our externally hosted website database and our intranet.
We have our business data in a SQL Server 2008 database, alongside the Web Server, hosted externally to the company LAN. Our intranet and CRM link into this database in order that people within the company can see and update live data. This presents a fairly major performance bottleneck, both in latency and as bandwidth is limited and shared with other external communications (e.g. internet browsing)
We are now moving into a new phase of development, where we need to make dramatic improvements on responsiveness of retrieval of contact information, so I am keen to investigate (and rule out if needs be) the possibility of setting up replication over the remote database to ensure we have a local, updatable and very nearly real-time copy of the external data.
Having previously used replication, and having read through the SQL documentation, it seems that the type of replication that would best suit us would be bi-directional transactional replication, with the publisher being the website database and a single subscription to a locally hosted copy (which would then be used by the intranet). It seems that immediately updating (via MSDTC) subscriptions would ensure the website is always up to date.
The most immediate problem I can see with using replication is that there are places in our intranet codebase that reply on some of the identity columns being sequential, and the way bi-directional replication manages identity ranges breaks this. Having said that, I would think the addition of timestamp columns on those tables and using this as the tidemark for table changes would be relatively easy to implement.
We have been warned that "Replication is fraught with lots of administration, performance, scalability, connectivity, and high availability problems" - to what extent is this true?
Thanks,
Mark.
August 19, 2009 at 7:10 am
For the type of replication you have in mind, the administration cost is higher. Set up is also difficult. But rest of them I do not agree. If you are connected to the Web Domain from internal network using VPN, it is safe and also easier. Replication does not use that much Bandwidth. Snapshot will take Bandwidth. With Windows datacenter 2008, the copying files is quite fast. I am not sure why Scalability is an issue at all since this will reduce/Distribute the load on the DB Server.
Just my 2 cents
-Roy
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply