question

sql server avatar image
sql server asked

Database Server connection

Hi All, Sql server 2012 standard edition We are using two database servers, databasesserverI is connected for application to store data. and databaseserverII using to replication data from datbaseserverI. Here databaseserverII is not available for application users. Now databasesserverI is connected and working with application to store data. Is there any chance to connect application to databaseserverII automatically when databasesserverI is not available(etc:- server down or server running slow) to application Could you explain how to write connection string's to satisfy above scenario. Thanks In Advance.
connection-string
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image Shawn_Melton commented ·
Are you using SQL Server replication?
0 Likes 0 ·
sql server avatar image sql server commented ·
Thanks all........ Yes we are using transactional replication.
0 Likes 0 ·

1 Answer

·
aderossi avatar image
aderossi answered
The only way to do it automatically is using AlwaysON, which is supported in standard edition for two nodes. Implementing Always on is not simple. Sadly, this is not only about connection strings. You have to mount a Windows Server cluster and then a SQL Server cluster to provide High Availability an automatic failover to your databases. Try here: http://blogs.msdn.com/b/srinivas-v-v/archive/2013/06/25/setting-up-always-on-availability-group-on-sql-server-2012.aspx
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aderossi avatar image aderossi commented ·
You can also use database Mirroring. This tool is much simpler to implement than AlwaysON, but Microsoft recommends not using it anymore in SQL 2012 or later versions. But it works fine and has automatic failover Try here: http://technet.microsoft.com/en-us/library/ms189852.aspx
2 Likes 2 ·
sql server avatar image sql server commented ·
we already configured fail over clustering on databaseserverII (by using two nodes active/passive mode)after that only we are replicating data from databaseserverI.
0 Likes 0 ·
raadee avatar image raadee commented ·
Just remember that Always On is just a marketing term that confuses people. Availability groups, failover clustering, mirroring and so on are the actual technologies that one should refer to when talking HA/DR. Always on is not equal to Availabilty groups.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.