This is a great feature! However, it misses a class of scenarios that prevent my organization from using it.
We are a relatively new company, so our identity exists only in AAD -- we don't even have an on-prem AD. Since Windows 10 came out, everyone's PCs are joined to AAD, and everyone authenticates to their computers using their OrgIds within AAD. Unfortunately the new AAD integrated security doesn't include this.
On the password side, all developers/QA/support who have access to any Azure resources have mandatory 2-factor authentication. The nature of SSMS doesn't really support this either. I suppose people could generate app passwords, but they're pretty cumbersome to use; while they do offer central account administration, they are no better than SQL logins from a usability perspective.
I would love to see support added for one or both of the above; either would enable a lot of additional use cases! Regardless, I love the direction this is moving. Good stuff.
Good high-level overview. I do feel the Azure SQL Database service falls short for a couple of these threats.
- The SQL Server Data tools in Visual Studio and (I think) SQL Management Studio are NOT set up to encrypt connections by default. It's too easy for someone to connect directly to an Azure-hosted database (e.g. for dev, QA, or support) and forget to encrypt. Whenever we find out about that we immediately make them change their password, but it just shouldn't be so easy. Encryption should be turned on by default. In fact, I would also suggest the tools should throw up scary warnings if things aren't encrypted.
- Azure SQL databases should support Organizational (O365) accounts alongside local logins. It makes it tough to centralize security when you have to create separate logins with separate passwords on each logical server.
This looks promising -- moreso than Federations did. I do have a couple questions.
Looking through the documentation, I might easily be missing it but I don't see anything relating to schema changes. How does that work? E.g. we use SSDT to manage our schemas and generate update scripts. In non-production environments we even publish directly from SSDT. Would we have to manually get a script and execute it against each shardlet? Ideally the whole set could be managed as one entity, with schema changes applied across the board.
Similar concerns exist for reference data, or any shared tables. When that data changes, do we have to synchronize it across all shardlets? A far better solution would be to allow queries to span databases. This would both enable more complex fan-out scenarios (see Jeff's post above), but also would allow queries to join sharded tables to reference tables, with the reference tables being stored in a common, shared database.
I managed to get an ISO downloaded but I don't think it's the Pro version. It's not entirely clear. I'm thinking it's not, since for volume licencing they list it separately. In any case I can't get any product keys.
This stuff looks great. I am really enjoying the new capabilities, but wondering if there is a way to upgrade a data tier application (i.e. change an existing database's schema) without modifying any users or their permissions. Essentially I would like to be able to to use a .dacpac to encapsulate all aspects of a database's schema WITHOUT messing around with stuff like users. The scenario is developing a dacpac in a test environment, and then wanting to use it to upgrade say a production database, which obviously would have different security than in test.
I did find a bunch of advanced settings under the project settings' Debug tab, but those don't seem to affect the actual compare process when upgrading the data tier application from within SQL Management Studio -- it still insists on dropping existing users in that database, which is obviously not ok.