January 12, 2010 at 8:18 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 1:30 pm
An even easier solution:
Two Tables, let's call them MyTableA and MyTableB
You also have a view called MyTable.
Consumers of the data read from the view, MyTable, which points to the current active table. Whenever you wish to swap out tables, alter the view. This will manage concurrency very nicely - the alter will get blocked by any active requests. When those requests complete, the view is recreated and subsequent requests hit the new table.
If you are concerned about performance, (and you are indexing, writing etc) then ensure your disk configuration is optimal for the load. (i.e. the tables are each on separate filegroups (on separate spindles or shelfs (if you are on a SAN) Also, you are likely making heavy use of TempDB building your indexes so see if you can also separate tempdb out into multiple files on separate spindles. Finally, make sure the maximum degree of paralellesm won't allow a very cpu intensive process to dominate the server.
One 'gotcha' if your view definition is 'select * from myTableA' and you alter the definition of that table, you need to exec SP_refreshView 'myTable' to refresh the metadata.
Good Luck, I hope that helped
SQL guy and Houston Magician
January 12, 2010 at 1:40 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