SQL Server DBA Interview question and answers Part 9

1.  If I shrink primary database what happen to log shipping in secondary database ?

Shrinking: if u use shrinking database release spaces in os to your disk

If we perform shrink in primary database automatically shrinking replicate to secondary database as well...No impact to log shipping configuration

2.  If I take a manual full backup for LS Configured database will it impact secondary database ?

Nothing to impact Log shipping.

Due to LSN mismatch log shipping will not work (restore job fail)

Note: Recommended in LS take copy only backup if require to take full backup

3. If I take a manual log backup for LS configured will it impact secondary database .

Due to LSN mismatch log shipping will not work (restore job)

Note: Due to this reason in log shipping if any user used to take adhoc full backup then we will always USE” COPY ONLY FULL BACKUP “ not to distribute any LSN number and to work log shipping as usual.

4. Adding multiple secondary server to the existing configuration ?

1. Get confirmation from client\customer.

2. Go to primary server> database>properties>Log shipping> add> secondary instance details+copy share location+db recovery state.

3. In 2nd secondary server again create additional copy and restore jobs.

Note: multiple secondary server > each secondary server should contains at least 1 copy and 1 restore jobs.

No downtime is required for adding secondary server.

4. How to add files to the logshipping database ?

1. Go to primary database add secondary .ndf or .ldf file.

Impact: After adding a file to log shipping db then there will be no impact to backup job, copy job but restore job gets fail.

2. Require to perform manual restoration in secondary server.

3. Identify after adding a file what is the recent backup is happened then confirm by perform

RESTORE FILELISTONLY FROM DISK='PATH'

Note: All backup files are moved to copy share and restored expect backup file after file added

4. Go to secondary and try to restore the log backup file with move option,

Restore log dbname from disk='[path of log backup file n copy folder]'

With move 'logical name of file' to 'physical path of secondary', no recovery

Ex:

RESTORE log TestDB
FROM DISK = 'Drive: \FolderNmae\DBName_20141129024726.trn'
WITH MOVE 'TestDB _File1' 

TO 'c: \Program Files (x86)\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\TestDB_File1.ndf'
 ,no recovery

5. Verify by running restore job whether log shipping is working or not. If works, log shipping is in sync.

Note: Always keep same version of Sql between primary and secondary

If primary -2005 and secondary-2008\higher

Then you can perform all operations but except SWICHOVER Scenario in log shipping.

Note:-

1. In log shipping for the version of Sql 2005 if any changes performed at primary server but the changes are failed.

2. To roll back the data we need to restore with fresh full backup, after restoring full backup if I enable log shipping jobs my restore job is going to fail due to LSN number mismatch .

Same in Sql server 2008 version just take a fresh log backup from primary and restore in secondary automatically Log shipping will going to start.

5. How to remove a file into Logshipping databases.

If we remove file in primary then automatically remove from secondary after backup restore.

Note: No impact to backup, copy and restore jobs.

6. Patch Management process in LS Instance ?

When an instance of SQL Server is configured as part of Log Shipping, t is important to install service packs in a correct sequence; otherwise we may get unexpected issues.

For Log Shipping:

There is no required sequence to apply a service pack for Primary, Secondary and Monitor servers in a Log Shipping environment. The following is the steps about apply service pack:

1. Apply the service pack on the Monitor server.

2. Apply the service pack on the all Secondary servers.

3. Apply the service pack on the Primary server.

Dbcc sqlperf (logspace):- Find out the log file size

7. What are the reasons for Backup , Copy and Restore Jobs fails ?

Backup job failure:

Agent failure

Disk space issue

MSDB database corruption

Sharing permission issue.

Recovery model changes

Incorrect path

Backup job disabled.

Copy Job Failure:

Lack of permission on backup folder

Network failure

Domain issue

Copy job disable

Job owner changes

Owner has no permissions.

Restore Job failure:

            LSN Mismatch due to log backup file missing

Permission issue on local copy folder

Agent down

Job owner changes

Job owner has no restore permissions.

8. Can I set up Log shipping  between servers in Multiple domains ?

Yes. It is possible to set up log shipping between servers that are in separate domains.

In SQL 2005 version: Not possible to configure log shipping between 2 different domains.

In SQL 2008 version onwards: Possible to configure log shipping between 2 different domains.

There are two ways to do this:

     Use pass-through security. Configure Windows NT accounts with the same name and passwords on the primary, secondary and monitor servers. Configure SQL Server related services to start under these accounts on all servers and use SQL authentication while setting up log shipping to connect to the monitor server. Or

Use conventional Windows NT security. You must configure the domains with two-way trusts. SQL Server related services can be started under domain accounts. Either SQL authentication or Windows authentication can be used by jobs on the primary and secondary servers to connect to the monitor server

9. What to do if my standby database crashes ?

•      Re-establish log shipping in case of one standby

•      Re-establish log shipping only on standby server (i.e. remove the Copy/Restore/Alert jobs and delete the standby database) and add the Standby instance as a new standby database.