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.
RossaminoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Aaron TomoskyDirector of Solutions ConsultingCommented:
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.
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.
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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).
 
Luan JubicaProject ManagerCommented:
couple of local databases

are these local dbs also SQL Server...is the amazon machine using the same version...what version is it?
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.


Luan JubicaProject ManagerCommented:
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.
Shalom CarmelCTOCommented:
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.
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,
RossaminoAuthor Commented:
We've decided to split the DB into two parts and to use sp_AddLinkedServer to have them communicate.

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
RossaminoAuthor Commented:
Easiest thing to do is to link them.
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.