Log Shipping Article-3

 Log Shipping In sql server Article-3

If the primary server becomes unavailable due to disk failure or some other reason, DBA can take the following steps to fail the database over to the standby server:

1.       Perform one last backup of the transaction log on the primary server (if possible).

2.       Copy all transaction log backups to the standby server and apply them in the same order they were taken on the primary server.

The last transaction log backup should be restored by using the WITH RECOVERY clause so that the standby database becomes operational.

3.       Transfer any logins that exist on the primary server to the standby server. Only the logins that must have access to the log-shipped database must be transferred.

This step might be further complicated if logins with the same name exist on both servers. In such cases, the DBA needs to ensure that appropriate mappings exist between SQL Server logins and database users on the standby server.

During the initial configuration of log shipping, you can allow the standby database to assume the primary role. That means you can ship transaction logs from the standby server to the primary server after you have failed the primary database over. So if primary server (server A) fails over to standby server (server B), servers can switch roles so that you can fail server B back to server A if needed.

1) What is the new feature that introduced in SQL server 2008 for log shipping?

A) In earlier editions (< SQL server 2008) you can set the backup/copy/restore job frequency to Minutes and Hours. However now we can schedule to in terms of seconds (however I damn sure that it will have lot of other problems as well) 

B) Backup compressed(log backup compression)

 List of Faqs

2)       Can we configure log shipping if the SQL server service account is running under local system account?

A)     yes we can see below per BOL-

“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”.

3)       What editions require configuring log shipping?

A)     Apart from express all other edition supports but in the versions wise is different

            For SQL server 2000

                    Server:                                       Editions

              Primary Server                 Enterprise or Developer Edition

              Secondary Server            Enterprise or Developer Edition

              Monitor Server                Any Edition

For SQL server 2005:

Requirements

Log shipping has the following requirements:

  i) SQL Server 2005 Standard Edition, SQL Server 2005 Workgroup Edition, or SQL Server 2005  Enterprise Edition must be installed on all server instances involved in log shipping.
  ii) The servers involved in log shipping should have the same case sensitivity settings.

  iii)The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.

For SQL server 2008 ->all except express..

      4)      what permission requires for shared folders on Primary and secondary for the service accounts

   
       A)     For the backup job, read/write permissions to the backup directory are required on the following:

     i)       The SQL Server service account on the primary server instance.

     ii)      The proxy account of the backup job. By default, this is the SQL Server Agent account on primary server instance.

      iii)    For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.

      iv)     For the restore job, read/write permission to the copy directory are required by the following:

      v)      The SQL Server service account on the secondary server instance.

      vi)     The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.

       5)     What happens to the log shipping in case if you have added the data file on the primary server?

A)     If both primary & secondary servers as same Disk configuration settings then you can ignore & secondary will takes up, however 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 (note that you have specified the Tuf file as well while restoring the trn by manually).

6)       What is index operation logging in log shipping?

A)   It is fully logged operation so it will replicate on secondary as well.

7)      What about If I shrink at the primary database will that log to secondary as database as well?

A)     Yes it can.

8)       Types of log shipping or types of restoring modes?

A)      With No recovery

B)       Standby/read-only.

9)       What it TUF file?

A)     TUF stands for: Transaction Undo File.
       You know that the log backup/copy/restore works according to the scheduled frequency.

TUF:

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(so here you have to think that next log backup completed from Primary side)….Also it requires to define when ever reconfiguring of the log shipping or during first configure..

10)       My Tuf file deleted unfortunate when the server was shutdown or during the server down time then what will happens to the log shipping?

A)     The log shipping will not work, Incase if you have OS level Backup then you can restore the file then you can be cool.

11)   My Log shipping is not working–what to do now?

A)     The first thing you need to look at your Log shipping job history ->Backup/copy/restore –>(think that all jobs are failing) or incase if it is specific then you need to look at the specific one for ex-

         Backup job history on Primary

         COPY and Restore history on secondary.

         Some reasons the Log shipping can break or stop working..

            12) What editions of SQL Server is log shipping available in?

1. 2012 – Enterprise, Business Intelligence, Standard, and Web
2. 2008R2 – Datacenter, Enterprise, Standard, Web, and Workgroup
3. 2008 – Enterprise, Standard, Web, and Workgroup
4. 2005 – Enterprise, Standard, and Workgroup

