sql server database mirroring concept
TRANSCRIPT
SQL Server 2008 for DevelopersUTS Short Course
Specializes in
C# and .NET (Java not anymore)
TestingAutomated tests
Agile, ScrumCertified Scrum Trainer
Technology aficionado • Silverlight• ASP.NET• Windows Forms
Peter Gfader
http://sharepoint.ssw.com.au/Training/UTSSQL/Pages/
Course Timetable Course Materials
Course Website
Course OverviewSession
Date Time Topic
1Tuesday03-08-2010
18:00 - 21:00
SQL Server 2008 Management Studio
2Tuesday10-08-2010
18:00 - 21:00
T-SQL Enhancements
3 Tuesday17-08-2010
18:00 - 21:00 High Availability
4Tuesday24-08-2010
18:00 - 21:00
CLR Integration
5Tuesday31-08-2010
18:00 - 21:00
Full-Text Search
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
What we did last week
Homework?
CREATE DATABASE SSW_TrainingGO
USE SSW_TrainingGO
CREATE LOGIN LOG_USER WITH PASSWORD=N'LOG_u$er_01', DEFAULT_DATABASE=SSW_TrainingGO
CREATE USER LOG_USER FOR LOGIN LOG_USERGO
EXEC sp_addrolemember N'db_datareader', N'LOG_USER'GOEXEC 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_logON ALL SERVER WITH EXECUTE AS 'LOG_USER'FOR LOGONAS
DECLARE @data xmlSET @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
SELECT geometry::STGeomFromText('LINESTRING (0 0,
0 2, 1 3, 2 2, 2 0, 0 2, 2 2, 0 0, 2 0)', 0)
Envelope;
Homework?
SQL 2008 High Availability Features
Session 3
What is High Availability?
What can go wrong?
What can we do about it?
AgendaAgenda
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 is high availability?
Hardware Disk failure Network failure Power Outages
What can go wrong? (continued)
Software Virus (and Virus Scanners) – File locking issues Disk space Corrupted files Bad upgrades OS Upgrades
SQL poor tuning or design DB Maintenance
What can go wrong?
People (PEBKAC)
Administrators Users
• Bottlenecks & Concurrency
Acts of God
Lightning Cleaners
What else can go wrong?
1. Hardware Solutions• UPS & Hardware Monitors• RAID / Mirroring• Off site server• Firewall• Physical Security
What can we do about it? (continued 3-1)
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
What can we do about it? (continued 3-2)
3. OS Level / Backup Solutions• Security• Change Management• Performance Monitoring/Tuning• Hot/Warm/Cold standby servers• Standard daily backups
– Verified procedure– Transaction logs
What can we do about it? (3-3)
Implementing Database Snapshots
Configuring a Database Mirror
Partitioned Tables
SQL Agent Proxies
Performing Online Index Operations
Mirrored Backups
What Will We Cover?
Database Snapshots
Database Snapshots
Mirroring for reporting
Recover from administrative error
Point-in-time reporting
Protection from application or user error
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
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
No special hardware
Configuring a Database Mirror
Configuring a Database Mirror
Easy to setup
Zero committed work lost Maximum one mirror per DB
Transparent client redirect
Virtually no distance limitations
No special hardware
Configuring a Database Mirror
Clients
Principal Server
Witness Server
Mirror Server
Configuring a Database Mirror
Configuring a Database Mirror
Clients
Witness Server
Mirror Server
Principal Server
Clients
Witness Server
Mirror Server
Principal Server
Configuring a Database Mirror
1. Principal
1. Take a full backup and a log backup as well2. Copy the full/log backups from Principal Instance to
Mirror instance
2. Mirror
1. Restore with NORECOVERY option the full backup2. Apply the log backup
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
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 MirroringALTER DATABASE myDatabase SET PARTNER OFF
Put DB from "Recovering..." into available online modeRESTORE DATABASE myDatabase WITH RECOVERY
Partitioned tables
Partitioned Tables
Allows for maximum concurrency
Partitioned Table parallelism Improved a lot in SQL 2008
Archive older data into different filegroups
Partitioned Tables
SQL Server agent
Windows Service
Executes SQL Server jobs
Administrative tasks
SQL Server Agent (recap)
New credential system
Sits on Active Directory
Fine grained control of your jobs
Jobs can be run by proxies instead of user loginsPreviously to run cmd shell type functionality you needed a user in the administrator group which opened up security problems
SQL Agent Proxies
Online Index Operations
Online Index Operations
Table
Index Created
- Table is accessible for read and update- Non-clustered indexes are available during clustered index creation
- 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
Mirror 2
Backup
Mirrored Backup media
Mirror 2
Mirror 1
Mirror 2
Mirror 1
Mirrored Backup media
Implementing Database Snapshots
Configuring a Database Mirror
Partitioned Tables
SQL Agent Proxies
Performing Online Index Operations
Mirrored Backups
Session Summary
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
Session 3 Lab
• 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
Where Else Can I Get Help?
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