Design a hybrid SQL Server solution

Design a hybrid SQL Server solution

1. SQL Server Option

   • Azure SQL (PaaS)
     • Migrate data from on-premises SQL Server to Azure
       • Downtime use bacpac
       • Without downtime use transaction replication
     • Caracteristicas
       • Pago por uso y se puede escalar sin interruciones
       • Reduce el costo de administracion
       • Sin administrar VM
       • Sin administrar SO
       • sin administrar SW de Base de datos
       • sin administrar actualizaciones, HA, backups
       • Las BD's pueden crecer hasta 4TB o ser particionadas horizontal o verticalmente
       • SLA 99.99%
     • Replicate data for business continuity
       • Mas de una copia creada de las bases de datos
       • Active Geo-Replication
       • Geo-Redundant Backup
     • Replicate data to scale out for read-only workload
       • Active Geo-Replication
     • Replicate data to refresh dev-test environment
       • Database copy
       • For snapshot with old data restore tha database to the point in time
     • Backup and restore data
       • Backups automaticos. 7 días Basic y 35 Standar y Premium
   • SQL Server on VM (IaaS)
     • Migrate data from on-premises SQL Server to Azure
       • Backup and Restore
       • Backup to URL and Restore
       • Detach and attach from URL
       • Convert VM and Upload to URL and deploy as new VM
       • Ship hard drive (Windows service to Export and Import)
     • Caracteristicas
       • Pago por uso de la VM y/o licencia de SQL Server. Puedes utilizar una licencia propia
       • Optimo para migrar App o extender on-primes App en la nube en implementaciones híbridas
       • Administración de la instancia SQL
       • Cuando se tiene recursos IT
       • Instancias SQL pueden crecer hasta 64TB
     • Replicate data for business continuity
       • Always on Availability group
       • Failover cluster instance
       • SQL VM as DR Solution
     • Replicate data to scale out for read-only workload
       • Build readable Always On replicas
     • Replicate data to refresh dev-test environment
       • To replicate data from Azure Databases to on-premises or Azure SQL VM, you can export the database into bacpac and import into SQL Server running in Azure VM or on-premises
       • If you only want to replicate specific tables instead of the whole database, you can run SqlPackage in Azure VM
     • Distributing referencing data/Multi-master
       • Azure SQL Data Sync can be used to implement the data distribution between on-premises SQL Server, Azure SQL VM and Azure SQL databases, in uni-direction or bi-direction
     • Backup and restore data
       • Recommend backup to Azure storage
       • Snapshot backup
   • Migrate cold data from on-premises SQL Server to Azure
     • Move data into data warehouse
       • SQL VM or SQL Database USE SISS (SQL Server Integration Service)
       • Moving data into Azure Data Warehouse USE ADF (Azure Data Factory) or bcp
     • Move data into Azure Big Data Stores for Advanced Analytics
       • Use ADF
       • Load data into big store (Storage Blob and Azure Data Lake Store)
     • Move data from other data platforms
       • From other DBMSs to Azure you can USE SSMA (SQL Server Migration Assistant)

2. Failover groups and active geo-replication

   • active geo-replication
     • Automatic Asynchronous Replication
     • Readable secondary databases
     • Multiple readable secondaries
     • Support of elastic pool databases
     • Configurable performance level of the secondary database
     • User-controlled failover and failback
     • Keeping credentials and firewall rules in sync
   • Auto-failover group capabilities
     • Failover group
     • Primary server
     • Secondary server:
     • Adding databases to failover group
     • Failover group read-write listener
     • Failover group read-only listener
     • Automatic failover policy
     • Manual failover
     • Grace period with data loss
     • Multiple failover groups