Sql server has log shipping as one of the disaster recovery solution. Log shipping provides the automated way of taking the log back ups on one server & restore them on another server.
Log shipping in general will have one primary server with Primary Database , Multiple secondary servers where the log will be shipped to & monitor server used to monitor the activities & raise the alerts when some thing goes wrong. We can use either primary server (or) secondary server to serve the purpose of Monitoring , How ever if some thing goes wrong on the server then there could be chance to loose the monitoring as well, that's why it is suggested to have a seperate dedicated server for monitoring.
When logshipping is configured , 4 jobs will be created on the primary , secondary & monitor servers.
1) Back up job is created on primary server - purpose of this job is to take a database back up
2) Copy job is created on secondary server - purpose of this job is to copy the back up database to secondary server location
3) Restore job is created on secondary server - purpose of this job is to restore the back up log file that is copied over to secondary server in step 2
4) Alert Job is created on either secondary server (or) monitor server based on where the monitoring is configured , purpose of this job is to alert if some thing goes wrong in the process.
Sql server agent which runs these jobs should have read/ write permission to the location where the back ups will be taken , copied to.
In this presentation i will show the implementation of logshipping by taking one default instance & other named instance on the same machine.
1) create a database on primary server.
CREATE DATABASE [testdb] ON PRIMARY
( NAME = N'testdb', FILENAME = N'E:\SQLDATA\testdb.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testdb_log', FILENAME = N'G:\SQLLOG\testdb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
4) configuring the primary database : right click the database -> properties -> transaction logshipping
set the network path where the backups has to be taken. Edit job to schedule the time how frequent the back ups has to be taken.
5) Add the secondary server instance , here we can add as many secondary servers as we want .
6) Restore Transaction Log can be set to No recovery mode (or) stand by mode . Advantage with stand by mode is the database can be used in read only mode . This is useful for reporting purposes.
7) Configure the Monitor server , here i am using the named instance as both secondary & monitor server.
8) Now we will have 4 sql jobs one on primary & rest on secondary servers.
9) Thats all, Logshipping is configured.
Monitoring Logshipping :
Logshipping can be monitor 3 ways
1) GUI : Right click on the server instance -> reports -> transaction logshipping
2) system stored procedures.
sp_help_log_shipping_monitor_primary 'satishbudati-pc', 'testdb'
sp_help_log_shipping_monitor_secondary 'satishbudati-pc\inst1', 'testdb'
3) msdb tables :
select * from msdb..log_shipping_monitor_primary
select * from msdb..log_shipping_monitor_secondary
Additional Reading Material : http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/02/24/scheduling-sub-minute-log-shipping-in-sql-server-2008.aspx
Log shipping in general will have one primary server with Primary Database , Multiple secondary servers where the log will be shipped to & monitor server used to monitor the activities & raise the alerts when some thing goes wrong. We can use either primary server (or) secondary server to serve the purpose of Monitoring , How ever if some thing goes wrong on the server then there could be chance to loose the monitoring as well, that's why it is suggested to have a seperate dedicated server for monitoring.
When logshipping is configured , 4 jobs will be created on the primary , secondary & monitor servers.
1) Back up job is created on primary server - purpose of this job is to take a database back up
2) Copy job is created on secondary server - purpose of this job is to copy the back up database to secondary server location
3) Restore job is created on secondary server - purpose of this job is to restore the back up log file that is copied over to secondary server in step 2
4) Alert Job is created on either secondary server (or) monitor server based on where the monitoring is configured , purpose of this job is to alert if some thing goes wrong in the process.
Sql server agent which runs these jobs should have read/ write permission to the location where the back ups will be taken , copied to.
In this presentation i will show the implementation of logshipping by taking one default instance & other named instance on the same machine.
1) create a database on primary server.
CREATE DATABASE [testdb] ON PRIMARY
( NAME = N'testdb', FILENAME = N'E:\SQLDATA\testdb.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testdb_log', FILENAME = N'G:\SQLLOG\testdb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
2) take back up of the database
BACKUP DATABASE [testdb] TO DISK = N'E:\SQLBACKUPS\testdb.bak' WITH NOFORMAT, NOINIT,
NAME = N'testdb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
3) restore the database on second server
RESTORE DATABASE [testdb] FROM DISK = N'E:\SQLBACKUPS\testdb.bak' WITH FILE = 1,
MOVE N'testdb' TO N'E:\SQLDATA\INST1\testdb.mdf',
MOVE N'testdb_log' TO N'G:\SQLLOG\INST1\testdb.ldf',
STANDBY = N'E:\SQLBACKUPS\INST1\ROLLBACK_UNDO_testdb.BAK', NOUNLOAD, STATS = 10
GO
set the network path where the backups has to be taken. Edit job to schedule the time how frequent the back ups has to be taken.
7) Configure the Monitor server , here i am using the named instance as both secondary & monitor server.
8) Now we will have 4 sql jobs one on primary & rest on secondary servers.
9) Thats all, Logshipping is configured.
Monitoring Logshipping :
Logshipping can be monitor 3 ways
1) GUI : Right click on the server instance -> reports -> transaction logshipping
sp_help_log_shipping_monitor_primary 'satishbudati-pc', 'testdb'
sp_help_log_shipping_monitor_secondary 'satishbudati-pc\inst1', 'testdb'
select * from msdb..log_shipping_monitor_primary
select * from msdb..log_shipping_monitor_secondary
Additional Reading Material : http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/02/24/scheduling-sub-minute-log-shipping-in-sql-server-2008.aspx
No comments:
Post a Comment