13)   Does the secondary need to be licensed?

       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.

14) Log shipping is compatible with backup compression. What edition of SQL Server do I need to take advantage of compression?

1. 2012 – Enterprise, Business Intelligence, or Standard
2. 2008R2 – Datacenter, Enterprise, or Standard
3. 2008 – Enterprise
4. 2005 – Not available

14. 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?

1. 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.

15. Are my logins shipped from the primary to the secondary?

         A)     No, they are not. You’ll need to set up a separate method to sync the logins.

         17)   Does this replace, or can it be combined with, our existing daily full and log backups?

1. TL; DR – no.
2. You’ll still want to take regular full and/or differential backups. Log shipping only takes one full backup – at the beginning – and that’s only if you specify that it does so. It can also be initialized from an existing full backup.
3. Taking two log backups in separate jobs will break the log chain, however. If you implement log shipping, it will replace your current transaction log backup job.

18)   "I'm using log shipping, so my log backups are automated... Why am I still seeing transaction log growth?"

Log shipping is just what it sounds like - you are shipping your transaction log backups to another server for DR purposes. The process is fairly simple - after the initialization - a job to backup the log on one server, a job to copy that log backup and a job to restore it without recovery (either NORECOVERY or STANDBY) on the destination server. There are also some jobs to monitor and alert if things don't go as you have them planned.

In some cases, you may only want to do the log shipping restore once a day or every 3rd day or once a week. That is fine. But if you make this change on all of the jobs (including the log backup and copy jobs) that means you are waiting all that time to take a log backup. That means you will have a lot of log growth - because you are in full recovery mode without log backups, and it also likely means a large log file to copy across. You should only modify the restore job's schedule and let the log backups and copies happen on a more frequent basis

Log shipping set up

Log shipping in SQL Server 2000 provides a means of establishing a warm backup solution by using the SQL Server Maintenance Plan Wizard. Transaction log backups from a database can be automatically shipped to a different server and applied to a standby database. You can use the standby database to perform read-only operations (depending on the load state).

Q1: What do I have to do before I start the log shipping set up through SQL Server Enterprise Manager?

A2: Here is the list of what you must do before you start log shipping in SQL Server 2000.

·         Either start SQL Server and SQL Server Agent services under a domain account or configure the relevant primary, secondary and monitor servers for pass-through security (see question three in this heading for more information).

·         You can set up log shipping from any computer that has SQL Server Enterprise Manager (SEM) installed. You must register all computers that are running SQL Server that function as servers, which are intended to be the secondary servers, through SEM, on the computer from which log shipping is going to be set up.

·         Create a folder on the primary server for the transaction log back ups. You can create this folder anywhere on the primary computer. There must be enough free disk space on the drive on which you place the folder to hold at least one days worth of transaction log back ups. The exact space required is not easy to predict because it depends on the size and frequency of the transaction log back ups for the database. Microsoft recommends that you create a different folder for each database that you log ship.

·         Share the folders you created in the previous step. Make sure that you grant READ and CHANGE permissions to the Microsoft Windows NT accounts under which SQL Server and SQL Server Agent services are started for the servers that participate in log shipping. If you use pass-through security, grant these permissions to the local Windows NT account, under which the SQL Server related services are started.

·         Remove or disable any transaction log back up jobs on the databases that will be log shipped. This includes any third party back up jobs.

Q2: Do I have to start SQL Server related services under a domain account as opposed to a local Windows NT account?

A3: It is possible to configure SQL Server services to start under a local Windows NT account, unless SQL Server is configured to run as a virtual server in conjunction with Microsoft Cluster Service. You can use Windows NT pass-through security for this purpose. Follow these steps to configure pass-through security:

·         Create a Windows NT account on the primary, secondary and monitor computers with the same name and passwords.

·         Configure SQL Server related services to start under these Windows NT accounts on all computers.

The SQL Server services must be started under a domain account if SQL Server is configured to run as a virtual server with Microsoft Cluster Service. Even if SQL Server is a virtual server, Microsoft recommends that you use a domain account to start the services when SQL Server computers are in a domain. You gain the following advantage by having SQL Server related services start under a domain account:

