Log Shipping in SQL Server-Part 1

Introduction

Log Shipping in SQL is one of the oldest Disaster Recovery and most reliable solutions from Microsoft. It was first introduced in SQL 2000 and is very popular even now and extremely easy to configure. It’s the least expensive also called a poor man’s DR solution. In a nutshell Log shipping involves moving data by capturing changes from the Primary server to one or more Secondary servers. From use case perspective it might be used to offload data from the OLTP database to secondary servers which could be used for Reporting, data warehousing etc., which are predominantly read data oriented operations. Log shipping involves one or more stand-by servers that is not used for regular operations. In an event of a disaster one of these secondary servers are made Primary and then can be used for business continuity. Log shipping can be used only at database level and not at Instance level. Log shipping does not fit all problems and it is up to the Architects to decide to use it or look at other DR strategies. I’ll explain Log shipping in two parts given the long nature of this article.

High-level approach

SQL Server log shipping allows you to automatically send transaction log backups from the primary database to one or more secondary databases which are hosted on separate secondary servers. There are three distinct steps (SQL server agent jobs) that are involved in Log shipping.

  • Backup the transaction log file on the Primary database server. These files could be stored on a Backup share or on a SAN mount point. A SQL job is created under category “Log Shipping”. This job can be configured at any frequency that is suitable to your scenario. This job invokes sqllogship.exe with arguments –backup and –server. Refer to this site for more details on sqllogship command https://docs.microsoft.com/en-us/sql/tools/sqllogship-application.
  • Copy these transaction log backup files from the backup share/SAN mount point to the destination Secondary SQL Server instance
  • Restore these transaction log backup files on the secondary SQL server instance

A fourth type of SQL server agent Job is the alert job which runs by default every 2 minutes (can be changed) all round the clock which monitors the Primary and Secondary servers and alerts when operations do not complete successfully. This job is created on the Primary server as well as every Secondary server. This job needs to be configured if an Operator needs to be notified or logged on the SQL log files or alerts sent off to a separate Monitor instance.

This log shipping architecture is an active-warm standby architecture. A warm standby server involves taking a full backup (of Primary) and applying the incremental transactional log backups (from Primary) to every secondary server in sequence. The standby servers (secondary servers) are in Standby/Read-only mode which means Read operations are allowed but not update. In the event of a disaster one of the secondary servers is brought online manually. It is important to apply all the outstanding transaction log backups including the backup of the active transaction log from Primary server before bringing the database online which is done using a simple T-SQL statement. There will a little downtime involved when the secondary server needs to be brought online.

A sample log shipping configuration is pictorially depicted below (which I borrowed from MSDN). In this particular instance there are three Secondary servers and one Monitor server. The Backup is taken and stored on a Backup share from which all the other secondary servers copy and restore the transactional log.


When/When-not to use Log Shipping

Although Log Shipping is a great DR strategy and one of my favorites, it doesn’t meet all scenarios and one must be aware before choosing this as your DR solution.

  • Log shipping is implemented at a database level
  • You cannot opt out certain database articles like Tables, stored Procedures from getting replicated
  • Log shipping fail over is manual. Though you could write scripts to automate this.
  • There is always a downtime involved when one of the fail over instances (secondary instance) is brought online. This is a critical factor DR architects need to consider.
  • Because due to the nature of the backup jobs execution timing which usually runs every few minutes there is a possibility that the latest transaction log backup might not have run yet and a fail over triggered or the transit files didn’t make it to the secondary servers, there are chances of some data loss. You could only minimize it by running the backup job more often.
  • In windows clustering the fail over to a passive server is seamless to the application layer but is not the case with log shipping. The application layer will need to be redirected to point to the secondary server that has been brought online or alternatively the load balancer must re-route the traffic to a different set of application servers which are pre-configured to talk to the secondary database server.
  • Unlike clustering where it points to a single set of data file mount points, Log shipping has distinct data mount points. So if one data mount goes bad you still have other secondary mounts available.
  • Clustering isn’t a very efficient solution in geographically dispersed sites but log shipping is apt.

 

Summary

In my next part, I will walk through the exact steps on how to setup a simple log shipping.

High Availability and Disaster Recovery

Introduction

In any Enterprise scale applications one of the most important criteria is Applications redundancy and ability to recover in the events of any disasters. For any IT systems as part of the solution design one of the key phase is defining the applications availability. These are usually over-looked and many IT Architects tend to not be aware. But in my experience this is a very crucial phase that must be defined right at the Architecture phase even before component design begins. These translates to applications and the supported Infrastructure’s High availability and Disaster Recovery.

High Availability:

High availability is the ability of the system to be available (accessible) in the event of system failure by having redundancies or fault-tolerant components. There will be redundancies built-in at multiple levels in order to achieve this right from backup power supply to multiple SMPS units to network ports to load balancers to storage. Even there is redundancy and/or fault-tolerance built-in on the software layer to achieve this. To give you an analogy of what High availability means, imagine you are driving a car. To ensure in the event of some failures your car is still drivable, you necessarily would carry a spare tire in case you run into a flat tire situations. So the truck or for that matter a car has certain amount of redundancies built-in.

Disaster Recovery:

Disaster recovery addresses the availability of the systems in the event of a disaster. It is the process by which systems are restored to a previous acceptable state in the event of a disaster. Continuing with my earlier analogy, if the truck breaks down completely, then a backup truck would arrive, the consignments transferred and the backup truck moves on to the final destination. What is an acceptable state is a very important aspect that needs to be well thought through and defined by the application Architects and then implemented by the Infrastructure support teams. An Acceptable state is defined using Recovery Time Objective (RTO) and Recovery Point Objective (RPO) to drive the DR plan.

Recovery Point Objective (RPO): is the point in time in the past to which you will recover. This is referring to the state of data in the past that you are able to recover.

Recovery Time Objective (RTO): is the point in time in the future at which the application will be up and running. This is usually the time it takes to recover the application before it is accessible to the world.

One of the notable differences between the two are, usually in HA there is no data loss but in DR there might be some data loss. DR is the last option that any organization decides.

Switching OFF DTA Tracking-BizTalk 2004

An old Blog published in 02/19/2009 consolidating in one place

One of the requirement that everyone on the BizTalk Production implementation team will need to do is to switch of DTA tracking. Having DTA tracking turned ON is CPU intensive and is going to slow down your SQL Database operations. It is good to keep this turned ON in the initial days of going into Production, just to have the flexibility to read message details if anything goes wrong, but turn it OFF once the system stabilizes. Here are the steps that needs to be carried out.

  • Open Enterprise manager
  • Under Databases->BizTalkMgmtDb, open the table adm_Group
  • For the property GlobalTrackingOption, switch OFF Global Tracking by changing it to ‘0’ from the default ‘1’
  • Restart all the Biztalk hosts

Azure BizTalk Microservices – POV

An old Blog published in 03/11/2015 consolidating in one place

Introduction

On Dec 3rd,2014 at the Annual BizTalk Integration Summit, Redmond Microsoft announced a new architectural approach, “Azure BizTalk Microservices”.  Microservices is an architectural style that’z not very old in the industry and is a trend that is catching up mostly from the drawbacks of having a monolithic style application architecture.  Now what is BizTalk as Microservices?  It’z simple.  The existing BizTalk EAI features like Validation, Batching/debatching, Extract, Transform etc exposed as Microservices.  When you take this and deploy on the cloud with all the beautifulness of Cloud capabilities added to it, it becomes Azure BizTalk Microservices.

Describe

“Microservice Architecture” is relatively a new term (a few years old) that not many are aware of/heard about it in this industry.  It is a way of designing software applications as a set of smaller services defined around business capabilities and which are independently deployable, run within their own process space and have a simple interface often as HTTP API.

This is in sharp contrast to a Monolithic way of Architecture style which is built as a single unit.  There are some disadvantages with this architecture style such as,

  • Any changes involves a build and deploy of a newer version of the application
  • They will need to understand and scan through the entire system although it might need updates in a very specific section of the solution
  • In large teams somebody who’z just joined in, understanding the entire application is going to be a night mare experience.
  • Horizontally scaling up of the monolith is possible by running many instances behind a load-balancer.

Very large monolithic applications often suffer with larger time-to-markets, less maintainability, huge development and test cycles.

Monolith Architectural style

A Monolith puts all its functionalities into a single process

monolith_1391118272

and scales by creating multiple instances of the monolith

monolith_scaling_-1698997056

Microservices Architectural style

A Microservice puts each of its functionalities into a service

microservice_1245930368

and scales by multiplying the needed functionalities

microservice_scaling_-1286023744

Microsoft would be providing high priority connectors as Microservices that are listed here, supporting all the standard protocols, most popular consumer and Enterprise SaaS systems eg:- Twitter, Facebook to Office365, salesforce etc.  We should also be able to build our own Microservices and publish them to our internal enterprise corporate gallery or marketplace, in a language agnostic way and would support both auto and manual updates.

out-of-box%20microservices_527531072

Microsoft is taking all the existing BizTalk service and server features and hosting them as Microservices inherent to the platform.  So typically we could take any of the SaaS connectors and leverage the BizTalk Microservices with existing protocols and build an Application Integration along with Workflow engine which can Orchestrate these Microservices.  These workflows would be JSON based definitions, support long running processes, rich logging and diagnostics.

biztalkfeaturesasmicroservices_-648144384

Conclusion

Microservices will be released on Azure in the Q1 of 2015 and it is really exciting to look forward.  This is a paradigm shift in Microsoft BizTalk server strategy and I’m sure has made other EAI vendors in the market to stand up and take account of.  It’z really an interesting thing to check how the BizTalk Services and Microservices are going to span out.