Patrick van den Born - Citrix Technology Professional

As a Citrix Consultant I’ve gained a lot of knowledge about Microsoft SQL and Microsoft SQL AlwaysOn over the years. I want to share my SQL knowledge with you. Please keep in mind that I’m a Citrix guy and not a SQL guru.

In the next blogs I’m going to share my knowledge about connecting Citrix Provisioning Services, Citrix XenDesktop and Citrix XenMobile to a multi subnet SQL AlwaysOn cluster.

This blog covers the following topics:

How much memory does my SQL server need? (Page Life Expectancy)
Limit SQL Server memory
Creating (Basic) AlwaysOn High Availability Groups
SQL accounts and AlwaysOn failover, why logging in after a failover doesn’t work OOTB
SQL accounts and AlwaysOn failover, copy SQL users to AlwaysOn replica servers
Performing an AlwaysOn database failover

How much memory does my SQL server need?

SQL-servers will perform better when a lot of memory is added and assigned to Microsoft SQL server. Adding to much memory is inefficient for your virtualized infrastructure. You do not want to have a SQL-memory monster of 64GB of RAM. And when using SQL AlwaysOn two or more of these monsters will cost you a lot of MEM!

When it comes to memory sizing or SQL performance troubleshooting, you can simply monitor the “Page Life Expectancy” perfmon counter of the SQLServer: Buffer Manager.

This counter represents how long (seconds) a page will live in memory. In other words: “SQL Server can read these pages from memory instead of disk”. The rule of thumb is: The PLE should be 300 seconds (5 minutes) or higher. So if you have a PLE below 300, your SQL-server has not enough memory. Or you have a database/application with a crappy query! In most of the times as an Infrastructure guy, I’m dealing with SQL servers dedicated for Infrastructure purposes: Citrix XenDesktop, VMware vSphere, RES One Workspace, RES One Automation, Ivanti Identity Director, Microsoft WSUS, etc. So PLE for these servers is a good indicator for memory size and performance.

Advise: Add the Page life expectancy to your monitoring tool. If it drops below 300 create warnings.

Limit SQL Server memory

In the first blog I’ve described the installation and setup of a SQL AlwaysOn availability cluster. In the how-to, we have created a policy where the pages of the SQL server service account are locked in memory and cannot be stored in the page file of the disk (Lock pages in memory). Due to this, it is a good idea to limit your SQL-server so it won’t eat all the memory resources needed by the operating system or other SQL instances on the same SQL server.

You can find the blog here: https://patrickvandenborn.blogspot.nl/2017/11/how-to-install-and-configure-microsoft.html

The memory limitation of a SQL instance can be configured as follows:
1. Open Microsoft SQL Server Management Studio
2. Connect to the SQL server which is hosting the SQL database

3. Right click the server and click Properties:

4. Configure the Maximum server memory for this instance. Rule of thumb give the OS at least 3GB of mem. So in my case: 4 GB – 3 GB OS = 1 GB for SQL Server:

5. Restart SQL Service

Creating (Basic) AlwaysOn High Availability Groups

The creation of a (Basic) AlwaysOn High Availabilty Group is the same for every database. Basic Availabiltity Groups can contain only one database, so for every database a Basic Availability Group is needed. For regular (SQL Enterprise) Availability Groups, you can add more databases to the same availability group.

A database must meet the following conditions to be added to an availability group:

Recovery mode: Full
At least one backup of the database is created.

So create a Basic Availability Group as follows:

1. Open Microsoft SQL Server Management Studio
2. Connect to the SQL server which is hosting the SQL database

3. Right click the database and select Properties:

4. Go to Options and verify if the Recovery model is set to Full. If not, change it to Full. Click OK:

5. Right click the database and click Tasks --> Back Up..

6. Click OK:

7. A backup of the database is completed. Click OK:

8. Right click Availability Groups and click New Availability Group Wizard…

9. Welcome page for availability group wizard, click next:

10. Enter the name for the new availability group and click Next:

11. Select the database for the availability group (only multiple when using SQL Enterprise Availability Groups), click Next:

12. Click Add Replica..:

13. Add SQL server for secondary replica, and click Connect:

14. Select Automatic Failover and ensure Availability mode: Synchronous commit. Click Listener tab:

15. Enter DNS Name for the new Listener. Enter default SQL port 1433. Click Add to add IP’s for this listener:

16. Enter the IP-address for the listener. If multi subnet failover cluster is configured, enter IP-address for all the possible failover subnets (in this example 2 subnets):

17. Listener IP for subnet 2:

18. Click Next:

19. Select Full database and log backup for the initial seeding to the second SQL-server. Use a temporary file share. Click Next:

20. The availability group is verified, click Next:

21. An overview of the availability group, click Finish to create the availability group:

22. Configuring the availability group:

23. The availability group created successfully, click Close:

New created availability group:

Multi subnet DNS-records for Availability Group Listener:

SQL accounts and AlwaysOn failover, why logging in after a failover doesn’t work OOTB

When using SQL accounts with a AlwaysOn cluster, logging in with SQL accounts after a database failover doesn’t work out of the box. The reason for this is, that the failover SQL servers doesn’t have the correct SQL-server login for that particular SQL user by default. So the SQL-server is denying the login from the server level.

Some products in the end user computing industry like RES/Ivanti are configured with SQL user accounts. So additional configuration is needed when using SQL user accounts with AlwaysOn.

Screenshots of “the problem”
So I’ve created a Basic Always On high availability group with database PBO-SQLDB-withSQLuser. The SQL user PBO-SQLUser is configured as dbo for the database PBO-SQLDB-withSQLuser:

The SQL Server PBO-SQL01 does have a SQL user PBO-SQLUser because the database and user is initially created on PBO-SQL01. When the “Basic” AlwaysOn Availability Group is created, the SQL users of that database are not synchronized to the other Availability Replica Server PBO-SQL02. Active Directoy users and groups will be synced to the other Availability replica servers.

SQL server logins of PBO-SQL01:

SQL Server logins of PBO-SQL02:

When testing the ODBC-resource with PBO-SQL01 as primary SQL Replica server:

When testing the ODBC-resource with PBO-SQL02 as primary SQL Replica server:

After failover the SQL user is in the security of the database, but there is no SQL login for the SQL server:

So logging in with the SQL username and password will fail after database failover to the second SQL AlwaysOn Replica Server:

SQL accounts and AlwaysOn failover, copy SQL users to AlwaysOn replica servers

Add the SQL user(s) with the same SID, username and password to all other AlwaysOn replica servers as follows:
1. Open Microsoft SQL Server Management Studio
2. Connect to the source SQL server where the SQL-users are configured

3. Click on the SQL-server and click New Query

4. Paste the following SQL Query and press Execute

SQL Query:

USE master

GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

DROP PROCEDURE sp_hexadecimal

GO

CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(256),

    @hexvalue varchar (514) OUTPUT

AS

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

DECLARE @tempint int

DECLARE @firstint int

DECLARE @secondint int

SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

SELECT @firstint = FLOOR(@tempint/16)

SELECT @secondint = @tempint - (@firstint*16)

SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

DROP PROCEDURE sp_help_revlogin

GO

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @type varchar (1)

DECLARE @hasaccess int

DECLARE @denylogin int

DECLARE @is_disabled int

DECLARE @PWD_varbinary varbinary (256)

DECLARE @PWD_string varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @tmpstr varchar (1024)

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)

DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

ELSE

DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@type IN ( 'G', 'U'))

    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'

    END

    ELSE BEGIN -- SQL Server authentication

        -- obtain password and sid

            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state

        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )

        BEGIN

          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

        END

        IF ( @is_expiration_checked IS NOT NULL )

        BEGIN

          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

        END

    END

    IF (@denylogin = 1)

    BEGIN -- login is denied access

      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

    END

    ELSE IF (@hasaccess = 0)

    BEGIN -- login exists but does not have access

      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

    END

    IF (@is_disabled = 1)

    BEGIN -- login is disabled

      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

    END

    PRINT @tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

   END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

5.   Then execute SQL Query:

exec sp_help_revlogin

6. The result of the query is a message with SQL queries to create the SQL Login on other SQL servers with the same SID, username and password. So copy all queries for SQL-users you want to copy to the other SQL AlwaysOn Replica servers:

Example:

-- Login: PBO-SQLUser

CREATE LOGIN [PBO-SQLUser] WITH PASSWORD = 0x0200751C9E00619211DF04A81F0CABAB80BD6D17E27C27F7911139F882E78DA5BEAE21DD94F05959C8BF2DEB72560DD737C2DC806E4D1A8F7CCAA64A48E533033AF10E7D5D53 HASHED, SID = 0xB5772CBA98308B46BAE4DD13FB6D3CE2, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

7. Login to the second SQL Availability Replica Server:

8. Click on the destination SQL-server followed by New Query

9. Paste the new SQL user login query and execute:

10. The user is created with the correct SID on the other SQL AlwaysOn Replica Server:

11. When we test the login again (after AlwaysOn failover):

Performing an AlwaysOn database failover

You can perform a database failover or failback as follows:
1. Open Microsoft SQL Server Management Studio
2. Connect to a SQL AlwaysOn Availibilty Replica