SQL Server 2014 Log Shipping Deployment (4): SQL Server Management Studio deployment log Shipping

13.2.4 Deploying log shipping with Management Studio

Use SQL Server Management Studio to deploy the log shipping steps as follows:

(1) Open the Properties page for the primary database DB01 as log shipping in the primary server SQLSVR1, and then select Transaction Log Shipping. Select the Enable this database as the primary database in the log shipping configuration (E) check box.

(2) Click "Backup Settings";

1. Fill in the "Backup folder network path" for \\192.168.1.20\backlog;

2. "If the backup folder is located on the primary server, then the local path to the file is entered" is optional; if the log backup folder is created on the master server SQLSVR1;

3. "Delete the file if it has been retained for more than:" This feature will delete the transaction log backup files over a specified time to avoid large shared folders that hold backup logs. The default is 72 hours.

4. "Alert when backup is not performed in the following time:" This feature monitors if the primary database does not perform a backup during the monitored time, then sends an alert to the operator. The default setting is 1 hours.

5. Under Backup jobs, click Schedule to go to the Job Schedule Properties page to set a schedule for transaction log backups for the primary database. It is important that the "execution interval", the default is 15 minutes to make a transaction log backup, can be based on the specific business needs of the job schedule to make the appropriate configuration; When the configuration is complete, click OK to return to the transaction log backup Settings page.

6. The last item in the "Transaction Log backup Settings" page is set to "set backup Compression", and if the "Do Not compress backup" box is selected, the new backup is not compressed during the log backup. If the Compress backup box is selected, a new backup is compressed when the log backup is made. If Use default server settings is selected, the operation is based on the values set at the server level (view server-level settings on the Database Settings page of the properties of the primary DB instance, and the default is to not compress the backups). Compressing a backup increases the CPU overhead of the server, and whether or not to turn on a compressed backup is based on the actual server capacity, business volume, backup size, and backup frequency to consider the decision.

When the configuration is complete, click OK to return to the transaction log shipping Properties page.

(3) on the Transaction Log Shipping Properties page, click the Add button to display the secondary database settings and set up a secondary server in this dialog box. Click Connect to select a secondary server instance. This is select SQLSVR2 as the secondary server, the secondary server defaults to the primary database name.

On the Initialize Secondary Database tab page, there are three options to specify how backups are restored to the secondary server:

1. "Yes, generate a full backup of the primary database and restore it to the secondary database (create it if the database does not exist)", and "restore Options" to set the location of the database folder where the data files and log files are stored. If there is no setting, the default set of database locations is used.

2. "Yes, restore an existing backup of the primary database to the secondary database (create it if the database does not exist)" or "restore Options" to set the location of the database folder where the data files and log files are stored. If there is no setting, the default set of database locations is used. With this option, users need to manually perform a full backup of the primary database and specify the network path where the primary database resides.

3. "No, secondary database is initialized"; This option means that a secondary database has been created on the secondary server and that the transaction log before the database restore must be available for log shipping to work properly. In addition, the secondary database must be in NORECOVERY and Standby mode to allow additional transaction log files to be applied.

(4) in the "Copy Files" tab page, "Copy files to the destination folder" fill in the destination folder, where the initial configuration of the secondary server created on the "D:\copylog". After the transaction log is restored to the secondary database, delete the copied files at the following time to control the size of the target file, delete the file that is older than the specified time, and the default is 72 hours.

Under Copy Job, click the Schedule button to set the execution schedule for the replication job. The important setting is the "execution interval", which is 15 minutes by default. You can also adjust this time size so that the secondary server data synchronization is closer to the primary database. When you click OK, return to the Secondary Database Settings property page.

(5) On the Secondary Database Settings page, click the Restore Transaction Log tab. In the "Two options for restoring the database state of a backup:

1. No recovery mode. The secondary database is in the NORECOVERY state, allowing the secondary database to restore the transaction log, but does not allow users to read and write to the secondary database.

2. Standby mode. The secondary database is in the standby state, allowing the user to read-only operations on the secondary database. In standby mode, you need to confirm that you are disconnecting users from the database while restoring a backup, because the restore process will monopolize the secondary database. If unchecked, the restore process does not execute when a user is accessing the secondary database at the point of restore, and the restore process interrupts the user connection if the user is accessing the secondary database at a restore point in time.

The default setting for delayed backup operation at least is 0 minutes, and in general, this setting needs to be modified if the business needs a secondary database to avoid or reduce the impact of data that is compromised in the primary database. This setting can postpone the restore process and give the technician time to prevent the secondary database from restoring the transaction log files that contain the corrupted data.

The "alarm when no restore is performed in the following time" is set to 45 minutes by default and needs to be set according to the tolerance of the enterprise business.

Under Restore job, click the Schedule button to set the timesheet table for the restore job. The important setting is the "execution interval", which is 15 minutes by default. When you click OK, return to the Secondary Database Settings property page.

(6) On the Secondary Database Settings property page, click OK to return to the transaction log shipping page.

Tips: If you need to add additional secondary databases, click the Add button and follow steps one by one above.

(7) If you need to add a monitoring server, on the transaction log shipping page, tick the monitor server and click the Settings button to go to log Shipping monitoring server settings.

(8) on the log Shipping Monitoring Server Settings page, click the Connect button to specify SQLSVR3 as the monitoring server.

In the proxy account for impersonation job or use the following SQL ServerD logins, select how the Backup, copy, and restore jobs connect to the service instance to update the job history information in the msdb database. Whichever you choose, the account you specify must have sysadmin role permissions on the secondary server.

Delete history After: Specifies the size of historical data saved in the msdb database, with a default value of 96 hours. This setting depends on the business requirements and disk size in the actual production environment.

Click OK to return to the transaction log shipping page.

(9) After the setup is complete, click "OK" on the "Transaction log Shipping" page to make the log shipping configuration;

When the display succeeds, click Close to complete the deployment of log shipping.

Tips: When you do not need to deploy the monitoring Server (7), (8) Two steps do not need to operate

This article is from the "Margin with Wish" blog, please be sure to keep this source http://281816327.blog.51cto.com/907015/1598313

SQL Server 2014 Log Shipping Deployment (4): SQL Server Management Studio deployment log Shipping