Secondary mirror SQL

[Wes98765]

Hey guys,
I need to know how to make a fail safe like cluster with sql server. With webs I just make a secondary dns and switch to another server if one goes down. How do I have one database contain all the same information as another so if one server goes down the data will be the same?

Thanks for any help

Wes

 

[SQLBill]

You have several options, all cost $$$$$

Cluster: usually an ACTIVE/PASSIVE cluster. You have two servers (called NODES). Let's call them SQL1 and SQL2. You then have a disk array. You cluster the two servers. You load SQL Server onto both nodes using cluster setup. You create your data files on the disk array as a virtual server. All the .mdf, .ndf, .ldf files are on the disk array. SQL1 can access the database files while it's the active node. Then when SQL1 is shutdown, the clustering software makes SQL2 the primary node and it can access the data on the array.

Replication: Buy the hardware to duplicate your current server. Use Transaction Replication or Log Shipping to keep the data on the backup server up-to-date. When the primary server is down, you bring the backup server on-line.

-SQLBill

 

[Wes98765]

Thanks for the input. If I use replication what hardware are you refering too? Where would I go to purchase and learn how to actually configure everything?

Thanks again

Wes

 

[SQLBill]

What do you use now for your SQL Server server?

You would buy the exact same setup. SQL Server has replication commands. Check out the Books OnLine for more information or search this site for Replication or for Log Shipping.

Books OnLine (BOL) is a client tool that comes with SQL Server. Find it via Query Analyzer, select HELP. Or Start>Programs>Microsoft SQL Server>Books OnLine

I don't use replication myself, so I don't know a lot about it.

-SQLBill

 

[JohnBates]

Wes,

Clustering will require SQL Server 2000 ENTERPRI$E edition - expensive.

Log shipping and Replication can be done with just the Standard edition.

I use merge replication to replicate data over to an alternate server - it has its pros and cons.
Good luck John

 

[mrdenny]

Wes,
No only does Clustering cost big money for the SQL Enterprise Edition, but you also have to purchase some sort of shared storage. Such as an HP Mini San, or EMC SAN or something else in that class. You'll also need Windows 2000 Advanced server (or 2003 Advanced Server).

I'm currently using both Merge and Transactional Replication to keep various systems backed up to backup servers in real time or near real time.

For a small shop replication is the way to go. If you have the money to burn, and/or need a high availability system then Clustering has some advantages.

Denny

Denny

Between the ESP=ON and the RUM (Read Users Mind) upgrade, I'm ready to go.