Few questions on Backups for warehouse

I am starting to load a data warehouse for a retention period of 10 years. My database backup plan is as follows -

1. Perform full back on Sunday.
2. Perform differential backup everyday from Mon - Sat.
3. Perform transaction log back-ups every hour on all days.

My recovery mode is going to be BULK-LOGGED at all times. I had a few questions / comments on the Maintenance Plan that I would be creating for the back-ups. My database name is Warehouse.

1. Differential Backups cannot be created via a Maintenance Plan. Only a full-backup gets created. Am I correct?

2. I shall be running Optimizations and Integrity checks prior to full-backup. Is this ok?

3. Remove files (both .BAK and .TRN) older than - I am thinking of having 6 days. I want only one full back-up at a time in the server. What settings can I use? I think the old back-up gets deleted when the new one is successful. What settings in the Maintainence Plan do I have to use to overwrite the previous back-up with the current one?

System Databases -

Should the settings for System Databases be the same as my Warehouse database?

The Maintenance Plan takes care of full-back up and TLOG back-ups. For Differential Backups I have to use the All Tasks from EM and specify the Differential Backup job. Correct?

All kinds of back-ups can occur in the database when it is active. Meaning, I have a job that loads data in the warehouse when a back-up is occurring simultaneously. Am I correct?

I do not intend to shrink the Transaction Log at any time, since it gets backed up every hour I do not expect it to grow to a large size. If I do have to shrink it, then I change the recover mode to Simple, shrink the log and then immediately do a full-backup and after that set the mode back to Bulk-Logged. Is the sequence of steps correct?

Please let me know your inputs.

Thanks,

Vivek1. Incorrect. Please refer to BOL.

2. Sure it's okay. It can be resource intensive so you may consider running during non-peak hours. Also, you should always run DBCC CHECKDB before a backup and if possible, after a backup completes. the sooner you find a problem, the better.

3. Delete all tran and diff backups after a successful full backup completes and has been written to another media (secondary copy) such as tape

IF YOU ARE RUNNING TRAN LOG BACKUPS, YOUR TRAN LOG GETS AUTOMATICALLY TRUNCATED. IN ANY CASE, YOU ARE RUNNING IN BULK MODEL. YOU REALLY SHOULD READ BOL.

Fast Hardware - Slow SQL

Hey All,
We've got a new 8-way SQL 2000 Server that pretty much
follows all of the Microsoft guidelines for performance
(i.e. the right kind of RAID arrays for both the
transaction and data volumes, etc). It was
configured/setup with Microsoft's sales support folks.
Although SQL runs okay, it doesn't run as fast as we'd
expect. One thing we notice is that processor/memory
utilization is almost always nothing.
As an example, if we're creating a blank database and
specify large file sizes, it could literally take
something like 6 hours to create a "blank" 100gb database
with a 20gb log file (on separate volumes).
We're also running a data conversion process as we're
upgrading our Microsoft Navision SQL server database and
although it runs faster than our older environment, the
system is still running overall at less than 1% CPU
utilization and maybe using 2gb of the available 8gb of
RAM.
I know my disk subsystem is fine as SQL can backup a 120gb
database to disk in less than 25 minutes. I've also
watched counters (like avg. disk. length queue) and they
are in the expected ranges.
How can I "force" SQL to run faster and utilize all the
hardware that it has available?
It's hard to believe that on such an expensive piece of
hardware it could still take 6 hours to create a blank
database!!!
Any help would be appreciated...
-davewhat aspect of performance are you interested in?
the blank db init is seriously off.
you should be able to init at a rate of ~50MB/sec per
file, on a big disk array with the db partitioned into
filegroups or files, you should be at a combined rate of
>200MB/sec, so for 120GB, 40min with single file and 10min
with 4 files.
8-way scalability is a tricky item, some SQL operations
scale very well (hash & merge joins), other ops scale very
poorly (RPC, etc)
i will be have article detailing some of these issues on
www.sql-server-performance.com in a few of weeks
also, are you large row count data modifications?
(inserts, updates & delete) check the above web site later
this week, there will be some very interesting items on
this matter
>--Original Message--
>Hey All,
>We've got a new 8-way SQL 2000 Server that pretty much
>follows all of the Microsoft guidelines for performance
>(i.e. the right kind of RAID arrays for both the
>transaction and data volumes, etc). It was
>configured/setup with Microsoft's sales support folks.
>Although SQL runs okay, it doesn't run as fast as we'd
>expect. One thing we notice is that processor/memory
>utilization is almost always nothing.
>As an example, if we're creating a blank database and
>specify large file sizes, it could literally take
>something like 6 hours to create a "blank" 100gb database
>with a 20gb log file (on separate volumes).
>We're also running a data conversion process as we're
>upgrading our Microsoft Navision SQL server database and
>although it runs faster than our older environment, the
>system is still running overall at less than 1% CPU
>utilization and maybe using 2gb of the available 8gb of
>RAM.
>I know my disk subsystem is fine as SQL can backup a
120gb
>database to disk in less than 25 minutes. I've also
>watched counters (like avg. disk. length queue) and they
>are in the expected ranges.
>How can I "force" SQL to run faster and utilize all the
>hardware that it has available?
>It's hard to believe that on such an expensive piece of
>hardware it could still take 6 hours to create a blank
>database!!!
>Any help would be appreciated...
>-dave
>.
>|||That does seem like a long time. What OS are you running? What kind of
SAN? Are you sure the drivers are the latest for that SAN / HBA?
--
Andrew J. Kelly
SQL Server MVP
"Dave Rose" <anonymous@.discussions.microsoft.com> wrote in message
news:1171c01c3f4ec$553e47b0$a401280a@.phx.gbl...
> Hey All,
> We've got a new 8-way SQL 2000 Server that pretty much
> follows all of the Microsoft guidelines for performance
> (i.e. the right kind of RAID arrays for both the
> transaction and data volumes, etc). It was
> configured/setup with Microsoft's sales support folks.
> Although SQL runs okay, it doesn't run as fast as we'd
> expect. One thing we notice is that processor/memory
> utilization is almost always nothing.
> As an example, if we're creating a blank database and
> specify large file sizes, it could literally take
> something like 6 hours to create a "blank" 100gb database
> with a 20gb log file (on separate volumes).
> We're also running a data conversion process as we're
> upgrading our Microsoft Navision SQL server database and
> although it runs faster than our older environment, the
> system is still running overall at less than 1% CPU
> utilization and maybe using 2gb of the available 8gb of
> RAM.
> I know my disk subsystem is fine as SQL can backup a 120gb
> database to disk in less than 25 minutes. I've also
> watched counters (like avg. disk. length queue) and they
> are in the expected ranges.
> How can I "force" SQL to run faster and utilize all the
> hardware that it has available?
> It's hard to believe that on such an expensive piece of
> hardware it could still take 6 hours to create a blank
> database!!!
> Any help would be appreciated...
> -dave