Standby server

I have a very specific question about copying a database to another server.
We have a database set up on a primary server. The same database has been
set up on an identical standby server. SQL has been switched off on the
standby, and we replicate the database and log files, for this database
only, from the main server to the standby server.
If we want to switch to the standby server, do we need to go through the
Attach or Restore process, or can we just start SQL with data and log files
that are different from what it started with? What specifically would be
wrong with this?
Thanks for any advice you can offer,
Anthony Yates
PS I am familiar with Attach/Detach and Backup/Restore
PPS I know that my logins will need to be reassigned before users use the
database

Are you stopping the primary SQL Server when you copy (replicate) the databa
se files? If not, the
solution is definitely not supported, and I doubt that it will work. Even if
 you stop the SQL
Server, you are using an unsupported solution. Attach is only guaranteed to 
work if you detach
first. And only starting the other SQL Server might work, but again, it isn'
t supported.

Use Log Shipping instead: http://www.microsoft.com/technet/pr...oy/sqlhalp.mspx


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"Anthony Yates" <anthony.spam@spammedout.com> wrote in message
news:uZ%23jSTbwFHA.2312@TK2MSFTNGP14.phx.gbl...
>I have a very specific question about copying a database to another server.
> We have a database set up on a primary server. The same database has been 
set up on an identical
> standby server. SQL has been switched off on the standby, and we replicate
 the database and log
> files, for this database only, from the main server to the standby server.
> If we want to switch to the standby server, do we need to go through the A
ttach or Restore
> process, or can we just start SQL with data and log files that are differe
nt from what it started
> with? What specifically would be wrong with this?
> Thanks for any advice you can offer,
> Anthony Yates
> PS I am familiar with Attach/Detach and Backup/Restore
> PPS I know that my logins will need to be reassigned before users use the 
database
>

I agree unless there is some technical reason not too Log shipping would be
a better fit.  However, we also have cases where ripping a coy of an active
OLTP away with the SAN and presenting that copy to another SQL Server works
well.  One such case we do this every day on a 1TB+ size database and it
works.  So if you primary fails and you have copies of all the data and log
files, attaching a copy of them on another SQL Server (same build) will
work.  It will just go through a recovery period for rollback and
rollforward operations.


"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:upH$KqbwFHA.3756@tk2msftngp13.phx.gbl...
> Are you stopping the primary SQL Server when you copy (replicate) the
> database files? If not, the solution is definitely not supported, and I
> doubt that it will work. Even if you stop the SQL Server, you are using an
> unsupported solution. Attach is only guaranteed to work if you detach
> first. And only starting the other SQL Server might work, but again, it
> isn't supported.
>
> Use Log Shipping instead:
> http://www.microsoft.com/technet/pr...oy/sqlhalp.mspx
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "Anthony Yates" <anthony.spam@spammedout.com> wrote in message
> news:uZ%23jSTbwFHA.2312@TK2MSFTNGP14.phx.gbl... 
>

Thanks for the replies. We are using Legato Replistor to make a replica of
the data and log files of a Sun database on SQL.
I am wondering if it is OK just to start the standby SQL with the updated
data and log files, or whether you need to go through the Attach process.
Does the standby SQL care if the data and log files are different (e.g a
different size) from when it last ran? We only replicate the Sun database,
not model, master, msdb, temp
Thanks
Anthony


"Danny" <someone@nowhere.com> wrote in message
news:DgyZe.4233$kH3.356@trnddc01...
>I agree unless there is some technical reason not too Log shipping would be
>a better fit.  However, we also have cases where ripping a coy of an active
>OLTP away with the SAN and presenting that copy to another SQL Server works
>well.  One such case we do this every day on a 1TB+ size database and it
>works.  So if you primary fails and you have copies of all the data and log
>files, attaching a copy of them on another SQL Server (same build) will
>work.  It will just go through a recovery period for rollback and
>rollforward operations.
>
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote
> in message news:upH$KqbwFHA.3756@tk2msftngp13.phx.gbl... 
>
>

Does Legato Replistor provide a consistent copy of the files?  From a SAN
perspective consistent files is that all files in the copy process are
written to two places on othe disk with at dual write process.  The data and
log files must be exactly in sync.  I don't know Legato Replistor.  If it's
software I'd be suspisous that it can provide both performance and
consistenty.  As for your quetion of just starting the SQL Server, either
way SQL Server will go through a recovery process and do roll back and roll
forward operations.  However, there are potential changes in master that
could cause the non attach scenario to fail.  I suggest you leave the SQL
Server running, attach your database, and fix you logins in a script.


"Anthony Yates" < anthonyDINGyates@air
DONGdesk.com> wrote in message
 news:%23JKY%23unwFHA
.2792@tk2msftngp13.phx.gbl...
> Thanks for the replies. We are using Legato Replistor to make a replica of
> the data and log files of a Sun database on SQL.
> I am wondering if it is OK just to start the standby SQL with the updated
> data and log files, or whether you need to go through the Attach process.
> Does the standby SQL care if the data and log files are different (e.g a
> different size) from when it last ran? We only replicate the Sun database,
> not model, master, msdb, temp
> Thanks
> Anthony
>
>
> "Danny" <someone@nowhere.com> wrote in message
> news:DgyZe.4233$kH3.356@trnddc01... 
>
>

Thanks very much.
Replistor is suppose to provide an exact copy, but I have never understood
how it is consistent. We store the log file on a different disk from the
data file, so they are synchronised in two different "specifications" (or
definitions) within Replistor. The replistor documentation talks about
replicating the whole SQL Data directory, but that asumes you have a very
simple setup. You clearly don't need to replicate Model, and I guess not
Temp either. The replication is asynchronous, but I have assumed that
Replistor keeps an internal clock of exactly what disk sector changed when.
As it happened, in this case we shut down SQL on the Source server, so there
should not have been any further changes going on.
I think you are right that the best way to approach this is to leave SQL
running but with the database unattached, then just attach it on failover.
However I would really like to know if there is actually anything wrong with
the practice of replicating the log and data files. The hypotheses I am
working on are:
- Maybe you need to replicate master and msdb as well. But I can't think of
what information they contain that you would need, asssuming the two SQL
servers are configured identically
- Maybe the database turning up as a new size causes an internal error while
SQL grows the disk space
-Maybe Replistor (older version) just has bugs
Thanks,
Anthony





"Danny" <someone@nowhere.com> wrote in message
news:UPa_e.4449$211.824@trnddc08...
> Does Legato Replistor provide a consistent copy of the files?  From a SAN
> perspective consistent files is that all files in the copy process are
> written to two places on othe disk with at dual write process.  The data
> and log files must be exactly in sync.  I don't know Legato Replistor.  If
> it's software I'd be suspisous that it can provide both performance and
> consistenty.  As for your quetion of just starting the SQL Server, either
> way SQL Server will go through a recovery process and do roll back and
> roll forward operations.  However, there are potential changes in master
> that could cause the non attach scenario to fail.  I suggest you leave the
> SQL Server running, attach your database, and fix you logins in a script.
>
>
> "Anthony Yates" < anthonyDINGyates@air
DONGdesk.com> wrote in message
>  news:%23JKY%23unwFHA
.2792@tk2msftngp13.phx.gbl... 
>
>

Let me re-emphasis that this is an *unsupported* configuration by MS (unless
 the vendor has some
paper from MS stating otherwise). What happens if you need to fail over and 
something doesn't work?
Who will you call? What SQL Server expertise does the Replistor vendor have?
 Who do they talk to?
What deal do they have with MS?

Even of you do decide to go by this solution, you should definitely do frequ
ent database and
transaction log backups.


> - Maybe you need to replicate master and msdb as well. But I can't think o
f what information they
> contain that you would need, asssuming the two SQL servers are configured identica
lly

Anything in the system tables in those databases.
In master, you have things such as logins, linked servers, linked server log
ins, sp_configure
settings, user defines error messages, user created system procedures.
In msdb, you have things as jobs, alerts, operators.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"Anthony Yates" < anthonyDINGyates@air
DONGdesk.com> wrote in message
news:%23wGlvC4wFHA.1276@tk2msftngp13.phx.gbl...
> Thanks very much.
> Replistor is suppose to provide an exact copy, but I have never understood
 how it is consistent.
> We store the log file on a different disk from the data file, so they are 
synchronised in two
> different "specifications" (or definitions) within Replistor. The replisto
r documentation talks
> about replicating the whole SQL Data directory, but that asumes you have a
 very simple setup. You
> clearly don't need to replicate Model, and I guess not Temp either. The re
plication is
> asynchronous, but I have assumed that Replistor keeps an internal clock of
 exactly what disk
> sector changed when.
> As it happened, in this case we shut down SQL on the Source server, so the
re should not have been
> any further changes going on.
> I think you are right that the best way to approach this is to leave SQL r
unning but with the
> database unattached, then just attach it on failover. However I would real
ly like to know if there
> is actually anything wrong with the practice of replicating the log and da
ta files. The hypotheses
> I am working on are:
> - Maybe you need to replicate master and msdb as well. But I can't think o
f what information they
> contain that you would need, asssuming the two SQL servers are configured 
identically
> - Maybe the database turning up as a new size causes an internal error whi
le SQL grows the disk
> space
> -Maybe Replistor (older version) just has bugs
> Thanks,
> Anthony
>
>
>
>
>
> "Danny" <someone@nowhere.com> wrote in message news:UPa_e.4449$211.824@trn
ddc08... 
>
>

Tibor,
This is what I am trying to get to the bottom of:
"In master, you have things such as logins, linked servers, linked server
logins, sp_configure settings, user defines error messages, user created
system procedures" - nothing there I can think of that would cause
corruption. We fix the logins after starting the standby SQL. The two copies
of SQL start off identical.
"In msdb, you have things as jobs, alerts, operators" -also things to fix up
afterwards, nothing to cause corruption.

My basic question is: what is the difference between:
1) Copying the data and log files and attaching them to the instance of SQL,
and
2) Copying the data and log files over the top of a previously-attached
database and restarting SQL?

The obvious thing is that the size of the database would be different, but I
can't see anything in the master or msdb that defines the size.
Any ideas most welcome,
Anthony





"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:%23nsv5K5wFHA.3252@TK2MSFTNGP10.phx.gbl...
> Let me re-emphasis that this is an *unsupported* configuration by MS
> (unless the vendor has some paper from MS stating otherwise). What happens
> if you need to fail over and something doesn't work? Who will you call?
> What SQL Server expertise does the Replistor vendor have? Who do they talk
> to? What deal do they have with MS?
>
> Even of you do decide to go by this solution, you should definitely do
> frequent database and transaction log backups.
>
> 
>
> Anything in the system tables in those databases.
> In master, you have things such as logins, linked servers, linked server
> logins, sp_configure settings, user defines error messages, user created
> system procedures.
> In msdb, you have things as jobs, alerts, operators.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "Anthony Yates" < anthonyDINGyates@air
DONGdesk.com> wrote in message
> news:%23wGlvC4wFHA.1276@tk2msftngp13.phx.gbl... 
>

Replistor are a proper supported solution:
http://www.microsoft.com/servicepro...ers/legato.mspx
Veritas Volume Replicator is another:
http://www.microsoft.com/windowsser...s/showcase.mspx

Anthony


"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:%23nsv5K5wFHA.3252@TK2MSFTNGP10.phx.gbl...
> Let me re-emphasis that this is an *unsupported* configuration by MS
> (unless the vendor has some paper from MS stating otherwise). What happens
> if you need to fail over and something doesn't work? Who will you call?
> What SQL Server expertise does the Replistor vendor have? Who do they talk
> to? What deal do they have with MS?
>
> Even of you do decide to go by this solution, you should definitely do
> frequent database and transaction log backups.
>
> 
>
> Anything in the system tables in those databases.
> In master, you have things such as logins, linked servers, linked server
> logins, sp_configure settings, user defines error messages, user created
> system procedures.
> In msdb, you have things as jobs, alerts, operators.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "Anthony Yates" < anthonyDINGyates@air
DONGdesk.com> wrote in message
> news:%23wGlvC4wFHA.1276@tk2msftngp13.phx.gbl... 
>

Of course Tibor is correct.  Good advice.

Now about implementing the risky solution...  First don't replicate master
unless everything is exactly the same on the server, this included the
server name.  Two things come to mind in master, the DB id associated with
the database and the file definitions in sysaltfiles.

Again I'm suspicious of maintaining file consistency.


"Anthony Yates" <anthony.spam@spammedout.com> wrote in message
news:eAV5Eb6wFHA.664@tk2msftngp13.phx.gbl...
> Tibor,
> This is what I am trying to get to the bottom of:
> "In master, you have things such as logins, linked servers, linked server
> logins, sp_configure settings, user defines error messages, user created
> system procedures" - nothing there I can think of that would cause
> corruption. We fix the logins after starting the standby SQL. The two
> copies of SQL start off identical.
> "In msdb, you have things as jobs, alerts, operators" -also things to fix
> up afterwards, nothing to cause corruption.
>
> My basic question is: what is the difference between:
> 1) Copying the data and log files and attaching them to the instance of
> SQL, and
> 2) Copying the data and log files over the top of a previously-attached
> database and restarting SQL?
>
> The obvious thing is that the size of the database would be different, but
> I can't see anything in the master or msdb that defines the size.
> Any ideas most welcome,
> Anthony
>
>
>
>
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote
> in message news:%23nsv5K5wFHA.3252@TK2MSFTNGP10.phx.gbl... 
>
>