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?

Minu
  • 391
  • 1
  • 13

2 Answers2

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.

Community
  • 1
wBob
  • 10,380
  • 2
  • 24
  • 44
  • 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. https://msdn.microsoft.com/en-us/library/mt696069.aspx – Minu Jul 04 '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 05 '16 at 09: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 05 '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?

RLF
  • 14,015
  • 2
  • 33
  • 47
  • 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 01 '16 at 15:49
  • @Minu - Comment on R code running on a readable secondary. – RLF Jul 01 '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 01 '16 at 19:54
  • Also, is there any confirmation at all that enabling R services on the secondary is even possible? – Minu Jul 01 '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 02 '16 at 02:33
  • Thanks. I'll probably do that. Do you have any idea if I can replicate a secondary from a SQL Server VM on Azure? – Minu Jul 02 '16 at 02:41
  • Sorry, but I do not know. (Clarifying my comment.) – RLF Jul 04 '16 at 19:02