Ms SQL Server Log Shipping Kathmandu August 2009

Ms SQL Server - Log Shipping Kathmandu, August, 2009

They told me it is important to know your {audience}

So on the count of 3 {SHOUT} your first name

1

2

3

{Who} am I? Shree Prasad Khanal MCSD - MCDBA - MCSA – MCAD - MCT - MCPD Enterprise Developer MCITP Database Administrator, Database Developer & BI Developer +18 MCTS Database and Business Intelligence shree@sqlpassnepal. org www. sqlpassnepal. org

{What} are we here for? �Overview of Log Shipping �What do you need before configuring �Key things to be considered �Monitoring the process �Demo �Failing over �Q & A

{Who} are you? A database administrator (DBA) is a person who is responsible for the environmental aspects of a database. In general, these include: Recoverability - Creating and testing Backups Integrity - Verifying or helping to verify data integrity Security - Defining and/or implementing access controls to the data Availability - Ensuring maximum uptime Performance - Ensuring maximum performance given budgetary constraints Development and testing support - Helping programmers and engineers to efficiently utilize the database.

{Where} do you come from?

So I asked. . {Where} do you come from?

Did you have {Fun} along the road?

Log Shipping What It Is Not

Overview “Log shipping is a process of making backup and restore operations of a database, automated which results to one or more warm standby servers. ”

What do you need before Configuring �Enterprise, Standard or Workgroup edition �Secondary server for standby database �Full or Bulk-logged recovery model �SQL Agent service running at both servers �Backup and destination folders

Log Shipping What It Is Monitor Primary (Source server) Standby (Destination server) Backup Transaction Log Restore Network … Users Client workstations 17

Log-Shipping Backup/Restore based technology that relies on transaction log files Need to have a shared folder that contains the log file backups Configure the frequency of backups and shipping No automatic failover Allows you to replicate data to several databases (one to many) Recommended to use Log Marking to keep databases in sync

Key things to be considered �Configuration of the standby server �Permission for the backup and destination folder �Backup operation schedule �Initialization of secondary database �Restore operation schedule �Expect high availability or scaling out? �Backup of all SQL logins �Avoid other backup implementation �SQL Server 2000 databases are not compatible

Log Shipping At A Glance Each action is governed by a separate SQL Agent primary server upthe SQL Agent onon secondary serverbacks copies SQL Agent on secondary server restores job and can be controlled and transaction log to own primary share transaction log its local share transaction log to the local database scheduled independently

Monitoring Log Shipping �MSDB database for job execution information �Store local info only �log_shipping_monitor_error_detail and log_shipping_monitor_history_detail tables �One server for monitoring �Later configuring of monitor requires reconfiguring of Log Shipping

Demo

Failing Over �Disable the log shipping in the primary if it is available �Backup the tail of the transaction log if it is available �Restore remaining transaction log backups with “WITH RECOVERY”

Are you {Ready} for SQL 2008?

Q&A

And {More} Next…………. . SQL Server 2008 Advanced Administration Topics include: Security High Availability Upgrading {and More}

Get {Ready} for SQL 2008! But {first}. . . complete your evaluation form Shree Prasad Khanal Database and Business Intelligence shreekhanal@hotmail. com

I {don’t know} I am not an {MVP}