Transcript SQL Server AlwaysOn

SQL Server AlwaysOn
Phil Brammer, Database Administrator
SQL Server AlwaysOn
Phil Brammer
Database administrator and a seventh year Microsoft
MVP in SQL Server and a Microsoft Certified Solutions
Expert. He has over 13 years’ data management
experience in various technologies from reporting
through ETL to database administration. Currently he
manages nearly 200 SQL databases instances from
gigabytes to multiple terabytes in size and is responsible
for setting the vision for Blue Cross and Blue Shield of
Nebraska's database architectural roadmap. He is very
active in the Microsoft SQL Server community and has
contributed to books, SQL Saturdays, SQL PASS Summits,
and local user groups.
2
Terminology
•
•
•
•
•
AlwaysOn Availability Groups
AlwaysOn Failover Cluster Instances (FCIs)
Replicas and Roles
Availability Group Listener
Application Intent
4
Setup Clustering
•
•
•
•
Install Clustering
Create Cluster
Add Nodes
Cluster Validation Report
5
Setup SQL for AlwaysOn
• Use SQL Configuration Manager
• Use Powershell
– get-item . | select IsHadrEnabled
– Enable-SqlAlwaysOn -Path
SQLSERVER:\SQL\Computer\Instance
6
Create Availability Group
•
•
•
•
•
•
Create database in full recovery
Setup fileshare for backup/restores
Use Wizard
Use Powershell
Use T-SQL
*AG Listener creation will likely fail
– Need to set Active Directory permissions
7
Direct Read-Only Traffic
•
•
•
•
Define Roles
READ_ONLY_ROUTING_URL
READ_ONLY_ROUTING_LIST
Application_Intent=ReadOnly
8
Perform Backups
• Offload Backups
• Set Backup Priorities
• sys.fn_hadr_backup_is_preferred_replica
9
Resources
• Microsoft SQL Server High Availability
http://www.microsoft.com/enus/sqlserver/solutions-technologies/missioncritical-operations/high-availability.aspx
• AlwaysOn Solutions Guide
http://download.microsoft.com/download/D/2/0
/D20E1C5F-72EA-4505-9F26FEF9550EFD44/Microsoft%20SQL%20Server%20
AlwaysOn%20Solutions%20Guide%20for%20High
%20Availability%20and%20Disaster%20Recovery.
docx
10