sql data mirroring + Virtual IP/Instance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

High Availability (2005)

sql data mirroring + Virtual IP/Instance

Author

Topic

cz
Starting Member

19 Posts

Posted - 2010-09-14 : 18:02:22

Hi,

I have SQLA as primary, SQLB as mirror and a witness node. Can I have a virtual IP/InstanceName/(or dynamic DNS?) like what is in Windows/SQL clustering, to allow a transparent interface to applications?

Currently we have about 100+ applications using SQLA. When SQLA is down and SQLB is online, it seems very tedious to switch IP/InstanceName to SQLB on all applications. Failover clustering is not an option since we require disk redundancy as well.

All comments are very welcome!

cz

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-14 : 18:11:45

Are your applications using the SQL Native client?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cz
Starting Member

19 Posts

Posted - 2010-09-14 : 18:15:13

Not all. We have many old 3rd party apps.
Thans

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-14 : 18:30:23

For those that don't use it, you could probably write a custom DNS script that would detect the automatic failover and do the DNS switcheroo. I do not know what's available for scripting from a DNS standpoint though.

We do manual failovers. We are fully scripted to do it, so it only takes a few seconds/minutes to get everything up on the DR server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-14 : 18:30:58

We don't use automatic failover since high safety mode is required, and it is too slow due to the two-phase commit. Performance is key on my systems, hence the need to use high performance mode.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cz
Starting Member

19 Posts

Posted - 2010-09-14 : 18:41:31

Right, the dynamic DNS script seems the only option.
Manually changing SQL instance name/IP on 100+ applications seems not realistic for us, even on the high performance mode.

Thanks Tkizer for your time!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-14 : 18:48:12

But how often are you expecting a failover?

You have quantify the performance hit of high safety mode with the possibility of a true failover.

Our DR site is over 300 miles away, and there's a 12 millisecond network latency hit per query. Multiply that out by the number of queries, and we are talking about major latency (I forgot to turn SAFETY OFF, so I know this firsthand!). We only do planned failovers where we have a team of people making all of the necessary changes, and since we are fully scripted, we don't need to touch many things.

That's just my two cents!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources