Log shipping alerts: March 2012

Friday, March 30, 2012

log shipping without backing up and restoring the whole database

Hi,
We have specific problem here. We have very large database (1TB) on one
server, and we need to set up log shipping to another server. Problem is that
we don't make regular sql server backups on the source server because of
obvious reason (it would take too long). Therefore we cannot restore database
in standby mode on the standby server. The only way to do database restore in
our case is to use BCV software (disk subsystem on both servers is EMC), and
that software doesn't have option od leaving database in standby mode. My
question is "is there any way to put database into standby mode, regardless
that is hasn't been restored using sql server native restore procedure?".
Servers are sql server 2000, service pack 3.
ThanksEven if you could put it into Standby mode, you wouldnt be able to apply
additional changes without TLog backups. I dont know if there is an option of
new software or not? If so, I wrote my own Log Shipping using SQL Lite Speed.
"Pedja" wrote:
> Hi,
> We have specific problem here. We have very large database (1TB) on one
> server, and we need to set up log shipping to another server. Problem is that
> we don't make regular sql server backups on the source server because of
> obvious reason (it would take too long). Therefore we cannot restore database
> in standby mode on the standby server. The only way to do database restore in
> our case is to use BCV software (disk subsystem on both servers is EMC), and
> that software doesn't have option od leaving database in standby mode. My
> question is "is there any way to put database into standby mode, regardless
> that is hasn't been restored using sql server native restore procedure?".
> Servers are sql server 2000, service pack 3.
> Thanks|||Transaction log backups are OK for this scenario. I would be able to backup
transaction log i.e. every 2 hours, copy tl backup to the new sever, and
restore it there. It is full database backup that I cannot apply in this
scenario, because it would take too long. So if I could set secondary
database which is restored using BCV (not native backup), to standby mode,
I'd be able to apply log shipping scenario.
Even LiteSpeed wouldn't help. This database doesn't have much text fields,
so I don't think that compression would be that good. Even if I'd get 50%
compression in database backup file and time needed to restore, it is still
500GB... BTW, BCV restore of 1TB database takes 2-3 hours. Using litespeed,
it would take hours to backup, hours to copy, and hours to restore...
"ChrisR" wrote:
> Even if you could put it into Standby mode, you wouldnt be able to apply
> additional changes without TLog backups. I dont know if there is an option of
> new software or not? If so, I wrote my own Log Shipping using SQL Lite Speed.
> "Pedja" wrote:
> > Hi,
> > We have specific problem here. We have very large database (1TB) on one
> > server, and we need to set up log shipping to another server. Problem is that
> > we don't make regular sql server backups on the source server because of
> > obvious reason (it would take too long). Therefore we cannot restore database
> > in standby mode on the standby server. The only way to do database restore in
> > our case is to use BCV software (disk subsystem on both servers is EMC), and
> > that software doesn't have option od leaving database in standby mode. My
> > question is "is there any way to put database into standby mode, regardless
> > that is hasn't been restored using sql server native restore procedure?".
> > Servers are sql server 2000, service pack 3.
> > Thanks|||We have a large database that we ship from a remote location as well. What I
did in this was to take a back up(Lite Speed) restore on stand by one time,
then continue with the logs. You do not have to do a full restore daily. When
you restore your transaction logs could you restore on stand by. (We use Lite
Speed Tranasction log as well). What we do is take a nightly backup, and copy
it over so that we have full back up for an emergency, but we never restore
them. It is harmless that backup takes long(you can schedule it for night
time). I have not noticed and significant performance trouble due to back up
in production.
> Transaction log backups are OK for this scenario. I would be able to backup
> transaction log i.e. every 2 hours, copy tl backup to the new sever, and
> restore it there. It is full database backup that I cannot apply in this
> scenario, because it would take too long. So if I could set secondary
> database which is restored using BCV (not native backup), to standby mode,
> I'd be able to apply log shipping scenario.
> Even LiteSpeed wouldn't help. This database doesn't have much text fields,
> so I don't think that compression would be that good. Even if I'd get 50%
> compression in database backup file and time needed to restore, it is still
> 500GB... BTW, BCV restore of 1TB database takes 2-3 hours. Using litespeed,
> it would take hours to backup, hours to copy, and hours to restore...
> "ChrisR" wrote:
> > Even if you could put it into Standby mode, you wouldnt be able to apply
> > additional changes without TLog backups. I dont know if there is an option of
> > new software or not? If so, I wrote my own Log Shipping using SQL Lite Speed.
> >
> > "Pedja" wrote:
> >
> > > Hi,
> > > We have specific problem here. We have very large database (1TB) on one
> > > server, and we need to set up log shipping to another server. Problem is that
> > > we don't make regular sql server backups on the source server because of
> > > obvious reason (it would take too long). Therefore we cannot restore database
> > > in standby mode on the standby server. The only way to do database restore in
> > > our case is to use BCV software (disk subsystem on both servers is EMC), and
> > > that software doesn't have option od leaving database in standby mode. My
> > > question is "is there any way to put database into standby mode, regardless
> > > that is hasn't been restored using sql server native restore procedure?".
> > > Servers are sql server 2000, service pack 3.
> > > Thanks

log shipping without backing up and restoring the whole database

Log Shipping with two production servers

I have two SQL Server databases, each with their own server. Is it suitable
to implement log shipping from each server to the other i.e. rather than
having the standard set up with a production server and a standby server, I
will set it up with two production servers 'swapping' logs...?
Hi,
Instead of "swapping logs" you might want to implement merge
replication, however you don't give a case for why you want to
implement log shipping. Is it for a hot standby (you could setup
clustering) or do you plan to utilise one server for reporting
purposes?
Also the official line from MS is that to implement log shipping both
SQL Server Editions should be Enterprise Version, however if you can
get hold of the SQL Server 2000 Resource Kit there is a Simple Log
Shipper Tool (basically two stored procs) on there. However with the
Enterprise methond it does monitor the log shipping for you. The only
disclaimer is that I could get SLS to work in a dev environment but not
in our production setup, so we went with snapshot replication.
Good Luck
Scott
|||Hi
You can't use log shipping for Scale Out as the destination DB can not be
used for write activities.
If you need to impliment something like scale out, look at replication, but
replication could use more resources than what you might gain by off loading
a server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike" wrote:

> I have two SQL Server databases, each with their own server. Is it suitable
> to implement log shipping from each server to the other i.e. rather than
> having the standard set up with a production server and a standby server, I
> will set it up with two production servers 'swapping' logs...?
>
>

Log Shipping with two production servers

I have two SQL Server databases, each with their own server. Is it suitable
to implement log shipping from each server to the other i.e. rather than
having the standard set up with a production server and a standby server, I
will set it up with two production servers 'swapping' logs...?Hi,
Instead of "swapping logs" you might want to implement merge
replication, however you don't give a case for why you want to
implement log shipping. Is it for a hot standby (you could setup
clustering) or do you plan to utilise one server for reporting
purposes?
Also the official line from MS is that to implement log shipping both
SQL Server Editions should be Enterprise Version, however if you can
get hold of the SQL Server 2000 Resource Kit there is a Simple Log
Shipper Tool (basically two stored procs) on there. However with the
Enterprise methond it does monitor the log shipping for you. The only
disclaimer is that I could get SLS to work in a dev environment but not
in our production setup, so we went with snapshot replication.
Good Luck
Scott|||Hi
You can't use log shipping for Scale Out as the destination DB can not be
used for write activities.
If you need to impliment something like scale out, look at replication, but
replication could use more resources than what you might gain by off loading
a server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike" wrote:

> I have two SQL Server databases, each with their own server. Is it suitabl
e
> to implement log shipping from each server to the other i.e. rather than
> having the standard set up with a production server and a standby server,
I
> will set it up with two production servers 'swapping' logs...?
>
>

Log Shipping with two production servers

I have two SQL Server databases, each with their own server. Is it suitable
to implement log shipping from each server to the other i.e. rather than
having the standard set up with a production server and a standby server, I
will set it up with two production servers 'swapping' logs...?Hi,
Instead of "swapping logs" you might want to implement merge
replication, however you don't give a case for why you want to
implement log shipping. Is it for a hot standby (you could setup
clustering) or do you plan to utilise one server for reporting
purposes?
Also the official line from MS is that to implement log shipping both
SQL Server Editions should be Enterprise Version, however if you can
get hold of the SQL Server 2000 Resource Kit there is a Simple Log
Shipper Tool (basically two stored procs) on there. However with the
Enterprise methond it does monitor the log shipping for you. The only
disclaimer is that I could get SLS to work in a dev environment but not
in our production setup, so we went with snapshot replication.
Good Luck
Scott|||Hi
You can't use log shipping for Scale Out as the destination DB can not be
used for write activities.
If you need to impliment something like scale out, look at replication, but
replication could use more resources than what you might gain by off loading
a server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike" wrote:
> I have two SQL Server databases, each with their own server. Is it suitable
> to implement log shipping from each server to the other i.e. rather than
> having the standard set up with a production server and a standby server, I
> will set it up with two production servers 'swapping' logs...?
>
>

Log Shipping with SQL Server 2000 STD Edition?

Hi SQL Server MVPs,
I just get into a sql server 2000 (std edition) environment, and they will
stay with the std edition for a while. Here are some questions about the Log
shipping:
1. Does SQL server 2000 STD version support Log Shipping?
(I know EE Edition and Developer Edition do.)
2. If tasks like the Transaction Log backup, transfering log from primary
server to secondary server, restore the log from the standby server were all
handled by scheduled scripts, and the syslogins was transfered by DTS, will
this approach work in a SQL server 2k std environment? Will the logins
resolving process work?
3. How can we setup the Log shipping monitor in SQL 2000 STD server?
Your expertise confirming information and any possible instruction documents
will be appreciated.
FD
Hi,
(a) Log-shipping is not supported in standard version of SQL server. However
I have read that basic log-shipping is supported in the standard version,
didn't get chance to get to know more about this.
(b) IMO, this approach should work, as long as the database is in the
NORECOVERY mode in the standby server.
HTH
Yogish
|||The Enterprise Edition (and dev) just have a wizard to set
it all up for you. It doesn't mean you can't write your own
version on any edition. You can find sample scripts in the
SQL Server Resource Kit - I think it's called Simple Log
Shipper. You can also find examples of implementations of
custom log shipping routines on various web sites.
You'll need to create your own method for monitoring log
shipping.
-Sue
On Wed, 16 Mar 2005 14:43:07 -0800, "Frank"
<Frank@.discussions.microsoft.com> wrote:

>Hi SQL Server MVPs,
>I just get into a sql server 2000 (std edition) environment, and they will
>stay with the std edition for a while. Here are some questions about the Log
>shipping:
>1. Does SQL server 2000 STD version support Log Shipping?
> (I know EE Edition and Developer Edition do.)
>2. If tasks like the Transaction Log backup, transfering log from primary
>server to secondary server, restore the log from the standby server were all
>handled by scheduled scripts, and the syslogins was transfered by DTS, will
>this approach work in a SQL server 2k std environment? Will the logins
>resolving process work?
>3. How can we setup the Log shipping monitor in SQL 2000 STD server?
>Your expertise confirming information and any possible instruction documents
>will be appreciated.
>FD
|||1) Yes. You have to set it up yourself, however.
2) Yes and Yes. By the way, you can move the logins using this method:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
3) To monitor the process you could use the jobs that perform the log
backups and restores. Have them email you if there is a failure. This is a
great way to email from SQL Server: XPSMTP --
http://sqldev.net/xp/xpsmtp.htm
You may find these KB articles helpful:
314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
http://support.microsoft.com/?id=314515
323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
Paper)
http://support.microsoft.com/?id=323135
325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
http://support.microsoft.com/?id=325220
821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
http://support.microsoft.com/?id=821786
321247 HOW TO: Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
Errors
http://support.microsoft.com/?id=329133
Keith
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:BBB0D86B-D4C9-4FFA-A496-C60FE8DEE0D3@.microsoft.com...
> Hi SQL Server MVPs,
> I just get into a sql server 2000 (std edition) environment, and they will
> stay with the std edition for a while. Here are some questions about the
Log
> shipping:
> 1. Does SQL server 2000 STD version support Log Shipping?
> (I know EE Edition and Developer Edition do.)
> 2. If tasks like the Transaction Log backup, transfering log from primary
> server to secondary server, restore the log from the standby server were
all
> handled by scheduled scripts, and the syslogins was transfered by DTS,
will
> this approach work in a SQL server 2k std environment? Will the logins
> resolving process work?
> 3. How can we setup the Log shipping monitor in SQL 2000 STD server?
> Your expertise confirming information and any possible instruction
documents
> will be appreciated.
> FD
|||All,
Thanks for the confirming information. I will start working in our testing
environment.
Frank
"Frank" wrote:

> Hi SQL Server MVPs,
> I just get into a sql server 2000 (std edition) environment, and they will
> stay with the std edition for a while. Here are some questions about the Log
> shipping:
> 1. Does SQL server 2000 STD version support Log Shipping?
> (I know EE Edition and Developer Edition do.)
> 2. If tasks like the Transaction Log backup, transfering log from primary
> server to secondary server, restore the log from the standby server were all
> handled by scheduled scripts, and the syslogins was transfered by DTS, will
> this approach work in a SQL server 2k std environment? Will the logins
> resolving process work?
> 3. How can we setup the Log shipping monitor in SQL 2000 STD server?
> Your expertise confirming information and any possible instruction documents
> will be appreciated.
> FD
sql

Log Shipping with SQL Server 2000 STD Edition?

Hi SQL Server MVPs,
I just get into a sql server 2000 (std edition) environment, and they will
stay with the std edition for a while. Here are some questions about the Log
shipping:
1. Does SQL server 2000 STD version support Log Shipping?
(I know EE Edition and Developer Edition do.)
2. If tasks like the Transaction Log backup, transfering log from primary
server to secondary server, restore the log from the standby server were all
handled by scheduled scripts, and the syslogins was transfered by DTS, will
this approach work in a SQL server 2k std environment? Will the logins
resolving process work?
3. How can we setup the Log shipping monitor in SQL 2000 STD server?
Your expertise confirming information and any possible instruction documents
will be appreciated.
FDHi,
(a) Log-shipping is not supported in standard version of SQL server. However
I have read that basic log-shipping is supported in the standard version,
didn't get chance to get to know more about this.
(b) IMO, this approach should work, as long as the database is in the
NORECOVERY mode in the standby server.
HTH
Yogish|||The Enterprise Edition (and dev) just have a wizard to set
it all up for you. It doesn't mean you can't write your own
version on any edition. You can find sample scripts in the
SQL Server Resource Kit - I think it's called Simple Log
Shipper. You can also find examples of implementations of
custom log shipping routines on various web sites.
You'll need to create your own method for monitoring log
shipping.
-Sue
On Wed, 16 Mar 2005 14:43:07 -0800, "Frank"
<Frank@.discussions.microsoft.com> wrote:

