SQL Server Upgradation Log Shipping Tutorial | SQL Server DBA Tutorial – Part 8

by

SQL Server Upgradation Log Shipping Tutorial from Coding compiler | SQL Server DBA Tutorial – Part 8, this blog is the eighth tutorial in the series, here you are going to learn about how SQL server upgradation works. Before going to learn this tutorial, we recommend you to go through the previous SQL Server Restoration DBA Tutorial – Part 7.

Topics Covered in this SQL Server DBA Tutorial

  • What is SQL Server Upgradation?
  • SQL Server Upgrade Advisor Tool
  • Steps Involved in Installing Upgrade Advisor
  • Types of Upgradation
  • Inplace Upgrade
  • Side by Side Upgrade
  • Post upgrade checks
  • What is Log Shipping
  • Log Shipping Architecture
  • Components of Log Shipping
  • Configuring Log Shipping
  • TUF File in Log Shipping

What is SQL Server UPGRADATION?

Upgrading means, migrating sql server old version to sql server 2012.

Before upgradation we need to run upgrade advisor tool.

Microsoft sql server 2012 upgrade advisor

Under installation menu  → in planning → click upgrade advisor.

SQL server 2012 upgrade advisor analyzes sql server 2008 R2 (or) other lower version sql server instances and gives results that may impact upgrade process.

Steps in installing upgrade advisor.

  1. Open installation  → go to planning → select upgrade advisor → wizard will be open  → click next
  2. Enter server name and select the components to analyze
  3. Select instance name and provide credentials
  4. Select the database. If you have SSIS packages provide path of files
  5. Select run of files
  6. Select run to begin analysis
  7. Once analysis complete, warnings will be displayed

What is SQL Server UPGRADATION?

Upgrading means, migrating sql server old version to sql server 2012.

Before upgradation we need to run upgrade advisor tool.

Microsoft sql server 2012 upgrade advisor

Under installation menu  → in planning → click upgrade advisor.

SQL server 2012 upgrade advisor analyzes sql server 2008 R2 (or) other lower version sql server instances and gives results that may impact upgrade process.

Steps in installing upgrade advisor.

  1. Open installation  → go to planning → select upgrade advisor → wizard will be open  → click next
  2. Enter server name and select the components to analyze
  3. Select instance name and provide credentials
  4. Select the database. If you have SSIS packages provide path of files
  5. Select run of files
  6. Select run to begin analysis
  7. Once analysis complete, warnings will be displayed

There are 2 types of upgradation

  1. Inplace upgrade
  2. Side by side upgrade

Inplace Upgrade:

Source server will become destination server after upgrading. It is going to install sql server 2012 components on old version. All the lower version support files will be converted to sql server 2012 supportable. This type of upgradation in Inplace upgrade.

Advantages:

  1. Fast, easy and automated process
  2. No additional hardware required
  3. Instance name will be same as earlier.

Disadvantages:

  1. Sql server will be in offline state during upgrade hence we have downtime.
  2. Roll back process is very complex
  3. Source server may be affected

Inplace upgrade process from Installation →from installation  → select upgrade from sql server 2008 R2 and continue remaining installation process.

Side-by-Side

Side by side upgrade consists of installing sql server 2012 and moving old server databases to new server just like migration process.

Advantages:

  1. Upgrade process is online without affecting business
  2. Minimal down time required in redirecting the user requests from sql 2008 R2 to sql server 2012.
  3. Selected databases can migrate
  4. Full control on upgradation
  5. Roll back will be easy as original server still available

Disadvantages:

1.manual process

Side -by-side process

2. Follow the same process which performed in migration like script out logins, jobs and take backup and keep.

3. Install sql server 2012

4. Copy the database backups and restore

5. Create logins and jobs by running scripts

6. Find and fix orphaned users.

7. Verify database connectivity and functionality.

Post upgrade checks

  1. Change compatibility level
  2. Check integrity of objects
  3. Set page verifications using CHECKSUM
  4. Update statistics

Change the compatibility level of database once upgradation complete successful using command alter database sql test 2012 set compatibility level=110

Check integrity:

Check integrity of database using command DBCC CHECKDB(database name)

Page verification:

CHECKSUM verifies computation of page and results are stores in page header. Using command alter db dbname set PAGE_VERIFY CHECK SUM with NO_WAIT.

Updating statistics:

The statistics need to update after upgrade to get full features of enhancement in sql 2012. Sp_ms foreach table ‘UPDATE STATISTICS’ ? with FULLSCAN

What is LOG SHIPPING in SQL Server DBA?

Log shipping:

Shipping of transaction log files from primary server database to second server database to get both database in continuous synchronization. It is a continuous process in the form of batches.

Log shipping requires 3 servers

1. Primary: Primary server contains source database for sending the transaction logs.

2. Secondary: Secondary  server contains destination database receiving the transaction logs.

3. Monitor: Monitor server tracks information related to log shipping jobs and sends alerts in case of failure.

Log shipping mainly depend on 3 jobs:

1. Backup job: For Backup transaction logs on primary server database. Always run on primary server.

2. Copy job:  Copy job runs from secondary server, copies transaction log backup from primary server to secondary server.

3. Restore job: Restore run on secondary server to restore backups.

Log shipping Architecture

Log shipping steps:

1. Before log shipping configuration, take full backup of primary database and restore it on secondary server. This is initial synchronization.

2. Create backup job on primary server to backup the transaction logs of primary database.

3. Create copy job on secondary server to copy the transaction logs.

4. Create restore job on secondary server to restore the transaction logs.

5. Create an alert jobs on monitor server to indicate a failure if the database are out of synchronization beyond the configuration threshold.

Failover steps:

Planned failover:

Situations

1. Upgrade from 2008 R2 to sql server 2012

2. Hardware failure

In these situations keep primary server in offline and make secondary server database online.

  1. Disable all log shipping shops on both servers.
  2. Make sure copy all backups copied to secondary server.
  3. Restore all T-log backup in the same order.
  4. Change connection to secondary server and start jobs unplanned/disaster.

Need to identify  crash time, when last backup, copy and restoration happened on server using ‘db0.log shipping-monitor’ tables of MSDB databases.

  1. Disable all log shipping jobs
  2. Perform tail log backup if log files available
  3. Manually copy last backups from network share to secondary server.
  4. Restore all T-log backups in same sequence.
  5. Now secondary database will come online
  6. Sync all logins, jobs then find orphaned user and fix them.

Advantages:

  1. Only log shipping allows to keep secondary database in standby mode.
  2. We can maintain multiple secondary databases.
  3. No additional hardware (or) software required.

Disadvantages:

  1. More down possible, we cannot bring secondary server immediately.
  2. Data loss possible, as jobs run every 15 minutes.
  3. We have to perform manual failover.
  4. Need to perform several steps to bring secondary server online which we call warm standby.

Components of log shipping

For configuring log shipping we require 3 servers and 3 jobs and we need backup destination, there are 2 types

  1. Local share
  2. Remote (or) Network share

In local share, backup copies into primary server share whenever primary goes down data loss is more.

In remote share,  backup copies to network share copy job of secondary server goes to this share and copies backup to local share of secondary server. We have to grant proper permissions to sql agent services for this network share folder on both  servers.

Pre-requisites:

  1. Make sure recovery model should be full.
  2. Maintain same hardware between both server to avoid performance issue.
  3. Service account running on both servers must have access to network share where log backups store.
  4. Keep secondary server always in No recovery.
  5. Make sure number of drives and name of drives should be same on both server.

Configuring log shipping

1. Select database → Go to properties →Select options

From recovery model drop down choose full recovery model

By using T-sql

Use master

Alter database databasename set RECOVERY FULL;

2. From properties → select transaction log shipping

  • Enable this as a primary database in log shipping

     Click Backup setting button

3. Transaction log backup settings

Provide network path to backup folder and local path if folder is local to primary server.

4. Using schedule option we can change time interval by default it will be 15 minutes (Latency)

5. Delete files older than (Retention period)  – 72 hours

   Alert if no backup occurs with in                – 1 hours

6. Keep compression as default.

Based on retention period old backup files will be deleted. Whenever there is no backup for particular period we need to get alert.

Backup setting completed, secondary server setting enabled.

Secondary database settings

Select secondary server name:

Initialize secondary database

We have to create database on secondary server, if not present.

Select one option from the 3 options.

  • Yes, generate a full backup of primary and restore

create secondary database if not available by using above option

  • Use existing backup of primary into secondary

In case backup already exist we use this option (It is not recommended, LSN mismatch may happen).

  • Secondary database is already initialized.

Restore options

In this we specify location of MDF and LDF files.

Copy files

Destination folder for copied files: \\win2KS13\secondary

Delete copied after (retention period) : 24 hours

Provide name to copy job

Schedule setting → specify setting

Restore Transaction Log

No recovery mode will not allow user to access secondary db standby mode, allows users to run select command till next restoration.

Disconnect users when restoring, kills all user connections.

No recovery → To keep database in restoring mode

Stand by → we can execute select commands

Disconnect users → It skills user who connect to server while restoring

Delay restoring → If we want , wantedly delaying restoring.

Log shipping configuration completed.

Failover steps in log shipping:

  1. Disable all log shipping jobs on primary and secondary server.
  2. Check whether we can take a tail log backup (or) not.

If log files available then only we can perform tail log backup.

  1. Manually copy last backup and tail log backup from Network share to secondary server.
  2. Restore all available log backups with no recovery and last log backup with recovery.
  3. Now secondary database will become online.
  4. Sync all logins, jobs
  5. Finds orphaned users and fix it.

Advantages and Disadvantages

Advantages:

  1. Whenever we have requirement, we can keep secondary database in standby mode.
  2. We can get multiple secondaries.

Disadvantages:

  1. More downtime and data loss possible
  2. Manual failover.
  3. In log shipping database as warm standby, we have to perform more steps to bring server online.

TUF file in Log shipping

TUF file stands for Transaction undo file (TUF)

TUF file contains the modifications that were not committed on primary database. When transaction log backup was in progress and when log was restoring on secondary database. When next transaction restoring in secondary server, sql server uses that TUF file and starts restoring incomplete transactions.

Monitoring Logstripping:

  1. Right click on sql server name in SSMS  → go to reports → select standard report  → Transaction log shipping status.
  2. We can query MSDB tables select * from msdb. Log_shipping_monitor_error_detail
  3. We can query system job history

Select * from msdb.. Sysjobhistory where message

Like ‘% operating system error%’

Related SQL Server DBA Tutorials And Interview Questions

SQL Server Tutorial

SQL Server DBA Tutorial – Part 2

SQL Server DBA Tutorial – Part 3

SQL Server DBA Tutorial – Part 4

SQL Server DBA Tutorial- Part 5

SQL Server DBA Tutorial- Part 6

SQL Server Interview Questions


Leave a Comment