• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

duplicate database locally from Amazon EC2

I am going to need to synchronize several tables in a couple of local databases with an SQL DB that Amazon will be hosting for us starting next week.  Is this difficult?  Will we require any additional software or is there some facility built into MS SQL already?

Performance, as always, is crucial so I can't be sending large chunks of the DB back and forth, just the changes.  Oh, and it needs to be bi-directional, of course.
0
Rossamino
Asked:
Rossamino
  • 5
  • 2
  • 2
  • +2
1 Solution
 
Aaron TomoskyTechnology ConsultantCommented:
With the full version of mssql server ( not the free express one) you can setup replication in one or both directions. Can I ask why both directions? That is the hardest part, usually this feature is used to take production data and bring it to a dev server to pla with.

You can also setup clustering, but that stuff is over my head.
0
 
RossaminoAuthor Commented:
We're posting the order-acquisition part of our web portal to Amazon which is the smaller part of what the company does.  The Amazon stuff needs to share pricing information with everything else which is located on-site.  Therefore, we need to download the new orders and upload any pricing information relatively instantaneously.
0
 
rajeevnandanmishraCommented:
Hi,
First of all i would say that the task you are going to do is quite simple, if you have planned your activities in advance.
Unfortunately, in MSSQL we don't have any such utility to synchronize the databases. You might need to use other tool like ApexSQL(http://apexsql.com/sql_tools_datadiff.aspx). I have used it a couple of times and is great in terms of synchronizing the databases.
Otherwise, you need to create scripts manually by looking at the data in both databases (could be a little ok, if there are only insert statements that you need to create. but if updates are also there, then ...)

if there is time, then try to do your activity on some test server, so you actually get to know that how much the actual activity will take.

Most probably it will the insert/update statements that you need to run on both the database to synchronize so you will be able to handle the chunk that you need to run.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
rajeevnandanmishraCommented:
Hi,
This is different type of synchronizing :-)
Yes as stated you can use replication. Again i will suggest to setup it with test machine first before going to actual server.
Clustering is totally different type of DR activity (goes through making the data available most of the time).
 
0
 
luaniCommented:
couple of local databases

are these local dbs also SQL Server...is the amazon machine using the same version...what version is it?
0
 
RossaminoAuthor Commented:
rajeevnandanmishra:  There will definitely be updates involved.  Does that mean that doing this via scripts is out?

luani:  Yes, the local DB's are also SQL server.  I'm not sure what version the Amazon server will be, but we've got an '08 and an '00 or '03 or something.


0
 
luaniCommented:
I think you can use SQLServer replication in this case....always if you don't to go with writing scripts to manage the whole thing. Also using the APEX Data comparator is OK.
0
 
shalomcCommented:
I wouldn't touch any replication or clustering tools. This is not a replication problem, but an application integration problem. Also, bi-directional data replication will create some serious security issues in your corporate firewall...

Reserve data replication for DR or for really simple scenarios.

The AWS server can write all of the incremental changes to a change log via the application code or with triggers.
Ditto for the on-site application server.

Use Biztalk or any other ESB to push pricing changes to the AWS instance, and to download orders from AWS.
True, you can write scripts instead, but an ESB adds levels of control that you will never have with scripts.
0
 
RossaminoAuthor Commented:
Shalom:
What's the easiest thing to do?  Our IT department consists of just me and I've got other responsibilities as well as I'm sure you can imagine.  CEO wants this in by Friday and we don't want to spend a lot of money.   We won't be generating all that much traffic with this.

Is Biztalk the way to go on this?  How difficult is it to implement?  What are the pro's and con's of using replication?  You've mentioned security issues through the firewall.  Are there ways to mitigate that risk? We don't keep any information that isn't already publicly available.  Is this not a simple scenario as far as DR is concerned?  What's the scope of DR supposed to be?

Thank you for your help on this,
0
 
RossaminoAuthor Commented:
We've decided to split the DB into two parts and to use sp_AddLinkedServer to have them communicate.
0
 
RossaminoAuthor Commented:
Easiest thing to do is to link them.
0

Featured Post

A Cyber Security RX to Protect Your Organization

Join us on December 13th for a webinar to learn how medical providers can defend against malware with a cyber security "Rx" that supports a healthy technology adoption plan for every healthcare organization.

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now