How to Solve Monitoring LSAlert Job Failed as Log shipping Report shows Red Even copy and restore Job Succeeded with DEMO

How to Solve Monitoring LSAlert Job Failed as Log shipping Report shows Red Even copy and restore Job Succeeded With DEMO

What is SQL Server log shipping?

SQL Server log shipping is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations 

A log shipping session involves the following steps:

• Backing up the transaction log file on the primary SQL Server instance
• Copying the transaction log backup file across the network to one or more secondary SQL Server instances
• Restoring the transaction log backup file on the secondary SQL Server instances

Implementation examples

One of the common log shipping scenarios is the environment with two servers (SQLServer-1 – primary and SQLServer-2 – secondary), two SQL Server instances (SQLInstance-1 and SQLInstance-2), and one SQL Server database named SQLDB-1 with log shipping running on it

Another common configuration is the environment with three (or more) servers (SQLServer-1 – primary, SQLServer-2 – secondary, and SQLServer-3 – secondary), three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3), and one SQL Server database named SQLDB-1 with log shipping running on it

Operating modes

There are two available modes and they are related to the state in which the secondary, log shipped, SQL Server database will be:

• Standby mode – the database is available for querying and users can access it, but in read-only mode
  • The database is not available only while the restore process is running
    • Users can be forced to disconnect when the restore job commence
    • The restore job can be delayed until all users disconnect themselves
• Restore mode – the database is not accessible

Advantages and disadvantages of using SQL Server log shipping

SQL Server log shipping is primarily used as a disaster recovery solution. Using SQL Server log shipping has multiple benefits: it’s reliable and tested in details, it’s relatively easy to set up and maintain, there is a possibility for failover between SQL Servers, data can be copied on more than one location etc.

Log shipping can be combined with other disaster recovery options such as AlwaysOn Availability Groups, database mirroring, and database replication. Also, SQL Server log shipping has low cost in human and server resources

The main disadvantages in the SQL Server log shipping technique are: need to manage all the databases separately, there isn’t possibility for an automatic failover, and secondary database isn’t fully readable while the restore process is running

Primary Server

Primary Server LogShipping Report

Secondary Server 

Secondary Server  LogShipping Report

Primary Server Job Info

Secondary Server Job Info

Here in Secondary server Monitoring Job is failing due to wrong scheduled restore job alert

Here copy job scheduled as 6 hours

Here Restore job scheduled as 6 hours but alert job scheduled as 45 mints it is conflict

To fix Need to schedule as 6 hours

After our change in alert now click submit to configured 

Now we are running job

It went success now

see status as success

that is now Red alert issue is fixed it

Happy Blogging

Step by Step How to Setup Clustering Instance with Logshipping on SQL Server With Demo

How to Setup Clustering Instance with Logshipping on SQL Server With Demo

What is Clustering?

A Microsoft SQL Server Cluster is nothing more than a collection of two or more physical servers with identical access to shared storage that provides the disk resources required to store the database files. 

These servers are referred to as "nodes".  Each of the nodes talk to one another via a private network, sending a heartbeat signal between them.  Should one node not communicate its heartbeat to the other node in the cluster the secondary node will take ownership of any dependent services being run by the node that lost communication.  This process is referred to as "failover".

A failover can occur both automatically (a server's heartbeat stops communicating) or manually.  A manual failover is beneficial in the event that patching or some other form of maintenance is required at the physical server level.  You would typically implement clustering in order to ensure that if you ever encounter hardware failure on the physical server hosting your SQL instance, your databases would continue to be available for dependent applications and their users. 

Unlike other clustering technologies that are implemented for better performance or for increased processing power via load-balancing, SQL clusters are designed for providing highly-available databases; eliminating downtime associated with hardware failure.  This architectural concept is referred to as "High Availability Clustering" or "HA Clustering" for short.  The service or groups of services that are hosted on a clustered node are respectively referred to as resources and resource groups.  Since these resources must be available to all nodes in a cluster then they must reside on a shared disk array in the form of SAN-NAS disk.  Each resource group will be mapped to a logical drive that is physically hosted on the shared disk array and will also have it's own associated IP address and network name. 

The SQL Server Installation Process on a Cluster

The SQL Server installation process detects when an installation is being attempted on a clustered node and will prompt you as to whether you wish to configure the SQL instance as clustered or not.  If you proceed with creating a clustered instance of SQL Server, the instance will be hosted on a "Virtual" Server.  Resources such as data and log files will be created on the shared SAN-NAS disk for SQL Server, SQL Server Agent, and Full-Text Indexing.

If selected in the installation process, Notification Services and Analysis Services are also cluster-aware in SQL Server 2005.  Conversely, the associated program files for the instance will be installed on the local drives of each of the clustered nodes in an identical fashion and registry values are set identically across all clustered nodes.  Since the "Virtual" server resides solely on the SAN it can be "owned" by any of the nodes you allow.  Each of the nodes can run these resources in identical fashion because each physical server/node has the program files and identical registry settings necessary to run the SQL instance. 

Furthermore, the users are oblivious to the underlying fluidity of the server.  They connect to it as they would any other physical server:  by server name (virtual server name in this case) if the default instance or by virtual server name\instance name if a named instance.  This is key for application connectivity.  Since the SQL instance simply changes ownership during a failover, connection strings the applications rely on to connect to their databases need not be recoded; the physical server may become unavailable, but the virtual server persists after the failover.

Active/Active or Active/Passive Clustering

Clusters are often referred to as either Active/Active or Active/Passive.  Just as you would expect by the name, in an Active/Active cluster there will be two or more nodes, each one owning an instance of Microsoft SQL Server.  If one node fails, the instance it owns would fail over to the other node, running along side (and contending for resources with) the other instance.  An Active/Passive architecture  requires that no matter how many nodes make up the cluster, at least one node is not the owner of an instance of SQL Server.  It is "passive" and only exists to accept a failover of a node hosting a SQL instance in the event of a failover. 

Current Microsoft licensing policies require you to only license the active nodes running Microsoft SQL Server.  The passive node need not be licensed.

How Many Nodes?

Today's clustering technology under Windows 2003 and Microsoft SQL Server 2005 Enterprise Edition allows for up to eight nodes to be combined into a single cluster.  The release of Windows 2008 and Microsoft SQL Server 2008 Enterprise Edition will bring with it the ability to double that to sixteen nodes.  (You are limited to two nodes if you utilize SQL Server Standard Edition.)  Do you want to cluster multiple databases of various SLAs within many nodes on a single cluster?  Is it beneficial to dedicate a two-node cluster to a single database?  The answer is:  "It Depends."  We look into this in detail in a future tip.

Pros and Cons

While clustering protects you from hardware failure relating to the server hosting the SQL Server instance, it does not protect you from media failure.  Unlike replication, database mirroring, or log shipping there is only a single copy of your database.  If the SAN-NAS encounters a failure then you could not only conceivably incur downtime, but possibly data loss.  It is recommended that you incorporate redundancy of your SAN-NAS or database mirroring with your clustering configuration to protect you from media failures.  Hardware and licensing costs may be high.  In an Active/Passive clustering model you'll purchase hardware you hope to never need to use.  The cluster build is more complex than a standalone server setup.  The physical build of the cluster is outside the scope of this discussion however.  Additional benefits for clustering include simplicity for installation of SQL and ease of administration and maintenance.

Summary

There is plenty to consider when planning on clustering SQL Server.  Hopefully you were able to get a better understanding of what clustering is and an idea of the terminology associated with clustering SQL Server 2005.  In the remaining tips in this series we will review these items in greater detail, walking you through the decisions you'll make on architecture, the installation process, the tools available to monitor the state of the cluster and its resources, as well as comparing clustering to the other high-availability options associated with Microsoft SQL Server.

Demo