1 / 19

Managing Always On availability groups with powershell

Managing Always On availability groups with powershell. Marcos Freccia Sr. SQL Server DBA Zalando SE Data Platform MVP. Who am i ?. Marcos Freccia Sr. SQL Server DBA @ sqlfreccia sqlfreccia@outlook.com http://marcosfreccia.wordpress.com Data Platform MVP

virginiawilson
Download Presentation

Managing Always On availability groups with powershell

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. Managing Always On availability groups with powershell Marcos Freccia Sr. SQL Server DBA Zalando SE Data Platform MVP

  2. Who am i? Marcos Freccia Sr. SQL Server DBA @sqlfreccia sqlfreccia@outlook.com http://marcosfreccia.wordpress.com Data Platform MVP MCSA DBA & DBA SQL Server 2016 – Charter MCSE Data Management and Analytics SQL Server 2016 – Charter SQL Server DBA for 8 years Originally from Brazil, now living in Berlin, Germany

  3. Just like Jimi Hendrix …  We love to get feedback Please complete the session feedback forms

  4. SQLBits - It's all about the community... Please visit Community Corner, we are trying this year to get more people to learn about the SQL Community, equally if you would be happy to visit the community corner we’d really appreciate it.

  5. Agenda Why I am speaking about it? Setting expectations Always On Availability Groups Basics Common Challenges How to resolve them

  6. Why I am speaking about it? There is no

  7. Why I am speaking about it? It is becoming a quite common HA + DR Deployment Lot’s of DBA’s are recommending to their companies Easier Configuration, Management, and Monitoring It is not a Plug and Play feature Automation is the key to have an seamless environment

  8. Setting expectations This session is 100% based in my environment at Zalando SE.

  9. Setting expectations We are not going to cover Windows Server Failover Clustering. This talk is not about installing configuring or deploying Always On Availability Groups. This talk is not to explain how PowerShell works. If we run out of time, all scripts will be published in my Github account

  10. AlwaysOn Availability Groups AlwaysOn Availability Groups enhances and combines database mirroring and log shipping capabilities Flexible Integrated Efficient • Multi-database failover • Multiple secondaries • Total of 8 secondaries • 2 synchronous secondaries • 1 automatic failover pair • Synchronous and asynchronous data movement • Built in compression and encryption • Auto-page repair • Automatic and manual failover (new design) • Flexible failover policy • Application failover using virtual name • Configuration Wizard • AlwaysOn Management Dashboard • System Center Integration • Rich diagnostic infrastructure • File-stream replication • Replication publisher failover • Active Secondary • Readable Secondary • Backup from Secondary • Improves primary server performance by offloading work to secondary • Monitoring and Troubleshooting enhanced • Automation using PowerShell

  11. Example HA/DR Topology DR HA Backups A DR A A A AlwaysOn-SRV4 Reports Sync Log Synchronization Async Log Synchronization AlwaysOn-SRV2 AlwaysOn-SRV1 AlwaysOn-SRV3

  12. Benefits Better Data Protection Multiple sync (no data loss) secondaries Automatic Page Repair Lower Recovery Point Objective (RPO) for DR secondaries through continuous log synchronization (w/ compression) Higher Availability Fast app failover to any secondary through Listener Full Hardware Usage Including Secondaries Active Secondaries: Read Workloads & Backups* Near real-time data through continuous log synchronization Easier Configuration, Management, and Monitoring Single solution Multiple databases Multiple replicas Unified configuration, management, and monitoring SCOM pack available

  13. Common challenges Server configuration doesn’t match among replicas Failover happens and application cannot connect anymore to the database Failover happens and SQL Server Jobs are not producing the expected results My SSRS DB is in an AG. How to handle Subscription Jobs? How to properly restore a database that it is in an AG? How to properly patch SQL Server when there is Availability Groups? …… and many more!!

  14. How to resolve them

  15. Powershell Built on the top of .NET Framework. Task-based command line shell and scripting language. Designed to System Admins and Power Users to manage (automate) various tasks. Modules are available to almost every Microsoft Product • SQL Server • Active Directory • Windows Server • And so on You can create your own modules! It is Open Source!!

  16. dbatools A PowerShell module for SQL Server Free and Open Source! Currently more than 300 commands! Firstly created by Chrissy LeMaire (@cl) PowerShell MVP Now maintained by more than 40 members of the community https://dbatools.io/

  17. Let’s go to some demos!

  18. Next steps PowerShell: https://docs.microsoft.com/en-us/powershell/scripting/powershell-scripting?view=powershell-6 DBATools: https://dbatools.io/

  19. Thank you! Please give feedback! Marcos Freccia Zalando SE Twitter: @sqlfreccia GitHub: https://github.com/marcosfreccia

More Related