Just as the name indicates, database mirroring creates a duplicate of the database instance that can be made available if the primary fails.
- [Instructor] In this video, we'll discuss database mirroring. A word of caution before we continue. Microsoft has indicated that this feature will be removed in future versions. Their recommendation is to use Always On Availability Groups instead. We'll take a brief look at Always On Availability Groups in a later video. So, what is mirroring? Essentially it's a solution that allows you to increase the availability of a database. It's implemented on per-database basis and only works with databases that are set up with the full recovery model.
Some of the benefits of working with database mirroring are that it increases the availability of a database. It also helps to increase data protection. And it improves the availability of a production database during upgrades. Essentially you have the ability to take a production database offline, switching over to the mirrored version of that database, and allow users to continue accessing the data as if nothing had happened. In terms of implementing it, we deal with a principal server, a mirror server, and a witness.
These must be hosted on separate server instances. Ideally, each instance is also hosted on a separate host system. Each instance will require a database mirroring endpoint. And these endpoints must be accessible to all other instances. In terms of authentication, you must ensure that the logins that exist on the mirror server are also present on the primary server. Also, you need to ensure that the metadata from the primary server is present on the mirror server so that we ensure that we have a duplicate environment, not only for the data but also for authentication and the metadata as well.
This ensures compatibility and completeness of the mirror. Again, it's important to note that Microsoft will be discontinuing support for mirroring in future database products. As a result, if you step out to the MSDN article on setting up a database mirroring, you can find all of the necessary steps to prepare your database mirroring solution. We're not going to demo it in here because of the fact that this will be removed in future versions, but feel free to use database mirroring in the meantime if you so choose.
This course is also ideal for anybody preparing for the Administering a SQL Database Infrastructure (70-764) exam, one of two exams necessary to earn an MCSA: SQL 2016 Database Administration certification.
- Installation and configuration considerations
- Data storage strategies
- Configuring database instances
- Performance considerations
- Security considerations
- Managing roles and users
- Managing data
- High-availability options