Just be aware that you might can face in the interview (even though simple question) -what is the new feature that introduced in SQL server 2008 for log shipping ?
A)In earlier editions (< SQL server 2008) you can set the backup/copy/restore job frequency to Minutes and Hours. However now we can schedule to in terms of seconds (how ever I damn sure that it will have lot of other problems as well)
B)Backup compressed(log backup compression)
1.Can we configure log shipping if the SQL server service account is running under local system account?
Sol ->yes we can see below per BOL-
“If the SQL Server service account is running under the local system account on the primary server, you must create the backup folder on the primary server and specify the local path to that folder here. The SQL Server service account of the primary server instance must have Read and Write permissions on this folder”.
2.What editions requires to configure logshipping?
>>Apart from express all other edition supports but in the versions wise is different
A)For SQL server 2000
Primary Server ->Enterprise or Developer Edition
Secondary Server ->Enterprise or Developer Edition
Monitor Server ->Any Edition
B)for SQL server 2005(per BOL)-
Log shipping has the following requirements:
!) SQL Server 2005 Standard Edition, SQL Server 2005 Workgroup Edition, or SQL Server 2005 Enterprise Edition must be installed on all server instances involved in log shipping.
!!) The servers involved in log shipping should have the same case sensitivity settings.
!!!)The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.
C)For SQL server 2008 ->all except express..
3.what permission requires for shared folders on Primary and secondary for the service accounts-
>>As per BOL
For the backup job, read/write permissions to the backup directory are required on the following:
The SQL Server service account on the primary server instance.
The proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance.
For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.
For the restore job, read/write permission to the copy directory are required by the following:
The SQL Server service account on the secondary server instance.
The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.
4)What happens to the log shipping in case if you have added the data file on the primary server ?
>>if both primary & secondary servers as same Disk configuration settings then you can ignore & secondary will takes up, how ever if you changed anything at the Primary side for ex->you have created the folder & added the folder or you have added to any other drive then you have to restore the Next trn backup (i,e after adding the datafile) with
MOVE option(note that you have specified the Tuf file as well while restoring the trn by Manually).
5)what is index operation logging in log shipping?
>>It is fully logged operation so it will replicate on secondary as well.
6)What about If I shrink at the primary database will that log to secondary as database as well?
>>yes it can.
7)Types of logshipping or types of restoring modes?
>>A)With No recovery B)Standby/readonly
8)What it TUF file?
>>TUF stands for ->T ->Transaction U->Undo F->File so it is Transaction Undo file.
You know that the log backup/copy/restore works according to the scheduled frequency.
It contains the Modification that were not committed on the Primary database when the Transaction log backup was in progress and when the log was restored to another database…so at some point of time when the next trn restored on the secondary side then SQL server uses the data from the Undo file and it starts restoring the Incomplete transactions(so here you have to think that next log backup completed from Primary side)….Also it requires to define when ever reconfiguring of the log shipping or during first configure..
9)My Tuf file deleted unfortunate when the server was shutdown or during the server down time then what will happens to the logshipping?
>>The log shipping will not work, Incase if you have OS level Backup then you can restore the file then you can be cool.
10)My Logshipping is not working–what to do now?
>>The first thing you need to look at your Log shipping job history ->Backup/copy/restore –>(think that all jobs are failing) or incase if it is specific then you need to look at the specific one for ex-
Backup job history on Primary
COPY and Restore history on secondary.
Some reasons the Logshipping can break or stop working..
1. Recent modifications(permissions removed) on the shared folders(Might be on Primary shared folder or on at the secondary side).
2.Human error ->either someone used the option of truncate only or switched the recovery model.
3.date/time for the windows servers unmatching due to any DST activities.
4.As usual ->Datafile added on Primary on different drives ->then you need to apply that on secondary with move until that your log shipping restore job will fail
5.Any I/O,Memory,N/w bottleneck–Please look at the error logs & event logs
7.your tuf file is missing.
8.sqllogship.exe –hahahaha some one deleted..
9. Drives full
10. Improper SP/hot fix applied.
11.You may have set the incorrect value for the Out of Sync Alert threshold.
12. Might be you have scheduled the jobs in same time
13.your MSDB database is full.