>Hi SQL Server MVPs,
>I just get into a sql server 2000 (std edition) environment, and they will
>stay with the std edition for a while. Here are some questions about the Lo
g
>shipping:
>1. Does SQL server 2000 STD version support Log Shipping?
> (I know EE Edition and Developer Edition do.)
>2. If tasks like the Transaction Log backup, transfering log from primary
>server to secondary server, restore the log from the standby server were al
l
>handled by scheduled scripts, and the syslogins was transfered by DTS, will
>this approach work in a SQL server 2k std environment? Will the logins
>resolving process work?
>3. How can we setup the Log shipping monitor in SQL 2000 STD server?
>Your expertise confirming information and any possible instruction document
s
>will be appreciated.
>FD|||1) Yes. You have to set it up yourself, however.
2) Yes and Yes. By the way, you can move the logins using this method:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
3) To monitor the process you could use the jobs that perform the log
backups and restores. Have them email you if there is a failure. This is a
great way to email from SQL Server: XPSMTP --
http://sqldev.net/xp/xpsmtp.htm
You may find these KB articles helpful:
314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
http://support.microsoft.com/?id=314515
323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
Paper)
http://support.microsoft.com/?id=323135
325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
http://support.microsoft.com/?id=325220
821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
http://support.microsoft.com/?id=821786
321247 HOW TO: Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
Errors
http://support.microsoft.com/?id=329133
Keith
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:BBB0D86B-D4C9-4FFA-A496-C60FE8DEE0D3@.microsoft.com...
> Hi SQL Server MVPs,
> I just get into a sql server 2000 (std edition) environment, and they will
> stay with the std edition for a while. Here are some questions about the
Log
> shipping:
> 1. Does SQL server 2000 STD version support Log Shipping?
> (I know EE Edition and Developer Edition do.)
> 2. If tasks like the Transaction Log backup, transfering log from primary
> server to secondary server, restore the log from the standby server were
all
> handled by scheduled scripts, and the syslogins was transfered by DTS,
will
> this approach work in a SQL server 2k std environment? Will the logins
> resolving process work?
> 3. How can we setup the Log shipping monitor in SQL 2000 STD server?
> Your expertise confirming information and any possible instruction
documents
> will be appreciated.
> FD|||All,
Thanks for the confirming information. I will start working in our testing
environment.
Frank
"Frank" wrote:

> Hi SQL Server MVPs,
> I just get into a sql server 2000 (std edition) environment, and they will
> stay with the std edition for a while. Here are some questions about the L
og
> shipping:
> 1. Does SQL server 2000 STD version support Log Shipping?
> (I know EE Edition and Developer Edition do.)
> 2. If tasks like the Transaction Log backup, transfering log from primary
> server to secondary server, restore the log from the standby server were a
ll
> handled by scheduled scripts, and the syslogins was transfered by DTS, wil
l
> this approach work in a SQL server 2k std environment? Will the logins
> resolving process work?
> 3. How can we setup the Log shipping monitor in SQL 2000 STD server?
> Your expertise confirming information and any possible instruction documen
ts
> will be appreciated.
> FD

Log Shipping with SQL Server 2000 STD Edition?

Hi SQL Server MVPs,
I just get into a sql server 2000 (std edition) environment, and they will
stay with the std edition for a while. Here are some questions about the Log
shipping:
1. Does SQL server 2000 STD version support Log Shipping?
(I know EE Edition and Developer Edition do.)
2. If tasks like the Transaction Log backup, transfering log from primary
server to secondary server, restore the log from the standby server were all
handled by scheduled scripts, and the syslogins was transfered by DTS, will
this approach work in a SQL server 2k std environment? Will the logins
resolving process work?
3. How can we setup the Log shipping monitor in SQL 2000 STD server?
Your expertise confirming information and any possible instruction documents
will be appreciated.
FDHi,
(a) Log-shipping is not supported in standard version of SQL server. However
I have read that basic log-shipping is supported in the standard version,
didn't get chance to get to know more about this.
(b) IMO, this approach should work, as long as the database is in the
NORECOVERY mode in the standby server.
HTH
Yogish|||The Enterprise Edition (and dev) just have a wizard to set
it all up for you. It doesn't mean you can't write your own
version on any edition. You can find sample scripts in the
SQL Server Resource Kit - I think it's called Simple Log
Shipper. You can also find examples of implementations of
custom log shipping routines on various web sites.
You'll need to create your own method for monitoring log
shipping.
-Sue
On Wed, 16 Mar 2005 14:43:07 -0800, "Frank"
<Frank@.discussions.microsoft.com> wrote:
>Hi SQL Server MVPs,
>I just get into a sql server 2000 (std edition) environment, and they will
>stay with the std edition for a while. Here are some questions about the Log
>shipping:
>1. Does SQL server 2000 STD version support Log Shipping?
> (I know EE Edition and Developer Edition do.)
>2. If tasks like the Transaction Log backup, transfering log from primary
>server to secondary server, restore the log from the standby server were all
>handled by scheduled scripts, and the syslogins was transfered by DTS, will
>this approach work in a SQL server 2k std environment? Will the logins
>resolving process work?
>3. How can we setup the Log shipping monitor in SQL 2000 STD server?
>Your expertise confirming information and any possible instruction documents
>will be appreciated.
>FD|||1) Yes. You have to set it up yourself, however.
2) Yes and Yes. By the way, you can move the logins using this method:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
3) To monitor the process you could use the jobs that perform the log
backups and restores. Have them email you if there is a failure. This is a
great way to email from SQL Server: XPSMTP --
http://sqldev.net/xp/xpsmtp.htm
You may find these KB articles helpful:
314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
http://support.microsoft.com/?id=314515
323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
Paper)
http://support.microsoft.com/?id=323135
325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
http://support.microsoft.com/?id=325220
821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
http://support.microsoft.com/?id=821786
321247 HOW TO: Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
Errors
http://support.microsoft.com/?id=329133
--
Keith
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:BBB0D86B-D4C9-4FFA-A496-C60FE8DEE0D3@.microsoft.com...
> Hi SQL Server MVPs,
> I just get into a sql server 2000 (std edition) environment, and they will
> stay with the std edition for a while. Here are some questions about the
Log
> shipping:
> 1. Does SQL server 2000 STD version support Log Shipping?
> (I know EE Edition and Developer Edition do.)
> 2. If tasks like the Transaction Log backup, transfering log from primary
> server to secondary server, restore the log from the standby server were
all
> handled by scheduled scripts, and the syslogins was transfered by DTS,
will
> this approach work in a SQL server 2k std environment? Will the logins
> resolving process work?
> 3. How can we setup the Log shipping monitor in SQL 2000 STD server?
> Your expertise confirming information and any possible instruction
documents
> will be appreciated.
> FD|||All,
Thanks for the confirming information. I will start working in our testing
environment.
Frank
"Frank" wrote:
> Hi SQL Server MVPs,
> I just get into a sql server 2000 (std edition) environment, and they will
> stay with the std edition for a while. Here are some questions about the Log
> shipping:
> 1. Does SQL server 2000 STD version support Log Shipping?
> (I know EE Edition and Developer Edition do.)
> 2. If tasks like the Transaction Log backup, transfering log from primary
> server to secondary server, restore the log from the standby server were all
> handled by scheduled scripts, and the syslogins was transfered by DTS, will
> this approach work in a SQL server 2k std environment? Will the logins
> resolving process work?
> 3. How can we setup the Log shipping monitor in SQL 2000 STD server?
> Your expertise confirming information and any possible instruction documents
> will be appreciated.
> FD

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 with clustering

