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. If so, then great. I am sure there are many different ways to implement log shipping, some of which are probably an improvement over mine. So feel free to improve upon my methods.
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 server.
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.
Synchronize SQL Server Login IDs
At this point, we are going to get into the details of how to actually set up log shipping. So get ready to be hit by lots of obscure details.
The first step before you can begin log shipping is to ensure that the SQL Server login IDs are synchronized between the production and standby servers. This is important because SQL Server maintains separate security among servers, and you will be making a backup of a database on one server and restoring it to another. In order for the database user IDs (found in each database) to work on the standby server (where the database is restored), there must be a matching SQL Server login ID on that server, otherwise users will not be able to log into the database on the standby server.
There are several ways to do this:
- Manually synchronize SQL Server login IDs between the two servers. While effective, rather boring.
- Script the login IDs from the production server and then run the script on the standby server to create the login IDs. You will have to do this periodically as users are added and deleted. While scripting works great for SQL Server login IDs that use NT Authentication, it doesn’t work so well for login IDs that use SQL Server Authentication. This is because if you have login IDs using SQL Server Authentication, you will have to know the user’s passwords in order to set them, or figure out some way to get users to set the passwords themselves. Login IDs that use NT Authentication are no problem because passwords aren’t used.
- Backup the Master database on the production server and restore it onto the standby server just before you begin the log shipping process. And should you ever have to failover your server, you will then have to restore the Master database again in order to keep your user accounts in synch.
The option you choose will depend on how the standby server is used.
But if you will be using the standby server for other databases, such as for development databases, you won’t be able to using log shipping on the Master database. Instead, you will have to use scripts, or manually keep the two servers synchronized. As I have already mentioned, if you have to do this, then you will want to use only login IDs that use NT Authentication, not SQL Server Authentication, because of the password’s issue.
Another problem with login IDs using SQL Server Authentication is the fact that when you restore a database to another SQL Server, you must synchronize the login IDs on the server with the database user IDs in the database. This is not a problem with user IDs that user NT Authentication. To synchronize the logins IDs and the database users IDs, you must run the sp_change_users_login stored procedure.
If you want to make log shipping easy, then don’t use the standby server for anything other than failover, and always use NT Authentication for your login IDs. While you don’t have to do either, your life will just be a little more stress-free.
Once you have figured out a way to synchronize the SQL Server login IDs, you are ready to begin the next step.
No comments:
Post a Comment