Azure Database for MySQL and PostgreSQL
techcrunch.com
techcrunch.com
For me, it might remove some blockers to moving some clients to a cloud managed database solution that had always been there with AWS RDS. If that follows through and pays off for having a proper PostgreSQL super user in the database environment available for use (what I really want), that can make certain things much more do-able (brings new things you have to watch out for, too, but... pick your battles).
The admin user provisioned for the server is not a full superuser, but it is close to that. We are trying to find the right set of permissions to provide to our customers without jeopardizing our ability to manage this server for you. Please submit your feedback here: https://feedback.azure.com/forums/597976-azure-database-for-... if you see a problem with the permissions.
Thank you for your feedback. We indeed offer a whole server (database cluster) and you can create multiple databases in it and have full control of the resources allocated to that cluster.
The admin user provisioned for the server is not a full superuser, but it is close to that. We are trying to find the right set of permissions to provide to our customers without jeopardizing our ability to manage this server for you. Please submit your feedback here: https://feedback.azure.com/forums/597976-azure-database-for-... if you see a problem with the permissions.
I'll give it a try. It may well be permissive enough for our my clients' needs; though plv8 would still be a blocker (already voted on that one).
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Conce...
Shared or not is absolutely an assumption on my part. If they're spinning up an individual cluster for you, then some of the restrictions on RDS don't make much sense to me. They make more sense if it were shared.
Unfortunately, for most of the clients I work with, the limits (shared or not) are show-stoppers so I've only dealt with building a system backed by AWS RDS/PostgreSQL once and the need to dive into the underlying mechanics just wasn't there at the time.
The limitations are presumably so that they have control over settings for things like replication and their features that you can't mess with.
This is going to be huge in India. Microsoft has massive mindshare in India with the govt because of the regulatory landscape.
I have it on firsthand info that several banks in India are going to make their first ever transition to the cloud on Azure because of Microsoft's willingness to go the extra mile around whatever security dances that the banks want them to do.
Plus Azure has India datacenter which takes of the PII problem for healthcare and financial data.
It is truly unfortunate that the management console has the worst UX ever.
I think that title goes to AWS...
The information architecture is fairly broken. And the UI that slides is even worse - the only reason a UI like that exists because it's highly stateful: you need to remember what you did in the previous screen. And with the whole information architecture, it's crazy hard.
If Azure builds a Google cloud like UI and calls it azure-lite, I would throw all my money at them.
MySQL: https://azure.microsoft.com/en-us/services/mysql/
PostgreSQL: https://azure.microsoft.com/en-us/services/postgresql/
Azure SQL Server has been great at work; operationally, it couldn't be simpler, and the point-in-time restore feature saved our bacon at least once. The only drawback has been... SQL Server itself. It's a solid performer, but T-SQL is a bear to work with. If MSFT can apply the same operations magic to postgres, they'll have a real winner on their hands.
The major bummer during development was, for me, the lack of a command to drop a schema and all the things in it. I've found the ability to `DROP SCHEMA IF EXISTS foo` to be invaluable when iterating on DB designs. In T-SQL, the alternative is to maintain a "drop-all.sql" file, which is tedious and error-prone when making use of foreign-key relationships. (more generally, postgres' DDL seems more ergonomic)
I miss the simplicity of postgres' upsert capabilities. T-SQL has MERGE, which is vastly more complicated and verbose. I get that it is quite powerful and flexible, but I've never personally needed more than upsert semantics, and the design of MERGE means that I pay (in esoteric syntax) for features that I don't use.
Table-valued params are an adequate substitute in T-SQL for postgtresql array parameters, but driver support outside of .NET is middling at best, so our Java code gets a little dodgy when using them.
Despite the more recent introduction of JSON support in SQL Server, there is no true equal to `json` or `jsonb` types.
There's simply no substitute in Azure SQL for the variety of procedural languages available in postgresql; T-SQL, as a procedural language, is just not very easy to work with. I'd much prefer python, perl, or even plpgsql itself.
Finally, I miss the ability to add comments on tables, columns, procs, etc, directly in SQL.
Those are the things that immediately impact me; others may not be bothered at all.
I believe that SQL Server's MERGE does not actually support what you might call UPSERT semantics: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Ra...
We've never encountered this kind of MERGE-related race condition before, but I'm not sure that we'd recognize it if we did. Probably it would surface as a failed transaction, but our merges are pretty low-volume so it hasn't happened yet.
Thanks for the link!
I have no faith in merge anymore. :(
I actually prefer T-SQL's MERGE, partly because it's easier to get the ID out of the matched row than with Postgres, which ignores a RETURNING clause if nothing changed, and if you _force_ an UPDATE creates a new row version and increments any sequences involved. So I suppose I've been annoyed by both.
Mostly agree on the rest though. In addition to the language support, just being able to create an aggregate in SQL is a bonus for me inside Postgres, without having to do it on the CLR in SQL Server.
I think if MS are quick to support 10.0 when it's out, I agree this'll be a intriguing platform.
If you really want to drop a whole schema (i.e. all its constituant objects) and recreate, then you could write a procedure to do so by cursoring through sys.objects (joined to other system tables as needed, sys.schemas to filter by name rather than schema ID for a start) and issuing "DROP <OBJECT> <name>" for each via "EXEC (@VarContainingDropStatement)". Having a built-in might be nice, but writing your own reusable procedure (rather than a more hard-coded script) should not be difficult.
> tedious and error-prone when making use of foreign-key relationships
If following the "cursor through sys.objects" approach that can be managed by making sure FK constraints are dropped first, either by forcing the objects listed out of the cursor into a particular order or by a separate loop first.
> T-SQL has MERGE, which is vastly more complicated and verbose.
I'm with you there. It also has some odd locking issues that you need to be aware of before they bite your rear.
I'd agree generally that T-SQL has some room for improvement. Date and string manipulation functions are sometimes lacking I find, though as I use little other than SQL Server I can't comment on other environments being better/worse. Being able to easily define a new aggregate procedure as I believe you can in postgres is something I could have made good use of in the past.
Super easy to set up and get working with. I've never used EF6 , so I can't really comment there, but I've seen mention that entity framework core is missing features relative to ef6, so that's something to think about.
Dealing with not-builtin types like jsonp/hstore is a bit tricky in EF so far it feels like, but doable? Doesn't seem like you can get a real first class experience with that yet though.
and no support for spatial data types. it's why i'm still mostly using SQLAlchemy and Python - i'm a big fan of .NET Core, though.
SQLAlchemy is pretty much the only framework I've seen do this pretty well, though I have plenty of other qualms with it. Still probably my favorite ORM overall.
Just a word of warning, plv8 extension isn't available in the preview:
https://docs.microsoft.com/en-us/azure/postgresql/concepts-e...
Apart from the lack of that extension, ah so exciting. <3 PostgreSQL.
/me looks outside for flying pigs. Yep, there they are, whole groups of them going by in formation. ;)
We had to abandon a multi-month deployment to Azure because the latency was too high and it caused all sorts of issues with our app. To be honest, our DB access approach wasn't great (heaps of really small requests), but we couldn't re-architect it in the timeframe we had. We were also doing this in Sydney, so maybe it was just a localised issue (the Australian data centres are relatively new).
Anyway, hopefully they have that sorted - we need more competition!
If you're asking why I think ClearDB is hot garbage, it's because of pricing, in ability to scale down (you literally cannot scale down ClearDB in Azure), connection limits on the DB that make no sense with the pricing, etc.
Postgresql database for Azure?
Much like SQL Server DTUs and DWUs, the spacebucks measurements only seem to useful for hiding the actual price comparisons.
At least they stopped the DTU/DWU non-sense for these services.
Also, does it talk about what level of admin rights I can get to these machines?
Any advice on how to compare performance for a given price?
Is this feature not available yet?
I opened a psql console and ran "BEGIN; SELECT CURRENT_TIMESTAMP; SELECT pg_sleep(200); SELECT CURRENT_TIMESTAMP; COMMIT;"
I see the error "server closed the connection unexpectedly"
In background processes you might get away with retrying connections for a minute but most users will probably consider your site broken if page load time is 45 seconds.