SQL Server Database Mirroring Concept

SQL Server 2008 for Developers
UTS Short Course
Peter Gfader
Specializes in

C# and .NET (Java not anymore) Testing Automated tests Agile, Scrum Certified Scrum Trainer Technology aficionado
Silverlight ASP.NET Windows Forms
Course Website
http://sharepoint.ssw.com.au/Training/UTSSQL/Pages/

Course Timetable Course Materials
Course Overview
Session Date 1 Time 18:00 - 21:00 Topic SQL Server 2008 Management Studio
Tuesday 03-08-2010
Tuesday 10-08-2010
18:00 - 21:00
T-SQL Enhancements
3
4
Tuesday 17-08-2010
Tuesday 24-08-2010 Tuesday 31-08-2010
18:00 21:00
18:00 - 21:00
High Availability
CLR Integration
18:00 - 21:00
Full-Text Search
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
Homework?
CREATE DATABASE SSW_Training GO USE SSW_Training GO
CREATE LOGIN LOG_USER WITH PASSWORD=N'LOG_u$er_01', DEFAULT_DATABASE=SSW_Training GO CREATE USER LOG_USER FOR LOGIN LOG_USER GO
EXEC sp_addrolemember N'db_datareader', N'LOG_USER' GO EXEC sp_addrolemember N'db_datawriter', N'LOG_USER' GO
CREATE TABLE [ServerLogins]( ID bigint IDENTITY(1,1) NOT NULL , [DateTime] datetime , PostTime nvarchar(100) , LoginName nvarchar(100) , EventType nvarchar(100) , ClientHost nvarchar(100) , [EVENTDATA] xml ) ON [PRIMARY] GO
CREATE TRIGGER logon_trigger_login_log ON ALL SERVER WITH EXECUTE AS 'LOG_USER' FOR LOGON AS DECLARE @data xml SET @data = EventData() INSERT INTO SSW_Training.dbo.ServerLogins([DateTime], PostTime, LoginName, EventType, ClientHost, [EventData]) VALUES ( GETDATE() , CONVERT(nvarchar(100), @data.query('data(//PostTime)')) , CONVERT(nvarchar(100), @data.query('data(//LoginName)')) , CONVERT(nvarchar(100), @data.query('data(//EventType)')) , CONVERT(nvarchar(2000), @data.query('data(//ClientHost)')) , EVENTDATA() ) GO
Homework?
SELECT geometry::STGeomFromText('LINESTRING (0 0, 0 2, 1 3, 2 2, 2 0, 0 2, 2 2, 0 0, 2 0)', 0) Envelope;
Session 3
SQL 2008 High Availability Features
Agenda

What is High Availability? What can go wrong? What can we do about it?
What is high availability?
Different people have different definitions

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

Perceived uptime Performance Issues
What can go wrong? How can we improve it?
What can go wrong? (continued)
Hardware

Disk failure Network failure Power Outages
What can go wrong?
Software

Virus (and Virus Scanners) File locking issues Disk space Corrupted files Bad upgrades OS Upgrades poor tuning or design DB Maintenance
SQL

What else can go wrong?
People (PEBKAC)

Administrators Users Bottlenecks & Concurrency
Acts of God

Lightning Cleaners
What can we do about it? (continued 3-1)
1. Hardware Solutions
UPS & Hardware Monitors RAID / Mirroring Off site server Firewall Physical Security
What can we do about it? (continued 3-2)
2. Software Solutions
Database Mirroring Log shipping
Auto backup transaction log, and restore

Monitor, Change
Replication (Can also reduce availability) Database Snapshots Alerts

OS: Disk Space, ... DB: Logs,...
Partitioned Tables Firewalls
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
What Will We Cover?

Implementing Database Snapshots Configuring a Database Mirror
Partitioned Tables
SQL Agent Proxies Performing Online Index Operations
Mirrored Backups
Database Snapshots
Database Snapshots
Mirroring for reporting
Point-in-time reporting
Recover from administrative error
Protection from application or user error
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
Database Snapshots
CREATE DATABASE AdventureWorks_dbss1800 ON ( NAME = AdventureWorks2008_Data, FILENAME = 'C:\data\AdventureWorks_data_1800.ss' ) AS SNAPSHOT OF AdventureWorks; GO
Database Snapshots
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
Database mirror
Configuring a Database Mirror
No special hardware

Easy to setup Transparent client redirect Zero committed work lost Maximum one mirror per DB

Virtually no distance limitations
No special hardware

Principal Server
Clients
Witness Server
Mirror Server

Mirror Server
Clients
Witness Server
Principal Server

Mirror Server
Clients
Witness Server
Principal Server
Before you mirror your database

1.
Principal
1. 2.
Take a full backup and a log backup as well Copy the full/log backups from Principal Instance to Mirror instance
2.
Mirror
1. 2.
Restore with NORECOVERY option the full backup Apply the log backup
Using the Mirror Wizard
Configure End Point on the Principal
Configure End Point on the Mirror
Set the service accounts

Use NT AUTHORITY\NETWORK SERVICE
Mirror is configured
Warning about FQDN
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
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
Database Mirroring Monitor
Lets you view the status and history of your current mirrors
Thresholds
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
Partitioned tables
Partitioned Tables
Partitioned Tables

Allows for maximum concurrency Partitioned Table parallelism
Improved a lot in SQL 2008
Archive older data into different filegroups
SQL Server agent
SQL Server Agent (recap)

Windows Service Executes SQL Server jobs
Administrative tasks
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
Online Index Operations
Online Index Operations
Index Created Table - Table is accessible for read and update
- Non-clustered indexes are available during clustered index creation
CREATE NONCLUSTERED INDEX IX_TextTable_MyKey ON [TestTable] ([MyKey])
WITH (ONLINE = ON);

GO
Mirrored Backups
Mirrored Backup media
Backup
Mirror 2
Mirror 1
Mirror 2
Mirror 1
Mirror 2
Session Summary

Implementing Database Snapshots Configuring a Database Mirror
Partitioned Tables
SQL Agent Proxies Performing Online Index Operations
Mirrored Backups
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
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
3 things
[email protected] http://peitor.blogspot.com twitter.com/peitor
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

[email protected] www.ssw.com.au

SQL Server Database Mirroring Concept

Uploaded by

Copyright:

Available Formats

SQL Server Database Mirroring Concept

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Server Database Mirroring Concept

Uploaded by

Copyright:

Available Formats

SQL Server 2008 for Developers

UTS Short Course

Course Timetable Course Materials

What we did last week

Basic T-SQL syntax New Data Types Inline variable assignment

Table Value Parameters

CREATE DATABASE SSW_Training GO USE SSW_Training GO

SQL 2008 High Availability Features

What is high availability?

Different people have different definitions

Perceived uptime Performance

What can go wrong? How can we improve it?

What is high availability?

Different people have different definitions

Perceived uptime Performance Issues

What can go wrong? How can we improve it?

What can go wrong? (continued)

Disk failure Network failure Power Outages

What can go wrong?

What else can go wrong?

Administrators Users Bottlenecks & Concurrency

What can we do about it? (continued 3-1)

What can we do about it? (continued 3-2)

Auto backup transaction log, and restore

Replication (Can also reduce availability) Database Snapshots Alerts

Partitioned Tables Firewalls

What can we do about it? (3-3)

3. OS Level / Backup Solutions

What Will We Cover?

Implementing Database Snapshots Configuring a Database Mirror

Mirroring for reporting

Recover from administrative error

Protection from application or user error

You cannot roll forward

Query from snapshot current database

CREATE DATABASE AdventureWorks_dbss1800 ON ( NAME = AdventureWorks2008_Data, FILENAME = 'C:\data\AdventureWorks_data_1800.ss' ) AS SNAPSHOT OF AdventureWorks; GO

Snapshot vs. Backup vs. Detach

Configuring a Database Mirror

Configuring a Database Mirror

Configuring a Database Mirror

Configuring a Database Mirror

Configuring a Database Mirror

Configuring a Database Mirror

Before you mirror your database

Using the Mirror Wizard

Configure End Point on the Principal

Configure End Point on the Mirror

Set the service accounts

Warning about FQDN

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

Database Mirroring Failover

Database Mirroring Monitor

Might come in handy

Disable Mirroring ALTER DATABASE myDatabase SET PARTNER OFF

Allows for maximum concurrency Partitioned Table parallelism

Improved a lot in SQL 2008