1 / 23

SQL Server 2012 AllwaysOn

SQL Server 2012 AllwaysOn. Stephan Hurni Microsoft V-TSP Principal Consultant, Trivadis AG stephan.hurni@trivadis.com. Trivadis solution portfolio and competences. IT SOLUTIONS, SERVICES & PRODUCTS. Business Integration Services. Business Intelligence. Infrastructure Engineering.

jenski
Download Presentation

SQL Server 2012 AllwaysOn

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. SQL Server 2012 AllwaysOn Stephan Hurni Microsoft V-TSP Principal Consultant, Trivadis AG stephan.hurni@trivadis.com

  2. Trivadissolutionportfolioandcompetences IT SOLUTIONS, SERVICES & PRODUCTS Business Integration Services Business Intelligence InfrastructureEngineering Application Development Managed Services Training Integration, Application Performance Management, Security TECHNOLOGIES Microsoft, Oracle, IBM, Open Source

  3. Trivadisfacts & figures • 11 Trivadis locationswithmorethan 600 employeesFinanciallyindependentandsustainably profitable • Key figures 2011 • Revenue CHF 104 / EUR 84 Mio. • Services formorethan 800 clients in over 1,900 projects • 200 Service Level Agreements • More than 4,000 trainingparticipants • Research anddevelopmentbudget: CHF 5.0 / EUR 4 Mio.

  4. HADRHA Enhancementsin SQL Server 2012

  5. HA/DR Features before SQL 2012 ... List is not complete, Replication only for Table Level HA...

  6. AlwaysOn Availability Groups AlwaysOn Availability Groups is a new feature that enhances and combines failover cluster, database mirroring and log shipping capabilities Efficient Integrated Flexible • Multi-database failover • Multiple secondaries • Total of 4 secondaries • 2 synchronous secondaries • 1 automatic failover pair • Synchronous and asynchronous data movement • Built in compression and encryption • Flexible failover policy • Automatic Page Repair • Active Secondary • Readable Secondary • Backup from Secondary • Automation using power-shell • Application failover using virtual name • Configuration Wizard • Dashboard • System Center Integration • Rich diagnostic infrastructure • File-stream replication • Replication publisher failover

  7. AlwaysOn Availability Groups Based on Windows Server Failover Cluster Conjunctionofmultiple Database Mirrorswith Failover Clustering

  8. Availability Group Scenarios • Example • Primary in Calgary • Failover Partner in Vancouver • Sync DR in Toronto AG • Async Secondaryin London (Geo DR) AG AG AG AG • Async Secondaryin Montreal (Reporting) Asynchcronous Data Movement Synchronous Data Movement

  9. AlwaysOn | Topics Availability DatabasesDBs canbeaddedtoavailabilitygroupsasprimary AvailabilityReplicas| primary DBs canhaveupto 4 replicas Availability ModesAsynchronous-commitmodeSynchronous-commitmode (max. 2 secondaries) TypesofFailover ¦ plannedmanual, automatic Client Connections to VNN (virtualnetworkname)(grouplistenerwithunique DNS A-record + multiple C-Names) Automatic Page Repair

  10. Windows Server Failover Cluster Built-in asfeature in Windows since 2003 Check Server Requirementshttp://msdn.microsoft.com/en-us/library/ff878487(v=sql.110).aspx Create Windows Cluster

  11. Availability Groups Creator (clustercomputeraccount in ADS) of AGs must haverights in ADS  joincomputers local administrative rights on nodes Have Network Name, IP Address, Connection Port, min 1 Database, Listener Port andaccountforAvailability Group readyeach AG has: own NN, IP Address, Connection Port Create multiple AGs forperformancebenefits min 1 AG per Node (activeprocesses per Node) approximately 40MB/sec throughput per AG Combine multiple DBs belongingtogetherto same AGs

  12. Create Availability Groups CREATE AVAILABILITY GROUP group_name WITH (<with_option_spec> [ ,...n ] ) FOR [ DATABASE database_name [ ,...n ] ] REPLICA ON <add_replica_spec> [ ,...n ] [ LISTENER ‘dns_name’ ( <listener_option> ) ] [ ; ] <with_option_spec>::= AUTOMATED_BACKUP_PREFERENCE = { PRIMARY | SECONDARY_ONLY| SECONDARY | NONE } | FAILURE_CONDITION_LEVEL = { 1 | 2 | 3 | 4 | 5 } | HEALTH_CHECK_TIMEOUT = milliseconds <add_replica_spec>::= <server_instance> WITH ( ENDPOINT_URL = 'TCP://system-address:port', AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }, FAILOVER_MODE = { AUTOMATIC | MANUAL } [ , <add_replica_option> [ ,...n ] ] ) <add_replica_option>::= BACKUP_PRIORITY = n | SECONDARY_ROLE ( { [ ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } ] [,] [ READ_ONLY_ROUTING_URL = 'TCP://system-address:port' ] } ) | PRIMARY_ROLE ( { [ ALLOW_CONNECTIONS = { READ_WRITE | ALL } ] [,] [ READ_ONLY_ROUTING_LIST = { ( ‘<server_instance>’ [ ,...n ] ) | NONE } ] } ) | SESSION_TIMEOUT = integer <listener_option> ::= { WITH DHCP [ ON ( <network_subnet_option> ) ] | WITH IP ( { ( <ip_address_option> ) } [ , ...n ] ) [ , PORT = listener_port ] } <network_subnet_option> ::= ‘four_part_ipv4_address’, ‘four_part_ipv4_mask’ <ip_address_option> ::= { ‘four_part_ipv4_address’, ‘four_part_ipv4_mask’ | ‘ipv6_address’ } http://technet.microsoft.com/de-de/library/ff878399.aspx (CREATE AVAILABILITY GROUP (Transact-SQL)

  13. Flexible failover policy ALTER AVAILABILITY GROUP AV01 SET (FAILURE_CONDITION_LEVEL = 1) ALTER AVAILABILITY GROUP AG1 SET (HEALTH_CHECK_TIMEOUT = 60000)

  14. HADR system views select * from sys.dm_hadr_availability_group_states select * from sys.dm_hadr_availability_replica_cluster_nodes select * from sys.dm_hadr_availability_replica_cluster_states select * from sys.dm_hadr_availability_replica_states select * from sys.dm_hadr_cluster select * from sys.dm_hadr_cluster_members select * from sys.dm_hadr_cluster_networks select * from sys.dm_hadr_database_replica_cluster_states select * from sys.dm_hadr_database_replica_states select * from sys.dm_hadr_instance_node_map select * from sys.dm_hadr_name_id_mapselect * from sys.dm_hadr_auto_page_repair select routing_priority, read_only_routing_url, replica_server_name, endpoint_url from sys.availability_read_only_routing_lists A join sys.availability_replicas B on A.replica_id = b.replica_id

  15. Demo Availability Groups AG’s

  16. Availability Groups Settings/Monitor

  17. Read_onlysecondaries Read_onlywith Database Mirroring? Read_onlywith HADR Availability GroupsSecondariesarereadable but not updateable Routing List SQLcmd with SSMS -E –S AV01 -K ReadOnly -d AV -Q [Query] DB context ApplicationIntent=ReadOnly

  18. Backup secondaries Save Network bandwithforredundant Full Backups copy_onlyforFull Backups Take careofbackuplocationforTXLog Backups TXlog-chain!!! HADR backupperferences selectsys.fn_hadr_backup_is_preferred_replica ('AV')

  19. Demo Read-onlysecondaries Backup secondaries read-only Backup

  20. Database Maintenance Have a solid standardizedandautomated Maintenance in place thinkoffailoversituations BewareofnewDatabases behaviors in AGs read_only Backup secondaries Optimize/Change your Database Maintenance Jobs ask Trivadis howtoMaintain SQL Server Databases anddeservemore

  21. Questions SQL Server 2012 HADR Thinking …

  22. 1. Preis Wettbewerb 2. Preis Am Trivadis Stand Signed by Thomas Huber 3. Preis

More Related