Showing posts with label PostgreSQL. Show all posts
Showing posts with label PostgreSQL. Show all posts

Sunday 29 November 2020

Azure SQL Basic Options Summary

OverviewAzure SQL is incredible.  There are a lot of options when choosing how to host database and performance good.  "handles patching, backups, replication, failure detection, underlying potential hardware, software or network failures, deploying bug fixes, failovers, database upgrades, and other maintenance tasks", from Microsoft Docs and Azure SQL.

Azure SQL is the PaaS database service that does the same functions as SQL Server did for us for many years as the workhorse for many organisations.  Microsoft initially only offered creating VM's and then installing SQL Server on-prem.   Azure SQL is Microsoft's PaaS SQL database as a Service offering on the cloud.  Azure SQL is a fully managed platform for SQL databases that Microsoft patches managed backups and high availability.  All the features that are available in the on-prem. Edition are also built into Azure SQL with minor exceptions.  I also like that the minimum SLA provide by Azure SQL is 99.99%.

Three SQL Azure PaaS Basic Options:

  1. Single Database - This is a single isolate database with it's own guaranteed CPU, memory and storage.
  2. Elastic Pool - Collection of single isolate databases that share DTUs (CPU, Memory & I/O) or Virtual Cores.
  3. Manage Instance - You mange a set of databases, with guaranteed resources.  Similar to IaaS with SQL installed but Microsoft manage more parts for me.  Can only purchase using Virtual Core model (No DTU option).
Thoughts: Managed Instances recommend up to 100TB but can go higher.  Individual databases under elastic pools or single databases are limited to a respectable 4 TB.

Two Purchasing Options:

  1. DTU - A single metric that Microsoft use to calculate CPU, memory and I/O.  
  2. Virtual Cores - Allows you to choose you hardware/infrastructure.  One can optimise more memory than CPU ratio over the generalist DTU option.
Thoughts:  I prefer the DTU approach for SaaS and greenfield projects.  I generally only consider Virtual Cores, if I a have migrated on-prem. SQL onto a Managed Instance or for big workloads virtual cores can work out cheaper if the load is consistent.  There are exceptions but that is my general rule for choosing the best purchasing option.

Three Tiers:

  1. General Business/Standard (There is also a lower Basic Level)
  2. Business Critical/Premium
  3. Hyperscale

Backups

Point in time backups are automatically stored for 7 to 35 days (default is 7 days), protected using TDE, full, differential and transaction log backups are used to point in time recovery.  The backups are stored in blob storage RA-GRS (meaning in the primary region and all the read-only backups are stored in a secondary Azure region).  £ copies of the data in the active Azure Zone and 3 read only copies of the data.

Long Term Retention backups can be kept for 10 years, these are only full backups.  The smallest retention is full backups retained for each weeks full backup.  LTR is in preview available for Managed Instances.

Azure Defender for SQL 

Monitors SQL database servers checking vulnerability assessments (best practice recommendations) and Advance Threat Protection which monitors traffic for abnormal behavior.

Checklist:

  1. Only valid IP's can directly access the database, Deny public Access,
  2. AAD security credentials, use service principals
  3. Advanced Threat Protection has real time monitoring of logs and configuration (it also scans for vulnerabilities), 
  4. Default is to have encryption in transit (TLS 1.2) and encryption at rest (TDE) - don't change,
  5. Use Dynamic data masking inside the db instance for sensitive data e.g. credit cards
  6. Turn on SQL auditing,

Note: Elastic Database Jobs (same as SQL Agent Jobs).

Azure offers MySQL, Postgre and MariaDB as hosted PaaS offerings. 

Note: The Azure SQL PaaS Service does not support the filestream datatype : use varbinary or references to blobs. 

Sunday 13 September 2020

Options Layering API's on Data Sources - Micrososervices kind of

Hasura takes data sources such as SQL, Postgress & MySQL and converts it into GraphQL API's.  SQL Server is in preview.  Service is available on Azure and hooks into AAD and AAD B2C.  Hasuru looks extremely interesting and useful.  Potentially a great time saver.

CDS/DataFelx/Oakdale - Allows for Entity creation and provides REST API's.

SharePoint lists provide HTTP API's for CRUD operations.

REST API's vs GraphQL

OpenAPI specification (previously known as the Swagger specification) is my default for an API, this allows for a known RESTful API that anyone with access can use.   Open API has set contracts that returned defined objects which is great, you can work with the API like a database with simple CRUD operations as defined by the specification.  The issue is that the returned objects are fixed in structure so you may need 2 or more queries to get the data you are looking for.  Alternatively, GrapghQL allows the developer to ask for the data exactly as the want it.
Open API example:
/api/user/{2} returns the user object  // Get the user object for user 2
/api/users/{2}/orders/10  // Returns the last 10 orders for the user
GraphQL example:
Post a single HTTP request.
query {
 User(id: "") {
    name
    email
    orders(last: 10 {
      orderid
      totalamount
      datemodified
    }
 }

Database Option Notes:
PostgreSQL - Hybrid relation database (Communitity edition free, standard and enterprise higher loads) sits on Linex but can also use Windows OS.  Replicas are used for additional read-only nodes (I think up to 5 geo relicas allowed) 
Can be hosted on Azure or AWS IaaS.  PostgreSQL is also offered as a fully managed service (PaaS) on Azure (either single server or hyperscale).
MariaDB community fork off MySQL - Open source relation database used in the LAMP app stack.  Azure MySQL manage service can have up to 5 replicas for read heavy workloads.

Saturday 1 August 2020

Possible Technical Roadmap and thoughts on a startup

Overview:  A friend of mine's son has recently built a web site, and it looks impressive, and we started discussing his project, this turned into a detailed technical conversation to everyone's horror at a BBQ.  This chap is 14, and all I can say is wow.  All hosted and built without spending any money.  I interview many developers, and his knowledge is outstanding.  

I've drawn his architectural explanation below, added a few items to check and what my next piece would be.

Clarification of High Level Design
Thoughts:
  1. Build the Native mobile apps, getting users buy-in with a Native App will be considerably higher.  As you have used React, use React Native (React is separate to React Native, a completely new codebase).
  2. Cordova/PhoneGap is a wrapper that would allow you to keep a single code base and merely inject the existing code into Native wrappers for iPhone and Android.  As your app is HTML 5, and already looks like a modern mobile app, I'd use PhoneGap.  At least try it out and see if it fits.  You'd then only have to deal with a single code base to update all the platforms.  You could always use Google's Flutter if you wanted a single code base for the web site and Mobile native apps. 
  3. Ensure your API's are OpenAPI/Swagger
  4. Security - the API already has some protection.  Ensure the database is protected/secured.
Revenue model:  Ads (Gambling Ads pay well). Make a premium-paid for service (keep end-user usage/sign-up free).  Sign-up and collection of money while not directly applicable to this venture, can be done and kept dead simple using Shopify.