Hi all, Firstly let me apologize if any of my questions sound stupid. I am a
newbie when it comes to SQL Server. We currently have 1 server running SQL
with a couple of databases collectively approaching 2GB. The HDD's are
configured on a RAID 5 and everything has been running OK. We just recently
experienced downtime with one of our other mail servers and there is a
sudden focus on backup failover servers. Unfortunately we do not have the
budget to invest in Windows Advanced Server and SQL Enterprise for
clustering etc. It is my understanding that I can have a standby cold fail
over server with SQL installed that I can switch on, incase the primary goes
down. Is that true? If yes, what is the best way to set this up? Are there
any best practices?
In our recent server failure, the server would not even bootup. As a result
we could not even get access to the data on the hard drives. We had to end
up biting the bullet by being down the the most part of the day, while the
server OS was re-built. Assuming something similar happens on the SQL
Server, how can I move the latest copy of the database over to the failover
server? Any suggestions? I know there are some options like having an
external rackmount storage system, but to my knowledge those are pretty
expensive and provided by EMC etc.
thanks a bunch!
Without Enterprise Edition and its associated clustering feature, your next
best availability technologies are replication and log shipping. I strongly
discourage replication as an availability option since many database
elements are not replicated. Log shipping is included with Enterprise
Edition, but you can 'roll your own' without too much difficulty. The SQL
Server 2000 Resource Kit includes a simple log shipping example that you can
adapt for your site. This will allow you to keep the data fairly current
with your production server, but will require a fair amount of manual
intervention to 'go live'.
My suggestion is to examine your disaster recovery policy now and offer
alternatives to your management based on expected cost and expected system
availability. SQL Clustering is one option and log shipping is another.
(You can combine them by log shipping to another site and really be
prepared.) Offer them a choice and show what the different levels of
spending actually buy in terms of system availability. You may be surprised
what becomes affordable when the choice is presented as a business decision,
not a technology decision.
No matter which technology path you choose, documentation and training will
be the keys to meeting your availability targets. You must have a good plan
and the personnel to implement it. Otherwise the technology doesn't get you
squat.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"RP" <rp@.nospam.com> wrote in message
news:uxFMQTzQEHA.2404@.TK2MSFTNGP11.phx.gbl...
> Hi all, Firstly let me apologize if any of my questions sound stupid. I am
a
> newbie when it comes to SQL Server. We currently have 1 server running SQL
> with a couple of databases collectively approaching 2GB. The HDD's are
> configured on a RAID 5 and everything has been running OK. We just
recently
> experienced downtime with one of our other mail servers and there is a
> sudden focus on backup failover servers. Unfortunately we do not have the
> budget to invest in Windows Advanced Server and SQL Enterprise for
> clustering etc. It is my understanding that I can have a standby cold fail
> over server with SQL installed that I can switch on, incase the primary
goes
> down. Is that true? If yes, what is the best way to set this up? Are there
> any best practices?
> In our recent server failure, the server would not even bootup. As a
result
> we could not even get access to the data on the hard drives. We had to end
> up biting the bullet by being down the the most part of the day, while the
> server OS was re-built. Assuming something similar happens on the SQL
> Server, how can I move the latest copy of the database over to the
failover
> server? Any suggestions? I know there are some options like having an
> external rackmount storage system, but to my knowledge those are pretty
> expensive and provided by EMC etc.
> thanks a bunch!
>
|||Thanks for the options. I will look into Log Shipping & the SQL Resource
Kit.
As far as business decision vs technology decision, easier said than done
when working for a company of your size. When it comes to small business, a
whole different set of rules come into play.
thanks again!
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:Ot1BHW0QEHA.2704@.TK2MSFTNGP10.phx.gbl...
> Without Enterprise Edition and its associated clustering feature, your
next
> best availability technologies are replication and log shipping. I
strongly
> discourage replication as an availability option since many database
> elements are not replicated. Log shipping is included with Enterprise
> Edition, but you can 'roll your own' without too much difficulty. The SQL
> Server 2000 Resource Kit includes a simple log shipping example that you
can
> adapt for your site. This will allow you to keep the data fairly current
> with your production server, but will require a fair amount of manual
> intervention to 'go live'.
> My suggestion is to examine your disaster recovery policy now and offer
> alternatives to your management based on expected cost and expected system
> availability. SQL Clustering is one option and log shipping is another.
> (You can combine them by log shipping to another site and really be
> prepared.) Offer them a choice and show what the different levels of
> spending actually buy in terms of system availability. You may be
surprised
> what becomes affordable when the choice is presented as a business
decision,
> not a technology decision.
> No matter which technology path you choose, documentation and training
will
> be the keys to meeting your availability targets. You must have a good
plan
> and the personnel to implement it. Otherwise the technology doesn't get
you[vbcol=seagreen]
> squat.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "RP" <rp@.nospam.com> wrote in message
> news:uxFMQTzQEHA.2404@.TK2MSFTNGP11.phx.gbl...
am[vbcol=seagreen]
> a
SQL[vbcol=seagreen]
> recently
the[vbcol=seagreen]
fail[vbcol=seagreen]
> goes
there[vbcol=seagreen]
> result
end[vbcol=seagreen]
the
> failover
>
|||Actually, it is the same rules. Size and scale may determine which solution
is best, but the inputs of cost and availability don't change. The only
difference is a business decision of whether the additional uptime is an
effective way to spend the company's money. Of course everyone has budget
limits. Sometimes major availability enhancements take a while to get
approved and implemented.
No matter what the decision is, you are fine since management will now be
aware of the risks and has active input in deciding how to mitigate them.
This works the same no matter how big or small your business.
Again, you can work on procedures, documentation, and training even without
a huge budget. I guarantee you that will improve system availability and
recoverability.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"RP" <rp@.nospam.com> wrote in message
news:eoURYM1QEHA.2468@.tk2msftngp13.phx.gbl...
> Thanks for the options. I will look into Log Shipping & the SQL Resource
> Kit.
> As far as business decision vs technology decision, easier said than done
> when working for a company of your size. When it comes to small business,
a[vbcol=seagreen]
> whole different set of rules come into play.
> thanks again!
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:Ot1BHW0QEHA.2704@.TK2MSFTNGP10.phx.gbl...
> next
> strongly
SQL[vbcol=seagreen]
> can
current[vbcol=seagreen]
system[vbcol=seagreen]
> surprised
> decision,
> will
> plan
> you
I[vbcol=seagreen]
> am
> SQL
> the
> fail
primary[vbcol=seagreen]
> there
> end
> the
pretty
>
|||Geoff, after doing some reading it appears that log shipping is only
available in Enterprise Edition. We are running Standard Edition. What
options am I left with to have a cold standby server with upto date data
incase the primary goes down? You had some reservations against replication
since all database elements are not replicated. What other options do I
have, other than restoring the last known good backup on the secondary
server?
thanks!
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O4KuwU1QEHA.2132@.TK2MSFTNGP11.phx.gbl...
> Actually, it is the same rules. Size and scale may determine which
solution
> is best, but the inputs of cost and availability don't change. The only
> difference is a business decision of whether the additional uptime is an
> effective way to spend the company's money. Of course everyone has budget
> limits. Sometimes major availability enhancements take a while to get
> approved and implemented.
> No matter what the decision is, you are fine since management will now be
> aware of the risks and has active input in deciding how to mitigate them.
> This works the same no matter how big or small your business.
> Again, you can work on procedures, documentation, and training even
without[vbcol=seagreen]
> a huge budget. I guarantee you that will improve system availability and
> recoverability.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "RP" <rp@.nospam.com> wrote in message
> news:eoURYM1QEHA.2468@.tk2msftngp13.phx.gbl...
done[vbcol=seagreen]
business,[vbcol=seagreen]
> a
> SQL
you[vbcol=seagreen]
> current
offer[vbcol=seagreen]
> system
another.[vbcol=seagreen]
good[vbcol=seagreen]
get[vbcol=seagreen]
stupid.[vbcol=seagreen]
> I
running[vbcol=seagreen]
are[vbcol=seagreen]
a[vbcol=seagreen]
have[vbcol=seagreen]
cold[vbcol=seagreen]
> primary
to[vbcol=seagreen]
while[vbcol=seagreen]
SQL[vbcol=seagreen]
an
> pretty
>
|||Log shipping is included with Enterprise Edition. You can 'roll your own'
with any edition. Before you ask, yes, it is legal provided all servers
involved are properly licensed. The SQL Server Resource Kit has an example
that will work with standard edition of SQL Server. You will probably need
to tweak it for your specific needs, but it is a good starting point. I
have Enterprise Edition, primarily for the scalability and clustering
features, but I wrote my own log shipping because the included product
didn't meet all of my needs.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"RP" <rp@.nospam.com> wrote in message
news:OLF0ZRBSEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Geoff, after doing some reading it appears that log shipping is only
> available in Enterprise Edition. We are running Standard Edition. What
> options am I left with to have a cold standby server with upto date data
> incase the primary goes down? You had some reservations against
replication[vbcol=seagreen]
> since all database elements are not replicated. What other options do I
> have, other than restoring the last known good backup on the secondary
> server?
> thanks!
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:O4KuwU1QEHA.2132@.TK2MSFTNGP11.phx.gbl...
> solution
budget[vbcol=seagreen]
be[vbcol=seagreen]
them.[vbcol=seagreen]
> without
and[vbcol=seagreen]
Resource[vbcol=seagreen]
> done
> business,
your[vbcol=seagreen]
Enterprise[vbcol=seagreen]
The[vbcol=seagreen]
> you
manual[vbcol=seagreen]
> offer
> another.
of[vbcol=seagreen]
training[vbcol=seagreen]
> good
> get
> stupid.
> running
> are
is[vbcol=seagreen]
> a
> have
> cold
Are[vbcol=seagreen]
a[vbcol=seagreen]
had
> to
> while
> SQL
> an
>
|||Geoff, is the Resource Kit available for download? Or do I need to purchase
the book from MS Press? Also talking about licensing issues, if the cold
standby is only used in the event of failure of the primary server, do I
still need a licenses for the standby server?
thanks
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:urxatWBSEHA.3168@.tk2msftngp13.phx.gbl...
> Log shipping is included with Enterprise Edition. You can 'roll your own'
> with any edition. Before you ask, yes, it is legal provided all servers
> involved are properly licensed. The SQL Server Resource Kit has an
example
> that will work with standard edition of SQL Server. You will probably
need[vbcol=seagreen]
> to tweak it for your specific needs, but it is a good starting point. I
> have Enterprise Edition, primarily for the scalability and clustering
> features, but I wrote my own log shipping because the included product
> didn't meet all of my needs.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "RP" <rp@.nospam.com> wrote in message
> news:OLF0ZRBSEHA.1312@.TK2MSFTNGP12.phx.gbl...
> replication
only[vbcol=seagreen]
an[vbcol=seagreen]
> budget
> be
> them.
> and
> Resource
> your
I[vbcol=seagreen]
database[vbcol=seagreen]
> Enterprise
> The
that[vbcol=seagreen]
> manual
expected[vbcol=seagreen]
be[vbcol=seagreen]
> of
> training
doesn't[vbcol=seagreen]
HDD's[vbcol=seagreen]
just[vbcol=seagreen]
there[vbcol=seagreen]
> is
for[vbcol=seagreen]
> Are
As[vbcol=seagreen]
> a
> had
the[vbcol=seagreen]
the[vbcol=seagreen]
having
>
|||"RP" <rp@.nospam.com> wrote in message
news:%23WtvuZBSEHA.568@.TK2MSFTNGP12.phx.gbl...
> Geoff, is the Resource Kit available for download? Or do I need to
purchase
> the book from MS Press? Also talking about licensing issues, if the cold
> standby is only used in the event of failure of the primary server, do I
> still need a licenses for the standby server?
Can't answer all that, but there are "roll-your own" logshipping routines
out there if you google for them.
To be honest, I sometimes forget ours is running it's so transparent. (I'd
give you the URL but I've honestly forgotten it.)
|||The Resource Kit is not available for download. As Greg noted, there are
several available for download. It isn't too difficult to build or modify
your own. It will take some time and a good understanding of the backup
tables in the msdb database.
As for the licensing, I am not a legal expert, but I suspect you will have
to fully license all servers. The only exception I can find to licensing a
server is with failover clustering and a normally inactive host node.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"RP" <rp@.nospam.com> wrote in message
news:%23WtvuZBSEHA.568@.TK2MSFTNGP12.phx.gbl...
> Geoff, is the Resource Kit available for download? Or do I need to
purchase[vbcol=seagreen]
> the book from MS Press? Also talking about licensing issues, if the cold
> standby is only used in the event of failure of the primary server, do I
> still need a licenses for the standby server?
> thanks
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:urxatWBSEHA.3168@.tk2msftngp13.phx.gbl...
own'[vbcol=seagreen]
> example
> need
data[vbcol=seagreen]
I[vbcol=seagreen]
> only
is[vbcol=seagreen]
> an
get[vbcol=seagreen]
now[vbcol=seagreen]
availability[vbcol=seagreen]
than[vbcol=seagreen]
feature,[vbcol=seagreen]
> I
> database
difficulty.[vbcol=seagreen]
> that
fairly[vbcol=seagreen]
and[vbcol=seagreen]
> expected
> be
levels[vbcol=seagreen]
be[vbcol=seagreen]
business[vbcol=seagreen]
a[vbcol=seagreen]
> doesn't
> HDD's
> just
> there
not[vbcol=seagreen]
> for
standby[vbcol=seagreen]
the[vbcol=seagreen]
up?[vbcol=seagreen]
bootup.[vbcol=seagreen]
> As
We[vbcol=seagreen]
day,[vbcol=seagreen]
> the
> the
> having
are
>
