Log Shipping vs Replication

I am seting up log shipping from a production SQL 2000 Enterprise server to a
similar server in our Disaster Recovery Site.
Various problems with log shipping have forced to rethink its use. I was
wondering how replication stacks up against Log Shipping, especially in the
areas of fail over and availability. I would want to cut over to the SQL
Server in the DR site in case of problems occuring. The two sites have a high
speed (~100 Megabit) link between them.
Thanks.
Sam
Log Shipping "replicates" the entire database as well as system objects,
permissions and database users.
While applying the logs the users cannot access the destination database.
Log Shipping's latency is at best 1 minute, assuming 0 s to copy the dumped
transaction log, and 0 seconds to apply it. In practice the latency is
around 2 minutes.
Log Shipping requires that the destination database be read only.
Transactional replication allows you to select which objects you wish to
replicate, and you can filter these objects by column or by row. Each table
object you replicate must have a PK.
Latency is at best a couple of seconds (setting the PollingInterval to 1s on
both the log reader and distribution agent), however underload you will
probably see a latency of 1 - 2 minutes.
With Transactional Replication users can access and update the subscription
database.
Paul Ibison has written an article on the differences which I can't locate
right now. I think its on SQL Server Central.
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:D0A5697C-6290-457C-8FC6-40FF814F409C@.microsoft.com...
> I am seting up log shipping from a production SQL 2000 Enterprise server
to a
> similar server in our Disaster Recovery Site.
> Various problems with log shipping have forced to rethink its use. I was
> wondering how replication stacks up against Log Shipping, especially in
the
> areas of fail over and availability. I would want to cut over to the SQL
> Server in the DR site in case of problems occuring. The two sites have a
high
> speed (~100 Megabit) link between them.
> Thanks.
> Sam
|||Sam,
I did a comparison of the functionality of these 2 methods on
sqlservercentral.com - it's also in the articles section on
www.replicationanswers.com. The article concentrates on the functional
differences, as they are not identical or equivalent. Although not exactly
what you are asking for it might still be useful.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hillary - thank you for your comments and information.
Paul - thank you for pointing out the articles on both web sites. I think I
will be purchasing your book before I do any real work on it.
Sam
"Paul Ibison" wrote:

> Sam,
> I did a comparison of the functionality of these 2 methods on
> sqlservercentral.com - it's also in the articles section on
> www.replicationanswers.com. The article concentrates on the functional
> differences, as they are not identical or equivalent. Although not exactly
> what you are asking for it might still be useful.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Sam,
I wish I'd written it, but Hilary is the author
Rgds,
Paul
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:AEF16260-42B1-469E-AC30-F55C059C8536@.microsoft.com...
> Hillary - thank you for your comments and information.
> Paul - thank you for pointing out the articles on both web sites. I think
I[vbcol=seagreen]
> will be purchasing your book before I do any real work on it.
> Sam
> "Paul Ibison" wrote:
exactly[vbcol=seagreen]
sql

Log shipping recovery trials

We have a Production Server A thats log shipped to Standby Server B. We want
to simulate some disaster recovery runs .
What are some things to consider. It seems obtainable from A to B.. But how
do I revert back to A again.. Do I have to redo log shipping and this time
from B to A which means I need to restore all dbs/logs in norecovery state
onto A from B using the wizard
Are there better ways to simulate this ? I dont wish for it to be a
nightmare especially since if we are going to test this and we would want to
revert back to A as soon as possible during this run...You don't have to set up reverse log-shipping. You simply backup B & restore
it on A & then re-initialise the log shipping process to continue testing..
Regards,
Greg Linwood
SQL Server MVP
"sql" <sql@.hotmail.com> wrote in message
news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> We have a Production Server A thats log shipped to Standby Server B. We
want
> to simulate some disaster recovery runs .
> What are some things to consider. It seems obtainable from A to B.. But
how
> do I revert back to A again.. Do I have to redo log shipping and this time
> from B to A which means I need to restore all dbs/logs in norecovery state
> onto A from B using the wizard
> Are there better ways to simulate this ? I dont wish for it to be a
> nightmare especially since if we are going to test this and we would want
to
> revert back to A as soon as possible during this run...
>
>|||When we fail to Standby Server B initially and simulate to run for 1 or 2
days before we recover Primary Server A, does that mean my downtime to
revert back to server A would be taking server B offline... doing a total
restore of all dbs to server A and then proceed. So my downtime is equal to
the amount of time it takes to restore the dbs as opposed to the initial
failover from A to B as the only process involved there is recovering the
dbs.. and other minor stuff such as logins,msdb related jobs and DTS
packages,sysmessages..assuming we are good to go here
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:O%23wVThA%23DHA.632@.TK2MSFTNGP12.phx.gbl...
> You don't have to set up reverse log-shipping. You simply backup B &
restore
> it on A & then re-initialise the log shipping process to continue
testing..
> Regards,
> Greg Linwood
> SQL Server MVP
> "sql" <sql@.hotmail.com> wrote in message
> news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> want
> how
time
state
want
> to
>|||Hi Hassan.
That's one way of doing it. Another is to do it in multiple steps - eg leave
B online whilst you perform your full backup restore to A (DTS packages &
whatever else), then restore the remaining log tails to A & your window of
downtime is fairly small. If you want to keep that downtime window even
smaller you could iterate the log tail restores.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23YHO71D%23DHA.132@.TK2MSFTNGP09.phx.gbl...
> When we fail to Standby Server B initially and simulate to run for 1 or 2
> days before we recover Primary Server A, does that mean my downtime to
> revert back to server A would be taking server B offline... doing a total
> restore of all dbs to server A and then proceed. So my downtime is equal
to
> the amount of time it takes to restore the dbs as opposed to the initial
> failover from A to B as the only process involved there is recovering the
> dbs.. and other minor stuff such as logins,msdb related jobs and DTS
> packages,sysmessages..assuming we are good to go here
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:O%23wVThA%23DHA.632@.TK2MSFTNGP12.phx.gbl...
> restore
> testing..
We
But
> time
> state
> want
>

Log shipping recovery trials

We have a Production Server A thats log shipped to Standby Server B. We want
to simulate some disaster recovery runs .
What are some things to consider. It seems obtainable from A to B.. But how
do I revert back to A again.. Do I have to redo log shipping and this time
from B to A which means I need to restore all dbs/logs in norecovery state
onto A from B using the wizard
Are there better ways to simulate this ? I dont wish for it to be a
nightmare especially since if we are going to test this and we would want to
revert back to A as soon as possible during this run...You don't have to set up reverse log-shipping. You simply backup B & restore
it on A & then re-initialise the log shipping process to continue testing..
Regards,
Greg Linwood
SQL Server MVP
"sql" <sql@.hotmail.com> wrote in message
news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> We have a Production Server A thats log shipped to Standby Server B. We
want
> to simulate some disaster recovery runs .
> What are some things to consider. It seems obtainable from A to B.. But
how
> do I revert back to A again.. Do I have to redo log shipping and this time
> from B to A which means I need to restore all dbs/logs in norecovery state
> onto A from B using the wizard
> Are there better ways to simulate this ? I dont wish for it to be a
> nightmare especially since if we are going to test this and we would want
to
> revert back to A as soon as possible during this run...
>
>|||When we fail to Standby Server B initially and simulate to run for 1 or 2
days before we recover Primary Server A, does that mean my downtime to
revert back to server A would be taking server B offline... doing a total
restore of all dbs to server A and then proceed. So my downtime is equal to
the amount of time it takes to restore the dbs as opposed to the initial
failover from A to B as the only process involved there is recovering the
dbs.. and other minor stuff such as logins,msdb related jobs and DTS
packages,sysmessages..assuming we are good to go here
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:O%23wVThA%23DHA.632@.TK2MSFTNGP12.phx.gbl...
> You don't have to set up reverse log-shipping. You simply backup B &
restore
> it on A & then re-initialise the log shipping process to continue
testing..
> Regards,
> Greg Linwood
> SQL Server MVP
> "sql" <sql@.hotmail.com> wrote in message
> news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> > We have a Production Server A thats log shipped to Standby Server B. We
> want
> > to simulate some disaster recovery runs .
> >
> > What are some things to consider. It seems obtainable from A to B.. But
> how
> > do I revert back to A again.. Do I have to redo log shipping and this
time
> > from B to A which means I need to restore all dbs/logs in norecovery
state
> > onto A from B using the wizard
> >
> > Are there better ways to simulate this ? I dont wish for it to be a
> > nightmare especially since if we are going to test this and we would
want
> to
> > revert back to A as soon as possible during this run...
> >
> >
> >
>|||Hi Hassan.
That's one way of doing it. Another is to do it in multiple steps - eg leave
B online whilst you perform your full backup restore to A (DTS packages &
whatever else), then restore the remaining log tails to A & your window of
downtime is fairly small. If you want to keep that downtime window even
smaller you could iterate the log tail restores.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23YHO71D%23DHA.132@.TK2MSFTNGP09.phx.gbl...
> When we fail to Standby Server B initially and simulate to run for 1 or 2
> days before we recover Primary Server A, does that mean my downtime to
> revert back to server A would be taking server B offline... doing a total
> restore of all dbs to server A and then proceed. So my downtime is equal
to
> the amount of time it takes to restore the dbs as opposed to the initial
> failover from A to B as the only process involved there is recovering the
> dbs.. and other minor stuff such as logins,msdb related jobs and DTS
> packages,sysmessages..assuming we are good to go here
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:O%23wVThA%23DHA.632@.TK2MSFTNGP12.phx.gbl...
> > You don't have to set up reverse log-shipping. You simply backup B &
> restore
> > it on A & then re-initialise the log shipping process to continue
> testing..
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "sql" <sql@.hotmail.com> wrote in message
> > news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> > > We have a Production Server A thats log shipped to Standby Server B.
We
> > want
> > > to simulate some disaster recovery runs .
> > >
> > > What are some things to consider. It seems obtainable from A to B..
But
> > how
> > > do I revert back to A again.. Do I have to redo log shipping and this
> time
> > > from B to A which means I need to restore all dbs/logs in norecovery
> state
> > > onto A from B using the wizard
> > >
> > > Are there better ways to simulate this ? I dont wish for it to be a
> > > nightmare especially since if we are going to test this and we would
> want
> > to
> > > revert back to A as soon as possible during this run...
> > >
> > >
> > >
> >
> >
>

Log Shipping Question

I have successfully setup log shipping between my production SQL 2000 Server
(setup in a Cluster) and my Disaster Recovery SQL 2000 Server (across a WAN)
What is the best way if any to stop the log shipping for a extended period
of time and re-enable it without setting up the entire maintenance plan
again. Is this possible? What happened is I needed to shut down my
production server due to hurricane Wilma as a precaution. I disabled the log
shipping jobs on both servers but didn't actually physicall shut down the
Disaster Recovery SQL 2000 Server.
The servers got out of synch...although it said the logs were being shipped
and they were working correctly after I turned the production server back on.
However, the log were not being copied to the Disaster Recovery Server.
Any help would be appreciated. Thanks!!
Hello mate,
The way I understand it is you could have just shut down your production
server without changing/stopping/disabling the log shipping jobs and just
bring it back online when your happy (the hurricane has passed!) and the
backup/copy and restores should resume as normal?
The shipping jobs seems very automated once started and syncronised - think
of it like a 'long reboot' of your production boxes. Your boxes aer fine
after bouncing them right - so whats the difference if they are down for a
few days?
I'm in the middle of documenting a similar setup with my production box (non
clustered) getting logs sent to the backup SQL box accross a WAN - the most
complex part of the exercise seems to be getting the backup DB up and running
post failure/disaster of the production box.
Hope this helps you mate.
Paul.
"foghorn69@.news.postalias" wrote:

> I have successfully setup log shipping between my production SQL 2000 Server
> (setup in a Cluster) and my Disaster Recovery SQL 2000 Server (across a WAN)
> What is the best way if any to stop the log shipping for a extended period
> of time and re-enable it without setting up the entire maintenance plan
> again. Is this possible? What happened is I needed to shut down my
> production server due to hurricane Wilma as a precaution. I disabled the log
> shipping jobs on both servers but didn't actually physicall shut down the
> Disaster Recovery SQL 2000 Server.
> The servers got out of synch...although it said the logs were being shipped
> and they were working correctly after I turned the production server back on.
> However, the log were not being copied to the Disaster Recovery Server.
> Any help would be appreciated. Thanks!!

Log Shipping Question

I have successfully setup log shipping between my production SQL 2000 Server
(setup in a Cluster) and my Disaster Recovery SQL 2000 Server (across a WAN)
What is the best way if any to stop the log shipping for a extended period
of time and re-enable it without setting up the entire maintenance plan
again. Is this possible? What happened is I needed to shut down my
production server due to hurricane Wilma as a precaution. I disabled the log
shipping jobs on both servers but didn't actually physicall shut down the
Disaster Recovery SQL 2000 Server.
The servers got out of synch...although it said the logs were being shipped
and they were working correctly after I turned the production server back on.
However, the log were not being copied to the Disaster Recovery Server.
Any help would be appreciated. Thanks!!
Hello,
Based on my experience, you shall remove and recreate log shipping for this
situation becaseu the expected backup on the primary servers were not
properly created due to server shutdown.
More related information:
329133.KB.EN-US INF: Troubleshooting SQL Server 2000 Log Shipping "Out of
Sync" Errors
http://support.microsoft.com/default...B;EN-US;329133
314515.KB.EN-US INF: Frequently Asked Questions - SQL Server 2000 - Log
Shipping
http://support.microsoft.com/default...B;EN-US;314515
SQL Server 2000 How To Setup Log Shipping
http://www.microsoft.com/downloads/d...&familyid=7395
ec1b-199f-42bc-a31b-2056adf73f94
http://www.microsoft.com/technet/tre...hnet/prodtechn
ol/sql/deploy/prodspecs/logship1.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Log Shipping Question
| thread-index: AcXmR7EoN/OMKH5UTPqRcRbKocEcOg==
| X-WBNR-Posting-Host: 66.20.1.9
| From: "=?Utf-8?B?Rm9naG9ybkxlZ2hvcm4=?=" <foghornleghorn@.nospam.postalias>
| Subject: Log Shipping Question
| Date: Thu, 10 Nov 2005 14:40:03 -0800
| Lines: 17
| Message-ID: <E1996FA4-68BA-4906-9886-D05F65119C1A@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:66533
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| I have successfully setup log shipping between my production SQL 2000
Server
| (setup in a Cluster) and my Disaster Recovery SQL 2000 Server (across a
WAN)
|
| What is the best way if any to stop the log shipping for a extended
period
| of time and re-enable it without setting up the entire maintenance plan
| again. Is this possible? What happened is I needed to shut down my
| production server due to hurricane Wilma as a precaution. I disabled the
log
| shipping jobs on both servers but didn't actually physicall shut down the
| Disaster Recovery SQL 2000 Server.
|
| The servers got out of synch...although it said the logs were being
shipped
| and they were working correctly after I turned the production server back
on.
| However, the log were not being copied to the Disaster Recovery Server.
|
| Any help would be appreciated. Thanks!!
|
|
|

Log Shipping or Replication?

We are researching various ways to create an offsite disaster recovery solution. We are talking about either Transactional Replication or Log Shipping. Which would you use?

Using:
SQL2000

Down time max of 4 hours.

My concerns are once a failure occurs and we are running production at the offsite facility how quickly could we get back to the primary cluster?

Thanks for any input... :DThe ever popular answer: It depends.

If cost control is the primary concern, log shipping is almost always cheaper and easier. If quick failover is the primary concern, transactional replication allows you to get failover down to minutes, maybe less. Depending on how you implement things, there can be more than a factor of ten difference in price.

This is one area where I would recommend nearly anyone hire a consultant. You need to have someone with considerable experience help you make these decisions, because you've got a lot riding on the outcome and it is an area that very few people really understand.

-PatP|||i'm a big fan of logshipping in these situations, mostly because as pat said it is cheap but also because its is an automated restore and very simple.

xact repl is a very good situation but the simplest way for you to see it's downside is pretty simple. search through this forum for replication and count how many questions there are then try log shipping, i think log shipping will have fewer due to it's simplicity and if we have learned anything, it is K.I.S.S.

now to get your five nines up consider hardware clustering with log shipping.
you will have immediate uptime with parallel data at all times.|||OK, I have to bite!

How can Log Shipping be cheaper than replication? IT'S THE OPPOSITE!!! For replication the minimum requirement is SQL Standard and a connection (doesn't even need to be a network connection, it can be a dial up). For Log Shipping you HAVE TO HAVE EE ON BOTH BOXES, unless you script your own "custom log shipping". But then you can't call it "Log Shipping" because the participants do not have a knowledge of server roles as they do in EE environment.

To answer the question, I'd say if money is an issue, then write your own transaction backup/restore process that will keep the servers semi-synched with a lag equal to the scheduled frequency.

And Scott, the number of questions on replication is not indicative of it's simplicity or complexity. It demonstrates poor planning and lack of experience with the technology.|||When you are looking at DR, you have to scale the whole system (including the infrastructure) to support the largest possible load. That means buying a LOT of network "pipe" to move your data or a truly massive distributor (a separate box from the production database to avoid single point of failure). By the time you buy the third box, its licenses, and the "pipe" (which is an ongoing monthly expense) replication is usually considerably more expensive than log shipping.

The answer still boils down to "it depends" in my mind, due to the number of possible influences, many of which are non-technical in nature.

-PatP|||Now you really contradicted yourself. In the first post you said that ...log shipping is almost always cheaper and easier...while now you're saying that replication is the way to go...Is it Kerry's influence from last night debate?

Here's more food for thought in regards to non-technical aspect of the dilema (I didn't have MS pricing handy, so here's their comparison with Horacle spin):

http://www.microsoft.com/sql/evaluation/compare/pricecomparison.asp

10K (2CPU SE) vs. 40K (2CPU EE)

Which one? Decisions, decisions...|||Now you really contradicted yourself. In the first post you said that while now you're saying that replication is the way to go...Is it Kerry's influence from last night debate?Would you care to reevaluate that observation just a wee bit? Unless I missed my own point (which I suppose is possible), replication offers lots of benefits, but at (potentially) much higher cost. At least I think that I said the same thing both times.

-PatP|||i agree replicatioin has a much higher cost in man hours.
my point in the earlier post about how many questions are for repl vs logship was designed to highlight the common knowledge about repl that we all have.
It aint always the easiest of db technologies to implement. occasionally you get lucky where it sets up textbook and you will never have any issues with it. but then there is the other 90% of the time.

by the way rdjabarov , I cant believe i am agreeing with pat. i feel so john mccain right now. :eek:|||"I just love this country!" (has to be heavily accented - RUSSIAN)

If cost control is the primary concern, log shipping is almost always cheaper and easier.You guys rock! ...But please stick to the principles, don't try to make a "quick win", and we'll all be happy, I promise!!!|||I guess I forgot to "reevaluate my observation", huh?

Log Shipping will require EE on both boxes. Assuming each box has at least 2CPUs, that's $40K per box. If the current prod server is running EE already, then the number above is the minimum non-hardware investment required to implement Log Shipping.

For warm stand-by solution or DR site (which is pretty much the same thing) I am not even recommending replication because it's not what it was designed for to begin with. I think writing a couple of scripts (literally a couple) while having even MSDE edition is all that is needed.

I started arguing with everybody here because I thought that suggestions made did not target the requirement presented by RobS.We are researching various ways to create an offsite disaster recovery solution. We are talking about either Transactional Replication or Log Shipping. Which would you use?|||Ok here we go. More things I thought about after reading your all's posts and doing more thinking over the weekend.

What happens if Host A goes down (internet connectivity) and we have to switch to Host B (DR Site). No need to worry about how that is done right now. My question is now which would be the easiest to get Host A back in sync?

My thought, expense has not yet been budgeted so we'll go with Money is not an obstacle at the moment.

Using replciation, setup a distribution server at Host C. Use Bi-Directional Replication so when Host A goes down Host B is up to date. The Host C (Distribution Server) is queing active transactions waiting for Host A to come back online. Once Host A is back online Host C should bring it back in Sync continuing to sync Host A while Host B is being used for production. At night (Maintenance Window) everything is then switched back to Host A with minimal downtime.

During normal operations (Host A) Host B would not be doing anything so there would be no transactions coming down the pipe to Host A.

I see replication being the quickest way to get Host A back online if a failure occurs. This entire system would have to be babysat I know. Are my thoughts flawed? :D Be kind... ;)|||My $0.02 worth since I had to maintain replicated databases for over 2 1/2 years. The replicated database(s) cannot be fully functional at once if the application(s) relies (rely) on identity columns in any table, because the value if the identity column is replicated and the receiving table has to accept that value and not try to create it's own identity value.

RobS ... as you can see, there are pro and con for each method. I personally like log shipping, even if home grown (ala BORK 4.5 for SQL 7).

And of course that brings back to the never ending debate of surrogate keys vs. natural keys :eek:|||There is one aspect of replication that I do not like. When you go to change a table (add/drop a column) or add a new column, there is manual intervention involved with getting the new data added to the publication. Admittedly, this does not happen a lot in most shops, but it happens just often enough to bite you in a sensitive spot.

The other aspect that I never liked may have been fixed. Has anyone played around with replicating stored procedures and views in SQL 2000?

If you can, I would go with the Log Shipping. In order to fail back to your regular systems, you would have to set up log shipping in "reverse" for the time it takes to get the regular system back on its feet data-wise. This could take a lot of time behind the scenes with no backup server in place, depending on how large the database is.|||For DR solution there 2 proven methods that have been tested and retested for the past 5 years by many companies: Log Shipping (or a custom backup/restore), and HW-based replication, like EMC or other giants of proprietary technologies. Caveats mentioned by the previous 2 posts clearly demonstrate that replication is not a solution for DR, because it's not what it is designed for.

Log Shipping functionality questions

SQL2K sp3
I need to have a basic understanding of LS in order to
answer some Disaster Recovery questions today. I
understand that it takes TLog backups from BoxA(Production
Server in use) and restores them to BoxB(secondary server
that those backups are being sent too) using NoRecovery. I
also understand that if BoxA dies BoxB can perform a Role
Change and become the Production Box. This part Im clear
on.
But what Im not clear on once BoxA is back online what
takes place? Im pretty sure you can do another Role Change
make BoxA the Production Box again. But what actually
takes place here?
Does BoxA go through a Restore process of all its old
backups using NoRecovery, have the latest TLog backups
shipped from BoxB, restore those with Recovery, and then
become Production again? This is what Im guessing would
need to happen in order to accomplish a Role Change from
BoxB to BoxA. But Im not sure and could use some insight.
Of course I JUST found out I need to know this stuff
IMMEDIATELY from the higher ups or else I would just try
testing this out on my own. So sorry for the lazy
questions here.
TIA, ChrisRfull backup of B applied to A. Then apply Trnxn Logs From B To A and A is
"HOT" again.
not sure if that answers your question.
are you planning on using the maintenance plan stuff...aka "Log shipping
Wizard" ?
Greg Jackson
PDX, Oregon|||>full backup of B applied to A. Then apply Trnxn Logs From
B To A and A is
>"HOT" again.
So I actually have to do a full transfer over the line
from B to A? Is this correct? How do companies with large
db's use this as an Enterprise solution? Im not trying to
sound ungrateful for your help, just trying to get a clear
understanding.
>are you planning on using the maintenance plan
stuff...aka "Log shipping
>Wizard" ?
Yes.
>--Original Message--
>full backup of B applied to A. Then apply Trnxn Logs From
B To A and A is
>"HOT" again.
>
>not sure if that answers your question.
>are you planning on using the maintenance plan
stuff...aka "Log shipping
>Wizard" ?
>
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||it aint easy.
moving data of the wire SUX. Especially for large Dbs.
this is really a "Poor Man's Replication" so larger companies probably just
use replication.
I have done tons of this in the past and am doing a huge log shipping
implementation right now.
I recommend NOT using the Wizard Thingy.
Roll your own.
if you need scripts, have questions, etc let me know.
GAJ|||Thanks for the info Jaxon.
>--Original Message--
>it aint easy.
>moving data of the wire SUX. Especially for large Dbs.
>this is really a "Poor Man's Replication" so larger
companies probably just
>use replication.
>I have done tons of this in the past and am doing a huge
log shipping
>implementation right now.
>I recommend NOT using the Wizard Thingy.
>Roll your own.
>if you need scripts, have questions, etc let me know.
>
>GAJ
>
>.
>

Log Shipping functionality questions

SQL2K sp3
I need to have a basic understanding of LS in order to
answer some Disaster Recovery questions today. I
understand that it takes TLog backups from BoxA(Production
Server in use) and restores them to BoxB(secondary server
that those backups are being sent too) using NoRecovery. I
also understand that if BoxA dies BoxB can perform a Role
Change and become the Production Box. This part Im clear
on.
But what Im not clear on once BoxA is back online what
takes place? Im pretty sure you can do another Role Change
make BoxA the Production Box again. But what actually
takes place here?
Does BoxA go through a Restore process of all its old
backups using NoRecovery, have the latest TLog backups
shipped from BoxB, restore those with Recovery, and then
become Production again? This is what Im guessing would
need to happen in order to accomplish a Role Change from
BoxB to BoxA. But Im not sure and could use some insight.
Of course I JUST found out I need to know this stuff
IMMEDIATELY from the higher ups or else I would just try
testing this out on my own. So sorry for the lazy
questions here.
TIA, ChrisRfull backup of B applied to A. Then apply Trnxn Logs From B To A and A is
"HOT" again.
not sure if that answers your question.
are you planning on using the maintenance plan stuff...aka "Log shipping
Wizard" ?
Greg Jackson
PDX, Oregon|||>full backup of B applied to A. Then apply Trnxn Logs From
B To A and A is
>"HOT" again.
So I actually have to do a full transfer over the line
from B to A? Is this correct? How do companies with large
db's use this as an Enterprise solution? Im not trying to
sound ungrateful for your help, just trying to get a clear
understanding.

>are you planning on using the maintenance plan
stuff...aka "Log shipping
>Wizard" ?
Yes.

