December 17, 2007 at 5:54 am
Hello,
In the near future we will be moving a database in house to our office. Currently it is running in a hosting centre. Currently we have a complicated process to upload changes to the remote database, which our external clients also log on to and make changes.
Now, fast forwarding a bit. I was hoping to set up the database in our office, and put another copy of the database in a DMZ (mirrored). Orginally I thought that our clients only viewed the database, but it turns out that they can also make changes to it as well.
So I am now wondering to myself, I dont really want our clients coming into the internal network and accessing that database, I would rather they accessed one that is in a DMZ, but their changes would be reflected on ours and vice versa.
Would I have to set up third database that would distribute changes between the 2 databases? I would need to have near real time replication. As it would all be in the same building there will be no WAN for any of these changes to go across.
It will be SQL 2005 Enterprise (may end up getting standard, but I want to try for Enterprise) running on Server 2003.
Is anybody in a similar setup already? Could I get away with having standard edition at one end? Would love to hear your thoughts.
Kind regards,
D.
December 17, 2007 at 7:25 am
Hi,
I would think that Merge Replication is the way to go for you, because with Transactional and Snapshot replication, the subscriber cannot make changes to the data.
You can put the subscriber DB in the DMZ and allow it to talk to your in-house db via port TCP 1433 ( default instance ). If you are using SQL Server Browser you must also allow port UDP 1434.
See for firewall information
http://msdn2.microsoft.com/en-us/library/ms175043.aspx
For more information about Merge Replication see:
http://msdn2.microsoft.com/en-us/library/ms152746.aspx
The only version of SQL 2005 that cannot be a publisher is SQL Express.
Hope this is of use
Regards
Neil
December 18, 2007 at 12:41 pm
Subscriptions can be updated with some forms of Transactional replication (i.e. updateable subscriptions or peer-to-peer replication), but merge replication does give you better control over conflict resolution.
December 18, 2007 at 3:23 pm
peer-to-peer does also gives you what you ask. This setup is *very risky* in terms of security. You should try to de-couple the logic of the outside modifications with the "inside" database.
* Noel
December 19, 2007 at 2:41 am
Hello,
Firstly thanks for your replies, they are all helpful as usual. Noel, when you say 'De-Couple', how would this be achieved? I should point out that this will be my first venture into replication.
Regards,
D.
December 20, 2007 at 4:12 pm
On way to de-couple it is to actually queue requests on the outside that are read from the inside, applied inside and then replicated to the outside. What that means is that with this in mind the firewall permits traffic from the inside out but not the other way around. I hope it is understandable...
Cheers,
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply