4

To avoid excess load on the primary SQL Server, I'd like to install and enable R Services on a secondary replica of the primary server. Is this possible?

Also, can I push computations from a remote R client onto the secondary server with R Services enabled?

Does this have any impact on the primary at all?

2

Readable Secondary is a feature of Always On Availability Groups and you can easily create one of these in Azure using the template from the gallery.

You have to do a little bit of extra config, to configure the read-only routing, something like this:

-- Configure the READ_ONLY_ROUTING_URL
ALTER AVAILABILITY GROUP [Contoso-ag] MODIFY REPLICA ON N'sqlserver-1'
WITH ( SECONDARY_ROLE( READ_ONLY_ROUTING_URL = N'TCP://sqlserver-1.contoso.com:1433') )

ALTER AVAILABILITY GROUP [Contoso-ag] MODIFY REPLICA ON N'sqlserver-0'
WITH ( SECONDARY_ROLE( READ_ONLY_ROUTING_URL = N'TCP://sqlserver-0.contoso.com:1433') )

-- Configure the READ_ONLY_ROUTING_LIST
ALTER AVAILABILITY GROUP [Contoso-ag] MODIFY REPLICA ON N'sqlserver-1' 
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'sqlserver-0', N'sqlserver-1')))

ALTER AVAILABILITY GROUP [Contoso-ag] MODIFY REPLICA ON N'sqlserver-0' 
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'sqlserver-1', N'sqlserver-0')))

Connecting to the readable secondary is then a matter of using the ApplicationIntent=ReadOnly facet in your connection string. I have not tried doing this from R but imagine you could. There's an easy way to verify whether or not you are connected to the secondary using sqlcmd which I used recently here.

As far as the push compute goes, this is not yet known. Looking on the Always On Availability Groups: Interoperability R Services is not listed.

I would personally be surprised if these features worked together in that way as R is so new, but it's a good thought. If it turns out it doesn't work, you could make it a feature request. I would also say though, when you connect to an AG listener and request read-only routing, it is only that, a request. As AOAG is a high-availability feature, if the secondary is down, you would connect to the primary. So let's say you only enabled R Services on the secondary, you create a situation where these scripts failed when the secondary was not available.

Unfortunately I can't test this out myself as I've burned all my Azure credits this month answering some question for @BrentO : ) Maybe later in the month.

  • Thanks for the information. I'm very new to the concept of AlwaysOn Cluster. Can you help me understand this statement? You cannot install SQL Server R Services on a failover cluster. However, you can install SQL Server R Services on a standalone computer that uses Always On and is part of an availability group. msdn.microsoft.com/en-us/library/mt696069.aspx – Minu Jul 4 '16 at 22:28
  • This refers to Windows Server Failover Clustering (WSFC) which you need not worry about if you are using Always On Availability Groups. – wBob Jul 5 '16 at 9:10
  • Correct me if I'm wrong, but this seems to be like the conclusion - "A secondary/replica is a feature of the AlwaysOn availability groups of SQL Server 2016. And according to MSDN, R Services can be enabled on a computer that uses AlwaysOn availability. This means that R Services can be enabled on a secondary. Connecting to a secondary (or a computer that uses AlwaysOn) from a remote client is a matter of inserting new variable into the connection string. But we still don't know if computations can be pushed." – Minu Jul 5 '16 at 11:42
1

Nick Burns wrote this Configuring R Services in SQL Server 2016 for SQLServerSentry.com.

Note his comments in the FINAL THOUGHTS:

Personally, I think that the integration of R with SQL Server 2016 is an incredibly exciting move from Microsoft. R provides enormous potential to analyze, explore and visualize the data in SQL Server. . . . But on a data warehouse, or a readable secondary perhaps the performance drain won't be too critical.

The work being done on the readable secondary should NOT affect the primary server. It is a readable but not writeable secondary, so the query ('R', 'TSQL', etc.) cannot leak back up to the primary server.

Do you have any reason to expect otherwise?

  • I've actually read that article. But that sentence doesn't look like it is confirming my question. So, I had to ask it here - to get a confirmation. But thanks for sharing the article. – Minu Jul 1 '16 at 15:49
  • @Minu - Comment on R code running on a readable secondary. – RLF Jul 1 '16 at 17:52
  • Thanks for that comment. That makes sense. But even if the secondary is readable or writeable, is my assumption that there will be no load on the primary server correct? – Minu Jul 1 '16 at 19:54
  • Also, is there any confirmation at all that enabling R services on the secondary is even possible? – Minu Jul 1 '16 at 19:55
  • There should be no impact on the primary server unless you take specific action. Nick Burns apparently believes it can be done. If you turn out to need a work database on the secondary with read and write just create a local database (workdb perhaps) where those steps can be run. – RLF Jul 2 '16 at 2:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.