My company is looking at consolidating its Access databases from
around the country into one centralised SQL resource.

They are very worried about having all their 'eggs in 1 basket' and so
are considering using clustering or a similiar solution.

Unfortunately, whilst budgets can stretch for a small SAN and two
decent servers - the huge cost of 2xSQL Server Enterprise edition is
prohibitive.

What are the options here?

The application they use uses a DSN to connect to the database.
Ideally, we would two servers each part of the same w2K cluster,
therefore if one server broke down we couldn't have to change all the
connection (DSN) information.

Making sure that sprocs etc are identical between the two SQL servers
isn't really an issue (as they don't really change) and we can always
set a DTS package to transfer these peridocally.

So, using SQL Standard edition and Windows 2000 Advanced server will
this work? Two servers with the same logical name server name + same
database name, replication from the master to the standby server.

Will this cure my problem of having no fault tolerance and having to
manually change the DSN's?

Would really appreciate some help on this one!!

Thanks very much

Jof

Replication isn't a good option since it only synchronizes data, not schema
changes or views or procs or (well, you get the idea). Log shipping may
help in keeping a standby server close to hand. Trying to put two servers
with the same name on the same net and in the same domain is not possible,
especially when you need them to connect to each other.

I suggest figuring out how much downtime your company can stand and
purchasing equipment accordingly. Either you live with the downtime and
hassle of switching a warm standby server into place or you get a clustered
solution. Either way your company decides, you are OK since senior
management has been made aware of the risks and business costs.

You report, they decide.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com




"Jof" <jonathan.fletcher (AT) virgin (DOT) net> wrote

If you use an Active/Passive cluster, you pay for only one SQL Server
Enterprise Edition license. Failover is automatic and takes place in about
30 sec.

If you can tolerate some down time, then you can look at log shipping, where
backups of the database and log are sent from one server to another. Upon
failure of the primary, you would have to change the name and IP of the
backup server to that of the primary and ensure that the primary stays off
the network. For this, you don't need the Enterprise Edition, though you
would have to manage log shipping on your own.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


"Jof" <jonathan.fletcher (AT) virgin (DOT) net> wrote

My company is looking at consolidating its Access databases from
around the country into one centralised SQL resource.

They are very worried about having all their 'eggs in 1 basket' and so
are considering using clustering or a similiar solution.

Unfortunately, whilst budgets can stretch for a small SAN and two
decent servers - the huge cost of 2xSQL Server Enterprise edition is
prohibitive.

What are the options here?

The application they use uses a DSN to connect to the database.
Ideally, we would two servers each part of the same w2K cluster,
therefore if one server broke down we couldn't have to change all the
connection (DSN) information.

Making sure that sprocs etc are identical between the two SQL servers
isn't really an issue (as they don't really change) and we can always
set a DTS package to transfer these peridocally.

So, using SQL Standard edition and Windows 2000 Advanced server will
this work? Two servers with the same logical name server name + same
database name, replication from the master to the standby server.

Will this cure my problem of having no fault tolerance and having to
manually change the DSN's?

Would really appreciate some help on this one!!

Thanks very much

Jof