This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Does the secondary instance need to be licensed?
I am not the licensing police, and I am not Microsoft – check with your licensing representative to clarify your exact situation. Generally, you can have one warm standby server. However, the second someone starts using it for reporting, testing, or anything else, you need to license it like any other server.
When log shipping is set up, Agent jobs are created to alert me if a backup, copy, or restore fails. How do I get notified?
You need to go into the Agent job, pull up Notifications, and choose your method – email an operator, or write to the event log, for example.
Are my logins shipped from the primary to the secondary?
No, they are not. You’ll need to set up a separate method to sync the logins.
What is the difference between the secondary being in Restoring vs. Standby?
Restoring means the database is not accessible. Standby means it is read-only. You make this decision when you set up the log shipping.
If the database is in Standby mode, users can query it – except when a log backup is being restored. You need to decide if a restore job will disconnect users, or if the restore is delayed until after the users are disconnected.
Can we configure log shipping if the SQL server service account is running under local system account?
Yes: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.
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 data file) with MOVE option.
Does index operation logging in log shipping?
Yes: It is fully logged operation so it will replicate on secondary as well.
May I shrink the log files of Primary Database?
Yes, you can shrink the log files but make sure that before it copied into Secondary Database.
What do you know about the TUF file?
TUF stands for ->T ->Transaction U->Undo F->File so it is Transaction Undo file.
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.
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.
Can we configure Log Shipping between the different domains?
Yes, we can configure Log Shipping on the server residing in different domains.
After some disk issues at the production server the logshipping continually failed. So I tried a full back up the primary database and restored the secondary database with this full backup and started the log shipping again. And the logshipping backup of the primary database failed with the error message below:
*** Error: BACKUP detected corruption in the database log. Check the errorlog for more information.
BACKUP LOG is terminating abnormally.
- Stop all user activity in the primary database.
- Switch to the SIMPLE recovery model (breaking the log backup chain and removing the requirement that the damaged portion of log must be backed up).
- Switch to the FULL recovery model.
- Take a full database backup (thus starting a new log backup chain).
- Start the logshipping job.
Does the file name first_file_000000000000.trn indicate that the copy or restore job was unsuccessful?
Each run of the copy and restore job is associated with at least one file. By default, if no files are copied or restored in a certain run of any of these two jobs, SQL Server places first_file_000000000000.trn in the file name field. This may or may not indicate a problem.
Does the sp_resolve_logins stored procedure work for remote logins in SQL Server?
No: The sp_resolve_logins stored procedure only works for typical logins. Any remote logins must be created manually on the secondary server.
Why is the log shipping check box sometimes dimmed in the Maintenance Plan dialog box?
- Multiple databases might be selected for the Maintenance Plan.
- The database that is selected is not in the Full or Bulk Logged Recovery model.
- SQL Server Enterprise Edition is not installed on the server.
Can I configure primary and secondary servers to use SQL authentication to connect to the monitor server?
Yes. It is possible to use either Windows or SQL authentication for primary and secondary servers to connect to the monitor server.
Why backup job failed on Primary server and thrown below error messages?
*** Error: Backup failed for Server ‘<Server Name>’. (Microsoft.SqlServer.SmoExtended) ***
*** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
*** Error: BACKUP LOG cannot be performed because there is no current database backup.
This message is thrown because the database is never backed up fully before. Do a full backup of the primary database to solve this issue.
What does sp_resolve_logins do?
At the time of the log shipping role change, the sp_resolve_logins stored procedure requires a BCP file of the syslogins system table from the primary server. This stored procedure loads the BCP file into the temporary table and loops through each login to verify if a login with the same name exists in the secondary server’s syslogins system table.
Execution of this stored procedure is required only if there are new logins created on the primary server after log shipping has been initialized and those same logins are not created on the secondary servers with the same SID.
