slide1
Download
Skip this Video
Download Presentation
SQL Server 2012 AllwaysOn

Loading in 2 Seconds...

play fullscreen
1 / 23

SQL Server 2012 AllwaysOn - PowerPoint PPT Presentation


  • 75 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' SQL Server 2012 AllwaysOn' - jenski


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
slide1
SQL Server 2012 AllwaysOn

Stephan Hurni

Microsoft V-TSP

Principal Consultant, Trivadis AG

[email protected]

slide2

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

slide3

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.
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

slide15
Demo

Availability Groups

AG’s

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

slide19
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

slide21
Questions

SQL Server 2012 HADR

Thinking …

slide22

1. Preis

Wettbewerb

2. Preis

Am Trivadis Stand

Signed by

Thomas Huber

3. Preis

ad