Sql Database Compare
admin 4/30/2019
-->
Azure SQL Database shares a common code base with SQL Server. The features of SQL Server supported by Azure SQL Database depend on the type of Azure SQL database that you create. With Azure SQL Database, you can create a database as part of a managed instance, as a single database, or as part of an elastic pool.
Microsoft continues to add features to Azure SQL Database. Visit the Service Updates webpage for Azure for the newest updates using these filters:
- Filtered to the SQL Database service.
- Filtered to General Availability (GA) announcements for SQL Database features.
Sql Database Compare Schema
Sql Database Compare Tools
If you have Visual Studio Professional you should also have Data Comparison Tool inside it. It's another great tool to compare data and schema. If you don't have access to any other tools to compare the data in two tables, I would use tablediff.exe, which is included in the SQL Server installation. You'll find it in the SQL Server program path. Comparison of relational database management systems. Jump to navigation Jump to search. The following tables compare. To compound this confusion the functionality can overlap with that of a parent database. An SQL schema is simply a namespace within a database. DBComparer is a professional database comparison tool for analyzing the differences in Microsoft SQL Server 2008 (and 2005) database structures. It is an excellent tool for any project development team or DBA managing multiple copies of the same database on an SQL Server.
SQL Server feature support in Azure SQL Database
The following table lists the major features of SQL Server and provides information about whether the feature is partially or fully supported and a link to more information about the feature.
IRONING BOARD • is a hamper that is used for holding dirty clothes for washing or wet clothes for drying. Tools equipment and paraphernalia for taking vital signs. FLAT IRON • Is along, narrow board, often with collapsible supporting legs, used as a working surface for ironing.
| SQL Feature | Supported by single databases and elastic pools | Supported by managed instances |
|---|---|---|
| Active geo-replication | Yes - all service tiers other than hyperscale | No, see Auto-failover groups(preview) as an alternative |
| Auto-failover groups | Yes - all service tiers other than hyperscale | Yes, in public preview |
| Always Encrypted | Yes - see Cert store and Key vault | Yes - see Cert store and Key vault |
| Always On Availability Groups | High availability is included with every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database | High availability is included with every database and cannot be managed by user. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database |
| Attach a database | No | No |
| Application roles | Yes | Yes |
| Auditing | Yes | Yes, with some differences |
| Automatic backups | Yes. Full backups are taken every 7 days, differential 12 hours, and log backups every 5-10 min. | Yes. Full backups are taken every 7 days, differential 12 hours, and log backups every 5-10 min. |
| Automatic tuning (plan forcing) | Yes | Yes |
| Automatic tuning (indexes) | Yes | No |
| Azure Data Studio | Yes | Yes |
| BACPAC file (export) | Yes - see SQL Database export | Yes - see SQL Database export |
| BACPAC file (import) | Yes - see SQL Database import | Yes - see SQL Database import |
| BACKUP command | No, only system-initiated automatic backups - see Automated backups | Yes, user initiated copy-only backups to Azure Blob Storage (automatic system backups cannot be initiated by user) - see Backup differences |
| Built-in functions | Most - see individual functions | Yes - see Stored procedures, functions, triggers differences |
| BULK INSERT statement | Yes, but just from Azure Blob storage as a source. | Yes, but just from Azure Blob Storage as a source - see differences. |
| Certificates and asymmetric keys | Yes, without access to file system for BACKUP and CREATE operations. | Yes, without access to file system for BACKUP and CREATE operations - see certificate differences. |
| Change data capture | No | Yes |
| Change tracking | Yes | Yes |
| Collation - database | Yes | Yes |
| Collation - server/instance | No, default logical server collation SQL_Latin1_General_CP1_CI_AS is always used. | Yes, can be set when the instance is created and cannot be updated later. |
| Columnstore indexes | Yes - Premium tier, Standard tier - S3 and above, General Purpose tier, and Business Critical tiers | Yes |
| Common language runtime (CLR) | No | Yes, but without access to file system in CREATE ASSEMBLY statement - see CLR differences |
| Contained databases | Yes | Currently no due to defect in RESTORE including point-in-time RESTORE. This is a defect that will be fixed soon. |
| Contained users | Yes | Yes |
| Control of flow language keywords | Yes | Yes |
| Credentials | Yes, but only database scoped credentials. | Yes, but only Azure Key Vault and SHARED ACCESS SIGNATURE are supported see details |
| Cross-database queries | No - see Elastic queries | Yes, plus Elastic queries |
| Cross-database transactions | No | Yes, within the instance. See Linked server differences for cross-instance queries. |
| Cursors | Yes | Yes |
| Data compression | Yes | Yes |
| Database mail | No | Yes |
| Data Migration Service (DMS) | Yes | Yes |
| Database mirroring | No | No |
| Database configuration settings | Yes | Yes |
| Data Quality Services (DQS) | No | No |
| Database snapshots | No | No |
| Data types | Yes | Yes |
| DBCC statements | Most - see individual statements | Yes - see DBCC differences |
| DDL statements | Most - see individual statements | Yes - see T-SQL differences |
| DDL triggers | Database only | Yes |
| Distributed partition views | No | Yes |
| Distributed transactions - MS DTC | No - see Elastic transactions | No - see Linked server differences |
| DML statements | Yes | Yes |
| DML triggers | Most - see individual statements | Yes |
| DMVs | Most - see individual DMVs | Yes - see T-SQL differences |
| Dynamic data masking | Yes | Yes |
| Elastic pools | Yes | Built-in - a single Managed Instance can have multiple databases that share the same pool of resources |
| Event notifications | No - see Alerts | No |
| Expressions | Yes | Yes |
| Extended events | Some - see Extended events in SQL Database | Yes - see Extended events differences |
| Extended stored procedures | No | No |
| Files and file groups | Primary file group only | Yes. File paths are automatically assigned and the file location cannot be specified in ALTER DATABASE ADD FILEstatement. |
| Filestream | No | No |
| Full-text search | Yes, but third-party word breakers are not supported | Yes, but third-party word breakers are not supported |
| Functions | Most - see individual functions | Yes - see Stored procedures, functions, triggers differences |
| Geo-restore | Yes - all service tiers other than hyperscale | Yes - using Azure PowerShell. |
| Graph processing | Yes | Yes |
| In-memory optimization | Yes - Premium and Business Critical tiers only | Yes - Business Critical tier only |
| JSON data support | Yes | Yes |
| Language elements | Most - see individual elements | Yes - see T-SQL differences |
| Linked servers | No - see Elastic query | Only to SQL Server and SQL Database |
| Log shipping | High availability is included with every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database | Natively built-in as a part of DMS migration process. High availability is included with every database and it is not recommended to use Log-shipping as HA alternative. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database |
| Logins and users | Yes, but CREATE and ALTER login statements do not offer all the options (no Windows and server-level Azure Active Directory logins). EXECUTE AS LOGIN is not supported - use EXECUTE AS USER instead. | Yes, with some differences. Windows logins are not supported and they should be replaced with Azure Active Directory logins. |
| Long-term backup retention - LTR | Yes, keep automatically taken backups up to 10 years. | Not yet. Use COPY_ONLYmanual backups as a temporary workaround. |
| Master Data Services (MDS) | No | No |
| Minimal logging in bulk import | No | No |
| Modifying system data | No | Yes |
| OLE Automation | No | No |
| Online index operations | Yes | Yes |
| OPENDATASOURCE | No | Yes, only to other Azure SQL Databases and SQL Servers. See T-SQL differences |
| OPENJSON | Yes | Yes |
| OPENQUERY | No | Yes, only to other Azure SQL Databases and SQL Servers. See T-SQL differences |
| OPENROWSET | Yes, only to import from Azure Blob storage. | Yes, only to other Azure SQL Databases and SQL Servers, and to import from Azure Blob storage. See T-SQL differences |
| OPENXML | Yes | Yes |
| Operators | Most - see individual operators | Yes - see T-SQL differences |
| Partitioning | Yes | Yes |
| Public IP address | Yes. The access can be restricted using firewall or service endpoints. | Yes. Needs to be explicitly enabled and port 3342 must be enabled in NSG rules. Public IP can be disabled if needed. See Public endpoint for more details. |
| Point in time database restore | Yes - all service tiers other than hyperscale - see SQL Database recovery | Yes - see SQL Database recovery |
| Polybase | No | No |
| Policy-based management | No | No |
| Predicates | Yes | Yes |
| Query Notifications | No | Yes |
| Query Performance Insights | Yes | No |
| R Services | Yes, in public preview | No |
| Resource governor | No | Yes |
| RESTORE statements | No | Yes, with mandatory FROM URL options for the backups files placed on Azure Blob Storage. See Restore differences |
| Restore database from backup | From automated backups only - see SQL Database recovery | From automated backups - see SQL Database recovery and from full backups placed on Azure Blob Storage - see Backup differences |
| Row Level Security | Yes | Yes |
| Semantic search | No | No |
| Sequence numbers | Yes | Yes |
| Service Broker | No | Yes, but only within the instance. See Service Broker differences |
| Server configuration settings | No | Yes - see T-SQL differences |
| Set statements | Most - see individual statements | Yes - see T-SQL differences |
| SMO | Yes | Yes version 150 |
| Spatial | Yes | Yes |
| SQL Analytics | Yes | Yes |
| SQL Data Sync | Yes | No |
| SQL Server Agent | No - see Elastic jobs | Yes - see SQL Server Agent differences |
| SQL Server Analysis Services (SSAS) | No, Azure Analysis Services is a separate Azure cloud service. | No, Azure Analysis Services is a separate Azure cloud service. |
| SQL Server Auditing | No - see SQL Database auditing | Yes - see Auditing differences |
| SQL Server Data Tools (SSDT) | Yes | Yes |
| SQL Server Integration Services (SSIS) | Yes, with a managed SSIS in Azure Data Factory (ADF) environment, where packages are stored in SSISDB hosted by Azure SQL Database and executed on Azure SSIS Integration Runtime (IR), see Create Azure-SSIS IR in ADF. To compare the SSIS features in SQL Database server and Managed Instance, see Compare Azure SQL Database single databases/elastic pools and Managed Instance. | Yes, with a managed SSIS in Azure Data Factory (ADF) environment, where packages are stored in SSISDB hosted by Managed Instance and executed on Azure SSIS Integration Runtime (IR), see Create Azure-SSIS IR in ADF. To compare the SSIS features in SQL Database and Managed Instance, see Compare Azure SQL Database single databases/elastic pools and Managed Instance. |
| SQL Server Management Studio (SSMS) | Yes | Yes version 18.0 and higher |
| SQL Server PowerShell | Yes | Yes |
| SQL Server Profiler | No - see Extended events | Yes |
| SQL Server Replication | Transactional and snapshot replication subscriber only | Yes, in public preview |
| SQL Server Reporting Services (SSRS) | No - see Power BI | No - see Power BI |
| Stored procedures | Yes | Yes |
| System stored functions | Most - see individual functions | Yes - see Stored procedures, functions, triggers differences |
| System stored procedures | Some - see individual stored procedures | Yes - see Stored procedures, functions, triggers differences |
| System tables | Some - see individual tables | Yes - see T-SQL differences |
| System catalog views | Some - see individual views | Yes - see T-SQL differences |
| Temporary tables | Local and database-scoped global temporary tables | Local and instance-scoped global temporary tables |
| Temporal tables | Yes | Yes |
| Time zone choice | No | Yes(preview) |
| Threat detection | Yes | Yes |
| Trace flags | No | No |
| Variables | Yes | Yes |
| Transparent data encryption (TDE) | Yes - General Purpose and Business Critical service tiers only | Yes |
| VNet | Partial - see VNet Endpoints | Yes, Resource Manager model only |
| Windows Server Failover Clustering | High availability is included with every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database | High availability is included with every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database |
| XML indexes | Yes | Yes |
Next steps
- For information about the Azure SQL Database service, see What is SQL Database?
- For information about a Managed Instance, see What is a Managed Instance?.