·         Change of password for the SQL Server start up account will not result in a failure of log shipping jobs. To successfully continue log shipping in a pass-through security situation, all the servers must have the password changed for the Windows NT start up account, at the same time.

Q3: Where can I set up log shipping from?

A4: In SQL Server Enterprise Manager, right-click the database for which log shipping has to be set up, and then clickMaintenance Plan. In the Welcome dialog box, click Next. Click to select the Ship the transaction log to other SQL Servers (log shipping) check box. The check box indicates to the SQL Server Maintenance Plan Wizard that this database must have log shipping. You can perform this step from a client that has SQL Server Enterprise Manager installed.

Q4: Why is the log shipping check box sometimes dimmed in the Maintenance Plan dialog box?

A5: The check box can be dimmed for one of the following reasons:

·         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 2000 Enterprise Edition is not installed on the server.

Q5: Why does the log shipping set up fail while performing the initial configuration?

A6: There are several reasons that may cause the log shipping set up to fail. At this time there is at least one known problem that causes this behavior. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

298743 BUG: All changes may not be rolled back when Log Shipping Maintenance Wizard fails

Q6: Are the table schema and database file structure changes propagated to the secondary server?

A7: In SQL Server 2000, all table schema and database file structure changes are logged operations. However, if a new NDF or LDF file is added to the primary database, the transaction log restore job fails while loading the transaction log backup that was performed immediately after the database file was added to the primary database. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

286280 Description of the effect to database recovery after you add or remove database files

Q7: Can I script log shipping?

A8: No. Currently, it is not possible to script log shipping. The only supported means of setting up log shipping is through the wizard as described in question 4 of this section.

Q8: Can I set up log shipping between servers in multiple domains?

A9: Yes. It is possible to set up log shipping between servers that are in separate 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. All other requirements are the same as explained in question 2 of this section.

Q9: Can I configure primary and secondary servers to use SQL authentication to connect to the monitor server?

A10: Yes. It is possible to use either Windows or SQL authentication for primary and secondary servers to connect to the monitor server. Microsoft recommends that you use Windows authentication for this purpose. However, if it is not possible to use Windows authentication, you can use SQL authentication. SQL Server will create a "log_shipping_monitor_probe" account on the primary, secondary and monitor servers, if it does not already exist, with the password specified when you set up log shipping. If SQL authentication is used for log shipping, you must configure SQL Server on the primary, secondary and monitor servers to use Mixed Mode authentication.

Log shipping security considerations

Q1: If I make the "guest" account unavailable before setting up log shipping, and I want my secondary database to be in a standby state, how can I allow users to have access to the secondary database (enforcing the same security model as the primary server)?

A1: The "guest" account must not be removed from SQL Server for any reason. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

315523 Removal of the guest account may cause a 916 error in SQL Server 2000 SP4 or a handled exception access violation in earlier versions of SQL Server 2000

However, you can can make the "guest" account unavailable for databases where there might be security concerns. Because the secondary database is in a standby state, it is not possible to use the sp_change_users_login stored procedure to re-map the logins appropriately. To enforce the same security model on a standby database, create the logins on the secondary server by using the same security identifier (SID) value as the primary server. Read the following Microsoft Knowledge Base article for more information about creating logins with the same SID values:

303722 How to grant access to SQL logins on a standby database when the guest user is disabled in SQL Server

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

321247 How to configure security for SQL Server log shipping.

Q2: What does sp_resolve_logins do?

A2: At the time of the log shipping role change, the sp_resolve_logins stored procedure requires a BCP file of the sysloginssystem 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. It then checks to see if the SID value for this login exists in the secondary database's sysusers system table. It finally checks to see if the SID value in secondary database's sysusers system table is not the same as the SID value in the secondary server's syslogins table. If these checks are satisfied, the sp_resolve_logins stored procedure runs the sp_Change_users_login stored procedure for that login, and fixes the SID in the secondary database's sysusers 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 (as described in Microsoft Knowledge Base article Q303722).

Q3: The sp_resolve_logins stored procedure runs successfully; however, it does not perform the expected modifications to the security on the secondary server. Why?

