Microsoft SQL Server 2000 Log Shipping Reinaldo Kibel Support Engineer Developer Support SQL Server

1
Microsoft SQL Server 2000 Log ShippingReinaldo
KibelSupport EngineerDeveloper Support - SQL
Server Microsoft Corporation
2
Agenda

• Disaster recovery options on Microsoft SQL
  Server 6.5 and 7.0
• The new log shipping on Microsoft SQL Server
  2000 and replication and clustering

3
Disaster RecoveryOptions

• Log shipping - standby (warm backup) server
• Snapshot backups
• Cluster Server
• Replication (not recommended)You have to keep up
  with security/DDL modifications manually

4
Log ShippingWhat It Is Not
5
Log ShippingWhat It Is
Monitor
Primary(Source server)
Standby(Destination server)
Transaction Log
Backup
Restore
Network

Users Client workstations
6
Log Shipping Software Requirements

• SQL Server version
• SQL Server 2000 Enterprise Edition
• Operating system versions
• Microsoft Windows NT 4.0
• Microsoft Windows NT 4.0 Enterprise Edition
• Microsoft Windows 2000 Server
• Microsoft Windows 2000 Advanced Server
• Microsoft Windows 2000 Datacenter Server

7
Log Shipping

• Built into SQL Server 2000
• Robust due to technology improvements
• Recovery models
• No operation conflicts
• No non-recoverable operations
• Fast failback
• Supercharge with snapshot backups
• Integrated with replication
• Protects you from logical failures as well as
  physical failures

8
How Should I Deploy Log Shipping?

• Log shipping makes a nice failure protection
  scenario
• You can set the delay for restoring the logs
• 1 to 15 minutes is what we typically see
• 1 minute for availability
• 15 minutes for logical data protection

Only if manually changed after initial setup
9
Questions to Ask

• For availability, where is your standby server
  sitting?
• Probably best if it is on a different subnet in a
  different data center
• Even better if it is on a separate public power
  grid
• How fast is the connection to your standby
  server?
• Logs can be fairly large
• What about client connectivity?
• Do you want to use a hardware-assisted solution
• Snapshot backup

10
What Is Your Switch Strategy?

• How are you going to switch to the standby
  server?
• How many transactions are you going to lose?
• How much is acceptable?
• After you are running on the standby server, does
  it have the same capacity as your production
  server?

11
What Is Your Switch Strategy? (2)

• How are you going to switch back?
• Log backups taken on secondary can be applied
  back on primary
• This assumes that your backups are intact
• Basically this is great for planned failovers
  (such as primary server OS upgrades)
• Think about the other case (it wasnt planned) ?

12
Automated Log Shipping

• Easy setup using Maintenance Plan
• Easy administration using Monitor
• Tracks status
• Facilitates role change
• Reachable from remoteEnterprise Manager
• Flexible
• The log can be backed up despite a damaged
  primary file

Primary
Secondary
Monitor
Secondary
13
Log Shipping Features

• Multiple secondaries from one primary
• Different settings for different purposes
• Multiple databases per instance
• Variable
• Backup frequency
• Copy frequency
• Restore latency
• Setup optionally creates secondary database
• Flexible actions and alerts

Flexibility
14
Changing Roles
Secondary
Primary

• Role change initiated at monitor or secondary
• Monitor coordinates change
• Remaining secondaries pick up from new primary
• Monitor is critical component
• Should be fault tolerant
• Can initiate role change from secondary if
  monitor is down not recommended
• Has to be specified during setup

15
Log ShippingFast Failback

• Database restore not required if the data and log
  files are not damaged
• BACKUP LOG TO WITH NORECOVERY STANDBY
• Log backups taken on secondary can be applied
  back on primary
• Very useful for planned failover
• Enables high availability reporting server
  toggle
• Not fully automated
• Must set up log shippingon new primary

16
Available Backup TypesFor Each Model
17
Log Backups in Bulk_Logged Mode

• Log backup contains extents modified by bulk
  logged operations since last log backup
• Avoids breaking the log chain
• Eliminates need for special full backup
• ?Minimal impact on automated backups
• ?Log shipping works!

Transaction Log
Log Backup
Data Extents
Extent modified by bulk logged operation
18
Caveats How Do I?

• The log can be backed up despite a damaged
  primary file
• Set database options with ALTER DATABASE instead
  of sp_dboption we also added the option to
  kill/kick users out of a database without
  shutting down SQL Server
• ALTER DATABASE PUBSset SINGLE_USER with ROLLBACK
  IMMEDIATEorALTER DATABASE PUBSset SINGLE_USER
  with ROLLBACK AFTER integer

19
Caveats Log and File Backup

• Flexibility
• Log and file backups dont block each other
• Files are independent of each other
• Must do concurrent file backups as a single
  operation

Time
Log
Log
Log
Log
Log
Log
File 1
File 2
File 3
File 4
20
Log ShippingTables (Primary)

• log_shipping_databases
• log_shipping_monitor
• log_shipping_primaries
• sysdbmaintplans
• sysjobs
• syslogins

21
Log ShippingTables (Standby)

• log_shipping_monitor
• log_shipping_plan_databases
• log_shipping_plan_history
• log_shipping_plans
• log_shipping_secondaries
• sysdbmaintplans
• sysjobs
• syslogins

22
Log ShippingStored Procedures

• sp_add_log_shipping_database
• sp_delete_log_shipping_database
• sp_add_log_shipping_plan
• sp_delete_log_shipping_plan
• sp_add_log_shipping_plan_database
• sp_delete_log_shipping_plan_database
• sp_add_log_shipping_primary
• sp_delete_log_shipping_primary
• sp_add_log_shipping_secondary
• sp_delete_log_shipping_secondary

