January 12, 2010 at 8:16 am
Hi SQL experts:
My project requires switching data online. We use a regular job to prepare new data and they should be made available when ready. For customers we only support simple read-only operations which will be finished in several seconds. We thought of 2 plans, each has pros and cons.
(1)Use three machines for the switching. MachineA is working online and serve clients, while MachineB is preparing data. MachineC contains meta info (which machine is available currently). When MachineB is ready, it asks MachineC to update the meta info. Then machineA will went offline and start to prepare data for the next round. When clients want to access the data, firstly it needs to ask MachineC to know whether A or B is available currently.
Pros:
a.Switching is quick and online performance will not be affected
Cons:
a.The logic is complex. We need to synchronize between 3 machines
b.Client is close coupled to server
c.Switching might happen just after client gets the meta info. Exception will happen then and client application should be designed carefully to handle this situation.
(2)Use only one DB. All requests are handled by a stored procedure SP_ SomeTask . For each request, SP_SomeTask will check meta information to determine which table to use. Suppose currently tableA has the data. When new data is ready, we load it into tableB and create indexes. After tableB is ready, we update the meta information and queries will be executed on tableB afterwards. TableA will be used for loading next time. And so on.
Pros:
a.Logic is simple and client don’t care about the details
Cons:
a.Performance of online request might be affected when tableB is loading and indexing
My question is:
Do we have other candidate solutions?
Is there supplementation of analysis or improvements for solution (1) and (2)?
Any comments is appreciated
January 12, 2010 at 9:24 am
I would opt for solution number 2. If done correctly, and insuring your disk situation is adequate you should be able to effect minimal impact.
The probability of survival is inversely proportional to the angle of arrival.
January 12, 2010 at 1:41 pm
Another duplicate post, please contiue this discussion here .
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply