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 12»»

Custom Log Shipping Expand / Collapse
Author
Message
Posted Thursday, May 1, 2008 12:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 31, 2014 1:03 AM
Points: 215, Visits: 428
Comments posted to this topic are about the item Custom Log Shipping
Post #493428
Posted Thursday, May 1, 2008 8:24 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:43 PM
Points: 772, Visits: 1,185
Personally, thus far, I find SQL 2005's GUI for Log Shipping pretty good and easy to use

for your script, the TARGET server/DB are always in NO RECOVERY? so it's more Mirroring than Log Shipping
as with SQL 2005 Log Shipping, the TARGET server/DB is READ-ONLY



SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #493614
Posted Thursday, May 1, 2008 10:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 1, 2010 2:40 PM
Points: 6, Visits: 40
So this solution would not be a good solution for someone looking for a reporting server since it does not A) terminate all connections to the database prior to applying the logs and b) Applys the logs with the NO RECOVERY option instead of the READ ONLY option? We are running SQL 2000 SE and we are looking for an alternative to upgrading to Enterprise edition in order to implement Log Shipping or something similar to have a reporting server.
Post #493785
Posted Thursday, May 1, 2008 1:05 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
Andy Leffler (5/1/2008)
So this solution would not be a good solution for someone looking for a reporting server since it does not A) terminate all connections to the database prior to applying the logs and b) Applys the logs with the NO RECOVERY option instead of the READ ONLY option? We are running SQL 2000 SE and we are looking for an alternative to upgrading to Enterprise edition in order to implement Log Shipping or something similar to have a reporting server.


Log shipping will NOT serve you as a reporting server. You may want to consider Replication for that ;)



* Noel
Post #493858
Posted Thursday, May 1, 2008 1:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 1, 2010 2:40 PM
Points: 6, Visits: 40
Noeld, why specifically do you say that Log Shipping will not work to establish a reporting server?
Post #493886
Posted Friday, May 2, 2008 4:37 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 5,353, Visits: 1,389
Good solution. I am not into log shipping. I just tried it 1-2 times so I can't comment much into this. But I like the custom solution.


Post #494113
Posted Wednesday, May 7, 2008 9:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:45 AM
Points: 44, Visits: 33
Actually we use log shipping for establishing a reporting database but there is an issue that we need one reporting base updated once a day and another one hourly. The problem comes that these bases are with same names in SQL 2005 log shipping wizzard and now they are in two different servers. Is there simple sollution to put them on one server with different names ie to put somewhere with move clause?
Post #496436
Posted Friday, June 6, 2008 8:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 12:25 PM
Points: 6, Visits: 38
I have instituted this process for building a framework to migrate a production database from SQL2000 to SQL2005 without interupting production. Allowing us to test apps and connections 'offline' and then cutover at a planned point in time with a final transaction log restore.

Conceptually, this process is a perfect fit. In practice, everything appears to be working except for the last step. The 'DataMirror' is stuck in restoring mode. Is this what should happen? The dbDataMirror database indicates the bak was verified and applied, then the subsequent trn files. The job fires off every set interval. Why would the destination db still be restoring?

Any help would be appreciated.

Thanks
-Chris
Post #512926
Posted Tuesday, June 24, 2008 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 8:58 AM
Points: 6, Visits: 20
[b]cstrabley Conceptually, this process is a perfect fit. In practice, everything appears to be working except for the last step. The 'DataMirror' is stuck in restoring mode. Is this what should happen? The dbDataMirror database indicates the bak was verified and applied, then the subsequent trn files. The job fires off every set interval. Why would the destination db still be restoring?


Chris,

Did you ever figure out what was happening with that last step?

I am about to implement this solution for a warm backup of our production database. Also - what interval is appropriate to run the big job on the backup server? Ideally - I wouldn't want to be more than 15 minutes behind the actual production box if we had to cut over to the warm backup...so is this realistic:

1. Full backup at the top of each hour
2. Trans log backup every 15 minutes
3. Run complete restore job (Step 3 script) at like :50 minute mark each hour

Or am I misunderstanding this script? Can I set it to run every 16 minutes so it just restores each new transaction log to the already "ready" database.

A bit confused...any help would be appreciated.

Cheers!

Bruce
Post #522585
Posted Wednesday, June 25, 2008 11:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 31, 2014 1:03 AM
Points: 215, Visits: 428
Hi Chris,

The solution is supposed to apply the logs in NO RECOVERY mode so that more logs can be continuously applied automatically. The last step of actually bringing the database online is a manual one and has been purposefully kept so. My thought was that in normal circumstances the logs would be continuously applied. And in case of a disaster, the DBA would verify the entire environment and then decide to bring the standby DB online after stopping the DataMirror jobs.

In order to bring your database online after all logs have been applied, you can consider adding the below statement just above the line which says "lblEndOfLoop:" which is the label for checking errors. That would be on line number 155.

"RESTORE DATABASE @strDatabase WITH RECOVERY"

So, immediately after looping through all logs and applying them, if you could add this line in the script, your database would get back online.

One note though. Next time your DataMirror jobs run, the first database file to be applied needs to be a full backup and not a transaction log since your database would be online. So you may need to verify if this works with your setup.

Also, if you are planning to connect and use the standby server actively and would have apps connecting through the day, you may run into a scenario where there are connections open when the next run of the DataMirror jobs start. You would then need a way to kill open connections to the DB. For that you may want to look at the following script from the Script library:

http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30024/

Thanks,

Girish
Post #523881
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse