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,
Thanks VV!!
