Log Shipping it is possible to implement between two databases on the same instance. Log shipping has the responsibility to keep the primary database synchronized with all changes, transactions performed with secondary database. However, it is not recommended because if it loses the instance, you will also miss the primary and standby database. Log shipping can certainly be included in the plan of backup / restore databases, is a very good chance of retention of data from various disaster scenarios that might occur, it in automatically saves a copy of the log in an other instance, server.
Log shipping is perform or implemented at the database level between primary database and secondary database. Also, it has a opportunity to configure a monitor server, that shows and verify the health of the log shipping session and if any errors happened it can send a notifications.
These are the ways in which you can implement log shipping within your environment.
- You can use log shipping at report server and maintain it, within the Select statement through secondary copy.
- When we have initializing a database mirroring session is to implement log shipping to get the principal and mirror very close in time from disconnected to bring online the database mirroring session.
- Side- by – Side Upgrading or Mirroring to New Platform is considered a migration of data in a different platform. If requires downtime to not be a long for the application.
- Log shipping is implemented together with failover clustering, database mirroring to have a second location for the data and management mechanism of collapse.
INFO: Log shipping relies on jobs being run by the SQL Agent service. If that service is not running, log shipping is not running. For Log Shipping is not needed any special hardware, but only that it is necessary to configure backup server like production server.
When you configure log shipping three jobs are be created:
The backup job – runs in primary
The copy job – usually configured on secondary
The restore job – runs in secondary
Care for the objects before, during implementation
Common objects at the instance-level, which is required to be copied in secondary are:
Security objects – Master key, Certificates, Logins used to grant access to all these should be done backup and to copied in the secondary database. As regards logins SSIS has a good opportunity to realize the transfer of logins from one instance to another.
Linked servers – have two components that you need to be aware of ODBC and OLEDB. The linked server also needs to re-create the access credentials correct.
SSIS – each package which depends on the primary database must be re-created in the secondary.
Endpoints – used to control different approaches to instance or database. Each endpoints should be re-created in secondary with STATE=STOPPED.
SQL Server Agent objects – any jobs that depend upon the primary database have to be re-created on the secondary.
DDL triggers – however, need to be re-created on the secondary.
Replication – primary database can be configured as a publisher or subscriber in replication. As we know the name of the database is usually the same as in secondary, only the instance name is different. Although all the replication objects are in the secondary database, they are not usable. You should plan a reconfiguration of the architecture of replication on secondary then it will be online.
Each instance-level object that is copied must be maintained in secondary.
Hirt Allan, Pro SQL Server 2008 Failover Clustering, ISBN-13:978-1-4302-1967-5
Wiley Publishing, Inc., Professional Microsoft® SQL Server® 2008 Administration, ISBN:978-0-470-24796-9