Helpful Basics of SQL Server Logshipping

SQLServerF1

SQL Server Log logshipping permits you to consequently send exchange log reinforcements from an essential database on an essential server case to one or more optional databases on discrete auxiliary server examples. The exchange log reinforcements are connected to each of the auxiliary databases independently. A discretionary third server occasion, known as the screen server, records the history and status of reinforcement and restore operations and, alternatively, raises alarms if these operations neglect to happen as booked.

SQL Server Log transportation permits you to consequently send exchange log reinforcements from an essential database on an essential server case to one or more optional databases on discrete auxiliary server examples. The exchange log reinforcements are connected to each of the auxiliary databases independently. A discretionary third server occasion, known as the screen server, records the history and status of reinforcement and restore operations and, alternatively, raises alarms if these operations neglect to happen as booked.

Log transportation comprises of three operations:
Go down the exchange log at the essential server occurrence.
Duplicate the exchange log document to the optional server occurrence.
Restore the log reinforcement on the optional server occurrence.

The log can be dispatched to various optional server cases. In such cases, operations 2 and 3 are copied for every optional server occurrence. A log shipping arrangement does not naturally fall flat over from the essential server to the auxiliary server. In the event that the essential database gets to be inaccessible, any of the auxiliary databases can be brought online physically. You can utilize an optional database for reporting purposes.

Primary server – The case of SQL Server that is your creation server.
essential database – The database on the essential server that you need to move down to another server. All organization of the log shipping setup through SQL Server Management Studio is performed from the essential database.
auxiliary server – The case of SQL Server where you need to keep a warm standby duplicate of your essential database.
auxiliary database – The warm standby duplicate of the essential database. The auxiliary database may be in either the RECOVERING state or the STANDBY state, which leaves the database accessible for constrained read-just get to.
screen server A discretionary example of SQL Server that tracks the majority of the points of interest of log delivery. Furthermore, you can design alarms for your log shipping arrangement.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Migration of SQL Server Instance from One Server to Another Server with Minimum Downtime

As newer versions of SQL Server are released, we plan implement to upgrade to higher versions and during this process, we may also upgrade to new and powerful servers with better hardware and latest Operating System. Also, as part of data center relocation, we migrate the SQL Server instance from one server to another server in new data center. The process of migration involves lot of steps and need to plan and implement the steps so that, at end of migration, we have SQL Server instance running without any problems.

This article covers a scenario where we want to migrate SQL Server instance from one server to another server. This is applicable for scenarios where we are migrating between servers which have same versions of SQL Server installed or new servers having higher version of SQL Server. Goal of the migration is that there is minimal downtime when we switch the applications from old server to new server.

Below are the high level steps involved in migration.

– First, we need to perform these steps on test server, so that we can find the issues that can arise during the migration before hand and also to test if the applications work without any problems after migration.

– If we are migrating to a higher version of SQL Server, then need to document the issues that can arise with the migration by running Upgrade Adviser and prepare documentation with remediation steps to fix those issues as part of the migration. If we are migrating to new server with same version of SQL Server, then no requirement of upgrade adviser.

– Install the required version of SQL Server instance on the new server.
– Apply latest Service Pack or Cumulative Updates.
– Script logins from existing server and Create those logins on the new server.
– Script all the jobs on existing server and create them on the new server, keep them disabled. Create maintenance plans on new server similar to existing server.
– Configure Database Mail if used.
– Create any linked servers if required.
– Configure SQL Server settings like Max Server Memory, MaxDoP, backup compression settings, etc on the new server.
– Configure OS or Network settings on new server like Lock Pages In Memory, Perform Volume Maintenance Tasks, etc.
– Backup all User databases on existing server, copy the backups to new server and restore them. Document the time it took for performing backups, copying backups to new server and for restoring of databases.
– Fix Orphan Users for all user databases.
– Enable backup and maintenance jobs like rebuild index, Update stats, etc on new server.
– Change the compatibility level of user databases to latest version(If migrating to higher version).
– Perform Application testing to make sure the application is working without any problems and performance is acceptable.
– Once, everything looks fine and got permission to move ahead, plan for the Go-Live of new server.

Go-Live Planning
– Our goal here is to have minimum downtime during the Go-Live process, so we need to either setup Logshipping or Database Mirroring between existing and new servers.

– Configure Logshipping from existing server to new server for all user databases and make sure backup, copy and restore jobs are running without any issue.
– Before Go-Live, make sure Logins and jobs are same on both the existing and new servers.
Start of Down Time
– Disable all jobs on existing server.
– Run logshipping backup jobs on existing server, once it completes successfully, disabled the job.
– Run logshipping copy jobs on new server, once it completes successfully, disabled the job.
– Run logshipping restore jobs on new server, once it completes successfully, disabled the job.
– Remove the logshipping configuration between existing and new server.
– Bring the all user databases online on new server by running “restore database databasename with recovery”.
– Enable all required SQL Server Agent jobs on new server and disable the same on old server
– Fix the orphan users.
– Change the compatibility level of all user databases to latest version(If migrating to higher version).
– Point the application to use the new server.
End of Down Time
– Test the Application to make sure it is working as expected.
– Later run rebuild indexes and Update Statistics on all user databases.
– If there are any issues, try possibilities of fixing those issue, if it is taking time and decided to rollback, refer below RollBack plan.
– If no rollabck required, take SQL Services offline on old server and decommission the old server.

RollBack Plan
– Verify all the user Databases are online on old server.
– Point the application back to old server.
– Enable all required jobs on old server and disable the same on new server.
– Test the application.

You can also use database mirroring instead of logshipping, just replace the steps related to logshipping with database mirroring related steps.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Configuring SQL Server 2012 Logshipping on AlwaysON Availability Group Databases with Successful Failover of AG and Logshipping

Configuring SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG) is supported in SQL Server 2012 or higher. We can failover AlwaysON Availability Groups between Primary and Secondary Server and also move or enable the Logshipping from new Primary Server to DR server. Below is the environment setup.

Three servers Server01, Server02, Server03 with Windows Server 2008 R2 as Operating System. Each server has one standalone default instance of SQL Server 2012. AlwaysON Availability Group (AG) and Listener “Prod_LSTNR” has been setup between Server01 and Server02.

Now we can setup Logshipping from Server01 to Server03 just the same way how we setup logshipping between any two server databases as mentioned below.

How to Setup Logshipping between two servers?
– Connect to SQL Server instance from SSMS using instance name as Server01.
– On Server01, right click the database you want to use as your primary database in the log shipping configuration, and go to its Properties and go to last tab “Transaction Log Shipping”.
– Select the “Enable this as a primary database in a log shipping configuration” check box, which will now enable the logshipping configuration options to choose backup, copy and restore settings.
– Under “Transaction log backups”, click on Backup Settings.
– In the Network path to the backup folder box, type the network path to the backup share to use it for placing the transaction log backups.
– If this backup share is located on the primary server itself then type the local path to the backup folder in the “If the backup folder is located on the primary server” text box. If the backup folder is not on the primary server, you can leave this box empty.
– Make sure that the SQL Server service account on the primary server has full permissions on the backup share.
– Configure the Delete files older than and Alert, if no backup occurs within parameters.
– You can choose to customize the schedule for log backup and alerts.
– Starting SQL Server 2008, backup compression is supported, so you may choose to enabled it or set it to use Instance wide backup compression configuration.
– Now, under Secondary server instances and databases, click Add.
– Click on Connect and then connect to the SQL Server instance Server03 that we want to use as the secondary server for logshipping.
– In the Secondary Database box, either choose a database from the list or just type the name of the database you want to create.
– On the Initialize Secondary database tab, choose the option that you want to use to initialize the secondary database.
– Make sure that you choose correct option above and appropriately prepare the database on secondary server.
– Now go to Copy Files tab, and in the “Destination folder for copied files” text box, put the path of the folder to which the transaction logs backups should get copied. This folder is often located on the secondary server or common share. Make sure that SQL Server service account on secondary has full permissions to this path.
– You may optionally choose to change the copy and alert thresholds as per your SLA’s
– Now move to Restore tab, under “Database state when restoring backups”, choose the No recovery mode or Standby mode option. If you are choosing Standby mode option, then also select that you want to disconnect users from the secondary database while the restore operation is performed.
– If you want to delay the restore process on the secondary server, choose a delay time under Delay restoring backups at least.
– You may optionally alter the restore and alert thresholds.
– If you want, you may also add a third server as Monitor server instance to monitor this log shipping configuration. You need to decide whether you want to use monitor server or not, because, if you want to add the monitor server later, we need to remove logshipping entirely and reconfigure it with monitor server.
– If you choose to configure monitor server as well, then click on Connect and connect to the SQL Server instance to use as the logshipping monitor server.
– Under Monitor connections, choose the connection method to be used by the backup, copy, and restore jobs to connect to the monitor server. Under History retention, choose the length of time you want to retain a history of log shipping.
– On the Database Properties dialog box, logshipping tab, click OK to begin the configuration process.
– Verify and make sure that logshipping log backup job is running successfully on Primary server Server01 and is creating the backup file on the backup share we provided to place the log backup files.
– Verify and make sure that logshipping copy job is running successfully on Secondary Server Server03 and is copying the backup file from the backup share to path specified during copy settings for secondary server.
– Verify and make sure that logshipping restore job is running successfully on Secondary Server Server03 and is restoring the backup files.
– Check Logshipping report from SSMS on Server01 and Server03, by right clicking on SQL Server Instance -> Reports -> Standard Reports -> Transaction Log Shipping Status.

Now we have successfully setup logshipping from Server01 to Server03, Next thing is to failover AlwaysON Availability Group “PROD_LSTNR” from Server01 to Server02, after which we also want to move the logshipping to happen from Server02 to Server03 and disable logshipping from Server01 to Server03.

Disable the logshipping from Server01 to Server03 by following below steps
Connect to SQL Server instance using SSMS with instance name as Server01 and Server03
Disable logshipping log backup job on Server01.
Disable logshipping copy job on Server03 related to Server01 as primary.
Disable logshipping restore job on Server03 related to Server01 as primary.

Perform AlwaysON Availability Group “PROD_LSTNR” failover from Server01 to Server02. Verify to make sure databases are synchronized and no errors related to AlwaysON.

Now configure logshipping from Server02 to Server03
– Connect to SSMS using SQL Server instance name as Server02.
– Configure logshipping from Server02 to Server03 using steps mentioned above under section “How to Setup Logshipping between two servers?”.
– Make sure logshipping is working fine by checking new backup job on Server02 and new copy and restore jobs on Server03 which are related to Server02 as primary.

From now onwards, you can failover AlwaysON Availability Group from Server01 to Server02 or vice-versa and at same time switch the logshipping to be performed from Primary AlwaysON AG database to Server03, by disabling the logshipping jobs related to AlwaysON AG secondary server.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings, SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG)

 

SQL Server 2012 Logshipping Backup Job Completes Successfully but No Backup File Created

I encountered a strange issue while working on SQL Server 2012 AlwayaON Availability Group databases which also has Logshipping configured. After failover of AlwaysON Availability Group databases from primary to secondary, and also after moving the logshipping from new primary server to DR server, logshipping backup job was having issues where the job was completing successfully, but was not creating the log backup files. Below is the environment setup.

Three servers Server01, Server02, Server03 with Windows Server 2008 R2 as Operating System. Each server has one standalone default instance of SQL Server 2012. AlwaysON Availability Group (AG) and Listener “Prod_LSTNR” has been setup between Server01 and Server02. Logshipping has been setup from Server01 to Server03(This is just same way how we setup logshipping between any two server databases). AlwaysON and Logshipping has been working without any problems after the initial setup.

Now, when there was a planned maintenance to replace Server01 and Server02 with new hardware, we can failover AlwaysON AG to secondary server and also configure or move logshipping from new primary server to DR server. We decided to do this with minimum downtime to production users by moving all databases to one of the server and to perform the maintenance on the AlwaysoN AG secondary server.

As part of planned maintenance, we failed over everything to Server01 and performed maintenance on Server02 and added it back to the AlwaysON AG. Verified and AlwaysON AG databases got synchronized in some time and logshipping also was working fine without any problems.

Now we have to perform maintenance on Server01, so we moved the AlwaysON AG “Prod_LSTNR” to Server02, which was successful and databases are synchronized as well. As expected, there is no logshipping configuration on databases now as logshipping was only setup when databases were primary on Server01 to Server03. We decided to configure logshipping from Server02 to Server03 now, so that logshipping to Server03 happens when the AlwaysON AG databases are either on Server01 or Server02.

Disabled Logshipping Backup job on Server01, disabled logshipping copy and restore jobs on Server03, which disabled logshipping from Server01 to Server03. Took server01 down for maintenance and is no more available now. Configured logshipping from Server02 to Server03 which created new backup job on Server02 and new copy and restore jobs on Server03. All logshipping jobs started running without any errors.

