SQL Server Performance Forum – Threads Archive

SQL Server failover support

Hi, I am using SQl Server 2000 as the back end for my application. Our client needs SQL server failover support in case the database or the database server goes offline at any time.
We need an automated method to make the database online at any point of time. I am aware of SQL Server failover clusters, but our client cannot afford to purchase SQL Server 2000 Enterprise Edition. Is there any other methods by which we can ensure the database is always available to users. I am aware of Transaction Replication & Log shipping but confused which one is better. My requirement is to provide a backup server. If the primary server fails the users will be connected to the backup server and when the primary server comes online it should be updated with the changes done on the backup server and users will be able to connect to it. In this scenario the failover cluster was the best I think, but the cost for the spftware and special hardware needed for installing failover cluster this is already rejected.
Thanks Adasoft

In the terms of cost for your client Log shipping will be ideal than the replication. But still manual intervention is required in order to perform role changes whenever failover is required. If the instant failover support is required then Clustering is only alternative, if not Like SQL Server 7.0, SQL Server 2000 doesn’t offer automated support for failover or for changing the servers’ roles so that log shipping goes the other direction. Instead, SQL Server 2000 supplies four stored procedures to assist in the failover process and relies on a new built-in Data Transformation Services (DTS) feature to transfer logins from the source to the destination server. You can use the stored procedures to resolve the logins on the former standby server, to change the servers’ roles, and even to change which server the Log Shipping Monitor resides on. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanx Satya, Now suppose, if I can bear the cost of SQL Server enterpise edition and Windows Advanced Server, is there any additional cost required. For example any special hardware requirement? I am very much interested in the automatic roll change done by the cluster Regards Adasoft

I don’t think anymore to spend when you can afford Enterprise edition alongwith OS Server edition on a powerful hardware. For more information on Hardware compatibility list refer to this pagehttp://www.microsoft.com/whdc/hcl/default.mspx and by default you may need to spend on licensing depending upon the client’s needs.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanx again,
If it is like that it would be wonderful. Actually I got this information from some hardware professionals that the hardware required for installing SQL Server failover cluster costs around 35000$. I think I should do more investigation into this matter. Thank you so much for showing me the direction and I would appreciate any help in this matter Adasoft

Merge replication could also give you what you want with both databases being able to be written to, however… merge replication is more complicated to set up and has restrictions/requirements that the application needs to fulfill (as per BOL) Also merge replication would only be able to suffer a temporary outage, how long temporary is depends on the amount of disk on the server, and how you handle identity values across the two instances never an easy answer unless money and time are plentiful <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

Hi,
In my case, the users will be connecting to the database using the machine name or IP address, For example, if the users are given IP1 as the DB server ID, they would be able to connect to the backup database server with the same ID if the primary DB server is down. For both Replication and log shipping the users will have to use a separate machine ID to connect to the database.I think Failover clustering is perfect is this scenario. Only cost is the problem. I want to know the total cost for both hardware and software required for Failover cluster installation. As far as I know, there is a special hardware requirement for failover clustering. I’ll appreciate any help in this matter.

Yes the failover cluster will take care the things on its own without the manual intervention, but on the budget part its expensive than compare to Replication or Log shipping. In terms of the cost its better to contact the vendor rather than findout in the newsgroups. I believe there are many vendors available in India for MS products.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>