For distributed database application environment, it is always required to synchronize different database servers, back up, copy Transaction Logs, etc. If we are going to implement using application we have to put lots of efforts to build up the application. SQL Server 2005 provides an advanced feature called Log Shipping. Log shipping is an Automated Process for backing up, restoring, copying the transaction logs and synchronizing the database for distributed database server application which can improve the application performance and availability of database. In my recent project, I have done some short of experiment on it. I am going to explain it in this article.
What is Log Shipping?
Log Shipping is used to synchronize the Distributed Database Server. Synchronize the database by copying Transaction logs, Backing up, Restoring data. SQL Server used SQL Server Job Agents for making those processes automatic. Log Shipping does not involve automatic transfer of server if there is any failure. This means it has just synchronized the databases but if the primary server fails, it will not redirect your application to a secondary server. This has to be done manually.
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all.
The key feature of log shipping is it will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers in “synch”.
Should the production server fail, all you have to do is point the users to the new server, and you are all set. Well, it’s not really that easy, but it comes close if you put enough effort into your log shipping setup.
The Need for Standby Servers:
In a perfect world we wouldn’t need standby servers for our SQL Servers. Our hardware would never fail, NT Server 4.0 or Windows 2000 would never blue screen, SQL Server would never stop running, and our applications would never balk.
In a partially perfect work, we could afford very expensive clustered SQL Servers that automatically failover our wounded and dead production SQL Servers, reducing our stress and keeping our users very happy.
But for most of us, the closest thing we can afford to implement when it comes to SQL Server failover are standby servers that we have to manually fail over. And even some of us can’t afford this. But for this article, I am going to assume that you can afford a standby server.
The concept of standby servers is not a new one. It has been around a long time and been used by many DBAs. Traditionally, using a standby server for failover has involved manually making database and log backups on the production server and then restoring them to the standby server on a regular basis. This way, should the production server fail, then users could access the standby server instead, and downtime and data loss would be minimized.
This article is about log shipping, a refined variation of the traditional manual standby failover server process. Its two major benefits over the traditional methods are that it automates most of the manual work and helps to reduce potential data loss even more.
Benefits of Log Shipping:
While I have already talked about some of the benefits of log shipping, let’s take a more comprehensive look:
Log shipping doesn’t require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement. In addition, you can use the standby server for other tasks, helping to justify the cost of the standby server. Just keep in mind that if you do need to fail over, that this server will have to handle not one, but two loads. I like to make my standby server a development server. This way, I keep my developers off the production server, but don’t put too much work load on the standby server.
Once log shipping has been implemented, it is relatively easy to maintain.
Assuming you have implemented log shipping correctly, it is very reliable.
The manual failover process is generally very short, typically 15 minutes or less.
Depending on how you have designed your log shipping process, very little, if any, data is lost should you have to failover. The amount of data loss, if any, is also dependent on why your production server failed.
Implementing log shipping is not technically difficult. Almost any DBA with several months or more of SQL Server 7 experience can successfully implement it.
Problems with Log Shipping:
Let’s face it, log shipping is a compromise. It is not the ideal solution, but it is often a practical solution given real-world budget constraints. Some of the problems with log shipping include:
Log shipping failover is not automatic. The DBA must still manually failover the server, which means the DBA must be present when the failover occurs.
The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over.
Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.
The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally.
When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user’s applications to the new standby server. In some cases, neither of these options is practical.
Log Shipping Overview:
Before we get into the details of how to implement log shipping, let’s take a look at the big picture. Essentially, here’s what you need to do in order to implement log shipping:
Ensure you have the necessary hardware and software properly prepared to implement log shipping.
Synchronize the SQL Server login IDs between the production and standby servers.
Create two backup devices.
One will be used for your database backups and the other will be used for your transaction log backups.
On the production server, create a linked server to your standby server.
On the standby servers, create two stored procedures. One stored procedure will be used to restore the database. The other stored procedure will be used to restore transaction logs.
On the production server, create two SQL Server jobs that will be used to perform the database and transaction log backups. Each job will include multiple steps with scripts that will perform the backups, copy the files from the production server to the standby server, and fire the remote stored procedures used to restore the database and log files.
Start and test the log shipping process.
Devise and test the failover process.
Monitor the log shipping process:
Obviously I have left out a lot of details, but at least now you know where we are headed.
To make my explanations easier to understand in this article, all my examples assume you will be failing over only one database from the production server to the standby server. In the real world you will probably want to failover more than just one. Once you have implemented log shipping for one database, it should be obvious how to implement others.
Generally, I just add additional databases to my already existing scripts and jobs. But if you prefer, you can create separate scripts and jobs for each database you want to failover using log shipping.
As you read the details of how I implement log shipping below, you may think of other ways to accomplish the same steps
Hardware and Software Requirements:
The hardware and software requirements for log shipping are not difficult. The hardware for the production and the standby server should be as similar as you can afford. If your production server only handles a couple of dozen simultaneous users, then you probably don’t need to spend a small fortune on making the standby server just like the production server.
On the other hand, if your production server handles 500 simultaneous users, or has multi-gigabyte database, then you may want to make your standby server as similar to the production server as you can afford.
As far as software is concerned, I just try to ensure than I have NT Server and SQL Server at the same level of service packs. In addition, the two servers must have SQL Server 7 configured similarly. For example, the code page/character set, sort order, Unicode collation, and the local all must be the same on both servers.
In order to help reduce any potential data loss during server failover from the production server to the standby server, your production server should have its transaction logs stored on a separate physical drive array than the database files. While this will boost your server’s performance, the main reason for this is to help reduce data loss.
For example, if the drive array with your database files on it goes down, then hopefully the drive array with the log files will be OK. If this is the case, then you should be able to recover the transaction log and move it to the standby server, significantly reducing any data loss. But if the transaction logs are on the same drive array as the database files, and the drive array fails, then you have lost any data entered into the system since the last log file was shipped to the standby server.
The main functions of Log Shipping are as follows:
- Backing up the transaction log of the primary database
- Copying the transaction log backup to each secondary server
- Restoring the transaction log backup on the secondary database
Components of Log Shipping
For implementing Log Shipping, we need the following components – Primary Database Server, Secondary Database Server, and Monitor Server.
- Primary Database Server: Primary Sever is the Main Database Server or SQL Server Database Engine, which is being accessed by the application. Primary Server contains the Primary Database or Master Database.
- Secondary Database Server: Secondary Database Server is a SQL Server Database Engine or a different Server that contains the backup of primary database. We can have multiple secondary severs based on business requirements.
- Monitor Server: Monitor Server is a SQL Server Database Engine which Track the Log shipping process.
Log Shipping Prerequisites
- Must have at least two Database Servers or two SQL Server 2005 Database Engines.
- Configuration user should have Admin privilege on that server
- SQL Server Agent Service Configured properly
- Configuration mode of Primary database should be a Full or Bulk Logged recovery model.
- Shared folder for copying the transaction logs.
SQL Server 2005 Version that Supports Log Shipping
|SQL Server 2005 Version||Available|
|SQL Server 2005 Enterprise Edition||Yes|
|SQL Server 2005 Workgroup Edition||Yes|
|SQL Server 2000 Standard Edition||Yes|
|SQL Server 2005 Developer Edition||Yes|
|SQL Server 2005 Express Edition||No|
Background Tables in Log shipping:
TABLES CREATED IN MSDB: