SQL Server 2012
This presentation is the property of its rightful owner.
Sponsored Links
1 / 23

SQL Server 2012 AllwaysOn PowerPoint PPT Presentation


  • 46 Views
  • Uploaded on
  • Presentation posted in: General

SQL Server 2012 AllwaysOn. Stephan Hurni Microsoft V-TSP Principal Consultant, Trivadis AG [email protected] Trivadis solution portfolio and competences. IT SOLUTIONS, SERVICES & PRODUCTS. Business Integration Services. Business Intelligence. Infrastructure Engineering.

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sql server 2012 allwayson

SQL Server 2012 AllwaysOn

Stephan Hurni

Microsoft V-TSP

Principal Consultant, Trivadis AG

[email protected]


Sql server 2012 allwayson

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


Sql server 2012 allwayson

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.


Sql server 2012 allwayson

HADRHA Enhancementsin SQL Server 2012


Ha dr features before sql 2012

HA/DR Features before SQL 2012

... List is not complete, Replication only for Table Level HA...


Alwayson availability groups

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


    Alwayson availability groups1

    AlwaysOn Availability Groups

    Based on Windows Server Failover Cluster

    Conjunctionofmultiple Database Mirrorswith Failover Clustering


    Availability group scenarios

    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


    Alwayson topics

    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


    Windows server failover cluster

    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


    Availability groups

    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


    Create availability groups

    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)


    Flexible failover policy

    Flexible failover policy

    ALTER AVAILABILITY GROUP AV01 SET (FAILURE_CONDITION_LEVEL = 1)

    ALTER AVAILABILITY GROUP AG1 SET (HEALTH_CHECK_TIMEOUT = 60000)


    Hadr system views

    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


    Sql server 2012 allwayson

    Demo

    Availability Groups

    AG’s


    Availability groups settings monitor

    Availability Groups Settings/Monitor


    Read only secondaries

    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


    Backup secondaries

    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')


    Sql server 2012 allwayson

    Demo

    Read-onlysecondaries

    Backup secondaries

    read-only

    Backup


    Database maintenance

    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


    Sql server 2012 allwayson

    Questions

    SQL Server 2012 HADR

    Thinking …


    Sql server 2012 allwayson

    1. Preis

    Wettbewerb

    2. Preis

    Am Trivadis Stand

    Signed by

    Thomas Huber

    3. Preis


  • Login