1. What High Availability options
are available in SQL Server?
There are five high availability
options available in SQL server
1. Mirroring
2. Log Shipping
3. Replication
4. Clustering
5. Always-ON
2. What is Log Shipping?
Log shipping is DR (disaster
recovery) feature in SQL server where one or more than one warm standby
databases are maintained on separate secondary servers in case Primary server
is crashed, secondary servers can be used
to host the application. T-log backup of Primary database is automatically
shipped from primary server to secondary servers and applied to secondary
databases individually so that Primary and secondary databases can be in SYNC.
3. How many server are used in
Log Shipping?
1. Primary server: Primary database
(Original) resides on Primary server
2. Secondary server: Copy of
Primary database is maintained on secondary servers
3. Monitor server (Optional):
Single Monitor server is used to monitor multiple log shipping configurations
among primary and secondary databases. This server is used to keep the history
and status of backup/restore operations. Monitor server also raise the alert if
Log shipping is out of SYNC and any Log shipping Job fails.
4. What are the Pre-requisite for Log-Shipping?
1. To set-up log shipping we must
have SYSADMIN rights on the server
2. SQL server version should be
SQL 2005 or later.
3. Database must be in FULL or
BULK LOGGED recovery model so T-logs can be applied.
4. SQL
Enterprise,Standard,Workgroup,Developer edition must installed on the servers
participating in log shipping.
5. Servers must have the same
case sensitivity settings.
6. SQL server services on both
Primary and secondary server should use the same domain account.
7. Shared folder should be
created in Primary server to hold the T-log backup. SQL service account of
primary should have Read/Write permission on the shared folder for backup job
to be successful. Agent account of secondary should have Read Permission
on the Shared folder of Primary server for copy job to be successful.
5. What recovery models are required to configure the Log Shipping?
Log shipping works with FULL and
BULK LOGGED recovery model.
6. What are advantages of Log shipping?
1. Easily Configured. It can be
configured using GUI wizard.
2. Log shipping doesn’t require
expensive hardware that’s why most of corporate org use it.
3. Low maintenance and very less
troubleshooting is required for log shipping.
4. Secondary server can be used
for reporting purpose to offload the primary server.
5. Manually fail-over process is
very short, it takes around 10-15 minutes.
6. We can have multiple secondary
databases which can be used for different purpose, one server can be used for
HA and another one for SQL server reporting.
7. What are Disadvantage of Log Shipping?
1. No Automatic Fail-over: DBA
needs to manually fail-over the server.
2. Downtime is required when
there is fail-over.
3. Possible Data Loss: Some data
can lost in case of server crash, it depends on schedule of Log Shipping jobs.
4. Log shipping set-up cannot be
scripted.
5. Each database which needs to
be log shipped should be set up through separate Log shipping Wizard.
8. Is it possible to configure Log Shipping from lower version to upper version and Vice versa?
Yes, We can configure Log Shipping from lower to upper version. But it is not possible vice versa. Primary server can be SQL 2008 and secondary 2012 but opposite is not possible.
9. What all jobs are created after configuring the Log Shipping?
when Log Shipping is configured, there are 4 jobs created between Primary Server and Secondary Server.
1. Backup job: This job is created on Primary Server and it performs the T-log backup of the Database.
2. Copy Job: This job is created on Secondary Server and it Copies the transaction log Backup from Primary Server to the Secondary Server.
3. Restore Job: This job is created on Secondary Server and it performs restore operation on the Secondary Server.
4. Alert Job: If Monitor server is used an Alert Job is also created on Monitor server and it raises alerts if any operation/Job is not completed successfully.
10. can we configure Log shipping between two database server which having different collation?
No, We cannot configure Log shipping in this case.
11.Can we configure Log Shipping between different domain?
Yes, We can configure Log shipping between servers which are in different domain but there must be Trust relationship between the domain.
12 If We create a user on the Primary database, will it automatically be created on the secondary?
Yes, it will be created on the secondary database automatically as its a logged operation.
13. If we take the FULL backup of Primary database, Will it affect the Log shipping?
No, Full backup will not disturb the Log shipping.
14. Will Manual T-log Backup break the Log Shipping?
Yes, Manual T-log Backup will break the LSN chain so Log shipping will not work. To resume the Log shipping , we have to manually copy and restore the T-log backup on Secondary database.
15. If we create the Login in Primary server , will it automatically transferred to Secondary server?
No, Login will not be transferred to secondary server. Log shipping works on Database Level not on Instance Level.
16.If we add a data file on the Primary database in the same location which exists on the target, will it automatically be created on the secondary?
Yes, data file will be created on Secondary server if the location is same.
17.If we add a data file in the Primary database in a location which does not exist on the target, will it automatically be created on the secondary?
No, it will not be created on secondary server if disk layout is not same.Log shipping will break in this case. we have to manually apply the T-log backup on secondary database "WITH MOVE" option to resolve the issue.
18. Can We shrink log shipped database log file WITH TRUNCATE option?
No, We should not Shrink Log file WITH TRUNCATE option because it will disturb the log shipping.We can shrink the log file but without TRUNCATE option.
19. Can we have Multiple secondary databases for a primary database in log shipping?
Yes, we can setup Multiple secondary databases for a primary database.
20. What is the best option to backup the Primary database when log shipping is running?
Database should be backed up with "COPY-ONLY"option ,it will never affect log shipping.
21. Can we take the backup of Secondary database in log shipping?
No, we cannot backup secondary database in log shipping.
22. If We create any Job on Primary server, will it be created on secondary server?
No, Job will not be created on secondary server as Job is server Level Object.
23 What is .TUF file in log shipping?
TUF file is known as "Transaction Undo File". TUF file is created when we configure Log shipping in STAND-BY mode.The transaction undo file contains Transaction that were not committed on the source database but were in progress when the transaction log was backed up.while restoring the log backup, uncommitted transactions will be saved into the undo file and only committed transactions will be written to disk making users to read the database. When you restore next log backup SQL server will get the uncommitted transactions from tuf file and check with the new log backup whether the Transaction is committed or not. If its committed the transactions will be written to disk else it will be stored in .tuf file until it gets committed or rolled-back.
24. What will happen if .TUF file is corrupted or deleted?
In such case Log Shipping will break, and we have to re-configure the log shipping.
25. What is .WRK file in Log Shipping?
When .trn files are copied from Primary server to Secondary server, an intermediate .WRK files temporarily generated and when T-log backup file copied completely at secondary server, they renamed to the .trn extension. The temporary naming using the .wrk extension ensures that the files will not picked up by the restore job until .trn file successfully copied.
26. What is STANDBY Mode in log shipping?
We can offload our primary database by using secondary server for read-only query processing and Reporting.for this purpose secondary database must be in STANDBY mode.
27. Types of mode in SQL Log shipping?
Two modes are used in log shipping:
1. No-Recovery- No user access is given to the secondary database and the database will be in Restoring state so the database does not have to care about uncommitted transactions.
2. STANDBY - secondary database can be used for Read-only purpose and .TUF file is used to contain the uncommitted transactions.
28. Switch-over the Log shipping Roles?
1. Disable the "BACKUP" Job on Primary server.
2. Go to Secondary server and Run the "COPY" Job so any remaining T-log backup copied to secondary server from Primary.
3. Run the "Restore" Job to restore those remaining T-LOG Backups.Now Secondary database is Up to date with Primary.
4. Disable "COPY" and "RESTORE" jobs.
5. Go to Primary database and Take Last T-Log Backup with NORECOVERY clause, it will left the Primary database in restoring state.
Backup Log DB_Name to disk='\\Seondary_server_UNC_Path\DB_Name_Norecovery.trn' with Norecovery
6. Go to secondary Server and restore last T-log backup with Recovery clause, it will bring the secondary database online.
Restore log DB_Name From disk='\\Seondary_server_UNC_Path\DB_Name_Norecovery.trn' with Recovery.
7. Now set-up the Log Shipping(from Secondary database that has become Primary now) and we don't need to initialize the Secondary database(Prior it was Primary) as its already in Restoring state.
8. Once the Log Shipping Set-up is completed, We can delete the Original Log Shipping disabled Jobs from Primary and secondary servers.
9. we have successfully Switched over the Role, Primary has become secondary and Secondary become Primary.
29. We configured Log Shipping in standby mode, but restore job failed with below error:"Could Not apply log backup,Exclusive access could not be obtained because the database is in use".
How to rectify the issue?
To restore T-log backup on the database, SQL Server needs exclusive access on the database.
When we configure Log shipping in standby mode, users will be able to access the database and can execute Read only queries against the secondary database.
Hence If the scheduled restore job runs at particular time interval, the database will have a lock and it won’t allow SQL Server to restore the T-logs.
To Prevent this error we need to check “Disconnect users in the database when restoring backups” options in log shipping configuration wizard.
0 comments: