January 7, 2010 at 8:17 am
We are launching a second facility that needs access to the production database. The problem is the original facility does not have the WAN bandwidth available to support a remote facility conencting to the DB server. This is because of the location of the facility, services beyond T1 just are not available.
Executive management made the decision to install a clone of the systems from the 1st facility to the 2nd facility. The second facility is scheduled to come online 2/1/2010.
Unfortunately the application and DB structure do not support replication. There over 300 tables in the database and over 90% of them use identity columns.
Now for the challenge. The customer and order data must be sycronized between the facilities. This will be run on a regular schedule.
To avoid identity conflicts the new facility will have all of the identities seeded at 500,000,000. This was another executive decision that I have to live with. Unless we can come up with a more elegant solution.
The final requirement is the application CANNOT be altered. There are a lot of political reasons behind this that I will not go into. Just know that is set in stone.
So back to my original question. How can i syncronize the data without using replicaton? And how do i resolve conflicts when new accounts are created?
Thank you in advance,
Greg
Greg Roberts
January 7, 2010 at 9:49 am
Is data being written and modified on both servers?
If so, you could use SSIS to sync the data and set this is a job to run frequently, however you are going to run into problems in resolving conflicts when data is being updated on both servers at the same time, the rules for conflicts need to be decided by the business.
This will be a lot of work and can see it turning into a bit of a headache.
I would try and use Replicaiton if possible, you state that you have too many tables to be able to use replication. Do all the tables need to be replicated?
If you have a mix of data tables and static lookup tables then you could replicate the data tables that are being written to, and have the static tables not replicated and maintained via another process like SSIS or BCP. This would allow merge replication to handle the conflicts and as long as your specified an Id range in the article property then this will take care of conflicts in the auto Ids.
January 7, 2010 at 11:26 am
Yes, data is being written to both servers.
Lookup data will be easy to maintain with a SSIS job, it's the customer and order data that is the big issue. The order and account tables all use identity columns.
Greg Roberts
January 7, 2010 at 1:58 pm
is the way this OP is addressing what sounds like a similar problem an option for you?
http://www.sqlservercentral.com/Forums/Topic843729-361-1.aspx
---------------------------------------------------------------------
January 7, 2010 at 3:56 pm
It is similar, and I did think about it, but it will not work in our company.
We have a lab in TX with equipment connected to the SQL DB thru a app server. The users connect thru a seperate app server. Our volume prohibits using our MPLS. The new lab will be in NC and the business has already committed to cloning the servers.
I'm starting to look at the MS Sync Framework. but that will have a large development effort. More than I think I have time for.
Greg Roberts
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply