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 (AT) nospam (DOT) com> wrote

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 (AT) Careerbuilder (DOT) com> wrote

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 (AT) nospam (DOT) com> wrote

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 (AT) Careerbuilder (DOT) com> wrote

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 (AT) nospam (DOT) com> wrote

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 (AT) Careerbuilder (DOT) com> wrote

"RP" <rp (AT) nospam (DOT) com> wrote


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 (AT) nospam (DOT) com> wrote