A brief comparison of Microsoft SQL Server versions from SQL Server 2012 to SQL Server 2022

 SQL Server 2012:


• Introduced AlwaysOn Availability Groups for high availability.

• Enhanced columnstore indexes for better data warehousing performance.

• Contained Databases for easier database portability.

• Improved security features, including user-defined server roles.


SQL Server 2014:


• In-Memory OLTP (Hekaton) introduced for improved transaction processing.

• Buffer Pool Extensions for SSD integration.

• Enhanced backup to Azure for improved disaster recovery.

• JSON support introduced.


SQL Server 2016:


• PolyBase introduced for querying across relational and non-relational databases.

• Stretch Database feature for hybrid cloud scenarios.

• Always Encrypted for enhanced security.

• Dynamic Data Masking for controlling access to sensitive data.


SQL Server 2017:


• Introduced support for SQL Server on Linux.

• Adaptive Query Processing for improved query performance.

• Graph Database capabilities.

• Automatic database tuning.


SQL Server 2019:


• Big Data Clusters introduced for integrated analytics on big data.

• Intelligent Query Processing for improved performance.

• Enhanced security features, including Data Classification and Always Encrypted with secure enclaves.

• Support for UTF-8 character encoding.


SQL Server 2022:


• Improved performance with enhancements to Intelligent Query Processing.

• Accelerated Database Recovery for faster database recovery times.

• Enhanced security with features like Certificate-Based Authentication.

• Support for JSON data types improvements.


Comments

Post a Comment

Popular posts from this blog

SQL Server DBA scripts for day-to-day tasks

   1. Check Database Size: SELECT name AS 'Database Name', size * 8 / 1024 AS 'Size (MB)' FROM sys.master_files WHERE DB_NAME(database_id) NOT IN ('master', 'tempdb', 'model', 'msdb'); 2. Check Fragmentation: SELECT OBJECT_NAME(ind.OBJECT_ID) AS 'Table', ind.name AS 'Index', indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER BY indexstats.avg_fragmentation_in_percent DESC; 3. Backup Information: SELECT database_name, backup_start_date, backup_finish_date, compressed_backup_size/1024/1024 AS 'Backup Size (MB)' FROM msdb.dbo.backupset ORDER BY backup_start_date DESC; 4. Check Active Sessions: SELECT session_id, login_time, host_name, program_name FROM sys.dm_exec_session...
Read more

T-SQL Script to check the database backup and restore progress and estimated completion time

  Script 1:  select  d.name as [Database Name], Percent_complete,  dateadd(second,estimated_completion_time/1000,  getdate()) as EstimatedCompletionTime, Getdate() as Current_Time_now,  CAST((datediff(minute, start_time, getdate())) as varchar(50)) +'  ' + 'Minutes' as CMD_Running_From,  CAST((estimated_completion_time/1000/60) as varchar(50)) +'  ' + 'Minutes'  as Will_Complete_in, start_time as CMD_Start_Time,  command As Running_Command from sys.dm_exec_requests req inner join sys.sysdatabases d on d.dbid = req.database_id where  req.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG') Script 2: select  d.name, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as EstimatedCompletionTime, Getdate() as now,  datediff(minute, start_time, getdate()) as running, estimated_completion_time/1000/60 as togo, start_time, command  from sys.dm_exec_requests req inner jo...
Read more

SQL Server Dynamic management views (DMV's)

  dm_audit_actions dm_audit_class_type_map dm_broker_activated_tasks dm_broker_connections dm_broker_forwarded_messages dm_broker_queue_monitors dm_cdc_errors dm_cdc_log_scan_sessions dm_clr_appdomains dm_clr_loaded_assemblies dm_clr_properties dm_clr_tasks dm_cryptographic_provider_algorithms dm_cryptographic_provider_keys dm_cryptographic_provider_properties dm_cryptographic_provider_sessions dm_database_encryption_keys dm_db_file_space_usage dm_db_index_operational_stats dm_db_index_physical_stats dm_db_index_usage_stats dm_db_mirroring_auto_page_repair dm_db_mirroring_connections dm_db_mirroring_past_actions dm_db_missing_index_columns dm_db_missing_index_details dm_db_missing_index_group_stats dm_db_missing_index_groups dm_db_partition_stats dm_db_persisted_sku_features dm_db_script_level dm_db_session_space_usage dm_db_task_space_usage dm_exec_background_job_queue dm_exec_background_job_queue_stats dm_exec_cached_plan_dependent_objects dm_exec_cached_plans dm_exec_connections...
Read more