SQL 2008 R2 Standard Replication

Hi,

We have SQL Server 2008 R2 Standard edition where we have mounted our database. This server machine is connected with a leased line and authorized user can access it using static IP. In this way user can access SQL server from outside. Now our company has open a new branch office and we would like to share entire database both ends with synchronization of entire transactions. We may have new branches in future.  

How could I configure TWO WAY REPLICATION to sync entire data?
Means all INSERT, UPDATE, DELETE etc process should reflect all databases.

Please advise.
stalliondzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
What is the bandwidth available to replication, you can do transactional or merge replication.

You need to assess which option is best for you.
http://technet.microsoft.com/en-us/library/ms152479%28v=sql.105%29.aspx

As discussed in the above link, multi-point-replication poses a security risk i.e. person at any of the branch offices gaining access to the local SQL server could access/alter data on the database that will be replicated to all.

If you can have each branch have their own data set that is combined at the Main HQ, that could be ..........centrally managed/aggregated and backed up data.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The only way to have two databases replicating to each other is to use the Merge Replication.
0
Ryan McCauleyEnterprise Analytics ManagerCommented:
You also have the option of doing peer-to-peer (sometimes called "Mesh") replication, where the nodes each replicate their transactions to every other node:

http://msdn.microsoft.com/en-us/library/ms151196.aspx

However, it's an enterprise feature, so licensing may be an issue here (you don't mention which edition you're currently using). Unless your connection is exceedingly slow, I'd recommend sending all of your transactions back to a single central server for writes, and you can have log shipping or some other kind of one-way replication out to each site for reporting if you'd like.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.