SQL Server 2012 for Developers - PowerPoint PPT Presentation

Loading...

PPT – SQL Server 2012 for Developers PowerPoint presentation | free to view - id: 47984a-MmIyM



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

SQL Server 2012 for Developers

Description:

SQL Server 2012 for Developers UTS Short Course DECLARE _at_i int = 0 WHILE _at_i – PowerPoint PPT presentation

Number of Views:272
Avg rating:3.0/5.0
Slides: 77
Provided by: Alvin79
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: SQL Server 2012 for Developers


1
SQL Server 2012 for Developers
  • UTS Short Course

2
Eric Phan SA _at_ SSW
w ericphan.info e EricPhan_at_ssw.com.au t
_at_ericphan
  • Loves C and .NET
  • Specializes in
  • Application architecture and design
  • SQL Performance Tuning and Optimization
  • Agile, Scrum Certified Scrum Trainer
  • Technology aficionado
  • Silverlight
  • ASP.NET
  • Windows Forms

3
Mehmet Ozdemir SA _at_ SSW
w blog.ozdemir.id.au e mehmet_at_ssw.com.au t
_at_mozdemir_au
  • SQL Server, BI, Infrastructure
  • Specializes in
  • Application architecture and design
  • SQL Performance Tuning and Optimization
  • HyperV, SCVMM
  • Technology aficionado
  • Virtualization
  • Reporting/BI
  • Cubes

4
Course Website
  • http//sharepoint.ssw.com.au/Training/UTSSQL/Pages
    /
  • Course Timetable
  • Course Materials

5
Course Overview
Session Date Time Topic
1 Tuesday DD-MM-YYYY 1800 - 2100 Management Studio and TSQL Enhancements (Part 1)
2 Tuesday DD-MM-YYYY 1800 - 2100 T-SQL Enhancements (Part 2) and CLR Integration
3 Tuesday DD-MM-YYYY 1800 - 2100 High Availability
4 Tuesday DD-MM-YYYY 1800 - 2100 Full-Text Search and Data Management Views
5 Tuesday DD-MM-YYYY 1800 - 2100 SQL Azure
6
What we did last week
  • Basic T-SQL syntax
  • New Data Types
  • Inline variable assignment
  • Table Value Parameters
  • DDL (Data Definition Language) Triggers
  • CTE (Common Table Expressions)
  • TOP WITH TIES
  • XML Queries
  • PIVOT/UNPIVOT

7
Homework?
8
SQL 2008 High Availability Features
  • Session 3

9
Agenda
Agenda
  • What is High Availability?
  • What can go wrong?
  • What can we do about it?

10
What is high availability?
  • Different people have different definitions
  • Perceived uptime
  • Performance
  • What can go wrong?
  • How can we improve it?

11
What is high availability?
  • Different people have different definitions
  • Perceived uptime
  • Performance Issues
  • What can go wrong?
  • How can we improve it?

12
What can go wrong? (continued)
  • Hardware
  • Disk failure
  • Network failure
  • Power Outages

13
What can go wrong?
  • Software
  • Virus (and Virus Scanners) File locking issues
  • Disk space
  • Corrupted files
  • Bad upgrades
  • OS Upgrades
  • SQL
  • poor tuning or design
  • DB Maintenance

14
What else can go wrong?
  • People (PEBKAC)
  • Administrators
  • Users
  • Bottlenecks Concurrency
  • Acts of God
  • Lightning
  • Cleaners

15
(No Transcript)
16
What can we do about it? (continued 3-1)
  • 1. Hardware Solutions
  • UPS Hardware Monitors
  • RAID / Mirroring
  • Off site server
  • Firewall
  • Physical Security

17
What can we do about it? (continued 3-2)
  • 2. Software Solutions
  • Database Mirroring
  • Log shipping
  • Auto backup transaction log, and restore
  • Replication (Can also reduce availability)
  • Monitor, Change
  • Database Snapshots
  • Alerts
  • OS Disk Space, ...
  • DB Logs,...
  • Partitioned Tables
  • Firewalls

18
What can we do about it? (3-3)
  • 3. OS Level / Backup Solutions
  • Security
  • Change Management
  • Performance Monitoring/Tuning
  • Hot/Warm/Cold standby servers
  • Standard daily backups
  • Verified procedure
  • Transaction logs

19
What Will We Cover?
  • AlwaysOn High Availability (new in SQL 2012)
  • Implementing Database Snapshots
  • Configuring a Database Mirror
  • Partitioned Tables
  • SQL Agent Proxies
  • Performing Online Index Operations
  • Mirrored Backups

20
AlwaysOn High Availability (SQL2012)
21
AlwaysOn High Availability
  • Like Database Mirroring but much more useful
  • DB Mirroring introduced in SQL 2005 SP1, improved
    in SQL 2008 and 2008R2
  • Mirrored copy unavailable until failed over, also
    needs witness server for automatic failover
  • AlwaysOn in SQL 2012 takes this to the next
    level!
  • Note Requires Enterprise Edition (not available
    to Standard)

22
AlwaysOn High Availability contd
  • Create up to 4 additional copies of each database
    of separate servers
  • Works either synchronously (LAN) or
    asynchronously (WAN)
  • Copies can be a mix of sync and async
  • Unlike mirroring remote copies allow for read
    only access (huge advantage)
  • Could be used for DR, Reporting and many other
    purposes
  • Existing Database Mirrors can be converted to
    AlwaysOn Availability Groups

23
AlwaysOn HandsOn Lab
  • http//online.holsystems.com/Software/holLaunchPad
    Online/holLaunchPadOnline.application?engSQLauth
    nonesrcSQLaltaddtruelabid4929
  • http//www.youtube.com/watch?vms-PjlX7S04

24
Database Snapshots
25
Point-in-time reporting
Mirroring for reporting
Recover from administrative error
Protection from application or user error
26
Database Snapshots
  • Snapshots are NOT a substitute for your backup
    and recovery setup
  • You cannot roll forward
  • If either the database or the database snapshot
    is corrupted, reverting from a snapshot is
    unlikely to correct the problem
  • Query from snapshot ? current database

27
Database Snapshots
  • CREATE DATABASE AdventureWorks_dbss1800 ON ( NAME
    AdventureWorks2008_Data, FILENAME
    'C\data\AdventureWorks_data_1800.ss' ) AS
    SNAPSHOT OF AdventureWorks GO

28
Database Snapshots
29
Snapshot vs. Backup vs. Detach
  • Snapshot
  • Only go back
  • SELECT statements
  • Backup
  • Rollback and Forward possible
  • Smaller
  • Detach database and copy
  • DB goes offline
  • Closes all connections

30
Database mirror
31
Configuring a Database Mirror
No special hardware
32
Configuring a Database Mirror
33
Configuring a Database Mirror
Virtually no distance limitations
No special hardware
34
Configuring a Database Mirror
Principal Server
Witness Server
Mirror Server
35
Configuring a Database Mirror
Mirror Server
Witness Server
Principal Server
36
Configuring a Database Mirror
Mirror Server
Witness Server
Principal Server
37
Before you mirror your database
  1. Principal Instance
  2. Take a full backup and a log backup as well
  3. Copy the full/log backups from Principal Instance
    to Mirror instance
  4. Mirror Instance
  5. Restore with NORECOVERY option the full backup
  6. Apply the log backup

38
Using the Mirror Wizard
39
(No Transcript)
40
(No Transcript)
41
(No Transcript)
42
Configure End Point on the Principal
43
Configure End Point on the Mirror
44
Set the service accounts
Use NT AUTHORITY\NETWORK SERVICE
45
(No Transcript)
46
Mirror is configured
47
Warning about FQDN
48
Mirror Operating Modes
  • High Performance (asynchronous)
  • Commits are done on the principal and transferred
    to the mirror
  • High Safety (synchronous)
  • Commits are written to both databases

49
(No Transcript)
50
Database Mirroring Failover
  • What happens when something bad happens to our
    principal server
  • You can make it failover to the mirror
  • This means that the two servers swap roles for
    the time being

51
(No Transcript)
52
Database Mirroring Monitor
  • Lets you view the status and history of your
    current mirrors

53
(No Transcript)
54
(No Transcript)
55
Thresholds
56
(No Transcript)
57
Might come in handy
  • Disable Mirroring ALTER DATABASE myDatabase SET
    PARTNER OFF
  • Put DB from "Recovering..." into available online
    mode RESTORE DATABASE myDatabase WITH RECOVERY

58
Partitioned tables
59
Partitioned Tables
60
Partitioned Tables
  • Allows for maximum concurrency
  • Partitioned Table parallelism
  • Improved a lot in SQL 2008
  • Archive older data into different filegroups

61
SQL Server agent
62
SQL Server Agent (recap)
  • Windows Service
  • Executes SQL Server jobs
  • Administrative tasks

63
SQL Agent Proxies
  • New credential system
  • Sits on Active Directory
  • Fine grained control of your jobs
  • Jobs can be run by proxies instead of user
    logins Previously to run cmd shell type
    functionality you needed a user in the
    administrator group which opened up security
    problems

64
Online Index Operations
65
Online Index Operations
Index Created
Table
66
Online Clustered Index Operation
67
  • CREATE NONCLUSTERED INDEX IX_TextTable_MyKey
  • ON TestTable (MyKey)
  • WITH (ONLINE ON)
  • GO

68
Mirrored Backups
69
Mirrored Backups
70
Mirrored Backups
71
Mirrored Backups
72
Session Summary
  • Implementing Database Snapshots
  • Configuring a Database Mirror
  • Partitioned Tables
  • SQL Agent Proxies
  • Performing Online Index Operations
  • Mirrored Backups

73
Session 3 Lab
  • High Availability Features
  • Database snapshots
  • Mirrored backups
  • Online Index Operations
  • Download from Course Materials Site (to
    copy/paste scripts) or type manually
  • http//tinyurl.com/utssql2009

74
Where Else Can I Get Help?
  • Free chats and webcasts
  • List of newsgroups
  • Microsoft community sites
  • Community events and columns
  • SQL Server user groups (www.sqlserver.org.au)
  • www.microsoft.com/technet/community

75
what's next?
76
3 things
  • EricPhan_at_ssw.com.au
  • http//ericphan.info
  • twitter.com/ericphan

77
3 things
  • mehmet_at_ssw.com.au
  • http//blog.ozdemir.id.au
  • twitter.com/mozdemir_au

78
  • Thank You!
  • Gateway Court Suite 10 81 - 91 Military Road
    Neutral Bay, Sydney NSW 2089 AUSTRALIA
  • ABN 21 069 371 900
  • Phone 61 2 9953 3000 Fax 61 2 9953 3105
  • info_at_ssw.com.au www.ssw.com.au
About PowerShow.com