Maintenance Routine - Best Practice

Please point me in the right direction. I am not a SQL DBA, nor am I very
experienced with SQL, nor does our budget allow for someone who is. I have
an experienced SQL pro available to me but believe I can handle the task at
hand with a little help.
I need to set up maintenance and backup routines for my SQL databases. I am
relatively familiar with Enterprise Manager and think I can see my way
through using a wizard to set up a proper maintenance plan. Problem is I
don't know what a proper plan is and I've heard that the wizards don't always
offer choices that fall in line with the best practices or pro's general
consensus on how things should be done.
I believe that part of the maintenance plan should be to truncate our logs .
. . but I don't know why we would do that given that the log files would be
used to recover the databases if something went wrong. I'm interested in
knowing if you'll share.
I am not opposed to reading and learning so please feel free to point me to
a recommended document or best practices article. Please bear in mind,
however, that I am not a DBA and (I am also very aware of our need for one)
as such the easier the reading the more helpful it will be.
Thanks for the help,
Po"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
If it's "good enough" you can do a lot of this with Enterprise manager.
Typically you'll do a FULL backup every "X days" (I tend to do once a night,
my old company we did every other night, etc. Depends a lot on different
factors).
Then do a TRANSACTION backup every "Y minutes" (it could be 60, 120, 5,
etc.)
The transaction log will "truncate" the log. Note that it will NOT shrink
the physical log file nor do you normally want to.
Typically figure "how long can I be down and how much data can I afford to
lose?"
When you recover from a failure, you'll restore the FULL backup first with
NORECOVERY and then EACH AND EVERY transaction log since that backup. This
is where those various factors come into play. For example, if you're doing
a transaction log backup every 5 minutes, you probably don't want to do a
FULL backup once a week as the worse case recovery scenario can be nasty.
(btw, you can do incremental backups betwene full ones which would help
there, but that starts to get complicated.)
Once you've got your backup scheme in place... TEST IT! I can't tell you
the number of times that I've seen people do these beautiful backup
schemes... only to have them fail when they need them. And once you test
it, practice it. Nothing is worse than in the heat of an emergency to
forget the critical steps required (e.g. forgetting to add NORECOVERY when
restoring the 50 gig DB :-)
As for other items, others can give advice on rebuilding indexes, etc.
(Though I often tend to a simple job of doing a 10% update on stats on all
tables if nothing else.)
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Backing up the log truncates in most situations. If the database is small
and it is all you have to manage, I would just create 3 separate maint
plans.
Backups:
Nightly full's
Hourly tlogs
Indexes\Stats
Weekly or nighly depending on the impact you can handle
Checkdb
Weekly
The idea is to keep it simple but depending on your hardware, you may have
to use a custom solution once you get to 10-50GB.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po|||Pretty standard backup:
large databases:
Full database - weekly
Differentials - daily
T-logs - daily every 15 - 60 minutes
Small databases:
Full - daily
T-logs - daily every hour
Let the MP "Integrity check" option run once a week
Let the "Optimizations" part run once a week for small databases, more often
if they are large or high transaction (this is the index rebuilding part,
which is important for performance)
These are very generic guidelines.
3 seperate plans:
1 for system databases only
1 for databases in full recovery model
1 for databases in Simple recovery model
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po|||Thank you everyone for the help. I noticed that all the posts referred in
one way or another to the size of the databases. I right-clicked on my
biggest database - it's about 57GB. How does that affect the advice given?
Please accept my apologies for not including the above information in the
first place. Also the server is a newer Dell 2950
2 Dual Core Xeon Processors (2.67GHz)
4GB RAM
RAID 1 OS Partition
RAID 5 Data Partition
We use the SonicWall CDP appliance for backup - so maintaining the database
is my bigger concern. Although I do plan to use the SQL native backup just
to be extra safe. I'm mentioning this because we don't have a 3rd partition
for log files (CDP handles SQL backup and restoration differently than SQL's
native backup I'm told) nor do I ship the log files - but that might only be
because I don't know what that means.
Testing in our environment is difficult as we are a 24hr company and taking
down this server costs us the use of our main application. I suppose I could
use a spare server to restore the database but I'm not sure of the best way
to confirm that the restoration was successful and the data is most up to
date. I'm not a regular user of the application so I wonder is there a way
to query the database to get the latest information written to it?
Sorry I'm growing this thread - I very much appreciate the help though.
Po
"Kevin3NF" wrote:
> Pretty standard backup:
> large databases:
> Full database - weekly
> Differentials - daily
> T-logs - daily every 15 - 60 minutes
> Small databases:
> Full - daily
> T-logs - daily every hour
> Let the MP "Integrity check" option run once a week
> Let the "Optimizations" part run once a week for small databases, more often
> if they are large or high transaction (this is the index rebuilding part,
> which is important for performance)
> These are very generic guidelines.
> 3 seperate plans:
> 1 for system databases only
> 1 for databases in full recovery model
> 1 for databases in Simple recovery model
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> > Please point me in the right direction. I am not a SQL DBA, nor am I very
> > experienced with SQL, nor does our budget allow for someone who is. I
> > have
> > an experienced SQL pro available to me but believe I can handle the task
> > at
> > hand with a little help.
> >
> > I need to set up maintenance and backup routines for my SQL databases. I
> > am
> > relatively familiar with Enterprise Manager and think I can see my way
> > through using a wizard to set up a proper maintenance plan. Problem is I
> > don't know what a proper plan is and I've heard that the wizards don't
> > always
> > offer choices that fall in line with the best practices or pro's general
> > consensus on how things should be done.
> >
> > I believe that part of the maintenance plan should be to truncate our logs
> > .
> > . . but I don't know why we would do that given that the log files would
> > be
> > used to recover the databases if something went wrong. I'm interested in
> > knowing if you'll share.
> >
> > I am not opposed to reading and learning so please feel free to point me
> > to
> > a recommended document or best practices article. Please bear in mind,
> > however, that I am not a DBA and (I am also very aware of our need for
> > one)
> > as such the easier the reading the more helpful it will be.
> >
> > Thanks for the help,
> >
> > Po
>
>|||No idea what a Sonic Wall CDP is, but if its doing SQL Server
backups/restores for you, your supportability by Microsoft and those of us
that know SQL pretty well might be more limited by that fact. Advice I give
everyone...Use SQL Native backup or a WELL-KNOWN 3rd party utility such as
Litespeed to do the backups, then move/copy/archive the resulting backup
files.
57GB is a good sized database. If it is transactional, I would almost
certainly go with the Weekly full, daily Differential, hourly T-log method.
I don't think a maintenance plan offers Differentials as part of the wizard,
so you may have to create a new job with a Backup command in it:
BACKUP DATABASE MyNwind
TO MyNwind_1
WITH DIFFERENTIAL
GO
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> Thank you everyone for the help. I noticed that all the posts referred in
> one way or another to the size of the databases. I right-clicked on my
> biggest database - it's about 57GB. How does that affect the advice
> given?
> Please accept my apologies for not including the above information in the
> first place. Also the server is a newer Dell 2950
> 2 Dual Core Xeon Processors (2.67GHz)
> 4GB RAM
> RAID 1 OS Partition
> RAID 5 Data Partition
> We use the SonicWall CDP appliance for backup - so maintaining the
> database
> is my bigger concern. Although I do plan to use the SQL native backup
> just
> to be extra safe. I'm mentioning this because we don't have a 3rd
> partition
> for log files (CDP handles SQL backup and restoration differently than
> SQL's
> native backup I'm told) nor do I ship the log files - but that might only
> be
> because I don't know what that means.
> Testing in our environment is difficult as we are a 24hr company and
> taking
> down this server costs us the use of our main application. I suppose I
> could
> use a spare server to restore the database but I'm not sure of the best
> way
> to confirm that the restoration was successful and the data is most up to
> date. I'm not a regular user of the application so I wonder is there a
> way
> to query the database to get the latest information written to it?
> Sorry I'm growing this thread - I very much appreciate the help though.
> Po
> "Kevin3NF" wrote:
>> Pretty standard backup:
>> large databases:
>> Full database - weekly
>> Differentials - daily
>> T-logs - daily every 15 - 60 minutes
>> Small databases:
>> Full - daily
>> T-logs - daily every hour
>> Let the MP "Integrity check" option run once a week
>> Let the "Optimizations" part run once a week for small databases, more
>> often
>> if they are large or high transaction (this is the index rebuilding part,
>> which is important for performance)
>> These are very generic guidelines.
>> 3 seperate plans:
>> 1 for system databases only
>> 1 for databases in full recovery model
>> 1 for databases in Simple recovery model
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
>> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
>> > Please point me in the right direction. I am not a SQL DBA, nor am I
>> > very
>> > experienced with SQL, nor does our budget allow for someone who is. I
>> > have
>> > an experienced SQL pro available to me but believe I can handle the
>> > task
>> > at
>> > hand with a little help.
>> >
>> > I need to set up maintenance and backup routines for my SQL databases.
>> > I
>> > am
>> > relatively familiar with Enterprise Manager and think I can see my way
>> > through using a wizard to set up a proper maintenance plan. Problem is
>> > I
>> > don't know what a proper plan is and I've heard that the wizards don't
>> > always
>> > offer choices that fall in line with the best practices or pro's
>> > general
>> > consensus on how things should be done.
>> >
>> > I believe that part of the maintenance plan should be to truncate our
>> > logs
>> > .
>> > . . but I don't know why we would do that given that the log files
>> > would
>> > be
>> > used to recover the databases if something went wrong. I'm interested
>> > in
>> > knowing if you'll share.
>> >
>> > I am not opposed to reading and learning so please feel free to point
>> > me
>> > to
>> > a recommended document or best practices article. Please bear in mind,
>> > however, that I am not a DBA and (I am also very aware of our need for
>> > one)
>> > as such the easier the reading the more helpful it will be.
>> >
>> > Thanks for the help,
>> >
>> > Po
>>|||"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> Thank you everyone for the help. I noticed that all the posts referred in
> one way or another to the size of the databases. I right-clicked on my
> biggest database - it's about 57GB. How does that affect the advice
> given?
>
Size only partly comes into play.
The factor is related to how long it takes to backup and restore. If you
have slow disks and it takes 8 hours to restore, you've got problems.
And how much the DB changes.
If it's a 57GB DB but there's 100GB of transactions a day, that can make a
difference. If there's 1MB of transactions, that can make things simpler.
> We use the SonicWall CDP appliance for backup - so maintaining the
> database
> is my bigger concern. Although I do plan to use the SQL native backup
> just
> to be extra safe. I'm mentioning this because we don't have a 3rd
> partition
> for log files (CDP handles SQL backup and restoration differently than
> SQL's
> native backup I'm told) nor do I ship the log files - but that might only
> be
> because I don't know what that means.
>
Not familiar with Sonicwall.
I tend to turst the SQL Server native backup.
Shipping basically means restoring the logs automatically to another server.
It provides faster recovery time AND provides testing of the backup/restore
process.
> Testing in our environment is difficult as we are a 24hr company and
> taking
> down this server costs us the use of our main application. I suppose I
> could
> use a spare server to restore the database but I'm not sure of the best
> way
> to confirm that the restoration was successful and the data is most up to
> date.
That's really the way to do it since if your main server fails, you're going
to need to use the second server anyway.
> I'm not a regular user of the application so I wonder is there a way
> to query the database to get the latest information written to it?
>
That's pretty much application specific. But 'yes' is the basic answer.
> Sorry I'm growing this thread - I very much appreciate the help though.
> Po
> "Kevin3NF" wrote:
>> Pretty standard backup:
>> large databases:
>> Full database - weekly
>> Differentials - daily
>> T-logs - daily every 15 - 60 minutes
>> Small databases:
>> Full - daily
>> T-logs - daily every hour
>> Let the MP "Integrity check" option run once a week
>> Let the "Optimizations" part run once a week for small databases, more
>> often
>> if they are large or high transaction (this is the index rebuilding part,
>> which is important for performance)
>> These are very generic guidelines.
>> 3 seperate plans:
>> 1 for system databases only
>> 1 for databases in full recovery model
>> 1 for databases in Simple recovery model
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
>> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
>> > Please point me in the right direction. I am not a SQL DBA, nor am I
>> > very
>> > experienced with SQL, nor does our budget allow for someone who is. I
>> > have
>> > an experienced SQL pro available to me but believe I can handle the
>> > task
>> > at
>> > hand with a little help.
>> >
>> > I need to set up maintenance and backup routines for my SQL databases.
>> > I
>> > am
>> > relatively familiar with Enterprise Manager and think I can see my way
>> > through using a wizard to set up a proper maintenance plan. Problem is
>> > I
>> > don't know what a proper plan is and I've heard that the wizards don't
>> > always
>> > offer choices that fall in line with the best practices or pro's
>> > general
>> > consensus on how things should be done.
>> >
>> > I believe that part of the maintenance plan should be to truncate our
>> > logs
>> > .
>> > . . but I don't know why we would do that given that the log files
>> > would
>> > be
>> > used to recover the databases if something went wrong. I'm interested
>> > in
>> > knowing if you'll share.
>> >
>> > I am not opposed to reading and learning so please feel free to point
>> > me
>> > to
>> > a recommended document or best practices article. Please bear in mind,
>> > however, that I am not a DBA and (I am also very aware of our need for
>> > one)
>> > as such the easier the reading the more helpful it will be.
>> >
>> > Thanks for the help,
>> >
>> > Po
>>|||Guys, thanks for the great information. The CDP is a pretty nice real-time
backup appliance. It does the job however it conflicts in a big way with the
SQL native routines. I've been using its SQL agent because I didn't have
backups and maintenance properly configured in SQL. Thanks to your help I'm
that much closer and will probably use the CDP to backup the SQL natiive data
and log backup files.
One or two more questions, though. 1. - How do I know if my database is
transactional? I think because it is written to regularly that it is. Am I
right?
2. How do I determine HOW transactional it is (1MB or 100MB)?
Now a better question (I hope). If log shipping happens extremely often -
every 5 minutes what is the point of replication software like DoubleTake?
Automatic failover, maybe? Or is it that the 5 minutes between log backups
isn't lost if something happens?
Thanks for your continued help.
Po
"Greg D. Moore (Strider)" wrote:
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> > Thank you everyone for the help. I noticed that all the posts referred in
> > one way or another to the size of the databases. I right-clicked on my
> > biggest database - it's about 57GB. How does that affect the advice
> > given?
> >
> Size only partly comes into play.
> The factor is related to how long it takes to backup and restore. If you
> have slow disks and it takes 8 hours to restore, you've got problems.
> And how much the DB changes.
> If it's a 57GB DB but there's 100GB of transactions a day, that can make a
> difference. If there's 1MB of transactions, that can make things simpler.
> > We use the SonicWall CDP appliance for backup - so maintaining the
> > database
> > is my bigger concern. Although I do plan to use the SQL native backup
> > just
> > to be extra safe. I'm mentioning this because we don't have a 3rd
> > partition
> > for log files (CDP handles SQL backup and restoration differently than
> > SQL's
> > native backup I'm told) nor do I ship the log files - but that might only
> > be
> > because I don't know what that means.
> >
> Not familiar with Sonicwall.
> I tend to turst the SQL Server native backup.
> Shipping basically means restoring the logs automatically to another server.
> It provides faster recovery time AND provides testing of the backup/restore
> process.
> > Testing in our environment is difficult as we are a 24hr company and
> > taking
> > down this server costs us the use of our main application. I suppose I
> > could
> > use a spare server to restore the database but I'm not sure of the best
> > way
> > to confirm that the restoration was successful and the data is most up to
> > date.
> That's really the way to do it since if your main server fails, you're going
> to need to use the second server anyway.
>
> > I'm not a regular user of the application so I wonder is there a way
> > to query the database to get the latest information written to it?
> >
> That's pretty much application specific. But 'yes' is the basic answer.
> > Sorry I'm growing this thread - I very much appreciate the help though.
> >
> > Po
> >
> > "Kevin3NF" wrote:
> >
> >> Pretty standard backup:
> >>
> >> large databases:
> >> Full database - weekly
> >> Differentials - daily
> >> T-logs - daily every 15 - 60 minutes
> >>
> >> Small databases:
> >> Full - daily
> >> T-logs - daily every hour
> >>
> >> Let the MP "Integrity check" option run once a week
> >>
> >> Let the "Optimizations" part run once a week for small databases, more
> >> often
> >> if they are large or high transaction (this is the index rebuilding part,
> >> which is important for performance)
> >>
> >> These are very generic guidelines.
> >>
> >> 3 seperate plans:
> >> 1 for system databases only
> >> 1 for databases in full recovery model
> >> 1 for databases in Simple recovery model
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> >> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> >> > Please point me in the right direction. I am not a SQL DBA, nor am I
> >> > very
> >> > experienced with SQL, nor does our budget allow for someone who is. I
> >> > have
> >> > an experienced SQL pro available to me but believe I can handle the
> >> > task
> >> > at
> >> > hand with a little help.
> >> >
> >> > I need to set up maintenance and backup routines for my SQL databases.
> >> > I
> >> > am
> >> > relatively familiar with Enterprise Manager and think I can see my way
> >> > through using a wizard to set up a proper maintenance plan. Problem is
> >> > I
> >> > don't know what a proper plan is and I've heard that the wizards don't
> >> > always
> >> > offer choices that fall in line with the best practices or pro's
> >> > general
> >> > consensus on how things should be done.
> >> >
> >> > I believe that part of the maintenance plan should be to truncate our
> >> > logs
> >> > .
> >> > . . but I don't know why we would do that given that the log files
> >> > would
> >> > be
> >> > used to recover the databases if something went wrong. I'm interested
> >> > in
> >> > knowing if you'll share.
> >> >
> >> > I am not opposed to reading and learning so please feel free to point
> >> > me
> >> > to
> >> > a recommended document or best practices article. Please bear in mind,
> >> > however, that I am not a DBA and (I am also very aware of our need for
> >> > one)
> >> > as such the easier the reading the more helpful it will be.
> >> >
> >> > Thanks for the help,
> >> >
> >> > Po
> >>
> >>
> >>
>
>|||"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:476126FC-F71F-42A8-A293-7FA7713B50D1@.microsoft.com...
> Guys, thanks for the great information. The CDP is a pretty nice
> real-time
> backup appliance. It does the job however it conflicts in a big way with
> the
> SQL native routines. I've been using its SQL agent because I didn't have
> backups and maintenance properly configured in SQL. Thanks to your help
> I'm
> that much closer and will probably use the CDP to backup the SQL natiive
> data
> and log backup files.
> One or two more questions, though. 1. - How do I know if my database is
> transactional? I think because it is written to regularly that it is. Am
> I
> right?
Yeah, basically if you're doing queries and updates, it's transactional.
If it's really only for reports and the like, it's more likely an OLAP.
(there's other guidelines, but that's the simple version :-)
> 2. How do I determine HOW transactional it is (1MB or 100MB)?
>
Monitor the size of the log and see how fast it grows.
> Now a better question (I hope). If log shipping happens extremely often -
> every 5 minutes what is the point of replication software like DoubleTake?
> Automatic failover, maybe? Or is it that the 5 minutes between log
> backups
> isn't lost if something happens?
>
Not famliar DoubleTake. But replication is "different". Generally used for
creating extra copies of data to scale out.
It can be part of a DR solution, but it's not a complete solution in and of
itself.
> Thanks for your continued help.
>
No problem.
> Po
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||DoubleTake is a drive level copy process...if a bit changes, that is
replicated. Slick stuff from the marketing materials
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:udR5OWAVIHA.4440@.TK2MSFTNGP06.phx.gbl...
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:476126FC-F71F-42A8-A293-7FA7713B50D1@.microsoft.com...
>> Guys, thanks for the great information. The CDP is a pretty nice
>> real-time
>> backup appliance. It does the job however it conflicts in a big way with
>> the
>> SQL native routines. I've been using its SQL agent because I didn't have
>> backups and maintenance properly configured in SQL. Thanks to your help
>> I'm
>> that much closer and will probably use the CDP to backup the SQL natiive
>> data
>> and log backup files.
>> One or two more questions, though. 1. - How do I know if my database is
>> transactional? I think because it is written to regularly that it is.
>> Am I
>> right?
> Yeah, basically if you're doing queries and updates, it's transactional.
> If it's really only for reports and the like, it's more likely an OLAP.
> (there's other guidelines, but that's the simple version :-)
>
>> 2. How do I determine HOW transactional it is (1MB or 100MB)?
> Monitor the size of the log and see how fast it grows.
>> Now a better question (I hope). If log shipping happens extremely
>> often -
>> every 5 minutes what is the point of replication software like
>> DoubleTake?
>> Automatic failover, maybe? Or is it that the 5 minutes between log
>> backups
>> isn't lost if something happens?
> Not famliar DoubleTake. But replication is "different". Generally used
> for creating extra copies of data to scale out.
> It can be part of a DR solution, but it's not a complete solution in and
> of itself.
>
>> Thanks for your continued help.
> No problem.
>
>> Po
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Ah, that's right. I have seen that.
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%233o7iXFVIHA.2368@.TK2MSFTNGP05.phx.gbl...
> DoubleTake is a drive level copy process...if a bit changes, that is
> replicated. Slick stuff from the marketing materials
> --
> Kevin3NF
> SQL Server dude
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html