Log Shipping with HOLD

I had an interview question the other day I don't understand. He asked about
types of replication and when you would use them. One of the options was "log
shipping with hold" and why?
I can't think of a good reason to do this, as with that type of replication,
you would want the replicant/subscriber to be as close as possible to the
primary/publisher. Otherwise, why not just use snapshot?
Thoughts please.
JayKon wrote:
> I had an interview question the other day I don't understand. He asked about
> types of replication and when you would use them. One of the options was "log
> shipping with hold" and why?
> I can't think of a good reason to do this, as with that type of replication,
> you would want the replicant/subscriber to be as close as possible to the
> primary/publisher. Otherwise, why not just use snapshot?
> Thoughts please.
For a standby or "disaster recovery" server, having a delay in the
restore process can help protect from logical disasters, i.e. somebody
nukes a million rows in a table by accident. You have time to prevent
that mistake from reaching your standby machine in case you need it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||My first reaction is "DOH!", that makes sense. But after that, I wonder if
thats really such a good idea. After all, you can still do a point-in-time
restore, or even restore to a seperate DB and manually extract the rows to
recover.
Anyway, thanks Tracy, I should have thought of that.
"Tracy McKibben" wrote:

> JayKon wrote:
> For a standby or "disaster recovery" server, having a delay in the
> restore process can help protect from logical disasters, i.e. somebody
> nukes a million rows in a table by accident. You have time to prevent
> that mistake from reaching your standby machine in case you need it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:B04C4FDD-89F3-468E-8626-B335D964D61F@.microsoft.com...
> My first reaction is "DOH!", that makes sense. But after that, I wonder if
> thats really such a good idea. After all, you can still do a point-in-time
> restore, or even restore to a seperate DB and manually extract the rows to
> recover.
>
Also,
Using Secondary Servers for Query Processing
http://msdn2.microsoft.com/en-us/library/ms189572.aspx
David
|||JayKon wrote:
> My first reaction is "DOH!", that makes sense. But after that, I wonder if
> thats really such a good idea. After all, you can still do a point-in-time
> restore, or even restore to a seperate DB and manually extract the rows to
> recover.
>
All true, but if you're in a low- or no-downtime environment, doing
weekly full/hourly log backups, with a two-hour delay in log shipping,
if something happens on Thursday, will it take longer to restore the
full and 4 days of logs, or two hours of logs (up to the failure)?
It's just another tool, another way of protecting yourself...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||David, the question was not about uses of replication, but rather the "with
hold" option.
"David Browne" wrote:

>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:B04C4FDD-89F3-468E-8626-B335D964D61F@.microsoft.com...
> Also,
> Using Secondary Servers for Query Processing
> http://msdn2.microsoft.com/en-us/library/ms189572.aspx
>
> David
>
|||"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:6BA5BF29-8848-46C4-9C8A-A238AAB4E5E9@.microsoft.com...
> David, the question was not about uses of replication, but rather the
> "with
> hold" option.
>
My point, perhaps not really explained, was that the secondary database is
available for querying only while you are not restoring logs. If, for
instance, you only restore logs at the end of the day, you can use the log
shipping secondary server for point-in-time reporting.
David

Log Shipping with HOLD

I had an interview question the other day I don't understand. He asked about
types of replication and when you would use them. One of the options was "lo
g
shipping with hold" and why?
I can't think of a good reason to do this, as with that type of replication,
you would want the replicant/subscriber to be as close as possible to the
primary/publisher. Otherwise, why not just use snapshot?
Thoughts please.JayKon wrote:
> I had an interview question the other day I don't understand. He asked abo
ut
> types of replication and when you would use them. One of the options was "
log
> shipping with hold" and why?
> I can't think of a good reason to do this, as with that type of replicatio
n,
> you would want the replicant/subscriber to be as close as possible to the
> primary/publisher. Otherwise, why not just use snapshot?
> Thoughts please.
For a standby or "disaster recovery" server, having a delay in the
restore process can help protect from logical disasters, i.e. somebody
nukes a million rows in a table by accident. You have time to prevent
that mistake from reaching your standby machine in case you need it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||My first reaction is "DOH!", that makes sense. But after that, I wonder if
thats really such a good idea. After all, you can still do a point-in-time
restore, or even restore to a seperate DB and manually extract the rows to
recover.
Anyway, thanks Tracy, I should have thought of that.
"Tracy McKibben" wrote:

> JayKon wrote:
> For a standby or "disaster recovery" server, having a delay in the
> restore process can help protect from logical disasters, i.e. somebody
> nukes a million rows in a table by accident. You have time to prevent
> that mistake from reaching your standby machine in case you need it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:B04C4FDD-89F3-468E-8626-B335D964D61F@.microsoft.com...
> My first reaction is "DOH!", that makes sense. But after that, I wonder if
> thats really such a good idea. After all, you can still do a point-in-time
> restore, or even restore to a seperate DB and manually extract the rows to
> recover.
>
Also,
Using Secondary Servers for Query Processing
http://msdn2.microsoft.com/en-us/library/ms189572.aspx
David|||JayKon wrote:
> My first reaction is "DOH!", that makes sense. But after that, I wonder if
> thats really such a good idea. After all, you can still do a point-in-time
> restore, or even restore to a seperate DB and manually extract the rows to
> recover.
>
All true, but if you're in a low- or no-downtime environment, doing
weekly full/hourly log backups, with a two-hour delay in log shipping,
if something happens on Thursday, will it take longer to restore the
full and 4 days of logs, or two hours of logs (up to the failure)?
It's just another tool, another way of protecting yourself...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David, the question was not about uses of replication, but rather the "with
hold" option.
"David Browne" wrote:

>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:B04C4FDD-89F3-468E-8626-B335D964D61F@.microsoft.com...
> Also,
> Using Secondary Servers for Query Processing
> http://msdn2.microsoft.com/en-us/library/ms189572.aspx
>
> David
>|||"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:6BA5BF29-8848-46C4-9C8A-A238AAB4E5E9@.microsoft.com...
> David, the question was not about uses of replication, but rather the
> "with
> hold" option.
>
My point, perhaps not really explained, was that the secondary database is
available for querying only while you are not restoring logs. If, for
instance, you only restore logs at the end of the day, you can use the log
shipping secondary server for point-in-time reporting.
David

Log Shipping with HOLD

I had an interview question the other day I don't understand. He asked about
types of replication and when you would use them. One of the options was "log
shipping with hold" and why?
I can't think of a good reason to do this, as with that type of replication,
you would want the replicant/subscriber to be as close as possible to the
primary/publisher. Otherwise, why not just use snapshot?
Thoughts please.JayKon wrote:
> I had an interview question the other day I don't understand. He asked about
> types of replication and when you would use them. One of the options was "log
> shipping with hold" and why?
> I can't think of a good reason to do this, as with that type of replication,
> you would want the replicant/subscriber to be as close as possible to the
> primary/publisher. Otherwise, why not just use snapshot?
> Thoughts please.
For a standby or "disaster recovery" server, having a delay in the
restore process can help protect from logical disasters, i.e. somebody
nukes a million rows in a table by accident. You have time to prevent
that mistake from reaching your standby machine in case you need it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||My first reaction is "DOH!", that makes sense. But after that, I wonder if
thats really such a good idea. After all, you can still do a point-in-time
restore, or even restore to a seperate DB and manually extract the rows to
recover.
Anyway, thanks Tracy, I should have thought of that.
"Tracy McKibben" wrote:
> JayKon wrote:
> > I had an interview question the other day I don't understand. He asked about
> > types of replication and when you would use them. One of the options was "log
> > shipping with hold" and why?
> >
> > I can't think of a good reason to do this, as with that type of replication,
> > you would want the replicant/subscriber to be as close as possible to the
> > primary/publisher. Otherwise, why not just use snapshot?
> >
> > Thoughts please.
> For a standby or "disaster recovery" server, having a delay in the
> restore process can help protect from logical disasters, i.e. somebody
> nukes a million rows in a table by accident. You have time to prevent
> that mistake from reaching your standby machine in case you need it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:B04C4FDD-89F3-468E-8626-B335D964D61F@.microsoft.com...
> My first reaction is "DOH!", that makes sense. But after that, I wonder if
> thats really such a good idea. After all, you can still do a point-in-time
> restore, or even restore to a seperate DB and manually extract the rows to
> recover.
>
Also,
Using Secondary Servers for Query Processing
http://msdn2.microsoft.com/en-us/library/ms189572.aspx
David|||JayKon wrote:
> My first reaction is "DOH!", that makes sense. But after that, I wonder if
> thats really such a good idea. After all, you can still do a point-in-time
> restore, or even restore to a seperate DB and manually extract the rows to
> recover.
>
All true, but if you're in a low- or no-downtime environment, doing
weekly full/hourly log backups, with a two-hour delay in log shipping,
if something happens on Thursday, will it take longer to restore the
full and 4 days of logs, or two hours of logs (up to the failure)?
It's just another tool, another way of protecting yourself...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David, the question was not about uses of replication, but rather the "with
hold" option.
"David Browne" wrote:
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:B04C4FDD-89F3-468E-8626-B335D964D61F@.microsoft.com...
> > My first reaction is "DOH!", that makes sense. But after that, I wonder if
> > thats really such a good idea. After all, you can still do a point-in-time
> > restore, or even restore to a seperate DB and manually extract the rows to
> > recover.
> >
> Also,
> Using Secondary Servers for Query Processing
> http://msdn2.microsoft.com/en-us/library/ms189572.aspx
>
> David
>|||"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:6BA5BF29-8848-46C4-9C8A-A238AAB4E5E9@.microsoft.com...
> David, the question was not about uses of replication, but rather the
> "with
> hold" option.
>
My point, perhaps not really explained, was that the secondary database is
available for querying only while you are not restoring logs. If, for
instance, you only restore logs at the end of the day, you can use the log
shipping secondary server for point-in-time reporting.
Davidsql

log shipping vs. Replication

>Now, having said that, once box A is back up, in an ideal
>world, why would you make the switch back to A as a
>primary since B should hopefully have the same capacity.
>
Because BoxA is in a location that meets the specs for our
customers. BoxB is not.

>As for a full restore back on A, it depends on what state
>it was in, where the secondary was, etc. Even if you
wind
>up depending on your situation having to do a full
restore
>on A, you essentially set up log shipping. Do your point
>in time on B, restore on A, then use log shipping to
catch
>up.
Ive never used it so please bear with me. I was told that
I would have to:
Take a full backup of BoxB.
Transfer it to the Location of BoxA.
Restore it on BoxA.
Is this not the case? I thought it sounded odd but was re-
assured.
The way I look at it is
1) Clustering replicates/clones a server
2) backing up and restoring the database to the standy server, replicates
the database
3) Log Shipping is a continous backup and restore of the database
4) replication replicates transactions
It depends on your requirements, and how expensive downtime is to you, or
how valuable your data is. For disaster recovery most people look at
clustering solutions.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
news:53e601c47424$b14a55a0$a601280a@.phx.gbl...
> Usually it is a capacity issue, not one like this. So if
> Box B is in an unsuitable location, do your customers
> know? Usually this is part of any SLA you establish with
> them.
>
> Again, it depends, especially on the type of failure. If
> you were able to grab the tail of the log on the primary,
> and at some point get it to standby, the secondary when
> all logs are applied at the time BEFORE you redirect
> traffic, they are in the same place data/etc. wise.
> Now, if your DBs are inconsistent, it's pretty much
> impossible. Remember that backup is an online operation,
> so if you need to do a full backup, do it, still allow
> work on B, restore the backup on A, then configure log
> shipping from B to A, and make the switch. It's pretty
> painless.
> If you did it your way, you would need to pretty much stop
> activity on B to ensure they are at the same point, which
> is probably what you don't want to do. Transaction logs
> exist for a reason.
> So again, it is completely dependent upon what state
> you're in at the time of failure.
> I've had customers do upgrades using log shipping and
> we've incurred only minutes of actual downtime because of
> it.

log shipping vs. Replication

I hate replication, it's given me nothing but endless
amounts of problems, headaches and people who are
completely pissed at me that they are not getting there
reports. I want to move to log shipping, my question is to
those of you who have used both. Is this really reliable,
is it going to be another headache or does it work well?
Thanks,
Bryan
Bryan,
have a look at this article which might help you choose between these
technologies, as they are not quite equivalent:
http://www.sqlservercentral.com/colu...eplication.asp
If I may ask, what specific problems have you been having with replication?
Regards,
Paul Ibison
|||Hey Paul,
I had a table with 9 million rows and 5 indexes. I
wanted to replicate this off to a reporting server. The
reporting server was the same as production a Dell 6650,
Raid 5( I wanted to strip it but the idea was to use it as
a warm backup to production) Quad processor, brand new. It
would run the snap shot from the distributor a dual, which
went really fast, about five minutes. It would apply the
data rather quickly, but then IO would hit the roof for
about 11 hours I think I set the timeout to last time. It
would always say waiting for a backend process to
complete. There is no way it takes that long to apply the
indexes. I read the Microsoft white paper and the
sqlserverperformance tips. All logs are on seperate
physical disk, the distributor was a dedicated distributor.
I increases the timeout, set the number of threads to the
number of tables. Loaded something else in memory, etc,
etc, etc... Finally. I just said screw this, this sucks
there has to be an easier way.
>--Original Message--
>Bryan,
>have a look at this article which might help you choose
between these
>technologies, as they are not quite equivalent:
>http://www.sqlservercentral.com/colu...ibison/logship
pingvsreplication.asp
>If I may ask, what specific problems have you been having
with replication?
>Regards,
>Paul Ibison
>
>.
>

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 Vs Replication

which enables schema changes like new columns, replication or log shipping ? i can't remember which one it was ?which enables schema changes like new columns, replication or log shipping ? i can't remember which one it was ?

This is what you're after?

http://msdn.microsoft.com/library/en-us/replsql/replimpl_22pf.asp

Log shipping procedure with replication

hello experts,

Im new to sqlserver database and i really need ur advice on this.

We are planning to setup logshipping for our sqlserver 2005 database.Our setup is like this

we have a primary database on server A which has to be logshipped to Server C.A reporting server B is also maintained by doing transactional replication between Server A and Server B.

Now i want to know whether the procedure for carrying out logshipping process between server A and Server C will remain the same or wil there be any change because a replication is also going on server A .Please give me a step by step procedure for doing the logshipping on a server which is even involved in replication process.

Thanks in advance

Regards

Arvind L

Hi Arvind,
Welcome to the world of Sql Server
pls refer the link it might help you, http://msdn2.microsoft.com/en-us/library/ms151224.aspx

Thanxx
Deepak

|||

Thanks deepak..

Log shipping procedure with replication

hello experts,

Im new to sqlserver database and i really need ur advice on this.

We are planning to setup logshipping for our sqlserver 2005 database.Our setup is like this

we have a primary database on server A which has to be logshipped to Server C.A reporting server B is also maintained by doing transactional replication between Server A and Server B.

Now i want to know whether the procedure for carrying out logshipping process between server A and Server C will remain the same or wil there be any change because a replication is also going on server A .Please give me a step by step procedure for doing the logshipping on a server which is even involved in replication process.

Thanks in advance

Regards

Arvind L

Hi Arvind,
Welcome to the world of Sql Server
pls refer the link it might help you, http://msdn2.microsoft.com/en-us/library/ms151224.aspx

Thanxx
Deepak

|||

Thanks deepak..

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 or Replication

Which would you recommend to setup as a failover database server? (We do not
have the ability right now to setup a sql cluster). What is the difference
between log shipping and database replication? Pros/Cons? Thanks.
- Gabe
i say go for log shipping. it is designed for a failover database
server - it maintains an exact copy of a database on your primary db
server by transfering transaction logs. Since it's restoring
transaction logs, you can even add tables, sp, etc to your primary db
and they will automatically be created on the backup db server.
My experience with replication is limited, but since it is more
flexible and not necessarly designed to mirror an entire database I
think it would introduce unecessary complexity to your situation and
require more maintenance.
steve.
|||It depends on a lot of things. You can get started with the
high availability options discussed in this chapter in the
Resource Kit:
Chapter 15 - High Availability Options
http://www.microsoft.com/technet/pro...rt1/c0161.mspx
There is also some information in the following KB article:
Disaster recovery options for Microsoft SQL Server
http://support.microsoft.com/?id=822400
-Sue
On Mon, 31 Oct 2005 16:46:34 -0500, "Gabe Matteson"
<gmatteson@.inquery.biz.nospam> wrote:

>Which would you recommend to setup as a failover database server? (We do not
>have the ability right now to setup a sql cluster). What is the difference
>between log shipping and database replication? Pros/Cons? Thanks.
>- Gabe
>
|||"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in
news:OIonzPm3FHA.1596@.tk2msftngp13.phx.gbl:

> Which would you recommend to setup as a failover database server? (We
> do not have the ability right now to setup a sql cluster). What is the
> difference between log shipping and database replication? Pros/Cons?
Neither log shipping nor replication supports automatic failover. If
failover is a concert to you, and you cannot implement a cluster, I would
strongly suggest that you have a look on the database mirroring feature of
SQL Server 2005.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||Gabe,
this article will hopefully also help:
http://www.replicationanswers.com/Standby.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Gabe,
All the answers you have recieved are good ones. Let me see if I can add
some added information.
Replication for a fail over option can work. I personally would not
recommend it unless your database is designed for Merge Replication. Merge
replication would give you the ability to start writing to another server as
soon as the failover conncetion is made.
Log Shipping has a time delay on the time the data is moved from the
production server to the standby server. This is going to depend on your
t-log backup schedule.
IMHO I would consider looking at less expensive failover options. I use
both Active/Active and Active/Passive. I perfer A/A becuase the second piece
of hardware is not sitting there doing nothing when the primary node is up.
The other option that I think you need to look at no matter what soultion you
choose is does the application understand the database failover. If a
conncetion is broken do you have to re-connect manually, or will the app look
for the second server after a certian amount of time without a responce. If
you are at a company where they mention that they can not afford to be down
or they are losing money. Then I would let them know that they really can't
afford to be without a cluster. I know that CDW sells them in a package for
pretty cheap. I have a contact that if you would like send me a note and I
am sure she could get you a good quote.
If you would like more infomation about the 3 options please let us know. I
am sure that you will get a ton of answers.
--Chris
If you wou
"Gabe Matteson" wrote:

> Which would you recommend to setup as a failover database server? (We do not
> have the ability right now to setup a sql cluster). What is the difference
> between log shipping and database replication? Pros/Cons? Thanks.
> - Gabe
>
>
|||Thanks, I appreciate all the replies!
"Chris Shaw" <Chris_Shaw@.SQLPASS.org> wrote in message
news:82847E0A-0820-40A4-B07F-130200E5E231@.microsoft.com...[vbcol=seagreen]
> Gabe,
> All the answers you have recieved are good ones. Let me see if I can add
> some added information.
> Replication for a fail over option can work. I personally would not
> recommend it unless your database is designed for Merge Replication.
> Merge
> replication would give you the ability to start writing to another server
> as
> soon as the failover conncetion is made.
> Log Shipping has a time delay on the time the data is moved from the
> production server to the standby server. This is going to depend on your
> t-log backup schedule.
> IMHO I would consider looking at less expensive failover options. I use
> both Active/Active and Active/Passive. I perfer A/A becuase the second
> piece
> of hardware is not sitting there doing nothing when the primary node is
> up.
> The other option that I think you need to look at no matter what soultion
> you
> choose is does the application understand the database failover. If a
> conncetion is broken do you have to re-connect manually, or will the app
> look
> for the second server after a certian amount of time without a responce.
> If
> you are at a company where they mention that they can not afford to be
> down
> or they are losing money. Then I would let them know that they really
> can't
> afford to be without a cluster. I know that CDW sells them in a package
> for
> pretty cheap. I have a contact that if you would like send me a note and
> I
> am sure she could get you a good quote.
> If you would like more infomation about the 3 options please let us know.
> I
> am sure that you will get a ton of answers.
> --Chris
> If you wou
> "Gabe Matteson" wrote:

Log Shipping or Replication

Which would you recommend to setup as a failover database server? (We do not
have the ability right now to setup a sql cluster). What is the difference
between log shipping and database replication? Pros/Cons? Thanks.
- Gabei say go for log shipping. it is designed for a failover database
server - it maintains an exact copy of a database on your primary db
server by transfering transaction logs. Since it's restoring
transaction logs, you can even add tables, sp, etc to your primary db
and they will automatically be created on the backup db server.
My experience with replication is limited, but since it is more
flexible and not necessarly designed to mirror an entire database I
think it would introduce unecessary complexity to your situation and
require more maintenance.
steve.|||It depends on a lot of things. You can get started with the
high availability options discussed in this chapter in the
Resource Kit:
Chapter 15 - High Availability Options
http://www.microsoft.com/technet/pr...com/?id=822400
-Sue
On Mon, 31 Oct 2005 16:46:34 -0500, "Gabe Matteson"
<gmatteson@.inquery.biz.nospam> wrote:

>Which would you recommend to setup as a failover database server? (We do no
t
>have the ability right now to setup a sql cluster). What is the difference
>between log shipping and database replication? Pros/Cons? Thanks.
>- Gabe
>|||"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in
news:OIonzPm3FHA.1596@.tk2msftngp13.phx.gbl:

> Which would you recommend to setup as a failover database server? (We
> do not have the ability right now to setup a sql cluster). What is the
> difference between log shipping and database replication? Pros/Cons?
Neither log shipping nor replication supports automatic failover. If
failover is a concert to you, and you cannot implement a cluster, I would
strongly suggest that you have a look on the database mirroring feature of
SQL Server 2005.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Gabe,
this article will hopefully also help:
http://www.replicationanswers.com/Standby.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Gabe,
All the answers you have recieved are good ones. Let me see if I can add
some added information.
Replication for a fail over option can work. I personally would not
recommend it unless your database is designed for Merge Replication. Merge
replication would give you the ability to start writing to another server as
soon as the failover conncetion is made.
Log Shipping has a time delay on the time the data is moved from the
production server to the standby server. This is going to depend on your
t-log backup schedule.
IMHO I would consider looking at less expensive failover options. I use
both Active/Active and Active/Passive. I perfer A/A becuase the second piec
e
of hardware is not sitting there doing nothing when the primary node is up.
The other option that I think you need to look at no matter what soultion yo
u
choose is does the application understand the database failover. If a
conncetion is broken do you have to re-connect manually, or will the app loo
k
for the second server after a certian amount of time without a responce. If
you are at a company where they mention that they can not afford to be down
or they are losing money. Then I would let them know that they really can't
afford to be without a cluster. I know that CDW sells them in a package for
pretty cheap. I have a contact that if you would like send me a note and I
am sure she could get you a good quote.
If you would like more infomation about the 3 options please let us know. I
am sure that you will get a ton of answers.
--Chris
If you wou
"Gabe Matteson" wrote:

> Which would you recommend to setup as a failover database server? (We do n
ot
> have the ability right now to setup a sql cluster). What is the difference
> between log shipping and database replication? Pros/Cons? Thanks.
> - Gabe
>
>|||Thanks, I appreciate all the replies!
"Chris Shaw" <Chris_Shaw@.SQLPASS.org> wrote in message
news:82847E0A-0820-40A4-B07F-130200E5E231@.microsoft.com...[vbcol=seagreen]
> Gabe,
> All the answers you have recieved are good ones. Let me see if I can add
> some added information.
> Replication for a fail over option can work. I personally would not
> recommend it unless your database is designed for Merge Replication.
> Merge
> replication would give you the ability to start writing to another server
> as
> soon as the failover conncetion is made.
> Log Shipping has a time delay on the time the data is moved from the
> production server to the standby server. This is going to depend on your
> t-log backup schedule.
> IMHO I would consider looking at less expensive failover options. I use
> both Active/Active and Active/Passive. I perfer A/A becuase the second
> piece
> of hardware is not sitting there doing nothing when the primary node is
> up.
> The other option that I think you need to look at no matter what soultion
> you
> choose is does the application understand the database failover. If a
> conncetion is broken do you have to re-connect manually, or will the app
> look
> for the second server after a certian amount of time without a responce.
> If
> you are at a company where they mention that they can not afford to be
> down
> or they are losing money. Then I would let them know that they really
> can't
> afford to be without a cluster. I know that CDW sells them in a package
> for
> pretty cheap. I have a contact that if you would like send me a note and
> I
> am sure she could get you a good quote.
> If you would like more infomation about the 3 options please let us know.
> I
> am sure that you will get a ton of answers.
> --Chris
> If you wou
> "Gabe Matteson" wrote:
>sql

Log Shipping or Replication

Which would you recommend to setup as a failover database server? (We do not
have the ability right now to setup a sql cluster). What is the difference
between log shipping and database replication? Pros/Cons? Thanks.
- Gabe
It depends on a lot of things. You can get started with the
high availability options discussed in this chapter in the
Resource Kit:
Chapter 15 - High Availability Options
http://www.microsoft.com/technet/pro...rt1/c0161.mspx
There is also some information in the following KB article:
Disaster recovery options for Microsoft SQL Server
http://support.microsoft.com/?id=822400
-Sue
On Mon, 31 Oct 2005 16:46:34 -0500, "Gabe Matteson"
<gmatteson@.inquery.biz.nospam> wrote:

>Which would you recommend to setup as a failover database server? (We do not
>have the ability right now to setup a sql cluster). What is the difference
>between log shipping and database replication? Pros/Cons? Thanks.
>- Gabe
>
|||"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in
news:OIonzPm3FHA.1596@.tk2msftngp13.phx.gbl:

> Which would you recommend to setup as a failover database server? (We
> do not have the ability right now to setup a sql cluster). What is the
> difference between log shipping and database replication? Pros/Cons?
Neither log shipping nor replication supports automatic failover. If
failover is a concert to you, and you cannot implement a cluster, I would
strongly suggest that you have a look on the database mirroring feature of
SQL Server 2005.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||Gabe,
this article will hopefully also help:
http://www.replicationanswers.com/Standby.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Gabe,
All the answers you have recieved are good ones. Let me see if I can add
some added information.
Replication for a fail over option can work. I personally would not
recommend it unless your database is designed for Merge Replication. Merge
replication would give you the ability to start writing to another server as
soon as the failover conncetion is made.
Log Shipping has a time delay on the time the data is moved from the
production server to the standby server. This is going to depend on your
t-log backup schedule.
IMHO I would consider looking at less expensive failover options. I use
both Active/Active and Active/Passive. I perfer A/A becuase the second piece
of hardware is not sitting there doing nothing when the primary node is up.
The other option that I think you need to look at no matter what soultion you
choose is does the application understand the database failover. If a
conncetion is broken do you have to re-connect manually, or will the app look
for the second server after a certian amount of time without a responce. If
you are at a company where they mention that they can not afford to be down
or they are losing money. Then I would let them know that they really can't
afford to be without a cluster. I know that CDW sells them in a package for
pretty cheap. I have a contact that if you would like send me a note and I
am sure she could get you a good quote.
If you would like more infomation about the 3 options please let us know. I
am sure that you will get a ton of answers.
--Chris
If you wou
"Gabe Matteson" wrote:

> Which would you recommend to setup as a failover database server? (We do not
> have the ability right now to setup a sql cluster). What is the difference
> between log shipping and database replication? Pros/Cons? Thanks.
> - Gabe
>
>
|||Thanks, I appreciate all the replies!
"Chris Shaw" <Chris_Shaw@.SQLPASS.org> wrote in message
news:82847E0A-0820-40A4-B07F-130200E5E231@.microsoft.com...[vbcol=seagreen]
> Gabe,
> All the answers you have recieved are good ones. Let me see if I can add
> some added information.
> Replication for a fail over option can work. I personally would not
> recommend it unless your database is designed for Merge Replication.
> Merge
> replication would give you the ability to start writing to another server
> as
> soon as the failover conncetion is made.
> Log Shipping has a time delay on the time the data is moved from the
> production server to the standby server. This is going to depend on your
> t-log backup schedule.
> IMHO I would consider looking at less expensive failover options. I use
> both Active/Active and Active/Passive. I perfer A/A becuase the second
> piece
> of hardware is not sitting there doing nothing when the primary node is
> up.
> The other option that I think you need to look at no matter what soultion
> you
> choose is does the application understand the database failover. If a
> conncetion is broken do you have to re-connect manually, or will the app
> look
> for the second server after a certian amount of time without a responce.
> If
> you are at a company where they mention that they can not afford to be
> down
> or they are losing money. Then I would let them know that they really
> can't
> afford to be without a cluster. I know that CDW sells them in a package
> for
> pretty cheap. I have a contact that if you would like send me a note and
> I
> am sure she could get you a good quote.
> If you would like more infomation about the 3 options please let us know.
> I
> am sure that you will get a ton of answers.
> --Chris
> If you wou
> "Gabe Matteson" wrote:

Log Shipping or Replication

Which would you recommend to setup as a failover database server? (We do not
have the ability right now to setup a sql cluster). What is the difference
between log shipping and database replication? Pros/Cons? Thanks.
- Gabei say go for log shipping. it is designed for a failover database
server - it maintains an exact copy of a database on your primary db
server by transfering transaction logs. Since it's restoring
transaction logs, you can even add tables, sp, etc to your primary db
and they will automatically be created on the backup db server.
My experience with replication is limited, but since it is more
flexible and not necessarly designed to mirror an entire database I
think it would introduce unecessary complexity to your situation and
require more maintenance.
steve.|||It depends on a lot of things. You can get started with the
high availability options discussed in this chapter in the
Resource Kit:
Chapter 15 - High Availability Options
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part1/c0161.mspx
There is also some information in the following KB article:
Disaster recovery options for Microsoft SQL Server
http://support.microsoft.com/?id=822400
-Sue
On Mon, 31 Oct 2005 16:46:34 -0500, "Gabe Matteson"
<gmatteson@.inquery.biz.nospam> wrote:
>Which would you recommend to setup as a failover database server? (We do not
>have the ability right now to setup a sql cluster). What is the difference
>between log shipping and database replication? Pros/Cons? Thanks.
>- Gabe
>|||"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in
news:OIonzPm3FHA.1596@.tk2msftngp13.phx.gbl:
> Which would you recommend to setup as a failover database server? (We
> do not have the ability right now to setup a sql cluster). What is the
> difference between log shipping and database replication? Pros/Cons?
Neither log shipping nor replication supports automatic failover. If
failover is a concert to you, and you cannot implement a cluster, I would
strongly suggest that you have a look on the database mirroring feature of
SQL Server 2005.
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Gabe,
this article will hopefully also help:
http://www.replicationanswers.com/Standby.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Gabe,
All the answers you have recieved are good ones. Let me see if I can add
some added information.
Replication for a fail over option can work. I personally would not
recommend it unless your database is designed for Merge Replication. Merge
replication would give you the ability to start writing to another server as
soon as the failover conncetion is made.
Log Shipping has a time delay on the time the data is moved from the
production server to the standby server. This is going to depend on your
t-log backup schedule.
IMHO I would consider looking at less expensive failover options. I use
both Active/Active and Active/Passive. I perfer A/A becuase the second piece
of hardware is not sitting there doing nothing when the primary node is up.
The other option that I think you need to look at no matter what soultion you
choose is does the application understand the database failover. If a
conncetion is broken do you have to re-connect manually, or will the app look
for the second server after a certian amount of time without a responce. If
you are at a company where they mention that they can not afford to be down
or they are losing money. Then I would let them know that they really can't
afford to be without a cluster. I know that CDW sells them in a package for
pretty cheap. I have a contact that if you would like send me a note and I
am sure she could get you a good quote.
If you would like more infomation about the 3 options please let us know. I
am sure that you will get a ton of answers.
--Chris
If you wou
"Gabe Matteson" wrote:
> Which would you recommend to setup as a failover database server? (We do not
> have the ability right now to setup a sql cluster). What is the difference
> between log shipping and database replication? Pros/Cons? Thanks.
> - Gabe
>
>|||Thanks, I appreciate all the replies!
"Chris Shaw" <Chris_Shaw@.SQLPASS.org> wrote in message
news:82847E0A-0820-40A4-B07F-130200E5E231@.microsoft.com...
> Gabe,
> All the answers you have recieved are good ones. Let me see if I can add
> some added information.
> Replication for a fail over option can work. I personally would not
> recommend it unless your database is designed for Merge Replication.
> Merge
> replication would give you the ability to start writing to another server
> as
> soon as the failover conncetion is made.
> Log Shipping has a time delay on the time the data is moved from the
> production server to the standby server. This is going to depend on your
> t-log backup schedule.
> IMHO I would consider looking at less expensive failover options. I use
> both Active/Active and Active/Passive. I perfer A/A becuase the second
> piece
> of hardware is not sitting there doing nothing when the primary node is
> up.
> The other option that I think you need to look at no matter what soultion
> you
> choose is does the application understand the database failover. If a
> conncetion is broken do you have to re-connect manually, or will the app
> look
> for the second server after a certian amount of time without a responce.
> If
> you are at a company where they mention that they can not afford to be
> down
> or they are losing money. Then I would let them know that they really
> can't
> afford to be without a cluster. I know that CDW sells them in a package
> for
> pretty cheap. I have a contact that if you would like send me a note and
> I
> am sure she could get you a good quote.
> If you would like more infomation about the 3 options please let us know.
> I
> am sure that you will get a ton of answers.
> --Chris
> If you wou
> "Gabe Matteson" wrote:
>> Which would you recommend to setup as a failover database server? (We do
>> not
>> have the ability right now to setup a sql cluster). What is the
>> difference
>> between log shipping and database replication? Pros/Cons? Thanks.
>> - Gabe
>>

Log Shipping or Replication

Which would you recommend to setup as a failover database server? (We do not
have the ability right now to setup a sql cluster). What is the difference
between log shipping and database replication? Pros/Cons? Thanks.
- GabeIt depends on a lot of things. You can get started with the
high availability options discussed in this chapter in the
Resource Kit:
Chapter 15 - High Availability Options
http://www.microsoft.com/technet/pr...com/?id=822400
-Sue
On Mon, 31 Oct 2005 16:46:34 -0500, "Gabe Matteson"
<gmatteson@.inquery.biz.nospam> wrote:

>Which would you recommend to setup as a failover database server? (We do no
t
>have the ability right now to setup a sql cluster). What is the difference
>between log shipping and database replication? Pros/Cons? Thanks.
>- Gabe
>|||"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in
news:OIonzPm3FHA.1596@.tk2msftngp13.phx.gbl:

> Which would you recommend to setup as a failover database server? (We
> do not have the ability right now to setup a sql cluster). What is the
> difference between log shipping and database replication? Pros/Cons?
Neither log shipping nor replication supports automatic failover. If
failover is a concert to you, and you cannot implement a cluster, I would
strongly suggest that you have a look on the database mirroring feature of
SQL Server 2005.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Gabe,
this article will hopefully also help:
http://www.replicationanswers.com/Standby.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Gabe,
All the answers you have recieved are good ones. Let me see if I can add
some added information.
Replication for a fail over option can work. I personally would not
recommend it unless your database is designed for Merge Replication. Merge
replication would give you the ability to start writing to another server as
soon as the failover conncetion is made.
Log Shipping has a time delay on the time the data is moved from the
production server to the standby server. This is going to depend on your
t-log backup schedule.
IMHO I would consider looking at less expensive failover options. I use
both Active/Active and Active/Passive. I perfer A/A becuase the second piec
e
of hardware is not sitting there doing nothing when the primary node is up.
The other option that I think you need to look at no matter what soultion yo
u
choose is does the application understand the database failover. If a
conncetion is broken do you have to re-connect manually, or will the app loo
k
for the second server after a certian amount of time without a responce. If
you are at a company where they mention that they can not afford to be down
or they are losing money. Then I would let them know that they really can't
afford to be without a cluster. I know that CDW sells them in a package for
pretty cheap. I have a contact that if you would like send me a note and I
am sure she could get you a good quote.
If you would like more infomation about the 3 options please let us know. I
am sure that you will get a ton of answers.
--Chris
If you wou
"Gabe Matteson" wrote:

> Which would you recommend to setup as a failover database server? (We do n
ot
> have the ability right now to setup a sql cluster). What is the difference
> between log shipping and database replication? Pros/Cons? Thanks.
> - Gabe
>
>|||Thanks, I appreciate all the replies!
"Chris Shaw" <Chris_Shaw@.SQLPASS.org> wrote in message
news:82847E0A-0820-40A4-B07F-130200E5E231@.microsoft.com...[vbcol=seagreen]
> Gabe,
> All the answers you have recieved are good ones. Let me see if I can add
> some added information.
> Replication for a fail over option can work. I personally would not
> recommend it unless your database is designed for Merge Replication.
> Merge
> replication would give you the ability to start writing to another server
> as
> soon as the failover conncetion is made.
> Log Shipping has a time delay on the time the data is moved from the
> production server to the standby server. This is going to depend on your
> t-log backup schedule.
> IMHO I would consider looking at less expensive failover options. I use
> both Active/Active and Active/Passive. I perfer A/A becuase the second
> piece
> of hardware is not sitting there doing nothing when the primary node is
> up.
> The other option that I think you need to look at no matter what soultion
> you
> choose is does the application understand the database failover. If a
> conncetion is broken do you have to re-connect manually, or will the app
> look
> for the second server after a certian amount of time without a responce.
> If
> you are at a company where they mention that they can not afford to be
> down
> or they are losing money. Then I would let them know that they really
> can't
> afford to be without a cluster. I know that CDW sells them in a package
> for
> pretty cheap. I have a contact that if you would like send me a note and
> I
> am sure she could get you a good quote.
> If you would like more infomation about the 3 options please let us know.
> I
> am sure that you will get a ton of answers.
> --Chris
> If you wou
> "Gabe Matteson" wrote:
>

Log Shipping for replication

I need to implement a dirt cheap replication method for some dirt
cheap servers. We are using the SQL Server workgroup license. Is
there anything in this that prevents using log shipping for
replication in Workgroup versus Enterprise?

Thanks
Travis"T.Paakki" <trp24@.hotmail.com> wrote in message
news:6358a14b.0405211202.70d39540@.posting.google.c om...
> I need to implement a dirt cheap replication method for some dirt
> cheap servers. We are using the SQL Server workgroup license. Is
> there anything in this that prevents using log shipping for
> replication in Workgroup versus Enterprise?
> Thanks
> Travis

I'm not sure what you mean by a "SQL Server workgroup license" - perhaps you
mean you're using MSSQL in a workgroup, rather than in a domain? In any
case, log shipping requires copying files from one server's filesystem to
another, so you would need to use service accounts which have those
permissions.

Apart from that, there are other options, including rolling your own
solution:

http://sqlguy.home.comcast.net/logship.htm
http://www.sql-server-performance.c...og_shipping.asp

MSSQL replication might also be a possibility, depending on what you're
trying to achieve, but it's not really viable to build any sort of "dirt
cheap" highly available system, if only because of operations costs, ie.
making sure there is always someone there to respond when you need them.

Simon

Log Shipping Error

Log Shipping is not replication in the immediate sense, but it is as close as I could find in this set of forums. If there is a better place for this question, please direct me to it.

I am trying to set up Log shipping on two new servers, one is primary and the other is backup. I have made sure that the SQL Startup Login is the same name and password on both servers, and that it has all administrative permissions. When I try to set up Log Shipping, after I click the 'Finish' button, I get the following error:

Error 50007: xp_repl_encrypt: Error executing srv_paramsetoutput

I have done a search for this on support.microsoft.com, and on all of Microsoft. I have found nothing on these attempts. I have even Googled it, and have found very limited entries about making sure that the passwords are the same on both machines, etc. I have followed that advice, to no avail.

The two new machines in question are running Windows Adv Server 2000, and are part of a work group, not a domain. They can see each other, and can read / write to each others shared areas (I have verified that the areas for the Log Shipping are shared).

If anyone can shed some light on this, I would be very grateful.

Thanks in advance,

RollinHi Rollin,

Are you using Logshipping in SQl 2005 ?

Thanks
Yunwen|||No, Since SQL 2005 is not even fully out of the box. We will be with SQL 2000 fro some time to come. Since I made the last post, however, we have upgraded the new servers in question to Win Adv Server 2003, instead of 2000, and the Log Shipping error went away. It seems there must have been some windows related problem.

Thanks for your interest.

Rollin|||Thanks Rollin for updating us with this info. Please let us know if there is any further issues with LS in Sql 2005.

Yunwen|||

I am having the same error when I set up log shipping in SQL 2000. Can you share with me the solution?

Thanks,

Jim

|||This problem occurs probably because you are using a password too long for the user
log_shipping_monitor_probe to validate against the monitor server.

In order do despite the problem try to use a single char in the log_shipping_monitor_probe password's. If the problem is resolved you have to find a password with a length up to 8 chars.

I think this is a bug in the log-shipping fo SQL Server 2000 but MS guys can tell us more...

Log Shipping Error

Log Shipping is not replication in the immediate sense, but it is as close as I could find in this set of forums. If there is a better place for this question, please direct me to it.

I am trying to set up Log shipping on two new servers, one is primary and the other is backup. I have made sure that the SQL Startup Login is the same name and password on both servers, and that it has all administrative permissions. When I try to set up Log Shipping, after I click the 'Finish' button, I get the following error:

Error 50007: xp_repl_encrypt: Error executing srv_paramsetoutput

I have done a search for this on support.microsoft.com, and on all of Microsoft. I have found nothing on these attempts. I have even Googled it, and have found very limited entries about making sure that the passwords are the same on both machines, etc. I have followed that advice, to no avail.

The two new machines in question are running Windows Adv Server 2000, and are part of a work group, not a domain. They can see each other, and can read / write to each others shared areas (I have verified that the areas for the Log Shipping are shared).

If anyone can shed some light on this, I would be very grateful.

Thanks in advance,

RollinHi Rollin,

Are you using Logshipping in SQl 2005 ?

Thanks
Yunwen|||No, Since SQL 2005 is not even fully out of the box. We will be with SQL 2000 fro some time to come. Since I made the last post, however, we have upgraded the new servers in question to Win Adv Server 2003, instead of 2000, and the Log Shipping error went away. It seems there must have been some windows related problem.

Thanks for your interest.

Rollin|||Thanks Rollin for updating us with this info. Please let us know if there is any further issues with LS in Sql 2005.

Yunwen|||

I am having the same error when I set up log shipping in SQL 2000. Can you share with me the solution?

Thanks,

Jim

|||This problem occurs probably because you are using a password too long for the user
log_shipping_monitor_probe to validate against the monitor server.

In order do despite the problem try to use a single char in the log_shipping_monitor_probe password's. If the problem is resolved you have to find a password with a length up to 8 chars.

I think this is a bug in the log-shipping fo SQL Server 2000 but MS guys can tell us more...

Log Shipping Error

Log Shipping is not replication in the immediate sense, but it is as close as I could find in this set of forums. If there is a better place for this question, please direct me to it.

I am trying to set up Log shipping on two new servers, one is primary and the other is backup. I have made sure that the SQL Startup Login is the same name and password on both servers, and that it has all administrative permissions. When I try to set up Log Shipping, after I click the 'Finish' button, I get the following error:

Error 50007: xp_repl_encrypt: Error executing srv_paramsetoutput

I have done a search for this on support.microsoft.com, and on all of Microsoft. I have found nothing on these attempts. I have even Googled it, and have found very limited entries about making sure that the passwords are the same on both machines, etc. I have followed that advice, to no avail.

The two new machines in question are running Windows Adv Server 2000, and are part of a work group, not a domain. They can see each other, and can read / write to each others shared areas (I have verified that the areas for the Log Shipping are shared).

If anyone can shed some light on this, I would be very grateful.

Thanks in advance,

RollinHi Rollin,

Are you using Logshipping in SQl 2005 ?

Thanks
Yunwen|||No, Since SQL 2005 is not even fully out of the box. We will be with SQL 2000 fro some time to come. Since I made the last post, however, we have upgraded the new servers in question to Win Adv Server 2003, instead of 2000, and the Log Shipping error went away. It seems there must have been some windows related problem.

Thanks for your interest.

Rollin|||Thanks Rollin for updating us with this info. Please let us know if there is any further issues with LS in Sql 2005.

Yunwen|||

I am having the same error when I set up log shipping in SQL 2000. Can you share with me the solution?

Thanks,

Jim

|||This problem occurs probably because you are using a password too long for the user
log_shipping_monitor_probe to validate against the monitor server.

In order do despite the problem try to use a single char in the log_shipping_monitor_probe password's. If the problem is resolved you have to find a password with a length up to 8 chars.

I think this is a bug in the log-shipping fo SQL Server 2000 but MS guys can tell us more...

Log Shipping Concern

My department is thinking of implementing Log Shipping for a database. We
have a concern that, due to the nature of replication via log files, some
transactions may not execute properly on the secondary server and when this
occurs we will have essentially non-matching databases. Is this concern a
real issue and if so, how are such issues resolved?
Right now our situation allows us to dump and restore the database at any
time and take it offline if we have to. This dump and restore availability
ensures that we have two exact copies of the database with identical
footprints. Basically our concern is that we want to be assured an equal
concurrecy with log shipping.
Can anybody provide any insight?
Thanks,
Jeff
PS. I was not sure which group to post this in (I posted the same message
in this and in .Replication). Which group do Log Shipping questions belong
in?Log Shipping works but can be a pain in the neck.
the biggest problem we ran into was the occurence of "Non-Logged" Operations
in production.
this breaks log shipping quicker than you can blink.
Cheers,
Greg Jackson
Portland, OR|||Log shipping is mainly used for maintaining standby
Servers.
If you want to do logshipping for just only one database
in a server, make sure you are taking a right decision.
Compare the pros & cons of Replication and Log Shipping
and see whether log shipping is your right choice over
Replication. If you are more concerned about data
concurrency, check whether Replication solves your purpose.
Once log shipping has been implemented, it is relatively
easy to maintain. If Logshipping is implemented properly,
usually there wont be much issues.Its very robust.
I have encountered only the issues which is explained in
the articles below.
http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/reskit/sql2000/part4/c1361.asp
This link may help you get better idea of Log shipping.
This is a webcast.
http://support.microsoft.com/default.aspx?kbid=821786
The issue you have mentioned about the non-matching
databases, I have not come across in my vast experience in
Logshipping or Replication. I think that,if the databases
on publisher and subscriber are in sync with every aspects
(contraints,data..), then this will not occur.
If I have understand correctly, then Replication is the
best and suits your purpose.
Hope this gives you some idea to start with.
SQLVarad (MCDBA-1999,MCSE-1999)
>--Original Message--
>My department is thinking of implementing Log Shipping
for a database. We
>have a concern that, due to the nature of replication via
log files, some
>transactions may not execute properly on the secondary
server and when this
>occurs we will have essentially non-matching databases.
Is this concern a
>real issue and if so, how are such issues resolved?
>Right now our situation allows us to dump and restore the
database at any
>time and take it offline if we have to. This dump and
restore availability
>ensures that we have two exact copies of the database
with identical
>footprints. Basically our concern is that we want to be
assured an equal
>concurrecy with log shipping.
>Can anybody provide any insight?
>Thanks,
>Jeff
>PS. I was not sure which group to post this in (I posted
the same message
>in this and in .Replication). Which group do Log
Shipping questions belong
>in?
>
>.
>|||>the biggest problem we ran into was the occurence of "Non-
Logged" Operations
>in production.
>this breaks log shipping quicker than you can blink.
If you are in a production environment, you shouldn't be
doing non-logged operations. And under SQL 2K, this
really for the most part isn't possible. You use Full or
Bulk-Logged, and log shipping works. Simple prevents you
from making tran log backups at all.
The non-logged issue was more of a problem in SQL 7 and
earlier.