T-SQL Supportability
Download
Report
Transcript T-SQL Supportability
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