A3: The sp_resolve_logins stored procedure requires an up-to-date BCP file of the primary server's syslogins system table. These logins must already by created on the secondary server. If these two conditions are met, the sp_resolve_logins stored procedure performs the modifications to the sysusers system table in the secondary database.

Q4: Do I have to run a Transfer Logins DTS task in conjunction with the sp_resolve_logins stored procedure before performing the role change?

A4: Yes. You must use the Transfer Logins task to make sure that the logins exist in the syslogins system table on the secondary server. This does not guarantee that the user can use the secondary database (if the secondary database is loaded in standby mode). If the user has to use the secondary database before performing the log shipping role change, see question 1 in this section.

Q5: Does the sp_resolve_logins stored procedure work for remote logins in SQL Server?

A5: No. The sp_resolve_logins stored procedure only works for typical logins. Any remote logins must be created manually on the secondary server.

Log shipping monitoring

Q1: Log Shipping Backup and Out of Sync alerts are firing, even when the secondary server is updated with the transaction log backups. Is this possible?

A1: Yes. It is possible that the alerts might fire even when the secondary database is being updated. If the alert threshold is set to a value less than double the time between back up and copy or restore jobs, the alerts might be raised. If the alerts are being raised and the threshold is close to or less than two times the time between subsequent backup and copy or restore jobs, go ahead and increase the threshold.

Q2: Why do the transaction log backups fail to restore on the secondary server?

A2: Transaction Log backups can only be restored if they are in a sequence. This sequence is determined by the LastLSN andFirstLSN fields that are returned by the RESTORE HEADERONLY command. If the LastLSN field and the FirstLSN field do not display the same number on consecutive transaction log backups, they are not restorable in that sequence. There may be several reasons for transaction log backups to be out of sequence. Some of the most common reasons are:

·         There are redundant transaction log backup jobs on the primary server that are causing the sequence to be broken.

·         There are non-logged operations performed in the database. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

272093 Description of the effects of nonlogged and minimally logged operations on transaction log backup and the restore process in SQL Server

·         The recovery model of the database was probably toggled between transaction log backups.

·         The Data Transformation Services (DTS) task on the primary server might be causing this problem. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

308267 FIX: DTS Copy Objects Task (DMO) breaks transaction log backup chain by switching recovery mode to Simple during transfer

Q3: Where can I find information about errors while performing back up, copy or restore operations?

A3: To get more information about a particular log shipping pair, follow these steps:

1.       Open SQL Server Enterprise Manager, and then connect to the monitor server.

2.       Under Management, click Log Shipping Monitor. In the right window pane, all the log shipping pairs are displayed (that have been configured with this server as the monitor server). If the log shipping pair is not visible, right-click theLog Shipping Monitor (under Management), and then click Refresh.

3.       Right-click the log shipping pair that you want information about, and then click View Backup History to view the back up job history.

4.       Right-click the log shipping pair, and then click View Copy/Restore History to view the history for copy and restore jobs.

5.       Right-click the log shipping pair, and then click Properties to view the current log shipping status, Source, and Destination alert status.

Q4: Does the file name first_file_000000000000.trn indicate that the copy or restore job was unsuccessful?

A4: 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. For example, the very first time that copy or restore jobs are run on the secondary server, there might not be any files available to copy or restore. In this case, first_file_000000000000.trn does not necessarily represent an error. However, under certain circumstances, this might represent a problem. Read the following Microsoft Knowledge Base article for more information:

292586 Backup, copy, and load job information is not updated on the log shipping monitor

Q5: Is it possible to modify the frequency and destination of the transaction log backups, on the primary server, after log shipping has been operational for a while?

A5: Yes. This information is in the Maintenance Plan on the primary server. To view the information, follow these steps:

1.       Double-click the Maintenance Plan on the primary server for the database for which this information must be modified.

2.       Click the Transaction Log Backup tab. Modify the destination and frequency in the dialog box.

3.       Because the copy job on the secondary server is expecting to copy transaction log backups from the share specified at the time log shipping was set up, this job might fail after modifying the target folder for the transaction log back ups. For more information about how to work around this problem, read the following article in the Microsoft Knowledge Base:

314570 Cannot modify backup network share after you change the transaction log backup folder

Log shipping role change

Q1: How do I perform a log shipping role change?

