Hi everyone.
I'm wondering if any one can help me. I'm fairly new to DTS and made a
blindingly obvious mistake the other day.
I currently have to SQL 2k servers, one in our business premises and
the other is running on a shared web server. The web server database is
used to power our commerical e-commerce websites and currently we run
all inhouse software off of this server as well.
Our original plan was to set the shop server up and have a dts package
transforming the relevant data between the two servers to keep them up
to date. We were going to have all web records with an odd key and the
shop records creating even keys by setting the identity increment to 2.
It's only upon testing i spotted the obvious mistake, If the shop
server has for example record 52000 then the next record should be
52002 - but if we DTS across record 520001 from the webserver the next
record on the shop server would actually be 52003, and vice versa. This
now means our whole DTS plan has gone out the window. Does anyone have
any ideas, how we can run 2 servers at the same time and keep them both
up to date. We do have to use identity insert as customers are given
reference numbers and if we DTS a web record to the shop server without
using identity search then when we get a customer enquiry and they give
us their order number etc - which we use the primary keys for then the
order number might have changed.
Ok i hope this all makes sense. Any ideas will be very appreciated.
James