We would like to implement 2 node active-passive clustering coupled
with log shipping. Clustering is for high availability and log
shipping for DR purpose. Do we have any white papers for this kind of
setup? Is it possible to host the log shiiping secondary database on
the inactive cluster server? Thouigh it doesn't server the DR purpose,
we are exploring all different methods. Please share your ideas
barbara wrote:
> We would like to implement 2 node active-passive clustering coupled
> with log shipping. Clustering is for high availability and log
> shipping for DR purpose. Do we have any white papers for this kind of
> setup?
SQL Server 2000 Failover Clustering
http://www.microsoft.com/technet/pro.../failclus.mspx
How to setup Log Shipping
http://support.microsoft.com/support...ppingFinal.asp
SQL Server Resource Kit (Part 4 is dedicated to Hig Availability)
http://www.microsoft.com/resources/d...s/default.mspx
SQL Server High Availability Resources
http://www.microsoft.com/sql/techinf...ailability.asp

> Is it possible to host the log shiiping secondary database on
> the inactive cluster server?
Yes, you can setup a stand-alone istance and ship one (or more) clustered
database to this istance but is preferable to setup an external box for log
shipping purpose

> Thouigh it doesn't server the DR purpose,
> we are exploring all different methods. Please share your ideas
Log Shipping can increase high availabilty because it reduce the time to
became operational when a disaster on primary server happen. In particular
Clustering
- high level of fault tolerance
- automatic failure
- high cost
Log shipping
- manual failure
- scale out (you can use secondary server as read-only server)
- low cost
Clustering increase the availability but Log Shipping reduce down-time if a
failure occur
Bye
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com
http://italy.mvps.org
|||Luca,
Thanks for the info. I've looked at all these articles before. If I
create standby on same server as cluser node (business doesn't want to
spend on another server), how do I failover to this? How does the
client connects to
standby? What name does it uses because the cluster node also exists
on the same server.
"Luca Bianchi" <rightjoinREMOVE_THIS@.hotmail.com> wrote in message news:<2qajjjFs3b8qU1@.uni-berlin.de>...
> barbara wrote:
> SQL Server 2000 Failover Clustering
> http://www.microsoft.com/technet/pro.../failclus.mspx
> How to setup Log Shipping
> http://support.microsoft.com/support...ppingFinal.asp
> SQL Server Resource Kit (Part 4 is dedicated to Hig Availability)
> http://www.microsoft.com/resources/d...s/default.mspx
> SQL Server High Availability Resources
> http://www.microsoft.com/sql/techinf...ailability.asp
>
> Yes, you can setup a stand-alone istance and ship one (or more) clustered
> database to this istance but is preferable to setup an external box for log
> shipping purpose
>
> Log Shipping can increase high availabilty because it reduce the time to
> became operational when a disaster on primary server happen. In particular
> Clustering
> - high level of fault tolerance
> - automatic failure
> - high cost
> Log shipping
> - manual failure
> - scale out (you can use secondary server as read-only server)
> - low cost
> Clustering increase the availability but Log Shipping reduce down-time if a
> failure occur
> Bye
|||I am confused? What would you gain in availability by log shipping to
another cluster node? I would log ship to another (possibly lower
performing) server in another location. Copying data in a tight circle
won't really gain anything in availability, which is the real goal here.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"barbara" <barbara_2003@.hotmail.com> wrote in message
news:5cd01256.0409091029.5a87adcc@.posting.google.c om...
> Luca,
> Thanks for the info. I've looked at all these articles before. If I
> create standby on same server as cluser node (business doesn't want to
> spend on another server), how do I failover to this? How does the
> client connects to
> standby? What name does it uses because the cluster node also exists
> on the same server.
>
> "Luca Bianchi" <rightjoinREMOVE_THIS@.hotmail.com> wrote in message
news:<2qajjjFs3b8qU1@.uni-berlin.de>...[vbcol=seagreen]
http://www.microsoft.com/technet/pro.../failclus.mspx[vbcol=seagreen]
http://support.microsoft.com/support...ppingFinal.asp[vbcol=seagreen]
http://www.microsoft.com/resources/d...s/default.mspx[vbcol=seagreen]
http://www.microsoft.com/sql/techinf...ailability.asp[vbcol=seagreen]
clustered[vbcol=seagreen]
log[vbcol=seagreen]
particular[vbcol=seagreen]
if a[vbcol=seagreen]
|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:epFRtxplEHA.2968@.TK2MSFTNGP14.phx.gbl...
> I am confused? What would you gain in availability by log shipping to
> another cluster node? I would log ship to another (possibly lower
> performing) server in another location. Copying data in a tight circle
> won't really gain anything in availability, which is the real goal here.
>
Actually I can think of a case.... physical disk corruption.
We have a DB where one block of data on teh disk has a bad CRC. This
prevents backups, etc.
We didn't realize the extent of the problem until too late so a restore from
a recent backup is not possible.
In this situation, something like what barbara's proposing might have been
very useful to us.
Having said that, log-shipping to a different machine is even better.
[vbcol=seagreen]
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "barbara" <barbara_2003@.hotmail.com> wrote in message
> news:5cd01256.0409091029.5a87adcc@.posting.google.c om...
> news:<2qajjjFs3b8qU1@.uni-berlin.de>...
of
>
http://www.microsoft.com/technet/pro.../failclus.mspx
>
http://support.microsoft.com/support...ppingFinal.asp
>
http://www.microsoft.com/resources/d...s/default.mspx[vbcol=seagreen]
> http://www.microsoft.com/sql/techinf...ailability.asp
> clustered
for[vbcol=seagreen]
> log
to
> particular
> if a
>
|||barbara wrote:
> Luca,
> Thanks for the info. I've looked at all these articles before. If I
> create standby on same server as cluser node (business doesn't want to
> spend on another server), how do I failover to this? How does the
> client connects to
> standby? What name does it uses because the cluster node also exists
> on the same server.
I agree completely with Geoff and Greg about their consideration. High
Availability has a cost. If you don't consider it, probably you don't need a
cluster solution.
By the way you have to refer to the clustered instance with its VIRTUAL
SERVER NAME that is different by the name of two node machine. The stand
alone instance could be referred by the traditional host name.
Imagine that you have a clustered node called NODE1 and the other node
called NODE2 (either with its own IP address). When you setup a clustered
(at operating system level) you have to assign, for the clustered, one
VIRTUAL SERVER NAME and one VIRTUAL IP ADDRESS (of the same class of both
node). When you install the virtual server instance of SQL Server you have
to provide another VIRTUAL SERVER NAME and another VIRTUAL IP ADDRESS for
SQL Server.
For example
The machine that form the cluster are:
NODE1 (10.0.0.1)
NODE2 (10.0.0.2)
When you create a clustered at Windows 2000/2003 level you can assign the
following name and ip address to the virtual server
W2KCLUSTER (10.0.0.3)
And then you setup SQL Server in a clustered instance creating a new virtual
server name with its virtual ip address
SQLCLUSTER (10.0.0.4)
If you install a stand-alone instance on NODE1 you can refer to the
clustered instance with the clustered instance name (that could be run on
NODE1 or NODE2) using its name or ip address (SQLCLUSTER or 10.0.0.4). If
you would connect to the stand alone instance you can connect to it without
considering the virtual server name but using NODE1.
Bye
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com
http://italy.mvps.org
|||Thanks for the good explanation. How do we change the client
configuration to point clients from SQLCLUSTER to node1? Oracle uses
LDAP/ONAMES for name resolution which is easier to change. Do we have
any for sql server? Is DNS name change is only option available? How
does it take care of caching at client level?
"Luca Bianchi" <rightjoinREMOVE_THIS@.hotmail.com> wrote in message news:<2qd1hrFueik4U1@.uni-berlin.de>...
> barbara wrote:
> I agree completely with Geoff and Greg about their consideration. High
> Availability has a cost. If you don't consider it, probably you don't need a
> cluster solution.
> By the way you have to refer to the clustered instance with its VIRTUAL
> SERVER NAME that is different by the name of two node machine. The stand
> alone instance could be referred by the traditional host name.
> Imagine that you have a clustered node called NODE1 and the other node
> called NODE2 (either with its own IP address). When you setup a clustered
> (at operating system level) you have to assign, for the clustered, one
> VIRTUAL SERVER NAME and one VIRTUAL IP ADDRESS (of the same class of both
> node). When you install the virtual server instance of SQL Server you have
> to provide another VIRTUAL SERVER NAME and another VIRTUAL IP ADDRESS for
> SQL Server.
> For example
> The machine that form the cluster are:
> NODE1 (10.0.0.1)
> NODE2 (10.0.0.2)
> When you create a clustered at Windows 2000/2003 level you can assign the
> following name and ip address to the virtual server
> W2KCLUSTER (10.0.0.3)
> And then you setup SQL Server in a clustered instance creating a new virtual
> server name with its virtual ip address
> SQLCLUSTER (10.0.0.4)
> If you install a stand-alone instance on NODE1 you can refer to the
> clustered instance with the clustered instance name (that could be run on
> NODE1 or NODE2) using its name or ip address (SQLCLUSTER or 10.0.0.4). If
> you would connect to the stand alone instance you can connect to it without
> considering the virtual server name but using NODE1.
> Bye
|||Currently there is no automagic client redirection. You can handle this in
a variety of ways. You can put the server portion of the conneciotn string
in a registry entry or an XML document and push changes otu to all the
clients. You can do a DNS alias redirect, but as you noted, there is the
client caching issue. This is one reason clustering is so useful. The
clients reconnect using the same connection string after a failover.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"barbara" <barbara_2003@.hotmail.com> wrote in message
news:5cd01256.0409100354.23e1b6a4@.posting.google.c om...
> Thanks for the good explanation. How do we change the client
> configuration to point clients from SQLCLUSTER to node1? Oracle uses
> LDAP/ONAMES for name resolution which is easier to change. Do we have
> any for sql server? Is DNS name change is only option available? How
> does it take care of caching at client level?
>
> "Luca Bianchi" <rightjoinREMOVE_THIS@.hotmail.com> wrote in message
news:<2qd1hrFueik4U1@.uni-berlin.de>...[vbcol=seagreen]
need a[vbcol=seagreen]
clustered[vbcol=seagreen]
both[vbcol=seagreen]
have[vbcol=seagreen]
for[vbcol=seagreen]
the[vbcol=seagreen]
virtual[vbcol=seagreen]
on[vbcol=seagreen]
If[vbcol=seagreen]
without[vbcol=seagreen]
|||That's the beauty of clustering. The client doesn't have to do anything,
except reconnect. The virtual server name and IP are both moved to the
other node. The client doesn't reference the node directly - only through
the virtual name and IP.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"barbara" <barbara_2003@.hotmail.com> wrote in message
news:5cd01256.0409100354.23e1b6a4@.posting.google.c om...
Thanks for the good explanation. How do we change the client
configuration to point clients from SQLCLUSTER to node1? Oracle uses
LDAP/ONAMES for name resolution which is easier to change. Do we have
any for sql server? Is DNS name change is only option available? How
does it take care of caching at client level?
"Luca Bianchi" <rightjoinREMOVE_THIS@.hotmail.com> wrote in message
news:<2qd1hrFueik4U1@.uni-berlin.de>...
> barbara wrote:
> I agree completely with Geoff and Greg about their consideration. High
> Availability has a cost. If you don't consider it, probably you don't need
a
> cluster solution.
> By the way you have to refer to the clustered instance with its VIRTUAL
> SERVER NAME that is different by the name of two node machine. The stand
> alone instance could be referred by the traditional host name.
> Imagine that you have a clustered node called NODE1 and the other node
> called NODE2 (either with its own IP address). When you setup a clustered
> (at operating system level) you have to assign, for the clustered, one
> VIRTUAL SERVER NAME and one VIRTUAL IP ADDRESS (of the same class of both
> node). When you install the virtual server instance of SQL Server you have
> to provide another VIRTUAL SERVER NAME and another VIRTUAL IP ADDRESS for
> SQL Server.
> For example
> The machine that form the cluster are:
> NODE1 (10.0.0.1)
> NODE2 (10.0.0.2)
> When you create a clustered at Windows 2000/2003 level you can assign the
> following name and ip address to the virtual server
> W2KCLUSTER (10.0.0.3)
> And then you setup SQL Server in a clustered instance creating a new
virtual
> server name with its virtual ip address
> SQLCLUSTER (10.0.0.4)
> If you install a stand-alone instance on NODE1 you can refer to the
> clustered instance with the clustered instance name (that could be run on
> NODE1 or NODE2) using its name or ip address (SQLCLUSTER or 10.0.0.4). If
> you would connect to the stand alone instance you can connect to it
without
> considering the virtual server name but using NODE1.
> Bye
|||Does microsoft know this problem? In enterprise environment, it is
very hard to push the DNS change to all clients. When they offer log
shipping, there should be an easy way for client redirection. I feel
this is is the biggest
drawback with sql server. How do you push XML or registry to all
clients? Do we need to have list of all clinet machine IPs?
Any other ideas in this subject is well appreciated.
Thanks
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<O6i6nwzlEHA.3608@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Currently there is no automagic client redirection. You can handle this in
> a variety of ways. You can put the server portion of the conneciotn string
> in a registry entry or an XML document and push changes otu to all the
> clients. You can do a DNS alias redirect, but as you noted, there is the
> client caching issue. This is one reason clustering is so useful. The
> clients reconnect using the same connection string after a failover.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "barbara" <barbara_2003@.hotmail.com> wrote in message
> news:5cd01256.0409100354.23e1b6a4@.posting.google.c om...
> news:<2qd1hrFueik4U1@.uni-berlin.de>...
> need a
> clustered
> both
> have
> for
> the
> virtual
> on
> If
> without

Log Shipping with clustered database.

Hi,
We are running SQL Server Failover cluster in Active/Passive mode.
Now, we want to setup DR server for this clustered database using
Log shipping.
But my concern is, can we do log shipping with clustered database ?
and the answer of this question is Yes then Please let me know How ?
Thanks & Regards,
Sajid C.
Yep...you can. Just like how you do it in a non-clustered instance. The only
problem you will have in here is if you want to be notified thru email if
your restore jobs fail as MAPI is not supported in a clustered environment.
MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
"Helping people grow and develop their full potential as God has plan for
them"
"csajid@.gmail.com" wrote:

> Hi,
> We are running SQL Server Failover cluster in Active/Passive mode.
> Now, we want to setup DR server for this clustered database using
> Log shipping.
> But my concern is, can we do log shipping with clustered database ?
> and the answer of this question is Yes then Please let me know How ?
>
> Thanks & Regards,
> Sajid C.
>
|||Hi,
Thanks for your reply.
Can you please provide me some document or any link for the same.
What are the necessary steps which should i take to do this task.
Thanks & Regards,
Sajid C.
bass_player wrote:[vbcol=seagreen]
> Yep...you can. Just like how you do it in a non-clustered instance. The only
> problem you will have in here is if you want to be notified thru email if
> your restore jobs fail as MAPI is not supported in a clustered environment.
> --
> MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
> "Helping people grow and develop their full potential as God has plan for
> them"
>
> "csajid@.gmail.com" wrote:

Log Shipping with clustered database.

Hi,
We are running SQL Server Failover cluster in Active/Passive mode.
Now, we want to setup DR server for this clustered database using
Log shipping.
But my concern is, can we do log shipping with clustered database ?
and the answer of this question is Yes then Please let me know How ?
Thanks & Regards,
Sajid C.Yep...you can. Just like how you do it in a non-clustered instance. The only
problem you will have in here is if you want to be notified thru email if
your restore jobs fail as MAPI is not supported in a clustered environment.
--
MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
"Helping people grow and develop their full potential as God has plan for
them"
"csajid@.gmail.com" wrote:
> Hi,
> We are running SQL Server Failover cluster in Active/Passive mode.
> Now, we want to setup DR server for this clustered database using
> Log shipping.
> But my concern is, can we do log shipping with clustered database ?
> and the answer of this question is Yes then Please let me know How ?
>
> Thanks & Regards,
> Sajid C.
>|||Hi,
Thanks for your reply.
Can you please provide me some document or any link for the same.
What are the necessary steps which should i take to do this task.
Thanks & Regards,
Sajid C.
bass_player wrote:
> Yep...you can. Just like how you do it in a non-clustered instance. The only
> problem you will have in here is if you want to be notified thru email if
> your restore jobs fail as MAPI is not supported in a clustered environment.
> --
> MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
> "Helping people grow and develop their full potential as God has plan for
> them"
>
> "csajid@.gmail.com" wrote:
> > Hi,
> >
> > We are running SQL Server Failover cluster in Active/Passive mode.
> >
> > Now, we want to setup DR server for this clustered database using
> > Log shipping.
> >
> > But my concern is, can we do log shipping with clustered database ?
> > and the answer of this question is Yes then Please let me know How ?
> >
> >
> > Thanks & Regards,
> > Sajid C.
> >
> >

Log Shipping with clustered database.

Hi,
We are running SQL Server Failover cluster in Active/Passive mode.
Now, we want to setup DR server for this clustered database using
Log shipping.
But my concern is, can we do log shipping with clustered database ?
and the answer of this question is Yes then Please let me know How ?
Thanks & Regards,
Sajid C.Yep...you can. Just like how you do it in a non-clustered instance. The onl
y
problem you will have in here is if you want to be notified thru email if
your restore jobs fail as MAPI is not supported in a clustered environment.
--
MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
"Helping people grow and develop their full potential as God has plan for
them"
"csajid@.gmail.com" wrote:

> Hi,
> We are running SQL Server Failover cluster in Active/Passive mode.
> Now, we want to setup DR server for this clustered database using
> Log shipping.
> But my concern is, can we do log shipping with clustered database ?
> and the answer of this question is Yes then Please let me know How ?
>
> Thanks & Regards,
> Sajid C.
>|||Hi,
Thanks for your reply.
Can you please provide me some document or any link for the same.
What are the necessary steps which should i take to do this task.
Thanks & Regards,
Sajid C.
bass_player wrote:[vbcol=seagreen]
> Yep...you can. Just like how you do it in a non-clustered instance. The o
nly
> problem you will have in here is if you want to be notified thru email if
> your restore jobs fail as MAPI is not supported in a clustered environment
.
> --
> MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
> "Helping people grow and develop their full potential as God has plan for
> them"
>
> "csajid@.gmail.com" wrote:
>

log shipping with cluster

is there any link about logshipping with windows 2000 cluster ?

Have you encountered a problem? These two technologies are independent of each other and shouldn't have problems when used together.|||

Hi Matt,

case closed, thank you for you response.

t tried to configure the log shipping from the passive node, not the owner, thats why it failed. thx.

sql

log shipping with cluster

is there any link about logshipping with windows 2000 cluster ?

Have you encountered a problem? These two technologies are independent of each other and shouldn't have problems when used together.|||

Hi Matt,

case closed, thank you for you response.

t tried to configure the log shipping from the passive node, not the owner, thats why it failed. thx.

Log Shipping with additional backups

I am planning to implement log shipping between two servers.
I understand that if I perform a full database backup of the primary
database during the log shipping, this will have no effect on the log
shipping. If I am log shipping every 30 minutes, so I have a log backup at
09:00 and 09:30, perform a full database backup at 09:15, the log backup at
09:30 will contain all changes from 09:00.
Can I use this full database backup and the log shipping logs to perform a
database recovery? Can I restore the 09:15 full backup and then the 09:30
log? Or, do I need to perform additional log backups, e.g. every hour? If
so, how do these log backups affect the log shipping backups?
Many ThanksYes you can use the log shipping log backups to do a recovery. You will
probably have to look at your log shipping scripts to make sure they are not
deleting the log backup files.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"si" <si@.discussions.microsoft.com> wrote in message
news:819CBAE2-0A90-42BB-8B2B-2B1DFF7DBE05@.microsoft.com...
>I am planning to implement log shipping between two servers.
> I understand that if I perform a full database backup of the primary
> database during the log shipping, this will have no effect on the log
> shipping. If I am log shipping every 30 minutes, so I have a log backup
> at
> 09:00 and 09:30, perform a full database backup at 09:15, the log backup
> at
> 09:30 will contain all changes from 09:00.
> Can I use this full database backup and the log shipping logs to perform a
> database recovery? Can I restore the 09:15 full backup and then the 09:30
> log? Or, do I need to perform additional log backups, e.g. every hour?
> If
> so, how do these log backups affect the log shipping backups?
> Many Thanks

Log Shipping with additional backups

