Hi @IreneLin88 ,
Based on your description, I looked up the operational documentation for the issue in question. If you need to
connect to alwayson secondary database via availability group in PowerBi Report, you need to complete the following steps:
1. First, before connecting Power BI to the secondary database, make sure that your AlwaysOn availability group is properly configured and that the secondary database is available for read-only operations. This setting is critical for Power BI to retrieve data without interfering with primary database operations.
2. Second, you'll need to configure a listener for an Always On availability group, Once you've configured your availability group listener, you'll need to update your Once you've configured your availability group listener, you'll need to update your connection string to connect to the Always On availability group listener. This will route traffic from your application automatically to the intended replica without having to manually update the connection string after every failover.
3. Next, configure read-only routing. read-only routing refers to automatically routing incoming listener connections to a readable secondary replica that is configured to allow read-only workloads.
4. and finally connect to sql server in power bi. open Power BI Desktop.->Select "Get Data" and then "SQL Server". When entering the server name make sure to append the ApplicationIntent=ReadOnly parameter to the server name to point the connection to the secondary copy. For example, if your server name is `ServerName`, you would enter `ServerName;ApplicationIntent=ReadOnly` in the server field in Power BI.->In the "Server" field, enter your server name as described above.
->In the "Server" field, enter your server name as described above. ->In the "Database" field, enter the name of the database you want to connect to.->Click "OK" and proceed with the authentication and importing of your data.
For more steps on this operation, you can refer to these official documents
What is an Always On availability group? - SQL Server Always On | Microsoft Learn
Configure backups on secondary replicas of an availability group - SQL Server Always On | Microsoft ...
Configure availability group listener - SQL Server Always On | Microsoft Learn
Connect to an availability group listener - SQL Server Always On | Microsoft Learn
Configure read-only access to secondary availability group replica - SQL Server Always On | Microsof...
Join a secondary database to an availability group - SQL Server Always On | Microsoft Learn
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
