1

Hallo SQL jedis,

I want my database to be always available. So I've set up database mirroring, which is working peachy.
My question though, is how can I consistently address the database? I want database access to be transparent of the underlying mirror status. Currently, if I'm sending traffic to SQL-SERVER-01, and a failover is triggered, my traffic now needs to go to SQL-SERVER-02.

Is there any way to abstract that? Can I use the clustering feature of Windows Enterprise to have one virtual IP address that automatically floats between whichever is the primary SQL mirror?

Laptopgrrl
  • 11
  • 2

1 Answers1

0

Yes, put the SQL-SERVER-02 in the connection string as the failover partner.

Data Source=SQL-SERVER-01;Failover Partner=SQL-SERVER-02;Initial Catalog=myDataBase;Integrated Security=True;
mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • Thank you, but it's addressing with an IP address I'm looking for - so that, eg, 172.16.0.88 will always be the IP of whichever SQL mirror host is active. – Laptopgrrl Apr 12 '11 at 00:17
  • Mirroring won't do that. With database mirroring you could have multiple databases that are mirroring, and the databases could be active on different servers at the same time. Mirroring doesn't provide instance level failover so it can't control the IP address. Using the "Failover Partner" parameter in the connection string is the way to go for automatic failover. If your driver doesn't support mirroring then automatic failover then you'll need to setup SQL alerts and Agent jobs to respond to the alerts to move IPs from one server to another on failover. This will be messy though. – mrdenny Apr 12 '11 at 03:04
  • I'm curious if anyone's used Windows' Failover Cluster Manager to provide the intelligence for virtual IP swapping. – Laptopgrrl Apr 12 '11 at 16:35
  • That could be done pretty easily, however you would need to keep in mind that if you have more than one database on the server, there is no way to guarantee that all the databases on active on the same server. What driver are you using that you can't use the Failover Partner setting like you can with the native drivers? – mrdenny Apr 12 '11 at 17:55