SQL SERVER log shipping, setup, monitoring, role transfer

server| Monitoring
















Log Shipping
Operations 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 both SQL1 and SQL2 start the SQL Server service and the SQLServerAgent service using a domain account.

2. Delete the local connection in Enterprise Manager, apply server name to register the tonym, secondary server Tonym02

3. Create a new shared folder NorthwindBackupShare01 on the SQL1 server, giving full permission to start the SQL Server account. Create a new folder on the SQL1 server Receivesql2logs, which is used to receive logs sent from SQL2 when the database role is converted.

Create a new shared folder NorthwindBackupShare02 on the SQL2 server, giving full permission to start the SQL Server account. Create a new folder on the SQL2 server receivesql1logs to receive the log that the database SQL1 uploaded.



4. Set the server that you want to apply log shipping as the full recovery model.

5. On the database maintenance plans, right-click on New maintenance plan, select the databases to be logshipping, allowing only one database to be selected at a time.



6. Get rid of the "back" database as part of the "Maintenance plan" to ensure maintenance schedule uniqueness (recommended)



7. Specifies the database log backup path.



8. Specifies the shared folder where the log files are stored.



8. Add the destination database.

Server name is the destination name

Transaction Log Destination Directory fills in the receive path from the SQL1 to the log file on SQL2.

Destination DB Select New database (Specify data file, log file storage path) or apply existing database

Database Load State

No recovery mode: The user will not be able to query the data, only for backup use.

Standby mode: Set to read-only mode, can be queried as long as it is not logged back.

Terminate users in Database (recommended): The callback program will be the only user of the database when you are saving the databases or transaction log files.

Allow database to assume primary roles: Allows role conversions between primary and secondary servers.

Select the shared directory path for the new primary server after role conversion.

O

9. Initialize The destination database: Pick the latest data or create a new backup. For large databases, it is more efficient to use backups. However, make sure that the logs from the backup are present in the log share directory on the primary server.



10. Set the frequency of log backups on the primary server.



11. Set the frequency at which secondary servers replicate backup logs and load backup logs, and the retention time of log files on secondary servers.



12. For log backup and log back up work, set a reasonable delay time, when the critical time is exceeded, the Log Shipping Monitor dialog box will be a corresponding warning message.



13. Specifies the monitoring server, which should be specified independently of the primary server, the secondary server as the monitoring server, or a secondary server as the monitoring server.



14. Click Next to specify the name of the maintenance plan. Finish to start the log shipping creation.
Monitor
1. After the log shipping is created, the information associated with log shipping is stored in the 7 tables in 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 the status information of log shipping backup, copy, load and so on under the management of the monitor server.


Delete Log Shipping
1. Select the log Shipping maintenance plan on the primary server, open the properties, select the Log Shipping Settings page, and then click Remove Log Shipping. This action removes the backup and recall of the SQL Server Agent from the secondary server and clears all relevant information from the Log Shipping table. In addition, information about the log shipping monitor is also cleared. However, this action will appropriately retain the SQL Server Agent's transaction log backup work on the primary server. The work is removed only when the database maintenance plan is deleted. If you want to remove the log shipping monitor from the monitor server, manually remove the data from the log_shipping_primaries and Log_shipping_secondarie tables (the msdb database at the monitor server).



If you set up log shipping within a database maintenance plan, you have allowed the destination database to be a new log shipping source database. When you delete a maintenance plan for a primary server, the secondary server retains its database maintenance plan and the transaction log file backup work. The way to delete these items is to delete the database maintenance plan associated with log shipping on the secondary server directly.


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 the Data transformation Services Group and select Local Packages. Right-click "Local Packages" and select "New Package". Select the Transfer logins Task from the Task menu. Select the primary server at the source and the Destination tab to select a secondary server. On the Logins tab, select the login that the transport is associated with a particular database, or all the landings for that server. (For our environment, it is recommended to use all landings that transmit this server)



3. The DTS package is guaranteed to exist as a primary server.



3. Specifies the DTS synchronization time (at least once a week).



Sync login account SID

1. BCP Master. syslogins out LocalPath yslogins.dat/n/S current_primary_server/u sa/p Sa_password.

The exported syslogins information will be used later.

2. Demote the primary server. Run the following stored procedure on 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 the secondary server. Run the following stored procedure at 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

The stored procedure will have the database quality as Single-user mode. Obviously no user is accessing the database, it tells me that the database is currently in use and is resolved in a way that the stored procedure is executed again.

4. Notifies the monitoring server that the role has changed and runs the following stored procedures on the monitoring 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. Resolve login account on secondary server

Use master

Exec sp_resolve_logins

@dest_db = ' dbname ',

@dest_path = ' Destination_path ',

@filename = ' filename ' (from step 1 export)

6. Connect database access and permissions. Link the resolved login account to the corresponding database user and its permissions after the transfer. (This step is missing from SQL book Online)

Use SourceName

Exec sp_change_users_login ' update_one ', ' username ', ' LoginName '
Role change
1. Remove log shipping from the database maintenance plan for the new primary server.

2. Delete the database maintenance plan on the primary server.

3. Delete the database maintenance plan on the secondary server.

4. Maintenance of all transaction logs.

5. Establish a new database maintenance plan on the new primary server, specifying the location of the new secondary server, the destination database, and the appropriate storage location for the transaction log.

6. Restart all activities for the new primary server.

After you successfully set up a role swap and build a new log shipping pairing server, Enterprise Manager's log shipping monitor may tell you that the new secondary server database has not been synchronized with the new primary server database (out of sync). You receive this report if the time lag between the last loaded transaction log and the last backup transaction log exceeds the Out-of-sync set value. You will need to copy the backup log of the new home server to the new secondary server's sync backup path. After the most recent backup data has been loaded, the log shipping monitor returns to the normal error-Free state.