How much time it usually takes to create a DR plan?

Hi,
In your opinion, how much time would be a reasonable amount of time for a
DBA to spend on the creation of a DR plan for a set of two MS SQL Server 2000
databases (OSes, and so on...) - one production and one standby, without
using replication, log shipping, or cluster, but only SAN storage block level
synchronization?
-- Hoping this makes any sense Oskar.You omitted almost all DR elements from the list :)
It depends on your environment. You just did not add your list Database
Mirroring however this is not applicable in SQL Server 2000 already.
So, what kind of a DR is this? A Cold-Standby? Like Backup\Restore or
Attach\Detach?
--
Ekrem Ã?nsoy
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:D6ECA2B1-D6DE-4FD4-A983-2971115693F1@.microsoft.com...
> Hi,
> In your opinion, how much time would be a reasonable amount of time for a
> DBA to spend on the creation of a DR plan for a set of two MS SQL Server
> 2000
> databases (OSes, and so on...) - one production and one standby, without
> using replication, log shipping, or cluster, but only SAN storage block
> level
> synchronization?
> -- Hoping this makes any sense Oskar.
>|||If you are doing this from scratch, the up-front time into the project can be
substantial to ensure the whole thing works with rigorous tests and to
document the procedures.
Once this is established, ongoing maintenance should be minimal.
Linchi
"Oskar" wrote:
> Hi,
> In your opinion, how much time would be a reasonable amount of time for a
> DBA to spend on the creation of a DR plan for a set of two MS SQL Server 2000
> databases (OSes, and so on...) - one production and one standby, without
> using replication, log shipping, or cluster, but only SAN storage block level
> synchronization?
> -- Hoping this makes any sense Oskar.
>|||If something goes wrong i.e. either one "processing unit", one "storage
unit", or both of them go bust I switch to the remaining "processing unit"
and "storage unit". If that doesn't work (e.g. because of some kind of
"logical", "non-storage-level" fault that couldn't be detected) I recover
from the last full DB backup + the chain of transaction log backups up to the
point of corruption for affected areas.
"Ekrem Ã?nsoy" wrote:
> You omitted almost all DR elements from the list :)
> It depends on your environment. You just did not add your list Database
> Mirroring however this is not applicable in SQL Server 2000 already.
> So, what kind of a DR is this? A Cold-Standby? Like Backup\Restore or
> Attach\Detach?
> --
> Ekrem Ã?nsoy
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:D6ECA2B1-D6DE-4FD4-A983-2971115693F1@.microsoft.com...
> > Hi,
> > In your opinion, how much time would be a reasonable amount of time for a
> > DBA to spend on the creation of a DR plan for a set of two MS SQL Server
> > 2000
> > databases (OSes, and so on...) - one production and one standby, without
> > using replication, log shipping, or cluster, but only SAN storage block
> > level
> > synchronization?
> >
> > -- Hoping this makes any sense Oskar.
> >
>|||I'm not counting the time for testing, just the "paperwork". When you write
"substantial", do you mean weeks or months?
"Linchi Shea" wrote:
> If you are doing this from scratch, the up-front time into the project can be
> substantial to ensure the whole thing works with rigorous tests and to
> document the procedures.
> Once this is established, ongoing maintenance should be minimal.
> Linchi
> "Oskar" wrote:
> > Hi,
> > In your opinion, how much time would be a reasonable amount of time for a
> > DBA to spend on the creation of a DR plan for a set of two MS SQL Server 2000
> > databases (OSes, and so on...) - one production and one standby, without
> > using replication, log shipping, or cluster, but only SAN storage block level
> > synchronization?
> >
> > -- Hoping this makes any sense Oskar.
> >|||It's hard to separate 'the paper work' from tests since you need to have
performed the tests to have the info for your paper work. If you are just
talking about creating a plan for doing the DR tests, we are talking about
hours or at most days.
Linchi
"Oskar" wrote:
> I'm not counting the time for testing, just the "paperwork". When you write
> "substantial", do you mean weeks or months?
> "Linchi Shea" wrote:
> > If you are doing this from scratch, the up-front time into the project can be
> > substantial to ensure the whole thing works with rigorous tests and to
> > document the procedures.
> >
> > Once this is established, ongoing maintenance should be minimal.
> >
> > Linchi
> >
> > "Oskar" wrote:
> >
> > > Hi,
> > > In your opinion, how much time would be a reasonable amount of time for a
> > > DBA to spend on the creation of a DR plan for a set of two MS SQL Server 2000
> > > databases (OSes, and so on...) - one production and one standby, without
> > > using replication, log shipping, or cluster, but only SAN storage block level
> > > synchronization?
> > >
> > > -- Hoping this makes any sense Oskar.
> > >|||One week should be sufficient.
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:48706660-18A6-4DB2-98F3-5F36D7A9A7BC@.microsoft.com...
> I'm not counting the time for testing, just the "paperwork". When you
> write
> "substantial", do you mean weeks or months?
> "Linchi Shea" wrote:
>> If you are doing this from scratch, the up-front time into the project
>> can be
>> substantial to ensure the whole thing works with rigorous tests and to
>> document the procedures.
>> Once this is established, ongoing maintenance should be minimal.
>> Linchi
>> "Oskar" wrote:
>> > Hi,
>> > In your opinion, how much time would be a reasonable amount of time for
>> > a
>> > DBA to spend on the creation of a DR plan for a set of two MS SQL
>> > Server 2000
>> > databases (OSes, and so on...) - one production and one standby,
>> > without
>> > using replication, log shipping, or cluster, but only SAN storage block
>> > level
>> > synchronization?
>> >
>> > -- Hoping this makes any sense Oskar.
>> >

How much space does MS SQL 7.0 need for reorganizing data

We are running MS SQL 7.0 and we use a database maintenance plan that
does, among other things, the "Reorganize data and index pages" (under
the "Optimizations" tab). We have had this optimization fail for some
of the databases due to file space shortages. I have a couple of
questions that may help to resolve this issue and aid my understanding
of how MS SQL Server 7.0 allocates space.
Assumptions: We have a single database TEST which has a single
filegroup (PRIMARY) and that this filegroup has only two files (one
for the data and one for the transaction log). Furthemore, there is an
infinite amount of free disk space available to the system.
1. If the data file has size N (in megabytes) how much of this file
must be free space to guarantee that the above optimization will not
fail due to lack of space in the PRIMARY filegroup? (For example, if
the data file has size 1000 megabytes of which 700 is currently used,
is 300 megabytes enough free space?)
2. The same question for the database's transaction log file.
3. If the data and transaction files are full and the "Reorganize data
and index pages" is run (so, there is not enough space), will checking
the "Automatically grow file" property be enough to ensure that the
system will grow the two files sufficiently to allow the re-indexing
to complete? Or do the files have to have enough space _before_ the
re-indexing?
Thanks, A. LewenbergIt is not really the size of the data file, but the size of the largest
table you wish to re-index... You need 1.25 * table size to drop/recreate
the clustered index...If you intend to do 2 tables at the same time, you
have to have extra space for both... This is in addition to the current size
used by the tables. But the 1.25 * space is used only temporarily...
The log file shouldn't be much ( but i am not sure).
The reorging can use lots of log space however...same as the size of the
index (I think)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"A. Lewenberg" <adam@.macrotex.net> wrote in message
news:6bd7331b.0403011328.7618c1de@.posting.google.com...
> We are running MS SQL 7.0 and we use a database maintenance plan that
> does, among other things, the "Reorganize data and index pages" (under
> the "Optimizations" tab). We have had this optimization fail for some
> of the databases due to file space shortages. I have a couple of
> questions that may help to resolve this issue and aid my understanding
> of how MS SQL Server 7.0 allocates space.
> Assumptions: We have a single database TEST which has a single
> filegroup (PRIMARY) and that this filegroup has only two files (one
> for the data and one for the transaction log). Furthemore, there is an
> infinite amount of free disk space available to the system.
> 1. If the data file has size N (in megabytes) how much of this file
> must be free space to guarantee that the above optimization will not
> fail due to lack of space in the PRIMARY filegroup? (For example, if
> the data file has size 1000 megabytes of which 700 is currently used,
> is 300 megabytes enough free space?)
> 2. The same question for the database's transaction log file.
> 3. If the data and transaction files are full and the "Reorganize data
> and index pages" is run (so, there is not enough space), will checking
> the "Automatically grow file" property be enough to ensure that the
> system will grow the two files sufficiently to allow the re-indexing
> to complete? Or do the files have to have enough space _before_ the
> re-indexing?
> Thanks, A. Lewenberg

How much space does MS SQL 7.0 need for reorganizing data

We are running MS SQL 7.0 and we use a database maintenance plan that
does, among other things, the "Reorganize data and index pages" (under
the "Optimizations" tab). We have had this optimization fail for some
of the databases due to file space shortages. I have a couple of
questions that may help to resolve this issue and aid my understanding
of how MS SQL Server 7.0 allocates space.
Assumptions: We have a single database TEST which has a single
filegroup (PRIMARY) and that this filegroup has only two files (one
for the data and one for the transaction log). Furthemore, there is an
infinite amount of free disk space available to the system.
1. If the data file has size N (in megabytes) how much of this file
must be free space to guarantee that the above optimization will not
fail due to lack of space in the PRIMARY filegroup? (For example, if
the data file has size 1000 megabytes of which 700 is currently used,
is 300 megabytes enough free space?)
2. The same question for the database's transaction log file.
3. If the data and transaction files are full and the "Reorganize data
and index pages" is run (so, there is not enough space), will checking
the "Automatically grow file" property be enough to ensure that the
system will grow the two files sufficiently to allow the re-indexing
to complete? Or do the files have to have enough space _before_ the
re-indexing?
Thanks, A. LewenbergIt is not really the size of the data file, but the size of the largest
table you wish to re-index... You need 1.25 * table size to drop/recreate
the clustered index...If you intend to do 2 tables at the same time, you
have to have extra space for both... This is in addition to the current size
used by the tables. But the 1.25 * space is used only temporarily...
The log file shouldn't be much ( but i am not sure).
The reorging can use lots of log space however...same as the size of the
index (I think)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"A. Lewenberg" <adam@.macrotex.net> wrote in message
news:6bd7331b.0403011328.7618c1de@.posting.google.com...
> We are running MS SQL 7.0 and we use a database maintenance plan that
> does, among other things, the "Reorganize data and index pages" (under
> the "Optimizations" tab). We have had this optimization fail for some
> of the databases due to file space shortages. I have a couple of
> questions that may help to resolve this issue and aid my understanding
> of how MS SQL Server 7.0 allocates space.
> Assumptions: We have a single database TEST which has a single
> filegroup (PRIMARY) and that this filegroup has only two files (one
> for the data and one for the transaction log). Furthemore, there is an
> infinite amount of free disk space available to the system.
> 1. If the data file has size N (in megabytes) how much of this file
> must be free space to guarantee that the above optimization will not
> fail due to lack of space in the PRIMARY filegroup? (For example, if
> the data file has size 1000 megabytes of which 700 is currently used,
> is 300 megabytes enough free space?)
> 2. The same question for the database's transaction log file.
> 3. If the data and transaction files are full and the "Reorganize data
> and index pages" is run (so, there is not enough space), will checking
> the "Automatically grow file" property be enough to ensure that the
> system will grow the two files sufficiently to allow the re-indexing
> to complete? Or do the files have to have enough space _before_ the
> re-indexing?
> Thanks, A. Lewenberg

How long does a query plan stay in the procedure cache?

How long does a a query plan stay in the procedure cache? Is there someway
we can reload a specific sp in SQL Server is restarted? TIADepends on the cost associated with creating the plan as well as how often
the sproc is run. Yes you can configure the sproc to run each time the SQL
Server is started by using the sp_procoption system stored procedure (see
BOL for details).
HTH
J
"ENathan" <njoysgolfing@.yahoo.com> wrote in message
news:OnKmNjuZFHA.2768@.tk2msftngp13.phx.gbl...
> How long does a a query plan stay in the procedure cache? Is there someway
> we can reload a specific sp in SQL Server is restarted? TIA
>|||Hi,
The most common reasons why a query execution plan is flushed from procedure
cache are:
1. There is not enough internal memory, or the query is not used frequently.
This causes the memory manager to assign the memory to other
procedure to be moved out from cache.You can try to run the query from time
to time.
2. Statistics are updated. When column statistics or index statistics are
updated, all query plans that use those tables will be discarded.
You can turn off auto-update statistics, and schedule UPDATE STATISTICS
yourself. After that you would need to run the query again in order to
can a compiled query plan
3. Schema changes. If the data structure is changed, indexes are added or
deleted, etc., then the related queries are marked for recompile.
Is there some way we can reload a specific sp in SQL Server is restarted?
We can put the procedure in startup . See sp_procoption system procedure in
books online.
Thanks
Hari
SQL Server MVP
"ENathan" <njoysgolfing@.yahoo.com> wrote in message
news:OnKmNjuZFHA.2768@.tk2msftngp13.phx.gbl...
> How long does a a query plan stay in the procedure cache? Is there someway
> we can reload a specific sp in SQL Server is restarted? TIA
>