How long to copy/replicate a database

My company is considering purchasing MS SQL Server to run an
application on (SASIxp). I am mainly familiar with Oracle, so I was
wondering how long it would take to copy a database. Basically we have
database A and each night we want to replace database B with the
contents of A. How long would this take say if we had a 10GB database
or a 20GB database.

What would be the technique to do this nightly, the Copy Database
Wizard, Snapshot Replication, Attach & Detach...? We need to automate
this process, and the source database can be made unavailable when
this happens.

Thanks,
Roger<OakRogbak_erPine@.yahoo.com> wrote in message
news:13fdc9b4.0310080623.7784f5b2@.posting.google.c om...
> My company is considering purchasing MS SQL Server to run an
> application on (SASIxp). I am mainly familiar with Oracle, so I was
> wondering how long it would take to copy a database. Basically we have
> database A and each night we want to replace database B with the
> contents of A. How long would this take say if we had a 10GB database
> or a 20GB database.

Well, your maximum speed is limited by hardware. And depending on how you
move things, you may get close to that speed.

> What would be the technique to do this nightly, the Copy Database
> Wizard, Snapshot Replication, Attach & Detach...? We need to automate
> this process, and the source database can be made unavailable when
> this happens.

Probably the easiest way given you can suffer from downtime is to do a
detach, copy, attach.

In that case you're pretty much limited to hardware speeds.

> Thanks,
> Roger|||OakRogbak_erPine@.yahoo.com Kill the 2 trees in email address to reply
(OakRogbak_erPine@.yahoo.com) writes:
> My company is considering purchasing MS SQL Server to run an
> application on (SASIxp). I am mainly familiar with Oracle, so I was
> wondering how long it would take to copy a database. Basically we have
> database A and each night we want to replace database B with the
> contents of A. How long would this take say if we had a 10GB database
> or a 20GB database.
> What would be the technique to do this nightly, the Copy Database
> Wizard, Snapshot Replication, Attach & Detach...? We need to automate
> this process, and the source database can be made unavailable when
> this happens.

An alternative is to use BACKUP/RESTORE. In this case, you can have
the databases available all the time.

How long it would take, depends on the hardware, but for a 10-20 GB, the
process would take something like 15-20 minutes on a decent machine. And
since you probably will backup the database anyway, only the restore
time would be extra.

Yet an alternative to consider is to apply transaction logs. Depending
on the transaction frequency, this can be a lot faster.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How large can SQLServer scale?

I'm considering options for a large scale data warehouse. Even though SQL can theorectially scale to 10 Terabytes plus, in practice - will it be able to do it? Has anyone else actually done it? Or should Oracle be used?This is blasphemy...

Oracle

Scale out not up...

Get some serious hardware and a lot of cash...

Why 10 terabytes...

What the subject matter?|||You get into a matter of what makes sense.

1 gigabyte is trivial. MSDE will eat that alive, and ask for more.

10 gigabytes is easy. It is beyone the range of MSDE, and probably needs somebody to casually watch over it.

100 gigabytes is more challenging, you need to at least pay attention to what you are doing. A full time dba starts to make sense.

1 terabyte requires serious planning. You need to think through what you are doing, have enterprise grade hardware, and leash the bozos so that they don't make a mess of your data.

10 terabytes is where the systems I know about start to peter out. There are a few dozen of them like Terraserver (http://terraserver.microsoft.com/) and Barnes and Noble (http://www.bn.com), but they aren't all that common.

160 terrabytes is the limit that I know about, although that isn't a public server. It has a small army of DBAs and developers, but it has run for about ten months without a minute of downtime so I'd say that it is "field tested" at the very least.

-PatP|||http://www.entmag.com/news/article.asp?EditorialsID=6032|||BIG BLUE

According to Winter Corp., the largest OLTP production database is an IBM DB2 cluster system run by Land Registry. That mainframe-based system is 18.3 terabytes.