If the primary goes down, the replica will act as the new primary since you are now running on top of Windows Failover Cluster: this takes care of the failover. The listener will also move to the new primary. Think of this as database mirroring plus clustering
HI , Just watched your session on high availability , that's a great work put in simple terms , thanks a lot for sharing your knowledge. I have a question on designing a high availability architecture. We currently have a set of OLTP databases serving for the application usage and these databases are being replicated (transactional, every 3 min) to another box for reporting purposes, I'm thinking of replacing replication with High availability groups (asynchronous) (one of the reasons to bring high availability groups in to picture is to have HA during database corruption (ex: page level corruption)) Q: as the indexing on report databases is different to indexing on application dB’s, the failover between app db and reporting db will not provide better performance to application or reports at the time of failover, this makes it a bit hard to design a cost effective, low downtime cluster. Please let me know if you can suggest a better solution for this situation. Appreciate your help. Cheers, Kiran
Hi Kiran, You are correct. With the indexing strategy being different between the reporting database and the OLTP source, your failover and synchronization mechanism will affect performance. Also, a lot of folks are not aware of the 14 extra bytes that you are adding to a row when readable secondaries are introduced for reporting purposes www.edwinmsarmiento.com/that-extra-14-bytes/ The approach here is to decide whether the data in the reporting database is greater than 60% of the OLTP data source. If it is, making the OLTP data source as an Availability Group readable secondary can be justified when you think about operations and maintenance cost down the road. If it isn't, keep the reporting database as it is. Also evaluate the amount of transaction log records generated in your OLTP database including when you do index maintenance. This will give you an idea on the amount of transaction log records that you need to replicate to your Availability Group secondary databases.
Edwin Sarmiento Thanks for the response Edwin, as it is not possible to use the secondary as a reporting db, I think I need to create another set of high availability group for reporting cluster, is it possible to setup replication between two high availability groups ? i.e., cluster1 With High availability groups for app db and cluster 2 with High availability groups for reporting db ; but there should be something that asynchrously updates reporting db on cluster 2 frpm app db on cluster1. Thanks for your valuable tie on this.
This depends on what the status of the primary is after going back online. If the primary was down for a long period of time, for example due to maintenance or hardware failure, you need to re-initialize the database to catch up with the latest version of the database. If the primary immediately got back online, it can resync back with the new primary. The concept is similar with database mirroring.
Nice presentation. I do have a question. How does the primary instance get back in sync on a fail over to a secondary instance? Does the secondary sync it back up automatically once the primary is back online?
Nice Job Edwin - I'm sure you're aware of this, but you can have mirrors with snapshots to help maximize resources on the secondary. question... With HAG, using the listener to redirect after a failover... what happens if the primary goes down and won't come back up - like a true disaster - can you run "fail-over" on the secondary instance and it too will update the listener?
Hi Belay, You can try downloading it from the link provided Note that you need the .NET Framework 4.0 and the SQL Server 11.0 Native Client on your workstation to use the Application Intent=ReadOnly parameter on your connection string
Yes it is possible. It is recommended to start with architecture design first before deciding how many Availability Groups and Listener Names you need to host in your failover cluster
The IP addresses for all of the servers, the Windows Server Failover Cluster and Availability Group listener name are all static IP addresses and have been identified prior to configuration. Please refer to this video for more information ua-cam.com/video/RgKpC0W143U/v-deo.html
If a have webiste that use sql server name to connect to sql and database name. Should I put listener name instead of servername and then the name of the database?
Yes, the listener name is what will replace your SQL Server name. The listener name will move between the replicas during failover so you don't need to modify your application connection string.
+Hoang Nguyen When the two servers are on two different subnets, you will need two virtual IP addresses for the Availability Group Listener Name - one for each subnet. But only one virtual IP address will be online at any given point in time. This is implemented using the OR logic dependency in the cluster network name resource
Thank you for the content! Now ready for my MCSE exam
Best of luck to achieving your goal!
Thanks so much for sharing Brilliant video and knowledge........Thanks so much
video quality (visibility) is poor, what am i doing wrong. i can see all other videos nicely
It could be your internet connection. The video is available in 720p.
If the primary goes down, the replica will act as the new primary since you are now running on top of Windows Failover Cluster: this takes care of the failover. The listener will also move to the new primary. Think of this as database mirroring plus clustering
HI ,
Just watched your session on high availability , that's a great work put in simple terms , thanks a lot for sharing your knowledge.
I have a question on designing a high availability architecture.
We currently have a set of OLTP databases serving for the application usage and these databases are being replicated (transactional, every 3 min) to another box for reporting purposes, I'm thinking of replacing replication with High availability groups (asynchronous) (one of the reasons to bring high availability groups in to picture is to have HA during database corruption (ex: page level corruption))
Q: as the indexing on report databases is different to indexing on application dB’s, the failover between app db and reporting db will not provide better performance to application or reports at the time of failover, this makes it a bit hard to design a cost effective, low downtime cluster.
Please let me know if you can suggest a better solution for this situation.
Appreciate your help.
Cheers,
Kiran
Hi Kiran,
You are correct. With the indexing strategy being different between the reporting database and the OLTP source, your failover and synchronization mechanism will affect performance. Also, a lot of folks are not aware of the 14 extra bytes that you are adding to a row when readable secondaries are introduced for reporting purposes www.edwinmsarmiento.com/that-extra-14-bytes/
The approach here is to decide whether the data in the reporting database is greater than 60% of the OLTP data source. If it is, making the OLTP data source as an Availability Group readable secondary can be justified when you think about operations and maintenance cost down the road. If it isn't, keep the reporting database as it is. Also evaluate the amount of transaction log records generated in your OLTP database including when you do index maintenance. This will give you an idea on the amount of transaction log records that you need to replicate to your Availability Group secondary databases.
Edwin Sarmiento Thanks for the response Edwin, as it is not possible to use the secondary as a reporting db, I think I need to create another set of high availability group for reporting cluster, is it possible to setup replication between two high availability groups ? i.e., cluster1 With High availability groups for app db and cluster 2 with High availability groups for reporting db ; but there should be something that asynchrously updates reporting db on cluster 2 frpm app db on cluster1. Thanks for your valuable tie on this.
Great Video - Can you please send me the scripts you used for disabling please
This depends on what the status of the primary is after going back online. If the primary was down for a long period of time, for example due to maintenance or hardware failure, you need to re-initialize the database to catch up with the latest version of the database. If the primary immediately got back online, it can resync back with the new primary. The concept is similar with database mirroring.
Nice presentation. I do have a question. How does the primary instance get back in sync on a fail over to a secondary instance? Does the secondary sync it back up automatically once the primary is back online?
Nice Job Edwin - I'm sure you're aware of this, but you can have mirrors with snapshots to help maximize resources on the secondary.
question... With HAG, using the listener to redirect after a failover... what happens if the primary goes down and won't come back up - like a true disaster - can you run "fail-over" on the secondary instance and it too will update the listener?
Hi Belay,
You can try downloading it from the link provided
Note that you need the .NET Framework 4.0 and the SQL Server 11.0 Native Client on your workstation to use the Application Intent=ReadOnly parameter on your connection string
It is good One and is it possible to have one listner and one AG group for two different SQl instance on four node cluster.
Yes it is possible. It is recommended to start with architecture design first before deciding how many Availability Groups and Listener Names you need to host in your failover cluster
Very good preso. Thanks for sharing.
hi, for the windows failover cluster did you create a random IP address? thanks
The IP addresses for all of the servers, the Windows Server Failover Cluster and Availability Group listener name are all static IP addresses and have been identified prior to configuration. Please refer to this video for more information ua-cam.com/video/RgKpC0W143U/v-deo.html
Very nice presentation Edwin. Thanks for sharing the knowledge
If a have webiste that use sql server name to connect to sql and database name. Should I put listener name instead of servername and then the name of the database?
Yes, the listener name is what will replace your SQL Server name. The listener name will move between the replicas during failover so you don't need to modify your application connection string.
Thanks Edwin
Thanks for watching Vijai
Hi, Great Video. What if those 2 servers are on 2 separate subnets? Then which IP address of which subnet should we put there ?
+Hoang Nguyen When the two servers are on two different subnets, you will need two virtual IP addresses for the Availability Group Listener Name - one for each subnet. But only one virtual IP address will be online at any given point in time. This is implemented using the OR logic dependency in the cluster network name resource
Edwin M Sarmiento That helps me a lot. Thank you very much
+Hoang Nguyen You are most welcome
Great presentation.Thanks
great job.... keep it up ..
Good Presentation
very Good guys
Great presentation.
Thanks Edwin