Share via

SQL Server 2012 AlwaysOn – Part 5 – Preparing to build an AlwaysOn Availability Group

  • Article

[Edit] You can find the summary of the complete series here.

Version 3.0 of article: please note additional links under 'Preconditions for Windows' pointing to lists of Cluster QFE fixes

Version 2.0 of article. Please note the changes around modification in the Stored procedure referenced to earlier and a new Stored Procedure which now is attached to the article

Since we covered different architecture we can use between AlwaysOn and SAP, we want to look into creating our first Availability group from scratch. We assume that we have a new installation of SAP which we just installed on one DBMS Server. The ASCS/SCS is installed already in the clustered configuration you wanted to have. There also is at least another database server instance running SQL Server 2012 on a different node which will be used as an instance running a replica for our Availability Group. The other server node is in the cluster configuration you want decided to setup for your WSFC configuration which should host the AlwaysOn Availability Group.

Based on some SAP customer feedback we will focus in this article in how to prepare and synchronize the databases on the different replicas so that one can start to create building the Availability Group. Part #6 of the series then will describe the actual creation of the availability group with part #7 describing what is going on behind the scenes when creating the Availability Group. As we will see, the way how to synchronize the logins between the different replicas does need some attention with SAP installations.

Preconditions for Windows

In order to have SQL Server AlwaysOn running smoothly one needs to make sure that the fixes of the following Microsoft QFEs are applied to ALL the server nodes which are participating in AlwaysOn.

Some of the fixes could already be implemented by later fixes or Service Packs you applied to Windows Server 2008 or Windows Server 2008 R2 (SQL Server 2012 is not supported on Windows Server 2003). Therefore it might happen that trying to install such a QFE will result in a pop-up telling that the fix does not apply. It is recommended to run SQL 2012 on Windows 2008 R2 SP1 or higher in general.

For a list of general recommended Fixes for clustering, which we automatically recommend for AlwaysOn usage as well, please check these articles and if necessary apply those QFEs if not yet included:

Before moving to the SQL Server part, make sure that the Cluster Validation Wizard runs without issues. Or just giving warnings we discussed in earlier parts. Dependent on the cluster configuration you can de-select the disk tests from running if the WSFC configuration doesn't have shared disks at all.

Preparation on SQL Server 2012 side

Check for the viability of trace flags which are used in the SQL Server instance. If the SQL Server instance is not a newly installed instance, but one which got upgraded from earlier SQL Server releases, check whether there are trace flags which might not be necessary anymore. Specifically for AlwaysOn delete all the trace flags which had to do with Database Mirroring functionality. Especially trace flag 1449 (described here: https://blogs.msdn.com/b/saponsqlserver/archive/2007/09/26/what-did-we-learn-using-database-mirroring-over-the-last-two-years-in-our-sap-erp-system-second-revision.aspx ) needs to be deleted as startup trace flag since it will disallow some configurations we need to perform for AlwaysOn.

First step to be taken is to enable AlwaysOn for each of the SQL Server instances which are supposed to run replicas. This is done by making these SQL Server instances aware of the WSFC configuration which should be used for AlwaysOn.

In order to perform this step, you need to open SQL Server Configuration Manager. Check for the service of SQL Server 2012 RDBMS, mark it and right click to open ‘Properties’. Change from the tab ‘Log On’ to the tab ‘AlwaysOn High Availability’ and you'll see the screen below:

clip_image002

Check the box in front of ‘Enable AlwaysOn Availability Groups’ and press Apply. You will then need to restart the SQL Server service. Hence this is not an online configuration setting.

After the restart, SQL Server 2012 knows the Cluster to use as framework for AlwaysOn.

Initial Synchronization of the databases

In the process of creating an Availability Group, SQL Server does offer to perform the initial database synchronization. This would be done by performing a backup on the server we are working on (assumed to be the primary replica initially). That backup would then be restored to the secondaries. However given the fact that we usually are talking large SAP databases of quite a few TB in size, we recommend to perform the synchronization manually. In order to do that you need:

· The exact same LUN configuration on the secondaries as there is on the primary side. This relates to drive letter/mount points. Ideally the same sizes/volumes available on the LUNs since extending the size of data files are transmitted as well and need to be executed on each of the secondaries as well.

· One full database backup which eventually needs to be copied to the DR site as well

· While copying and restore of the backup is going on the primary and the future secondaries, we need to perform transaction log backups which are getting copied to the future secondary replicas as well. It is best to use Log-shipping for performing this. Only thing is that the restore job on the secondary replicas would need to be disabled for the time the database backup is getting restored

· After the restore of the initial backup on the secondary servers is done, the transaction log backups need to be restored. In case of having Log-shipping established between the primary and the different secondaries, enabling the Log shipping restore job on the secondary will take over the task of restoring those logs. Dependent on the time it took to copy and restore the actual backup, this catch-up by restoring Tlog backups can take some hours. Using Log-shipping for this process does have the advantage that the secondaries are kept synchronized for longer time or up to the time where one can take a downtime to create the AlwaysOn Availability Group.

For the restore, please don't recover the databases. The databases need to remain in non-recovered state. In order to do so please go to the ‘Options’ pane and change the default of ‘Restore with Recovery’ to ‘Restore with Norecovery’ as seen here:

clip_image004

Second step after the databases on the future primary and the future secondaries are in sync is to make sure that the SQL Server logins (visible in sys.server_principals) are created in each of the other instances supposed to run replicas and are matched correctly to the users in the SAP database.

SAP creates a special login which is mapped into a user within the SAP database. That user will own a schema within that database. In that schema we are supposed to find the tables, views, stored procedures of the SAP schema.

In our example, we have a SAP ABAP system with the SID=E64. Subsequently we have the following logins created when installing SAP against SQL Server:

  • Domain\e64adm Integrated user login
  • Domain\SAPServiceE64 Integrated user login
  • e64 SQL Server login

For SAP Java instances, there will be no <sid> login, but a SAP<SID>DB login which is created and which will be mapped into the SAP database as explained below with the example of an SAP ABAP stack. Review OSS Note 1294762 - SCHEMA4SAP.VBS if the SAP SQL users in the primary system are not configured correctly.

The first two logins are mapped into the sysadmin role which automatically will map them into the dbo schema of the SAP database. A schema where no SAP objects are existing in.

The <sid> (e64) login is mapped into serveradmin role and to user <sid> (e64) in the <SID> (E64_ database. There will be a schema which is named <sid> (e64) in the <SID> (E64) SAP database and which is owned by the database user <sid> (e64). See the graphics for below for an overview:

clip_image006

The user e64 in the database E64 would look like this:

clip_image008

As displayed, the login <sid> (e64) is mapped into this database user <sid> (e64). The database user itself owns the schema <sid> (e64).

To check whether the ownerships are fine can also be check with this SQL statement:

select sp.name as 'Login Name', sp.principal_id as 'Server Principal ID', sp.type_desc as 'Login Type', dp.name as 'Database User', dp.principal_id as 'Database Principal ID', dp. type_desc as 'Database Role', s.name as 'Schema Name'

from sys.server_principals sp, sys.database_principals dp, sys.schemas s

where dp.sid=sp.sid and dp.principal_id = s.principal_id

and sp.name = '<sid>'

The result of this query in our case (SID=E64) looks like:

Login Name

Server Principal ID

Login Type

Database User

Database Principal ID

Database Role

Schema Name

e64

282

SQL_LOGIN

e64

5

SQL_USER

e64

As you can see in the query above the link between the login mapped into the database is the column ‘sid’ which contains a GUID. The value needs to be the same in the sys.server_principals (master database) table containing the logins and sys.database_principals in the SAP database. If the result comes back empty, then this linkage is broken and the SAP application will not work.

In order to generate the logins necessary for the SAP application on the instances which are supposed to run replicas, DO NOT create them manually, but perform these steps:

  1. Take the attachment and unzip it. Load the SQL Script into a Query Window of SSMS and create the Stored Procedures, ideally in master database

  2. Now execute:

    execute sap_help_revlogin

    The output will be a list of SQL commands like the following:

    --

    Login: e64 CREATE LOGIN [e64] WITH PASSWORD = 0x02008F15AA1BE9DBC8C7C9DB6DC66975A1FE15BB43120465A6576C677460A4E3C507BDF89809639F57DB58D72F3CDBE88CFE519D43692514F0B19A211AC1ABA66F14868F089E

    HASHED, SID = 0x98C419644690D1428B8BE50102921514, DEFAULT_DATABASE = [master],

    CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

    ….

  3. Copy the output (result) of the execution of sap_help_revlogin to the instances supposed to run the replicas. Execute the result set in a Query window in SSMS. There will be a list of error messages telling that this or that login does exist already. This is normal and can be ignored. The important part is that the SAP logins as well as their permissions on the server level will be created as on the original server.

The key difference between you creating the logins manually and executing the commands created by the script is that the ‘create login statements’ of the script will create the logins with the exact same ‘sid’ value as found on the principal server. Means there is a match between the ‘sid’ values in sys.server_principals (in master database) and sys.database_principals (in the SAP database)

In the first version of the blog we referenced a similar procedure which is released with this KBA: https://support.microsoft.com/kb/918992/

However that Stored Procedure fell a bit short on functionality and did not script and transfer server level permissions, server role assignments and database assignments of the account. These shortcomings are fixed with the new Stored Procedure attached to the version 2 of this article.

SAP releases supporting AlwaysOn

On the SAP ABAP side, AlwaysOn will be supported for all SAP applications which run on the SAP ABAP stack and which are one Basis releases which are getting supported for SQL Server 2012. Reason is that the SAP executables are using the SQL Native Access Client of SQL Server 2012 when connecting against SQL Server 2012. Compared to the predecessor releases there are changes which had to made to work with AlwaysOn in SNAC. Therefore AlwaysOn will not properly work with SNAC versions of releases before SQL Server 2012.

On the SAP Java side, Java instances running on JVM 1.4.xx will not be able to benefit from AlwaysOn since the older JDBC driver did not get changed to work with AlwaysOn configurations. The new JDBC driver 4.0 from Microsoft will be necessary to leverage AlwaysOn for Java. Means only Java instances which are running on JVM 5 and 6 will be able to benefit from AlwaysOn in SQL Server 2012 once SAP releases support for the Microsoft JDBC driver 4.0.

sap_helprevlogin.zip

Comments

  • Anonymous
    September 21, 2015
    The comment has been removed