Configuring
this section will list some of the important considerations to take into account when configuring your solution.
Keeping the Data in Sync
This is the most important aspect of configuring log shipping. How close should the secondary server be behind the primary? Or, from a business standpoint, how many transactions can we afford to lose? A few factors go into making this decision:
| | How many transactions are generated per hour? This number should be measured in some way before implementing log shipping. One possible way is to load test the application in a lab situation. |
How big is the transaction log file generated, and how long does it take to copy and apply to the secondary server? |
By default, transaction logs are backed up every 15 minutes. You can set this interval to be as frequent as every 1 minute, or as infrequent as, say, every 30 or 60 minutes. Each environment and use will be different. On a highly utilized OLTP database, such as one that would power an e-commerce site, transactions are more important, so the backup frequency may drop to every 5 minutes to ensure a smaller file size.
In conjunction with determining the frequency of transaction log file backups, devise a plan as to how often the files will be copied to the secondary server(s), and if there will be any delay in applying them. If the load delay is set to 0, which is the default, the transaction log file will be applied as soon as it is copied.
Servers
This section will detail where to place your servers for log shipping, and some tips on connectivity.
Location
The location of the log shipped servers is crucial to a successful implementation. If the primary and secondary are located next to each other in the same data center, and a failure occurs, you may have lost both your main and backup servers. So the point of having redundancy is negated.
Log shipping allows you to place servers in any geographic location. This is the recommended practice. For example, a company has data centers in Boston, New York, Tokyo, London, and Sydney. The primary HR server is located in New York, but is log shipped to servers in each location. If it is not possible to spread the primary and secondary servers to different locations, then other precautions should be met: The primary and secondary should be, at a minimum, on different power grids, and other similar high availability principles should be applied.
The log shipping pair should never exist on the same server. Even though this is possible due to the multiple instances support of SQL Server 2000, the reason for not having them on the same box is that the server becomes a single point of failure.
Connectivity
The log shipping pair must be able to communicate with each other. This may mean that both servers are part of the same domain,or that proper login credentials are provided so the servers have the proper access. Ensure that all secondary servers also have connectivity to the public network so all users and applications can connect to it in the event of a switch.
Basic connectivity is not the only thing to take into consideration. Since log shipping does not utilize any form of compression on the transaction log files or the initial backup made by the Database Maintenance Plan Wizard, the files copied over the network have the potential of being large. As these files grow, they may take longer to copy, and slow down the log shipping process. So having a properly designed network with low latency is important. You can compensate for the size of the files and the corresponding latency if the load delay is altered.
