1

as we are in the process of expanding of businees, now we are stepping a ahead to add few more datacenter, now we have a requirement where we need to sync the production database with other datacenter servers, with few requirements. Here are the present server setup configuration as follows ( 2 servers)

  • Server : Microsoft Windows Server 2008 R2.
  • .net Framework : Microsoft .net framework 3.5
  • IIS 7.5

Scenarios

  • How do we sync the production database to other server. is there any automation, replication method to perform the action, i.e whenever one record or some transaction happen it should sync to other server. i am wondering how people achieve efficiently.

  • Can we replicate or sync only table's which we mention, i.e in our DB Schema, there are some table, which we can ignore the syncing operation.

  • Is there any suggestion to improve the syncing operation or how other people deal with this scenario? is there any work arounds ? i heard of mirroring , but i don't know how to implement in this scenario.

    i apperciate anybody guide us to build a successful story..

Community
  • 1
Ravi Gadag
  • 65
  • 8
  • I'm not 100% clear from your post are you looking for redundancy or improved performance? – chunkyb2002 Jan 15 '12 at 22:44
  • replication services, i want to replicate the DB across the geographical locations. performance also matter – Ravi Gadag Jan 16 '12 at 02:06
  • Looks like information on this thread over at the DBA Stack Exchange might come in handy http://dba.stackexchange.com/questions/7451/sql-server-load-balancing – chunkyb2002 Jan 16 '12 at 14:30
  • As with any Disaster recovery scenario, you need to look at your recovery time objective (how long to get it working again) and recovery point objective (how much data can I afford to lose). Do a risk assessment, and figure out what technology you can afford. MSSQL has several options for replicating databases, including clustering, log shipping, and mirroring. – JakeRobinson Jan 20 '12 at 05:40

5 Answers5

2

you can do replication via subscription, mirroring, or log shipping (or in many cases multiple types of replication- eg mirroring and subscriptions for availability and partitioning). The question is what is the objective behind the replication. Additionally why aren't you migrating stuff like this to amazon, azure, or any number of cloud based solutions where all of these types of issues are solved, as well as issues you may not have thought of (backup, geographical load balancing- just off the top of my head)

For my money the cloud is where you start looking for answers to geographical distribution solutions.

Jim B
  • 24,081
  • 4
  • 36
  • 60
1

How we deal with this scenario is that we use Peer-To-Peer (PTP) replication. This allows us to not only sync databases across two separate data centers but we also have the flexibility to only sync the tables that we care about aka user data and not logging data.

Performance wise PTP synchronization is relatively instant for us, which this will obvious depend on the link between data center and how much traffic there is between sites. PTP also gives us the ability to have our server act independently of one another if needed.

One of the biggest gotchas though from experience is setting up the primary keys appropriately. If you use guid's as your primary key then there is no issue but incrementing identity columns become tricky as each database acts independently of one another. There fore you need to ensure that a key generated by the identity column on server 1 will not be generated on server 2 other wise the replication will encounter an error because you cannot insert that new record due to primary key violation.

This can be solved by either setting the identity seed for each table to a different value. For example server 1 will have the identity seed start at 1 and server 2 will have the identity seed start at 1,000,000. The other option would be to slightly stagger the identity seed and increment by the number of servers in your PTP replication scheme. So in the 2 server example, server 1 will start at seed 1 and server 2 will start at seed 2 with both incrementing by a value of 2.

Ian Chamberland
  • 680
  • 4
  • 8
  • is there any tool ? or some kind of tutorial or information where we can learn it, or will think to hire. as i need the information to put up in meeting – Ravi Gadag Jan 19 '12 at 04:30
  • this is a feature of SQL Server (Enterprise Edition only I believe) and you can find an over view of this feature here: http://technet.microsoft.com/en-us/library/ms151196.aspx – Ian Chamberland Jan 19 '12 at 04:34
1

For background, Google for "Brewer's CAP Theorem" - it basically says you can't have your replicated highly-avaliable transactionally-consistent cake and eat it.

There are lots of ways to do this, and I don't think you've told us enough to give prescriptive advice, but basically you can do this in the application, the database, or the VM level.

Adding replication to the app is often hard, so I'd avoid this if possible.

If you choose to do it in the database:

Transactional Replication is great for provising near-real time read-only copies of your live database. It permits you to select individual tables, sub-sets of all columns and even row filtering in the publication, resilience to connectivity issues, multiple subscribers and a nice management interface. There are some tricks you can use to enable limited write capability at the subscribers, but they're complicated.

Merge replication gives multi-master replicas, but has onerous schema requirements, has conflicts as part of its design and has scalability and reliability issues in me experience. Avoid.

Peer-to-peer replication. This I've never used, but it's built on Transactional replication, so should be OK, but the strong recommendation is to only allow updates on a single node to prevent

Database mirroring is only applicable in well-connected situations - i.e. same data centre. In the standard implementation if one member of the mirror fails, transactions stop for both. Synchronous transaction mirroring also slows down your production server. Avoid.

Your question doesn't say why you want replication - whether it's for performance or fault-tolerance or what, but I would recommend you consider whether e.g. a SQL Azure instance will meet your needs.

Good luck.

Alasdair C-S
  • 329
  • 1
  • 7
  • I think its also worth saying that too my knowlege, you can only mirror to one host, so if multiple datacenter replication is required, mirrioring woulnd't be an option, or you would have to mirror to one and use something else for the others. TL shipping is what i would reccomend if its for DR only. You're only sending the changes over, and the destination servers can run in a read only mode. I'm no SQL guru though. – Eric C. Singer Jan 21 '12 at 21:55
  • @EricC.Singer- typically you'd use both mirroring and publish replication to solve that issue. – Jim B Jan 22 '12 at 00:24
  • +1 for Brewers Cap therom :), though we will think of requirement bit deeper. :) – Ravi Gadag Jan 22 '12 at 13:34
  • @ravi, remember that brewers cap doesn't apply until there is a failure in connectivity (which all of the distributed systems I am aware of provide solutions for) – Jim B Jan 22 '12 at 15:06
0

Database replication doesn't really improve performance, since both servers still need to process and store all changes.

It may be worth looking into federated partitioning across geographically separate servers, but this is not a beginner subject.

A database mirror is not writable.

adaptr
  • 16,576
  • 23
  • 34
0

You might want to look into Red Gate Software's SQL Data Compare - which would allow you to synchronize the data between the databases.

They also have a SQL Compare product that just synchronizes the schema changes.

cpuguru
  • 401
  • 5
  • 14