>--Original Message--
>full backup of B applied to A. Then apply Trnxn Logs From
B To A and A is
>"HOT" again.
>
>not sure if that answers your question.
>are you planning on using the maintenance plan
stuff...aka "Log shipping
>Wizard" ?
>
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||it aint easy.
moving data of the wire SUX. Especially for large Dbs.
this is really a "Poor Man's Replication" so larger companies probably just
use replication.
I have done tons of this in the past and am doing a huge log shipping
implementation right now.
I recommend NOT using the Wizard Thingy.
Roll your own.
if you need scripts, have questions, etc let me know.
GAJ|||Thanks for the info Jaxon.

>--Original Message--
>it aint easy.
>moving data of the wire SUX. Especially for large Dbs.
>this is really a "Poor Man's Replication" so larger
companies probably just
>use replication.
>I have done tons of this in the past and am doing a huge
log shipping
>implementation right now.
>I recommend NOT using the Wizard Thingy.
>Roll your own.
>if you need scripts, have questions, etc let me know.
>
>GAJ
>
>.
>

Log Shipping functionality questions

SQL2K sp3
I need to have a basic understanding of LS in order to
answer some Disaster Recovery questions today. I
understand that it takes TLog backups from BoxA(Production
Server in use) and restores them to BoxB(secondary server
that those backups are being sent too) using NoRecovery. I
also understand that if BoxA dies BoxB can perform a Role
Change and become the Production Box. This part Im clear
on.
But what Im not clear on once BoxA is back online what
takes place? Im pretty sure you can do another Role Change
make BoxA the Production Box again. But what actually
takes place here?
Does BoxA go through a Restore process of all its old
backups using NoRecovery, have the latest TLog backups
shipped from BoxB, restore those with Recovery, and then
become Production again? This is what Im guessing would
need to happen in order to accomplish a Role Change from
BoxB to BoxA. But Im not sure and could use some insight.
Of course I JUST found out I need to know this stuff
IMMEDIATELY from the higher ups or else I would just try
testing this out on my own. So sorry for the lazy
questions here.
TIA, ChrisR
full backup of B applied to A. Then apply Trnxn Logs From B To A and A is
"HOT" again.
not sure if that answers your question.
are you planning on using the maintenance plan stuff...aka "Log shipping
Wizard" ?
Greg Jackson
PDX, Oregon
|||>full backup of B applied to A. Then apply Trnxn Logs From
B To A and A is
>"HOT" again.
So I actually have to do a full transfer over the line
from B to A? Is this correct? How do companies with large
db's use this as an Enterprise solution? Im not trying to
sound ungrateful for your help, just trying to get a clear
understanding.

>are you planning on using the maintenance plan
stuff...aka "Log shipping
>Wizard" ?
Yes.

>--Original Message--
>full backup of B applied to A. Then apply Trnxn Logs From
B To A and A is
>"HOT" again.
>
>not sure if that answers your question.
>are you planning on using the maintenance plan
stuff...aka "Log shipping
>Wizard" ?
>
>
>Greg Jackson
>PDX, Oregon
>
>.
>
|||it aint easy.
moving data of the wire SUX. Especially for large Dbs.
this is really a "Poor Man's Replication" so larger companies probably just
use replication.
I have done tons of this in the past and am doing a huge log shipping
implementation right now.
I recommend NOT using the Wizard Thingy.
Roll your own.
if you need scripts, have questions, etc let me know.
GAJ
|||Thanks for the info Jaxon.

>--Original Message--
>it aint easy.
>moving data of the wire SUX. Especially for large Dbs.
>this is really a "Poor Man's Replication" so larger
companies probably just
>use replication.
>I have done tons of this in the past and am doing a huge
log shipping
>implementation right now.
>I recommend NOT using the Wizard Thingy.
>Roll your own.
>if you need scripts, have questions, etc let me know.
>
>GAJ
>
>.
>

log shipping encryption

I have a few questions that are bugging me, being a newbie to SQL 2000
Enterprise. I want to set up a warm site server for disaster recovery of my
production server at a remote location. Both Sql servers will be behind
firewalls, (if that matters) and the remote site will not be a production
server; only receiving the shipped transaction logs and being updated
accordingly. For budgetary reasons, they are Internet attached via T1.
1. Is this setup possible?
2. If so, will SQL2000 allow me to encrypt the data it is shipping?
3. Why can't the Chiefs find a defense that will hold the other team to
less than 35 points so I can get rid of my ulcer?
Hoping for all three answers, but hey...2 out of 3 is still a good average.
Thanks in advance.
Hi
The setup is possible.
SQL does not support encryption at transport level, that function belongs to
the OS or network stack. You could connect the 2 servers via Windows VPN.
Setup the dial-up VPN, originating from the local server, to use the network
cards, with no-hangup, and auto-retry.
The VPN then can run compression (big benefit as most log files can be
compressed by up to 70%) and encryption.
Windows VPN is very efficent and is great for security needs like yours.
Regards
Mike
"Rod Hurley" wrote:

