LOGSHIPPING INTERVIEW QUESTIONS.

1)    What is Log shipping and purpose of Log shipping?

Log shipping is nothing but Shipping transaction log backup periodically one server to another server

2)    What is Primary Server, Secondary Server & Monitor Server?

a)    Primary Server:- It is a production server it holds the original copy of the database. log shipping configured in primary server.

b)    Secondary Server:- it holds stand by copy of the database.we must intialise the database on secondary server by restoring a backup fromprimary server usingeither recovery or stand by option. 

c)     Monitor Server:- An optional Server is called as Monitor Server that records history and status of backup, copy and restore operations and raises alerts if any operations fail. The Monitor Sever should be on separate server to avoid losing critical information. Single Monitor Server monitors multiple Log shipping configurations.

3)    What are the Jobs running for Log shipping and explain them?

Log shipping having four operations which are handled by SQL Server Agent Job.

a)  Backup Job: - Backup job is created on Primary Server instance and it performs backup operation. It logs history on the local server and monitor severs and deletes old backup files and history information.

b)  Copy Job: - Copy Job is created on Secondary server instance and it performs copies the backup files from primary sever to secondary server. It logs history on the secondary server and monitor server.

c)   Restore Job: - Restore Job is created on the Secondary server instance and it performs restore operation. It logs history on the local server and monitor sever and deletes old files and history information.

d)  Alert Job: - If a Monitor Server is used, the Alert Jobs is created on the Monitor server instance and it raises Alerts if any operations have not completed successfully.

4)    Requirements for Log shipping?

a)    sql enterprise or workgroup ,standby installed on all server involved in logons.

b)    data should be in full or bulk recovery model.

c)     case sensitive setting should be same on both servers.

5)    How to configure Log shipping?

a.   Choose Primary Server, Secondary Servers, and optional Monitor server.

b.   Create a File share to keep Transaction log backups (Best to place on a separate computer)

c.   Create a folder for each Secondary server into which transaction log backup copies.

d.   Choose Backup Schedule for Primary Database

e.   Choose Copy and Restore Schedules for Secondary Database

f.    Choose Alert Job schedule for Monitor Server if configured

6)    What are permissions required for Log shipping?

We must have sysadmin on each server instance to configure Log shipping.

7)    In Logshipping which Recovery Models can we used?

We can use either full or bulk logged recovery model for log shipping.

8)    Where you monitoring Log shipping and how?

The following methods can use for monitoring Log shipping.

a)  Monitor server (History Tables):- Monitor Server tracks all statistics, status and errors that could be happen during Log shipping.

1)  Log_shipping_monitor_primary:- Stores primary server status

2)  Log_shipping_monitor_secondary:- Stores secondary servers status

3)  Log_shipping_monitor_history_detail:- Contains history details for logshipping agents.

4)  Log_shipping_monitor_error_detail:- Stores error details for log shipping jobs.

5)  Log_shipping_monitor_alert:- Stores Alert Job ID

b)  System Stored Procedures (MSDB):- System Stored procedures gives the history information about the specified server that are configured in Log shipping.

1)  sp_help_log_shipping_monitor (Run at Monitor Server)

2)  sp_help_log_shipping_monitor_primary @Primary_Database = ‘DBName’ (Run at MS)

3)  sp_help_log_shipping_monitor_secondary @ Secondary_Database = ‘DBName’ (Run at MS)

4)  sp_help_log_shipping_alert_job (Run at Mon Server)

5)  sp_help_log_shipping_primary_database @ Database = ‘DBName’ (Run at Primary Server)

6)  sp_help_log_shipping_secondary_database @ Database = ‘DBName’ (Run at Sec Server)

c)   Transaction Log shipping Status report (Summary Reports):-  This report shows the status of log shipping configurations for which this server instance is a primary, secondary or monitor.

d)  SQL Server Agent Job Histor:- Right click on Jobs > View history

e)  Checking the SQL Server Log

9)    How to failover secondary server, when the Primary Server fails?

If the Primary Server will become un-available, do the following steps.

a)    Take the Tail of Log from Primary server if possible.

b)    Restore Tail of log into all Secondary Database

c)     Remove Log-shipping configuration from Primary Server

d)    Select any one of Secondary server and bring into online with Alter Database DBName set Online

e)    Right click on Primary Database and Generate script for Users and Logins.

f)     Then move the script to Secondary server to create Users and Logins

g)    Re-configure log shipping from New Server (Secondary server)

10) What are errors occurred in Log shipping?

There are two errors are occurred during Log shipping

1)    14420:- This error occurs when the Backup job fails

2)    14421:- This error occurs when the Restoring job fails

11) What is.tuf file.?

while restoring log back up on secondary server uncommited transctions are come to .tuf file.