23
Log ShippingStored Procedures (2)

• sp_get_log_shipping_monitor_info
• sp_can_tlog_be_applied
• sp_change_monitor_role
• sp_remove_log_shipping_monitor
• sp_change_ primary_role
• sp_resolve_logins
• sp_update_log_shipping_monitor_info
• sp_create_log_shipping_monitor_account

24
Log ShippingStored Procedures (3)

• sp_update_log_shipping_plan
• sp_define_log_shipping_monitor
• sp_update_log_shipping_plan_database
• sp_change_ secondary_role

25
Log ShippingRole Change

• Create a DTS package with Transfer logins task -
  back up the syslogins table
• Create a job schedule that runs either one time
  or on a recurrent basis
• In order to perform an actual role change
• Run msdb..sp_change_primary_role on current
  primary
• Run msdb..sp_change_secondary_role on current
  secondary
• Run msdb..sp_change_monitor_role on monitor
  server
• Run master.. sp_resolve_logins on new primary

26
Differences Between 7.0 SP2 and SQL 2000

• No GUI manual configuration
• Intended only for temp solution
• No automatic way of role change
• SQL Server 7.0 does not need to be Enterprise
  Edition
• Cannot switch back
• SQL Server 7.0 Service Pack 2 (SP2) introduces a
  pending upgrade database option
• On SQL Server 7.0, pending upgrade is set to True
  on database to be log shipped
• Cannot create clustered index on a table if non-
  clustered index already exists

27
Differences Between 7.0 SP2 and SQL 2000 (2)

• On primary server
• Create a local share to be used by transaction
  log backups
• Create a database maintenance plan on primary
  server to take database backup and recurring
  transaction log backup (shared)
• On secondary server
• Restore the database with norecovery mode
• Run msdb..dbo.sp_add_log_shipping_plan
• Run msdb..dbo.sp_add_log_shipping_plan_database
  to add a database to the log shipping plan
  created above
• The status of log shipping can be monitored by
  viewing the job history of copy/restore jobs

28
Log ShippingOther Options
Monitor
Transaction Log
inventory
inventory
Transaction Log
customers
customers
Transaction Log
orders
orders
Transaction Log
monitor
standby
primary
29
NLB and Log Shipping

• One way to handle switching is with NLB (WLBS)
• Setup
• You need a private network between the two log
  shipping SQL Servers
• Need to resolve IP addresses using LMHosts or a
  WINS server
• Make sure you periodically sync master and msdb

30
NLB and Log Shipping (2)

• Manually switch NLB when primary goes down
• Clients dont have to know that theyre
  connecting to a new SQL Server
• But the clients cant depend on the server name
  either
• One solution is to use IIS server to point to SQL
  Server
• Problem requires intervention
• Maybe this is okay in the planned downtime case

31
NLB and Log Shipping (3)
32
NLB and Log Shipping (4)with IIS
VIP
33
NLB and Log Shipping (5)Example Planned Failover

• Standby B is used for reporting
• Primary A prepares next days reporting
  database
• At predetermined time
• A is restored with standby and receives new
  connections
• After existing sessions end, B is rolled
  forward and recovered
• B is updated

B
B
A
A
Load/Update
Reporting
Load/Update
Reporting
Planned Failover
NLB
NLB
Client
Client
34
Log Shipping with SnapshotHardware-assisted
Solution

• Create database on standby with snapshot
  backup/restore
• Set up log shipping
• Conventional log backups are copied over network
  or to disk, and are transferred the same way
• Split mirror during database backup
• Mount volume during database restore to standby

Primary
Standby
Disaster Recovery Site
Split Mirror
Remote Mirroring
35
Log Shipping with Snapshot (2)Hardware-assisted
Solution

• Very fast restore
• From mirror in seconds
• Longer from tape
• Back up with the minimum possible impact on
  production server
• Very fast backup
• Create reporting or test database
• Initialize warm standby

36
Log Shipping with Replication

• Replication can fail over
• Typically youd protect the publisher

Before
After
Role change
Distributor
Distributor
Subscribers
Subscribers
37
Log Shipping with Replication (2)Choices

• Synchronous model
• Fault tolerant
• Slower change propagation to subscribers
• Semi-synchronous model
• Subscribers are updated as changes occur
• Subscribers see duplicate changes in some failure
  situations but replication designed to handle
  this now

38
Log Shipping and Cluster

• To protect against both logical and physical
  failures

Log ship standby server for each Virtual SQL
Server
39
Log Shipping and Cluster (2) Cluster Benefits

• Failover clustering is a good enterprise solution
• Clients dont necessarily need to understand
  clusters because the server name/IP address
  doesnt change on failover
• Single copy of data
• Fewer synchronization issues
• No data integrity concerns
• May require more expensive hardware

40
Log Shipping and Cluster (3) Log Shipping
Benefits

• Hot standby has a separate copy of the data
• Use for DSS/reporting
• Provides for logical data protection
• Hot standby need not be geographically close, but
  distance clusters may make this moot
• Can have multiple backup servers with log
  shipping
• Clients probably need to know name and location
  of log shipping server
• Unless you use NLB/WLBS

41
Summary

• Log shipping just works on SQL Server 2000
• Only available on Enterprise Edition
• Log shipping is helpful, great when combined with
  a cluster
• Also often used for reasons other than
  availability

42
(No Transcript)