I am planning to implement log shipping between two servers.
I understand that if I perform a full database backup of the primary
database during the log shipping, this will have no effect on the log
shipping. If I am log shipping every 30 minutes, so I have a log backup at
09:00 and 09:30, perform a full database backup at 09:15, the log backup at
09:30 will contain all changes from 09:00.
Can I use this full database backup and the log shipping logs to perform a
database recovery? Can I restore the 09:15 full backup and then the 09:30
log? Or, do I need to perform additional log backups, e.g. every hour? If
so, how do these log backups affect the log shipping backups?
Many ThanksYes you can use the log shipping log backups to do a recovery. You will
probably have to look at your log shipping scripts to make sure they are not
deleting the log backup files.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"si" <si@.discussions.microsoft.com> wrote in message
news:819CBAE2-0A90-42BB-8B2B-2B1DFF7DBE05@.microsoft.com...
>I am planning to implement log shipping between two servers.
> I understand that if I perform a full database backup of the primary
> database during the log shipping, this will have no effect on the log
> shipping. If I am log shipping every 30 minutes, so I have a log backup
> at
> 09:00 and 09:30, perform a full database backup at 09:15, the log backup
> at
> 09:30 will contain all changes from 09:00.
> Can I use this full database backup and the log shipping logs to perform a
> database recovery? Can I restore the 09:15 full backup and then the 09:30
> log? Or, do I need to perform additional log backups, e.g. every hour?
> If
> so, how do these log backups affect the log shipping backups?
> Many Thanks

Log Shipping with additional backups

I am planning to implement log shipping between two servers.
I understand that if I perform a full database backup of the primary
database during the log shipping, this will have no effect on the log
shipping. If I am log shipping every 30 minutes, so I have a log backup at
09:00 and 09:30, perform a full database backup at 09:15, the log backup at
09:30 will contain all changes from 09:00.
Can I use this full database backup and the log shipping logs to perform a
database recovery? Can I restore the 09:15 full backup and then the 09:30
log? Or, do I need to perform additional log backups, e.g. every hour? If
so, how do these log backups affect the log shipping backups?
Many Thanks
Yes you can use the log shipping log backups to do a recovery. You will
probably have to look at your log shipping scripts to make sure they are not
deleting the log backup files.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"si" <si@.discussions.microsoft.com> wrote in message
news:819CBAE2-0A90-42BB-8B2B-2B1DFF7DBE05@.microsoft.com...
>I am planning to implement log shipping between two servers.
> I understand that if I perform a full database backup of the primary
> database during the log shipping, this will have no effect on the log
> shipping. If I am log shipping every 30 minutes, so I have a log backup
> at
> 09:00 and 09:30, perform a full database backup at 09:15, the log backup
> at
> 09:30 will contain all changes from 09:00.
> Can I use this full database backup and the log shipping logs to perform a
> database recovery? Can I restore the 09:15 full backup and then the 09:30
> log? Or, do I need to perform additional log backups, e.g. every hour?
> If
> so, how do these log backups affect the log shipping backups?
> Many Thanks

log shipping will NOT work

I am trying to set up log shipping between some servers. They are in different domain, but there is a two way trust. And I couldn't get it to run inside one domain as well. The SQL agent and SQL server services are running under a domain account that has access to the shares. The jobs actually run but the "last run outcome" remains unknown and the "last run" says never. The error log for these jobs have nothing logged.

Am I missing something here? I have setup and torn this down a million times, and the same thing every time...nothing.

I can also manually backup a log to the Backup share across domains.

Does anyone have a clue what the heck is going on here?

All server are on 9.0.2047.

Thanks,
jason
do you mean to say theres no information in job history ? ? ?.....if the job has run then the sql server agent in either primary or secondary might have been in hanged state........just try to restart the sql server agent and try........anywasys you cud verify if it has run or not by manually logging into the shared folder in primary and see if the tran logs are present........and similarly in secondary check if the tran log files are copied in the secondary servers folder..........else you can check the same in the monitor server which states the recently restored log files in the secondary and the ones which were copied recently and last log file to be backed up in primary|||oh jeez, just a simple restart of the agent did the trick. Thanks for your time.
sql

Log Shipping will not restore tran logs on secondary server

We're planning to implement log shipping on our databases, and I have been toiling with it all weekend trying to get it to work on some test databases. The result is the same whether I do it via the wizard or manually via T-SQL.

I am using 3 servers, all SQL Server 2005 Standard SP1. All 3 SQL Servers are configured identically.

When I setup log shipping, it initializes with no problems. When it processes the first tran log file, it restores it with no problem. Every successive log file thereafter is not restored. No errors are generated. The only outright indication of a problem is that the monitor server shows that there has not been a recent restore.

The backup and copy both suceed. The restore claims to suceed. If I review the job history for each step, it says that it skipped the log file and then reports that it did not fina any log files to restore.

Message
2006-11-06 05:00:01.92 Skipped log backup file. Secondary DB: 'MyDemo', File: 'D:\MSSQL\Backup\MyDemo\MyDemo_20061106115619.trn'

Message
2006-11-06 05:00:01.95 Could not find a log backup file that could be applied to secondary database 'MyDemo'.
2006-11-06 05:00:01.96 The restore operation was successful. Secondary Database: 'MyDemo', Number of log backup files restored: 0

Any ideas or suggestions?

A little more info for you. I can manually restore the successive log file backups without any errors. However, the output says that there were 0 pages processed. The successive log files are being created empty. Well, these are test databases, so there are no active transactions ocurring, so no big surprise, right? I updated a lot of data in the tables, and the next log file is ....... empty.

So even with committed transactions ocurring, the tran log backup is still empty.

|||

I found the problem. It was a security setting that the Log Shipping wizard does not give you the ability to change. It defaulted to using Windows security (the account used by SQL Agent) for the connection to the Monitor server to update the state of the restore process. Because we use local system accounts, not domain accounts, for SQL Agent, it was not able to update the Monitor.

My scripts were based off of the first log shipping instance that I set up, so the option to change it there wasn’t included in the script generated by SQL Server. Since I originally set it up with the primary, secondary, and monitor on my local machine, it worked fine.

In the wizard, there is a setting for the account that the monitor should use, but it only affects the primary's connection to the monitor, not the secondary's connection.

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. Clustered Environment

I am embarking on a project to upgrade our SQL server environment. I'm curious for some opinions on the relative benefits of Log Shipping versus a Clustered environment and you have any experience that has helped form your opinion on the issue?
Thanks,
MarcMarc,

I think it depends on what you want to achieve. Log Shipping is useful if you want use a copy of your primary database for read-only queries as it reduces the load on your primary database, although this is not possible if you run Log Shipping continuously as the log shipping restore job requires that there be no connections in the database being log shipped to.
Also, you are building in tolerance against data loss - assuming that the database you are log shipping to is on a different physical disk or server.

You have to have a database in Full or Bulk Logged recovery mode before you can log ship it.

If you have a lot of inserts, updates and deletes occuring on your log shipped database, the size of the log shipping backups and restores can be considerably - something to think about if your network bandwidth is low. In this case you may want to look at using 3rd party log shipping software such as Quest Litespeed which can compress the backups.

Clustering is more of a protection against O/S failures or server failures. When the SQL Server service on the active node fails, is terminated unexpectedly or is shutdown, that is detected by the cluster service and it fails SQL Server (and any other cluster-aware services) over to the inactive node which then becomes the active node.

In SQL 2005 you also have the option of Mirroring which can be combined with clustering...although Microsoft didn't initially support the use of mirroring in a Production environment (so why have it as a feature you may ask!). Not sure whether it is now supported since SP2.

Regards
Lempster|||Lempster -

Can you tell me more about using clustering with mirroring. I'm exploring all of the options (clustering, mirroring, log shipping and replication) and am seeing the benefits of each. I can image the benefit of using clustering with mirroring, but I can't get my head around the actual implementation.

The clusters have to be pointing to a shared storage device. How do you then set it up to mirror? If you have a cluster [Cluster] with [Box A] and [Box B]. Can you set the mirror up to point to the [Cluster] as the principal so that it's not really worried about the individual boxes?

During my research on mirroring, it looks like Microsoft started supporting mirroring with SP1.

Thanks,

Marc|||Marc,

I've not implemented 'clustered mirroring' (for want of a better term), but there's a fair bit about it in SQL 2005 BOL. Essentially, you'd have the principal and mirror on different clusters. The following is taken from BOL:

"The principal server and mirror server both reside on clusters, with the principal server running on the virtual server of one cluster and the mirror server running on the virtual server of a different cluster.

If a cluster failover makes a principal server temporarily unavailable, client connections are disconnected from the database. After the cluster failover completes, clients can reconnect to the principal server on the same cluster or on a different cluster (or on an unclustered computer), depending on the operating mode."

Regards
Lempster

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 vs database mirroring

Hi Mvps:

Whats the major difference between Database mirroring and Log shipping?. I did read few differences but was told when it comes to applying the transaction logs on the secondary server , DB Mirroring has a diffferent methodology. Is that right?. Please clarify.

Thanks

Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Database mirroring supports only one miror for each principal database. Database mirroring also supports automatic failover if the principal database becomes unavailable. The mirror database is always offline in a recovering state, but you can create snapshots of the mirror database to provide read access for reporting, etc.

Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s). Log shipping supports an unlimited number of secondaries for each primary database.

Database mirroring is preferable to log shipping in most cases, although log shipping does have the following advantages:

1. it provides backup files as part of the process
2. multiple secondaries are supported
3. it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error

More information about both technologies is available in SQL Server 2005 Books Online in the topics "Understanding Log Shipping" and "Overview of Database Mirroring".

Phillip Garding
Senior Program Manager
Microsoft SQL Server

|||

Log shipping uses the tried and true restore logic.

Database mirroring uses a variation on the restore logic that is completely incorporated into the mirroring process. Plus redo is multi-threaded in Enterprise Edition.

|||

Hi Meher,

Other differences are listed below:

With Log Shipping:

Data Transfer: T-Logs are backed up and transferred to secondary server
Transactional Consistency: All committed and un-committed are transferred
Server Limitation: Can be applied to multiple stand-by servers
Failover: Manual
Failover Duration: Can take more than 30 mins
Role Change: Role change is manual
Client Re-direction: Manual changes required

With Database Mirroring:

Data Transfer: Individual T-Log records are transferred using TCP endpoints
Transactional Consistency: Only committed transactions are transferred
Server Limitation: Can be applied to only one mirror server
Failover: Automatic
Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds
Role Change: Role change is fully automatic
Client Re-direction: Fully automatic as it uses .NET 2.0

Hope this helps

Thank you,
Saleem Hakani
Saleem@.sqlnt.com

|||

thankx Saleem,

i would appreciate if you could help me. i am gloin to have my iterview in the comin weeks. so if you could

help me this problem i will b greatful... my email is is gibtronics2000@.gmail.com. The reply of yours is really helpful.

keep it up...

thankx

gangster

|||One important thing to keep in mind is that snapshots from db mirroring only works with Enterprise edition -- $$$ (especially if you need 2 or 3 copies of it). One last thing to consider is bi-directional transactional replication (this takes some thinking to have your schema support it correctly though).|||Saleem, Phillip

Phillip says "Mirroring is preferable to log shipping in most cases"...
Could I submit one case to you, please?

We have a web site for which we already have failover clustering but we want to setup some disaster recovery plan.
We will have cheaper hardware in a different location with exactly the same software and a "recent" copy of the database. We have connection between the two sites to keep our copy up-to-date.
A manual "switch" would be fine.

Although there is no plan for this, it might be useful to use the secondary database for reporting but I think this should be fine with both technologies (as we have Sql Server Enterprise Edition).

Could you advise between Asynchronous Mirroring and Log Shipping?

Thanks

Eric
|||

I have to disagree with Saleem on a few points.

Client Re-direction: Fully automatic as it uses .NET 2.0

It is only fully automatic IF the client app is using ADO.Net 2.0 (not all do) AND IF the developers have coded the connections correctly to take advantage of it. ADO.Net only takes adavatage of mirroring if you tell it the alternate server in the connection string.

Failover: Automatic

Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds

Role Change: Role change is fully automatic

Failover is only automatic if you are running mirroring in High-Safety mode with a Witness server that is up and functioning and able to contact the mirroring partner.

I should also add that failing back to the original primary server is not supported. If you want mirroring to return to the original primary server, as soon as it becomes available, you have to set up a job to do it. I set up a job that runs every minute on the mirroring server that checks for databases that are the primary mirror and that are in the synchronized state (Where sys.database_mirroring.mirroring_state = 4 And sys.database_mirroring.mirroring_role = 1) and fail it over.

Another point about mirroring is that the official recommendation by MSFT is to only mirror a maximum of 10 databases per server because each mirroring session consumes 2 threads. Logshipping does not have this limitation.

Yet another good point about mirroring is that you can mirror the publisher of merge replication and configure replication to automatically use the alternate database if it fails over. This is designed to replace Alternate Synchronizing partners in replication as Alternate Synchronizing partners is being deprecated.

You should also take note that you can do both mirroring and log shipping.

|||Robert,

Thanks for the info but it does not really help me in choosing...

Can I phrase my question slightly differently...

I don't see how log shipping could have a serious negative effect on my primary server as it's pretty simple in principle...
Is there anyway that Asynchronous Mirroring could have a negative impact on my Primary Server?

Eric

|||

Hi Robert,

I will be setting up Mirroring in the next few days. I cam across this thread and saw your comments:

"It is only fully automatic IF the client app is using ADO.Net 2.0 (not all do) AND IF the developers have coded the connections correctly to take advantage of it. ADO.Net only takes advantage of mirroring if you tell it the alternate server in the connection string."

Can you post sample code on connection string to do this? I believe our developers do not have this string comnnection option.

Another thing: "I should also add that failing back to the original primary server is not supported". Do you mind if you give me a bit of technique on how you do the job for failover to revert back to the primary node? This feature is new to me. I am not really into things like this. And one question, if the failover returns back to the primary server, do all transactions made in the mirrored database be automatically reflected on the primary?

Thanks!

Friday, March 30, 2012

log shipping without backing up and restoring the whole database

log shipping without backing up and restoring the whole database

log shipping without backing up and restoring the whole database

Log Shipping with two production servers

Log Shipping with two production servers

Log Shipping with two production servers

Log Shipping with SQL Server 2000 STD Edition?

Log Shipping with SQL Server 2000 STD Edition?

Log Shipping with SQL Server 2000 STD Edition?

Log Shipping with HOLD

Log Shipping with HOLD

Log Shipping with HOLD

Log shipping with clustering

Log Shipping with clustered database.

Log Shipping with clustered database.

Log Shipping with clustered database.

log shipping with cluster

log shipping with cluster

Log Shipping with additional backups

Log Shipping with additional backups

Log Shipping with additional backups

log shipping will NOT work

Log Shipping will not restore tran logs on secondary server

log shipping vs. Replication

log shipping vs. Replication

Log Shipping vs. Clustered Environment

Log Shipping vs Replication

Log Shipping Vs Replication

Log Shipping vs database mirroring

Log shipping alerts

Blog Archive

About Me