Contents
Create empty databases on the secondary server
Create folders for transaction logs
On the primary
On the secondary
Export logins
Review Maintenance Plans
Enable TCP/IP Protocols
Configure Log Shipping
On the primary
Configure Alerts
On the primary
On the secondary
Verify Setup
Create empty databases on the secondary server
1.
In SQL Server Management Studio, right click the
server and choose new database
2.
Be sure to configure the file locations to the
same location as on the primary
Create folders
for transaction logs
In a domain environment, make sure the SQL Service is
running as a domain login. When you
configure the share give this user full control. In a non-domain environment use the same
username and password on both the primary and secondary. This will create pass through authentication:
http://support.microsoft.com/kb/321247
http://support.microsoft.com/kb/321247
On the primary
1.
Create a folder for the transaction
logs on the Primary server:
2.
Configure this as a shared folder
On the secondary
1.
Create a folder for the transaction
logs on the Secondary server:
2.
Configure this as a shared folder named
Export logins
from the primary
1.
Use a query to export logins from the primary server.
2.
Run script on secondary, to create logins on the secondary. Make sure “Create empty
databases on the secondary server” has been completed first.
Review Maintenance
Plans
Transaction log
backups should not be running on the primary.
Backing up the transaction log independently will disrupt the log shipping
process.
1. Remove log shipped databases form any
transaction log backup maintenance plan or job. If the log shipping databases are the only FULL recovery model databases, remove the Maintenance plan
or job.
2. If there is a Maintenance plan to remove transaction
log files after x number of hours on the primary, remove any log shipped
databases from the maintenance plan.
Enable TCP/IP Protocols
Configure Log
Shipping
On the primary
In SQL Server Management Studio, right click the database to configure and click properties
1. Click Enable this as a primary database in a log shipping configuration
2. Click Backup Settings
a. Enter a network path to the backup folder by IP (EX: \\10.10.10.10\LS)
b. If this is a local folder enter the folder location here
c. Configure retention for Transaction logs here, default is 72 hrs
3. Click Add under, Secondary server instances and databases
a. Under Initialize Secondary Database - MS SQL server will backup and restore the primary database to the secondary server.
i. Choose yes; generate a full backup of the primary database.
OR
ii. Choose yes; restore an existing backup o the primary database.
Use option 1 for smaller databases. If you use option 2 be sure to take a full backup and copy this to the secondary, entering the local path under Backup File”
b. Under Copy Files enter the location created in "Create folders for transaction logs"
c. Under Restore Transaction Log choose from:
i. No recovery mode - use for availability reasons only
ii. Standby mode - read-only access is allowed, used for scalability. If you chose the Standby mode option, click the radio button to disconnect users from the secondary database while the restore operation is underway.
4. If a monitor server is required, click use a monitor server instance and then click settings. Configure the instance connection in the resulting dialog box.
Configure
Alerts for replication job failure
The following jobs and alerts should be automatically created during
setup. You will need to manually set the
notifications to Email, your operator, when the job/alert fails:
On the primary
Jobs
1.
LSAlert_[primary server name]
2.
LSBackup_[database name]
Alerts
3.
Log shipping Primary Server Alert.
4.
Log shipping Secondary Server Alert.
On the
secondary
Jobs
1.
LSAlert_[secondary server name]
2.
LSCopy_[primary server name]_[database name]
3.
LSRestore_[primary server name]_[database name]
Alerts
4.
Log shipping Primary Server Alert.
5.
Log shipping Secondary Server Alert.
Verify Setup
1. Review the error log
2. Verify trn files in the location created in "Create folders for transaction logs" on both the primary and secondary servers.
3. View the SQL Server Agent, Job Activity Monitor. You should see the copy and restore jobs completing successfully.
4. View the Log Shipping Report – this can be done from the primary, secondary or monitor server.
a. Right-click the server instance in Object Explorer, point to Reports, and point to Standard Reports.
b. Click Transaction Log Shipping Status.
2. Verify trn files in the location created in "Create folders for transaction logs" on both the primary and secondary servers.
3. View the SQL Server Agent, Job Activity Monitor. You should see the copy and restore jobs completing successfully.
4. View the Log Shipping Report – this can be done from the primary, secondary or monitor server.
a. Right-click the server instance in Object Explorer, point to Reports, and point to Standard Reports.
b. Click Transaction Log Shipping Status.
No comments:
Post a Comment