is it good idea to replicate sql server db files?

[jaekim]

Hi.

I am wondering if it is a good idea to replicate sql server db files
using frs.

I don't really know how the frs works, so
does frs replicates the whole database from time to time or just the
portion that is changed?

Also if the db is expected to change very often, and wouldn't it make
the whole system down?

I wonder if it's a good idea just to make a backup of the database and
copy it.

What's the usual practice?

[Erland Sommarskog]

Re: is it good idea to replicate sql server db files?

[posted and mailed, please reply in news]

jaekim (jkim65@socal.r r.com) writes:[color=blue]
> I am wondering if it is a good idea to replicate sql server db files
> using frs.
>
> I don't really know how the frs works, so
> does frs replicates the whole database from time to time or just the
> portion that is changed?
>
> Also if the db is expected to change very often, and wouldn't it make
> the whole system down?
>
> I wonder if it's a good idea just to make a backup of the database and
> copy it.
>
> What's the usual practice?[/color]

I'm uncertain of what your question actually is, and whatever I have never
heard of frs.

You talk about replication, but your question seems to be about backup.
Replication and backup are two quite different things.

To backup a database, you use the BACKUP command in T-SQL. There are three
ways to back up a database:

* Full backup, backup the entire database.
* Differential backup, back up the changes since the last full backup.
* Log backup, backs up the *transaction log*.

Normally you use both Full backup and Log backup. By backing up the
transaction log, you can get up-to-the-minute recovery in case of a
crash (which could be a fatal human error).

To be able to backup the transaction log you must run in Full or Bulk-logged
recovery mode. On the other hand, if you run in these modes, you must
backup the transaction log, or the log will eventually fill your disk.

It is important to understand that SQL's BACKUP command knows about
transactions, and thus you can backup the database while there is
activity in it. If you would just copy the database files outside SQL
Server, you might get a useless set of bytes on the tape.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Greg D. Moore \(Strider\)]

Re: is it good idea to replicate sql server db files?


"jaekim" <jkim65@socal.r r.com> wrote in message
news:91d0d16b.0 407040210.7116f 55d@posting.goo gle.com...[color=blue]
> Hi.
>
> I am wondering if it is a good idea to replicate sql server db files
> using frs.
>
> I don't really know how the frs works, so
> does frs replicates the whole database from time to time or just the
> portion that is changed?
>
> Also if the db is expected to change very often, and wouldn't it make
> the whole system down?
>
> I wonder if it's a good idea just to make a backup of the database and
> copy it.[/color]


I would NOT trust FRS to replicate my database.

Either as Erland suggests use BACKUP and RESTORE (look up log-shipping) or
use SQL Server's replication.

[color=blue]
>
> What's the usual practice?[/color]

[Ross Presser]

Re: is it good idea to replicate sql server db files?

On Sun, 4 Jul 2004 12:51:28 +0000 (UTC), Erland Sommarskog wrote:

[color=blue]
> I'm uncertain of what your question actually is, and whatever I have never
> heard of frs.
>
> You talk about replication, but your question seems to be about backup.
> Replication and backup are two quite different things.
>[/color]
FRS refers to File Replication Service, provided by Windows 2000 Server and
Windows Server 2003. It basically does the same thing as rsync: copy file
changes from one server to another on a scheduled basis.

Some details:

http://www.microsoft.com/windows2000/techinfo/reskit/samplechapters/dsdh/dsdh_frs_ncpi.asp

Your recommendations are of course correct: Database replication is best
handled by the database server, not the filesystem.