1 / 23

SQL Server to SQL Azure: Migration Challenges Virgílio Esteves

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

sakina
Download Presentation

SQL Server to SQL Azure: Migration Challenges Virgílio Esteves

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. XVII Encontro – 29/11/2011 SQL Server to SQL Azure: Migration ChallengesVirgílio Esteves

  2. Virgílio Esteves ID&T – Research Leader @HIS & Founder of NetPonto Coimbra C# / WPF / Silverlight / XNA / Azure

  3. Challenges • Data Storage • Connectivity • Authentication • Schema • T-SQL Supportability • Replication & Log Shipping

  4. Data Storage • SQL Server • No size limits • SQL Azure • Web Edition • 1GB or 5GB • Business Edition • 10GB » 50GB (10GB increments)

  5. Connectivity • Tools • Connection management

  6. Connectivity: Tools • SQL Server • Management Studio (SSMS) • SQLCMD • Profiler • SQL Azure • Management Studio (SSMS) [2008 R2+] • SQLCMD • Portal

  7. 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

  8. Connectivity: Connection Management • SQL Server • No “default” connection termination • SQL Azure • Connection termination at server discretion • “Coded” reason codes

  9. 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

  10. Connection Termination: Causes • Idle connections • Long running transactions • Denial of Service attacks • Network problems • Failover problems • Load balancer discretion

  11. “Coded” Reason Codes

  12. Authentication • SQL Server • SQL Authentication • Windows Authentication • SQL Azure • SQL Authentication

  13. Schema • SQL Server • No limitations • SQL Azure • Doesn’t support heaps • All tables must have a clustered index

  14. T-SQL Supportability • SQL Server • No limitations • SQL Azure • Some are fully supported • Some are partially supported • Some are fully unsupported

  15. 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

  16. 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

  17. T-SQL: Unsupported • Common Language Runtime (CLR) • Database file placement • Database mirroring • Distributed queries • Distributed transactions • Filegroup management

  18. T-SQL: Unsupported • Global temporary tables • SQL Server configuration options • SQL Server Service Broker • System tables • Trace flags

  19. Replication & Log Shipping • SQL Server • Fully supported • SQL Azure • Not supported

  20. Is there more?!? But some things come to the rescue Yes…

  21. 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)

  22. Questions?

  23. Thanks! Virgílio Esteves raposo@gmail.com http://pontonetpt.com/blogs/raposo http://twitter.com/vraposo http://pt.linkedin.com/in/virgilioesteves

More Related