Rossamino
asked on
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.
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.
ASKER
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.
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.
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.
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).
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).
couple of local databases
are these local dbs also SQL Server...is the amazon machine using the same version...what version is it?
ASKER
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.
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.
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.
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.
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.
ASKER
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,
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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Easiest thing to do is to link them.
You can also setup clustering, but that stuff is over my head.