> I have a few questions that are bugging me, being a newbie to SQL 2000
> Enterprise. I want to set up a warm site server for disaster recovery of my
> production server at a remote location. Both Sql servers will be behind
> firewalls, (if that matters) and the remote site will not be a production
> server; only receiving the shipped transaction logs and being updated
> accordingly. For budgetary reasons, they are Internet attached via T1.
> 1. Is this setup possible?
> 2. If so, will SQL2000 allow me to encrypt the data it is shipping?
> 3. Why can't the Chiefs find a defense that will hold the other team to
> less than 35 points so I can get rid of my ulcer?
> Hoping for all three answers, but hey...2 out of 3 is still a good average.
> Thanks in advance.

Log shipping and Replication

I am documenting our disaster recovery plans and I was wondering if anyone
has any experience with failing over to a log shipping secondary in a
replicated environment.
If I log ship all user databases, and msdb, and distribution to a secondary
server, all to an exact point in time (after disabling all agent jobs), will
replication work on the secondary box? What will break?
I'm thinking that as long as the CNAME is updated so the DNS servers point
to the new box, and the SYSSERVERS table is updated with the new LOCAL server
name, and the account that starts the SQL Server and owns all the replication
jobs is the same domain account, all should be good.
It just sounds too good to be true!
The publisher servername is hardcoded into several system replication
tables. You could try renaming the standby server after failover and
renaming the old server, then updating the sysservers table - this should
work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||It is too good to be true. Basically what you have to do is enable sync with
backup on your publication database.
Then ship your msdb, distribution, published database to the standby server.
What will happen is your log reader will only read from the log before it
dumps which increases the latency. Now when you failover, you rename the
standby server with the name of the primary and it should pick up where it
left off if you enable the continue on data consistency profile. You may
need to issue sp_repldone incase the log reader is ahead of the distribution
database on the standby. This is somewhat documented in SQL 2000 BOL.
There are more options in SQL 2005. For example with a remote distributor
and if you use the PublisherFailoverPartner.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
news:16FD2787-A5C5-4492-8185-43BF85EB3380@.microsoft.com...
>I am documenting our disaster recovery plans and I was wondering if anyone
> has any experience with failing over to a log shipping secondary in a
> replicated environment.
> If I log ship all user databases, and msdb, and distribution to a
> secondary
> server, all to an exact point in time (after disabling all agent jobs),
> will
> replication work on the secondary box? What will break?
> I'm thinking that as long as the CNAME is updated so the DNS servers point
> to the new box, and the SYSSERVERS table is updated with the new LOCAL
> server
> name, and the account that starts the SQL Server and owns all the
> replication
> jobs is the same domain account, all should be good.
> It just sounds too good to be true!

log shipping and replication

Ive got my publishers and distributors on the same server say Server A . We
have implemented log shipping as Disaster Recovery solution.
Ive got my published databases log shipped to Server B.
My questions are : Do I need to log ship the distribution database as well ?
How can I recover from this scenario should Server A fail ?
I have read Books Online on Replication and Log shipping.. We cannot
implement the " sync with backup " option because of the increased latency.
It also talks about the semi-synchronous mode. I havent really followed it
for the fact that the distribution database is on the same server as the
publisher and currently we are not log shipping that.
Can someone guide me here on how to recover based upon my settings ? Using
SQL 2000
Thanks
Hassan,
In my experience when replication databases are restored onto another server
than the original, restoration is only partially successful using
KEEP_REPLICATION, and on failover it was not possible to rename the server
then start synchronizing - I found it easiest to recreate the publications
from scratch. However if you restore to Server A itself (or another "Server
A") then it should be ok.
The "sync with backup" option is to ensure that transactions don't get into
the distribution database before the publication database is backed up,
otherwise the distribution database backup could be ahead of the publication
backup. You can achieve the same effect by making sure that the restore of
the publication database and logs is older than the restore of the
distribution database - then you have essentially performed a synchronous
mode backup. In your case you will need a distribution backup, as regular as
possible.
To see the order of restores if Server A goes down, have a look in BOL at
"replication, backup and restore operations".
hth,
Paul Ibison