Transcript Title goes here

Windows Azure SQL Database
- Migration and Optimization
Windows Azure Conference 2014
Agenda
•
•
•
•
•
•
•
Windows Azure SQL Database
Migration Considerations
Migration Tools and Options
Performance Considerations
Monitoring and Troubleshooting
WA SQL DB Premium Database
CSF Telemetry
Windows Azure Conference 2014
Windows Azure SQL Database
• Extremely fast & easy to provision
– Seconds / Minutes.
• Flexibility & Built-in Manageability:
• Tools to manage SQL Azure:
– SQL Server Management Studio, Azure Management Portal
– PowerShell, Rest API
• Removes needs for patching.
• Built-in High Availability:
• Database replicated across 2 Instances across 2 data centres by default
• 99.9% Availability SLA
• Predictable Performance:
• Reserved resources through “Premium Offering”
• Elasticity:
• Quick Scale-out to thousands of distributed databases.
Windows Azure Conference 2014
Windows Azure Conference 2014
Common Compatibility Considerations
• Features
– Clustered Index is required
– Non-default Collation should be at database level
•
•
•
•
•
Some TSQL features not supported: Eg: XML Schema Collections
Maximum 149 databases + master DB
No cross-database joins
Authentication using a user name and password
Some Services don’t exist
–
–
–
–
–
SQL Agent
Distributed Transactions
Replication
Linked Server
Log Shipping
• Throttling
– Resource consumptions, concurrent connections, idle connections etc
Windows Azure Conference 2014
Migration
• Migration involves transfer
– Schema
– Data
• Multiple tools to enable migration. Choosing tool for
migration depends on
– Type (SQL / Non SQL / Third Party)
– Size
– Complexity
Windows Azure Conference 2014
Data Transfer Tools
Tools
Schema
Compat.
Check?
Data
Efficiency Notes
BCP
No
No
Yes
Good
Efficient transfer of data to existing table
SSMS Generate Scripts
Yes
Some
Yes
Poor
Support for WASQLDB scripts
SSMS Import & Export Data
No
No
Yes
Good
Simple UI on top of SSIS; also available in SSMS
SSIS
No
No
Yes
Good
Most flexible
Migration Wizard
Yes
Yes
Yes
Good
Great capabilities; e.g. evaluate trace file
DACPAC (SSMS, .NET)
Yes
Yes
No
N/A
Entity containing all database objects, but no data
BACPAC (SSMS, .NET)
Yes
Yes
Yes
Good
Export/import of DAC plus data with DAC framework
Copy Database
Yes
N/A
Yes
Good
To move from existing WASQLDB
Windows Azure Conference 2014
SQL Server Migration Assistant
• Automate and simplify database migration
• Supports migration to:
– SQL Server and SQL Azure
• Supports migration from:
– Oracle, Sybase, MySQL, Access
• Migrates:
– Schema
– Data
• Using SSMA with WA SQL DB
Windows Azure Conference 2014
Simplifying the Migration Process
Migration
Analyzer
Assess the migration project
SQL Server Migration Assistant
Available for MySQL, Oracle, Sybase,
and Microsoft Access migrations
Schema Converter
Data Migrator
Migration Tester
Migrate the schema and
business logic
Migrate the data
Test the converted database
Convert the application, test, integrate, and deploy
Windows Azure Conference 2014
WA SQL Database Copy
CREATE DATABASE db1copy AS COPY OF db1
SELECT * FROM sys.dm_database_copies
Master
Database
db1
db1
copy
Copies in Progress
db1copy
SELECT * FROM sys.databases
db1 - ONLINE
db1copy - ONLINE
Windows Azure Conference 2014
DEMO
• Using Migration Tools to migrate WA SQL DB
Windows Azure Conference 2014
Windows Azure Conference 2014
Effect of Connectivity
• Performance affected by
connectivity
– Multiple hops
– Concurrent Connection Limit
– DOS attacks
– DB Failover
Windows Azure Conference 2014
Connectivity – Countermeasures
•
•
•
•
•
•
•
•
Minimize distance from DB: To customer and application
Open connections late, close early,
Connection pooling
Less Chatty communication: SPs, TVPs, batching
Caching: Local, Azure Cache, CDN
Prefer SQL Server Managed Transactions
Consider ADO.NET async methods
Use Transient Fault Handling Application Block
Windows Azure Conference 2014
Other DAL best practices
• ODBC / JDBC
– Reduce cursor-based operations
– Leverage read only, forward only and direct database interactions
• ADO.NET
– Chose appropriate execution mode with SqlCommand
– Batch multiple commands with DataAdapter or implement direct database interactions through SPs
– Use binary remoting format for Dataset serialization
• Entity Framework
–
–
–
–
–
–
Avoid state tracking in object generation if not needed, avoid lazy loading
Use Stored Procedures for data store interactions
If not possible, at least use compiled queries and pre-generated views
Map multiple results set to object sets
Batch multiple insert/update/delete operations
Integrate (not specific support) with TVP and SqlBulkCopy
Windows Azure Conference 2014
Effect of Shared Resources and Throttling
• Resource shared with neighbors
– CPU, memory, IO, Network
– Temp DB, worker threads, SQL Engine
• Engine Throttling
– Occurs when there is excessive resource usage
– Blocks connectivity for read and/or write
• Soft : Affects subset of DBs
• Hard : Affects all DBs
Throttling
Engine
Resource
Governance
Soft
• Resource Governance
– Sets and monitors limit on worker threads (180)
– Sets and monitors limit on sessions (internal)
• Resource Limits
–
–
–
–
DB Size (150 GB):
Txn. Duration (24 hrs), Total no of locks/txn (1M):
Temp DB (5 GB of Temp DB space, 2GB/txn):
Log space (2GB/txn), Memory Usage (16MB for > 20 sec):
Windows Azure Conference 2014
Hard
Resource
Limit
Resource Bottlenecks - Countermeasures
• Transactions and Lock
– Short Lived Transactions
– Coarser grain lock hints
– Monitor with following DMVs
• sys.dm_tran_active_transactions
• sys.dm_tran_database_transactions
• sys.dm_tran_locks
• sys.dm_tran_session_transactions
• DB Size Limits
– Archival, Drop Unused Objects
Windows Azure Conference 2014
• Temp DB, Txn Log length
• Drop temporary objects soon after usage
• Reduce number of rows in Txn scope
• Multiple short transactions
• Sharding
• Formula
•
number of rows affected in table * (avg size of field being
updated in bytes + 80) < 2 GB
• Memory Usage
•
Query Tuning on queries with group by, order by etc
Premium Database
• Dedicates a fixed amount of reserved capacity
– For secondary replicas too
• Scenario Fitment
– High resource consumption (CPU, Memory, IO)
– Exceeds WA SQL DB concurrency limit (180)
– Higher response time consistency (low latency)
• Current Sizes Available
Reservation Size
CPU cores
Concurrent
active requests
(workers)
P1
1
200
2000
150
8
P2
2
400
4000
300
16
Windows Azure Conference 2014
Sessions
Data IO (IOPS)
Memory (GB)
Windows Azure Conference 2014
Monitoring
• Use SQL Server System Views for Monitoring
– Monitor top queries, deadlocks, resource consumption, sessions
– Many sample queries are available
•
•
•
•
•
•
Implement periodic monitoring policy
Move monitored data to dedicated DB
Ensure Monitoring itself is non-intrusive
Implement analysis/reporting on data gathered
Consider CSF Telemetry approach
Tools
– Cerebrata, NewRelic and others
Windows Azure Conference 2014
Useful System Views
DMV
Scenario
sys.dm_exec_query_memory_grants
Queries waiting for memory before they can be executed.
sys.dm_exec_cached_plans
Execution plans that are currently in the case.
sys.dm_db_missing_index_details
Missing indexes that would increase the query performance.
sys.dm_db_missing_index_columns
Missing table columns for a given index.
sys.dm_db_missing_index_groups
Missing indexes are contained in a specific missing index group, excluding spatial indexes.
sys.dm_db_missing_index_group_stats
Groups of missing indexes, excluding spatial indexes.
sys.dm_db_index_usage_stats
Information about the usage of an index.
sys.dm_db_index_physical_stats
Information about the physical layout for a given index (space consumption etc).
sys.dm_db_index_operational_stats
Information about the performance for a given index.
sys.dm_exec_procedure_stats
Usage of stored procedures in the database.
sys.dm_exec_trigger_stats
Usage of triggers in the database.
Windows Azure Conference 2014
Premium Database and sys.resource_stats
• sys.resource_stats
– Provides data about CPU, Memory, IOPS, Sessions for the database
– One row for every five-minute reporting window
• There must have been a change in resource consumption
• (Idle databases might return no rows in the interval)
– Historical data is retained for 14 days
Windows Azure Conference 2014
Demo
• Monitoring WA SQL DB using System Views
Windows Azure Conference 2014
CSF Telemetry
• Cloud Service Fundamentals Project
–
–
–
–
–
–
DAL with retry logic
Logging over NLOG
Integration with WAD
Data collection pipeline
Query-able Telemetry System
Sample Reports
• Works at high scale, with sharding
• Incorporates best practices for
telemetry
– Dedicated diagnostics account
– Diag. data moves to table then to RDMBS
– Std. log libraries, runtime config. Changes
• Free downloadable code project
Windows Azure Conference 2014
Windows Azure Conference 2014