Everything about database

Role change, part crossing-over, and monitoring server is in the position

When come to a standstill of the database on the line (the likelihood is the work is defended inside the plan,Or it is the state outside anticipating) ,If return the database on server of aid having equipment to be able to be offerred,access,You may set your mind at one a little bit quite.The daily record with a good design is deferent system (trade the database log file conveys from main server equipment aid server) can give you such self-confident heart.Built-in at SQL Serve 2000 the program of tool of Enterprise Manager of enterprise board and development edition bears a daily record namely deferent function.

Role change

Convey the log from main server after less important server,You can be in drop main server with displacement of less important server when necessary.If main server produces a problem,Or it is plan sex come to a standstill (upgrade for example hardware or installation amend an order) ,During the database on the line must stop to serve a paragraph.The part that right now you can change the database on less important server,The database on the line is become then after letting it replace main server.The manual on line of SQL Server 2000 (Books Online,BOL) calls this operation the daily record deferent part is changed (Log Shipping Role Change) .Convey in the log in the process,Less important server needs set to be in cannot recover from an illness (Nonrecovered) condition,Because this trades,the log just can be answered from main server put less important server (one but you recover from an illness the database,cannot farewell is put trade record) .When changing a part,The database that you need will less important server gives recover from an illness,Indicative its are database of new main server.You also can be set of database of old main server database of new less important server.If database of old main server did not damage,Can be in so new main server and old main server (already became new less important server) between the new daily record that build place is deferent function.We call way of this kind of switch part crossing-over (Role Reversal) .

These operations are how-to can edit for six basic measure,It is respectively:1, move and remit enter order of enter an item in an account,2, demote (Demote) main server,3, upgrade (Promote) less important server,4, part of announcement monitoring server already was changed,5, analytic on less important server enter order of enter an item in an account,6, and concatenate database is accessed with attributive.

Measure 1: Move and remit enter order of enter an item in an account Above all,BOL suggests you build a SQL Server 2000 DTS to enclose (Package) ,Use will main server ascend date of enter an item in an account to transfer less important server,And carry out the analytic movement that SID of date of enter an item in an account records between each server.The DTS Transfer Logins Task that move ascends place of date of enter an item in an account to use can be used inside SQL Server 2000 DTS Designer only.You can be built on main server with store DTS is enclosed,Next the executive means that set of call Dtsrun.exe should enclose, the job that passes through main server SQL Server Agent (Job) .Should enclose the meeting when carrying out to will ascend date of enter an item in an account to convey from some server to another server,But it is not met analytic the SID(that its enter order of enter an item in an account is in can explain why to need in measure later analytic enter order of enter an item in an account) .However,To be in later can successful analytic enter order of enter an item in an account,You must establish a record first,The remit material that list of data of main server Syslogins contains inside its.

Remit ascends date of enter an item in an account to arrive when less important server,The SQL Server Agent that BOL suggests you establish a two level works:Use Bcp remit,And duplicate enter order of enter an item in an account.In the first measure,You will ascend the Bcp that uses primitive pattern remit of date of enter an item in an account to come a certain archives.And in the second measure,You must will ascend date of enter an item in an account to duplicate to a certain archives of less important server,So that make can be used when role change later analytic enter order of enter an item in an account.In measure the 5 SID that you put use Sp_resolve_logins the program to enter order of enter an item in an account beforehand.This job is built after finishing,OK and fixed ground is carried out (carry out every night for example) .Come so server admiral withholds secondary to publish document of remit of date of enter an item in an account newly at any time,So that undertake the log conveys,the part is changed.

Measure 2: Demote main server To let main server it is a log no longer the data sources of deferent system,You must it " demote " .You can demote the origin database of main server,Let it become potential less important server.Carry out Sp_change_primary_role to put a program beforehand on main server next,The purpose is to move to convey except original daily record function.Program code list 1 show this to put a program how to convey Pubscopy log beforehand database from read / write mode to be changed read-only equipment aid mode,Preparation is accepted at any time trade the backup data of the log.This meeting after putting program by to count a move beforehand conveys in the log main server database is deleted inside the plan.Afferent parameter puts those who accuse the program to need to carry out the following job beforehand:Backup is the closest trade all use person inside database of log, end are online, be in database set equipment hold status and the person that use more accesses a class.Put a program beforehand time pass code to narrate indicative BACKUP LOG sentence carry out whether successfully.

Program code list 1:Convey the log database from read / write mode to demote read-only of mode put a program beforehand.

USE Master
GO
EXEC Msdb.dbo.sp_change_primary_role
@db_name = 'Pubscopy' ,
@backup_log = 1,
@terminate = 1,
@final_state = 3,
@access_level = 1

Measure 3: Upgrade measure of the next of less important server is upgrade current and less important server to recover from an illness condition (Recovered State) ,Such its ability replace the database on original line,And turn potential log into database of deferent and main server.On less important server,If you already affirmed the person that do not have any use continues to put access,occupy a library,Can carry out Sp_change_secondary_role to put a program beforehand,Like program code list 2 are shown:

Program code list 2:Put what database of less important server upgrades into main server database the program beforehand.

USE Master
GO
EXEC Msdb.dbo.sp_change_secondary_role
@db_name = 'Pubscopy' ,
@do_load = 1,
@force_load = 1,
@final_state = 1,
@access_level = 1,
@terminate = 1,
@keep_replication = 0,
@stopat = Null

These parameter will make this put a program to try to trade all the rest beforehand log file duplicates from former and main server to less important server,And secondary of these log file to load server database.Parameter @do_load=1 can undertake backing up recently,And to load is all trade log file;Parameter @force_load=1 is appoint when executive Sqlmaint.exe have not the Forceload option that the file converts;Parameter @final_state=1 is set of database of new main server recover from an illness mode;Parameter @access_level will access means to set a condition of the person that use more before.The database of the person that parameter @terminate=1 makes this put a program to interrupt all use beforehand accesses a movement, means is to carry out ALTER DATABASE to cooperate IMMEDIATE option.However,When if carry out this,putting a program beforehand,Between your own Enterprise Manager and database online be in open condition,Movement of ALTER DATABASE will fail.So you must move means to affirm with the hand whether to already have place the database online give interrupt.Finally,If this database is autotyped for the database by set (the publisher database of Replication) (Publisher) ,So @keep_replication=0 parameter will maintain a server to get on all autotyping as before set.

If you ever chose to let less important server become the main server with potential future,Criterion the database safeguards a plan to meet place is built on less important server one trades log backup works (the Transaction-log Backup Job) of SQL Server Agent.After this job activations,Trade log backup file can begin to appear in new main server.You need these archives to go reset conveys the log a new less important server.

[1] [2] issues one page