Log shipping alerts: Log Shipping Failover

Is it possible to failover a single or a selected number of databases
onto a standby server? We're currently log shipping about 12 databases
to a standby and I was wondering if it was possible to randomly
failover onto selected dbs.
My understanding of log shipping is that when the primary server goes
down, the standby takes over. But, does that mean that all of the db's
on the standby server now become the primary? Or, can failover occur on
selected dbs?
The reason I ask is because if one db on the primary, for example, was
to go down, I'd like to be able to bring the particular db on the
standby server online while I fix or restore what's on the primary
server. Can it be done individually or is it an all or nothing
scenario?
Your personal advice or links is greatly appreciated.
Thanks,
PepsThis is a multi-part message in MIME format.
--090006000402040507050100
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Log shipping pairs are at the individual database level. As such you
can change the log shipping roles of the primary & secondary for a
single DB if you like without affecting any other databases that happen
to be shipping their logs to or from the primary or secondary servers
(with sp_change_secondary_role, sp_change_monitor_role &
sp_change_primary_role or with the SQLEM GUI (ie. delete the logshipping
maint plan, RESTORE...WITH RECOVERY on the secondary and create a
logshipping maint plan (on the secondary) in the other direction)).
For more info see BOL:
How to set up and perform a log shipping role change
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp
--
*mike hodgson*
http://sqlnerd.blogspot.com
theSpinel wrote:
>Is it possible to failover a single or a selected number of databases
>onto a standby server? We're currently log shipping about 12 databases
>to a standby and I was wondering if it was possible to randomly
>failover onto selected dbs.
>My understanding of log shipping is that when the primary server goes
>down, the standby takes over. But, does that mean that all of the db's
>on the standby server now become the primary? Or, can failover occur on
>selected dbs?
>
>The reason I ask is because if one db on the primary, for example, was
>to go down, I'd like to be able to bring the particular db on the
>standby server online while I fix or restore what's on the primary
>server. Can it be done individually or is it an all or nothing
>scenario?
>
>Your personal advice or links is greatly appreciated.
>
>Thanks,
>Peps
>
>
--090006000402040507050100
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Log shipping pairs are at the individual database level. As such
you can change the log shipping roles of the primary & secondary
for a single DB if you like without affecting any other databases that
happen to be shipping their logs to or from the primary or secondary
servers </tt><tt>(with sp_change_secondary_role,
sp_change_monitor_role &
sp_change_primary_role or with the SQLEM GUI (ie. delete the
logshipping maint plan, RESTORE...WITH RECOVERY on the secondary and
create a logshipping maint plan (on the secondary) in the other
direction)). 
 
For more info see BOL: 
 
How to set up and perform a log shipping role change 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp</a> ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp</a> 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
theSpinel wrote:
<blockquote
cite="mid1148526142.572880.159390@.g10g2000cwb.googlegroups.com"
type="cite">
<pre wrap="">Is it possible to failover a single or a selected number of databases
onto a standby server? We're currently log shipping about 12 databases
to a standby and I was wondering if it was possible to randomly
failover onto selected dbs.
My understanding of log shipping is that when the primary server goes
down, the standby takes over. But, does that mean that all of the db's
on the standby server now become the primary? Or, can failover occur on
selected dbs?
The reason I ask is because if one db on the primary, for example, was
to go down, I'd like to be able to bring the particular db on the
standby server online while I fix or restore what's on the primary
server. Can it be done individually or is it an all or nothing
scenario?
Your personal advice or links is greatly appreciated.
Thanks,
Peps
</pre>
</blockquote>
</body>
</html>
--090006000402040507050100--|||Thanks, Mike.
I tested this on one of the db's but I received an error about not
finding the store procedure 'sp_change_primary_role.'
Any ideas?|||This is a multi-part message in MIME format.
--000806010409090003060507
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Those sp_change... procs are in the msdb database. You will need to
either switch to that database before running those procs (with the USE
statement) or explicitly state the full object name when executing it,
for example
exec msdb.dbo.sp_change_primary_role @.db_name='MyDB', ....;
Personally I would do this all with T-SQL statements, however it may be
easier for you to do it with the GUI:
1) manually start the SQLAgent "backup" job on the primary,
2) manually start the SQLAgent "copy" job on the primary,
3) delete the maint plan on the primary,
4) restore that log backup on the secondary DB using the WITH RECOVERY
option,
5) delete the SQLAgent "restore" job on the secondary;
6) then, optionally, you can establish the log shipping in the other
direction
just by going through the maint plan wizard on the secondary and
specifying
the old primary as the new secondary.
And don't forget to make sure the logins work properly on the secondary
(I always forget that bit), ie. that they exist (with the same passwords
as on primary) and that they're not orphaned. Up to you.
This BOL article is pretty concise in what you need to do (in
Transact-SQL) if you want to go the T-SQL way (just remember all the
sp_change... procs are in msdb (sp_resolve_logins is in master)) but
you'll need to go through the maint wizard afterwards to establish the
log shipping back in the other direction again (ie. step 6 above):
How to set up and perform a log shipping role change (Transact-SQL)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp
Hope this helps.
--
*mike hodgson*
http://sqlnerd.blogspot.com
theSpinel wrote:
>Thanks, Mike.
>I tested this on one of the db's but I received an error about not
>finding the store procedure 'sp_change_primary_role.'
>Any ideas?
>
>
--000806010409090003060507
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Those sp_change... procs are in the msdb database. You will need
to either switch to that database before running those procs (with the
USE statement) or explicitly state the full object name when executing
it, for example 
</tt>
<blockquote><tt>exec msdb.dbo.sp_change_primary_role @.db_name='MyDB',
....;</tt> 
</blockquote>
<tt>Personally I would do this all with T-SQL statements, however it
may be easier for you to do it with the GUI: 
1) manually start the SQLAgent "backup" job on the primary, 
2) manually start the SQLAgent "copy" job on the primary, 
3) delete the maint plan on the primary, 
4) restore that log backup on the secondary DB using the WITH
RECOVERY option, 
5) delete the SQLAgent "restore" job on the secondary; 
6) then, optionally, you can establish the log shipping in the other
direction 
just by going through the maint plan wizard on the secondary and
specifying 
the old primary as the new secondary. 
And don't forget to make sure the logins work properly on the secondary
(I always forget that bit), ie. that they exist (with the same
passwords as on primary) and that they're not orphaned. Up to you. 
 
This BOL article is pretty concise in what you need to do (in
Transact-SQL) if you want to go the T-SQL way (just remember all the
sp_change... procs are in msdb (sp_resolve_logins is in master)) but
you'll need to go through the maint wizard afterwards to establish the
log shipping back in the other direction again (ie. step 6 above): 
 
How to set up and perform a log shipping role change (Transact-SQL)</tt> 
<tt><a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp</a> ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp</a> 
 
Hope this helps. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
theSpinel wrote:
<blockquote
cite="mid1148539314.052466.229210@.i39g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">Thanks, Mike.
I tested this on one of the db's but I received an error about not
finding the store procedure 'sp_change_primary_role.'
Any ideas?
</pre>
</blockquote>
</body>
</html>
--000806010409090003060507--

Friday, March 9, 2012

Log Shipping Failover

No comments:

Post a Comment

Log shipping alerts

Blog Archive

About Me