1 / 51

Database mirroring

ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS. Database mirroring. Database Mirroring. What is it?. Is a mixture of replication and log shipping

nuwa
Download Presentation

Database mirroring

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS Database mirroring

  2. Database Mirroring. What is it? • Is a mixture of replication and log shipping • It moves database transactions from one SQL Server database to another SQL Server database on a different instance of SQL Server. • This mirrored copy is a standby copy and can not be accessed directly; it is used only for a failover situation Database Mirroring

  3. Database Mirroring: How does it work? • At least 2 instances of SQL Server • Primary server is the "principal" • This is the LIVE database • Secondary server is the "mirror.“ • This is the STANDBY • As transactions are written to the principal database they are also sent and written to the mirrored database. Database Mirroring

  4. Three modes of operation • High Availability: • synchronizes transaction writes on both servers and enables automated failover. • Needs a witness server to act as arbiter. • High Protection: • synchronizes transaction writes on both servers, but failover is manual. • High Performance: • Doesn’t care if the writes are synchronized on both servers, so is faster. • assumes that everything is going to complete successfully on the mirror. Database Mirroring

  5. Relative merits • Automatic failover to a secondary copy of your data is the real benefit of Database Mirroring. • So most implementation will probably use the High Availability mode. • The other options still offer a built-in process for failing over, but require manual intervention Database Mirroring

  6. The process • Without Mirroring: • When a user makes a change to a database: • Change is stored in the Log Buffer • Log Buffer is written to the Transaction Log • This is called Hardening • Changes that have been written to the Transaction Log are Flushed (written) to the database Database Mirroring

  7. The process • With Mirroring: • When a user makes a change to a database: • SQL Server stores the change in the database’s log file • Log buffer is written to the transaction log AND principal server copies log buffer to the mirror server • Mirror server hardens the transaction log • Changes are then flushed to the principal server’s database • Changes in the transaction log on the mirror server are replayed against the mirror database, making it a copy of the principal Database Mirroring

  8. Practical exercise

  9. First we shall produce a copy on the mirror server by backing up the sales database from the principal machine • Then we shall set up the mirror environment Database Mirroring

  10. The backup Database Mirroring

  11. Backup the Database File Note name and directory for backup Database Mirroring

  12. Backup the Transaction Log Database Mirroring

  13. Restore the backup Database Mirroring

  14. Database Mirroring

  15. Database Mirroring

  16. Database Mirroring

  17. Set up the mirroring Database Mirroring

  18. Click on Configure Security Database Mirroring

  19. Database Mirroring

  20. Database Mirroring

  21. Leave accounts clear Database Mirroring

  22. Database Mirroring

  23. Database Mirroring

  24. Don’t start Mirroring Database Mirroring

  25. Log Shipping

  26. A note about recovery models • full recovery, • simple recovery and • bulk-logged recovery. • The recovery models determine how much data loss is acceptable in case of a failure and what types of backup and restore functions are allowed. Log Shipping

  27. Simple • The simple recovery model allows you to recover data only to the most recent full database or differential backup. Transaction log backups are not available because the contents of the transaction log are truncated each time a checkpoint is issued for the database. Log Shipping

  28. Full • The full recovery model uses database backups and transaction log backups to provide complete protection against failure. Along with being able to restore a full or differential backup, you can recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX and bulk-loading data, are fully logged and recoverable. Log Shipping

  29. Bulk Logged • Bulk-Logged • The bulk-logged recovery model provides protection against failure combined with the best performance. • SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations are not logged • Under the bulk-logged recovery model, a damaged data file can result in having to redo work manually based on the operations that are not fully logged Log Shipping

  30. Use Simple when • Your data is not critical. • Losing all transactions since the last full or differential backup is not an issue. • Data is derived from other data sources and is easily recreated. • Data is static and does not change often. • Space is limited to log transactions. (This may be a short-term reason, but not a good long-term reason.) Log Shipping

  31. Bulk Logged when • Data is critical, but logging large data loads bogs down the system. • Most bulk operations are done off hours and do not interfere with normal transaction processing. • You need to be able to recover to a point in time. Log Shipping

  32. Full when • Data is critical and no data can be lost. • You always need the ability to do a point-in-time recovery. • Bulk-logged activities are intermixed with normal transaction processing. • You are using replication and need the ability to resynchronize all databases involved in replication to a specific point in time. Log Shipping

  33. Log Shipping • Copies transactions from a Primary Server to one or more Secondary Servers. Log Shipping

  34. Server roles in Log Shipping • Primary • The production server • All log shipping takes place on this machine • Secondary • Holds the standby copy of the database • Initialise by restoring a backup from the primary • Use NORECOVERY or STANDBY option • STANDBY allows users read-only access to the database while backups are restored • Monitor • Tracks log shipping details Log Shipping

  35. Jobs • Four jobs are involved in Log Shipping • Backup • Runs on primary server every 2 mins by default • Copy • Runs on each secondary server just after the backup completes • Restore • Restores on the secondary servers • Alert • Optional – runs on the monitor server • Raises alerts on primary and secondary servers Log Shipping

  36. Mirroring vs Log Shipping Log Shipping

  37. Log Shipping Practical Exercise

  38. Change the Recovery model to FULL • Backup the AdventureWorks database from the principal and restore it on the secondary server • Ship the logs to the second instance Log Shipping

  39. Changing the Recovery Model Log Shipping

  40. Backup the database Log Shipping

  41. Backup the Transaction Log Log Shipping

  42. Restore the database to the secondary server Log Shipping

  43. Configure Log Shipping Log Shipping

  44. Log Shipping

  45. Log Shipping

  46. Log Shipping

  47. Log Shipping

  48. Click OK to configure the log shipping Log Shipping

  49. Test the shipping Log Shipping

  50. Wait 5 minutes and then check to see that your record has been shipped t o the secondary database Log Shipping

More Related