Replication Configuration?

Our website and sql database are hosted away from our offices and is updated via the web by customers and by us in our offices. However due to our slow internet connection we would like to bring a replication server into our office and have the two databases talk to each other when a change occurs.(Customers updating the public database and us update a local one).

I have seen sites mention circular replication is troublesome and may not work, is this the case or is it just a matter of correct configuration?

Any advice, tips etc would be appreciated.

Thanks

John

I guess u need a Transactional replication configured between the two server.

Two scenario u can have

1. Central publishers and many(as many as u wish) read only subscriber

2. Other case in which subscribers also get updated Immediate updating subscribers.

I guess u have a scenario where in both the server will get updated. u can go for transactional replication with immediate updating subscribers.

I see no issues in this and ofcourse correct configuration is must for anything u do with SQL Server.

|||

Also i have one more quick question..how frequent is update on the both the server?

if both server are frequently updated u should consider using merge replication instead of transactional replication with immediate updating subscription as immediate updating subscription is useful for only occasional changes on the subscribers.

|||

I posted this on a different thread, but I think it applys to you as well.

Here is a direct quote from BOL http://msdn2.microsoft.com/en-us/library/ms152570.aspx

The following types of replication and replication options allow you to make changes at a Subscriber and have those changes flow to the Publisher:

Type of replication

Use when…

Merge replication

There are a large number of Subscribers.

Data is replicated to mobile users.

Replicated data is frequently updated at the Subscriber.

Data filtering is needed so that Subscribers receive different partitions of data.

Replicating structure only not data

I have a SQL 2005 database that I am using with a website. This basic website will be sold to other companies and ran on their servers with different URLs. Since, All of these databases will store different data, I'm not sure how I can make updates to original database and replicate those structure changes to the other DBs without changing the data also. Is there a way to automate the replication of structural DB changes without replicating the data along with it?

Thanks,

Kirk

You can enable @.replicate_dll at publication level, which will push the schema change to your subscriber. And you can set all the ins/upd/del commands to NONE for all of your articles, which will ignore all the data changes that took places on the publisher.

Gary

|||

Thanks Gary,

I am not new to SQL 2005 but I have never done any replication. Could you lead me to a good reference that would explain how to do some of the things that you wrote about? Any help is greatly appreciated.

Kirk

|||

I think the following would be a good start

General info on transactional replication

http://msdn2.microsoft.com/en-us/library/ms151176.aspx
http://msdn2.microsoft.com/en-us/library/ms151706.aspx

Creating create publication - by default @.replicate_ddl is on, so it will replcate ddl (aka - schema changes)

http://msdn2.microsoft.com/en-gb/library/ms147855.aspx
http://msdn2.microsoft.com/en-gb/library/ms188738.aspx

Create article - you want to set @.ins_cmd='NONE', @.upd_cmd='NONE', @.del_cmd='NONE'

http://msdn2.microsoft.com/en-gb/library/ms173857.aspx

Regards,

Gary

|||Thanks Gary, I will take a look at those links.