I pull data down from an Azure based SQL Server every now and then using the SQL Server Import and Export Wizard (which I already know isn't the best way to go about that...in the queue to change but not for now). I was wondering if there is a way to add user ApplicationIntent=ReadOnly somehow like you would in a standard SQL Server Connection string so I hit one of the read only secondary databases instead of the high trafficked main one. I'm going to go ahead with no there is not you are doing it wrong but I thought I'd ask first.

Thanks.

CodePudding user response：

Don't beat yourself up for using the Import/Export Wizard - for quick and dirty /one-off ETL work, it's a great tool. Under the hood, it uses SSIS. But by design it is a wizard and so is not going to give you all of the knobs to turn.

But all is not lost! To answer your question about using read-only application intent, at the point right before you'd actually run the ETL through the wizard, it gives you the option to save the SSIS package rather than run it. Save it off and then you have the option of changing the connection however you'd like. This Q/A over on the DBA sibling site shows you how to get a read-only connection in SSIS.

If that all seems roundabout, the suggestion from @Stu in the comment above of just determining the name of the secondary replica and using that is a great one given the one-off nature of Import/Export Wizard work.