Comparación entre SQL Server y SQL Azure
El siguiente artículo realiza una comparación entre SQL Azure Database y SQL Server en las áreas de adminsitración lógica, administración física, aprovisionamiento, soporte de TSQL, almacenamiento, y otras capacidades.
Feature | SQL Server (On-premise) | SQL Azure | Mitigation |
Data Storage | No size limits as such | · Web Edition · Business Edition Exact size and pricing information can be obtained at Pricing Overview. Note: When you reach the allocated level (1 GB or 10 GB), only SELECTs and DELETEs will be supported. UPDATEs and INSERTs will throw an error. | · An archival process can be created where older data can be migrated to another database in SQL Azure or on premise. · Because of above size constraints, one of the recommendations is to partition the data across databases. Creating multiple databases will allow you take maximum advantage of the computing power of multiple nodes. The biggest value in the Azure model is the elasticity of being able to create as many databases as you need, when your demand peaks and delete/drop the databases as your demand subsides. The biggest challenge is writing the application to scale across multiple databases. Once this is achieved, the logic can be extended to scale across N number of databases. |
Edition | · Express · Workgroup · Standard · Enterprise | · Enterprise Edition | |
Connectivity | · SQL Server Management Studio · SQLCMD | · SQL Server 2008 R2 Management Studio provides complete connectivity to SQL azure. Prior versions have limited support. · SQLCMD | |
Data Migration | | · SQL Server Integration Services, BCP and SqlBulkCopyAPI are supported | |
Authentication | · SQL Authentication · Windows Authentication | · SQL Server Authentication only | Use SQL Server authentication |
Schema | No such limitation | SQL Azure does not support heaps. ALL tables must have a clustered index before data can be inserted. | Check all scripts to make sure all table creation scripts include clustered index. |
TSQL Supportability | Certain TSQL commands are fully supported; some are partially supported while others are unsupported. · Supported TSQL: http://msdn.microsoft.com/en-us/library/ee336270.aspx · Partially Supported TSQL: http://msdn.microsoft.com/en-us/library/ee336267.aspx · Unsupported TSQL: http://msdn.microsoft.com/en-us/library/ee336253.aspx | ||
“USE” command | Supported | Not supported | USE command is not supported because each of the databases created by the user may not be on the same physical server. So the application has to retrieve data separately from multiple databases and consolidate at the application level. |
Transactional Replication | Supported | Not supported | You can use BCP or SSIS to get the data out on-demand into an on premise SQL Server. You can also use the SQL Data Sync tool to keep on-premise SQL Server and SQL Azure in sync. |
Log Shipping | Supported | Not supported | |
Database Mirroring | Supported | Not supported | |
SQL Agent | Supported | Cannot run SQL agent/jobs on SQL Azure | You can run SQL agent on on-premise SQL Server and connect to SQL Azure |
Server options | Supported | · Some system views are supported (http://msdn.microsoft.com/en-us/library/ee336238.aspx) | The idea is most system level metadata is disabled as it does not make sense in a cloud model to expose server level information |
Connection Limitations | N/A | To provide fair usage experience to all tenants on the nodes, connections to service may be closed due to one of the following situations: · Excessive resource usage · Long running queries – (over 5 minutes) · Long running single transactions between BEGIN TRAN and END TRAN – (over 5 minutes) · Idle Connections – (over 30 minutes) | |
SSIS | Can run SSIS on-premise | Cannot run SSIS in SQL Azure | Run SSIS on site and connect to SQL Azure with ADO.NET provider |
Saludos,
Ing. Eduardo Castro Martínez, PhD – Microsoft SQL Server MVP
Costa Rica
Technorati Tags: SQL Server
LiveJournal Tags: SQL Server
del.icio.us Tags: SQL Server
http://ecastrom.spaces.live.com
http://universosql.blogspot.com
http://todosobresql.blogspot.com
http://todosobresqlserver.wordpress.com
http://mswindowscr.org/blogs/sql/default.aspx
http://citicr.org/blogs/noticias/default.aspx
http://sqlserverpedia.blogspot.com/
Note: Cross posted from Eduardo Castro.Permalink
Comments