This post has already been read 31020 times!
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!
- 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
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
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)
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)
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
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.
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
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.