Monday, January 8, 2018

Microsoft SQL and Microsoft SQL AlwaysOn basics for Citrix Admins

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


3.    Right click on the Availability group and click Failover…


4.    Failover welcome page, click Next:


5.    Select the new Primary relica and click Next:


6.    Check the failover and click Finish:


7.    Failover or Failback completed:

1 comment:

  1. Great article Patrick - it helped me immensely. I just want to alert you of a couple things I needed to do to make this work and it tripped me up a little.

    Open 1433 Inbound TCP ports for SQL
    Open 5022 Inbound TCP ports for SQL-AlwaysOn - before adding replica

    ReplyDelete