In het ontwikkelen van een AG komen de volgende onderdelen naar voren:
- Ontwikkelen van een AG
- Configureren Windows Clustering
- Aanmaken AG
- Configureren van de alleen lezen routering
- Beheren van de Failover
- Aanmaken gedistribueerde AG
New to availability groups?
If you are still using database mirroring, there are several reasons to transition your high-availability strategy to availability groups. Database mirroring is deprecated as of SQL Server 2012, for example, and basic availability groups are now included in SQL Server 2016 Standard Edition as a replacement. Also, if you are exploring options for high-availability/disaster-recovery (HA/DR) solutions but have never implemented availability groups, SQL Server 2016 provides several benefits to consider.Whereas database mirroring occurs at the database level, using a single thread to perform the data replication, data is moved within availability groups by using a worker pool, which provides better throughput and reduces CPU overhead. When your application requires multiple databases, you can assign the databases to a single availability group to ensure that they all fail over at the same time. By contrast, the unit of failover with database mirroring is a single database. With database mirroring, you use a SQL Server witness instance to manage automatic failover, but with availability groups you rely on Windows Server Failover Clustering (WSFC) to arbitrate uptime and connections. Furthermore, clustering is a more robust solution than database mirroring because it provides additional levels of protection.A key benefit of availability groups is the
ability to scale out replicas that you can configure to support both
high-availability and disaster-recovery requirements.
For high-availability scenarios, you should locate two or three servers in
the same geographic location, configured to use synchronous-commit
mode and automatic failover. That said, automatic failover should be
used only in low-latency scenarios because writes to the primary replica are not considered complete until they reach the transaction log on the
secondary replica.
For disaster-recovery scenarios in which the servers are more than 100 kilometers apart, asynchronous-commit mode is a better choice to
minimize the performance impact on the primary replica.Another
benefit of availability groups is the ability for databases on a secondary
replica to support online reads as well as database backups. This
capability allows you to implement a scale-out architecture for reporting solutions by having multiple copies of secondary replicas in multiple
geographies. You provide connectivity to the availability group by using a virtual IP address called the listener, which you configure to connect transparently to the primary replica or to a secondary replica for reading.
Figure 3-1 is a diagram of an availability group with replicas in New York, Los Angeles, and Seattle and a listener to which clients connect.
(Bron: Intro to SQL)
De voorziening Always On – AG is een high-availability en disaster-recovery-oplossing die een bedrijfsmatig alternatief biedt voor databasespiegeling. Geïntroduceerd in SQL Server 2012 (11.x) maximaliseert Always On-beschikbaarheidgroepen de beschikbaarheid van een reeks gebruikersdatabases voor een onderneming. Een AG ondersteunt een failover-omgeving voor een afzonderlijke set gebruikersdatabases, die beschikbaarheidsdatabases worden genoemd, die samen falen. Een AG ondersteunt een set primaire primaire databases en één tot acht reeksen overeenkomstige secundaire databases. Optioneel kunnen secundaire databases beschikbaar worden gemaakt voor alleen-lezentoegang en / of voor sommige back-upbewerkingen. Een AG faalt op het niveau van een beschikbaarheidsreplica. Failovers worden niet veroorzaakt door databaseproblemen, zoals het verdacht worden van een database als gevolg van het verlies van een gegevensbestand, het verwijderen van een database of het beschadigen van een transactielogboek.
An availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log.
availability group
A container for a set of databases, availability databases, that fail over together.
availability database
A database that belongs to an availability group. For each availability database, the availability group maintains a single read-write copy (the primary database) and one to eight read-only copies (secondary databases).
primary database
The read-write copy of an availability database.
secondary database
A read-only copy of an availability database.
availability replica
An instantiation of an availability group that is hosted by a specific instance of SQL Server and maintains a local copy of each availability database that belongs to the availability group. Two types of availability replicas exist: a single primary replica and one to eight secondary replicas.
primary replica
The availability replica that makes the primary databases available for read-write connections from clients and, also, sends transaction log records for each primary database to every secondary replica.
secondary replica
An availability replica that maintains a secondary copy of each availability database, and serves as a potential failover targets for the availability group. Optionally, a secondary replica can support read-only access to secondary databases can support creating backups on secondary databases.
availability group listener
A server name to which clients can connect in order to access a database in a primary or secondary replica of an Always On availability group. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica.