Log-shipping is one of the disaster recovery solution. Log-shipping copies transactions from 'primary database' to one or more 'secondary databases' by sending transaction log backups and then restore to the destination databases individually. If the primary database becomes unavailable any of the secondary database can brought into online manually.
To work with Log-shipping we required 3 servers i.e Primary Server , Secondary Server and optional monitor server.
1. Primary Server :
* Primary Server holds the original copy of the database .
* Log shipping configuration and administration will be done from primary server.
* In primary server the database in in online state where the applications or users are connected.
* In primary server , the database called primary database and its recovery model must be full or bulk-logged.
2. Secondary Server :
* The server which maintains the copy of the original database is called as the secondary server.
* We can maintain multiple secondary servers in Log-shipping configuration.
* In secondary server the database state must be either restoring or stand by mode.
* User can have read-only access if the secondary database configured in Stand By option.
* Database recovery model must be Full or Bulk-Logged.
3. Monitor Server :
* Monitor server is an optional server which records status of backup , copy & restore operations and raises alerts if any job fail.
Advantages :
* To reduce down time for applications .
* To implement load balancing i.e we can allow reads from server and manipulations from primary server.
Disadvantages :
There is no automatic fail over i.e If primary server fails then secondary server will not come to on-line.
There might be a data loss if T.Log is damaged.
There is no immediate shrink to the secondary server.
Once you create Log shipping the below jobs will be created and handled by SQL Server Agent.
1. Backup Job :
* Backup job performs transaction log backups from the primary database and placed into shared folder.
* It is created and executed in Primary Server.
2 . Copy Job :
* Copy job performs copy the transaction log backups from shared folder to secondary server folder.
* Copy job is created and executed in Secondary Server.
3. Restore Job :
* Restore job performs restoring transaction log backups onto secondary database .
* Restore job is created and executed in Secondary Server.
4. Alert Job :
* Alert job raises alerts if any backup , copy and restore jobs fails.
* Alert job will be created and executed in Monitor Server.
Prerequisites :
* SQL Server 2005 Standard Edition , Workgroup Edition or Enterprise Edition must be installed on all server instances involved in Log-shipping.
* All servers should have same case sensitivity settings.
* The databases recovery model must be either Full or Bulk-Logged.
* To configure Log-shipping we must have Sysadmin on each server instance.
Configuring Log-shipping :
* Choose Primary Server , Secondary Server and optional Monitory Server.
* Create a folder to hold backup files of primary database and grant read-write permission on this folder to the account on which sql server services are running.
* Create one more folder in secondary server to copy the backup files from the primary and share this folder to the proxy account by giving read-write permission in secondary server.
* Go to Primary server --> Right Click on required database --> Tasks --> Ship Transaction Log.
* Select check box "Enable this as Primary Database"
* Click on Backup settings --> Enter bacup folder path = "\\SystemName\\BackupFolderName"
* Click on schedlue change the time to five monutues --> ok
* Under secondary database "Click on Add Button"
* Select secondary server instance and click on connect button.
* Select the option "No the secondary database is Initialized"
* Click on copy file tab --> Enter destination folder as "\\SystemName\\CopyfolderName"
*Click on schedule change the time to 5 minutes.
* Click on Restore trandaction log tab.
* Select "standby mode" option and check box "Disconnect"
* Click on schedule change time and keep 5 minutes then OK --> OK
* Under the monitor server instance select the option to user a monitore server instance.
* Click on settings --> click on connect --> Select the required monitor server instance name.
* Click on ok-->ok.
Observations :
* Go to Primary Server --> SQL Server Agent --> Jobs --> View the backup job with the name "Backup folder name" which is created in primary database.
* Three Alerts are created in Alerts folder with the names
* Log Shipping Primary Server Alert
* Log Shipping Secondary Server Alert
* Log Shipping Monitor Server Alert.
* In secondary server verify that 2 jobs are created.
* Copy (LS_Copy_Sales)
* Restore (Ls_Restore_Sales)
* All the above jobs (B,C&R) uses "SQL Logship.exe) file.
* Alert jobs calls sys.sp_check_log_shipping_monitor_alert.
* SQLMain.exe is responsible for updating backup , copy & restore information in the monitor server.
Monitoring Log-shipping :
We can monitor Log Shipping in 3 ways i.e
1. Using MSDB Tables.
2. Using Log Shipping Status Report
3. Using Job History
1. Using MSDB Tables and Views :
Go to Primary Server --> MSDB
1. Log_Shipping_Primary_databases
It consists of information related to Log Backups Folder Path & Last Log Backup File Name.
2. Log_Shipping_Primary_Secondaries
It consists of information related to secondary server name and database name
Go to secondary server --> MSDB
1. Log_Shipping_Secondary
It consists of information related to Copy folder and Last copied file.
2. Log_Shipping_Secondary_databases
It consists of information related to last restore file.
Performing Fail Over :
Process of making secondary database available to the applications or users is called fail over when primary server / db failed.
Log Shipping failover process is not automatic.
1. Perform one last T.Log backup in the primary server if possible.
2. Disable Log Shipping Jobs.
3. Restore if any backups are not applied at secondary server WITH NO RECOVERY.
4. Restore tail log backup WITH RECOVERY.
5. Remove Log shipping configuration from Primary Server.
6. Select any one of secondary server available to the users by bring into online with alter database database name set online
7. Right click on primary database and generate scripts for users and logins.
8. Then move the script to secondary server to create users and logins .
9. Re configure log shipping from new server i.e secondary server.
To work with Log-shipping we required 3 servers i.e Primary Server , Secondary Server and optional monitor server.
1. Primary Server :
* Primary Server holds the original copy of the database .
* Log shipping configuration and administration will be done from primary server.
* In primary server the database in in online state where the applications or users are connected.
* In primary server , the database called primary database and its recovery model must be full or bulk-logged.
2. Secondary Server :
* The server which maintains the copy of the original database is called as the secondary server.
* We can maintain multiple secondary servers in Log-shipping configuration.
* In secondary server the database state must be either restoring or stand by mode.
* User can have read-only access if the secondary database configured in Stand By option.
* Database recovery model must be Full or Bulk-Logged.
3. Monitor Server :
* Monitor server is an optional server which records status of backup , copy & restore operations and raises alerts if any job fail.
Advantages :
* To reduce down time for applications .
* To implement load balancing i.e we can allow reads from server and manipulations from primary server.
Disadvantages :
There is no automatic fail over i.e If primary server fails then secondary server will not come to on-line.
There might be a data loss if T.Log is damaged.
There is no immediate shrink to the secondary server.
Once you create Log shipping the below jobs will be created and handled by SQL Server Agent.
1. Backup Job :
* Backup job performs transaction log backups from the primary database and placed into shared folder.
* It is created and executed in Primary Server.
2 . Copy Job :
* Copy job performs copy the transaction log backups from shared folder to secondary server folder.
* Copy job is created and executed in Secondary Server.
3. Restore Job :
* Restore job performs restoring transaction log backups onto secondary database .
* Restore job is created and executed in Secondary Server.
4. Alert Job :
* Alert job raises alerts if any backup , copy and restore jobs fails.
* Alert job will be created and executed in Monitor Server.
Prerequisites :
* SQL Server 2005 Standard Edition , Workgroup Edition or Enterprise Edition must be installed on all server instances involved in Log-shipping.
* All servers should have same case sensitivity settings.
* The databases recovery model must be either Full or Bulk-Logged.
* To configure Log-shipping we must have Sysadmin on each server instance.
Configuring Log-shipping :
* Choose Primary Server , Secondary Server and optional Monitory Server.
* Create a folder to hold backup files of primary database and grant read-write permission on this folder to the account on which sql server services are running.
* Create one more folder in secondary server to copy the backup files from the primary and share this folder to the proxy account by giving read-write permission in secondary server.
* Go to Primary server --> Right Click on required database --> Tasks --> Ship Transaction Log.
* Select check box "Enable this as Primary Database"
* Click on Backup settings --> Enter bacup folder path = "\\SystemName\\BackupFolderName"
* Click on schedlue change the time to five monutues --> ok
* Under secondary database "Click on Add Button"
* Select secondary server instance and click on connect button.
* Select the option "No the secondary database is Initialized"
* Click on copy file tab --> Enter destination folder as "\\SystemName\\CopyfolderName"
*Click on schedule change the time to 5 minutes.
* Click on Restore trandaction log tab.
* Select "standby mode" option and check box "Disconnect"
* Click on schedule change time and keep 5 minutes then OK --> OK
* Under the monitor server instance select the option to user a monitore server instance.
* Click on settings --> click on connect --> Select the required monitor server instance name.
* Click on ok-->ok.
Observations :
* Go to Primary Server --> SQL Server Agent --> Jobs --> View the backup job with the name "Backup folder name" which is created in primary database.
* Three Alerts are created in Alerts folder with the names
* Log Shipping Primary Server Alert
* Log Shipping Secondary Server Alert
* Log Shipping Monitor Server Alert.
* In secondary server verify that 2 jobs are created.
* Copy (LS_Copy_Sales)
* Restore (Ls_Restore_Sales)
* All the above jobs (B,C&R) uses "SQL Logship.exe) file.
* Alert jobs calls sys.sp_check_log_shipping_monitor_alert.
* SQLMain.exe is responsible for updating backup , copy & restore information in the monitor server.
Monitoring Log-shipping :
We can monitor Log Shipping in 3 ways i.e
1. Using MSDB Tables.
2. Using Log Shipping Status Report
3. Using Job History
1. Using MSDB Tables and Views :
Go to Primary Server --> MSDB
1. Log_Shipping_Primary_databases
It consists of information related to Log Backups Folder Path & Last Log Backup File Name.
2. Log_Shipping_Primary_Secondaries
It consists of information related to secondary server name and database name
Go to secondary server --> MSDB
1. Log_Shipping_Secondary
It consists of information related to Copy folder and Last copied file.
2. Log_Shipping_Secondary_databases
It consists of information related to last restore file.
Performing Fail Over :
Process of making secondary database available to the applications or users is called fail over when primary server / db failed.
Log Shipping failover process is not automatic.
1. Perform one last T.Log backup in the primary server if possible.
2. Disable Log Shipping Jobs.
3. Restore if any backups are not applied at secondary server WITH NO RECOVERY.
4. Restore tail log backup WITH RECOVERY.
5. Remove Log shipping configuration from Primary Server.
6. Select any one of secondary server available to the users by bring into online with alter database database name set online
7. Right click on primary database and generate scripts for users and logins.
8. Then move the script to secondary server to create users and logins .
9. Re configure log shipping from new server i.e secondary server.
No comments:
Post a Comment