Later, maintenance of Server01 finished and was online and got connected to AlwaysON AG “Prod_LSTNR” and successfully synchronized as secondary.

After some time, we started receiving alerts for logshipping that the copy and restore latency was above the thresholds. We checked and found backup, copy and restore jobs from Server02 to Server03 are all running successfully. Logshipping report shows that there was no backup, copy or restore performed since Server01 was back online. Checked the history of backup job and noticed that backup was not performing any backups, it was just skipping performing the log backup on Server02.

Started verifying the logshipping and AlwaysON settings and noticed that AlwaysON AG backup preferences was set to run backups on any replica with priority of 60 for Server01 and priority of 40 for Server02. As now, we have both Server01 and Server02 online and Server02 acting as AlwaysON Primary, log backups cannot be performed on Server02 due to low priority and that Server01 is online. Changed the AlwaysON AG backup preferences to run backups on Primary Server, after which backup job started creating the backup files successfully. Later copy and restore jobs also starting working without any issues or latency.

You can find AlwaysON backup preferences from SQL Server management Studion (SSMS) -> Expand Always On High Availability -> Availability Groups -> Open properties of Availability Group where the databases in part of -> Go to Backup preferences tab, where we can change the settings.

If we see the issue only from logshipping perspective, we could not have identified the issue, looking into the issue as AlwaysON along with logshipping helped us in identifying the issue.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Unable to Open Logshipping BackJob Properties for Database with AlwaysON AG Created

Recently while working on a production deployment, faced an issue with SQL Server LogShipping. As, it was planned maintenance, we had to find and fix the issue immediately. Writing this blog, so that others can get an idea on what can be done when faced with this type of issue.

Coming to the issue, we had three SQL Server 2012 servers Server01, Server02 and Server03. AlwaysON Availability Group had been setup between Server01 and Server02. Logshipping is setup between Server 01 and Server 03.

We had a planned maintenance where the two servers Server 01 & Server02, hardware was planned to be replaced, so we planned in a way that we will take down one server at a time and perform the maintenance, while the other server will be serving production traffic.

So, we failed-over the AlwaysoN AG with listener name as “Prod_RPT_LSTNR” from Server01 to Server02 which was successful. We disabled Logshipping backup job on Server01, disabled copy and restore jobs. on Server03. Configured logshipping from Server02 to Server03, which completed successfully, new backup, copy and restore jobs started working fine. Now Server01 was taken down for maintenance.

Later, logshipping started failing with errors related to backup path being incorrect. We checked and found that drive where we pointed backup path was removed and we were told that the drive cannot be added back, so we had to change the backup path in logshipping.

We tried to change the path and tried to click on the Logshipping backup job button in logshipping configuration tab, but it was returning below error


TITLE: Microsoft SQL Server Management Studio
------------------------------
SQL Server Management Studio could not save the configuration of 'Server01' as a Primary.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An entry for primary server Server01, primary database ReportServerTempDB does not exist on this secondary. Register the primary first. (Microsoft SQL Server, Error: 32023)
--------------------------------------------------------

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

– After some troubleshooting and research, we found that, when we configured logshipping from Server02 to Server03, we connected to the SQL instance Server02 from SSMS via AlwaysON AG listener name which was Prod_RPT_LSTNR, but later when we tried to change the backup settings we connected to SQL instance Server02 SSMS using server name which was Server02. Now, we connected back again to SQL instance Server02 via SSMS using Listener name, then it allowed us to click on logshipping backup job button and we were able to open the job properties with out any errors.

Running below command on Secondary server, will provide us with the details of logshipping primary database like primary_server, primary_database, backup_source_directory, backup_destination_directory, file_retention_period, etc.

sp_help_log_shipping_secondary_primary
@primary_server = 'SQLInstanceName',
@primary_database = 'DatabaseName'

Running below command on Primary server will provide details of primary database settings

sp_help_log_shipping_primary_database
@database = 'DatabaseName'

Now, important thing to understand here is that, when we configure logshipping in environments where we have multiple ways of connecting a SQL instance, example via AlwaysON Listener name, IP Address, Alias, etc, we need to connect using the same method later as well, if we want to make any changes to the logshipping configuration. This happens because, the SQL Server instance name which we used to connect will be stored in MSDB logshipping metadata and will look for same server name later, so if later we try to connect using another method by specifying different instance name like IP Address/listener/alias, it will mis-match with the metadata and will return errors.

Hope this was helpful.

Thanks,
SQLServerF1 Team