January 1, 0001 by rdagumampan

Azure SQL Database Migration

Azure SQL Database

Service tiers - standard - premium

always on availability group for handling fail-over during sizing changes bacpac when we get burned from cost of operations

Features for geo-redundancy is currently being develop

/why the need for firewall? azure sql database firewall

/do we have pay extra for storage? includes storage, cpu, support

/can i use azure sql database are part of on-prem always on ag? no, its not possible

/how do we roll back if we get burned? we can export as bapac and import into sql 2017

/tools ssms

//azure sql database each sever is individual virtual machine

/operational view

/can we connect to sql database from linked server yes, but with some compatibility issues

Managed Instance

/why azure sql database environment you dont have to worry about infra high availabilutu built-in

/which to choose new projects, go for azure sql database

/securing data leaks multiple subscription rbac in databases

Module 2

/azure sql database/caling Azure SQL Databases Up Down pricing tier scale up/down connections to the database maybe temporarily dropped duration depends on both size and service tier standard tier 250gb < 6 hrs changing within premium tier of 250gb < 3hours

/azure sql database/how the switchover workS azure sql database uses kubernetes to support sql availability groups to relocate the dns record

/azure sql database/knowledge check index maintenance are done automatically for most cases sql server agent is not present azure sql database

/azure sql database/business continuity automatic backups transaction log backups backups are included in pricing tier point in time restore remote redundant storage

/azure sql database/disaster recovery failover active geo-replication all geo replication is async you can have secondaries at different service tier

/azure sql database/where bacpac is saved storage account or local harddrive

/azure sql database/security all external access are disabled by default we can create vnet and connect this to IaaS setup use azure ad to revalidate users with access to database all vnet must be the same region as the client sql server resources ip blocked for 10mins after wrong password vnet is included in pricing tier

/azure sql database/transparent data encryption encrypted data at rest (hardware level) tde requires no application changes

/azure sql database/always encrypted encrypt data in motion

/azure sql database/common issues database connectivity high dtu usage query timeouts deadlocks database storage consumption slow queries

/azure sql database/tools query performance insights automatic tuning extended events dmvs intelligent insights azure database portal

/plans forward microsoft developer features with azure sql database first before they adapt to boxed products machine learning and ai in deriving tuning reccomendations


vnet, private ip clr, sql agents, cross db queries, service brokers, replication 35tb data native backups features of onprem and azure

/limitations /obsolete always on ag windows auth management dw

/retired features database mirroring extended stored proc

/features for post GA / oct12018 filestream and file tables cross instance distributed queries stretchdb polybase policy based management

/dacpac vs bacpac dacpac only have schema but bacpac has schema and data bcp maybe the best option for bigger databases 400gb, its taking 12hours counting, 80core setup 2gb we reccommend to delivery manually

/transactional replication minimal downtime configure your azure database as subscriber all changes to your data and schema showup in azure database sync is complete, change connection string to your application remove replication

/data migration assistant simple will not pickup delta data

/migration for shoter downtime of db dms is preferred way, live migration


Azure SQL Data Sync - metadata db is created to track changes from source db

azure sync group -hub -member(s)

References: https://www.mssqltips.com/sqlservertip/5571/whats-in-a-dtu-choosing-the-right-resource-model-and-service-tier-for-azure-db/ http://vunvulearadu.blogspot.com/2018/08/demystifying-azure-sql-dtus-and-vcore.html

© 2017 | About | Contact | Follow me on Twitter | Powerered by Hucore & Hugo