Based on repeated customer questions, especially around the readable secondary of AlwaysOn, I would like to summarize in (quite) a few words the abilities to read from a secondary replica/mirror with the two main functionalities of SQL Server Database Mirroring (DBM) and AlwaysOn. Though SAP Netweaver applications are always requiring Read/Write access, a lot of customers still seem to have the need to do some read-only activity. These might be data extract from the SAP ERP database or other read-only applications customer use to fill gaps in analytics, reporting, etc. Using a HA/DR functionality like DBM or AlwaysOn, it is justified to ask whether the mirror/secondary replica can be used for such a purpose of read-only access. Therefore we will explain in this article how it can work with these two SQL Server HA/DR technologies.
The only mirror of SQL Server DBM is in principle not accessible for read-only activity. However using another technology of SQL Server Database Snapshots, one can make this happen. See more information on how Database Snapshots work in this recent article related to SQL Server 2012: http://msdn.microsoft.com/en-us/library/ms175158.aspx Since no real changes were done to the way Database Snapshots work, the article does apply very well to earlier versions of SQL Server as well (introduction of Database Snapshots happened with SQL Server 2005)
Hence the steps would look like:
Advantages/Disadvantages of this solution?
Now we had customers who went another way and from time to time decided that they would like to use the DBM Mirror for some other purposes where they also would be able to eventually write against this mirror database. Well knowing that this meant to go through the initial steps of database synchronization again (backup on primary, restore to secondary, get up to synchronized point). The steps these customers performed looked like:
After this the mirror database would be fully usable or any activity against it, like acting as DBMS for a sandbox system or something like this. And that is where an evil chain of events could unfold under certain circumstances!!
In order to explain more, we need to look into what the SAP application side knows about the DBM configuration. Since DBM does not work with a virtual name, the SAP Netweaver side needs to know the name of the principal and the mirror. Usually the SQL Server Client side used by SAP is getting the name of the mirror during connecting and keeps it in memory. As long as the SAP process is up and running and failovers do happen, the SQL Server client will take the information it has about the mirror and will redirect connection attempts to the mirror side if the principal is not reachable. However in the SAP case we do have cases where SAP processes restart and with that the information in the SQL Client for the restarted process is lost. Hence we need to make sure that the information for the principal and the mirror can be read out the SAP Default profile. As a result we look at parameters as described in OSS Note 965908 – SQL Server Database Mirroring and SAP Applications .
Another problem we encountered in situations like this:
So now let’s assume, the following scenario:
In case of a SAP reconnect of a SAP process, the SAP process will submit the content as found in dbs/mss/server to the SQL Server Client to establish a connection. Before the acting mirror (node1) got opened up, the connection attempt against node1 got refused, hence the SQL Client got direct to the server that was named as failoverpartner which was the acting principal node2. There a successful connection could get established.
Now that the DBM configuration is dropped and the former acting mirror (node1) and node2 are accessible, the connection attempt in case of a SAP process (formerly connected to node2) reconnecting will look like:
Needless to say that this scenario would lead to disaster. Worst on it, it will take a while until this issue is recognized. Suddenly there might be ABAP short dumps talking about duprecs, etc. Number Range numbers suddenly might collide and other very strange things will happen.
How can we prevent such an issue in this scenario?
Obviously there are a quite a few measures one could take in order to prevent such a disaster scenario in case one really wants to open the former mirror database for other purposes. At least one of our customers learned it the hard way.
One of the goals of AlwaysOn was to enable customers using the secondary replicas for reading data as it actually changes. Means as some data gets changed, one should be able to read those changes in relatively short time on the secondary replicas. Sure there is a small delay which is determined by the time it takes to send and persist the Transaction Log data in the secondary replica plus how long it takes to redo those changes on the secondary replica. But under ideal conditions we assume that taking less than 1second.
Another thing which needed to be solved is avoiding collision of the redo operations on the secondary (changing data) and the reading requests. In normal read-committed isolation level, one could imagine readers blocking the redo thread from applying the changes. Or the redo thread would block readers from reading for short time. First case being the more intrusive to the system usually. In order to avoid these situations, the readers will read in snapshot isolation level on the read-only database. Means as the redo thread is performing its changes to the data, the secondary needs to keep at least the origin version of the data until the redone transaction got committed in order to provide an unblocked consistent view to the reading applications.
Snapshot isolation level is enabled at the moment the secondary is set to be readable as well. However in order for the secondary to become accessible all open transactions which got initiated on the primary and transferred on the secondary need to have opened with readability on the secondary enabled. That means if there is a transaction on the primary running for hours before committing, the ability to read from the secondary might be delayed for quite a while (see later).
What also got introduced are two different modes of read-only connectivity. In one mode all connections are accepted by the secondary replica, independent of the intent of the application. Means there is no further check or anything when establishing the connection. According to SAP OSS Note 1772688 – SQL Server AlwaysOn and SAP Applications we don’t support these kind of readable secondary replicas with SAP. Reason is that we under no circumstances would like to end up in a situation where parts of the connections end up somehow on a readable secondary.
The second mode of read-only connectivity is that the secondary replica only would accept connections which signal a read-intent. In those cases, the connection string needs to have a connection parameter like: ApplicationIntent = ReadOnly defined. More information around this mode in this article: http://msdn.microsoft.com/en-us/library/hh213417.aspx . If the application submits this connection parameter, one even can get special read-only-routing when one connects against the Listener name of the AwaysOn configuration. In case of connecting with Read-Only intent against the Listener, one first would connect to the primary in order to check whether there is some read-only-routing setup. If it is the case, the connection will be routed to the secondaries in the priority as those are defined (see later in this article). Thereby it doesn’t matter whether the secondary replicas are set to the Availability Mode Synchronous or Asynchronous.
When we look at the scenarios where SAP customers would like to have read-only access to a secondary replica, the advantages and disadvantages would look like:
So let’s play through a few scenarios of using a readable secondary and how to avoid a disaster situation as described above with the DBM scenario.
First Step: Make the secondary readable
In order to make a specific secondary replica read-only use SSMS. In the Object explorer got to ‘AlwaysOn High-Availability’ à ‘Availability Groups’ and mark the Availability Group the Secondary Replica with the database you want to put read-only is part
Select Properties and you’ll see this screen:
In our case, we would like to have the synchronous replica becoming the target for our read-only application. We now change the settings like indicated in the next screen shot:
As mentioned, with SAP we would like to allow read-only applications using the read-intent parameters in the connections string only.
Second Step: Check whether the Secondary is readable with read-intent connectivity
In order to check whether the secondary is able to accept read-only connections, you now can connect with SSMS against the SQL Server instance running the read-only database replica and issue a read like:
select * from <sid>.SVERS
The failure message we would expect to get is:
Msg 978, Level 14, State 1, Line 1
The target database ('E64') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.
Since we didn’t instruct SSMS to submit the read-intent when connecting to the secondary database, it is now telling that we do not have the correct connection parameters set. This failure message would be expected when trying to connect with a SAP Netweaver application as well. If this failure message doesn’t come up it means that you did set the secondary unrestricted readable. Something we don’t support with SAP configurations.
Since you got the Query Window connected to the Secondary instance and you got the error above, click the right mouse key in the query Window and choose ‘Change Connection’ as seen here:
Choose Options as shown here:
In the appearing Dialog choose the tap ‘Additional Connection Parameters’ and add the string ‘ ApplicationIntent = ReadOnly’ as seen below:
Now press ‘Connect’. This SSMS connection is now established with a read-intent. Hence the query issued against our read-intent readable secondary replica should succeed.
However if the little select, usually completing in a few milliseconds, is hanging. Investigate by issuing this statement:
select * from sys.dm_exec_requests
against the secondary replica instance. You will find at least one entry where the execution of the statement is suspended (column: status) and the column wait_type will show a value of:
This signals that there is at least one transaction open on the primary system which had been opened before you switched the secondary to become a readable secondary. As mentioned, when switching to a readable secondary, we need to provide committed versions before the Redo Thread is performing changes to the data. However with a transaction still open on the primary that has been open before switching to readable secondary, such a committed version of the data could not be provided on the secondary. Hence the select will wait until the transition is successful. The transition becomes successful, when all the transactions that were open before switching are committed or rolled back on the primary.
Another error message which came up could be:
Msg 976, Level 14, State 1, Line 1
The target database, 'E64', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.
Would indicate that the database is not switched to any readable mode.
Step 3: Think about what how to connect the read-only application.
You can connect the application using the node name of the particular node\instance name of running the secondary replica. This is expected to be different than the name the SAP Netweaver applications are connecting against. Those would use the Listener Name to connect to the AlwaysOn configuration and would not at all refer to a single server name
Disadvantage of connecting the read-only application to a specific instance name would be that in case of a failover, there would be no alternative server and the application would need to be stopped and adapted in case of the secondary replica coming down.
You also could connect the read-only application by using the listener name and leverage read-only-routing. However in this case you really need to makes sure that:
Let’s assume we got the same 3 node configuration as before. Now we would set the Readable Secondary setting of all the replicas to ‘read-intent only’. But that is not enough. For every one of those instances we now need to setup the order the read-intent-only connection should be routed for the case the specific node is in the primary role. If we don’t do that, the read-intent-only connections would be connected straight to the primary when using the Listener name. We need you to perform these further steps now:
Let’s go the READ_ONLY_ROUTING_URL. You can determine that URL by running the script which is documented in this blog by Matt Neerincx: http://blogs.msdn.com/b/mattn/archive/2012/04/25/calculating-read-only-routing-url-for-alwayson...
Then you take the URL which likely looks like: 'tcp://<Full Qualified Domain Name of the server>:<Port>'
And execute the T-SQL Statement like this on the primary server:
ALTER AVAILABILITY GROUP [AG name]
MODIFY REPLICA ON
N'<server name>' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'tcp:// ://<Full Qualified Domain Name of the server>:<Port>));
This needs to be repeated for every instance that should participate in the read-only routing.
If this is finished you need to setup the Routing table for every instance separately
In our configuration we run the three nodes sapdenali5, sapdenali6, sapdenali7. The SQL Command to define the routing table would look like:
ALTER AVAILABILITY GROUP [AG name]
MODIFY REPLICA ON
N'<server name>' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('<first server>','<second server>','<third server>',’…’)));
The number of instances which can specified is aligned with the limits of AlwaysOn in general. In our case the statement could look like:
ALTER AVAILABILITY GROUP [ERPPRODE64]
MODIFY REPLICA ON
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('sapdenali5', 'sapdenali6','sapdenali7')));
The routing table can be checked with this SQL query on the primary instance:
select ar.replica_server_name, rl.routing_priority, (select ar2.replica_server_name from sys.availability_read_only_routing_lists rl2 join sys.availability_replicas AS ar2 ON rl2.read_only_replica_id = ar2.replica_id where rl.replica_id=rl2.replica_id and rl.routing_priority =rl2.routing_priority and rl.read_only_replica_id=rl2.read_only_replica_id) as 'read_only_replica_server_name' from sys.availability_read_only_routing_lists rl join sys.availability_replicas AS ar ON rl.replica_id = ar.replica_id
In our case this query should come back with this result:
With this routing table, a read-intent connection should be routed to sapdenali6 if the primary is currently sapdenali5.
Only one thing to note: In order to make read-only-routing work, the connection string also needs to have a database defined which is part of the AlwaysOn Availability Group.
This effect can be demonstrated best with using sqlcmd.exe of SQL Server 2012. As described in this BOL article: http://msdn.microsoft.com/en-us/library/ms162773(v=SQL.110).aspx we added the –K option to define the ReadOnly Intent.
Let’s assume our account which we want to use to connect has the master database defined as default database and we try to connect with sqlcmd.exe like this:
C:\Program Files\Microsoft SQL Server\110\Tools\Binn>sqlcmd -E -SERPPRODE64vn
Where ERPPRODE64vn is the Listener name, we are ending up on the primary since we didn’t specify a read-only intent. However even if we specify the read-only intent like this:
C:\Program Files\Microsoft SQL Server\110\Tools\Binn>sqlcmd -E -SERPPRODE64vn -KReadOnly
We are ending on the primary server. Reason is that the account we are trying to connect with has the master database defined as default database to connect to. Hence if we add a database out of the AG related to the Listener name, we finally will end up on the secondary we defined first in the routing list:
C:\Program Files\Microsoft SQL Server\110\Tools\Binn>sqlcmd -E -SERPPRODE64vn -KReadOnly -dE64
As failovers are taking place and the instance which was the read-only target becomes primary, the read-only intent connections get terminated. As with normal application trying to leverage AlwaysOn, the read-only connections should have a connection retry. If the read-only application now reconnects, it will connect to the instance defined in the routing list of the instance which just became primary.
More detailed documentation about the read-only routing can be found in these articles:
What about the scenario of getting a secondary database fully accessible? A scenario where the database should be used for read and write. Exactly the case which we identified as a possible problem scenario with DBM.
As in DBM, we can’t move a secondary replica database to read/write as long as the database is part of an Availability Group. Hence as in DBM, we need to get the database out of the AG. We usually do this with dropping the replica out of the AG. This is done in SSMS Object Explorer.
In opposite to DBM are we supporting the connection of SAP applications to AlwaysOn configurations through the AlwaysOn Listener name only and not through the DBM connection string. Removing the replica from the AG, will also move the former replica out of the umbrella of the listener name and with that will make it unavailable for the SAP Netweaver application which uses the Listener name to connect. Hence a manmade mishap like the potential scenario described with DBM can’t happen anymore. Nevertheless we still would recommend an eventual rename of the database in order to avoid confusion.
Happy Holidays to all of you
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.