Technology of SQL Server database (106)
Tuesday, March 03, 2009 by rain
So how do we realize this kind of consistency of data?The answer may have a lot of to plant,But it is the most database that includes SQL Server inside the product uses a kind of duplicate technology to solve definitely this one problem.The duplicate technology that the gist of this chapter introduces SQL Server namely.I let below people begin from duplicate overview.
SQL Server was offerred inside the duplicate ability of buy,Duplicate component is not supplementary product however a cent of core engine.Duplicating below the main technique help that this supports ability of distributed data processing,We can be on the different database server that crosses net of local area network, vast region or Internet the many copies that maintain data,Assure data with the means of synchronism or asynchronous automatically thereby the consistency of data between many copies.By nature,Duplicate even if duplicate to database of much mark handling order from a source data library data.
16.1.1The duplicate model of SQL Server
SQL Server is used " publish and order " this one term will describe its duplicate activity.Alleged publish even if occupy library server to other number (the person that order) duplicate data.Order even if from additional server (publisher) receive duplicate data.Although publish,mix the target that order is will duplicate data,But publish and be being ordered is not different point of view however (publisher and order) same data manipulation (duplicate data) ,Reflect a certain administrative levels sex and ordinal sex however (always undertake publication first,Undertake ordering again next) .The duplicate component of SQL Server has the publisher, person that order, person that distribute, publication and paper, push order buy and pull order.
(1) publication and paper
Paper (Article) be by duplicate data gather,A paper can be whole watch, certain kind (perpendicular differentiating watch) or certain travel (the watch that the level differentiates) it is process of a few memory even.The basic composition that the paper is publication is unit.Publication is paper and gather,It can include one or more paper.What the person that order orders is publication is not the paper in publication,Can make order so more simple.
(2) publisher
Publisher is the server that points out edition publication.Publisher server will maintain source data library (include publication) and about the information of publication,Make data can be used at duplicating.Besides the decision what data will be duplicated,Publisher should detect outside which somes of duplicate data produces change,Of the person that duplicate these change to distribute in distributing a database.
(3) the person that distribute
The person that distribute is to point to a service that delivers the duplicate data that come or general affairs or memory process to send to the corresponding person that order from publisher implement,Be in charge of safeguarding distribute a database.
(4) the person that order
The copy that the person that order is the data that shows memory is duplicate,And the server that receive and maintains the data that already published.The person that order also can undertake modification to publishing data,But although order person can undertake modification to data,But it still is a person that order.Of course,The publisher of the person that the person that order also can be ordered as other.
The server that the publisher, person that distribute, person that order does not point to mutual independence certainly actually,It just is duplicating to SQL Server the description of the different part that acts in the process.SQL Server allows server of a SQL Server to be able to be played the part of perform different role.For instance,Server of a publisher can publish publication to also can come as the person that distribute already memory and deferent snapshot duplicate and general affairs duplicates.Of course the publisher of the person that server of a person that order also can serve as other to order at the same time,Do not cross this kind of circumstance to see rarely only.In applying actually we decide to whether let a server act one or more part to be in very old rate is the consideration that is based on duplicate system performance.The person that distribute to rise for example from distribute a database the database to the person that order answer the efficiency that manufactures publication,The load that reduces publisher server.The person that we often do not allow server of some SQL Server to play the part of show edition already acts the person that distribute again,Make another servers special assumed task of the person that distribute to improve publisher and the performance of the person that distribute thereby however.
(5) the type that order
There are two kinds of kinds that order in SQL Server:Push order and pull order.Push through using order or pull order the change that will give edition database to happen to duplicate to order a database.Push ordering is to show by publisher the modification that will all in publication database happening duplicates the to the person that order person that need not order issues the request that order.Should publish database happening to revise only,Publisher can move the person that order this kind of modification system now oneself over there.In synchronous to data sex the requirement compares tall situation (if want publication content to issue change only,The database that order is about to make revise accordingly) had better use push order.Pull ordering is to show every cross the person that order the change that a paragraph of time can ask to publisher duplicate publication database happens.Had better use in circumstance of the person that a lot of is order pull order buy.Because pull,ordering is by the person that order is not publisher is started,Be in so will decide synchronous publication database changes by the person that order the situation that turn also had better be used pull order.
16.1.2The duplicate representative of SQL Server
(1) snapshot acts as agent
Snapshot representative Snapshot Agent is founded on the person that distribute and memory snapshot file,In distributing a database the record publishs database and the synchronous information between the database that order.Snapshot representative moves on server of the person that distribute to join with publisher photograph,The snapshot that each publication has him acts as agent.
(2) the log reads a representative
The log reads a representative (Log Reader Agent) win the bid log of publisher general affairs duplicate general affairs moves to distribute number to occupy a library.The log that makes each when duplicate with general affairs publication database has him reads a representative.The log reads a representative to move on server of the person that distribute.
(3) distribute a representative
Distribute a representative (Distribution Agent) server of the person that order can distribute memory in the general affairs in distributing a database or snapshot.If clerical publication or snapshot publication are installed,push to was founded only order mix in publisher instantly namely order the person that buy between synchronous,Go up in the person that distribute they can have distribute a representative severally;Otherwise publication content of clerical publication and snapshot will share to distribute a representative.Amalgamative publication did not distribute a representative.
(4) amalgamative representative
Amalgamative representative (Merge Agent) be used to mobile, amalgamative increase by degrees in what what after snapshot representative founds initiative snapshot, produce revise,The amalgamative representative that each amalgamative publication has him.Should use push when ordering amalgamative publication,Amalgamative generation manage moves on publisher;Should use pull when ordering amalgamative publication,Amalgamative representative moves on the person that order.Snapshot publication and clerical publication do not have amalgamative representative.
(5) alignment reads a representative
When snapshot duplicates or general affairs duplicates if chose option of Queued Updating or option of Immediate Updating With Queued Updating As A Failover,Need to use alignment to read a representative.
Alignment reads a representative is the multi-line Cheng representative that moves on the person that distribute,It basically is in charge of read a news and will including the general affairs in the message to apply publisher from inside alignment of message of the person that distribute.
16.1.3The duplicate type of SQL Server
SQL Server provided three kinds of duplicate kinds:Snapshot is duplicate (Snapshot Replication) , general affairs is duplicate (Transactional Replication) , amalgamative and duplicate (Merge Replication) .One kind can be used in applying actually or a variety of duplicate types.Duplicate type is in avery kind of the autonomy that consistency of data and node achieve on different level,The requirement that the choice of because this is right duplicate type basically relies on applied system logarithm to occupy autonomy of consistency, node and existing network natural resources circumstance (if the network is broad,transmit speed with the network) .Introducing work respectively snapshot is duplicate, duplicate in mixing three amalgamative and duplicate, we will discuss how to select right duplicate kind.These three kinds of duplicate types introduce to the point below.
(1) snapshot is duplicate
Character of if name place,Snapshot is duplicate mean the publication data in publication database gives to take a picture in some hour,Server of the person that order number according to duplicating next.Snapshot is duplicate implementation is relatively simple,The instantaneous data that what its duplicate is some hour database only,Duplicate success did not affect this locality database (publication database or the database that order) consistency.The applied environment middling with less change is occupied to use snapshot to duplicate in number,If duplicate,the static state that often is not revised is expressed.
(2) general affairs is duplicate
The content that with snapshot log of duplicate and different general affairs duplicates is not data however statement of many DELETE UPDATE INSERT or memory process.When use general affairs is duplicate,Revising always is to happen on publisher (the work that set option of the person that order newlier instantly is duplicate place of the person that can ordering revises duplicate data) ,The kind that the person that order occupies with reading access only will revise report to the database that order,Can avoid duplicate conflict so.If data is newer frequency is bigger and the hope revises the person that duplicate to order as soon as possible to often use general affairs to duplicate.
(3) amalgamative and duplicate
Amalgamative and duplicate the person that allow to order undertakes modification to publication,Will revise incorporate target database (can be publication number occupying a library also can be the database that order) .Each node can work independently and need not join each other,The consistency that can have any operations to publication and need not consider work.If incorporating,conflict produces when revising,Duplicate to solve strategy to undertake analyse and acceptance conflict one party to conflict according to certain regulation or the conflict that define oneself revise.
16.1.4 duplicate consistency of data
In distributed application environment general affairs is handled besides the ACID requirement of contented general affairs,The consistent sex that still must satisfy data asks.The duplicate consistency of data below duplicate environment basically has two sort model:
Clerical consistency (Transactional Consistency)
Of data concentrative (Data Convergence)
(1) clerical consistency (Transactional Consistency)
Below duplicate environment,Clerical consistency basically is the node that points to all participation to duplicate must have same data result market after duplicate end,Be like those who happen on all node all general affairs were carried out by ground of one by one in every node there is consistency of general affairs of two kinds of levels to duplicate data in SQL Server:Instantly clerical consistency (Immediate Transactional Consistency) , consistency of potential general affairs (Latent Transactional Consistency)
Instantly clerical consistency
Instantly clerical consistency makes sure all ginseng are in at the node of duplicate general affairs either hour has identical data.Two phase are used to submit an agreement between the node that through be in all participation in SQL Server general affairs handles,The consistency that can realize general affairs below environment of distributinging type application thereby.All node must refer general affairs at the same time or do not refer general affairs,General affairs is in why one node refers failure to be able to bring about whole routine want to answer on all node boil.Apparently this kind relatively " slashing " the general affairs that clerical consistency does not suit to a large number of node are participated in is handled,Be transmitted because of the network and fluky and function is flabby also calm.Assure general affairs when duplicate so instantly the instantaneous that consistency asks publication database must carry data with the database that order namely is synchronous.
Consistency of potential general affairs
Potential clerical consistency allows data in publication database and the accord that asynchronous maintains between the database that order,Namely after giving edition content to produce change,The ability after passing proper delay time reflects the change of publication there the person that order.The general affairs that the success refers to did not affect publisher on the person that general affairs is being ordered is handled.If publish a database to produce new data no longer,change,That after passing certain time-interval,All node will have same data result market.Additional,Revise what potential general affairs sends a gender to ask final synchronous data includes place of a certain site to do entirely,Namely its or is node A carry out or the data condition after many general affairs,Or is the data condition after node B executes routine.
Instantly the difference of clerical consistency and consistency of potential general affairs depends on:Instantly data of clerical consistency requirement instantly synchronous,And do not allow to be in publisher (source server) the person that the data change of place happening is duplicating to order (purpose server) before have a paragraph of latency time.In SQL Server,No matter be general affairs,duplicate,Still be snapshot duplicates not to ask to assure to duplicate stand namely clerical consistency.General affairs duplicates to duplicate to be compared with snapshot was to shorten only the latency time that data distributes,Belong to substantially at potential clerical consistency.
(2) of data concentrative (Data Convergence)
Below duplicate environment,Of data concentrative it is to show all node have same data result finally.But as consistent as general affairs sex is different,The data result here may not include a certain node the result after all general affairs are carried out.
Amalgamative and duplicate the autonomy of the concentration that those who emphasize is data and node the consistency that is not general affairs.Incorporating in duplicating,All node must not interrelate normally,More ground is handle in order to leave the means of the line free to data,The synchronism that just just assures data finally after incorporate.But after although be in,incorporating sometimes,The work that some node place has handles a result also not all mirrors other node,Because still may be put in a few node to be in when incorporate,leave line job position.
16.1.5 synchronous mode
The data inside publication of the person that alleged synchronism points to exert edition namely and descriptive file and of the person that order duplicate keep consistent.
(1) manual synchronism
Should found when ordering,Load initiative snapshot file and hand of the person that you can be being ordered is moved is not through the network,This is called " hand labour is synchronous " .If publication is bigger,So will load snapshot file to undertake manual synchronism will improve efficiency greatly from tape or other memory medium.Example exactly the number has ten a few GB according to the library,So to tape database download,Load database of the person that order afresh again,Will transmit speed than passing so slower and very not reliable network already fast simple.Use handiwork method is handled into travel synchronism,Need not run snapshot representative to undertake initiative synchronism again,SQL Server also won't express the goal with publish theory civil undertake initialization synchronism,It will assume already synchronous the person that order and publisher,Issue duplicate document the person that order instantly.
When undertaking manual synchronism,The watch structure that will be in charge of ensuring publication paper and target are expressed by the user and data are identical.The change that the advantage of this kind of method is generation of duplicate data place can be distributed instantly to the person that order,The system that avoids to carry out snapshot to acting manage will undertake initiative synchronism and be caused thereby is overweighted.
Notice:Allude here " need not run snapshot to act as agent " it is to point to need not run snapshot representative to undertake publisher and the snapshot initialization between the person that order,But representative of the snapshot in the process duplicating after be in still should move.
(2) automatic synchronism
Automatic synchronism is to show the publication watch between the person that order and publisher and the initiative synchronism that the target expresses are finished automatically by SQL Server.Below acquiescent circumstance,SQL Server can start synchronism automatically to handle an order in program time,Found synchronous gather inside the person that distribute by snapshot representative above all (the describes file and *.bcp data file of *.sch) ,Build on the person that distribute establish a synchronous operation,Distribute a representative to deliver synchronous gather the person that order to use descriptive file to build watch structure above all next,Database of the person that use BCP to duplicate data to order next.
(3) without synchronism
Do not need to order database and publication database synchronism namely without synchronism,SQL Server can assume publication database and order buy a database already synchronous,Also won't be opposite synchronous undertake test and verify,These jobs should be finished by him user.
The develop with duplicate 16.1.6 attacks structure
SSQL Server supports astral form develop to attack only structure,In this structure,Duplicate data from central publisher / the person that distribute flows to the many person that order,Duplicate data does not have between the person that order deliver.So if some is ordered person cannot work normally,The person that do not affect other to order receives duplicate data from the person that distribute.
Develop of use star form attacks the latency time that the biggest advantage of the structure was reducing to order the data in the process at its,In because duplicating,be being ordered,Synchronism came true in the person that duplicate data is in all ordering through three paces at most.The data that if flow,uses in data synchronism so does not use astral form develop to attack more the structure is fast and efficient.the data bulk on the person that additionally this structure allows to have publication breaking up decreasing thereby memory is ordered in each.But,Astral form develop attacks the drawback that the structure also has oneself,Main show is in:
The person that the synchronous processing of data relies on a center overly to distribute / publisher.The person that should order is revised when duplicating,The person that the synchronous requirement center of data is distributed / publisher is participated in among them,Two phase were used to submit an agreement in this synchronism.When the person that other is ordered undertakes ordering,Publisher should participate in the person that order duplicate data report to other among them again.The person that bring about a center to distribute thereby / publisher can raise hold to be restricted only a person that order.Right now we can be passed only increase the additional person that the center outside is distributed / the person that publisher supports more to order thereby;
If the center is distributed person / publisher invalidation,Whole and duplicate system will break down,Of data order and distribute will stop.
There is above in SQL Server the astral shape structure of a few kinds of forms:
Central publisher (Central Publisher)
The central publisher that contains the person that distribute remotely (Central Publisher With Remote Distributor)
Publish the person that order (Publishing Subscriber)
The person that the center is ordered (Central Subscriber)
(1) central publisher (Central Publisher)
Central publisher is a kind of most simple starlike develop attacks structure,Below this kind of configuration,Part of the person that a server plays the part of show edition already acts part of the person that distribute again,The server that allows one or more independence at the same time acts part of the person that order.This configuration agrees with from data center (like company headquarters) the person that use to data (like the branch) duplicate data,And these data do not allow be revised by the person that data is used (wait like company financial forms for reporting statistics) .This structure shows 16-1 place like plan institute.
(2) ) the central publisher that contains the person that distribute remotely (Central Publisher With Remote Distributor)
Below central publisher configuration as a result of,All duplicate representatives, memory that publish and orders activity and information and safeguard wait for a lot of jobs to be finished by a server,Because of this,If duplicate general affairs or data are bigger or have the too much person that order,Can produce huge to duplicate efficiency negative effect,The finite sex of network resource makes this one issue more outstanding.Be based on this,The person that we often will be distributed and publisher depart leave,The part that the server that lets independence respectively will come to act the person that distribute and publisher,Make publisher server is put from the solution in distributing the task from.What should emphasize is must have between the person that distribute and publisher can lean, high-speed signal company is received.This structure is shown like place of graph 16-2.
(3) publish the person that order (Publishing Subscriber)
Below this configuration,Have two publisher original publisher:And publish the person that order to publish the person that order.The order person that is original publisher,The person that part of the person that also play the part of show edition at the same time is ordered to other publishs data.Both has same publication data.Should go out edition person transmit speed with the network between the person that order slower or communication cost uses this higher to deploy program constantly.Publish the person that order to have change trains effect,It orders data from original publisher above all,The order person that publishs data it again next.Primitive order the person that buy and publishing the person that order can be publisher and the dual role of the person that distribute.This structure is shown like place of graph 16-3.
In cross continent or the duplicate processing middling below the circumstance that crosses a state is used publish plan of the person that order.Another requirement that should satisfy are to publish the person that order and should compare between the person that order original publisher and there is shorter network between the person that order distance, more reliable pass lose property.Can allow original publisher and publish there is slower transmission rate between the person that order,But must have reliable transmission property.
(4) the person that the center is ordered (Central Subscriber)
The person that the center is ordered is to show the person that a lot of publisher are ordered to is duplicate publication general affairs and data.Target watch is deuced to cut every to break up by water piece contain data of this locality of a label advocate key is worth.Every publisher publishs a among them to break up only piece.Go up to those having for the applied environment that rolls data business,This configuration plan is very valuable.In if in large fraction is sold,tying interconnected system,Break up to selling a watch to have a level.Each sale branch is publisher,the sale that attributes itself only data publishs sale headquarters.This structure is shown like place of graph 16-4.