What type of replication? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

What type of replication?

Hi everybody, I’m having a little trouble figuring out which type of replication I should use for my application. My scenario is basically an eCommerce store that I would like to take the orders in, but then all the orders would be processed on an internal server. So I have a live SQL Server that the store is run on and that takes the orders as the come in. Then I have a local (internal) SQL Server that I would like to receive the "new" orders as they are created on the live server. Once the orders are on the local server they would be processed and then pieces of the data would need to be pushed back to the live server – not immediately, maybe twice a day or something like that – so that customers can track the status of their orders and things like that. I was thinking it should be a merge replication process but am not sure. The only changes that will be made on the live server are the new orders, nothing after that. So really I just need to push the local data to the live server which could almost be a transactional replication process. Any help would be much appreciated. And any additional information you would need in order to help me, I can provide with the best of my knowledge. Thanks :: Joel Flint :: :: Developer
:: Sport Compact Only
::http://store.dragracing.com
:: [email protected]
Hi,<br /><br />Just wanted to add to my last post. I was wondering if it were possible to setup both servers as dist/pub/sub. For example, I would setup the live server that runs the site to be a distibuter/publisher that the local server (subscriber) would pull from every time and order comes in. <i>(Not sure how it would be notified of the new record.)</i> Then I would setup the local server to be a distributer/publisher that would push to the live server (subscriber) on a scheduled basis. I’m very new to replication so I don’t even know if this is possible, but in theory it is really what I’m trying to accomplish.<br /><br />Thanks in advance every body! [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />:: Joel Flint ::<br /><br />:: Developer<br />:: Sport Compact Only<br />::<a target="_blank" href=http://store.dragracing.com>http://store.dragracing.com</a><br />:: [email protected]<br />
Replication is, in principle, designed to offer data availability in distributed environments and sometimes as a means achieving fault-tolerance. What you are describing is a cause-effect situation that may benefit more from a process-oriented solution without the use of replication. The only requirement that would necessitate your implementation of replication would be if the LOCAL server required to keep a synchronized copy of the data in the LIVE server with some degree of transactional latency. Without this, or even inspite of it, this is how I would approach it: 1. Receive order(s) on the LIVE Server
2. Send the necessary (not all) data required to process the order(s) to the LOCAL server
3. Process the order(s)
4. Update the LIVE server as soon as each order has been processed All Step 2 and 4 require are distributed transactions using linked servers and transactional latency is almost non-existent. Step 2 would involve setting up an INSERT and/or UPDATE trigger on the table(s) holding the order details and calling a remote stored procedure to update the data on the LOCAL server. Step 4 is similar only this time the trigger is fired when the LOCAL order details are UPDATED. You get to accomplish your immediate goals, your customers get to see the status of their orders in real-time and you avoid the medical bills associated with baby-sitting updated subscriptions in transactional replication. Less administration is the secret to a long life. Nathan H.O.
Moderator
SQL-Server-Performance.com
vbkenya, Thanks a lot for the response! Sounds like a great solution. I do have one concern though…How do I keep the IDs the same? I am currently using the auto-incrementing primary key as the order number. They must match on both servers. Do you suggest I use other means to derive the order number? I actually started doing it this way, but got stuck on the order number part of it. What I did was put an INSERT trigger on the order table that would basically copy the order number to a replication table. Then I was running a scheduled DTS ActiveX script on the LOCAL server that would check the replication table for order numbers then copy them down to the local server. Didn’t have a solution yet for updating the live server after processing. Is this kind of how I would go about implementing your solution? Is there anything I can read on this type development? I’m not incredibly efficient in SQL, I’m more of a Web Developer, so any additional help would be much appreciated. Thanks again for your recommendations! I would still love to hear what other people might have to say as well, so thanks in advance for any input. :: Joel Flint :: :: Developer
:: Sport Compact Only
::http://store.dragracing.com
:: [email protected]
Fact: The orders are created on the live server and get an orderID auto-assigned to them. The Local server only works with orders already created. The INSERT trigger on the orders table copies the new data to the local server table thus: CREATE TRIGGER Trg_OrderInserted ON Orders
FOR INSERT
AS
INSERT INTO LocalServer.OrdersDatabase.dbo.LocalOrders (OrderID,Field2,Field3)
SELECT OrderID,Field2,Field3 FROM Inserted Remember that the local table has an OrderID field that is not defined as IDENTITY (auto-increment) so there should be no worry over the synchronization of the IDs. After local updates to the order details, the trigger on the local table would look something like this: CREATE TRIGGER Trg_OrderUpdated ON LocalOrders
FOR UPDATE
AS
UPDATE LiveServer.OrdersDatabase.db:confused:rders o
SET o.field2=i.Field2, o.Field3=i.Field3
INNER JOIN Inserted i ON
:confused:rderID=i.OrderID
Viola! You will not need to do anything else to make sure that two servers are synchronized automatically. Just remember to configure the linked servers – An easy task considering that they are both SQL Servers.
Nathan H.O.
Moderator
SQL-Server-Performance.com
Thanks for all your help. I’m gonna try and implement it. I might need some additional help though. Thanks again, :: Joel Flint :: :: Developer
:: Sport Compact Only
::http://store.dragracing.com
:: [email protected]
vbkenya, Okay, I got a couple of issues. First, the live server cannot access the local server. The local server is internal only. I guess I could run a scheduled dts that pulls down the new orders periodically. What do you think? Second, if I run an UPDATE trigger on the local server that would update the live server, do you think that would affect the runtime of my front end application? When I run the UPDATE statement through a stored proc from my ASP app, will my app have to wait for the trigger to run as well or will it run in the background? Thanks again for your help, :: Joel Flint :: :: Developer
:: Sport Compact Only
::http://store.dragracing.com
:: [email protected]
]]>