instant Business Insight with Azure SQL and Power

instant Business Insight with Azure SQL and Power BI Ian Choy Microsoft Technology Solutions Professional

Key Objective What this is • • • Quick intro on Azure SQL Database & Power BI Step-by-step process I used in a POC for On. Prem SQL to Azure SQL with Power BI General scripts & considerations during my POC What this is NOT • Drilling into DB migration • SQL features deep dive • Power BI deep dive

SQL Server, Azure VMs, Azure SQL DB Hybrid Cloud On Premises Shared Lower Cost Platform as a service Infrastructure as a service Virtual SQL SQL Server on physical machines Software as a services Azure SQL Database Virtualized Databases SQL Server in Azure VM Virtualized machines Azure SQL Database (public cloud) SQL Server Physical Machines(raw Iron) Off Premises Higher Administration SQL Server in on-premises VM (private cloud) SQL Server in Azure VM (public cloud) SQL Server Private Cloud Virtualized Machines + Appliances Physical Dedicated Higher Cost Options: Lower Administration

Designed for predictable performance Redefined Across Basic, Standard, and Premium, each performance level is assigned a defined level of throughput Measure of power Introducing the Database Throughput Unit (DTU) which represents database power and replaces hardware specs DTU is defined by the bounding box for the resources required by a database workload and measures power across the six performance levels. % CPU % read % write % memory Basic — 5 DTU S 0 — 10 DTU S 1 — 20 DTU S 2 — 50 DTU S 3 — 100 DTU P 1 — 125 DTU P 2 — 250 DTU P 4 — 500 DTU P 6 — 1, 000 DTU P 11 — 1, 750 DTU

Scalability options in Azure SQL Database Vertical: Scale up or scale down Change service tiers for a given database as capacity needs fluctuate Horizontal: Scale out or scale in Scale up/down Add or remove databases (sharded and/or in a pool) as more or less capacity is needed Premium Standard Basic Scale out/in Basic

Elastic Pool • Pool Databases with similar Utilization Patterns • Achieve Reduction in DTU’s and Price. • 20 DB sampled never spike more then 100 DTU • All 20 DB can share 100 e. DTU pool.

Point-in-time restore Automatic backup Full backups weekly, different backup daily, log backups every 5 minutes Daily and weekly backups automatically uploaded to geo-redundant Azure Storage Self-service restore SQL Database backups sabcp 01 bl 21 Azure Storage Geo- replicated Point-in-time up to a second granularity REST API, Power. Shell, or Portal Creates a new database in the same logical server Tiered retention policy Basic - 7 days Standard - 14 days Premium - 35 days No additional cost to retain backups Restore from backup sabcp 01 bl 21

Active geo-replication Mission-critical business continuity on your terms, via programmatic APIs Self-service activation in Premium Create up to 4 readable secondaries Replicate to any Azure region Automatic data replication, asynchronous REST API, Power. Shell or Azure Portal RTO<1 h, RPO<5 m, you choose when to failover Up to 4 secondaries

Power BI Overview Data sources Power BI service Saa. S solutions e. g. Marketo, Salesforce, Git. Hub, Google analytics Content packs On-premises data e. g. Analysis Services Live dashboards Organizational content packs Corporate data sources or external data services Visualizations Azure services Azure SQL, Stream Analytics… Reports Excel files Workbook data / data models Power BI Desktop files Data from files, databases, Azure, and other sources 01001 10101 Datasets Data refresh Natural language query Sharing & collaboration

One gateway for multiple cloud services and experiences Cloud services Preview Power BI Power. Apps Read access, scheduled refresh, live connection Preview Microsoft Flow Preview Azure Logic Apps Live connection, CRUD support (create, read, update and delete ) Gateway Cloud Service Data source connection credentials are encrypted Azure Service Bus Application Gateway Data source connection credentials can only be decrypted by the gateway On-premises data sources SQL Server Analysis Services Other data sources Files, Share. Point

Preparation Azure setup Azure SQL feature setup Export / Import Power BI design

Preparation Azure Credit Trial = https: //azure. microsoft. com/en-us/offers/ms-azr-0044 p/ Power BI = https: //powerbi. microsoft. com/en-us/get-started/ Azure Storage Tools = http: //storagetools. azurewebsites. net/ Azure Power. Shell = https: //github. com/Azure/azure-powershell/releases SSDT (Sql. Package) = https: //msdn. microsoft. com/en-us/library/mt 204009. aspx Az. Copy = https: //azure. microsoft. com/en-us/documentation/articles/storageuse-azcopy/

Azure setup Connect to your Azure Account Add-Azure. Account Connect to use Resource Manager cmdlet Add-Azure. Rm. Account Provision an Azure Storage for your DB export (LRS, GRS, etc. ) New-Azure. Storage. Account –Storage. Account. Name “xxx" -Location “southeast asia" -Type "Standard_LRS" Create a Resource Group where your Azure SQL will be part off New-Azure. Rm. Resource. Group -Name “xxx" -Location "southeast asia"

Azure setup – part 2 Azure setup Create a Azure SQL “Server”… it’s like a SQL instance. New-Azure. Rm. Sql. Server -Resource. Group. Name “xxx" -Server. Name “xxx" -Location “southeastasia" -Server. Version "12. 0" Configure your Firewall. . Don’t do 0 - 255. New-Azure. Rm. Sql. Server. Firewall. Rule -Resource. Group. Name “xxx" Server. Name “xxx" -Firewall. Rule. Name “xxx" -Start. Ip. Address "0. 0" End. Ip. Address "255. 255"

Export / import Export / Import Export your existing On. Prem Database to a local folder C: Program Files (x 86)Microsoft SQL Server130DACbin sqlpackage. exe /Action: Export /ssn: . /sdn: xxx /tf: C: Filesxxx. bacpac Copy your. BACPAC file to Azure Storage (x minutes per x db size) C: Program Files (x 86)Microsoft SDKsAzureAz. Copy /Source: C: File /Dest: https: //xxx. blob. core. windows. net/bacpac /Pattern: xxx. bacpac /NC: 2 /Dest. Key: xxx Obtain your Azure Storage key Get-Azure. Storage. Key -Storage. Account. Name “xxx“

Export / import – part 2 Export / Import and restore. BACPAC file to Azure SQL Server New-Azure. Rm. Sql. Database. Import –Resource. Group. Name $Resource. Group. Name –Server. Name $Server. Name –Database. Name $Database. Name –Storage. Keytype $Storage. Key. Type –Storage. Key $Storage. Key -Storage. Uri $Storage. Uri –Administrator. Login $credential. User. Name –Administrator. Login. Password $credential. Password –Edition Premium –Service. Objective. Name P 1 Database. Max. Size. Bytes 50000

Azure SQL feature setup Enable Azure SQL requested features 1 ) TDE = Set-Azure. RMSql. Database. Transparent. Data. Encryption Resource. Group. Name $Resource. Group. Name -Server. Name $Server. Name -Database. Name $Database. Name -State "Enabled“ 2) Columnstore Index = Create Clustered Columnstore Index [XXX_CI] on Fact_CCI with (DATA_COMPRESSION = COLUMNSTORE) 3, 4, etc. ) Active Geo-replication, Point-in-time restore, etc.

Power BI design Enable Azure SQL requested features 1 ) Connect Live or Extract Data 2) Determine key Dimensions & Measures / KPI 3) Design data model 4) Add Charts and Custom visuals https: //app. powerbi. com/visuals/ 5) Publish to Power BI. com

Things to consider Azure SQL Compatibility https: //azure. microsoft. com/en-us/documentation/articles/sqldatabase-general-limitations/ Upgrade Advisor https: //www. microsoft. com/en-us/download/details. aspx? id=48119 Bandwidth speed test http: //azurespeedtest. azurewebsites. net/