What is Log Shipping?
Log Shipping is a process that provides an alternative to replication. It starts with a source database (primary database), from which a full backup is taken. This backup is then restored as a new database, which is used as the copy database (secondary database). The source database then provides regular transaction log backups that contain all the changes made to it. These backups are used to restore the changes to the copy database. The process of taking transaction log backups, copying the backup files and restoring the backups is automated via SQL Server Agent. Multiple copy databases can be set up if required.
Log Shipping Features
■ Simple to set up and maintain
■ Reliable and robust
■ Source and copy databases can be completely remote from one another
■ Unlimited number of copy databases can be set up
■ Does not affect the performance of the source database
■ Available to SQL Server 2000*, 2005 and 2008.
Log Shipping Limitations
■ The copy databases can only be full copies of the whole source database; therefore, Log Shipping cannot be used to merge multiple sources into a single central copy database.
■ All copy databases have Read-Only attributes.
■ Automation requires SQL Server Agent; so realistically, Log Shipping is not available with SQL Server Express editions.
■ The backup strategy applied to the source database needs to take Log Shipping into consideration. (*SQL Server 2000 may be problematic in this area.)
Log Shipping Process
This process can be described by the following simple steps:
1. SQL Server Agent job fires on the source server and takes a transaction log backup of the source database, placing the backup file in a shared directory.
2. SQL Server Agent job fires on the copy server and copies the backup file from the shared directory to a
location on its own hard drive.
3. Another SQL Server Agent job fires on the copy server and uses the copied backup file to restore the transactions from the source database to the copy database.
Then click on "connect" button in the below screen and select the secondary instance in my case ( select any secondary server if your secondary instance is not on your primary). In order to add more secondary servers, we can add any number of secondary server's with the help of before screen.
And then select the secondary database in the very next drop down list.
In this step you can specify how to create the data on the secondary server. You have three options: create a backup and restore it, use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.
Since we already taken a full backup of the database in the beginning, we go for the second option. And we need to click on the "Restore Options.." button. Then appear's a new screen in which we need to give the path for the ".mdf" and ".ldf" files of the database in the primary server's instance.
Upon clicking "ok" button, add the path for backup file in the primary server as below.
Mention the folder path in the "copy files" tab for storing the copy of full backup ( which is a result of copy job ) in the secondary server.
In "Restore Transaction Log" tab, select the option the you desired as per your requirement. I am selecting the "standby" option. Click on "ok" upon that.
Now it is upto you to have a monitor server or not. We can perform log shipping without monitor server.
If desired, select the check box in the below screen.
You get the below screen upon clicking that check box followed by "Settings.." button.
Click on "Connect" button to add a monitor server. I am adding it as my secondary server.
Now click on "ok" in the above screen and "ok" in the following screen. You can the see the progress of log shipping configuration.
Click on "Close" upon completion.
Now go back to the secondary server instance and expand it. And then expand the databases folder. You can see the "credit" database in a standby mode (Result of our log shipping configuration).
Now to get the standby database into online, execute the below command in the management studio.
You get an error if you are using the same database which you are trying to get online for executing the above command as below.
Change the using database to master as below.
And the click on "execute" button. You will get the below screen.
Now you can refresh the databases folder for seeing our "credit" database online.
This in my first blog. So, please post some comments upon reading this so that i will improve and correct myself if i need to in my next blog posts.
Thank you everyone for reading!
Log Shipping is a process that provides an alternative to replication. It starts with a source database (primary database), from which a full backup is taken. This backup is then restored as a new database, which is used as the copy database (secondary database). The source database then provides regular transaction log backups that contain all the changes made to it. These backups are used to restore the changes to the copy database. The process of taking transaction log backups, copying the backup files and restoring the backups is automated via SQL Server Agent. Multiple copy databases can be set up if required.
Log Shipping Features
■ Simple to set up and maintain
■ Reliable and robust
■ Source and copy databases can be completely remote from one another
■ Unlimited number of copy databases can be set up
■ Does not affect the performance of the source database
■ Available to SQL Server 2000*, 2005 and 2008.
Log Shipping Limitations
■ The copy databases can only be full copies of the whole source database; therefore, Log Shipping cannot be used to merge multiple sources into a single central copy database.
■ All copy databases have Read-Only attributes.
■ Automation requires SQL Server Agent; so realistically, Log Shipping is not available with SQL Server Express editions.
■ The backup strategy applied to the source database needs to take Log Shipping into consideration. (*SQL Server 2000 may be problematic in this area.)
Log Shipping Process
This process can be described by the following simple steps:
1. SQL Server Agent job fires on the source server and takes a transaction log backup of the source database, placing the backup file in a shared directory.
2. SQL Server Agent job fires on the copy server and copies the backup file from the shared directory to a
location on its own hard drive.
3. Another SQL Server Agent job fires on the copy server and uses the copied backup file to restore the transactions from the source database to the copy database.
How to do?
Generally log shipping is performed with two different server instances.
But here i am creating two instances in a single server and configuring the log shipping for "credit" database.
You can observe the two instances namely SQLSERVER2008 and INSTANCE1.
I am assuming SQLSERVER2008 as my primary server. So expand it and then right click on the credit database and take a full database backup.
In order to configure a database for log shipping, first we need to enable log shipping for that database. So, First right click on credit database -> select properties -> Transaction log shipping in left menu.
Now enable it to see the below screen.
Click on backup settings button, to see the below screen.
We need to give a network path for a backup share folder even if the backup folder is present in the local machine. Use backup compression option according to your knowledge towards that. And click on "ok" finally when you are done.
Upon clicking "ok",we get the below screen. Click on "Add.." button to add a secondary server.
And then select the secondary database in the very next drop down list.
In this step you can specify how to create the data on the secondary server. You have three options: create a backup and restore it, use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.
Since we already taken a full backup of the database in the beginning, we go for the second option. And we need to click on the "Restore Options.." button. Then appear's a new screen in which we need to give the path for the ".mdf" and ".ldf" files of the database in the primary server's instance.
Upon clicking "ok" button, add the path for backup file in the primary server as below.
Mention the folder path in the "copy files" tab for storing the copy of full backup ( which is a result of copy job ) in the secondary server.
In "Restore Transaction Log" tab, select the option the you desired as per your requirement. I am selecting the "standby" option. Click on "ok" upon that.
Now it is upto you to have a monitor server or not. We can perform log shipping without monitor server.
If desired, select the check box in the below screen.
You get the below screen upon clicking that check box followed by "Settings.." button.
Click on "Connect" button to add a monitor server. I am adding it as my secondary server.
Now click on "ok" in the above screen and "ok" in the following screen. You can the see the progress of log shipping configuration.
Click on "Close" upon completion.
Now go back to the secondary server instance and expand it. And then expand the databases folder. You can see the "credit" database in a standby mode (Result of our log shipping configuration).
Now to get the standby database into online, execute the below command in the management studio.
You get an error if you are using the same database which you are trying to get online for executing the above command as below.
Change the using database to master as below.
And the click on "execute" button. You will get the below screen.
Now you can refresh the databases folder for seeing our "credit" database online.
This in my first blog. So, please post some comments upon reading this so that i will improve and correct myself if i need to in my next blog posts.
Thank you everyone for reading!
Thank you brother..!
ReplyDelete