T-SQL Supportability

Report
XVII Encontro – 29/11/2011
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
 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
Internet
Azure
Forwards “sticky” sessions
to TDS protocol tier
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
“Coded” Reason Codes
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
Yes… But some things come to the rescue
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)
Questions?
Thanks!
Virgílio Esteves
[email protected]
http://pontonetpt.com/blogs/raposo
http://twitter.com/vraposo
http://pt.linkedin.com/in/virgilioesteves

similar documents