Welcome to dbFreaks.com!
FAQFAQ      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Best Practices for Log Shipping

 
   Database Help (Home) -> Replication RSS
Next:  Brendan Reynolds rounding module  
Author Message
Larry Epn

External


Since: Aug 05, 2008
Posts: 2



(Msg. 1) Posted: Sat Oct 11, 2008 4:24 am
Post subject: Best Practices for Log Shipping
Archived from groups: microsoft>public>sqlserver>replication (more info?)

We have a 35Gb db that grows about 25% per year. Luckily, we just put it on
new iron that has speeded it up greatly. We have only ever backed this up
once daily at night when no users were on, with "simple" recovery mode. Good
fortune that we have not had a failure in six years. Bad luck would have had
us lose a full day's work if catastrophe struck. Must be divine protection.
Now that we have the new iron, we're looking at using the old server as a
log-shipping secondary server. Assume both new and old server are adequate
hardware to support the load; new server = win2008svr, sql2008std; old server
= win2003ent_sp2, sql2008std.
I have used some articles to "learn" how to do this. Oh yeah, transactional
replication is not an option due to absence of primary keys on many tables.
So, assuming I am able to make my way through the online samples to do this
setup (which I have done and appears to be working since I can see changes
occurring in the read-only secondary database), I now have several questions:
1. Now that the primary server database is set as "full recovery" mode, and
the tran logs are being shipped successfully to the secondary server, can I
still make full backups of the primary database without screwing up the
transaction log truncation? In other words, does the act of doing a separate
backup mess up the transaction log backups running as part of the log
shipping jobs? If I can't make backups of the primary database, should I
just backup the secondary database late at night to get the "once-a-day"
backup?
2. What are the basic steps for manually failing over to the secondary
server if/when the primary server croaks? Of course, redirecting the client
apps is necessary, but I'm confused with the changes I have to make on the
secondary server in SQL, particularly with changing it from standby to
active, etc.
3. Once the primary server has failed, the secondary server has become the
production active server, and then the primary server is fixed, what must I
do to go back to the original primary server? I assume I wait until there is
no activity and restore a backup taken from the secondary (active) server and
restor it onto the primary server, redirect the clients, etc...but is there
anything I need to do within the log shipping setup/jobs?
4. Can you direct me to documents that explain the big picture particularly
well, and don't just contain step-by-step examples on basic setup? I'm not a
DBA but have been "the dba" by default in many small environments, so I have
somewhat of a clue, but don't live the details day-by-day.
Thanks in advance!
Larry

 >> Stay informed about: Best Practices for Log Shipping 
Back to top
Login to vote
Paul Ibison

External


Since: Oct 03, 2008
Posts: 104



(Msg. 2) Posted: Sun Oct 12, 2008 3:41 am
Post subject: RE: Best Practices for Log Shipping [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

For the first part, the safest option is to do a copy-only backup:
http://msdn.microsoft.com/en-us/library/ms191495.aspx
Controlled failovers and failbacks are explained here:
http://msdn.microsoft.com/en-us/library/cc645954.aspx
For more general info, I'd use BOL or Ron Talmage's original article:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
It is a setup article but each step is explained with background info so you
should gain a good understanding this way.
HTH,
Paul Ibison (www.replicationanswers.com)

 >> Stay informed about: Best Practices for Log Shipping 
Back to top
Login to vote
FM

External


Since: Oct 14, 2008
Posts: 25



(Msg. 3) Posted: Tue Oct 14, 2008 4:59 am
Post subject: RE: Best Practices for Log Shipping [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hello Larry,

Paul has provided indeed very good link to resolve your query about Log
Shipping.

I would like to go 1 step ahead about your 1st questions:
Though Log Shipping happening successfully; Considering Desastar recovery in
mind Full & Tlog backup must available of the Primary Database. Yes you must
do the Full Database backup which do not disturb your tlog backup on primary.
The reason is when you are doing recovery or point in time recovery you must
need the Full database backup and subsequent tlog backups.

Hope this may help you.


"Larry Epn" wrote:

> We have a 35Gb db that grows about 25% per year. Luckily, we just put it on
> new iron that has speeded it up greatly. We have only ever backed this up
> once daily at night when no users were on, with "simple" recovery mode. Good
> fortune that we have not had a failure in six years. Bad luck would have had
> us lose a full day's work if catastrophe struck. Must be divine protection.
> Now that we have the new iron, we're looking at using the old server as a
> log-shipping secondary server. Assume both new and old server are adequate
> hardware to support the load; new server = win2008svr, sql2008std; old server
> = win2003ent_sp2, sql2008std.
> I have used some articles to "learn" how to do this. Oh yeah, transactional
> replication is not an option due to absence of primary keys on many tables.
> So, assuming I am able to make my way through the online samples to do this
> setup (which I have done and appears to be working since I can see changes
> occurring in the read-only secondary database), I now have several questions:
> 1. Now that the primary server database is set as "full recovery" mode, and
> the tran logs are being shipped successfully to the secondary server, can I
> still make full backups of the primary database without screwing up the
> transaction log truncation? In other words, does the act of doing a separate
> backup mess up the transaction log backups running as part of the log
> shipping jobs? If I can't make backups of the primary database, should I
> just backup the secondary database late at night to get the "once-a-day"
> backup?
> 2. What are the basic steps for manually failing over to the secondary
> server if/when the primary server croaks? Of course, redirecting the client
> apps is necessary, but I'm confused with the changes I have to make on the
> secondary server in SQL, particularly with changing it from standby to
> active, etc.
> 3. Once the primary server has failed, the secondary server has become the
> production active server, and then the primary server is fixed, what must I
> do to go back to the original primary server? I assume I wait until there is
> no activity and restore a backup taken from the secondary (active) server and
> restor it onto the primary server, redirect the clients, etc...but is there
> anything I need to do within the log shipping setup/jobs?
> 4. Can you direct me to documents that explain the big picture particularly
> well, and don't just contain step-by-step examples on basic setup? I'm not a
> DBA but have been "the dba" by default in many small environments, so I have
> somewhat of a clue, but don't live the details day-by-day.
> Thanks in advance!
> Larry
 >> Stay informed about: Best Practices for Log Shipping 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
reinitializing log shipping - I have a log shipping database maintenance plan that has stopped working. Server 2000 on SQL server 2000. The problem is that since the job stopped (b/c of a password change on the server) we have deleted many of the logs. It has been stopped for..

Log Shipping - Hi, I have been struggling to implement log shipping for about 2 months and I am about to give up. The problem that I am facing is that our database is about 60 GB and every time I set up log shipping it works fine till the weekend where we have..

log shipping - SQL server 2000 log shipping was working fine. All service packs up-to-date. Suddenly, my standby database decided it was "loading/suspect". I removed all log shipping, but I couldn't do anything with the standby. I couldn't even delete i...

Log Shipping Question - While reviewing our log shipping setup, I noticed we backup, ship and restore logs from 7am-7pm. We also perform a full backup at 9pm. My question is this: Will the transaction backup at 7am pick up transactions since the last transaction log backup...

Log Shipping ReInit - how to? - We have setup Log Shipping in SQL 2005. Working flawlessly. It appears that only tran log backup will occur forever unless we intervene. I do want a full backup to happen, you know, eventually... What is best way to automate this? Ideally, a full..
   Database Help (Home) -> Replication All times are: Pacific Time (US & Canada) (change)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]