Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log shipping and A Control Mechanism Expand / Collapse
Author
Message
Posted Thursday, July 01, 2010 12:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:02 AM
Points: 138, Visits: 259
Comments posted to this topic are about the item Log shipping and A Control Mechanism


Post #945938
Posted Thursday, July 01, 2010 3:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 03, 2014 8:41 AM
Points: 3, Visits: 69
Hi,
Very interesting article. However, I wasn't sure if the reason for architechting this solution was to facilitate log shipping replication for SQL 2000 and below, or as a way of improving upon 2005's already available functionality. Obviously, there is a mention that this is a custom solution to the problem of implementing log shipping replication.

Thanks,
Post #946027
Posted Thursday, July 01, 2010 12:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 30, 2012 5:12 AM
Points: 40, Visits: 143
amarokmusic (7/1/2010)
Hi,
Very interesting article. However, I wasn't sure if the reason for architechting this solution was to facilitate log shipping replication for SQL 2000 and below, or as a way of improving upon 2005's already available functionality. Obviously, there is a mention that this is a custom solution to the problem of implementing log shipping replication.

Thanks,


Log shipping can be used not only for DR but to keep data periodically updated as ETL process for the usage on the other servers. The article shows how to use control tables and how to make solution that is more robust than Microsoft's one. We don't have any issues with this solution for years while Microsoft's solution required reset from time to time.
Post #946418
Posted Friday, July 02, 2010 7:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 4,128, Visits: 5,837
I had to do a similar home-grown approach for a client of mine with many thousands of databases on a single server that we needed to get over to a reporting/DR box. Quite interesting!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #946817
Posted Saturday, July 24, 2010 3:16 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, January 24, 2014 7:02 AM
Points: 3,066, Visits: 1,413
Great piece Leo. I think for DBA's with less experience (Like myself) it would have been nice if the article was little more detailed, but then I guess it would be a book instead of an article.

Thanks for it. I'm experimenting a bit with it. Could you please tell me where could I get more detailed information regarding the two Jobs you created. Thanks in advance!





My blog
Post #958365
Posted Monday, July 26, 2010 8:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 30, 2012 5:12 AM
Points: 40, Visits: 143
Ignacio A. Salom Rangel (7/24/2010)
Great piece Leo. I think for DBA's with less experience (Like myself) it would have been nice if the article was little more detailed, but then I guess it would be a book instead of an article.

Thanks for it I'm experimenting a bit with it. Could you please tell me where could I get more detailed information regarding the two Jobs you created. Thanks in advance!


Unfortunately, you need implement those jobs by yourself. I did describe the steps that each job should have and pieces of code in the article.

"To keep log shipping near real time, I created two additional jobs. The first job is on the source server and will be started by the alert from a trigger on the table ls_backupset. This job has several steps such as copying database log files to the destination server based on the information from ls_backupset and CopySetHistory tables. When each log file is copied, the record of success or failure becomes inserted to the history table CopySetHistory. To define the log backup file’s name the last two characters have to be taken out from the file’s name in table msdb.. backupset. This is how Microsoft writes it in the name field. It can be done while inserting the records to the table ls_backupset and at the same time extension trn can be added at the end of the file’s name.

Then, this job starts a second job which is located on the destination server. It can be done many different ways. For example, one way is through the linked server option between source and destination servers. The destination server job is going to insert all new records from the source control tables: ls_backupset and CopySetHistory.

To restore log files,all database connections must be killed first. I developed 3 stored procedures. The first one is restoring one log file based on the next submitted parameters – database name, log file path and name, standby file path and name. The second one is the wrapper for one database that allows us to restore multiple log files for this database in a loop. The third one is the wrapper which allows us to restore log files for multiple databases on the same server. Remember that table BackupDBInfo has all the necessary information for each database and this allows us to construct a copy and restore statements with dynamically built code inside the stored procedures. Also, it permits us to make the procedures generic. "

Post #958804
Posted Tuesday, July 27, 2010 3:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, January 24, 2014 7:02 AM
Points: 3,066, Visits: 1,413
Thank you for your response Leo. I don't think it is unfurtunate that I have to implement the jobs myself, on the contrary I'm quite excited by the challenge.

You did indeed describe the jobs, but what I was actually for was a sample TSQL for the job steps. For example to create the trigger on the ls_backupset table. I was considering to create a SSIS package that will be called by the trigger on the ls_backupset table. What do you think about it?
Once again thank you for your time and help.





My blog
Post #959280
Posted Tuesday, July 27, 2010 6:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 30, 2012 5:12 AM
Points: 40, Visits: 143
Ignacio A. Salom Rangel (7/27/2010)
Thank you for your response Leo. I don't think it is unfurtunate that I have to implement the jobs myself, on the contrary I'm quite excited by the challenge.

You did indeed describe the jobs, but what I was actually for was a sample TSQL for the job steps. For example to create the trigger on the ls_backupset table. I was considering to create a SSIS package that will be called by the trigger on the ls_backupset table. What do you think about it?
Once again thank you for your time and help.


You refered to "The first job is on the source server and will be started by the alert from a trigger on the table ls_backupset"
This is simple trigger that has basically one line of code with raiserror statement. Please read article (see link) and it gives you all necessary trigger info. http://www.sqlservercentral.com/articles/Administration/sqlserveralerts/1435/
Post #959327
Posted Tuesday, July 27, 2010 7:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, January 24, 2014 7:02 AM
Points: 3,066, Visits: 1,413
Thank you Leo!




My blog
Post #959385
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse