how to downgrade sql server 2019 standard to sql server 2014 standard

[peterkimani]

I have backup sql server database 2014 and restored on 2019 for a production environment. Meanwhile our core appkication which runs on tomcat 8 have extrem slow speeds, hence need to restore database backup on sql server 2014.

Sicne the database is quite big, the option for generating scripts is taking too long, the option of sql import and export from 2019 to 2014 is also bring character conversion issue, is there any other way i can roll back from sql server 2019 to sql server 2014

[#2]

Hi @peterkimani -- No, there is no easy way to rollback the database.  But before you do that, what troubleshooting steps have you taken from the database perspective?  I've listed a few ideas below.  Take care.

• Update statistics
• Change database compatibility level to 120
• Since the database is now on SQL 2019, enable Query Store and determine which statements are performing poorly.
• What are the highest wait types?
• Does excessive index fragmentation exist?

[#3]

@bake13 

database compatibility level 

wait types

excessive index fragmentation exist

[#4]

Hi @peterkimani -- Thanks for the information.  My recommendation would be to start working through a few of these items.  Index and statistics maintenance should be a high priority.  From there, enable Query Store on the database for  a while to gain an understanding of which queries are performing poorly.

I don't have context around the wait types, but you may want to consider adjusting max degree of parallelism either at the instance or database level as I noticed CXPACKET was a predominant wait type.

Beyond SQL Server, you didn't mention whether the new SQL2019 instance was on a new server, but I'll assume that is the likely scenario.  Have you used perfmon or other monitoring tool to verify the server is correctly sized, IO throughput is sufficient, etc.?  Take care.

[#5]

@bake13 

i was trying to set high affinity for processor but i got below

TITLE: Microsoft SQL Server Management Studio
------------------------------

Alter failed. (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47008.0&Evtsrc=Microsof...

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The affinity mask specified is greater than the number of CPUs supported or licensed on this edition of SQL Server. (Microsoft SQL Server, Error: 5833)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5833-database-engine-e...

BUTTONS:

OK

-

[#6]

@peterkimani 

Use this statement on the database that is performing poorly. It should get better.

USE putyourdatabasenamehere
GO
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO