search

MicrosoftDocs / azure-docs

Open source documentation of Microsoft Azure
https://docs.microsoft.com/azure
Creative Commons Attribution 4.0 International
10.24k stars 21.41k forks source link

Create external tables for replica DB #39565

Closed anilvem1 closed 5 years ago

anilvem1 commented 5 years ago

Hi, Currently we have DB in P6 tier and it has a Geo replication DB setup. Business access reporting data via Geo replication DB tables via another SQL DB external tables. If we wanted to migrate to Hyperscale, we will not be having Geo replication DB, but we wanted to know how we can create external Data source/tables for Hyperscale replica (not primary) so that business always connects to replica DB only?

If I use below, I can create Data source to primary only. How to create data source for replica? https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-2017#examples

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

anilvem1 commented 5 years ago

I tried to use below query to create external data source CREATE EXTERNAL DATA SOURCE XXXXXXXXXXXXXX WITH ( TYPE=RDBMS, LOCATION='xxxxxxxxx.database.windows.net', CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly' DATABASE_NAME='xxxxx', CREDENTIAL= CRED ); GO;

It gave me the below error. Incorrect syntax near 'CONNECTION_OPTIONS'.

KalyanChanumolu-MSFT commented 5 years ago

Thank you for reaching out. We are actively investigating and will get back to you soon.

Mike-Ubezzi-MSFT commented 5 years ago

@anilvem1 This scenario is not supported. If you want to create external tables, these would need to be created in the primary instance and then replicated to the read replicas, where they are then accessible via the ApplicationIntent=ReadOnly connection string designation. All functionality that is supported by the Hyperscale service is done through the primary and available in the replicas as a ReadOnly operation.

Please see: Can I create a dedicated endpoint for the read-scale replica

No. You can only connect to read-scale replica by specifying ApplicationIntent=ReadOnly.

The best means for requesting functionality with the Hyperscale offering is to create an entry in the Uservoice forum: SQL Database Uservoice forum

Mike-Ubezzi-MSFT commented 5 years ago

@anilvem1 Please let us know if you have additional questions with here and we will be glad to continue the discussion. Thank you!