SQL Server AlwaysOn

1. SQL Server AlwaysOn

2. Edit Welcome Speaker: John Ecken Topic: SQL Server AlwaysOn Technology News and Events: To receive news, event invites, and special offers from Tandem Solution please opt-in at http://www.training4it.com/signup Follow me and Like Tandem Solution on Facebook Twitter:@JohnEcken and @Training4IT LinkedIn: @www.linkedin.com/in/johnecken/ Welcome

3. About Tandem Solution is highly regarded for our comprehensive and superior training solutions. Whether it's customizing training for client projects or offering hundreds of Guaranteed to Run classes, we help you succeed. We always utilize the most qualified and certified instructors. We are honored to serve a distinguished list of clients because of how we provide training, the quality of instruction, the myriad of technologies that we offer, the money that we save clients, and the overall value that we bring as a training partner. Experience our most valued training services and see why our customers prefer Tandem Solution to train their greatest resource… their employees. Tandem Solution

4. Introducing SQL Server 2014 DR Fail-over Clustering (Shared Drive) Database Mirroring Always On Log Shipping Replication Introducing SQL Server 2014 AlwaysOn

5. Improvements with Always On Efficiency and cost-effectiveness are improved through the implementation of: • AlwaysOn Readable Secondaries • Contained Databases • Multi subnet availability Improved Efficiency and Cost-Effectiveness

6. What happens when… In SQL Server 2008 R2 or prior • Database mirroring provides automated failover of a single database • Redundant copies of the database exist on principle and mirror server • Failing over multiple databases at the same time requires custom code logic to detect single failure and then initiate failover of other databases • Application failover is accomplished through the use of FailoverPartner connection string value In SQL Server 2012 • Redundant copies (up to four) of the databases exist on nodes participating in the Availability Group, maintained either synchronously or asynchronously • Multiple-database failover is handled automatically by the Availability Group • Application failover through the Availability Group Listener and Application Virtual Name Business requirements are for automatic failover of multiple databases together with no single point of failure and redundancy across multiple datacenters

7. Groups AlwaysOn Availability Groups Enhance the capabilities of database mirroring • Multiple database coordinated failover for applications that require multiple databases on a single instance (e.g. SharePoint) • Simplified application connectivity and automatic redirection through the implementation of Availability Group Listener and Application Virtual Name • Built in compression and encryption • Synchronous or asynchronous data movement • Automatic or manual failover modes with configurable failover trigger levels • Automatic repair of page corruptions • Readable secondary replicas • Support for FILESTREAM, FILETABLE, RBS and Service Broker • Simplified configuration wizards, PowerShell integration and Availability Group Dashboard for monitoring

8. Topology examples A A A A Direct attached storage local, regional and geo secondaries Synchronous data movement Asynchronous data movement

9. What happens when… In SQL Server 2008 R2 or prior • SQL Server Logins are mapped to Database Users through login SIDs • Incorrectly mapped logins result from creating logins on additional servers • Can result in problems during failovers or when restoring databases to new servers • Requires manual transfer of login using sp_help_revlogin or SSIS Transfer Logins task to maintain identical SID on both servers • Nothing tracks external dependencies that might exist in the database code In SQL Server 2012 • Partially Contained Databases contain Database Login Credentials as a part of the database • Simplifies failover planning for the environment by allowing the login information to be persisted as a part of the database • Developers can track instance level impacts and uncontained dependencies with You need to move a database to a different SQL Server (Containment)

10. Containment Demo

11. AlwaysOn in SQL Server 2014 • What’s being delivered • Increase number of secondaries from four to eight • Increase availability of readable secondaries • Support for Windows Server 2012 CSV • Enhanced diagnostics • Main benefits • Further scale out read workloads across (possibly geo-distributed) replicas • Use readable secondaries despite network failures (important in geo-distributed environments) • Improve SAN storage utilization • Avoid drive letter limitation (max 24 drives) via CSV paths • Increase resiliency of storage failover • Ease troubleshooting What’s New

12. Groups in 2014 • Description • Increase number of secondaries (4–8) • Max number of sync secondaries is still two Increase Number of Availability Group Secondaries • Reason • Customers want to use readable secondaries • One technology to configure and manage • Many times faster than replication • Customers are asking for more database replicas (4–8) • To reduce query latency (large-scale environments) • To scale out read workloads

13. Always On Group Demo

14. Readable Secondary Client connection behavior determined by the Availability Group Replica option • Replica option determines whether a replica is enabled for read access when in a secondary role and which clients can connect to it • Choices are: • No connections • Only connections specifying Application Intent=ReadOnly connection property • All connections Read-only Routing enables redirection of client connection to new readable secondary after a failover • Connection specifies the Availability Group Listener Virtual Name plus Application Intent=ReadOnly in the connection string • Possible for connections to go to different readable secondaries if available to balance read-only access Client Connectivity

15. Readable secondary Client connects to the Availability Group Listener virtual name • Standard connections are routed to the Primary server for read/write operations • ReadOnly connections are routed to a readable secondary based on ReadOnly routing configuration ReadOnly Routing Availability Group Listener

16. Readable secondary Challenges: • Query workloads typically require index/column statistics so the query optimizer can formulate an efficient query plan • Read-only workloads on a secondary replica may require different statistics than the workload on the primary replica • Users cannot create different statistics themselves (secondaries can’t be modified) Solution: • SQL Server will automatically create required statistics, but store them as temporary statistics in tempdb on the secondary node If different indexes are required by the secondary workload, these must be created on the primary replica so they will be present on the secondaries • Care should be taken when creating additional indexes that maintenance overhead does not affect the workload performance on the primary replica Query Performance on the Secondary

17. Readable secondary Backups can be done on any replica of a database to offload I/O from primary replica • Transaction log backups, plus COPY_ONLY full backups Backup jobs can be configured on all replicas and preferences set so that a job only runs on the preferred replica at that time • This means no script/job changes are required after a failover Transaction log backups done on all replicas form a single log chain Database Recovery Advisor tool helps with restoring backups from multiple Secondaries Offloading Backups to a Secondary

18. Readable secondary Read-only workloads on mirror database using traditional database mirroring can block replay of transactions from the principal Using Readable Secondaries, the reporting workload uses snapshot isolation to avoid blocking the replay of transactions • Snapshot isolation avoids read locks which could block the REDO background thread • The REDO thread will never be chosen as the deadlock victim, if a deadlock occurs Replaying DDL operations on the secondary may be blocked by schema locks held by long running or complex queries • XEvent fires which allows programmatic termination/resumption of reporting Workload impact on the secondary

19. Readable Secondary Demo

20. Upcoming SQL and Azure Together Business Information with what you already have (SQL, Reporting Service, Excel) Office 365 and Power BI *For Future Events Please Sign Up at: Future Events http://www.training4it.com/signup

21. Thank You!