SQL Server 2008 r2 High Availability or automatic failover

Question

As per our current project requirement, we have the following Microsoft Stacks:

Windows Server 2008 R2

SQL Server 2008R2

Sharepoint Sererv 2010

BizTalkServer 2010

We have covered almost all required coding part, and now we need to provide the automatic failover / High Availability with Load Balancing(HA & LB) of SQL Server 2008 R2 without any HA Hardware and changing the code/configuration, for HA & LB we purposed to used Peer-to-Peer Replication. Now, we haven't found any configuration kind of thing in SQL Server 2008 R2 to achive the automatic failover without changing the code/configuration i.e. DataBase connection string, do we have any way to configure the automatic failover in SQL. Example: we have two DB Servers PRIMARY and SECONDARY, now in case of failure of PRIMARY, SECONDARY server should be awake and play as PRIMARY Server role without changing any code/configuration, SQL should handle the same.

Answer 1

No, no such thing for P2P repl.

I suggest you separate HA from LB. They are two separate things and a technology which might be good for one might do nothing for the other, or what it does for the other might not be appropriate for your needs.

There are HA/DR technologies that are (more or less) transparent to the app.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 2

Please suggest me the solution for HA only, forget about LB.

What we require to achieve the HA? Is it possible to achieve the same with SQL Server 2008 r2 only?

Answer 3

No HA solution is totally transparent.

You will at the very least lose the connection from the app to SQL Server. The app can re-try, of course, so the end-user might not notice anything. Windows Failover Clustering allow for this.

Database Mirroring requires a bit more from the DBA  (since it is the database whidh fails over, not the whole instance). And you ideannly want to modify the connection string for the app so it points to both servers.

Log shipping is more of a DR solution than a failover solution.

Each HA/DR solution has its strengths and weaknesses. I suggest you start here: http://msdn.microsoft.com/en-us/library/bb522583(SQL.105).aspx

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 4

In current scenario, its quite costly to change the connection string... any other possible way. For changing the connection string already I mentioned in my query that our code is about to deliver to the client so its costly process.

Please suggest any other way apart from changing the code or buying H/W.

Answer 5

If you set up a traditional SQL failover cluster you will not have to change the connection string upon failover.

---

David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

Answer 6

http://msdn.microsoft.com/en-us/library/ms190202.aspx

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 7

What does your mean by "traditional SQL failover cluster"? Please explain the same.

Are you talking about mirroring? If yes, its also required to change the connecting string to failover connection string, which contains the primary DB Server name and failover(mirror or secondary) DB Server name. In case on failure of primary server, failover server take the responsibilities of primary server. I know about this approach and its also required the changes in connection string, and microsoft recommended to use Peer-to-Peer replication in place of same.

Answer 8

As per our current project requirement, we have the following Microsoft Stacks:

Windows Server 2008 R2

SQL Server 2008R2

Sharepoint Sererv 2010

BizTalkServer 2010

We have covered almost all required coding part, and now we need to provide the automatic failover / High Availability with Load Balancing(HA & LB) of SQL Server 2008 R2 without any HA Hardware and changing the code/configuration, for HA & LB we purposed to used Peer-to-Peer Replication. Now, we haven't found any configuration kind of thing in SQL Server 2008 R2 to achive the automatic failover without changing the code/configuration i.e. DataBase connection string, do we have any way to configure the automatic failover in SQL. Example: we have two DB Servers PRIMARY and SECONDARY, now in case of failure of PRIMARY, SECONDARY server should be awake and play as PRIMARY Server role without changing any code/configuration, SQL should handle the same.

Configure SQL failover cluster. If you want to have simple pseudo-one node setup consider using Fault Tolerance feature present in VMware hypervisors. Both this approaches are 100% transparent to clients.

-nismo

Answer 9

Not fulfil my requirement...:(

Any other solution please...

Answer 10

Traditional SQL Failover Cluster does not require changing the connection string upon failover.  However, if you will need a shared storage device where to store your database files.  If you want to set up a cluster without shared storage, you will have to use third party replication solutions.  I wrote a step-by-step article here on how to do that.

http://clusteringformeremortals.com/2009/10/07/step-by-step-configuring-a-2-node-multi-site-cluster-on-windows-server-2008-r2-%e2%80%93-part-3/

---

David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

Answer 11

As I know to use failover we need to use the following connection string: Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True; In current scenario we are using below connection string: Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI; To achieve the failover in traditional SQL Failover, we have to go for WS2008 clustering and need to use the above failover connectionstring, and I cannot go for the same. Thanks for your response.

Answer 12

With failover clustering you do not need a connection string that specifies a Failover Partner.  A SQL Server Failover Cluster has a client access point that moves between the cluster nodes, think of it as a 3rd computer name. This name is actually registered in AD and DNS. As far as your application is concerned, when a failover occurs it doesn't even realize SQL is running on a different server. It DOES NOT require any special connection string, you simply configure the connection string once to connect to the client access point.

---

David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp