June 14, 2006 at 2:29 pm
would anyone like to share their log shipping role reversal experience. I am somewhat frustated with it.
thanks
June 19, 2006 at 8:00 am
This was removed by the editor as SPAM
June 20, 2006 at 9:36 am
yea bro ... spill the beans !!
June 21, 2006 at 4:54 am
To change the role of a standby server when the primary server fails or is taken offline, disable the database restoration job on that server, execute the role change stored procedures, synchronize SQL Server logins and standby database user accounts, and then use the standby database as the new production database. Ensure all logins, jobs, alerts, operations, and DTS packages are synchronized, and then redirect clients to the promoted standby server. In all examples below, substitute relevent database names, filepaths etc as necessary.
In a planned role change, pause all applications that use the production database, and then place the production database in single user mode. Pausing the production database helps ensure that the standby database is transactionally current with the production database. Pausing the production database fails if applications do not disconnect from the database after each transaction. Be sure you don’t have open connections in Enterprise Manager, Query Analyzer, or other utilities that would cause the pause procedure to fail. In an unplanned role change, recent transactions that are not backed up and copied to the standby server before the primary server fails are lost.
To pause applications that use the production database disconnect applications from the production database if the database is still available. The method for doing so varies by application. In a planned failover, allow open transactions to complete, but do not allow new ones to start.
Note: Ensure that no users are connected to the production database before continuing, including any connections you have through SQL Server Enterprise Manager.
You need to execute the following stored procedures in this order to perform the role change: To execute the sp_change_primary_role system stored procedure
1.Using SQL Query Analyzer, connect to the primary server by using Windows Authentication.
2.In the query window, type and execute the following Transact-SQL script:
Msdb..Sp_change_primary_role
@db_name=,
@backup_log=1,
@terminate=0,
@final_state=3,
@access_level=2
Note: You will not be able to run this system stored procedure if the primary server has failed. All transactions not previously backed up are lost and users have to recreate these lost transactions.
When the sp_change_primary_role system stored procedure executes with these parameters, the system stored procedure performs the following actions:
·Removes the production database from the log-shipping database maintenance plan.
·Disables the log-shipping transaction log backup job on the primary server.
·Backs up the tail end of the production database transaction log.
·Terminates and rolls back all pending transactions in the production database.
·Places the production database in single-user mode for the duration of the system stored procedure.
·Sets the recovery state of the production database after the system stored procedure completes to NO RECOVERY, which allows the application of transaction log backups from the promoted standby database.
·Sets the access level of the production database after the system stored procedure completes to restricted user mode.
Note: If this system stored procedure fails because SQL Server cannot obtain exclusive access to the production database, rerun this system stored procedure after closing any existing connections to the production database. If the first execution of this system stored procedure fails, the log-shipping transaction log backup job is still disabled.
To stop the log-shipping restore job on the standby server
1.Using SQL Server Enterprise Manager, connect to SQL Server on the standby server.
2.Expand Management, and then click Jobs.
3.Right-click Log Shipping Restore for ._logshipping, and then click Disable Job.
Disabling the log-shipping restore job prevents its periodic execution from interfering with the execution of the sp_change_secondary_role system stored procedure.
Note: Ensure that no users are connected to the standby database before continuing, including any connections you have through SQL Server Enterprise Manager.
To execute the sp_change_secondary_role system stored procedure
1.Using SQL Query Analyzer, connect to the standby server by using Windows Authentication.
2.In the query window, type and execute the following Transact-SQL script:
Msdb..sp_change_secondary_role
@db_name=,
@do_load=1,
@force_load=1,
@final_state=1,
@access_level=1,
@terminate=0,
@stopat=NULL
When the sp_change_secondary_role system stored procedure executes with these parameters, this system stored procedure performs the following actions:
·Copies the final production database transaction log backup to the standby server along with any additional transaction log backups that were not previously copied.
·Disables the log-shipping transaction copy job on the standby server.
·Restores all transaction log backups not previously restored to the standby database and initiates the recovery of the standby database.
·Terminates and rolls back all pending transactions.
·Sets the access level of the standby database after completion of the system stored procedure to multiuser mode.
·Creates a log-shipping database maintenance plan on the new primary server for the new production database. The SQL Server Agent job defined in this plan backs up the transaction log of the new production database to the folder location you specified when you set up log shipping.
Note: If this system stored procedure fails because SQL Server cannot obtain exclusive access to the standby database, rerun this system stored procedure after closing any existing connections to the production database. If the first execution of this system stored procedure fails, the log-shipping copy job is still disabled.
To execute the sp_change_monitor_role system stored procedure
1.Using SQL Query Analyzer, connect to SQL Server on the monitoring server by using Windows Authentication.
2.In the query window, type and execute the following Transact-SQL script:
Msdb..Sp_change_monitor_role
@primary_server=,
@secondary_server=,
@database=,
@new_source=
When the sp_change_monitor_role system stored procedure executes with these parameters, the msdb database on the monitoring server updates to reflect the new primary server, the new standby server, and the new transaction log backup folder.
The former standby server is now the primary server. To maintain the database redundancy protection provided by log shipping, edit the database maintenance plan on the promoted standby server to enroll the original primary server as a standby server. If the tail of the transaction log for the production database on the original primary server was backed up and the database left in recovery mode, you can apply transaction logs from the new primary server without reinitializing the original production database. If the original primary server failed, initialize and synchronize the standby database on this server before adding it to the database maintenance plan.
To synchronize SQL Server logins and database users
1.Using SQL Query Analyzer, connect to SQL Server on the new primary server by using Windows Authentication.
2.In the query window, type and execute the following Transact-SQL script:
sp_resolve_logins 'db_name', 'destination_path', ‘filename'
Note: This script will not successfully synchronize mappings between SQL Server logins and database users in the standby database if you do not run the synchronize logins job for each standby server each time you change SQL Server logins or database users on the primary server.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply