sql server replication:
[quoted text, click to view]

Look into Log-shipping.

Basically once you have restored the database on the second server, if done
correctly (WITH NORECOVERY) you can then apply logs from the 1st server
every N minutes.

So say you do a transaction log backup every 15 minutes on the first set of
servers.

You then continously apply these to the 2nd server every 15 minutes or so.

This means you never lose more than 15 minutes worth of data.

That can be done with your existing setup and some scripts which you can
generally find by googling for.


[quoted text, click to view]

It should, but I wouldn't recommend it. (Of course I wouldn't recommend 200
databases on a server.... but I don't know your business model. The only
company that I did see that it was a big mistake.)


[quoted text, click to view]

It is expensive.

[quoted text, click to view]

Basically you need some form of storage that can be shared among two or more
SQL Server Enterprise setups, can be a SAN, shared SCSI array, etc.

You can build a homegrown cluster w/o Enerprise, but it'll be messy and
you'll need to do a lot of the work yourself.

Hope that helps.


[quoted text, click to view]

I have to determine the best (affordable) physical architecture for the
following scenario.

We will have an eCommerce solution that has 200+ SQLServer databases running
on Windows 2000 server (normal server, not the Advanced or Datacentre
editions) and I need to determine the best solution for this with regard to
the following:
1 - it must have high availability
2 - it must have good redundancy
3 - it must have good data-backup procedures.

The existing design is simply to have these databases one two servers
(scaling out) that every night back up the local databases and copy them
onto the other server and restore them there. If one server dies, we can
resurrect the other databases up on the surviving server, albeit with up to
24 hours data missing.

I'm not happy with this since I don't think we should be happy with losing
up to 24 hours worth of data.

Various scenairos have been suggested, but I'm not sure what's the best one
to adopt.

One suggestion relies upon data replication, but my understanding is that
this won't work with 200 databases on 2 servers.

Another relies upon clustering. However, I understand that this requires
the Enterprise version and this is way too expensive for us (can only afford
Standard edition).

Another relies upon a SAN. Although expensive, this SAN is to be shared and
therefore the cost is effectively reduced. However, without the Enterprise
edition of SQLServer, I understand that one can't cluster the databases, so
if one dies another can't take over the data without the data files first
being detached (which of course they can't be because the server's not there
anymore for one to perform the detachment).

Any suggestions/advice please?

Many thanks in advance

Griff

Greg - A great help!

One further question though. I understand that backing up a big database
can be quite resource intensive. I presume restoring one could be equally
so. Our current plan would be to do these out of hours.

How resource intensive would backing up the log files be? Presumably, with
100 live databases, one wouldn't want them all kicking off
together....they'd need to be staggered presumably? If one finds the server
to be fairly near capacity (CPU, RAM) then would one want to do many small
log backups or fewer larger log backups?

Thanks

Griff

[quoted text, click to view]

You may also want to look at LiteSpeed from Imceda Software.

--

[quoted text, click to view]

"It depends".

We do hourly transaction backups on our major databases and the added disk
I/O and CPU is unnoticable to us.


[quoted text, click to view]

Actually if you use SQL Server's Database Maintenance Plans and select "All
User Databases" it'll automate a lot of the backup and it happens to kick
them off in a serial fashion.


[quoted text, click to view]

"It depends." If you do transaction log backups more often, they are
smaller and hence take less time, etc. If you wait, they get bigger, but
you may be able to schedule them for a better time.

Honestly, I'd base my schedule on business requirements..... what's the most
amount of data you can go with losing? 15 minutes? 1 hour? 6 hours? And
work from there.


[quoted text, click to view]

Greg

One further question if I may:

For the high-availability scenario, I would of course want there to be
minimal down time. To this end, I'd want to move the log file backups
(*.trn) across the network to my stand-by SQLServer and restore these log
files as and when they arrive.

Now, I don't know whether I'm inventing a problem here or not. If there are
going to be further log files restored, one has (as you pointed out) to
restore a log file with the "norecovery" option set. However, one doesn't
know that the next TRN file is going to arrive since the live server may be
lost at any moment. It may be that a log file is restored with norecovery,
and then the server suddenly becomes the LIVE server.

Presumably, the norecovery switch won't stop people updating the database?
However, I couldn't then start backing up that database with that option on
and shipping the logs to a third server? I'm not sure whether I can issue a
"with recovery" command without doing a restore?

I guess my question here is: on failure, how does one build in resiliancy
into the now-live "backup server"?

Thanks

Griff

Tibor

That's exactly what I wanted to know. Thanks!

One further point regarding backing up log files. My databases also get
updated by periodic large updates which use bulk inserts (no logging). Is
this compatible withthe FULL recovery option - i.e., will the updates
continue to occur without logging or will they get blocked because this
option is incompatible with the DB setting, or do they proceed with full
logging?

Thanks

Griff

Griff,

I'm not sure I understand your question properly. Are you asking: If I have a database restored WITH
NORECOVERY, now do I make it fully accessible, as it now will be the production database?

If so, just execute below command:
RESTORE DATABASE dbname WITH RECOVERY

Above doesn't actually do a restore, it just performs the recovery phase of the database to make it
fully accessible ("go live").

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


[quoted text, click to view]

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:%23myxPy0jEHA.3844@TK2MSFTNGP12.phx.gbl...
[quoted text, click to view]

To add to this, if one wishes, one can also add a STANDBY='filename' which
allows you to access the database in read-only mode. However, to apply
further logs, you lose the read-only ability while the log is being applied.

Griff,

Assuming that you are on SQL2K, whenever the database is in FULL recovery model, all operations are
fully logged. So, running a BCP (etc) will be fully logged in this case.

If you are in simple, you can't even perform log backup, so your (non-) options should be obvious
for this recovery model.

If you are in BULK_LOGGED recovery model, the operations will be minimally logged, but the next log
backup will not only include log records, but also data pages (the ones affected by the "bulk"
operation). I.e., that log backup can be huge, potentially larger than if you were running in FULL
recovery model.

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


[quoted text, click to view]