A1: Click the following link to read the SQL Server 2000 Books Online topic about performing a log shipping role change:

How to set up and perform a log shipping role change (Transact-SQL)

Q2: Can I perform a role change while the primary server is offline or unavailable?

A2: Yes. Running the sp_change_primary_role stored procedure on the primary server is optional.

Q3: Why does the sp_resolve_logins stored procedure fail with error message 208 when run from the secondary database at the time of a role change?

A3: The sp_resolve_logins stored procedure does not qualify the sysusers system table with the master database prefix. This is a known problem with the code for the sp_resolve_logins stored procedure. For more information about this problem, read the following article in the Microsoft Knowledge Base:

310882 BUG: sp_resolve_logins stored procedure fails if executed during log shipping role change

Q4: Is there a problem when promoting a secondary server to be a primary server, when there are multiple secondary servers involved in a role change?

A4: Read the following Microsoft Knowledge Base article about a known problem that can cause errors while performing a role change that involves multiple secondary servers:

300497 FIX: Log shipping: Cannot change role from secondary to primary when database names are different

Q5: How can I re-establish log shipping after promoting the secondary server to be the primary server?

A5: If the Allow database to assume Primary role check box is selected, while setting up log shipping, in the Add Destination Database dialog box, follow these steps to add a new secondary server after performing a role change. If the setting was not selected, use the Maintenance Plan Wizard to set up log shipping after a role change.

1.       Open SQL Server Enterprise Manager, and then connect to the promoted primary server. Register the server that you intend to add as the secondary server.

2.       Expand Management (in SQL Server Enterprise Manager), and then click Maintenance Plans. Right-click the appropriate Maintenance Plan from the list, and then click Properties.

3.       Click the Log Shipping tab, and then click Add.

4.       Provide the appropriate information regarding the secondary server about this dialog box, and then click OK. This will add the new secondary server to log shipping.

Q6: How can I continue to log ship to the former primary server without restoring a database backup?

A6: It is possible to log ship between two servers repeatedly without having to restore the complete database backup. The requirement is that both the primary and secondary servers are available when you perform the role change procedure. As part of performing the role change, you must run the sp_change_primary_role stored procedure. You must run thesp_change_primary_role stored procedure with a @final_state parameter of either 2 or 3. This will leave the primary database in an unrecovered state after performing the transaction log back up. Because the database is left in an unrecovered state, this database can be selected when the log shipping destination is added (as explained in the previous question). This way you do not have to reload a database backup.

Log shipping removal

Q1: How can I stop log shipping for a particular log shipping pair?

A1: Follow these steps to remove a log shipping pair:

1.       Open the SQL Server Enterprise Manager on the primary server. Expand Management, and then click Maintenance Plan. Right-click the Maintenance Plan, and then click Properties.

2.       Click the Log Shipping tab, and then click to select the log shipping pair that you want to remove.

3.       Click the Delete command button to remove this pair from log shipping. If this is the last pair in log shipping, clicking Delete removes log shipping. If you want to continue log shipping to a different server or to a database, click Add. Then, click to select the appropriate server or database to act as the secondary server before you remove the existing log shipping secondary.

Q2: Is there a problem with removing log shipping for a database that has special characters in it's name?

A2: Read the following Microsoft Knowledge Base article, which discusses this problem in more detail:

295936 FIX: Error removing log shipping on secondary database when database name has a quote

INSOME OF THE CASES LOG SHIPPING MAY BREAK

1. Recent modifications (permissions removed) on the shared folders (Might be on Primary shared folder or on at the secondary side).

2. Human error ->either someone used the option of truncate only or switched the recovery model.

3. date/time for the windows servers unmatching due to any DST activities.

4. As usual ->Data file added on Primary on different drives ->then you need to apply that on secondary with move until that your log shipping restore job will fail

5. Any I/O, Memory, N/w bottleneck–Please look at the error logs & event logs

6. Corruption.

7. Your tuf file is missing.

8. sqllogship.exe –  someone deleted..

9. Drives full

10. Improper SP/hot fix applied.

11. You may have set the incorrect value for the Out of Sync Alert threshold.

12. Might be you have scheduled the jobs in same time

13. Your MSDB database is full.