230 likes | 435 Views
XVII Encontro – 29/11/2011. SQL Server to SQL Azure: Migration Challenges Virgílio Esteves. Virgílio Esteves. ID&T – Research Leader @HIS & Founder of NetPonto Coimbra C# / WPF / Silverlight / XNA / Azure. Challenges. Data Storage Connectivity Authentication Schema
E N D
XVII Encontro – 29/11/2011 SQL Server to SQL Azure: Migration ChallengesVirgílio Esteves
Virgílio Esteves ID&T – Research Leader @HIS & Founder of NetPonto Coimbra C# / WPF / Silverlight / XNA / Azure
Challenges • Data Storage • Connectivity • Authentication • Schema • T-SQL Supportability • Replication & Log Shipping
Data Storage • SQL Server • No size limits • SQL Azure • Web Edition • 1GB or 5GB • Business Edition • 10GB » 50GB (10GB increments)
Connectivity • Tools • Connection management
Connectivity: Tools • SQL Server • Management Studio (SSMS) • SQLCMD • Profiler • SQL Azure • Management Studio (SSMS) [2008 R2+] • SQLCMD • Portal
SQL Azure Network Topology Application Internet Azure TDS (tcp) Load Balancer Forwards “sticky” sessions to TDS protocoltier TDS (tcp) Security Boundary Load Balancer Load Balancer Load Balancer Load Balancer Load Balancer TDS Protocol Gateway – Proxy to Backend SQL Server SQL Server SQL Server SQL Server SQL Server SQL Server SQL Server SQL Azure Fabric – Automatic Failover, Replication and Load Balancing
Connectivity: Connection Management • SQL Server • No “default” connection termination • SQL Azure • Connection termination at server discretion • “Coded” reason codes
Connection Termination: Causes • Lock consumption • Uncommitted transactions • Transactions blocking system calls • Log file size • TempDB usage and size • Excessive memory usage • Maximum database size reached
Connection Termination: Causes • Idle connections • Long running transactions • Denial of Service attacks • Network problems • Failover problems • Load balancer discretion
Authentication • SQL Server • SQL Authentication • Windows Authentication • SQL Azure • SQL Authentication
Schema • SQL Server • No limitations • SQL Azure • Doesn’t support heaps • All tables must have a clustered index
T-SQL Supportability • SQL Server • No limitations • SQL Azure • Some are fully supported • Some are partially supported • Some are fully unsupported
T-SQL: Fully or Partially Supported • Constants • Constraints • Cursors • Index management and rebuilding • Local temporary tables • Reserved keywords • Spatial data and indexes • Stored procedures • Triggers
T-SQL: Fully or Partially Supported • User-defined functions • Statistics management • Transactions • Tables, joins and table variables • Create/Drop databases • Create/Alter/Drop tables • Create/Alter/Drop users and logins • Views
T-SQL: Unsupported • Common Language Runtime (CLR) • Database file placement • Database mirroring • Distributed queries • Distributed transactions • Filegroup management
T-SQL: Unsupported • Global temporary tables • SQL Server configuration options • SQL Server Service Broker • System tables • Trace flags
Replication & Log Shipping • SQL Server • Fully supported • SQL Azure • Not supported
Is there more?!? But some things come to the rescue Yes…
Links worth looking… • SQL Azure Migration Wizard (http://sqlazuremw.codeplex.com/) • SQL Azure Backup (http://www.red-gate.com/products/dba/sql-azure-backup/) • Supported T-SQL Statements (http://msdn.microsoft.com/en-us/library/ee336270.aspx) • Partially Supported T-SQL Statements (http://msdn.microsoft.com/en-us/library/ee336267.aspx) • Unsupported T-SQL Statements (http://msdn.microsoft.com/en-us/library/ee336253.aspx) • SQL Azure Guidelines and Limitations (http://msdn.microsoft.com/en-us/library/ff394102.aspx)
Thanks! Virgílio Esteves raposo@gmail.com http://pontonetpt.com/blogs/raposo http://twitter.com/vraposo http://pt.linkedin.com/in/virgilioesteves