SQL Server Log Shipping Interview Questions.

Which jobs get created after enabling log shopping?

4 jobs get created. Backup job on Primary Server, copy job on Secondary server, restore job on Secondary server and alert job on the Secondary server.

A user started complaining he is not able to access database once failover is done from primary server to secondary server? What is the reason and how to fix it?

The user’s corresponding login might not exist on the Secondary server. We need to create the login of the user in secondary server similar to the login in the primary server.

What are TUF and WRK files?

Transaction Undo File. This file gets created only if the Secondary server is in STANDBY mode. This file holds all uncommitted transactions and SQL Server will use this file to check which earlier uncommitted transactions are committed/rolled back. Accordingly, it will write the data to disk.

Transaction Log backups file extensions will be changed to ‘.WRK’ extension while getting copied from Primary server to the Secondary server. Once the backup file gets copied to the secondary server fully then the log backup file extension will be changed back to ‘.trn’. This helps in blocking restores to happen when the file is getting copied.

What happens if TUF file will corrupt?

Log shipping will fail if TUF file gets corrupted. We need to set up log shipping again to fix the issue.

Can we add new data file to primary database? What happens after we try to add?

Yes, file can be added to the primary database. After adding the file to the primary database, if the same path exists in the secondary server then the file gets added to the secondary database as well. If the same file path doesn't exist in the secondary server then log shipping fails.

Can we add new data file to secondary database? What happens after we try to add?

No, we can't add a new file to the secondary database directly as it will be either in restoring or standby (read only) mode.

Does log shipping support all recovery models?

No. Only FULL and Bulk-Logged recovery models are supported.

How to failover database from primary to secondary?

»    Disable all jobs (backup, copy and restore) on primary and secondary servers.

»    Apply all pending log backups to the secondary server by first copying and then restoring them to the secondary database.

»    Take tail log backup of the primary database with NORECOVERY option. Primary database will go into restoring state after this.

»    Restore all the pending log backups to secondary and finally restore the tail log backup as well with RECOVERY option.

»    This will bring the secondary database ONLINE, now configure log shipping in secondary (to make this as primary).

»    Now secondary (current primary) will start acting as a primary database for log shipping.

»    Now run all the new backup, copy and restore jobs that got created. This will make the old primary as a secondary database for log shipping.

How to find what was the last restored transaction log in log shipping?

There are several ways to find this out:

»    We can use the table “select * from msdb..log_shipping_monitor_secondary”

»    We can check ‘Transaction Log shipping reports’ report in standard reports at the instance level.

»    We can manually check ‘backup and restore reports’ in standard reports at the database level.

»    We can manually verify the backup and restore jobs timings and their history to check when was the last time they executed successfully.

How to apply Service Pack / Hot Fixes when Log Shipping is enabled?

How to install service packs and hotfixes on an instance of SQL Server that is configured to use log shipping?

»    First apply Service Pack on Monitor Server (if you have a Monitor Server).

»    Next apply the same update on Secondary Server (if more than one secondary apply on all secondary’s) of Log shipping.

»    Finally apply the same update on Primary Server.

Can we have different versions of primary and secondary servers in log shipping?

Can we configure log shipping between different SQL Server versions? Like Primary on SQL Server 2012 and Secondary on SQL Server 2014?

Yes, we can configure log shipping from lower version of SQL Server to higher version. But in case of failover you will have issues like we cannot configure log shipping from higher to lower version.

We can configure log shipping from SQL 2012 to SQL 2014. We will receive error similar to below when we try to configure log shipping from higher version to lower version of SQL Server:

Restore failed for Server 'SQL2012'.  (SqlManagerUI)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1322+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------

System.Data.SqlClient.SqlError: The database was backed up on a server running version 12.00.2269. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1322+)&LinkId=20476

I will keep adding new questions to this list. Please share the questions you are aware of.

#LogShipping Questions, #Log Shipping Interview Questions, log shipping interview questions, Log Shipping, log shipping questions

SQL Server Cluster Interview Questions.

SQL Server AlwaysOn Availability group interview questions.

Thanks VV!!