Log ShippingOperations Guide
Version: 1.0
By tonymeng
2004-11-30
Index
Create log shipping .. 3
Monitor .. 9
Delete log shipping .. 10
Role alter .. 11
Role change .. 13
Create log shipping
1. SQL Server Node 1 Tonym and Tonym02 must be in the same domain, and SQL1 and SQL2 are used to start SQL Server service and SQLServerAgent services using domain accounts.
2. Delete the Local connection in the Enterprise Manager, apply Server Name Register your local server Tonym, auxiliary server Tonym02
3. New shared folder northwindbackupshare01 on the SQL1 server is given to the FULL permissions that start the SQL Server account. Create a new folder ReceiveSql2logs on the SQL1 server to receive logs from SQL2 when performing database role conversion.
Add a shared folder northwindbackupshare02 on the SQL2 server to give Full permissions that start the SQL Server account. Create a new folder ReceiveSql1logs on the SQL2 server to receive the logs from the database SQL1.
4. Set the server you want to apply to Log Shipping to complete recovery mode.
5. Right-click the New Maintenance Plan on Database Maintenance Plans, select Logshipping database, only one database is allowed each time.
6. Remove Back Up The Database As Part of The Maintenance Plan to ensure that the maintenance plan is unique (recommended)
7. Specify the database log backup path.
8. Specifies the shared folder where the log file is stored.
8. Add a destination database.
Server name is a destination name
Transaction Log Destination Directory fills in the reception path to the log file from SQL1.
Destination Database Select New Database (Specify Data File, Log File Storage Path) or application existing database
Database
Load
State
No recovery mode: The user will not be able to perform a data query, only for future use.
Standby Mode: Set to read-only mode, you can query as long as you do not log back.
TERMINATEERS IN DATABASE (Recommended): When the database or transaction log file, the repository will be the unique user of the database.
Allow Database To Assume PRIMARY ROLE: Allows role conversion between the primary server and the secondary server.
Select the shared directory path of the new primary server after role conversion.
o
9. Initialize The Destination Database: Pick the most recent data or establish a new backup information. For large databases, it is more efficient to use backup. However, to ensure that log sharing directories from backups are present on the log sharing directory on the primary server.
10. Set the log backup frequency on the primary server.
11. Set the auxiliary server to copy the backup log and load the frequency of the backup log, and the log file in the secondary server.
12. Set a reasonable delay time for log backup and log backup, when exceeding critical time, the log Transfer Monitor dialog will respective warning information. 13. Specify the monitoring server, you should specify the third server independent of the primary server, the auxiliary server as the monitor server, or specify the secondary server to monitor the server.
14. Click Next to specify the name of the maintenance plan. Finish, started to create log shipping.
Monitor
1. Log Shipping is created, and the log shipping related information is stored in the 7 tables of MSDB:
Log_Shipping_Plans
LOG_SHIPPING_PLAN_DATABASES
Log_shipping_databases
LOG_SHIPPING_PLAN_HISTORY
LOG_SHIPPING_MONITOR
LOG_SHIPPING_PRIMARIES
Log_shipping_secondaries
2. You can see status information of Log Shipping backup, copy, load, etc. in MANGEMENT.
Delete log shipping
1. Select the log shipping maintenance plan on the primary server, open the property, select the [log shipping] setting page, then click [Remove Log Shipping]. This action will remove the backup and reposition of the SQL Server Agent from the secondary server, and clear all relevant information in the log transfer data. In addition, information about the log transfer monitor will also be cleared. However, this action will properly reserve the trading log backup of the SQL Server Agent on the primary server. This work will only be removed only when the database maintenance plan is deleted. If you want to remove the log transfer monitor from the monitoring server, use the data of the log_shipping_primaries with Log_Shipping_Primaries with Log_Shipping_Secondarie (in the MSDB database of the monitoring server).
If you set the log transfer in the Database Maintenance Plan, you have allowed the destination database to be used as a new log transfer source database. When you delete the main server's maintenance plan, the secondary server will still retain its database maintenance plan, as well as the transaction log file backup work. The way to delete these items is to delete the database maintenance plan related to the log transfer on the secondary server.
Role Alter
1. Create a login synchronization DTS package on the primary server.
2. Open Enterprise Manager and connect to the primary server. Expand Enterprise Manager Tree to "Data Transformation Services" group, select "Local Packages". Right click "Local Packages" and select "New package". Select "16 Transfer Logins Task" from the "Task" menu. In the source selection primary server, the destination tab select the secondary server. In the Logins tab, select Transfer to the login associated with a specific database, or transfer all logins of the server. (Recommended for our environment, transfer all logins of the server)
3. Save the DTS package in the primary server.
3. Specifies the DTS synchronization time (at least once a week).
Synchronous login account SID
1. BCP MASTER..SYSLOGINS OUT LOCALPATH / SYSLOGINS.DAT / N / S CURRENT_PRIMARY_SERVER / U SA / P SA_PASSWORD.
The exported Syslogins information will be used later.
2. Dowstrate the main server. Run the following stored procedure at the primary server.
Use master
EXEC MSDB..SP_CHANGE_PRIMARY_ROLE @ DB_NAME = 'current_primary_dbname'
@Backup_log = 1,
@Terminate = 1,
@Final_State = 3,
@access_level = 1
3. Upgrade auxiliary server. Run the following stored procedures in the secondary server.
Use master
EXEC MSDB..SP_CHANGE_SECONDARY_ROLE
@db_name = 'current_secondary_dbname'
@DO_load = 1,
@force_load = 1,
@Final_State = 1,
@access_level = 1,
@Terminame = 1,
@keep_replication = 0,
@stopat = null
This stored procedure will use the database quality as a single user mode. Obviously, no user is accessing the database, but tells me the database currently in use, the solution is re-executed once.
4. Notify the monitoring server role has changed and run the following stored procedure on the monitor server.
Use master
EXEC MSDB..SP_CHANGE_MONITOR_ROLE
@Primary_server = 'current_primary_server_name',
@secondary_server = 'current_secondary_server_name',
@Database = 'current_secondary_dbname',
@new_source = 'new_source_directory'
5. Resolution login account on the secondary server
Use master
EXEC SP_RESOLVE_LOGINS
@DEST_DB = 'DBNAME',
@DEST_PATH = 'Destination_Path',
@FileName = 'filename' (from step 1 export)
6. Connect data inventory and permissions. Connect the login account that is parsed to the corresponding database user and its permissions. (SQL Book Online is missing this step)
Use SourceName
EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'UserName', 'Loginname'
Role change
1. Remove the log transfer function in the database maintenance plan of the new primary server.
2. Delete the database maintenance plan on the main server.
3. Delete the Database Maintenance Plan on the secondary server.
4. Maintain all transaction logs.
5. Create a new database maintenance plan on the new primary server, specify the appropriate storage location of the new secondary server, the destination database location, and the transaction log.
6. Restart all activities of the new primary server.
