New Features in SQL Server 2019

New Features in SQL Server 2019

Support to Persistent Memory (PMEM) Devices

SQL Server 2019 provides support to Persistent Memory (PMEM) devices. SQL Server directly accesses the device, bypassing the storage stack of the operating system for the files placed on the PMEM device.

Columnstore Index Enhancements

SQL Server 2019 also provides enhancements to columnstore index features such as columnstore index maintenance, better metadata memory management, a low-memory load path for columnstore tables, and improved performance for bulk loading to columnstore indexes.

Resumable Online Index Creation

SQL Server 2019 also provides support for resumable online index creation similar to resumable online index rebuilds in SQL Server 2017.  Check out this tip about resumable online index creation.

Up to Five Synchronous Replica Pairs for Availability Groups

We can now configure up to five synchronous replicas in an Availability Groups AG (one primary and up to four secondary replicas) with automatic failover between these replicas.

Enable High Availability Configurations for SQL Server Running in Containers

With SQL Server 2019, we can configure Always on Availability Groups using Kubernetes as an orchestration layer.

Better Scale-out with Automatic Redirection of Connections Based on read/write Intent

In SQL Server 2019, client applications can connect to any of the replicas of the Availability Group. The connection redirects to the primary replica as per the AG configuration and connection string.

SQL Data Discovery and Classification

We have explored this feature in SSMS 17.5. In SQL Server 2019, SQL Data discovery and classification is integrated into the SQL Server engine with new metadata. This enables us to ensure GDPR and other compliance needs for our databases.

Always Encrypted with Secure Enclaves

SQL Server 2019 introduces the secure enclave technology. A secure enclave extends client applications, data trust to the server side. It secures the data from the malware and privileged users.

Certificate Management Functionality in SQL Server Configuration Manager

In SQL Server 2019, certificate management is integrated into the SQL Server Configuration Manager. We can view, validate the certificates being used in SQL Server instance. We can view and validate certificates installed in a SQL Server instance. This also provides detail about certification expiration dates. This feature helps to manage certificates in a better way. We can also deploy certificates for AG instances starting from the primary replica.

UTF-8 Support

SQL Server 2019 provides support for UTF-8 character encoding. We can now create a char or varchar column to store UTF-8 data. This feature improves data compatibility and performance improvements.

Vulnerability Assessment

We can use vulnerability assessment to track compliance of SQL Server instances and Azure SQL Database instances with recognized security best practices. We can implement using the reports shared by this tool. This provides easy to implement security compliance such as GDPR.

Introducing SQL Server 2019: What's New in the Public Preview

Microsoft introduced the public preview of SQL Server 2019 on Monday at the start of the 2018 Ignite conference. While the big news is around SQL Server Big Data clusters, there are a number of major improvements that continue to improve and transform the centerpiece of the Microsoft data platform stack.

You will note most of these improvements are to the relational database engine as development of the BI stack is no longer tied directly to the release of the database engine. Somewhat like SQL Server 2017, this release of SQL Server has a number of minor enhancements.

Steps to Deploying Ola Hallengren IndexOptimize Script

1.       Open browser and go to https://ola.hallengren.com/downloads.html

2.       download IndexOptimize.sql and CommandExecute.sql like below

3.       Create  user database or deploy IndexOptimize.sql and CommandExecute.sql scripts dba maintains database.  

4.       As per your requirement you can execute below scripts .

A. Rebuild or reorganize all indexes with fragmentation on all user databases

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30

B. Rebuild or reorganize all indexes with fragmentation and update modified statistics on all user databases

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y'

C. Update statistics on all user databases

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = NULL,

@FragmentationHigh = NULL,

@UpdateStatistics = 'ALL'

D. Update modified statistics on all user databases

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = NULL,

@FragmentationHigh = NULL,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y'

E. Rebuild or reorganize all indexes with fragmentation on all user databases, performing sort operations in tempdb and using all available CPUs

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@SortInTempdb = 'Y',

@MaxDOP = 0

F. Rebuild or reorganize all indexes with fragmentation on all user databases, using the option to maintain partitioned indexes on the partition level

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@PartitionLevel = 'Y'

G. Rebuild or reorganize all indexes with fragmentation on all user databases, with a time limit so that no commands are executed after 3600 seconds

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@TimeLimit = 3600

H. Rebuild or reorganize all indexes with fragmentation on the table Production.Product in the database AdventureWorks

EXECUTE dbo.IndexOptimize

@Databases = 'AdventureWorks',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@Indexes = 'AdventureWorks.Production.Product'

I. Rebuild or reorganize all indexes with fragmentation except indexes on the table Production.Product in the database AdventureWorks

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@Indexes = 'ALL_INDEXES, -AdventureWorks.Production.Product'

J. Rebuild or reorganize all indexes with fragmentation on all user databases and log the results to a table

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@LogToTable = 'Y'

Pre-Check list to Migrate SQL Server

• Took the Databases Back Up from old server and moved Backups to New Servers.
• Installation SQL Server on server.
• Restored Databases
• Changed DB owner to SA
• Transferred Logins to new server from old server.
• changed Database Compatibility Level.
• Move jobs to server.
• Move linked servers if any.
• complete DBCC CHECKDB WITH DATA_PURITY (Checks for invalid data values based on column data type).
• Update Statistics (sp_updatestats) (Brings database statistics are up to date to allow the query optimizer to choose the best query plan, take a Full Backup of the Database, make sure you can recover the database with all of the migration efforts)

Checklist to configure SQL Server Database Mirroring.

Checklist to configure SQL Server Database Mirroring.

1. Enable SQL TCP and UDP(for Named instances) in firewall.
2. Start MSSQLSERVER Services with Domain user credentials (instead of Local System Account) on principal database Server and Mirror Database Server.
3. Enable DB mirror Ports in Firewall (Principal, Mirror and Witness).
4. principal DB should be FULL Recover model.
5. Take latest full backup and Logbackup.
6. Restore full and Log backup with no recovery option.
7. The end point should have the status started.