Transcript SQL Server “SQL-Server 2012” Highlights

SQL-Server 2012
Always On
SQL Server “SQL-Server 2012”
Highlights
High
Availability
>Business
Intelligence
>EIM
Scalability &
Performance
Security &
Manageability
SQL Server
AlwaysOn
>Up to 15K
Partitions/Table
User-Defined
Server Roles
Microsoft
Project
“Juneau”
PHP Driver
>Unified
Semantic Model
>SSIS Server
Reliable,
Integrated
Failover Detection
Column Store
Index, “Apollo”
Contained
Database
Authentication
Win32 Access
to Database files
Local DB
Runtime
>Crescent
>Data Quality
Application
Centric Failover
Fast
FILESTEAM
Distributed
Replay
FileTable
UTF-16
>In-memory BI for
corporate
>Enhanced MDS
Multiple
Secondaries
Fast Full-Text
Search
Audit
Enhancements
Semantic
Search Platform
Paging for
result sets
>Alerting
>MDS add-in for
Excel
Readable
Secondaries
Fast Spatial
Performance
Management
Pack for High
Availability
Full Globe
Spatial Support
JDBC 4.0 driver
>Sysprep for AS
Online
Operations
>DBC & OLTP
Appliances/RAs
Backup
Secondaries
DAC
Enhancements
Support for
ARM
processors
>PowerPivot
Enhancements
HA for
StreamInsight
Default Schema
for Windows
Group
ODBC for Linux
Windows Server
Core Support
Active Directory
w/ SharePoint
for SSRS
Hybrid
Applications
with SQL Azure
Beyond
Relational
Web &
Breadth
>Reporting as
SharePoint
Shared Service
What is Always On Availability Group
Recommended HA for database availability
Best features of Mirroring
Non Shared Storage
Fast Failover
Automatic Page Repair
Support for High Latency Networks
Plus new features
Failover Group of databases
Multiple secondary replicas
Readable secondary replicas
Virtual names for fast failover
More flexible failover policy
Integration with traditional Failover Clustering
AlwaysOn Availability Groups
AlwaysOn Availability Groups is a new feature that
provides availability for application database(s).
• Provides protection from planned and unplanned
downtime
>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
 Automatic and manual
Failover
 Flexible failover policy
 Automatic Page Repair
>Integrated







Application
failover using
virtual name
Configuration
Wizard
Dashboard
System Center
Integration
Rich diagnostic
infrastructure
File-stream
replication
Replication
publisher failover
>Efficient

Active
Secondary



Readable
Secondary
Backup from
Secondary
Automation
using powershell
Case Study – Dell DVD Store Enterprises
Need to accept 24 x 7 transactions
For High Availability
Zero down time and data loss (automatic)
SLA to cover storage failure
“I see web site unavailable and you are fired”
“Reporting”
Near real time. No more than 1 minute behind
With minimal impact on live transactions
Disaster Recovery
Have second data centre. No more than 30 mins down time and 5
minutes data loss
Upgrade
Any upgrade needs to be with no down time or data loss
Typical HA/DR/Reporting Architecture
Application Server(s)
DR Site
SQL-Server 2012 Always On
>Reports
Réplica
Réplica
Backup
Application Server(s)
>SAN
>éplica
Réplica
Reports
Backup
Installation
Always On relies on WSFC
Always On is NOT a clustered install of SQL
Does not need Shared Disk
Windows 2008 Server+ EE needed
Need to Enable HadrService
Via configuration manager
Or via PowerShell
Some Patches and QFE Needed
KB 976097
KB 2494036
Migrating to Always On / SQL-Server
2012
Any normal migration is possible:
Backup / Restore
Attach / Detach
Mirroring
Log Shipping
Replication
Might need some post migration tasks
Compatibility
Use of new features such as Containment
Quorum Changes
Required for automatic failover
Built in feature of WSFC
Replaces the concept of “witness” to avoid split brain
Two recommended Steps
1. Select nodes to vote
2. Select the Quorum Type
If Odd number use “Node Majority”
If even number use “Node Majority plus File Share Witness”
Requires QFE KB KB 2494036 to enable Voting
May need to adjust quorum after failover outside automatic target
Secondary Replica Features
Failover
Backup \ DBCC
Reporting Queries
AlwaysOn AutoStat
Automatic Page Repair
Secondary Replica – Active Log Sync
Log
Capture
Network
Log Recieve
DB1
DB1
Log Pool
Commit
Redo
Thread
Log Cache
Log Cache
Log Flush
Log Harden
DB1 Log
DB1 Data
Acknowledge
Commit
DB1 Log
Redo
Pages
Page
Updated
DB1 Data
Secondary read is behind primary
Log is first hardened and then applied
Redo thread is asynchronous and runs in the background
Latency (typically seconds) can be larger for log intensive operations like bulk
import or index create/rebuild
Sync Replica minimizes latency due to network issues
Availability Groups and Failover Cluster Instance

>
AlwaysOn Resources
“SQL-Server 2012” AlwaysOn Resource Center:
http://msdn.microsoft.com/enus/sqlserver/gg490638(en-us,MSDN.10)
Documentation
MSDN forums
Microsoft Connect
AlwaysOn Blog