Jeff,
Not sure you received this. I got a mail error returned when I sent
it.
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Davis, Ralph
Sent: Wednesday, July 27, 2005 11:20 PM
To: 'Jeff Bennett '; LazyDBA Discussion
Subject: RE: Log Shipping / Failover
Jeff,
Sure, pretty simple really
CREATE proc sp_BRP_DB_ACTIVATION AS
SET NOCOUNT ON
DECLARE @DBname SYSNAME
DECLARE @DBname_header varchar(75)
DECLARE @EXEC_STR varchar(255)
SET NOCOUNT ON
/***************************************************************************
**/
/***** PROCESS THE INDIVIDUAL DATABASES
*************/
/***************************************************************************
**/
DECLARE DBNAMES_CURSOR CURSOR FOR SELECT NAME FROM sysdatabases
WHERE status = 2098176 -- this is standby status for our databases
ORDER BY NAME
OPEN DBNAMES_CURSOR
FETCH NEXT FROM DBNAMES_CURSOR INTO @DBNAME
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @DBNAME_HEADER = '***** RECOVERING DATABASE
'+@DBNAME+' FROM STANDBY MODE TO WORKING STATUS'
SELECT @DBNAME_HEADER
SELECT @EXEC_STR = 'RESTORE DATABASE '+@DBname+'
WITH RECOVERY'
EXEC (@EXEC_STR)
END
FETCH NEXT FROM DBNAMES_CURSOR INTO @DBNAME
END
DEALLOCATE DBnames_cursor
SELECT '********* RUN COMPLETE - ALL DATABASES ACTIVATED TO PRODUCTION
MODE **********'
SET NOCOUNT OFF
Thanks,
Ralph W. Davis
working from home
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Jeff Bennett
[mailto:mssqldba-ezmlmshield-x51987184.[Email address protected]
Sent: Wednesday, July 27, 2005 9:02 PM
To: LazyDBA Discussion
Subject: RE: Log Shipping / Failover
Ralph,
Without giving away any corporate secrets, can you share the sp you mention
with us?
Thanks,
Jeff Bennett
St. Louis, MO
Davis Ralph <mssqldba-ezmlmshield-x74656362.[Email address protected] wrote:
Chris,
I don't think log shipping will handle your logins. User accounts
within the databases yes, but logins(stored in master) I don't think so. I
believe you still have to handle anything in master on your own. We have a
SP that puts new our logins on about 20 servers with the same SIDs and
passwords. That way no matter where we restore a databases our logins are
valid.
Now for bringing the server live there are multiple issues. You
will have to redirect(dns and wins) the original servername to the IP
address of the failover server. This involves flushing the cache on the
servers accessing the SQL box because they will have the name and old IP
stored. As far as bringing the databases live, we wrote a SP to read
sysdatabases and run a 'restore with recovery' against any database in
standby mode.
With this setup we can be live on the failover/BR server in less
than 2 minutes from the decision to go, if everyone is positioned to do
their job. It is a manual process.
That is the only plus to a cluster where the cutover and back is
less than 30 seconds, no need to play with dns/wins, or flush the cache.
However, it is more expensive and more temperamental, in my opinion. We
have both, in order to be politically correct, but the cluster has never
once failed over automatically even when we believe it should have. And we
still ship logs to a failover server and BR, even for the cluster, because
it still has a single point of failure(the disks).
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Chris Wood
[mailto:mssqldba-ezmlmshield-x26843537.[Email address protected]
Sent: Wednesday, July 27, 2005 9:23 AM
To: LazyDBA Discussion
Subject: Log Shipping / Failover
Hi,
Having setup log shipping, the scheduled job seems to be pumping down
the logs to a failover server should something catastrophic happen to my
main server. Noting that it will copy all of my logins, permissions and
objects... has anyone got a best suggested step by step process for
bringing this server 'live' ?
P.s. is this a better solution that using a form of replication if I
want it as best failover solution without implementing clustering?
Cheers
----------------------------------------------------------------------
Scope is a registered charity number 208231.
Visit our website at http://www.scope.org.uk
This message, and any file(s) transmitted with it are confidential
and are intended only for the person(s) to whom they have been
addressed by the sender. This message may contain confidential and/or
privileged material. If you are not the intended recipient of this
message, or if you believe it was transmitted to you in error, you are
required to delete the message and any copies of it, and to notify the
sender immediately. Any unauthorised disclosure, copying, distribution,
or printing of this message or accompanying files, or unauthorised use
of any information contained therein, by anyone other than the
intended recipient(s) is prohibited and may be unlawful.
Any views expressed in this message or in any file(s) transmitted with
it are those of the author, and may not necessarily represent the
views of Scope.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is
intended
solely for the person or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in reliance upon this
information by persons or entities other than the intended recipient is
prohibited. If you received this in error, please contact the sender and
delete
the material from any computer.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is intended
solely for the person or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in reliance upon this
information by persons or entities other than the intended recipient is
prohibited. If you received this in error, please contact the sender and delete
the material from any computer.
MS Sql Server LazyDBA home page