We use the UK South data centre for our Azure SQL Databases, we use S3 pricing tier at rest because we require Columnstore indexes and that’s the minimum tier that supports it, we up-scale over night when doing our main ETL run.
We’ve reported this time after time with Microsoft and even contacted the Product Team myself but the issues are still unresolved so here’s one to watch out for if you are going to be using Columnstore (possibly other features) in the UK datacentres.
Ordinarily you get the error message below when you try and select from a table that has a clustered columnstore index on it and you are using an instance of SQL Server that doesn’t support columnstore indexing, so less that S3 for instance.
Msg 35340, Level 16, State 9, Procedure vwPurchaseOrder, Line 7 [Batch Start Line 0]
LOB columns are disabled in columnstore.
Msg 4413, Level 16, State 1, Line 39
Could not use view or function ‘Tabular.vwPurchaseOrder’ because of binding errors.
When you change the pricing tier (up/down scale) you are essentially moving from one SQL instance to another, sadly you will find that on occasion you will reach an instance that hasn’t been deployed properly, the switches that enable columnstore aren’t set correctly.
The solution – down scale then scale back to the tier you actually want in the hope it works.
If you get this scenario yourself then make sure you report it, frankly I think it’s really poor that what should be a straightforward deployment process can’t be done correctly.