SQL Mirroring and Automatic Failover using a Citrix NetScaler

SQL Mirroring and Automatic Failover using a Citrix NetScaler

SQL Mirroring and Automatic Failover using a Citrix NetScaler

This post has already been read 25240 times!

sql_netscaler_failover

SQL Mirroring and NetScaler Fail Over

I was asked to provide high availability with regard to the Citrix back end databases. I started to look into setting up mirrored partners for all the Citrix products and some of them offered it as a standard install option, however – some didn’t. Therefore I had to look at other ways of getting automatic fail over for the SQL side of things.

Most people running a Citrix environment will have a NetScaler sitting somewhere on the estate so I started to take a look at the NetScaler DataStream functionality hoping this would provide the answer, and it did!

The senario.

  • 1 SQL Server as the principal mirror
  • 1 SQL Server as the mirrored server
  • 1 Witness SQL Server to provide automatic failover
  • 1 NetScaler VIP providing central access to the SQL backend

Pre requisites
To get this up and running correctly you will need the SQL Server running in mixed mode authentication and you will have to add an account to the SQL Server that the NetScaler will use to query the databases.
SQL Mirroring needs to be set up (If you need to know how to do this just google it – loads of guides out there.)

Setting it all up

Database User
Navigate to System – User Administration – Database Users
Add a user that will be used to connect to the back end database servers (This user must be present on the SQL Servers or authentication will fail)

netscaler_user

NetScaler Database User

The Virtual Server
The First thing you will need to do is set up the servers on the NetScaler.
Navigate to Traffic Management – Load Balancing – Servers
Add an entry for each SQL Server (Principal and Mirror)

add_servers

Add The Servers

 

You will then need to set up the monitor that will identify if the service is the Principal Database Server or not.

Navigate to Traffic Management – Load Balancing – Monitors

  • Click Add
  • Give the Monitor a name (Include the database name you are monitoring for ease of administration)
  • Select MSSQL-ECV as the monitor type
  • Select the Special Parameters tab
  • Enter “master” in the Database field
  • Enter “select mirroring_role_desc from sys.database_mirroring where database_id = DB_ID(‘YOUR_DB_NAME’)” in the Query field
  • Enter the user you created earlier in the User Name field
  • Enter “MSSQL.RES.ROW(0).TEXT_ELEM(0).EQ(“PRINCIPAL”” in the Rule field
  • Select the correct version of SQL from the Protocol Version drop down box
  • Click on Create

You should now see the new SQL Monitor listed

monitors_created

Monitors Created

Now you set up a service for both the Principal and Mirror Database Servers and assign your new monitor to it. This should return the Principal as Up and the Mirror as Down.

Navigate to Traffic Management – Load Balancing – Services

  • Click Add
  • Enter a Service Name and select the relevant Server from the drop down
  • Set the protocol to MSSQL and put in 1433 as the port (assuming this is the relevant SQL Port your using)
  • Locate your new service from the list on the left and click Add
  • Click Create

Make sure you add a Service for each SQL Server. After creating these you should see 1 service Up and 1 Down that corresponds to your current live and mirrored SQL Server.

create_service

Create Service

services_created

Services Created

Finally you need to set up the new Virtual Server that will load balance the traffic depending on the current live SQL Server.

Navigate to Traffic Management – Load Balancing – Virtual Servers

  • Click Add
  • Enter a name for the new Virtual Server and give it the IP Address you want for the VIP
  • Select MSSQL for the Protocol and enter 1433 for the port (again assuming this is the relevant SQL Port your using)
  • Select BOTH the services you created previously from the available services
  • Click Create
new_vip

New VIP

up_and_down

Up and Down

 

Thats it!

You should now see a new Virtual Server showing as up listed
If you scroll to the right you will notice that the health is 50%, this is right as it will be pointing to the primary node. When the SQL Database fails over the services you created will know the currently active node and set themsolves accordingly.

As long as you have set your applications to point to the VIP (prefereably using dns) then they will continue to work as expected.

Hope this helps somebody out.

Laters,
b@m

8 thoughts on “SQL Mirroring and Automatic Failover using a Citrix NetScaler

  1. Ben Hilly

    Excellent article, but unfortunately i came across some Kerberos (I think!) issues…

    The moment I try to connect on the new Alias which represents the Load Balancing IP address (alias.company.local), i get bouced with the message “The target Principal Name is incorrect. Cannot generate SSPI context.”

    Any idea on this? It’s a new installation with 1 mirrored database. The Service Account on which the SQL service runs is Domain Administrator.

    Many thanks!

  2. Ekrem Onsoy

    So what’s the catch Bretty? I mean the Mirror Server will be “unreadable” anyway? All connections will be directed to the Primary Server. Automatic failover is not the capability of Netscaler itself, but Database Mirroring’s via the Witness Server. Why would anyone need Netscaler for a topology like this?

    What value does Netscaler add to your environment in a scenario like this? I’ll appreciate if you shed some light.

    1. Bretty Post author

      Hi there. Thanks for the comments. The role of the NetScaler was not to handle the actual SQL failover. As you started the witness server and mirror partner will deal with that.

      The role of the NetScaler is to front the connection and provide access to the currently live SQL server, be that the primary or the fail over.

      Some older applications dont allow you to configure a primary and a mirror DB server. In this case eg.
      Primary – SQL1
      Failover – SQL2

      In the event of a failure an app reconfigure would be required to repoint it to the new live DB.

      If you fronted this with NetScaler you could point the application to SQL.domain.local or something like that and let the NetScaler manage where the traffic does depending on the current live DB server.

      Hope this clears up what I was trying to achieve with this.

      Dave.

      1. Ekrem Onsoy

        Then it seems that in this use case NetScaler is a “redirector” just like the Listener in an AlwaysOn AG or simply a Virtual Server Name in a Failover Clustering scenario.

        So for this case I think setting up a Failover Clustering would do the same job. So it’s up to the decision makers to decide which one would be more benefitial and cost effective.

        Thanks for the clarification Dave! Cheers.

Leave a Reply

Your email address will not be published. Required fields are marked *